T-SQL Tuesday #101: Essential Tools

Comments: No Comments
Published on: April 10, 2018

What are the tools you love to use? What are the tools that maybe need a little sharpening? What tools do you have that maybe you wish were the same caliber as somebody else’s tools? And lastly, which tools do you not possess that you wish you possessed?

This are maybe some of the questions that Jens Vestergaard (b | t) would like for us to examine as we take an introspective look into our tool belts this month for the 101st installment of TSQL Tuesday.

If you are interested in reading the original invite, you can find that here.

“Besides SQL Server Management Studio and Visual Studio Data Tools we all have our own set of tools that we use for everyday chores and tasks. But how do we get to know which tools are out there, if not for other professionals telling us about them? Does it have to a fully fledged with certification and all? Certainly not! If there’s some github project out there, that is helping you be double as productive, let us know about it. You can even boast about something you’ve built yourself – if you think others will benefit from using it.”

That is the invite. I am going to not adhere very closely to that invite. You see, I take a rather open ended definition to the meaning of “tools” this time around. You see, there are many general tools that just about everybody will use. But what about the tools that help to refine your abilities as a DBA?

Brief Intermission

A shout out is absolutely necessary for Adam Machanic (twitter)for picking the right blog meme that has been able to survive so long in the SQLFamily. This party has helped many people figure out fresh topics as well as enabled them to continue to learn.

Refinery

There are two tools I would like to throw out there as essential to the DBA tool belt. These are not your traditional tools by any means but they are easily some of the most essential tools you could employ in your trade craft. These tools are Google-FU and blogging.

Yes, I am taking a liberal definition to the term “tools” and I warned you of that already. These are seriously some of the most important tools anybody could acquire. These are the tools that allow you to sharpen your skills and become an overall better professional. Let’s start with google-fu.

Google-fu is the ability to employ internet searches to find answers to your current questions as well as your current problems. 15 years ago, this skill was much more difficult to acquire and frankly quite a bit more important. In the present world, algorithms are running in the background and profiling you to help you find the answer you are looking for a little more easily. You do more searches, Google learns you better and you get better more accurate results over time. This is a good thing. Every data professional should be able to employ a good Google search to find the appropriate answers to their current problems.

Blogging on the other hand also gets easier over time but for different reasons. Where Google has evolved to help you improve your google-fu, the only way for you to improve your blogging ability is through more and more practice.

Why is blogging so important? Blogging is not there just to help you become a better writer. That is a nice benefit because every data professional needs to be able to write to some extent depending on business needs, requirements, documentation etc. Blogging helps you become a better technical person.

What is often overlooked about blogging is that it requires the writer (if they truly care), to research, practice, and test what it is they happen to be writing about. Why do people do this when blogging? Well, the truth is simple. You are putting a piece of yourself out there for public consumption and people will nitpick it. You will want to be as accurate as possible with whatever you put out there for the world to see. This also becomes a bit of your resume and future employers may see it. You will want them to see your value and not something littered with mistakes.

Over time, your writing will also tend to serve as a personal knowledge base. How cool is that? You will forget the fixes for things over time. You will forget some of the cool solutions over time. That is natural. If you have it written somewhere, you will be able to find it and use it again and again.

 

TSQL2sDay150x150The Wrap

These are a couple of the tools that I highly recommend for all data professionals. Sure they are non-traditional tools, but that does not diminish their importance. I recommend you try to polish these particular tools as frequently as plausible.

Oh, and if you are interested in some other SQL Server specific tools, read this series I have published.

Syspolicy Phantom Health Records

SQL Server comes with a default SQL agent job installed (for most installations) to help manage the collection of system health data. I would dare say this job is ignored by most people and few probably even know it exists.

This topic is not new to me. You may recall a previous article I wrote entailing one type of failure and how to resolve that failure. That article can be found here.

I recently ran into a variant of the problem outline in that previous article that requires just a bit of a different approach. The errors turn out to be similar on the surface but really are different upon closer inspection.

Phantom Health Records

