T-SQL Tuesday #102: Giving Back

Comments: No Comments
Published on: May 8, 2018

bleeding heartLast month we had the opportunity to discuss some of the most important tools for a data professional. I took that opportunity to discuss how it is important to blog. As it turns out, that article correlates fairly strongly to today’s article.

This are maybe some of the questions that Riley Major (b | t) would like for us to examine our own deep dark secrets and psychological makeup and why we give back or how we help give back or what we plan to do to give back in this, the 102nd, installment of TSQL Tuesday.

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

“Now I will give you an opportunity to give back. Everyone reading this has benefited from their fellow data professionals. And that benefit puts you in a position to share alike. You’ve learned something, so you can teach. You’ve been supported, so you can help. You’ve been led, so you can lead. But you don’t have to do it alone. We’re all going to do it together.

So here is my call. Pick some way you can help our community. “

That is the invite – basically. The problem now is that there have been multiple TSQL2

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.

Reality Check

Very much related to my blog post about blogging, I have to echo the sentiment about how “Blogging helps you become a better technical person.” A lot of what I do for my blog is there to help the community, but it has a self-serving purpose. It helps me become a better technical person. It also helps me to improve my communications and writing skills.

There are some side effects of blogging as well. Each of us has a finite number of keystrokes in our lifetime. That said, it makes sense to write certain technical things down in a blog post rather than retyping the same information over and over for various different email or forum responses. Make sense? If nothing else, it just seems more efficient to write a long technical explanation once rather than 12 times.

So there we have a couple of self-serving reasons to blog. Those same self-serving reasons also frequently apply to being involved in the community. For example, the more you exert yourself to help answer forum questions, the more you learn. You become a more experienced technical person. In addition, you learn how to communicate better and write better (hopefully). You are practicing your craft in a public forum where people can easily shred you (and they often do), when you are wrong – even minutely wrong. This potential for being blasted in the forums typically makes one work harder at getting everything just about perfect.

If you opt to speak in front of technical people, guess what? You are doing the same things I just wrote about in regards to forum responses as well as with blogging. The big difference is that you are now doing it in person, live, on stage, and verbally! You have really put yourself out there in a big way to go speaking in front of people. You will likely double down even more with regards to ensuring your material is very near perfect and bullet proof. In addition, you will probably practice a few (hundred) times to make sure you don’t fumble with your words. What does this mean? You are becoming a more solid technical person and honing your communication skills. Again, very self serving!

Or is it? The one final aspect of being a community visible person is the drive behind what you do. I like to share what I learn. I also like to share my time. I believe in serving others with a charitable demeanor. Giving of yourself will always enhance your life more than you can imagine – when you do it with the attitude of putting others first. There is no selfish intent to those that really want to help the community.

It doesn’t matter if you are helping the sqlfamily, your local Scouting organization, boys and girls clubs, sports teams, or volunteering at the local schools etc; if you are doing it with the intent to serve and do good – you will enhance your life in some way. If you are doing it for some accolade or truly self-serving reason, you may get the accolade but you will find yourself stunted in the growth potential.

People that give of themselves freely is such an awesome characteristic. There are many in the SQL community that truly give of themselves freely – like SQLSoldier. When it is a part of your identity, it comes naturally and there isn’t a lot that needs to be done to plan for it. Sometimes, maybe it would be nice to be able to have more time to be able to do more – sure. And that is the beauty of this characteristic. If you are giving of yourself freely, you often find that you want to give more. That is great! Do what you can, when you can. Sometimes, it will be more. Sometimes, it will be less. It is all good as long as the heart is in the right place.

TSQL2sDay150x150The Wrap

This has been my diatribe about service and giving back to the community. When done properly, there is a natural born effect of enhancing one’s personal life equal in some way to the amount of effort given towards the community.

Oh, and if you are interested in some of my community contributions (which according to Jens Vestargaard is an awesome contribution), read this series I have published.

Change SQL Server Collation – Back to Basics

