Patch Time

If you’re in the SQL Server world you should be aware of this by now however for those who’ve been busy getting stuff done this week Microsoft have released a security update for SQL Server described in full in KB4583459.

Data can be sent over a network to an affected Microsoft SQL Server instance that might cause code to run against the SQL Server process if a certain extended event is enabled. To learn more about the vulnerability, see CVE-2021-1636.

Patches are available for SQL Server 2012 and above with currently supported service packs. As it’s most people’s best interest to maintain a SQL Server that doesn’t allow this to happen it’s a great idea to get this patch installed now. If you are intending on ruining someone’s day with this exploit I wholeheartedly apologise for spoiling your fun.

I anticipate quite the majority of my clients will “accidentally” end up with this patch through Windows Update which is probably for the better (exceptions given if it somehow kills the SQL Server).

Some Things I Never Knew In SQL Server

Over the course of the last couple of months I’ve come across some things in SQL Server that I’ve never really thought about.

“..” implies the default schema for A user.

If you have a user in a database with say a default schema of “Production” writing this:

SELECT ProductCode, ProductName, ProductColour
FROM CompanyDB..Products

Actually implies the Production schema.

The TempDB can really cause problems if it’s not configured correctly.

More of a reminder this one. I’ve recently worked on a client who was having some performance issues with a reporting tool. When they ran extracts from a SQL database it wasn’t very fast. I tried it myself and agreed it wasn’t performing as well as it should.

The reporting tool is heavily driven by cursors but let’s not get started on those. I looked in Activity Monitor and noticed quite a lot of contention on the TempDB. A further investigation revealed that there was a total of 1 TempDB for a Octa-core server.

After adding a further 7 TempDB row and log files we found that the reporting tool ran much much quicker and the contention was gone.

You can alias without using “As”

This one probably isn’t “best practice” but you can write:

SELECT C.CustomerID, C.Name, C.Address1, C.City
FROM Customers AS C

As:

SELECT C.CustomerID, C.Name, C.Address1, C.City
FROM Customers C

I don’t like doing this because to me that’s breaking a general principle of keeping script readable plus the reason I’ve never known about this is because all the T-SQL books and resources I’ve read have NEVER mentioned this is correct syntax so they must also consider it a no-no.

You stand a good chance of recovery if your server doesn’t boot but you’ve still got the SQL data.

I recently helped a client that lost a SQL data warehouse server to a Windows update that somehow caused it to not boot. They managed to pull all the files off the volumes so we had all the user databases as well as master and msdb databases.

The client was on a tight budget so we raced against time to get the server going as quick as possible. I was surprised to see that simply dropping in the master and msdb databases over the ones installed on the new server (shut the SQL Server instance down first) and putting the user databases at the original location restored pretty much all settings and data.

Of course you’ll run into problems if there was some encryption in use and you’ll need to use the same SQL Server release but other than that it proved a seemingly OKish way to restore the client’s data warehouse quickly.

70-462: Administering SQL Server 2012/14

This week I passed Microsoft Exam 70-462: Administering SQL Server 2012/14. This was my second attempt after a near passĀ of 674/1000 (pass mark is 700) on the first attempt.

My preparation was similar to that I did with 70-462 so check out my earlier blog post for more details. The main difference was that I built a lab environment and messed around with everything in SQL Server with the aid of a Microsoft study guide. Lots of stuff has been learnt from this and confidence with the product comes with that progression.

Honestly the first attempt at 70-462 felt easier than 70-461. Naturally I’m more of an SQL administrator (alright: configurator) than a query writer. I was disappointed to have failed it first time but I decided to come back for seconds.

My 3rd and final exam I have planned for this year will be 70-411: Administering Windows Server 2012. Upon hopeful completion of said exam I will become an MCSA in SQL Server 2012/2014 with a view to upgrading to SQL Server 2016/7 next year.