asked on

SQL Server 2005: How to Suspend DB Transactions for a Split-Mirror Backup Strategy

In our environment, we use Sybase and SQL Server 2005. With our Sybase setup, we use a split-mirror backup strategy, utilizing our SAN, and it works very well. We don't use third party software to perform the split-mirror, but instead use command-line tools to do so. Before splitting the mirror, we run a "quiesce database" command on the necessary databases to suspend all transactions so the DBs are in a stable state when the split occurs (prevents torn pages, etc.). Being able to quiesce the databases is what helps us avoid having to use third-party tools to do the split (the third party tools would do something similar at a deeper level).

My question is how to recreate this setup in SQL Server 2005 without paying for a third-party software that utilizes VDI (Virtual Device Interface)? I'm looking for the SQL Server 2005 equivalent of "quiesce database" so I can suspend all transactions before performing the split of the mirror.

(P.S. I realize there are a number of different strategies available in '05 like mirroring, etc. However, in this question I would like to avoid discussing other methods and strictly discuss how this one would be implemented if it were chosen).

I look forward to learning from your expertise!

-M-

Guy Hengel [angelIII / a3]

>we run a "quiesce database" command on the necessary databases to suspend all transactions
AFAIK, this is a common misunderstanding.
transactions are NOT suspended, but are stored differently, so any "torn pages" can be recovered later...

In SQL Server, there is a SNAPSHOT DATABASE which gives you a snapshot of the database which you could backup then apart from the main database (although there is a minor overhead on it <2-3%)

if you don't want to use third part, develop your own:
http://www.microsoft.com/downloads/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&DisplayLang=en

-M-

ASKER

angelIII,

Thank you for your comments! Please see mine below...

> AFAIK, this is a common misunderstanding. transactions are NOT suspended, but are stored differently, so any "torn pages" can be recovered later...

To clarify, here is a snippet of the Sybase books online: "quiesce database used with the 'hold' keyword suspends all updates to the specified database. Transactions cannot update data in suspended databases, and background tasks such as the checkpoint process and housekeeper process skip all databases that are in the suspended state."

> In SQL Server, there is a SNAPSHOT DATABASE which gives you a snapshot of the database which you could backup then apart from the main database (although there is a minor overhead on it <2-3%)

I apologize, but I'm afraid I didn't completely understand the above statement. Could you please clarify? My understanding of database snapshots is that they're simply a view of a point in time accomplished through a copy-on-write technology. The snapshots themselves are just sparse files containing the original copy of pages that have been changed since the snapshot was made. The snapshots are entirely dependent on the database from which they're built, and the BOL states that "You cannot back up or restore snapshots." I'm afraid that even if these were able to be backed up, it wouldn't provide me with the full capabilities I'm looking for. The split-mirror backup strategy has the advantage of backing up everything on the disks (SQL installation, all databases including system DBs, etc.) while this type of approach, if it were possible, would strictly provide me with a database level backup of only my user databases.

>if you don't want to use third part, develop your own:

Thank you for bringing up this potential workaround. I'm afraid I'm not much of a programmer, but I'll keep this in my back pocket in case it's my only option for accomplishing this task!

I appreciate the comments and your willingness to help.

-M-

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

-M-

ASKER

Thanks again for the comments. It would appear that if I want to achieve a true split-mirror backup strategy, I'll either have to develop it myself of buy the third-party tool.

:-(

-M-