One of my most favorite things in the world is the opportunity to deal with extremely varying database and environment requirements. Many vendors and databases seem to have a wide swath of different requirements. Some of the reasons for these requirements are absurd and some are not. That is a discussion for a different day.

When dealing with vendors, sometimes you get good documentation and requirements for the app. If you happen across one of these opportunities, you should consider buying a lottery ticket. Most of the time, the requirements and documentation are poorly assembled and then suffer from linguistic shortcomings.

What do you do when you run into poor documentation from a vendor? The typical answer would be to either call them or make a best guess (even if you call them, you are likely stuck with a best guess anyway). Then what do you do when you find that your best guess was completely wrong? Now it is time to back pedal and fix it, right?

When that mistake involves the server collation setting, the solution is simple – right? All you need to do is uninstall and reinstall SQL Server. That is the common solution and is frankly a horrific waste of time. This article will show some basics around fixing that problem quickly without a full reinstall.

I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.

Reinstall Prohibited

I am not a huge fan of wasting time doing something, especially if there is a more efficient way of achieving the same end result. I am not talking about cutting corners. If you cut corners, you likely just end up with having more work to do to fix the problems your sloppiness will have caused. That to me is not the same end result.

Having run into a bit of a problem with a vendor recently (with lacking requirements), I found myself with a server that was installed with the wrong collation instead of what the vendor wanted (never-mind they said nothing of it until a month after the server was setup and ready for them to use). The vendor needed the collation fixed immediately (basically it needed to be fixed yesterday). I really did not want to do a reinstall of the server and the sysadmins were just about to click through the uninstall and redo the install.

Oy Vey! Everybody hold up just a second here! First things first – verify with certainty there is good reason to need to change the server collation. It is perfectly legit to give the vendor the third degree here. Make sure they understand why they need the change. If they can answer the questions satisfactorily, then proceed with the change.

Next, just because the vendor says you have to uninstall/reinstall (or reboot) the server to make a certain change, does not mean they know what they are talking about. I have run into too many cases where the vendor thinks you must reboot the server to change the max memory setting in SQL Server (not true for sure).

Sure, common myth would say that you must reinstall SQL Server in order to change the default server collation. That is not entirely accurate. Reinstall is just one option that exists.

In the case of this vendor, they required that the SQL_Latin1_General_CP850_CS_AS collation be used. The server was set for SQL_Latin1_General_CP1_CI_AS. So, let’s see how we can change the collation without a reinstall.

The first thing to do is to confirm the collation we have set.

We can see from these results that indeed the collation is wrong and we need to change it in order to comply with the request from the vendor. Next we will need to stop the SQL Server services.

I think that is pretty clear there what to do. As a reminder, the preferred method to stop and start SQL Server services is via the SQL Server Configuration Manager. We won’t do every start/stop from here for this article for good reason.

Once the services are stopped, then we need to open an administrative command prompt and navigate to the SQL Server binn directory as shown here.

This is for a default instance on SQL Server 2017. If you have a named instance or a different version of SQL Server, you will need to navigate the instance folder structure for your instance.

Next is where the magic happens. We enter a command similar to this:

Here is a quick summary of those flags in this command:

[-m] single user admin mode
[-T] trace flag turned on at startup
[-q] new collation to be applied

There are more such as -s available in books online for your perusal.

If you are curious what is up with those Trace Flags, pretty simple. TF4022 is to bypass startup procs. TF3659 on the other hand is supposed to write errors to the error log (at least in theory).

When the script starts, you will see something like the next two screens:

In the first, you can see that it says it is attempting to change the collation. In the second, just before the completion message, it states that the default collation was successfully changed. Let’s close this command prompt window and then go start SQL Server and validate the change.

And that is a successful change. See how easy that is? This effort takes all of maybe 5 minutes to complete (validation, starting, stopping and so on). I would take this over a reinstall on most days.

Now that we have changed the collation, all I need to do is repeat the process to set the collation back to what it was originally (in my test lab) and make sure to bookmark the process so I can easily look it up the next time.

