SQL Server Updates

Updating SQL Server is a topic that infrequently gets sent to the support desk which is surprising given the importance of the applications that depend on a SQL database.

The questions usually being:

Is it safe to apply a SQL Server update?
Are you responsible for the update or are we?
What SQL Server updates are certified/supported with our software?

As of SQL Server 2017 the service pack is no more and the cumulative update now has the same level of validation as what the service packs used to have. This simplifies the update process somewhat. Generally it is safe to install these CUs and Microsoft even encourages you to do so on a “proactive” basis.

Installing an update to SQL Server should always be done with a get-out plan in case something goes wrong during the install. That means a properly tested backup and recovery plan.

I have a default rule that when I approach a job and the client asks me to install SQL Server for them I always apply the latest available cumulative update for testing. After that point the client is handed the responsibility for maintenance of SQL Server including the updates.

Most clients I’ve worked on do not patch SQL Server however most have unknowingly applied a SQL update through some form of Windows update mechanism. It’s not always clear if this is WSUS managed or not.

In an ideal world there would be user-acceptance testing done prior to applying SQL Server updates but many businesses do not have the time or facilities to do so. Regardless of the testing done a cumulative update should not break an application and I would always expect the author to be working with the latest service pack or cumulative update during development and support.

In conclusion patching SQL server is something that should be done on a regular basis and an activity that IT administrators need to be confident about handling.

SQL Server 2019 Released

This week Microsoft released SQL Server 2019 RTM.

I’ve not had much time to play with it so far. The headline new feature of SQL Server 2019 appears to be support for Big Data clusters which is not something any of my clients have asked about. However I still think they’d benefit from the in-memory tempdb and some new performance tweaks so I’m keen to check these new features out and see for myself.

I did have issues upgrading from SQL Server 2017 on my desktop PC. I had a Developer edition instance that was previously upgraded from SQL Server 2016. The 2019 installer was failing on the Client Connectivity SDK. I ended up removing as much as I could but was unable to clear away the SQL Server 2016 Setup files. To resolve this I did an install of SQL Server 2016 again and uninstalled everything. This then cleared away the troublesome setup files and I was able to install SQL Server 2017 and SQL Server Management Studio 18.4. If anything this reinforces the best practice of not upgrading SQL Server instances but installing as new.

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.

What Do SQL Server Consultants Use (Aside from SSMS)?

We’ve been really busy in the office recently doing many installations and upgrades for a whole host of cool customers. Not so much to post on SQL recently (learning Data Warehousing) so instead here’s the software championships…

LastPass – Like all peeps in the consultancy game I’ve got a zillion passwords to remember. Keeping them all in a secure vault I can get anywhere saves a massive amount of time and frustration. Give it a try on me.
Notepad++ –. Venerable text editor fluent in a million languages and talented in many life skills. Can confirm the Compare plugin is a must.
SumatraPDF – You wouldn’t read documentation with Adobe Reader now would you? This one is lightweight so it launches fast.
Microsoft OneNote – Searching emails for crucial information is so 00s. It’s also the leading cause of bleeding eyes and crushed souls. Be a hero: put your notes in a modern notebook (and search later!)
7-zip – Zip it. Ship it. Some bugs were fixed.

Other brands are available of course.

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.

Passing Exam 70-461: Querying Microsoft SQL 2012/2014

Recently I passed my first Microsoft Exam. After lots of time studying, tinkering and practising I can now say I’ve passed exam 70-461: Querying Microsoft SQL Server 2012/2014.

For those looking to take exams you might find my “study mix” helpful. I’m not going to divulge what was on the exam but what I would say is that you need to be prepared and you really, really need to know your T-SQL right from the knowledge about how and why queries work to how you write them.

Courses – I attended a course in Leeds, West Yorkshire provided by a company called QA Ltd. They provided a service they call “certification in a box” which is: the course, a voucher for the corresponding Microsoft labs for 14 days, a voucher for a MeasureUp practice exam for 180 days and a voucher to take the final exam. The instructors were very knowledgeable and I thoroughly enjoyed the courses as well as learning a lot.
Training Books – My humble beginnings in SQL were from Sam’s Teach Yourself SQL in 10 Minutes a Day but for this exam you need something far heavier. Microsoft Press have a book written specifically for those taking exam 70-461. Just a word a of caution: don’t take the included practice exam as a good measure of what the real exam is like. Microsoft Press even event spelt ‘training’ wrong on the CD label.
Practice Exams – The course from QA provided a practice exam from MeasureUp. Again, I’m not going to let you in on the exam content but this practice exam isn’t exactly what you should expect for the real thing. It was great for learning as it does provide explanations of why an answer was wrong.
Experience – As I mentioned above you really need to know the practicalities of how SQL works. If you can do it under pressure in your job you can do it in an exam (which is less pressure!).
Lab – As a keen target archer I found inspiration to build a database on a PC at home. This gave me a great example of how to build an eventually query data from it without regurgitating examples.

Lastly a word about “braindumps”. Don’t. That’s all you need to know bout them.

My next exam will be 70-462: Administering SQL Server 2012/2014 and then followed by Exam 70-411 to hopefully earn an MCSA in SQL Server.

RAM is cheap, time isn’t.

There aren’t many small challenges in life that I haven’t solved by: a) WD-40, b) bicarbonate of soda or c) add more RAM.

This week a colleague asked a question regarding specifying a server: “is it ok for Client X to reduce RAM on the new server they are specifying?”
We will eventually build a finance system onto this server. It will run Microsoft SQL Server, the finance application and also reporting services through SQL.

An 8GB stick of RAM would cost about £100 based on a quick look at my favourite hardware site. Compare the cost of RAM to the productivity time users could be spending waiting for results or reports to complete over the 3-6 year lifespan of the server.

“RAM is cheap, time isn’t.”

Don’t forget!