If you are unfamiliar with the topic, I recommend reading the previous article. Then after reading that article, maybe brush up a little bit on the SQL Agent. The failures we will be looking at are within the SQL Agent and come from the job called: syspolicy_purge_history.

For this latest bout of failure, I will start basically where I left off in the the last article. The job fails on a server and I have access to other servers of the same SQL version where the job works.

Before diving any further into this problem, let’s look at what the error is:

A job step received an error at line 1 in a PowerShell script.
The corresponding line is ‘set-executionpolicy RemoteSigned -scope process -Force’.
Correct the script and reschedule the job. The error information returned by PowerShell is:
‘Security error. ‘. Process Exit Code -1. The step failed.

Having the error in hand, and knowing that the job works elsewhere, my next logical step (again based on experience from the last article with this job) is to script the job from another server and use it to replace the job on the server where it fails. In principle this is an AWESOME idea.

 

Sadly, that idea was met with initial failure. As it turns out, the error remained exactly the same. This is good and unfortunate at the same time. Good in that I was able to confirm that the job was correctly configured with the following script in the job:

Since the step fails from SQL Server let’s see what else we can do to make it run. Let’s take that code and try it from a powershell ise. So, for giggles, let’s cram that script into powershell and see what blows up!

Now isn’t that a charming result! This result should be somewhat expected since the code I just threw into the ISE is not entirely powershell. If you look closer at the code, you will notice that it is using sqlcmd like conventions to execute a parameterized powershell script. Now, that makes perfect sense, right? So let’s clean it up to look like a standard PoSH script. We need to replace some parameters and then try again.

This will result in the following (resume reading after you scratch your head for a moment):

The key in this failure happens to be in the sqlserver. PoSH thinks we are trying to pass a drive letter when we are just trying to access the SQLServer stuff. Depending on your version of server, SQL Server, and PoSH you may need to do one of a couple different things. For this particular client/issue, this is what I had to try to get the script to work.

If you read the previous article, you may notice this command looks very much like the command that was causing the problems detailed in that previous article. Yes, we have just concluded our 180 return to where we started a few years back. Suffice it to say, this is expected to be a temporary fix until we are able to update the system to PoSH 5 and are able to install the updated sqlserver module.

As is, this script is not quite enough to make the job succeed now. To finish that off, I created a ps1 file to house this script. Then from a new step (defined as a sqlcmd step type) in the syspolicy purge job, I execute that powershell script as follows:

Tada, nuisance job failure alert is resolved and the system is functioning again.

Conclusion

I dare say the quickest resolution to this job is to probably just disable it. I have seen numerous servers with this job disabled entirely for the simple reason that it fails frequently and just creates noise alerts when it fails. Too many fixes abound for this particular job and too few resolve the failures permanently.

I would generally err on the side of fixing the job. Worst case, you learn 1000 ways of what not to do to fix it. 😉

Given this job is tightly related to the system_health black box sessions (sp_server_diagnostics and system_health xe session), I recommend fixing the job. In addition, I also recommend reading the following series about XE and some of those black box recorder sessions – here.

SQL Server Extended Availability Groups

Comments: 1 Comment
Published on: April 1, 2018

It may come as no surprise to many that Microsoft has hastened the SQL Server development cycle. Furthermore, it may be no surprise to many that Microsoft has also hastened the patch cycle for SQL Server.

If you were unaware of this, consider this as your notice that Microsoft has indeed hastened the patch cycle. Not only has the patch cycle become more rapid, the idea of Service Packs is more or less a notion of history at this point. Critical Updates (or CUs) is the new norm. This is a pretty good thing due in large part to the rapid improvements that can be made to the product due to Azure.

With all of this considered now, there is some really awesome news. A hint to this awesome news is in the preceding image and title of this post. In a recent CU for SQL Server 2017, Availability Groups and Extended Events both have seen massive upgrades. The upgrades are so big in fact that it is mind blowing. These upgrades were no small feat by any means and it took some major investment and cooperation from the likes of some well known competitors.

