Jason Brimhall | SQL RNNR

Categories: News, Professional, SSC, TSQL Tuesday

Tags: Database Administration, Database Maintenance, Professional, TSQL Tuesday

Comments: 3 Comments

Published on: January 14, 2020

TSQL Tuesday

Behold, the second Tuesday of January 2020 and that means it is time for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

This month, the blog party is almost like a masquerade ball. John Shaulis (b | t) has implored us to take hold of our inner impostor. Okay okay, he hasn’t asked us to be impostors, but rather he has requested we talk about Impostor Syndrome.

At first glance, and seeing the word “impostor”, my thoughts turned outward. I thought John wanted us to out all of those Sr. DBA candidates out there who are soooo confident but don’t know the difference between a PK and FK or even the difference between a delete and truncate (as examples).

In reality, John is asking about something entirely different. Looking up “Impostor Syndrome” I see several definitions similar to the one that John posted. Here are a couple of examples.

Impostor syndrome can be defined as a collection of feelings of inadequacy that persist despite evident success. ‘Impostors’ suffer from chronic self-doubt and a sense of intellectual fraudulence that override any feelings of success or external proof of their competence. – Harvard Business Review

And…

Impostor syndrome is a psychological pattern in which one doubts one’s accomplishments and has a persistent internalized fear of being exposed as a “fraud”. – Wikipedia

These definitions reveal something very pertinent to the topic. The syndrome consists of the presence of persistent, nagging, chronic self-doubt. Even in the face of success, the feelings of inadequacy can’t be escaped. Well, this certainly adds light to the topic and made me ponder even further. You see, I think the disease is a much larger problem than having the occasional feeling of being inadequate. Persistent chronic feelings of inadequacy and Impostor Syndrome (as defined) would make me concerned about the presence of depression as well. This can be a very serious issue and shouldn’t be taken lightly. Seek help!

Well, What if…

What about the fact that so many of us do have feelings of inadequacy? The occasional feeling of not being good enough is not entirely a bad thing. (Again, the trick is figuring out where that line between chronic/persistent/nagging feelings ends and the occasional feelings is ok.)

There have been many times when I have personally had the feeling of inadequacy as a Data Professional. These thoughts often creep up whenever doing something new or different or even something uncommon (but practiced). I have even had these thoughts occasionally right before speaking. I have a close friend who has these thoughts just about every time before he speaks.

When these thoughts occasionally crop up, what is done next is the important thing. How does one cope? Does the coping mechanism work? Do you practice your speaking in front of a mirror for hundreds of hours? Maybe you have a few people proof read an article you are writing. Maybe, you build a proof of concept and then ask a couple friends to test it and try to break it to make sure it works.

Coping mechanisms are great methods to assist in the removal of self-doubt. Another good coping mechanism is to get some physical activity. Physical activity can help with the chronic self doubt and is also something therapists may recommend for depression (important because the symptoms for both Impostor Syndrome and Depression are so close).

Another component of Impostor Syndrome is the fear of being discovered as a fraud. Living in fear isn’t healthy. If there is the persistent unshakable feeling of fear about being discovered as a fraud, then it is time to find help and also start troubleshooting your personal health. Here is a good starting point to help troubleshoot your health in regards to this syndrome.

One of the best coping mechanisms for Impostor Syndrome is the use of learned behaviors. Some of the learned behaviors that are great for everybody are as follows:

Learn to take your mistakes in stride. Mistakes are natural. Own the mistake. Mistakes can make us better at we do. The sooner we own a mistake, the sooner we can learn how to not make that same mistake again.
Learn to accept internal validation. Not everybody needs a participation trophy. Internal validation is just as good as external validation. Also be accepting of constructive criticism.
Set smaller attainable goals directed at specific skills or behaviors you wish to improve upon or learn.
Learn to rely on others. Be able to reach out and ask for help.
Instead of being an information hoarder, learn to disseminate knowledge. In addition, it is perfectly acceptable to admit you don’t know something and trust that you can learn it quickly or find somebody that already knows it.

Wrapping it Up

It is not abnormal to have the occasional feelings of inadequacy. In IT, and particularly among those who are successful, thoughts of being a fraud can occur. The important thing to do is recognize the thoughts and frequency. When the thoughts start to creep in, learn to replace it with a coping mechanism or an alternate behavior. This article provided some coping mechanisms, and some behaviors to try and learn.

In the case of experiencing chronic inescapable self-doubt and living in constant fear of being found out as a fraud, that is an entirely serious issue and professional help may be required.

Feel free to explore some of the other TSQL Tuesday posts I have written.

If you are in need of a little tune-up for your XE skills, I recommend reading a bit on Extended Events to get up to date. For some “back to basics” related articles, feel free to read here.

PowerShell to Backup XE Session

by Jason Brimhall

Categories: Extended Events, News, Professional, Scripts, SSC

Tags: Extended Events, PoSH, Powershell, SQL Administration, SQL Internals, XE, XEvents

Comments: No Comments

Published on: January 10, 2020

Quite some time ago, I shared a few articles that peeled back the top layer of how to use PowerShell (PoSh) with Extended Events (XEvents). Among those articles, I showed how to retrieve the metadata, predicates and actions, and targets (to mention a few). Those are prime examples of articles showing some of the basics, which means there is plenty of room for some deeper dive articles involving both PoSh and XEvents. One topic that can help us bridge to the deeper end of the XEvents pool is how to generate scripts for our XEvent Sessions.

In this article, I will venture to show how to generate good backup scripts of our sessions using PoSh. That said, there are some caveats to using PoSh to generate these scripts and I will share those as well.

Wading to the Deeper End!

PoSh provides many methods and classes that allow us to more easily do various things. One of my favorite uses for PoSh is to automate tasks across the enterprise database server footprint. Nothing like creating a script that can effectively hit all of the servers one manages in one fell swoop to help minimize effort and time spent touching all of the servers. Afterall, a prime directive of the effective DBA is to be lazy.

So, when I look at creating a script in PoSh, I also look at how can I make this script function for a single server just the same as it would work for hundreds of servers. That will be one of the “nice to have” add-ons that you will see in this script. But, how did we get there in the first place?

A critical component of using PoSh to generate the scripts for each of the XEvent Sessions on the server is the GetScript(). GetScript() can be called for many different objects within PoSh. Another critical component is the XEvent SMO classes that were added as of SQL Server 2012. Take a mental note of that because it will come into play in a bit.

In addition to these critical components, I used some of the stuff that I mentioned in the previous articles as well as my article on XEvent management with PoSh. As I have said many times, these articles are building blocks and do rely upon many fundamentals divulged in previous articles.

Getting into the Script

Given this is a script that interacts with SQL Server, we need to ensure the SqlServer module is installed and loaded. It if is already installed, that is great. During my testing, I found that one of my servers, I ran this locally on, did not have it installed. With that discovery, I figured it is just easier to implement a check. If the module is not installed, then install it.

$modulecheck = Get-InstalledModule -Name SqlServer | Select Name
IF (!$modulecheck){

Install-Module SqlServer -Force
}
 
Import-Module SqlServer

$modulecheck = Get-InstalledModule -Name SqlServer | Select Name

IF (!$modulecheck){

Install-Module SqlServer -Force

}

Import-Module SqlServer

The next major component is how I fetch my list of servers. I prefer to have all of my enterprise SQL Servers listed within SQL Server. I keep a table with all of the servers and designate various attributes for the Servers (e.g. prod or dev, business unit, decommissioned etc).

IF($svrname){
    $SQLServerList = [ordered]@{
        ServerName = $svrname}
    }
ELSE {
    IF($TypeName){
        $SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "
    SELECT DISTINCT
	   ServerName
	FROM Mgmt.ServerList			   sl
		INNER JOIN [Mgmt].[ServerType] st
			ON sl.ServerTypeID = st.TypeID
	WHERE st.TypeName = '$TypeName';"
        }
    ELSE{
        $SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "
    SELECT DISTINCT
	   ServerName
	FROM Mgmt.ServerList			   sl
		INNER JOIN [Mgmt].[ServerType] st
			ON sl.ServerTypeID = st.TypeID
	WHERE st.TypeID <> 7;"
        }
    }

