Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How do you quiesce SQL database

Author  Topic 

neilgarcia
Starting Member

5 Posts

Posted - 2007-07-25 : 08:19:42
How do you quiesce SQL database so that I can take a storage based snapshot?

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-07-25 : 08:23:37
quote:
Originally posted by neilgarcia

How do you quiesce SQL database so that I can take a storage based snapshot?



You can take it offline, or just stop the server all together.
Go to Top of Page

neilgarcia
Starting Member

5 Posts

Posted - 2007-07-25 : 08:28:59
Thanks rudesyle, but is there a way to do it while online. The system is 24x7 and taking it offline is not very practical.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 08:32:26
quote:
The system is 24x7 and taking it offline is not very practical

Then why do you want to quiesce it ? You should use BACKUP DATABASE command to backup your database.


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

neilgarcia
Starting Member

5 Posts

Posted - 2007-07-25 : 08:39:50
khtan, I do not want to backup the database. I just want to take a storage based snapshot so that I can replicate the data to another site using the storage utilites.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-25 : 09:18:21
If you want to be able to do a disaster recovery, you need to do database backups.




CODO ERGO SUM
Go to Top of Page

neilgarcia
Starting Member

5 Posts

Posted - 2007-07-25 : 09:47:31
Michael, backups will only provide you with some level of data protection not business continuance. In the event of a disaster, fire or natural calamity your backups are no good because there might be no server to restore data to or even a data center to house the server. A DR site will give you business continuance because it allows you to move your IT operations somewhere else.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-25 : 10:15:57
quote:
Originally posted by neilgarcia

Michael, backups will only provide you with some level of data protection not business continuance. In the event of a disaster, fire or natural calamity your backups are no good because there might be no server to restore data to or even a data center to house the server. A DR site will give you business continuance because it allows you to move your IT operations somewhere else.



That makes no sense to me. If you do not have a plan to have servers or even data center available to use, I do not see how you can plan to do a disaster recovery.







CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-25 : 10:27:43
these are your options:

1. full backup once a day, diff backup twice a day, transaction log backup every 15 minutes.
2. replication
3. log shipping
4. database mirroring

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-25 : 10:40:07
Some SAN replication tool can copy open files, or look at XOsoft's WANSync HA for sql.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-25 : 10:48:04
This whole thing sounds like a load of BS that some SAN vendor is selling. I would not trust any DR solution that depends on replicating data at the volume level to produce usable databases.

You must have backups, replication, log shipping, or mirroring to do disaster recovery. You need backups in any case.




CODO ERGO SUM
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-07-25 : 10:57:34
you would quiesce the database to get split mirror backup or snapshot as the original poster has asked. This is a fairly common practice in Oracle and DB2 land.

Anyway, to make this happen you need to use the software provided by your SAN or other storage vendor (veritas for example). I am not aware of a way to quiesce the database using a simple TSQL command. The software interacts with SQL VDI to make things work.

HP, EMC and HDS all have methods to make this work with SQL Server. You can also use the veritas solution if you have other SAN hardware and/or just want to use veritas.

Here are some links:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/spltmirr.mspx#EYC
http://seer.entsupport.symantec.com/docs/286858.htm



-ec
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-07-25 : 11:04:19
quote:
Originally posted by spirit1
1. full backup once a day, diff backup twice a day, transaction log backup every 15 minutes.
2. replication
3. log shipping
4. database mirroring




There are other solutions:

1. split mirror backup
2. snapshot backup
3. remote volume replication

Of course you might also want to implement a standard sql backup solution to complement these other possibilities. But with a VLDB sometimes that is difficult - especially if you need to be able to fully recover the database in a short time period.

To clarify though, these backup solutions are not available with a standard SQL Server installation. You need 3rd party software and/or hardware to implement.



-ec
Go to Top of Page

neilgarcia
Starting Member

5 Posts

Posted - 2007-07-25 : 11:23:03
Thanks for pointing that out, eyechart. We've been successful with PL/SQL using scripts provided by the vendor, it's MS SQL that's a problem. We might have to go and acquire the vendor utility that will do these things for us.
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-07-25 : 11:30:45
quote:
Originally posted by neilgarcia

Thanks for pointing that out, eyechart. We've been successful with PL/SQL using scripts provided by the vendor, it's MS SQL that's a problem. We might have to go and acquire the vendor utility that will do these things for us.



the PL/SQL scripts you run probably also interact with a command line tool from the vendor. This will be the same for a SQL Server implementation as well - the scripting is just a little different.

for example, the veritas tool is called 'vxsnapsql' and you call this via shell script (cmdshell, vbscript, powershell, etc.) to kick off a snapshot style backup if you use veritas storage foundation for windows.



-ec
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-25 : 11:42:21
i also forgot to add
5. Database Snapshots

as an option

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

gsronald
Starting Member

1 Post

Posted - 2008-07-09 : 12:21:03
Can you share the script to me? I would like to take snapshot to perform storage base backup.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-09 : 12:35:45
http://msdn.microsoft.com/en-us/library/ms175876.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-09 : 13:07:40
Database snapshot is not good tool for storage backup. As you original database is down it becomes down too and it is not easy to restore.
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-09 : 23:24:00
>> I would like to take snapshot to perform storage base backup.

Guess you mean SAN snapshot.
Go to Top of Page

dsides
Starting Member

4 Posts

Posted - 2008-12-23 : 21:33:17
quote:
Originally posted by sodeep

Database snapshot is not good tool for storage backup. As you original database is down it becomes down too and it is not easy to restore.



I disagree with this. I have performed numerous split mirror implementations with Oracle and DB2 all successful. I have seen point in time recovery on databases as large as 28tb restored in alternate datacenters in as little as 20 minutes using split mirrors. The original database never goes down; I have no idea where you got that information as it is not true. Many companies around the world are using this technology to create QA, reporting and test databases daily on Oracle, Sybase, DB2 and SQL Server (using third party utilities like Hitachi Data Protection Manager or Veritas).

While it is possible to use VDI to write your own provider in order to perform a quiesce of a database for sql server or use Protection Manager from Hitachi or other tools from Veritas, BMC and Compellent; this is a terrible thing for Microsoft to require. Oracle, Sybase, db2 and I believe even MySQL all provide methods in the database to quiesce the data without involving a third party.

The basic steps for all of the other databases are as follows:
1. sql statement to quiesce the database (put it in hot backup mode, changes go to redo logs I believe then get written to datafiles when the database comes out of hot backup mode)
2. use cli to perform a split of a mirrored disk (in other words snap a copy of the mirror)
3. sql statement to take the database out of hot backup mode.

In Hitachi land it is something like this for Oracle (I am doing this from memory so it will not be exact)

1. SQL: 'ALTER DATABASE BEGIN BACKUP';
2. OS: pairsplit -g dbdiskgroup -IM0
3. SQL: 'ALTER DATABASE END BACKUP';

By doing this the Oracle recovery manager can use the created mirror to bring a duplicate database back on line.

There are more steps involved in setting all of this up initially, but if implemented properly this is a very efficient method of backing up very large databases. The steps above are almost the same for EMC and Compellent both of which I have seen work. Microsoft needs to implement this type of technology out of the box as the other db vendors have. For more information on this topic search google for oracle "split mirror" hitachi
Go to Top of Page
    Next Page

- Advertisement -