There is a bit of a caveat to this. On each change of the collation, I ran into permissions issues with my default logging directory (where the sql error logs are written). I just needed to reapply the permissions and it was fine after that (SQL Server would not start). That said, the permissions issue was not seen on the box related to the change for the vendor. So just be mindful of the permissions just in case.

The Wrap

Every now and again we have to deal with a sudden requirements change. When that happens, we sometimes just need to take a step back and evaluate the entire situation to ensure we are proceeding down the right path. It is better to be pensive about the course of action rather than to knee jerk into the course of action. Do you want to spend 5 minutes on the solution or 30-40 minutes doing the same thing? Changing collation can be an easy change, or it can be one met with a bit of pain doing a reinstall (more painful if more user databases are present). Keep that in mind and keep cool when you need to make a sudden change.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

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.

SQL Server User Already Exists – Back to Basics

One of my all-time favorite things in SQL Server is security. No matter what, it always seems that there is a new way to abuse permissions. When people abuse their access level or abuse the way permissions should be set in a SQL Server environment, we get the pleasure of both fixing it and then trying to educate them on why what they did was wrong and how to do it the right way.

In similar fashion, I previously wrote about some fundamental misconceptions about permissions here and here. I have to bring those specific articles up because this latest experience involves the basics discussed in those articles along with a different twist.

I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.

Gimme Gimme Gimme…

