The question of encryption seems to be coming up a lot recently. I’ve had a number of people asking me about how to go about encrypting SQL Server. SQL can encrypt our data at a number of different levels and gives us a quite a few options when doing so. I want to use this post to put together a matrix so you can easily see which method of encryption suits your purpose. I’ll look in to each method in more detail in a future series of posts. Transport Layer Security Transparent Data Encryption (TDE) Backup Encryption Cell Level Encryption Always Encrypted Encrypts Data at Rest Enc ..read more

Photo by Pixabay on Pexels.com Here’s a quick one for today and is an issue that had me stumped for a while. It’s not one that I’d come across before and there isn’t really all that much out there on the internet about it. The Issue as I Saw It So, what was the issue? I was setting up an AG, all pretty standard stuff using the wizard in SSMS. I went through the usual setup and when I got to the end, everything seemed to create as I’d expect it, the only issue and first hint that there was an issue was that the wizard just sat there spinning when trying to join a database to the secondary. I ev ..read more

Photo by Pixabay on Pexels.com This was an interesting question that I was asked yesterday and something that I’d never really thought of before. Can you delete the top x number of rows based on an ORDER BY? Why would you want to do that? Well let’s just assume that we have a ‘people’ table and some strange bug in the application has cause the need to delete the top 10 oldest females for whatever reason (I know, it’s very contrived example and not the situation that my colleague was facing but it’ll do to illustrate the point). We have a table that looks something like this…. It’s easy enough ..read more

Have you ever had the need to attach a large number of database in one go? There’s no way to attach multiple databases in SSMS or via script, so you’re probably going to be left with the slow, arduous task of doing them one by one. I recently had to deal with a DR situation (I won’t go into details of what happened just yet as things are still quite sensitive, but I might look at it at some point in the future) where I faced exactly that issue. For one reason or another I needed to attach several hundred databases quickly. I didn’t fancy doing that via SSMS or script each one individually so ..read more

Sometimes I find remembering where a particular server sends its backups to a nightmare. You might have servers backing up to different locations, you might have different locations for individual databases and different locations for your fulls, diffs and logs. You might be trying to get your head around a customer’s set up, where the backups make no logical sense at all. Whatever you’re up to, at some point, for some reason you’re going to need to access your backup location to get at the files. I used to figure out where that was by querying msdb if I was on a customer’s kit or if onsite w ..read more

Image by Belinda Cave from Pixabay Imagine that we’re seeing very heavy load on our SQL server, be it CPU or IO and we need to find out which database is the driver. I’m sure we’ve all been there at some point, sometimes it’s easy to tell. We might only have a handful of databases on the server with one known to be the heaviest utilised. But sometimes things might not be so obvious, there could be a large numbers of databases or no obvious resource hog. In those instances we need some way to figure out what how much time each database is spending on the CPU if that’s what we’re interested in o ..read more

Photo by Joey Kyber on Pexels.com I’m going to assume that most people will have at least heard of Brent Ozar’s brilliant First Responder Kit. If you haven’t then you’ve been missing out on some serious (and FREE) SQL troubleshooting goodness and I’d suggest you check it out. Now I use these scripts all the time when checking out SQL Servers. Sometimes I run them as and when I need them but one of the most handy uses for them is to have them running from an Agent job and logging to tables on a regular basis (I usually run them every 15 minutes but might run them more or less regularly as neede ..read more

Spinlock contention is always a real headache to deal with. I recently saw an issue when spinlock contention on SOS_CACHESTORE was making the server virtually unresponsive. The issue was very intermittent with no obvious pattern but the assumption is that it was caused by a particular process in the application. Finding what that process was the tricky part, they don’t show up as waiting tasks so your usual scripts for looking for waiting processes may not work here. The place that we can see works who are waiting on a spinlock is in sys.dm_os_workers, specifically the is_sick column. If that ..read more

Here’s an interesting issue that recently came up. We were seeing very high compilations and recompilations on a server to the point that it started causing us some very serious issues (admittedly this wasn’t the sole issue but it was certainly a contributing factor, the other factors were also very interesting so I might look at those in another post). After looking in the plan cache we could see a very high number of single use plans for a particular stored procedure. Now as you probably know, SQL will usually cache an execution plan and use it over and over whenever a particular query runs ..read more

It look like we had a little bug in sp_RestoreScript that was putting a rogue 0 after the backup file name. That’s now been fixed in 1.9. For information and documentation please visit https://sqlundercover.com/2017/06/29/undercover-toolbox-sp_restorescript-a-painless-way-to-generate-sql-server-database-restore-scripts/ Keep an eye out for sp_RestoreScript 2.0 which is currently in development and will bring a while load of new features and flexibility ..read more