Monday, July 16, 2018

A quick 'save me from myself' thing in SSMS

Have you ever felt that 'down-elevator' feeling in the pit of your stomach when you realize you just executed a script against the wrong instance? I have.

It's of course possible to add name checks and all to only allow execution on a given instance, I'm going to go out on a limb here and guess that most people don't do that in most cases.

Happily, there's a quick and easy setting you can take advantage of to give yourself a visual indication that hey, maybe you're not where you thought you were: the 'Use custom color:' setting in the Connection Properties of the Connect to Server dialog box.

To get there, first click Options:

Check the box for 'Use custom color:', and hit 'Select...':

Pick your color, click 'OK' (I tend to use red for production, orange for QA/test, green for development, but go with whatever works best for you), and then click 'Connect':

From here on out, the status bar down at the bottom of the query window will be whatever color you've picked for that instance:

While various third-party tools offer similar functionality (often with a lot more flexibility and configurability), this is built-in to SSMS and has been around for quite a while. It's a great quick gut-check!

Tuesday, July 10, 2018

Updates and housekeeping

After... well, forever... on an old theme, I've updated. Some older posts may look weird, but most should be good. Please let me know if you run into any problems.

The code I can't live without

Today's T-SQL Tuesday topic is thought-provoking: What code couldn't you live without? (OK, OK, technically it's just titled 'Code You Would Hate to Live Without', but work with me here.)

One of the hallmarks of the dedicated DBA is that we write our own tools. I've encouraged juniors to write their own code, even if they can find things online that solve their problems (inexperienced folks blindly using code from online is a whole can of worms that I may open later). We gain so much by doing the work to learn about the systems we're interacting with, and the harder work of figuring out how to write the code, be it T-SQL, Powershell, or what have you, to really dig in there.

As a brief aside, in my experience if you don't have the curiosity and drive to begin writing your own tools, you're not going to get very far as a DBA. I'm not sure I'd WANT you to be in charge of my servers. Now, not for a second would I expect highly-polished code from juniors right out of the gate. What matters more is evidence of that curiosity and drive. What matters is that we keep learning, keep practicing, and keep improving.

On the flip side, there are times we find something out there that Just Works. Part of the wisdom we develop as we progress is knowing when to press on with writing our own, and when someone else's code does the job perfectly.

I'm finished digressing - on to the code I'd hate to live without!

Under the 'code from others' umbrella, code I'd hate to live without includes:

Of the things I've written myself, I would hate to live without these things (please note, these are all hosted in Dropbox; if you want copies and can't get to Dropbox please let me know!):
  • Replication scripts!
  • A script to get stored procedure execution information, and the query plan if you want.
    • This query gives a lot of info - when the plan was cached, how many times the plan's been looked up, how many things reference the plan, when the proc was last executed, how many times it's been executed, all sorts of worker, reads, and writes info including averages.
    • The usual caveats on the cache info apply - restarts, cache flushes, index rebuilds, plan recreations, etc. are all things to keep in mind
  • Some templates for file manipulation
I've got a lot more, but these are the most important to me.

Thursday, February 08, 2018

On the importance of testing your backups

You're backing up your databases. The jobs run when they're supposed to, you've chosen the right types of backups, got all your settings dialed in, they take the expected amount of time, the files are there on disk. Everything's good!

Well, maybe.

Have you restored your backups? You should. I'd even go so far as to say that without test restores, your careful backup strategy was a waste of time and energy.

"But the backups complete without errors," you say. And that's certainly good, but it's only part of it. Things can go wrong in backups. Disk hiccups after the backup is generated can cause problems. Bugs in the database engine can cause unexpected problems as well. Different database products will have different issues and quirks, but the key thing to remember is that they ALL have them. Your preferred RDBMS is no different. It's up to you as the DBA to learn what they are, and to learn how to work with and through them.

This is one that I've personally run into: "RESTORE DATABASE is terminating abnormally" error message when you perform a full backup of a database in SQL Server 2008 R2

We added a new data file to a database, and due to a quirk in our naming convention the truncation caused a problem: we wound up with two identical logical filenames in the backup file list, and we couldn't restore the backup. We were fortunate that we didn't need to do any restores for that database before we found the problem, and Microsoft did release the fix in the linked article, but we would've been in serious trouble if we hadn't caught the issue and suffered data loss.

You also need to test your restores in a realistic fashion. How much data can you recover? How long will it take? This is when you will need to consider the business's needs.

