SQL RNNR

This is 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.

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.

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?

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 job is the syspolicy_purge_history job.

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.

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).

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?

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.

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. You appear to have a corrupt database and nasty IO errors.

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 shows 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.

I didn't show the impact to my existing databases on the test instance because I felt it out of scope.…

Agreed.

Nicely done. Came across the article at the end of April and was slack jawed. I couldn't figure out how…

One thing to note: this was a brand new install with no data in the database. Thus the…

I don't see a mention of how or if, existing databases are affected. IIRC, changing server collation only affects…

I want to upgrade an SQL Server 2008 R2 to SQL Server 2016. I would like to find the queries…

Great post, thank you! I am definitely going to check out your post on 'learning-extended-events-in-60-days'!

Thanks Robert.

Great article Jason. When I saw your initial list of things to do and #5 was "Restore", my initial thought…

that is a very good question. AX 2009 did things a little differently. While minimal, at best, the online users…