Upgrade the first: Availability Groups have now been extended to be able to include nodes from MySQL, PostGres and MariaDB. Frankly, I don’t understand the MariaDB move there but it’s all good. I am 100% on board with the MySQL addition and may have to work really hard to find a use case to include PostGres.

Imagine the realm of possibility this change brings!! First we got SQL Server on Linux and now we can include a predominantly Linux flavored DBMS in a SQL Server High Availability solution. LAMP engineers have got to be losing their gourds right about now over this. Microsoft is taking away every anti-MS premise that has been used in recent years and turning the world on its ears to become more global and reachable in the architecture and DBMS world.

Upgrade the second: In order to help support and troubleshoot AGs on these other platforms, we need some tools. The tools of choice happen to be in the form of Extended Events. While there is nothing quite yet in place on these other platforms to properly monitor an AG, XE is able to capture some MySQL, PostGres and MariaDB information as transmitted across the wire when these platforms are added to an AG. How COOL is that?

If you are really chomping at the bit, I recommend procuring the latest CU that was recently released. You can find that CU from this Microsoft site here.

The Wrap

I am in full support of this new direction from Microsoft. Partnering with other large platforms to provide a supremely improved overall product is very next level type of stuff and frankly quite unheard of in this ultra competitive world. It is so unheard of in fact that this was a nicely crafted April Fools joke. Happy April Fools Day!

Having mentioned Extended Events, if you are interested, I do recommend a serious read from any number of articles posted in 60 day series.

Extended Events File Initialization Failure

It should come as no surprise that I write a lot of articles about Extended Events (XE). This happens to be another article on Extended Events. Truth be told, this article is hopefully something that is more of an edge case scenario. Well, I sure hope that is the case and that it is not a common problem.

One of the recommended methods to trap payload data in an XE session is via the use of the event_file target. Sending data to a file has numerous benefits such as being able to take the trace and evaluate the trace file from a different machine (locally to that machine).

Every once in a blue moon you just may run into various issues with the event_file such as explained here or here. Though slightly different, the net effect is quite similar and should be treated with roughly the same kind of troubleshooting steps.

Configuration Error

As luck would have it, I ran into one of these rare opportunities to troubleshoot an error occurring on a client server. Truth be told, I was unfamiliar with the actual error at first. Here is that error.

Error: 25602, Severity: 17, State: 22.

The preceding error was scraped out of the SQL Server error log. Obviously a little more detail was needed because this error is far from useful without that detail. Looking a little deeper, I found some errors like this.

Msg 25602, Level 17, State 22, Line 43
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”,
encountered a configuration error during initialization. Object cannot be added to the event session.

Some very good clues are actually contained in that particular message. Some of these clues include the following: a) the term “target”, b) the term “event_file”, and c) the phrase “event session.” Ok, I get it at this point. One of my Extended Event Sessions I had put on the server and used previously was broken. But, since it had been working and I know I had fetched data from it, I found myself puzzled as to why it might be busted.

The next logical thing to do at this point was to test the various sessions that are stopped and try to figure out which one is causing the problem and see if the error is reproduced. Finally upon finding the session that is failing, I ran into the complete message.

Msg 25602, Level 17, State 22, Line 43
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”,
encountered a configuration error during initialization. Object cannot be added to the event session.
The operating system returned error 3: ‘The system cannot find the path specified.
‘ while creating the file ‘C:\Database\XEDROPME\SVRLoginAudit_0_131650006658030000.xel’.

The additional info that I needed is in bold text in the previous text. So, for some reason, there is a problem with the path for the XE trace file output. Going out to the file system to check it out, I found that the client in this case decided to delete the entire folder. How does that happen? Well, it does! When it happens, the XE traces will start to fail and you will no longer capture the intended trace data. Let’s take a look at a simulated reproduction of this issue.

First, I will create a session and then start the session, then validate the session exists and then stop the session.

And here is what I see on my test server when I validate the session exists.

Perfect so far. Now, let’s make that trace output directory disappear. For this demo, you might note that I had created the directory as “C:\Database\XEDROPME”. My intent in the name was obviously to notify the world that the folder was to be dropped.