It is not uncommon to need to create a login and grant that login access to a database (or associate that login to a database user. In fact, that is probably a fairly routine process. It is so routine, that I have a demo script for it right here.

I even went as far as to include some of the very routine mistakes I see happening on a frequent basis (as referenced by a prior post here).

To this point, we only have a mild abuse of how to set permissions for a principal. Now it is time for that twist I mentioned. This user account needs to be created on a secondary server that is participating in either a mirror or an Availability Group. Most people will take that user account that was just created on the first server and then use the same script to add the account to the secondary server. Let’s see how that might look.

For this example, I will not go to the extent of creating the mirror or AG. Rather, I will pretend I am just moving the database to a new server. So I have taken a backup and then I will restore the database to the new server.

Next, let’s go ahead and recreate the login we created on the previous server.

You see here that I am only going to create the login if it does not exist already. Running the script produces the following for me.

Now, let’s deviate a bit and grant permissions for the login just like so many administrators will do.

It seems pretty apparent that my login that I just created does not have access to the GimmeSA database, right? Let’s go ahead and add permissions to the GimmeSA database and see what happens.

Well, that did not work according to plan right? Enter twist the second.

What I am seeing more and more of, is people at this point will just grant that login (that was just created) sysadmin rights. You can pick up your jaw now. Indeed! People are just granting the user SA permissions and calling it good. This practice will certainly work – or appear to work. The fact is, the problem is not fixed. This practice has only camouflaged the problem and it will come back at some future date. That date may be when somebody like me comes along and starts working on stripping non-essential sysadmins from the system.

There are two legitimate fixes for this particular problem (and no granting sysadmin is definitely not one of them). First you can run an orphan fix with a script such as this one by Ted Krueger. That will map the user that already exists in the database to the login principal (thus the reason for the error we saw). Or, you can prep your environment better by using the SID syntax with the create login as follows.

The trick here is to go and lookup the SID for the login on the old server first and then use that sid to create the login on the new server. This will preserve the user to login mappings and prevent the orphan user issue we just saw. It will also prevent the band-aid need of adding the login to the sysadmin server role.

The Wrap

In this article I have introduced you to some basics in regards to creating and synchronizing principals across different servers. Sometimes we try to shortcut the basics and apply band-aids that make absolutely no sense from either a practical point of view or a security point of view. Adhering to better practices will ease your administration burden along with improving your overall security presence.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Database Corruption and IO Errors

A quick way to have your day turned upside down and rip your gut out with nerves and anxiety is to come in one day to find that users are panicked, applications are not working and the HelpDesk team is curled up in the fetal position in the corner. Why? The sky is falling and everybody thinks the database has blown up.

Calmly, you settle in and check the server and eventually find your way to the error logs to see the following:

Msg 823, Level 24, State 2, Line 1

The operating system returned error 1(Incorrect function.) to SQL Server during a read at offset 0x0000104c05e000 in file ‘E:\Database\myproddb.mdf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Suddenly you understand and feel the collective fear and paranoia. What do you do now that the world has seemingly come to an end for your database?

Corruption

What exactly does this error message mean? Well, typically, an 823 error is a very strong indicator that there is some sort of problem with the storage system, hardware or driver that is in the path of the I/O request.

Great! That fear is getting a little heavier knowing what the error represents. This doesn’t bode well for the database. Let’s go ahead and crack out the list of what we can do or check when a problem like this hits:

  1. Check msdb.dbo.suspect_pages
  2. Run a consistency check for all databases on the same volume
  3. Check Logs (SQL, Windows, Storage system) to see if there may be additional info (via different errors/warnings) in close proximity to the 823 error.
  4. Check your drivers
  5. Restore the database

This is where your experience, training, and preparedness come in handy. An experienced data professional will be prepared with database backups (including log backups). So you are not concerned here because all of your backups are reporting successful. As you prep to pull the backups (for the past couple of days just in case) you notice that there are no available backups in your repository. Looking closer at your backup jobs you discover that the backups completed in mere seconds where they normally take hours for this database.

Now that your heart is racing, forehead is beading up with sweat, gut is sinking and the fear is very palpable – what do you do? Time to step through the rest of the steps and pull out your lucky charms, right?

Querying against suspect_pages, you find the table to be completely empty. You know that checkdb runs regularly but maybe it didn’t run last night. That is easy enough to check with a little query from here. Since a consistency check does not seem to have run (as confirmed by the script) and is the second item on the checklist, let’s go ahead and run it now.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Crud. Blood pressure and nerves are getting a little more frazzled now. Maybe we can cycle through the database and find which table is causing the problem. Let’s try a checktable of every table in the database. Before doing the checktable, one more check against suspect_pages still shows no rows to be found.

Running the checktable, every table is coming up clean except one. That one table produces the same sort of error as the checkdb and just so happens to be the largest and most critical table to the database. Blood pressure is closing in on critical now. We have a corruption issue that is severe enough that checktable cannot complete, we know we have 823 errors and some sort of IO issue and do not have a backup.

Wait…backup. Let’s try to force a backup and see what happens. We can tell the backup to continue after error so let’s see what happens. Maybe that will allow you to move the database to a different server or different spindles to try and just recover the data.

Msg 3202, Level 16, State 2, Line 1

Write on “E:\SQLBackups\myproddb.bak” failed: 1(Incorrect function.)

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

The situation just does not want to get any better at this point. Time for drastic measures – shut down the SQL Server services and try to xcopy the data and log files to a different server and try to re-attach from there. Anything is worth a shot, right?

Error 1: Incorrect Function

Glad to know it is not just SQL Server throwing the errors – sorta. The corruption is ever present and there is nothing more that can be done, right? All hope is lost. Time to fill out the resume and move on to something else, right? Give it one more shot. A spark of insanity hits and you wonder if a mere query against the clustered index will work, if not then maybe something against any of the indexes to try and spare any data at all.

You rub your luck horseshoe and query the table (yes the table that checktable aborts because of corruption). Lo and behold you get results from this very simple query. How is that possible. On a whim, you drop all the Non-Clustered Indexes and try a fresh backup.

Hallelujah! The backup completes without error. Time to take this backup and restore the database to a completely different server. Then on the new server run a consistency check to determine if it is all clear. To your liking, there is absolutely no corruption at this point so the non-clustered indexes can be recreated (easy to do because you have a script with the index definitions handy).

Wrap

This journey from fire and brimstone and the world ending to the epiphany and then the sweet euphoric feelings of success is not a normal resolution for these types of errors. This sort of thing happened for a client that called trying to get around the 823 errors. We had absolutely no indication whatsoever of where the corruption was beyond knowing we had failing disks at the time. We got lucky in that the non-clustered indexes in this case ended up being stored on the bad sectors and dropping those indexes allowed us to recover the database and make it usable.

When everything is breaking, the unconventional thought (especially without having a checkdb complete to tell you which index or which pages are corrupt) of dropping indexes may just save your bacon. It saved my clients bacon!

As a follow-up item, it is important to regularly check the dbccLastKnownGood for each database. That can be done by following the script in this article.

Checking the Health of your CLR

Comments: 1 Comment
Published on: January 16, 2018

The Common Language Runtime (CLR) is the fundamental nerve center of the Microsoft .NET Framework. It is this nerve center that provides the execution environment for all .NET Framework code. One may sometimes refer to CLR code as managed code.

CLR hosted from within SQL Server (CLR Integration), provides the ability to create stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. And because managed code compiles to native code, we sometimes see a bit of a performance gain for various scenarios.

It is this bit of a performance gain, that we may see more CLR integration use within our SQL environments. And with increased CLR there will be an increased chance for something to go a little haywire. If things can (and will go haywire), it is important to have a means to be able to monitor them.

If you know me, you are probably aware that when it comes to monitoring a problem, I will most probably recommend a solution that involves a little bit of XE. If you don’t know me, here is a hint: some of those monitoring tools can be found in this series.

CLR

I had a client reach out recently because they were having all sorts of issues with their CLR procs. The client is losing some sleep, a little weight, and a fair amount of hair trying to figure out what is causing their CLR nightmares. This client has magic CLR. The CLR works fabulous or a day or three. Then suddenly the CLR procs just disappear and have to be re-created on the server. Granted this seems more like a problem of somebody did something they didn’t realize they were doing and poof the CLR is gone.

So, what can we do about this? The answer is quite simple. I am going to use an extended event session that will monitor the various aspects of CLR from a couple of different angles. First, I need to try and find events that fit my needs. I can do that with the queries and instructions found here.

As I query the XEvent catalog, I can see that there is a limited set of options and they can be seen in the following image.

Most of the events can be found in the sqlclr package and in the debug channel. The exception in both cases being the “assembly_load” event. I will go ahead and add all of those events to my session except for the garbage collection event.

The next step is to help cover my bases and see if somebody might be changing the objects and causing the odd behavior. I will do that with the object_created, object_altered, and object_deleted events. The caveat here is I will limit the scope of those events to only look for CLR specific changes. I will be able to do that by filtering on object_type in each of those three events. Here is a look at those object types.

 

In Extended Events, we see that we can monitor for object changes in the various different objects such as “TRIGASM”,”PROCASM”, and “FNTABASM”. What do these map to though? Well, here is the answer to that question!

  • 16724 TRIGASM = Assembly (CLR) DML trigger
  • 17232 PROCASM = Assembly (CLR) stored-procedure
  • 21313 ASM = CLR Assembly
  • 21318 FNSCLASM = Assembly (CLR) scalar-function
  • 21574 FNTABASM = Assembly (CLR) table-valued function
  • 17985 AGG — Assembl Aggregate function (CLR)

With all of this rolled together, we can now assemble the XE Session.

Once deployed, I can go ahead and follow the instructions here to test different CLR managed code objects.

The Wrap

Having a tool to be able to monitor CLR health will be essential as you deploy more and more managed code within SQL Server. Extended Events offers a great lightweight means to do just that. This article has shown how to deploy a session that will capture the various changes with CLR objects within our database environment. In addition, you will be able to capture various conditions related to performance or problems with the managed code. You may even recognize some of the CLR events from the system_health session.

If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.

«page 1 of 49

Calendar
May 2018
M T W T F S S
« Apr    
 123456
78910111213
14151617181920
21222324252627
28293031  

Welcome , today is Sunday, May 27, 2018