There are two objectives around recovery that you need to consider: the recovery point objective (RPO) and recovery time objective (RTO). In brief, they respectively address how much data the business is comfortable with losing, and how long the business is comfortable with a database being offline in case of failures. People too often assume that both RPO and RTO will be close to zero. It's certainly possible to get very close to instantaneous recovery with no data loss, but it can get very expensive very quickly as you're then getting into high availability (HA) territory. The old saw, 'fast, cheap, and good: pick two,' definitely holds true here!

If you don't know how long your restore will take, you won't know if you can meet the RTO. If you don't know how much data is available in your backups, you won't know if you can meet RPO. If your backups are bad, you're guaranteed not going to meet either the RPO or RTO unless neither matters for the instance (but how many instances do you manage that fall under that 'it would be nice to recover it, but we can live without it' heading? I know I don't have many in production). If you haven't talked with your management about their expectations around RPO and RTO, please do so.

While RPO and RTO are certainly important considerations, they can be ongoing topics of conversation - your first order of business is to ensure that your backups first CAN be restored, and second that they give you the results you expect. As with anything, practice makes perfect. Openness and honesty will go a long way if you identify gaps. These can be hard things to bring up, I know - I've had to have a few unpleasant conversations on this topic over the course of my career. But how much worse will the conversations be if something fails and you can't recover?

Test your backups, folks! You'll be glad you did!

Tuesday, January 16, 2018

SQL Saturday 700 in Colorado Springs is coming up!

If you work with SQL Server in any capacity, I can't recommend attending a SQL Saturday enough. A day of no-cost training and networking is a fantastic opportunity. SQL Saturdays offer a wide variety of content - there are commonly tracks on database administration, business intelligence, development, professional development, management, and many others. 

So many big names in the SQL Server community got started speaking for local user groups, SQL Saturdays, and the like, and many of them continue doing so. It's a fantastic opportunity to learn and spend time with deeply knowledgeable people, pretty much all of whom are also super-nice!

It's also a great opportunity to get to know the sponsors. Odds are you'll have the opportunity to talk directly with knowledgeable folks from storage vendors, tool vendors, recruiters, and more.

One of the other best things about SQL Saturday is that there's an open call for speakers for each event, and many of them work hard to include new speakers and local folks. If you want to share your knowledge in a larger venue, it's a great opportunity to get started. I've submitted a session myself this year. You don't even have to stick to your local area. While you're on your own for your expenses, it can be extremely rewarding to travel, meet new people, and get yourself out there in front of a larger audience.

If you live along the Front Range in Colorado (or anywhere in CO, really), northern New Mexico, etc., I hope you'll consider attending SQL Saturday #700 in Colorado Springs on 24 March.  

There are also ample volunteer opportunities. It can take a surprising amount of work to make the events run smoothly, and there's always something the organizers can use help with. Even though I'm no longer helping to plan and run the event, I've still ticked the volunteer opt-in on the registration form.

Dan Gurney, 1931 - 2018

I wrote a brief memorial for Dan Gurney over at The Pit Crew Online. The man had a LIFE, let me tell you.

Monday, January 15, 2018

A quick note about multi-hop replication in SQL Server

Many people use SQL Server replication in a multi-hop setup. Publish from A to B, republish from B to C, and so on. This is a well-known use case, even if it makes those of us who have to maintain it a bit crabby at times - throw in some other stuff like Change Data Capture that uses the Log Reader Agent and it's a recipe for fun (but that's another post for another day).

One thing I've seen a number of people run into trouble with is making changes to publications in this sort of environment. It's simple enough when you're adding new things - you start at the start and move down the chain. Where I've seen folks have the most trouble is with removing articles and publications - for that, you have to start at the last hop and work your way back to the source.

Managing this kind of setup can become tedious, so I urge you to get comfortable with the various stored procedures, views, and tables that are present in SQL Server. Microsoft's documentation on the topic is thorough and helpful, and spending some time in the lab poking around will amply repay you. Document your environment, learn what replicates where, and when it comes time to make those changes you'll be in much better shape.

If you don't have a lab, don't fret! Simply sign up for a Visual Studio Dev Essentials account at, and you can download SQL Server Developer Edition at no cost to you.

Helllllloooooo 2018!

Gosh, about 4 years. I figure it's time to start bloggin' again!

I'm still working with SQL Server, so I'm going to mostly blog about that. I'm still interested in things Japanese, so I'm probably going to blog about that a bit too. I've also developed an interest in motorsport in the interval, but for that you'll want to check out The Pit Crew - that's where I write about that!