Voice of the DBA

Tag: administration

Stress Testing

Many of the DBAs that manage production systems will at some point determine what level of hardware is needed to support a workload. Whether this is a physical server purchase or a cloud “rental”, someone has to decide what hardware is needed. How many cores, the amount of RAM, the number of disks, which hopefully correspond to some level of IOPs, and more. Even in the Azure SQL Database world, you must decide what database capacity you will pay for.

Since this is a big decision, and changes can be hard to make, many DBAs overbuy hardware. After all, no one wants to have a slow server. This is true for Azure as well, at least for many people I know. While changing from an S3 to a P2 is quick and easy in the Azure portal, it’s not such an easy sell to management. If they’ve budgeted $150/month and you tell them we want to go to $900/month, the technical change is the easiest part of this.

As a result, I’m surprised that we don’t really have better ways to determine if hardware will support our workload. I see this question asked all the time, and although there are tools and techniques suggested, I’ve yet to see many people have a set, known standard way of evaluating hardware and a particular workload.

One one hand, I think there should be better tools to do this, whether from Microsoft or someone else. I suspect since this is such a rare activity and businesses have been willing to overbuy hardware (or deal with substandard performance), that there isn’t any large impetus to solve this issue.

However I wanted to ask if any of you actually stress test hardware? Either your current hardware or new purchases. If you don’t know what your level your current hardware performs at, how do you compare that to new hardware?

Do you have a way to replay and measure a workload? Do you have the time to do so when new hardware arrives? Is there a documented method you use? Apart from discussing this today, I’d love to see some articles that detail exactly how you test hardware from a technical tool perspective, and then a followup that examines and evaluates the results.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

April 29, 2016
What’s Your Downtime?

That’s the question this week: What’s your downtime?

I thought about this as I read this piece on Azure’s downtime being greater than its rivals in 2014. You do need to provide an email to read the article, but essentially the article builds on tracking from CloudHarmony to show that Azure had more downtime for the year, with about 54 hours. In Europe, that high water mark was 5.97 hours for compute and 1.31 hours for storage, so the European Azure cloud is doing better than the others.

That’s pretty good. Certainly individual machines went down, and services were unavailable for short periods of time during failover, but keeping a hardware platform up around 5 hours of downtime a year is good. I’m not sure that many of my companies have done this, though to be fair, mostly it’s been patches from Microsoft that caused downtime for Windows machines.

However, let’s look at your database instances. Do you know what your downtime is? I’d encourag you to track it, and be sure that you report on it. Or at least have the data, in case the boss asks. I don’t know what our SSC total downtime is, but I can tell you that our cluster rebooted in Aug 2015 and Mar 2016, brief outages for some patching. In the past, I’ve typically seen our database instances run the better part of a year, usually only coming down for a Service Pack.

If you have monitoring in place, and you should, then I hope you have some idea of downtime for your main databases. If you can share, see if you might set the high water mark for the SQLServerCentral community in the last year.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

April 15, 2016
Restoring a Striped Backup–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I uncompressed some SQL Backup Pro files. Since multiple threads were used to make the backup, I ended up with multiple files in my file system, as shown here:

Each of these is part of a striped backup, a piece of a backup file. To restore the backup, I need all the files to reassemble the backup. This is fairly simple, but you should be aware of how this works and how to perform a restore.

In my case, you can see I have 7 files for each database. They are the same name with an _0x at the end, with x being the number of the file.

To restore these, I go to SSMS and click the restore choice. That gives me my restore database dialog, where I can select that I’m restoring from devices. As you can see below, no devices (files) are selected.

I then click the ellipsis to get a dialog allowing me to add files.

Hopefully you’ve done this before, and you can click “add” to add files. You need to naviate to the location of your backup files if it isn’t the default.

Next you can select the files. Holding down CTRL, I can multi-select files.

Once I pick them, I click OK and then I see them all in the device dialog.

Now I click OK and SQL Server reads the headers, and I end up with a single database to be restore, as shown below.

Now, I can click OK, and often do in development areas. HOWEVER, if you are doing this in production, please, please, please, click the Script button instead. You’ll get a new query window, and you can cancel out of this dialog to see the code.

From here, you should click “Save” and save this, then execute it.

As you can see above, the statement is simple. List each disk file, separated by a comma. Then the rest of the RESTORE statement is a standard restore.

SQLNewBlogger

This is a fairly simple task, one I’ve done dozens of times, but it’s worth practicing. If you want to write about it, what happens if you’re missing a file? What if you change the order of files? This was a 10 minute blog, and it’s a good chance to play and practice your restore skills, which are as important as anything you do as a DBA.

March 7, 2016
Creating a Database Snapshot
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.

However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick "create database xx" syntax I often use, with a snapshot I need to be more specific.

The big item that I must specify is the location of the snapshot file. This is the file that gets written to with the copy-on-write process that ensures the original state of the pages when the snapshot is created are still available.

You do need to give the database a logical name as well, which can be anything, but the reference below has suggestions. I’d say that this is bad:

CREATE DATABASE sandbox_snapshot
ON (NAME = MySnap
, FILENAME = ‘h:\SQL Server\MySnap.ss’
)
AS SNAPSHOT OF Sandbox

But this is better

CREATE DATABASE sandbox_snapshot_20150122_1345
ON (NAME = SandBox_Snap_20150122
, FILENAME = ‘h:\SQL Server\MySnap.ss’
)
AS SNAPSHOT OF Sandbox

Because the snapshot is based on an existing database, at a particular point in time, it’s useful to specify the time when the snapshot was created, if possible. While you can get this from metadata, if you have people that look to multiple snapshots for information, it can be handy to know when each is from.

However if you are providing this on a schedule, like daily, for people to report from, you might need to have the same name every day. Think about this, as you cannot rename a snapshot once it’s created.

SQLNewBlogger

When I realized I had to lookup the syntax, I took a few notes and captured code, which meant I combined this writing (< 10 minutes) with other work I was doing.

You should do the same. When you tackle something new, take screenshots, save code, and drop it in a OneNote/EverNote/etc notebook for your weekly blog writing.

Reference

The references I used:
- Create a Database Snapshot – https://msdn.microsoft.com/en-us/library/ms175876.aspx
January 25, 2016