But I Don’t Have a Test Server…

I frequently see statements on forums along the lines of “I don’t have a test server, so I’m going to do something I’ve never done before directly on my production servers…” This is usually followed by questions along the lines of:

• But that’s OK and I won’t get fired, right?
• So how do I know if it worked?
• Is that dangerous?

The replies are:

• No. You should be fired
• You won’t
• By All the Gods! Yes! It’s stupidly, insanely, I don’t really like my production server, my data, my boss, or my job, dangerous. Yes.

I understand. You’re working for a not-for-profit, so you can’t afford tons of new servers. You’re looking at a 50tb production server of which, you can’t make a copy. Your bosses just don’t want to spend the money on servers for testing. So these are valid reasons to just skip any semblance of testing or assurance that what you’re doing is good, bad or indifferent for your servers, right? No. No, no, no. 10,000 times, no.

If you’re doing something for the very first time in production, you’re going about your job wrong.

You need to set up a test machine of some type, even if it’s just a tiny VM on your laptop. At a bare minimum, this test machine should have a copy of the structure of your production database. This means you can at least validate any script that’s going to modify structures will compile and run without error. After that, you should strive to have a representation of production data. This doesn’t have to be an exact copy. In fact, it shouldn’t be an exact copy. You should clean any sensitive data prior to using it for testing. Barring that, you should at have, again, this is part of the bare minimum, a copy of your production statistics applied to this test database.

To get a copy of statistics from within SQL Server Management Studio (SSMS), right click on the database in question and select Tasks. Within the Tasks drill down menu select Generate Scripts. You’ll get a simple wizard. Script the entire database. On the Set Scripting Options screen, you’re going to choose Advanced. There, you’ll scroll down until you see Script Statistics. By default, this will be disabled. You’re going to select Script Statistics and Histogram. From there, generate your database script. You can now run this script to create a copy of your production database.

After you create your brand new test database, I’d suggest turning off Auto Update Statistics. If you get a statistics update event to fire through any of the automatic means, it’s going to see that you have no data and will replace your production statistics with empty ones. Done.

You now have the bare minimum of a test database and you no longer have any excuses for not validating your scripts prior to running them on production.

Want to talk in person about query tuning? I’m doing two different all day pre-conference seminars. The first is in Las Vegas at Connections on September 14th. Click here now to register. The second is at SQL Saturday San Diego on September 18th. Let’s get together and talk about query tuning and execution plans.