That statement is easy enough and is performed from my test environment for those getting weary of the use of xp_cmdshell. Now, let’s try to start that session that we knew was previously running.

I re-formatted the output of the error for ease of readability. Otherwise, the output in the preceding image is what will happen when the output directory is dropped. The fix is rather simple at this point – put the folder structure back into place. To read an introductory post about checking if a session exists or not on your server, check this out (a more advanced post is coming soon).

Conclusion

From time to time we will run into various problems supporting Extended Events. This is bound to happen more frequently as we support more varied environments with more hands in the kitchen (so to speak). We need to learn that even small changes can have a ripple effect to other things that may be running on the server. It is worthwhile to perform a little due diligence and clean things up as we make changes – or at minimum to observe the system for a time to ensure no unintended consequences have occurred.

Extended Events is a powerful tool to help in troubleshooting and tuning your environment. I recommend investing a little time in reading the 60 day series about Extended Events. This is not a short series but is designed to provide an array of topics to help learn the tool over time.

Profiler for Extended Events: Quick Settings

Not long ago, I wrote a rather long article about a new-ish feature within SQL Server Management Studio (SSMS) that impacted Extended Events. You can read that book – here! The XEvents Profiler feature is one of those things that you may or may not use. If you consider using the feature, I do believe it is important that you research it a bit and try to learn the pros and cons first.

With that there is a little more about the feature that the aforementioned book did not cover. In fact, this information has pretty much gone ignored and mostly stays hidden under the covers.

Settings

As of SSMS 17.4 we have been given the ability to control XEvents Profiler just a tiny bit more. For what it is worth, we as Database Professionals love to be able to control our database environment. So this teeny tiny bit of new control ability is potentially a huge win, right?

If you are the controlling type, or maybe just the curious type, you will be pleased to know that under “Options” from the Tools menu in SSMS, Microsoft has tucked some new control options to help you configure XEvents Profiler – to a degree. If you open options, you will see this new node.

If you expand the “XEvent Profiler” node (circled in red), you will discover the “options” node. If you click on this “options” node and do a quick comparison (in SSMS 17.4 and SSMS 17.5) you will also find that you don’t need t expand the “XEvent Profiler” node at all because the options are listed in the right hand pane for both nodes and they are exactly the same. So, choose one or the other and you will end up at the same place.

The options that you currently have are:

  • Stop Session on Viewer Closed
  • Toolbar commands stop and restart

You can either set these options to True or False. I recommend you play with them a bit to discover which you really prefer. That said, I do prefer to have the “Stop Session on Viewer Closed” set to true. There is “profiler” in the name of the feature afterall. And if you have read the “book” I wrote about this feature, you would know that the filtering offered by the default sessions of this feature basically turn on the fire hose effect and can have a negative impact on your server. Are you sure you want a profiler style fire hose running on your production server?

Conclusion

There surely will continue to be more development around this idea of an XE style profiler. More development generally means that the product will mature and get better over time. This article shows how there is more being added to the feature to try and give you better control over the tool. We love control so the addition of these options is actually a good thing. Is it enough to sway me away from using the already established, more mature, and high performing tools that have been there for several generations? Nope! I will continue to use TSQL and the GUI tools available for XE that predated the XEvent Profiler.

Some say this is a way of bridging the gap. In my opinion, that gap was already bridged with the GUI that has been available for several years. Some say that maybe this tool needs to integrate a way to shred XML faster. To that, I say there are methods already available for that such as Powershell, the live data viewer, the Target Data viewer, or even my tools I have provided in the 60 day series.

I would challenge those that are still unfamiliar with the XE GUI (out for nearly 6 years now) to go and read some of my articles or articles by Jonathan Kehayias about the power that is in XE as well as some of the power in the GUI.

«page 3 of 118»

Calendar
November 2018
M T W T F S S
« Jul    
 1234
567891011
12131415161718
19202122232425
2627282930  

Welcome , today is Monday, November 12, 2018