?
Solved

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

Posted on 2006-05-11
4
Medium Priority
?
873 Views
Last Modified: 2008-01-09
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-
0
Comment
Question by:-M-
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16671186
>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
0
 
LVL 1

Author Comment

by:-M-
ID: 16682493
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-
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 520 total points
ID: 16682585
as I did not work with sybase yet, but only alot with SQL Server (which had the same engine some time ago), and also work alot with oracle, let me confirm my statment for those 2 then...

regarding the snapshot database:
how to create a snapshot:
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
http://msdn2.microsoft.com/en-US/library/ms175876.aspx

as you noticed, this works indeed with sparse files.
Actually, you are right about that one cannot backup the snapshot database, indeed it is considered a backup itself of the database...



0
 
LVL 1

Author Comment

by:-M-
ID: 16699696
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-
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question