IF($svrname){

$SQLServerList = [ordered]@{

ServerName = $svrname}

}

ELSE {

IF($TypeName){

$SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "

SELECT DISTINCT

ServerName

FROM Mgmt.ServerList sl

INNER JOIN [Mgmt].[ServerType] st

ON sl.ServerTypeID = st.TypeID

WHERE st.TypeName = '$TypeName';"

}

ELSE{

$SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "

SELECT DISTINCT

ServerName

FROM Mgmt.ServerList sl

INNER JOIN [Mgmt].[ServerType] st

ON sl.ServerTypeID = st.TypeID

WHERE st.TypeID <> 7;"

}

Once, I know which servers need to be queried, I need to make sure I set my PoSh location properly.

SET-Location SQLServer:\XEvent\$SvrName

1	SET-Location SQLServer:\XEvent\$SvrName

With this script, I also have the ability to script out a specific XEvent session or to script every XEvent session deployed on the server. When scripting all sessions on the server, I opted to put all of the sessions into a single output script file. Since, this script can be run against multiple servers, I name each output according to the Server Name and the Instance on that server followed by the XEvent Session name (or all_sessions in the event all sessions are being returned).

IF($XESession){
   $OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_" + $XESession + ".sql"
   $session = dir | Where-Object {$_.name -eq $XESession}
   $sessionlist = $session
       }
ELSE {
    $OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_all_xesessions.sql"
    $sessionlist = dir #| Select name
    }

IF($XESession){

$OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_" + $XESession + ".sql"

$session = dir | Where-Object {$_.name -eq $XESession}

$sessionlist = $session

}

ELSE {

$OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_all_xesessions.sql"

$sessionlist = dir #| Select name

}

Since I prefer to use a custom event_file path, I also wanted to ensure my script would produce a means to ensure the custom file path is created. I needed to validate that this only gets added to those sessions that had an event_file added to it.

    $filetarget = ''
    $filetarget = $session.Targets.TargetFields | Where-Object {$_.name -eq 'filename'} | Select Value

    IF ($filetarget -and $filetarget.Value -match ":\\"){
     $FileDir = ''
        $FileDir = Split-Path $filetarget.Value -Parent

$filetarget = ''

$filetarget = $session.Targets.TargetFields | Where-Object {$_.name -eq 'filename'} | Select Value

IF ($filetarget -and $filetarget.Value -match ":\\"){

$FileDir = ''

$FileDir = Split-Path $filetarget.Value -Parent

There are a few more nuggets throughout that help bring this whole thing together. This is what the completed script looks like.

# does not work against 2008R2. The XE smo classes are not backwards compatible to 2008r2

$modulecheck = Get-InstalledModule -Name SqlServer | Select Name
IF (!$modulecheck){

Install-Module SqlServer -Force
}
 
Import-Module SqlServer


$DestinationFolder = "C:\Database\XE\PoshScriptOut\" #make sure to end the path with \
$XESession = "" #"WaitsExt"#= "AlwaysOn_health"
$SvrName = "" #$env:computername "yourservername", empty string fetches all servers
$InstanceName = "" # "default" empty string for all instances on server or as a short-cut
$SQLRepository = "YourServerListDBServer" # enter the name of the SQL Server where your database server list is maintained
$TypeName = $null #e.g. the environment server type such as Prod, Dev, Etc.


IF($svrname){
    $SQLServerList = [ordered]@{
        ServerName = $svrname}
    }
ELSE {
    IF($TypeName){
        $SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "
    SELECT DISTINCT
	   ServerName
	FROM Mgmt.ServerList			   sl
		INNER JOIN [Mgmt].[ServerType] st
			ON sl.ServerTypeID = st.TypeID
	WHERE st.TypeName = '$TypeName';"
        }
    ELSE{
        $SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "
    SELECT DISTINCT
	   ServerName
	FROM Mgmt.ServerList			   sl
		INNER JOIN [Mgmt].[ServerType] st
			ON sl.ServerTypeID = st.TypeID
	WHERE st.TypeID <> 7;"
        }
    }

ForEach  ($server in  $SQLServerList){ 
  $SvrName = $server.ServerName

SET-Location SQLServer:\XEvent\$SvrName

IF($InstanceName){
    $yourinstancelist = [ordered]@{
        DisplayName = $InstanceName}
    }
ELSE {
$yourinstancelist = get-childitem | Select DisplayName
}

ForEach  ($yourinstancename in $yourinstancelist.DisplayName){

cd \XEvent\$SvrName\$yourinstancename\sessions


$scriptout = ""

If(!(test-path $DestinationFolder))
{
      New-Item -ItemType Directory -Force -Path $DestinationFolder
}

IF($XESession){
   $OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_" + $XESession + ".sql"
   $session = dir | Where-Object {$_.name -eq $XESession}
   $sessionlist = $session
       }
ELSE {
    $OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_all_xesessions.sql"
    $sessionlist = dir #| Select name
    }

ForEach  ($session in $sessionlist){
    $sessname = $session.name

    $filetarget = ''
    $filetarget = $session.Targets.TargetFields | Where-Object {$_.name -eq 'filename'} | Select Value

    IF ($filetarget -and $filetarget.Value -match ":\\"){
     $FileDir = ''
        $FileDir = Split-Path $filetarget.Value -Parent

$sessionscript = $session.ScriptCreate()
$scriptout += 
"USE [master];
GO
-- Create the Event Session
IF EXISTS ( SELECT *
		        FROM sys.server_event_sessions
		        WHERE name = N'" + $sessname +"' )
    DROP EVENT SESSION [" + $sessname + "]
    ON SERVER;
GO

EXECUTE xp_create_subdir '" + $FileDir + "\';
GO

" + 
$sessionscript.GetScript() + ";

"
        #}

    }
    ELSE {
$sessionscript = $session.ScriptCreate()
$scriptout += 
"USE [master];
GO
-- Create the Event Session
IF EXISTS ( SELECT *
		        FROM sys.server_event_sessions
		        WHERE name = N'" + $sessname +"' )
    DROP EVENT SESSION [" + $sessname + "]
    ON SERVER;
GO

" + 
$sessionscript.GetScript() + ";

"
    }
}
$scriptout | Out-File $OutputFile -Force
}
} #close foreach loop of serverlist

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

# does not work against 2008R2. The XE smo classes are not backwards compatible to 2008r2

$modulecheck = Get-InstalledModule -Name SqlServer | Select Name

IF (!$modulecheck){

Install-Module SqlServer -Force

}

Import-Module SqlServer

$DestinationFolder = "C:\Database\XE\PoshScriptOut\" #make sure to end the path with \

$XESession = "" #"WaitsExt"#= "AlwaysOn_health"

$SvrName = "" #$env:computername "yourservername", empty string fetches all servers

$InstanceName = "" # "default" empty string for all instances on server or as a short-cut

$SQLRepository = "YourServerListDBServer" # enter the name of the SQL Server where your database server list is maintained

$TypeName = $null #e.g. the environment server type such as Prod, Dev, Etc.

IF($svrname){

$SQLServerList = [ordered]@{

ServerName = $svrname}

}

ELSE {

IF($TypeName){

$SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "

SELECT DISTINCT

ServerName

FROM Mgmt.ServerList sl

INNER JOIN [Mgmt].[ServerType] st

ON sl.ServerTypeID = st.TypeID

WHERE st.TypeName = '$TypeName';"

}

ELSE{

$SQLServerList = Invoke-Sqlcmd -serverinstance $SQLRepository -database "DBA" -query "

SELECT DISTINCT

ServerName

FROM Mgmt.ServerList sl

INNER JOIN [Mgmt].[ServerType] st

ON sl.ServerTypeID = st.TypeID

WHERE st.TypeID <> 7;"

}

ForEach ($server in $SQLServerList){

$SvrName = $server.ServerName

SET-Location SQLServer:\XEvent\$SvrName

IF($InstanceName){

$yourinstancelist = [ordered]@{

DisplayName = $InstanceName}

}

ELSE {

$yourinstancelist = get-childitem | Select DisplayName

}

ForEach ($yourinstancename in $yourinstancelist.DisplayName){

cd \XEvent\$SvrName\$yourinstancename\sessions

$scriptout = ""

If(!(test-path $DestinationFolder))

{

New-Item -ItemType Directory -Force -Path $DestinationFolder

}

IF($XESession){

$OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_" + $XESession + ".sql"

$session = dir | Where-Object {$_.name -eq $XESession}

$sessionlist = $session

}

ELSE {

$OutputFile = $DestinationFolder + $SvrName + "_" + $yourinstancename + "_all_xesessions.sql"

$sessionlist = dir #| Select name

}

ForEach ($session in $sessionlist){

$sessname = $session.name

$filetarget = ''

$filetarget = $session.Targets.TargetFields | Where-Object {$_.name -eq 'filename'} | Select Value

IF ($filetarget -and $filetarget.Value -match ":\\"){

$FileDir = ''

$FileDir = Split-Path $filetarget.Value -Parent

$sessionscript = $session.ScriptCreate()

$scriptout +=

"USE [master];

-- Create the Event Session

IF EXISTS ( SELECT *

FROM sys.server_event_sessions

WHERE name = N'" + $sessname +"' )

DROP EVENT SESSION [" + $sessname + "]

ON SERVER;

EXECUTE xp_create_subdir '" + $FileDir + "\';

" +

$sessionscript.GetScript() + ";

}

ELSE {

$sessionscript = $session.ScriptCreate()

$scriptout +=

"USE [master];

-- Create the Event Session

IF EXISTS ( SELECT *

FROM sys.server_event_sessions

WHERE name = N'" + $sessname +"' )

DROP EVENT SESSION [" + $sessname + "]

ON SERVER;

" +

$sessionscript.GetScript() + ";

}

$scriptout | Out-File $OutputFile -Force

}

} #close foreach loop of serverlist

If you follow along on that script, you will see that I concatenate the sessions together and then force overwrite the output file. This ensures that I am not appending to an existing file and also ensures that I have the most current version of the XEvent session script. I configure this to output to the local server from where the script is executed.

Caveats

Using PoSh to generate scripts is extremely handy especially when working across numerous servers. That said, there are certain problems for which one must be aware. First is that the SMO classes for PoSh were introduced as of SQL Server 2012. These objects are not backwards compatible.

The second issue I have may be less of a concern to you than me, but it goes well with concerns I mentioned in the article I wrote about how to do this same task in TSQL. The script generated via PoSh adds the events in alphabetical order. I have also seen on some cases where the predicate is not in the exact order that was originally created. Predicate order is essential! Event order may not be an essential thing for anything other than Source Control or for the demanding OCD DBA. Your mileage may vary.

Final Thoughts

Bringing PoSh to the world of XEvents can open up your eyes to a world of better automation and DBA efficiency as you manage your enterprise. PoSh also can help us dive deeper into the world of XEvents as we will see in some upcoming articles.

Working with Extended Events will help you become a better DBA. Working with PoSh can also help you in many various tasks to become a better DBA. Combine the two and you just might have a super weapon.

Interested in exploring the vast world of Extended Events? Check these out! The library of articles is pretty large and continues to grow.

Unicode, Emojis and Databases Oh My!

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Christmas, emoji, SQL Administration, SQL Internals, unicode

Comments: No Comments

Published on: January 5, 2020

Over the past several (or pervious if you have been following along) articles, I have explored the use of special characters in certain database scenarios. Using these special characters brings certain fun and flare to your hum drum naming schemes. In addition to the fun, there comes a little bit of extra peculiarities to ensure everything works as expected.

While figuring out some examples, I found myself searching for good reliable sources constantly. I also found myself trying to find various emojis that would work well to represent one idea or another. Sometimes, the effort paid off, and sometimes it seemed frivolous.

If only there was an easy comprehensive source to search through all available unicode characters for SQL Server. Then a thought occurred to me. Why not create my own database with these characters?

Laying the Groundwork

After determining that a database would work best locally to make it easier to research these characters, I quickly determined that I would need to figure out how to write a powershell script so I could scrape a list of characters from a website. That should be easy, right?

With a sample such as that, I just need to figure out how to pull the image for each character and the unicode value of each. Next task to figure out is where can i find some decent powershell examples on the web that would do a similar task.

In addition to figuring out how to do the powershell end of this, there is the database side of things. What should the table design be for this kind of data? Which values and attributes should be stored? Am I overthinking this? Is this too much work?

Well, this is indeed far too much work. Especially given this awesome gem I found while trying to find the exact reference link I was looking for in one of the pervious articles. You see, as it turns out Solomon Rutzky had already done all (and I really do mean all) of the heavy lifting for this when he wrote a function that will do exactly what I was looking to do – here. The function that Solomon created will return all 188,657 code points that are usable in SQL Server.

Now, instead of building out a script to import the data from somewhere else, I just need to employ the use of this fabulous script and move on with all the oodles of time I was spared.

Put a bow on it

Playing around with emojis in a database is a fun endeavor. Not only is it fun to play with for personal growth, but it does have some business advantages. We live in an era were these emojis are popping up everywhere and are even being stored long term in many databases. I view it as a great opportunity to improve your skill set and better your career for the future.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the twelfth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

*Previous has been intentionally mis-spelled throughout this article as “pervious” as an ode to a fat finger mistake in a recent article.

Creative XE Sessions – Funky Data

by Jason Brimhall

Categories: Extended Events, News, Professional, Scripts, SSC

Tags: Christmas, Extended Events, SQL Administration, SQL Internals, XE, XEvents

Comments: No Comments

Published on: January 4, 2020

In the previous article, I explored the opportunity of using special characters (or emojis) within Extended Event Sessions. Creating a session with these characters in the name is not terribly complicated. However, it is a bit more of a pain on the data review side (as mentioned in the previous article).

Just because it gets to be a little more complicated is seldom a deterrent for somebody such as myself. In fact, many times it is more of a challenge. These challenges are fun. With this particular challenge, and as I promised, we are going to see how we can solve the problems posed by having emojis within the object names (which causes pseudo funky data to be trapped in the session data).

Get the Funk Out!

As was demonstrated in the last article, when we have special characters in the database names and that data is captured in an XE Session, we end up with some smelly funky cheese type data that is fairly unpleasant to our desires. We have to do something quickly with that funky data in order to make it useful.

Unlike funky cheese that may have turned, we are not going to discard this data as being unwanted or un-useful. We still have a use for it. We just need to transform it a little bit before it becomes a little more palatable.

Let’s go back to a query we used to parse the session data from that last article. We need to modify this query to make it more capable of handling this new data experience.

Knowing that we have certain patterns that need to be recognized and altered, we can take advantage of the replace function. The first two patterns to find are: “&#” and then “;“. The next step is a little less known. What do we replace those known patterns with? Well, as it turns out, and as is shown in the above query results window, we know that we are looking for two surrogate code points (High and Low) that represent the character in question. When we do a little TSQL trickery and use NCHAR with those surrogate values, we can render the character to display in the desired format. Let’s see that in query form (it will help that explanation quite a bit).

SELECT NCHAR(55357) + NCHAR(56489);

1	SELECT NCHAR(55357) + NCHAR(56489);

The results of this query will look like the following.

Easy peasy right? Well, sort of easy. We can easily render that funky data we are seeing in the session details to something more human friendly. However, in order to display that data in human friendly format, we have to execute it in a sort. This means we have to do something a little more with the query – we have to convert some part of the query to a dynamic SQL statement. Here is how we can do that!

DECLARE @DynSQL NVARCHAR(MAX);

WITH presel AS (
SELECT
event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP],
	REPLACE(REPLACE(event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(max)'),'&#','NCHAR('),';',') + ') AS DBName
FROM(
SELECT CONVERT(XML, t2.event_data) AS event_data
 FROM (
  SELECT target_data = convert(XML, target_data)
   FROM sys.dm_xe_session_targets t
    INNER JOIN sys.dm_xe_sessions s 
        ON t.event_session_address = s.address
   WHERE t.target_name = 'event_file'
    AND s.name = N'💩') cte1
   CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent(FileTarget)
   CROSS APPLY  sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name', 'varchar(1000)'), NULL, NULL, NULL) t2)
    AS evts(event_data)
)

SELECT p.event_name,
       p.TIMESTAMP,
       LEFT(p.DBName, LEN(p.DBName) - 2) AS DBName
	INTO #xedbnames
	FROM presel p;

SELECT @DynSQL = 'SELECT p.event_name,
       p.TIMESTAMP,' + p.DBName + ' AS DBName
	   FROM #xedbnames p'
	   FROM #xedbnames p

EXECUTE (@DynSQL)
DROP TABLE #xedbnames;

DECLARE @DynSQL NVARCHAR(MAX);

WITH presel AS (

SELECT

event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,

event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP],

REPLACE(REPLACE(event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(max)'),'&#','NCHAR('),';',') + ') AS DBName

FROM(

SELECT CONVERT(XML, t2.event_data) AS event_data

FROM (

SELECT target_data = convert(XML, target_data)

FROM sys.dm_xe_session_targets t

INNER JOIN sys.dm_xe_sessions s

ON t.event_session_address = s.address

WHERE t.target_name = 'event_file'

AND s.name = N'💩') cte1

CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent(FileTarget)

CROSS APPLY sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name', 'varchar(1000)'), NULL, NULL, NULL) t2)

AS evts(event_data)

)

SELECT p.event_name,

p.TIMESTAMP,

LEFT(p.DBName, LEN(p.DBName) - 2) AS DBName

INTO #xedbnames

FROM presel p;

SELECT @DynSQL = 'SELECT p.event_name,

p.TIMESTAMP,' + p.DBName + ' AS DBName

FROM #xedbnames p'

FROM #xedbnames p

EXECUTE (@DynSQL)

DROP TABLE #xedbnames;

In this new version of the query, I have a few things going on. First, I am pattern matching and doing a replacement for those aforementioned terms. Next, I am dumping the results into a temp table and removing some extra data (unnecessary stuff due to the pattern matching and replacement). From the temp table, I then build a dynamic query to pull it all together and use that NCHAR trick to put the two surrogate values together to visually represent the character we expect. In the end, I have results that look like this.

Now, from a TSQL query, I have a visual representation of the Database Name that matches what I should be seeing within the SSMS object tree. This makes more logical sense to the person reviewing the data and is far easier to correlate to the correct database.

What about the histogram target that was demonstrated in that other article? Let’s look at the changes for that one as well.

DECLARE @DynSQL NVARCHAR(MAX);

WITH presel AS (
SELECT 
    REPLACE(REPLACE(n.value('(value)[1]', 'nvarchar(60)'),'&#','NCHAR('),';',') + ') AS database_name,
    n.value('(@count)[1]', 'bigint') AS ghost_count
FROM
(SELECT CAST(target_data as XML) target_data
 FROM sys.dm_xe_sessions AS s 
 JOIN sys.dm_xe_session_targets t
     ON s.address = t.event_session_address
 WHERE s.name = N'👻'
  AND t.target_name = 'histogram' ) as tab
CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)
)


SELECT p.ghost_count,
       LEFT(p.database_name, LEN(p.database_name) - 2) AS DBName
	INTO #xedbnames
	FROM presel p;

SELECT @DynSQL = 'SELECT p.ghost_count,
       ' + p.DBName + ' AS DBName
	   FROM #xedbnames p'
	   FROM #xedbnames p

EXECUTE (@DynSQL)
DROP TABLE #xedbnames;
GO

DECLARE @DynSQL NVARCHAR(MAX);

WITH presel AS (

SELECT

REPLACE(REPLACE(n.value('(value)[1]', 'nvarchar(60)'),'&#','NCHAR('),';',') + ') AS database_name,

n.value('(@count)[1]', 'bigint') AS ghost_count

FROM

(SELECT CAST(target_data as XML) target_data

FROM sys.dm_xe_sessions AS s

JOIN sys.dm_xe_session_targets t

ON s.address = t.event_session_address

WHERE s.name = N'👻'

AND t.target_name = 'histogram' ) as tab

CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)

)

SELECT p.ghost_count,

LEFT(p.database_name, LEN(p.database_name) - 2) AS DBName

INTO #xedbnames

FROM presel p;

SELECT @DynSQL = 'SELECT p.ghost_count,

' + p.DBName + ' AS DBName

FROM #xedbnames p'

FROM #xedbnames p

EXECUTE (@DynSQL)

DROP TABLE #xedbnames;

The results are now similar to those we fetched for the event_file target (similar in display format). Again, this makes it easier to digest for us humans.

A little more effort on the preparation end with the TSQL queries, makes for an easier time going forward on the data review end of these XEvent Sessions.

Put a bow on it

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the eleventh article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

Creative Extended Event Sessions

by Jason Brimhall

Categories: Extended Events, News, Professional, Scripts, SSC

Tags: Christmas, Extended Events, SQL Administration, SQL Internals, XE, XEvents

Comments: 5 Comments

Published on: January 3, 2020

In the previous article, I showed how to take advantage of collations to add more unique and interesting names to databases. In that article, I alluded to the better collation to use but failed to be very explicit about it.

As Solomon Rutzky pleads in many articles, the best default collation to use is not any collation that starts with SQL, but rather the collation one should use is Latin1_General_100_CI_AS_SC. I agree. Let’s not use the old default collations anymore and start using more modern collations – even if Microsoft does not change the default, you should change your defaults in your builds to represent a better more modern collation!

Fun with Sessions

Let’s suppose I strongly suspect I am having an issue with the ghost cleanup process for a specific database called ‘👻’. I want to prove whether the suspicions are accurate or not so I resort to an old tried and true XE session to validate. Here is that session.

USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name=N'👻')
    DROP EVENT SESSION [👻] 
    ON SERVER;
GO
CREATE EVENT SESSION [👻] ON SERVER
ADD EVENT sqlserver.ghost_cleanup (
	ACTION ( sqlserver.database_name, sqlserver.client_hostname,
	sqlserver.client_app_name, sqlserver.plan_handle, sqlserver.sql_text,
	sqlserver.query_hash, sqlserver.session_id )
	WHERE ( [database_name] = N'👻') 
	AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' )
ADD TARGET package0.histogram ( SET filtering_event_name = N'sqlserver.ghost_cleanup'
									, source = 'database_name' )
WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS
		, TRACK_CAUSALITY = ON );


ALTER EVENT SESSION [👻] 
ON SERVER 
STATE = START;
GO

USE master;

-- Create the Event Session

IF EXISTS(SELECT *

FROM sys.server_event_sessions

WHERE name=N'👻')

DROP EVENT SESSION [👻]

ON SERVER;

CREATE EVENT SESSION [👻] ON SERVER

ADD EVENT sqlserver.ghost_cleanup (

ACTION ( sqlserver.database_name, sqlserver.client_hostname,

sqlserver.client_app_name, sqlserver.plan_handle, sqlserver.sql_text,

sqlserver.query_hash, sqlserver.session_id )

WHERE ( [database_name] = N'👻')

AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' )

ADD TARGET package0.histogram ( SET filtering_event_name = N'sqlserver.ghost_cleanup'

, source = 'database_name' )

WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS

, TRACK_CAUSALITY = ON );

ALTER EVENT SESSION [👻]

ON SERVER

STATE = START;

Now, let’s try to validate my suspicions.

Use [👻];
Go

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
IF SCHEMA_ID('Halloween') IS NULL EXECUTE ('CREATE SCHEMA Halloween');
GO

IF OBJECT_ID('Halloween.Ghosts','U') IS NOT NULL
BEGIN
DROP TABLE Halloween.Ghosts
END
GO


DECLARE @BeginDate DATE = '2014-10-01'
		,@EndDate DATE = '2014-10-31'

 SELECT TOP 1000000
        Pinky       = IDENTITY(INT,1,1),
        Blinky		= 'GHOST ITEM ' + CONVERT(VARCHAR(20),ISNULL('', 0)),

        CandyMan	= ABS(CHECKSUM(NEWID()))%50000+1,
        Sadako		= CHAR(ABS(CHECKSUM(NEWID()))%26+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%26+65),
        Slimer		= DATEADD(DAY,RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @EndDate,@BeginDate) ), @EndDate),
        Poltergeist	= RIGHT(NEWID(),12),
        MalcomCrowe	= CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
        TheBlob		= REPLICATE('Hey, Dracula!! Why do you say blah, blah blah?',1000)
   INTO Halloween.Ghosts
   FROM master.dbo.sysColumns t1,
        master.dbo.sysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

/* let's get our Blinky updated properly */
Update hg
	Set Blinky = 'GHOST ITEM ' + CONVERT(VARCHAR(20),ISNULL(Pinky, 0))
	From Halloween.Ghosts hg;

--===== A table is not properly formed unless a Primary Key has been assigned
     -- Takes about 1 second to execute.
  ALTER TABLE Halloween.Ghosts
        ADD PRIMARY KEY CLUSTERED (Pinky)

/* additional index to show the ghosts */

Create NonClustered Index IX_GhostPinky on Halloween.Ghosts (Pinky)
/* tweak the ghost cleanup with an undocumented TF 
Required for DBCC Page results to show which pages have a GHOST entry
Don't try this at home - we are what you call experts (besides this is only a lab box!!) */
--DBCC TRACEON (661, -1)
--GO

/* Somebody made a mistake and thought Halloween was Oct 30 instead of Oct 31 
we need to kill those records
*/

Delete top(666)
	From Halloween.Ghosts
	WHERE Slimer = '10/30/2014';

Use [👻];

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF SCHEMA_ID('Halloween') IS NULL EXECUTE ('CREATE SCHEMA Halloween');

IF OBJECT_ID('Halloween.Ghosts','U') IS NOT NULL

BEGIN

DROP TABLE Halloween.Ghosts

END

DECLARE @BeginDate DATE = '2014-10-01'

,@EndDate DATE = '2014-10-31'

SELECT TOP 1000000

Pinky = IDENTITY(INT,1,1),

Blinky = 'GHOST ITEM ' + CONVERT(VARCHAR(20),ISNULL('', 0)),

CandyMan = ABS(CHECKSUM(NEWID()))%50000+1,

Sadako = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

Slimer = DATEADD(DAY,RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @EndDate,@BeginDate) ), @EndDate),

Poltergeist = RIGHT(NEWID(),12),

MalcomCrowe = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

TheBlob = REPLICATE('Hey, Dracula!! Why do you say blah, blah blah?',1000)

INTO Halloween.Ghosts

FROM master.dbo.sysColumns t1,

master.dbo.sysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

/* let's get our Blinky updated properly */

Update hg

Set Blinky = 'GHOST ITEM ' + CONVERT(VARCHAR(20),ISNULL(Pinky, 0))

From Halloween.Ghosts hg;

--===== A table is not properly formed unless a Primary Key has been assigned

-- Takes about 1 second to execute.

ALTER TABLE Halloween.Ghosts

ADD PRIMARY KEY CLUSTERED (Pinky)

/* additional index to show the ghosts */

Create NonClustered Index IX_GhostPinky on Halloween.Ghosts (Pinky)

/* tweak the ghost cleanup with an undocumented TF

Required for DBCC Page results to show which pages have a GHOST entry

Don't try this at home - we are what you call experts (besides this is only a lab box!!) */

--DBCC TRACEON (661, -1)

--GO

/* Somebody made a mistake and thought Halloween was Oct 30 instead of Oct 31

we need to kill those records

Delete top(666)

From Halloween.Ghosts

WHERE Slimer = '10/30/2014';

The best part is yet to come. With the event session running, I need to execute the preceding query. The query may take several minutes to complete – don’t worry yet. After the query completes, it is time to check the event session data to try and confirm the suspicions.

SELECT 
    n.value('(value)[1]', 'nvarchar(60)') AS database_name,
    n.value('(@count)[1]', 'bigint') AS ghost_count
FROM
(SELECT CAST(target_data as XML) target_data
 FROM sys.dm_xe_sessions AS s 
 JOIN sys.dm_xe_session_targets t
     ON s.address = t.event_session_address
 WHERE s.name = N'👻'
  AND t.target_name = 'histogram' ) as tab
CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)
;

SELECT

n.value('(value)[1]', 'nvarchar(60)') AS database_name,

n.value('(@count)[1]', 'bigint') AS ghost_count

FROM

(SELECT CAST(target_data as XML) target_data

FROM sys.dm_xe_sessions AS s

JOIN sys.dm_xe_session_targets t

ON s.address = t.event_session_address

WHERE s.name = N'👻'

AND t.target_name = 'histogram' ) as tab

CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)

;

This is where things start to get a little interesting! If I try to open the session data to view it from the GUI, I will see something like the following.

Well, that is not very useful! Let’s try it from a TSQL query instead!

Well, that seems to be a little better. At least I get some data back at this point. Sadly, the database name is poorly displayed. That said, the database name that is displayed is accurate – just not very useful. As it turns out, XE is lagging a bit behind in the ability to display the characters that require certain code points (seemingly any that require multiple code points or above a certain range). I have not yet tested the full limitation, just understand that there is a limitation.

What if I tried a different target such as an event_file target?

USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT * 
          FROM sys.server_event_sessions 
          WHERE name=N'💩')
    DROP EVENT SESSION [💩] 
    ON SERVER;
GO
CREATE EVENT SESSION [💩] ON SERVER
ADD EVENT sqlserver.ghost_cleanup (
	ACTION ( sqlserver.database_name, sqlserver.client_hostname,
	sqlserver.client_app_name, sqlserver.plan_handle, sqlserver.sql_text,
	sqlserver.query_hash, sqlserver.session_id )
	WHERE ( [database_name] = N'💩') 
	AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' )
ADD TARGET package0.event_file
			(SET filename = N'C:\Database\XE\💩.xel'
						 )
WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS
		, TRACK_CAUSALITY = ON );


ALTER EVENT SESSION [💩] 
ON SERVER 
STATE = START;
GO

USE master;

-- Create the Event Session

IF EXISTS(SELECT *

FROM sys.server_event_sessions

WHERE name=N'💩')

DROP EVENT SESSION [💩]

ON SERVER;

CREATE EVENT SESSION [💩] ON SERVER

ADD EVENT sqlserver.ghost_cleanup (

ACTION ( sqlserver.database_name, sqlserver.client_hostname,

sqlserver.client_app_name, sqlserver.plan_handle, sqlserver.sql_text,

sqlserver.query_hash, sqlserver.session_id )

WHERE ( [database_name] = N'💩')

AND sqlserver.client_app_name <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' )

ADD TARGET package0.event_file

(SET filename = N'C:\Database\XE\💩.xel'

)

WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS

, TRACK_CAUSALITY = ON );

ALTER EVENT SESSION [💩]

ON SERVER

STATE = START;

The event session will start successfully. In addition, I can confirm that the event file is created on disk.

Then I will rerun the experiment to test the ghost_cleanup process against the 💩 database. If I query the event session with TSQL, I will see something like this.

If I look at the event session from within the GUI, I will see something slightly more usable.

Peeking at the file data (instead of displaying it in tabular format) from tsql commands, I will see something like this.

Stay tuned to see how we can resolve this issue where the 💩 and 👻 are not being translated properly in these TSQL queries for these sessions.

Put a bow on it

A picture is worth a thousand words, right? Sometimes, a picture for an event session just may be able to say it better than 50-60 letters used to try and describe the session. Maybe you just want to try it out for fun. Either way, the use of these characters in an event session can be a little difficult when looking to review the data.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the tenth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

Creative Database Naming

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Christmas, collation, Professional, SQL Administration, SQL Internals

Comments: 1 Comment

Published on: January 2, 2020

Every now and again, we as DBAs need to get away from the daily grind of the hum drum tasks. We need to find a way to have a little fun (the kind that would be non-harmful and lacking in mal-intent).

Sometimes, this fun can be had through learning opportunities. Sometimes, we can have a little fun through diving deep into SQL Server in some way. At least I know that proves to be enlightening and enjoyable for myself. Sometimes, it is just fun for the sake of a good laugh. What if we could find an opportunity to have a little fun that incorporates learning and a laugh or two? BINGO!

Enter the opportunity to play with emojis, collations, and database (or object) names.

Fun with Characters

Let’s figure we have a requirement to create a database with sensitive data. Due to the sensitivity of the data, it is classified confidential (for your eyes only, don’t talk about it and plug your ears if somebody starts talking about it). This is so sensitive that an apt name for the database could be anything like 🙈 or 🙉 or 🙊. Being smart, you know there are two more databases coming down the line so you only want to pick one of those for the name and not all three (though all three could make sense for a single database name).

Being on top of your game, you prep for all three databases in advance. Here is the script to create the three new databases.

CREATE DATABASE [🙈];
GO
CREATE DATABASE [🙉];
GO
CREATE DATABASE [🙊];
GO

CREATE DATABASE [🙈];

CREATE DATABASE [🙉];

CREATE DATABASE [🙊];

In executing the script, you receive the following results:

Msg 1801, Level 16, State 3, Line 15
Database ‘🙈’ already exists. Choose a different database name.
Msg 1801, Level 16, State 3, Line 17
Database ‘🙉’ already exists. Choose a different database name.
Msg 1801, Level 16, State 3, Line 19
Database ‘🙊’ already exists. Choose a different database name.

You look up and down through your SSMS window and even query sys.databases to ensure those databases are factually not present. You are able to confirm that none or currently present on the server yet the script continues to fail. How could this be? Clearly, the images are each very different.

From the database review, you know there is only one other database with an emoji style name – 👺. What could it be? Well, as it turns out, it is the collation that was chosen for the SQL Server install. The chosen collation doesn’t support a large enough range of code points so most emojis end up getting mapped to the same thing when stored in SQL Server (topic for much larger article and covered very completely by Solomon Rutzky on his blog – – here). The short of the issue is the use of supplementary characters and splitting the code point into 2 surrogate codes. For most emojis, this surrogate system results in the “high” surrogate key being the same. Let’s take a look at it.

SELECT UNICODE(N'☯' COLLATE Latin1_General_100_CI_AS_SC) AS '☯',
       UNICODE(N'🙉' COLLATE Latin1_General_100_CI_AS_SC) AS '🙉',
       UNICODE(N'🙊' COLLATE Latin1_General_100_CI_AS_SC) AS '🙊',
       UNICODE(N'🙈' COLLATE Latin1_General_100_CI_AS_SC) AS '🙈',
	   UNICODE(N'👺' COLLATE Latin1_General_100_CI_AS_SC) AS '👺',
       'Latin1_General_100_CI_AS_SC' AS Collation
UNION
SELECT UNICODE(N'☯' COLLATE DATABASE_DEFAULT),
       UNICODE(N'🙉' COLLATE DATABASE_DEFAULT),
       UNICODE(N'🙊' COLLATE DATABASE_DEFAULT),
       UNICODE(N'🙈' COLLATE DATABASE_DEFAULT),
       UNICODE(N'👺' COLLATE DATABASE_DEFAULT),
       DATABASEPROPERTYEX(DB_NAME(), 'Collation');

SELECT UNICODE(N'☯' COLLATE Latin1_General_100_CI_AS_SC) AS '☯',

UNICODE(N'🙉' COLLATE Latin1_General_100_CI_AS_SC) AS '🙉',

UNICODE(N'🙊' COLLATE Latin1_General_100_CI_AS_SC) AS '🙊',

UNICODE(N'🙈' COLLATE Latin1_General_100_CI_AS_SC) AS '🙈',

UNICODE(N'👺' COLLATE Latin1_General_100_CI_AS_SC) AS '👺',

'Latin1_General_100_CI_AS_SC' AS Collation

UNION

SELECT UNICODE(N'☯' COLLATE DATABASE_DEFAULT),

UNICODE(N'🙉' COLLATE DATABASE_DEFAULT),

UNICODE(N'🙊' COLLATE DATABASE_DEFAULT),

UNICODE(N'🙈' COLLATE DATABASE_DEFAULT),

UNICODE(N'👺' COLLATE DATABASE_DEFAULT),

DATABASEPROPERTYEX(DB_NAME(), 'Collation');

Notice how each of the monkeys plus the devil mask all translate to the same code point when using the SQL_Latin1_General_CP1_CI_AS (default collation)? This is the root of the problem. If we change to a different server with a better collation (e.g. Latin1_General_100_CI_AS_SC) then we will see vastly different results.

First, let’s validate that the collation on this other instance truly is displaying the correct code points (and it is) instead of just the high surrogate key.

Then let’s go ahead and create each of those three high security databases (seenoevil, speaknoevil, and hearnoevil). Et voila! We now have each of those new databases created and they are indeed unique as we desired (as you can see in the preceding image).

If you want to learn all of the hard core nitty gritty behind this, I recommend reading the blog by Solomon Rutzky – here. Solomon knows his 💩 about the collations and can answer or figure out how to answer just about anything you could throw at him about the topic. His blog is a WEALTH of in-depth knowledge with all sorts of tests and proofs. Check it out!

Put a bow on it

Working as a DBA and creating databases doesn’t have to be the same old hum drum day in and day out. This article showed how to have a little fun while creating databases and database objects. At the same time, I showed how the collation of your server can impact some of your naming schemes.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the ninth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

*Post Mortem from Solomon who is the collation genius

And, that brings us to the next fun fact: being able to use more than 1 emoji for database names has nothing to do with whether or not SQL Server sees a surrogate pair as being a single Supplementary Character or merely two surrogate code points. Using SCA collations only affects the behavior of the built-in functions, even though the official documentation says that they help in sorting and comparison (the doc is wrong and I haven’t had time to submit a correction). Being able to name more than 1 database one or more emoji characters is entirely based on how the characters compare to each other, and that is a function of sort weights, which are managed separately. Sort weights do map to code points, but they can change in value depending on the locale/culture being used, as well as the sensitivities selected (i.e. case, accent, etc). The problem you run into with at least most of the version 80 collations (i.e. all Windows collations without a version number in their names, and all SQL Server collations) is simply that they never defined any sort weights for either supplementary code points, or even the surrogate code points used to create surrogate pairs. And with no sort weights defined, their sort value is always 0, and since they are all “0”, they all equate to not only each other, but to anything else with a sort weight of “0”, even an empty string.

Starting with the version 90 collations (even without the “_SC” flag in the name), sort weights were added to the surrogate code points (not to supplementary characters) so that they could at least be distinguished from each other in sorting and comparison operations. And this is why:

you were not able to create the 3 monkey DBs using SQL_Latin1_General_CP1_CI_AS
you were able to create the 3 monkey DBs using Latin1_General_100_CI_AS_SC
you can create those 3 monkey DBs using SQL_Latin1_General_CP850_BIN2, even with that being a SQL Server collation, because binary collations don’t use sort weights, but instead just go by each byte or code unit (depending on BIN or BIN2, respectively)

Cannot Open Backup Device

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Backup, Christmas, Database Maintenance, Professional, Restore, SQL Administration, SQL Internals

Comments: 1 Comment

Published on: January 1, 2020

Your success as a DBA is directly relational to your ability to ensure proper Database backups are occurring. There is a whole slew of considerations that go into effect when discussing what a proper Database backup actually is. To drill it down to its most basic definition, a proper database backup is one that fulfills business requirements for recovery, retention, and has been routinely tested to ensure the backup can be restored.

Remember, a backup that cannot be restored is factually not a backup. Why? Well, going back to the definition of a backup, it is a fail-safe mechanism to be implemented in the event of a failure. If it cannot be implemented (in this case restored), then it provides no value because it cannot be used.

What about when you run into a problem even getting to the point of getting a successful backup operation? For instance, this error can be a bit troubling and may just cause you to lose plenty of time troubleshooting it.

Msg 3201, Level 16, State 1, Line 3
Cannot open backup device ‘\\SomeServer\C$\MSSQL14\MSSQL\DATA\Full_Backup2.bak’. Operating system error 1326(The user name or password is incorrect.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

Sure, the error seems to report plenty of adequate information, but you may be surprised at the pain this particular error can cause.

Backup Device Permissions

If you search long enough you will come across numerous articles or forum posts that all pretty much have the same direction. They all point to the problem being a permissions issue. There are a few concerns with the permissions stigma however. In a recent issue for a client, we could verify that permissions were indeed not at fault. To ensure the permissions were not at fault, we granted local admin in the OS as well as sysadmin within SQL Server for the service account. In addition, we also tried a proxy account with full blown access in Windows and in SQL.

To take it a step further, we also ensured that the special permissions for service accounts were also in effect. Those permissions are:

Permission to bypass traverse checking (SeChangeNotifyPrivilege)
Permission to replace a process-level token (SeAssignPrimaryTokenPrivilege)
Permission to adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Permission to access this computer from the network (SeNetworkLogonRight)

None of this seemed to have any effect when trying to perform a database backup in SQLServer. In our case, we also ensured that the service account had full blown access to the folders as well.

Following the traditional troubleshooting measures, I created a virtual backup device as well only to get the same error. I tried to use the UNC from a remote server and it worked perfectly fine for all accounts in question. What could be going on? Then, I figured why not try the UNC from the server itself. Finally, a clue! Suddenly I was getting prompted for my credentials but my credentials would not work no matter what. Just for giggles, I verified my account was not locked out, and indeed my account was working just fine.

As it turns out, sometimes the problem is not really a permissions issue but it is a veiled security issue. You see, if I use the actual server name instead of the CNAME in all cases above, suddenly everything worked. The issue turns out to be more along the lines of a Windows issue and is documented here (still valid on Windows 2016).

Here is the basic SQL backup test script that led us down the path to the CNAME being the issue.

USE master;
GO
BACKUP DATABASE MAS_SYSTEM
	TO
		DISK = '\\ServerName.yourdomain.com\c$\MSSQL14\MSSQL\DATA\Full_Backup2.bak';
--Preceding Succeeds

BACKUP DATABASE MAS_SYSTEM
	TO
		DISK = '\\CName.yourdomain.com\c$\MSSQL14\MSSQL\DATA\Full_Backup2.bak';
--Preceding Fails with Error 1326

USE [master];
GO

EXEC master.dbo.sp_addumpdevice
	@devtype = N'disk'
  , @logicalname = N'DB_BAK'
  , @physicalname = N'\\CName.yourdomain.com\c$\MSSQL14\MSSQL\DATA\Full_Backup.bak';
GO

BACKUP DATABASE YourDB TO DB_BAK;
GO
--Preceding fails with error 1326

EXEC master.dbo.sp_dropdevice @logicalname = N'DB_BAK';
GO

EXEC master.dbo.sp_addumpdevice
	@devtype = N'disk'
  , @logicalname = N'DB_BAK'
  , @physicalname = N'\\ServerName.yourdomain.com\c$\MSSQL14\MSSQL\DATA\Full_Backup.bak';
GO

BACKUP DATABASE YourDB TO DB_BAK;
GO
--Preceding Succeeds

USE master;

BACKUP DATABASE MAS_SYSTEM

DISK = '\\ServerName.yourdomain.com\c$\MSSQL14\MSSQL\DATA\Full_Backup2.bak';

--Preceding Succeeds

BACKUP DATABASE MAS_SYSTEM

DISK = '\\CName.yourdomain.com\c$\MSSQL14\MSSQL\DATA\Full_Backup2.bak';

--Preceding Fails with Error 1326

USE [master];

EXEC master.dbo.sp_addumpdevice

@devtype = N'disk'

, @logicalname = N'DB_BAK'

, @physicalname = N'\\CName.yourdomain.com\c$\MSSQL14\MSSQL\DATA\Full_Backup.bak';

BACKUP DATABASE YourDB TO DB_BAK;

--Preceding fails with error 1326

EXEC master.dbo.sp_dropdevice @logicalname = N'DB_BAK';

EXEC master.dbo.sp_addumpdevice

@devtype = N'disk'

, @logicalname = N'DB_BAK'

, @physicalname = N'\\ServerName.yourdomain.com\c$\MSSQL14\MSSQL\DATA\Full_Backup.bak';

BACKUP DATABASE YourDB TO DB_BAK;

--Preceding Succeeds

Put a bow on it

When running into error 1326, it makes plenty of sense to try to create a backup dump device (only as a testing exercise) as well as test the connectivity to the UNC path from the local server instead of a remote server. In my case, the problem was the local server was blocking access to a CName version of the server.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the eighth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

Negative Port Numbers

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Christmas, Professional, script, SQL Administration, SQL Internals, SQLFamily

Comments: No Comments

Published on: December 31, 2019

As a DBA, one of the more critical tasks is to confirm proper setup of a SQL Server. In order to confirm proper setup, I like to validate tcp/ip and port settings. I do this not just through the SQL Server Configuration Manager, but also via query through Management Studio.

Occasionally, when validating the port configurations through this method, you will encounter a fun little puzzle. If you are paying attention to the results, you just may see something that could cause you to scratch your noggin.

Of course, it all depends on the query method you choose to use to perform your validations. There are two easy access methods to use to query for the tcp/ip and port settings. Each may produce slightly different results. Let’s explore these methods.

What’s in a Port?

First let’s go with the easiest of the queries. We will query sys.dm_exec_connections with something that looks like the following.

SELECT DISTINCT
       local_net_address,
       local_tcp_port
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL;
GO

SELECT DISTINCT

local_net_address,

local_tcp_port

FROM sys.dm_exec_connections

WHERE local_net_address IS NOT NULL;

Executing this query via sqlcmd on my server in question, I might see something like the following in the results.

As you can surmise from the image, the instance I am setting up and validating happens to have multiple IP addresses and a more complex setup because I have it listening on different ports depending on the IP address source. We will focus on the non-default IP address for the purposes of this article. In the local_tcp_port field, you can see that the ports are being reported as expected. And for the sake of simplicity, these ports are correct.

Let’s now divert our attention to the alternative option – CONNECTIONPROPERTY().

SELECT  @@SERVERNAME,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port
GO

SELECT @@SERVERNAME,

CONNECTIONPROPERTY('net_transport') AS net_transport,

CONNECTIONPROPERTY('protocol_type') AS protocol_type,

CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,

CONNECTIONPROPERTY('local_net_address') AS local_net_address,

CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port

In the results window, I see something like this:

Take quick note of the port number I have circled in red. This doesn’t match the original query at all. In fact, it doesn’t come anywhere close to the actual port number. In addition, the port number shown here is a negative value. Obviously a negative port is not correct as TCP/IP ports only range from 0-65535. So what is happening here? Let’s change this query just a little bit and combine the two sources.

:CONNECT SAEMASDB01\MAS
GO
SELECT  @@SERVERNAME,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS MaskedTCPPort,
   ec.local_tcp_port AS ExecConnTCPPort,
   ec.local_tcp_port - CONVERT(BIGINT,CONNECTIONPROPERTY('local_tcp_port')) AS PortMask,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address
   FROM sys.dm_exec_connections ec
   WHERE ec.local_net_address = CONNECTIONPROPERTY('local_net_address');
GO

:CONNECT SAEMASDB01\MAS

SELECT @@SERVERNAME,

CONNECTIONPROPERTY('net_transport') AS net_transport,

CONNECTIONPROPERTY('protocol_type') AS protocol_type,

CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,

CONNECTIONPROPERTY('local_net_address') AS local_net_address,

CONNECTIONPROPERTY('local_tcp_port') AS MaskedTCPPort,

ec.local_tcp_port AS ExecConnTCPPort,

ec.local_tcp_port - CONVERT(BIGINT,CONNECTIONPROPERTY('local_tcp_port')) AS PortMask,

CONNECTIONPROPERTY('client_net_address') AS client_net_address

FROM sys.dm_exec_connections ec

WHERE ec.local_net_address = CONNECTIONPROPERTY('local_net_address');

The results of this query give me the following.

OK, cool. So we can see that some sort of masking has been created to conceal the port number when it is a “dynamic” port. What is the significance of 65536 though? Well, it just so happens that 16 bits is 65536. This would give us port values of 0-65535 with port 0 being reserved and unusable. Thus, we simply do a little math to figure out what the actual port is by adding 65536 to the negative port value. That is shown in the following code snip.

SELECT  @@SERVERNAME,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS MaskedTCPPort,
   CONVERT(BIGINT,CONNECTIONPROPERTY('local_tcp_port')) + 65536 AS ActualTCPPort, -- max port 65535 plus port 0 = 65536 to get the true port number
   ec.local_tcp_port AS ExecConnTCPPort,
   ec.local_tcp_port - CONVERT(BIGINT,CONNECTIONPROPERTY('local_tcp_port')) AS PortMask,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address
   FROM sys.dm_exec_connections ec
   WHERE ec.local_net_address = CONNECTIONPROPERTY('local_net_address');
GO

SELECT @@SERVERNAME,

CONNECTIONPROPERTY('net_transport') AS net_transport,

CONNECTIONPROPERTY('protocol_type') AS protocol_type,

CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,

CONNECTIONPROPERTY('local_net_address') AS local_net_address,

CONNECTIONPROPERTY('local_tcp_port') AS MaskedTCPPort,

CONVERT(BIGINT,CONNECTIONPROPERTY('local_tcp_port')) + 65536 AS ActualTCPPort, -- max port 65535 plus port 0 = 65536 to get the true port number

ec.local_tcp_port AS ExecConnTCPPort,

ec.local_tcp_port - CONVERT(BIGINT,CONNECTIONPROPERTY('local_tcp_port')) AS PortMask,

CONNECTIONPROPERTY('client_net_address') AS client_net_address

FROM sys.dm_exec_connections ec

WHERE ec.local_net_address = CONNECTIONPROPERTY('local_net_address');

When calculating the value between sys.dm_exec_connections and ConnectionProperty(), ensure you perform a conversion on the value from ConnectionProperty(). Despite documentation showing that it is an integer value on the port, it is not. The data type for the port value from ConnectionProperty() is actual sql_variant and an implicit conversion won’t work there.

Put a bow on it

Validating your server setup is an integral component of your duties as a SQL Server DBA. When performing those validations, it is possible to run into an intriguing difference in reported port values. That difference of value is easily rectified if you understand that the ConnectionProperty function is doing a bit of a port mask by subtracting 65536 from the actual port number value.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the seventh article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

Get a List of Files with Data

by Jason Brimhall

Categories: News, Professional, Scripts, SSC

Tags: Christmas, Powershell, Professional, script, SQLFamily

Comments: No Comments

Published on: December 30, 2019

Suppose you have a directory on your server that houses over 300,000 csv files. These files are automatically created by an automated process related to your SQL Server operations and are automatically created when the process runs. The process runs on a schedule every 15 minutes.

Now suppose that you have been tasked with figuring out which of the files have data of value in them and which can be ignored. The files that can be ignored will all be either 0k or 1k in size. However, some of the 1k files can also be ignored because they only contain a header. Every csv file should have a header. So, we must figure out a way to filter out those files that are 1k or less and only have a header row.

Filtering out these files with this criteria will allow for us to find files that have useful data in them. Now why do you need to figure out which ones have useful data? Let’s just say that maybe some of the files were created with some bad data in them and you need to figure out which files may have bad data so those particular files can be regenerated. As luck would have it, the automated process does not have any sort of logging and does the bare minimum to create the files in the first place. (Sometimes you just inherit a flawed process where inadequate thought was given.)

Finding the Files

The hard part has been done at this point. At least you know some of the attributes that will help distinguish wanted files from the unwanted files. Sorting through these files by hand could be rather bothersome and cumbersome. The trick here is to find a way to search all of these files quickly and filter easily the bad from the good. Sounds like an opportunity for another automation or script. I have just the powershell script for that.

$numdays = 30
foreach ($File in Get-ChildItem 'C:\YourDirectoryHere\*' -Include *.csv | ? { $_.LastWriteTime -gt (Get-Date).AddDays(-$numdays)} )
{
    $fc = Get-Content $File -Delimiter "`r`n";
#    if ($fc.Length -gt 1)
 #   {
        $flen = $fc | Measure-Object -Line 
        if ($flen.Lines -gt 1)
        {
        $File.Name
        }
  #  }
}

$numdays = 30

foreach ($File in Get-ChildItem 'C:\YourDirectoryHere\*' -Include *.csv | ? { $_.LastWriteTime -gt (Get-Date).AddDays(-$numdays)} )

{

$fc = Get-Content $File -Delimiter "`r`n";

# if ($fc.Length -gt 1)

# {

$flen = $fc | Measure-Object -Line

if ($flen.Lines -gt 1)

{

$File.Name

}

# }

}

With this script, I have a couple of things to help me find files of interest quickly. First, I have a filter in place to allow me to search a specific number of days worth of files. Second, I have a filter in place to ensure it only returns file names where there is more than just a header row present.

Now, instead of spending hours perusing files trying to find something of value, I have reduced my time spent to just a mere fraction of that. Next steps after this would be to go and add some additional logging and better robustness to the initial process to reduce the chance of bad data being put into the files in the first place.

Put a bow on it

Automated processes are fantastic. We as DBAs strive to have more tasks automated than not. That said, automation without planning is just creating a time sink later on in the job. At some point, you may need to employ some powershell script similar to this in order to find where your automated process has gone wrong (if you did not plan that process well enough from the start).

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the sixth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

What is the Merit of the Job?

by Jason Brimhall

Categories: News, Professional, SSC

Tags: Professional, SQLFamily

Comments: No Comments

Published on: December 29, 2019

For the past several months I have been pondering over the topic of meritocracy. Meritocracy seems to be a hot button topic that goes between hot and cold cycles in various circles and climates for whatever reason. There seem to be quite a few political fires that can be easily stoked with this topic. For me, I haven’t been thinking of it from a political standpoint. For me, I have been pondering how the rat race of meritocracy has affected me unwittingly.

What is meritocracy? Well, there are a great many ways to describe this idea, but let’s just stick to the base definition.

Meritocracy: a form of social system in which power goes to those with superior intellects or ability.

I have no doubt that there is an absolute need for meritocracy in certain situations. For example, as a sports coach, you want your best athletes on the field in order to compete. The best athletes are determined through a series of assessments and monitored efforts and performances. They have earned their way into that position based on merit. Let’s be honest here, as spectators we also want to watch the best athletes and not the scrubs else we wouldn’t pay money to attend. It is a disservice in certain situations to not award those with the greatest ability the merit of more playing time in an athletic competition.

Serious Flaw

When we start employing a merit system to our personal lives or to our professional goals, things can get a little dicey. One of the major attributes of a merit based system is extreme competition. I view competition as a good thing. It keeps me on my toes and progressing personally. That said, there is a hidden undesirable impact that comes from extreme competition – and that impact affects interpersonal relationships.

As IT professionals (and the running joke even in IT is that DBAs are far worse than everybody else) we are far too often introverted. We are internally driven to achieve certain levels of success. Along with that internal drive, we far too often focus on the merits of the job that affect our career status and seldom pay adequate attention to those merits that affect our personal relationships and feelings as human beings.

You see, meritocracy is a rat race. In a meritocracy we are driven for results and in business the results frequently come at the cost of human interactions and relationships. How well do you know the people around you? Have you taken much effort to try and establish a rapport with your co-workers or clients – beyond the ones that sign the checks?

This is the serious flaw in a merit based system. One tries to climb the ladder of success but at the cost of being an approachable human being. Nice people don’t need to finish last. Maybe your merit review should have a line item about building relationships of trust and determine how well you have succeeded at that requirement (did you exceed expectations?)!

Put a bow on it

Merit based systems certainly have their place in business and in life. How well we govern our personal interaction with the merit based system and how we conduct our personal growth should matter more than most other achievements in life. Interactions with people is crucial to our growth as a professional and as a human being.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the fifth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.

«page 1 of 73

SQL RNNR

Coping with Self Doubt

TSQL Tuesday

Well, What if…

Wrapping it Up

PowerShell to Backup XE Session

Wading to the Deeper End!

Getting into the Script

Caveats

Final Thoughts

Unicode, Emojis and Databases Oh My!

Laying the Groundwork

Put a bow on it

Creative XE Sessions – Funky Data

Get the Funk Out!

Put a bow on it

Creative Extended Event Sessions

Fun with Sessions

Put a bow on it

Creative Database Naming

Fun with Characters

Put a bow on it

Cannot Open Backup Device

Backup Device Permissions

Put a bow on it

Negative Port Numbers

What’s in a Port?

Put a bow on it

Get a List of Files with Data

Finding the Files

Put a bow on it

What is the Merit of the Job?

Serious Flaw

Put a bow on it

Now Reading (2)

In Queue (1)

Recently Read (16)

January 2020
M	T	W	T	F	S	S
« Dec
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31