Voice of the DBA

Tag: Redgate

  • 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.

  • The Exceptional DBA for 2011

    Once again we’re running a contest to find the Exceptional DBA around the globe. I’ve been a judge the last three years, and it’s been a hard choice trying to pick from some great applications. This year the judges are Brad McGehee, Brent Ozar, Rodney Landrum and me.

    Nominations are now open, and you can nominate yourself, or a colleague for the change to be named the Exceptional DBA of 2011.

    Best of luck to everyone, and a few hints.

    • More writing is better – We don’t want to read a chapter from War and Peace, but take a few paragraphs to describe your environment with some details.
    • Be specific on the highlights. – Let us know what you did, why it’s important or impressive to your organization.
    • Be well rounded – We are looking for people that do more than their job. They teach others, they are proactive, and they make the DBA profession better for everyone.
    • Proofread – Have a friend or even your boss review the nomination form.

    The nominations are open until June 30, so take a week or so and think about how best to present yourself.

  • Clean Code is Easier to Read – SQL Prompt

    I saw a post recently that had query that looked like this:

    select a.*,name, b.*
 from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT'
and
b.grantee_principal_id = a.principal_id

     

    Ugly to read, at least to me, and in a poorly written format. The table, table format isn’t ANSI compliant and isn’t recommended. So I did this:

    formatsql

    A little better, and easier to read, but not great.

    SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a ,
        sys.database_permissions b
WHERE   permission_name = 'INSERT'
        AND b.grantee_principal_id = a.principal_id

    However now I can make a few quick edits. Remove the comma between tables and add “INNER JOIN” and then move the AND clause up to an ON clause to give me this:

    SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a
  INNER JOIN sys.database_permissions b
    ON b.grantee_principal_id = a.principal_id
WHERE   permission_name = 'INSERT'

    Much better, and easier to read.

  • And the winners are….. (iPads for Christmas contest)

    Congratulations to the following winners of our iPads for Christmas contest. It was a hard choice, but I ended up with these winners:

    • CC-597066
    • DataChomp
    • Scott Murray-240410
    • richardn-1128243
    • Michael Lysons
    • chris.cantley
    • Nick Van Dyk
    • barb.wendling
    • hghumphrey
    • MdApache

    I had over a 100 entries to go through, and it  took some time, and a few passes to get down to about 20, then 14, then pick the last 10. These were interesting entries, and while it was close, these seemed to stand out in my mind.

    I’ll be contacting the winners today, and then letting Red Gate know as well and we’ll get those iPad shipments moving.

    Thanks to everyone that entered, and happy holidays to you all.