Voice of the DBA

Author: way0utwest

  • Striping Backups in SQL Backup Pro

    SQL_68x68_BackupThere are times you really do want to stripe backups across multiple files. If you have a short backup window, or a large data set, if you can write the backup to multiple files at the same time, you can drastically reduce your backup window. I think I heard one person say they had a client that was backing up a 2TB database in around 30 minutes with striping. Now that’s cooking with gas!

    My company, Red Gate Software, makes a nice backup product called SQL Backup Pro, which offers a number of features, compression and encryption being the two most notable ones. However I noticed at one point that it supported striping as well, so I decided to play with it.

    I started the backups, picking a database and then getting to the backup file screen.

    sqlbackup1

    There’s a drop down in the upper left, which gives you a few choices. One of these is the backup and mirror and the other is a backup to multiple files.

    sqlbackup2

    I selected the “Split backup into multiple files”, which equates to striping, and then added a file to the file dialog.

    sqlbackup3

    However I wanted to add a second file. I clicked “Add” again.

    sqlbackup4

    It’s nice that the names are changed, but adding a second file to the same path isn’t likely to give me much more throughput on the backup. Both files would write to the same I/O device. I was thinking that “Add” might give me a dialog for a different drive/path, but it didn’t.

    Instead I needed to click on the ellipsis next to the folder (I’ve added the arrow in the image below):

    sqlbackup5

    This brought me to a dialog that shows me the drives my machine can see. I didn’t test UNC paths, but I assume any valid path would work. You can set a path, or you can pick one, and you can even create a new folder. Handy since this could be a remote machine. The SQL Backup Pro client is like SSMS, it’s a client view to a remote service.

    sqlbackup6

    I select the D: drive on my machine, which is actually a separate physical drive.

    sqlbackup7

    I get back to the main dialog, and this time when I click “Add”, I get the desired result. A second file on a different path.

    sqlbackup8

    I run the backup, and sure enough, I have files in two places, each a little over 1MB. There’s some overhead, so it’s not a completely even stripe.

    sqlbackup10

    sqlbackup11

    As a comparison, I’ve highlighted a regular backup I made after this, same db, with one file:

    sqlbackup9

    That seems a little cumbersome, but I’d anticipate that you would only set this up once for each set up databases, and since you can easily script a SQL Backup set of commands (on the last screen) or create template, this shouldn’t be too hard to manage. In fact, the command for multiple databases does support striping as well, as in this example:

    EXECUTE master..sqlbackup ‘-SQL “BACKUP DATABASES [AdventureWorks,AdventureWorks_SSC,AdventureWorks2008R2]

    TO DISK = ”F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\<AUTO>_2.sqb”,

    DISK = ”C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\<AUTO>_1.sqb”

    WITH DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10″‘

    Since striping is usually done with only very large databases, this might not even be necessary. Most databases won’t need striping, and you can set a template for them that backs up to whatever location you wish.

    SQL Backup Pro has some nice features, and allows you to easily manage lots of backups across lots of servers. The compression ratios are nice and it has some great features, like this striping of backups, that can really improve your backup performance.

  • Lesson #6: What CIOs Really Care About

    What Do CIOs Care About?

    I saw a blog by Dave Rosenberg and it was interesting in a few ways. First of all, Dave works for OSDL, which promotes the use of open source software.

    There were a number of basic things that you could probably guess like costs are important, blogs aren’t, and more. But there were two things that I really thought made sense to a SQL Server guy.

    The first was that security was mentioned as a big item, but more importantly, one of the examples was “don’t build SQL Server apps using sa.” I’ve preached against this many times, but still developers want to do it. And I don’t understand why it’s so hard to just create a login and get away from the all access development. Especially given the security issues across all platforms in recent years.

    The second one was something that I think more CIOs should stand behind and not just “talk” about. Hiring and maintaining staff was listed with this comment: “This is my number one issue.”

    And it should be. Retaining staff is a big deal, especially as more and more systems are integrated or even running side by side in new and innovative, and most often, complex ways. Having some knowledge of the journey that got you to a place is important and it’s hard to rebuild that in new people. Making good hiring decisions and then making an effort on a regular basis to be sure those people stay is important in all businesses, large and small.

    Not that you won’t have to turn over staff at times or cut your losses. After all, not every person fits in every job and circumstances certainly change.

    It should just be the exception rather than a regular practice.

    (update, Info World has removed the original blog)

  • Self-aware Instances

    I like the idea of limited self-awareness in our systems.

    I’ve been seeing lots of monitoring solutions written up lately. I saw this blog from Phil Darmanin on SQLServerCentral and we have had numerous articles written over the years on building monitoring solutions. The build vs. buy debate is one that companies regularly have, and if time is an issue, my company, Red Gate, sells a great solution in SQL Monitor that’s worth checking out.

    However most of the solutions I see use a similar architecture. They have a central monitoring server that reaches out to all other servers and gathers information from them, which is rolled up into a database for reporting. That’s fine, but in many cases there isn’t a ton of testing or error handling built into home grown solutions, and when they fail, you lose data for the day. You also either go into fire-fighting mode to get things working, or you just drop data for a few days.

    However I’ve had a different approach in the past. I’ve typically had a set of monitoring scripts that I’ve deployed to each instance, having them run on a schedule that gathers all the data on settings, changes, backups, jobs, etc. for that instance. I then have a central server that just reads that data from each instance, so if I have an issue with my rollup process, I still have the data on each instance. For this Friday, I wanted to ask this question of you:

    Do you like the idea of architecting processes to be more self aware and distributed, or do you prefer some central process that reaches out to control everything?

    It’s an interesting debate, and one that reminds me of an MIT competition years ago. Early in the 90s, there was a competition where different professors were trying to build robots to navigate a space. One team used a central CPU with complex instructions that controlled the legs and eyes and made decisions. The other used smaller, less powerful controllers in each leg and eye and had them “learn” to work together. Both worked well in different ways, but the latter approach always struck me as a more interesting one.

    For your SQL Server, or application code, which approach to you prefer and why? Let us know this Friday.

    Steve Jones

    The Voice of the DBA Podcasts

  • The IF Statement in a T-SQL Query

    I’ve seen quite a few posts from people asking how to do something like this:

    SELECT 
  a.ID
, IF a.MyChar = 'A' THEN 'Success'
  ELSE 'Fail'
FROM MyTable a

    Of course, that doesn’t work in T-SQL, and you’ll get something like this:

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword ‘IF’.

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword ‘THEN’.

    There’s not IIF, no IF( x, then y, else z) construct. There is an IF … ELSE statement, but it’s use in code flows as a control statement such as

    DECLARE @i CHAR(1)
SELECT @i = mychar FROM MyTable

IF @i = 'A'
  SELECT 'Success'
ELSE 
  SELECT 'Fail'

    Instead we have a CASE statement, which is designed to give you multiple choices. In the example above, I’d write:

    SELECT 
  a.ID
, CASE WHEN a.MyChar = 'A' THEN 'Success'
  ELSE 'Fail'
  END
FROM MyTable a

    I can even add multiple “WHEN” clauses if I want:

    SELECT 
  a.ID
, CASE 
    WHEN a.MyChar = 'A' THEN 'Success'
    WHEN a.MyChar = 'B' THEN 'Close'
    WHEN a.MyChar = 'C' THEN 'Far'
    ELSE 'Fail'
  END
FROM MyTable a

    Let your developers know that when they are looking for an inline IF type of logical statement, T-SQL gives them CASE instead.