SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How do you quiesce SQL database
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/23/2008 :  22:44:09  Show Profile  Reply with Quote
quote:
Originally posted by dsides

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



We don't deal with oracle or other Database Platforms over here. Explain this in Other RDMS forum. Read Booksonline for SQL Server Before you make suggestion about SQL Server.
Go to Top of Page

dsides
Starting Member

USA
4 Posts

Posted - 02/27/2009 :  13:06:44  Show Profile  Reply with Quote
That is a rather narrow vision. I have read the SQL Server Books and have implemented Split Mirror backups for SQL Server using storage vendor supplied software. I was just trying to point out to others in this board that Split Mirrors are a good thing and that it is a common practice.

It benefits those in the SQL Server Community to know what is available on other platforms. That way when you have a chance to request features from Microsoft you know options to suggest. And this has been brought up to Microsoft as a short coming on many occasions.

When I was at the Redmond campus for an Enterprise Customer briefing I witnessed another enterprise customer bringing this up to the SQL Server development team as something they desired and was preventing them from deploying large databases on SQL Server.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/27/2009 :  14:03:35  Show Profile  Reply with Quote
quote:
Originally posted by dsides

That is a rather narrow vision. I have read the SQL Server Books and have implemented Split Mirror backups for SQL Server using storage vendor supplied software. I was just trying to point out to others in this board that Split Mirrors are a good thing and that it is a common practice.

It benefits those in the SQL Server Community to know what is available on other platforms. That way when you have a chance to request features from Microsoft you know options to suggest. And this has been brought up to Microsoft as a short coming on many occasions.

When I was at the Redmond campus for an Enterprise Customer briefing I witnessed another enterprise customer bringing this up to the SQL Server development team as something they desired and was preventing them from deploying large databases on SQL Server.




I think the point sodeep was making is fairly basic:
You don't know what you are talking about.

A "database snapshot" in SQL Server terms has nothing to do with split mirrors or any other hardware/SAN level backups, so basically, everything you said was completely off the mark.

You might try reading about database snapshots in SQL Server 2005 or 2008 Books Online if you want to learn why it is not a good solution for a backup.






CODO ERGO SUM
Go to Top of Page

dsides
Starting Member

USA
4 Posts

Posted - 02/27/2009 :  19:32:41  Show Profile  Reply with Quote
quote:
Originally posted by Michael Valentine Jones


I think the point sodeep was making is fairly basic:
You don't know what you are talking about.

A "database snapshot" in SQL Server terms has nothing to do with split mirrors or any other hardware/SAN level backups, so basically, everything you said was completely off the mark.

You might try reading about database snapshots in SQL Server 2005 or 2008 Books Online if you want to learn why it is not a good solution for a backup.
CODO ERGO SUM





I am sorry that I have offended you and sodeep. I misinterpreted what sodeep was saying, you and he are correct in stating that native SQL Server SnapShots are not a good option for DR. I was going off of the original question:

quote:
Originally posted by Neil Garcia


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



He was specifically asking about using a storage/hardware based snapshot. rudestyle answered correctly stating that the only way to do what he wanted to do with built-in tools was to take the database offline. NeilGarcia tried to explain what his intentions were after you told him the only way to recover was with backups. Several other people piped in trying to support Neil's intentions. Eyechart even put links back to Microsoft's site attempting to show you what a split mirror backup is (like the original poster was asking about). Here is a link to Microsoft Technet that explains the whole process http://technet.microsoft.com/en-us/library/cc966458.aspx. This has been around at least since SQL 2000, the technet article is from May 31,2002. Eyechart was also correct in pointing out that this is not a native SQL Server function.

quote:
Originally posted by eyechart


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.



Once again you and soodeep were correct in your assessment that Native SQL Server Snapshots are not good for backup or DR. I was simply trying to point out to those who are interested that Hardware based backups of SQL Server is a good workable solution and that is what the original question was about. I do know what I am talking about here. Hardware based backups are in some ways more reliable, faster and more efficient that a standard SQL backup. You will probably take offense to what I have said, you probably don't believe it, but that is your option. Please take the time and read the information provided by Microsoft (listed above) on this type of backup. Combining this with interconnected remote data centers gives you the option to quickly recover from a disaster. I have recovered databases over 20 tb in size in less than 30 minutes using these methods. This method can also allow you to mount your original database to another server and take a physical backup offloading the I/O intensive work of backups to a non customer facing machine. If you have the hardware it is a good way of doing a solid, reliable SQL Server backup. I am not a storage sales person, I am just trying to pass on a good way of doing backups.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/28/2009 :  13:13:00  Show Profile  Reply with Quote
quote:
Originally posted by dsides
...This method can also allow you to mount your original database to another server and take a physical backup offloading the I/O intensive work of backups to a non customer facing machine. If you have the hardware it is a good way of doing a solid, reliable SQL Server backup. I am not a storage sales person, I am just trying to pass on a good way of doing backups.


Backups done by mounting the database on another server to do the backup have limited utility. They may have some value in a disaster recovery situation, but they cannot be used to restore a database to a point in time with transaction logs.

The backup on the offline database happens in a parallel environment where the transaction log backup chain on the production server is not part of the same sequence. That means that after restoring from the offline backup, the transaction logs from the production server cannot be used to roll the database forward.

I specifically asked one of the leading SAN vendors about this when they were attempting to sell me a hardware solution like you are suggesting, and they confirmed that I was correct about not being able to use backups made this way with the live transaction log backups made after the split.

You might be able to recover the database using file level backups made from the split volume, and then applying the transaction logs, but there are well know issues with being able to attach database files that were not properly detached.

The only scenario for recovering to a point in time is to restore all the server volumes from the split volumes and roll forward from that point with the transaction logs. This may be OK for some situations but you are basically committing to recovering a whole server, instead of a database.

I think anyone planning for a disaster recovery should be aware of the limitations of methods that depend on recovering databases from volume level backups and decide if they are really worth it. In any case, if your planning does not include normal database backups, you are asking for trouble.



CODO ERGO SUM
Go to Top of Page

dsides
Starting Member

USA
4 Posts

Posted - 02/28/2009 :  23:13:05  Show Profile  Reply with Quote
You obviously understand the traditional SQL Server backup and recovery methods well. Your company is lucky, as you probably are aware there are many people being used as a dba that are not a true dba; especially with SQL Server (that is a whole other rant, I constantly argue with the Oracle fanatics that SQL is a great database, no matter what they think).

quote:
Originally posted by Michael Valentine Jones
The backup on the offline database happens in a parallel environment where the transaction log backup chain on the production server is not part of the same sequence. That means that after restoring from the offline backup, the transaction logs from the production server cannot be used to roll the database forward.



You are correct that type of backup; where you mount the database to a secondary server and then backup only being good for a single Point in Time and cannot be used to roll forward in SQL Server. But read on below and I'll explain how this can be overcome as well by using your mirror as your primary backup (I would only suggest this method if your mirror is being replicated to a geographically remote site). I know of many companies using this method with VLDBs (Very Large Databases) primarily because of the time it takes to create a database backup can take more than 24 hours. With those type of environments they sometimes use multiple mirrors so that they have rolling recovery points so that they only have to use logs to roll forward for a very short period. They sometimes will also use a mirror to make a cold backup to tape to send off to a third location for government mandated retention.

quote:
Originally posted by Michael Valentine Jones
I specifically asked one of the leading SAN vendors about this when they were attempting to sell me a hardware solution like you are suggesting, and they confirmed that I was correct about not being able to use backups made this way with the live transaction log backups made after the split.



I am curious to which storage vendor you were working with. Hitachi, Compellent, SUN and HP (not sure about EMC, but I would think they have the same thing to be competitive) all have utilities to support PIT recovery with the combination of split mirror backup and transaction logs. With SQL Server you have to use the utilities the storage vendor sells (unless you are preversly familar with C++ and the Microsoft VDI api).

What happens is the mirror is split with the database in a quiesced state; for SQL Server, storage vendors use the Microsoft VDI api calls to do this, threfore driving the cost of this method up for SQL Server (other database vendors do this natively which was what my original rant was about); this overcomes the problems you correctly described where there have been problems in recovery when the database files are not properly detached. The vendors also make calls to the SQL Server backup api that ensures that the proper checkpoints have been made when the mirror was created, this allows you to use the transaction logs from your existing database to roll forward to the current point in time. If you are interested in this process check out Hitachi's product called Protection Manager.

This same process with the VDI/VSS api calls are used to get consistent backups for Exchange server.

When Neil Garcia asked the question originally he was probably accustomed to doing this process natively with either Oracle or DB2, both of which provide a sql alter statement that allows you to put the db in "hot backup mode" or "quiesced state" (this is where the vendors use the Microsoft VDI/VSS api calls to SQL Server). After the split is taken which generally is only a couple of seconds you have a point in time to recover from a stable copy of your database; In the vendor products the process of bringing the log files curent are handled for you during the split, but in Oracle after the split is made and you end "hot backup mode" your split mirror backup script would need to "alter system archive log current;" this forces the database to make new archive/transaction logs from that point forward therefore giving you consistent logs that match your mirror if a PIT recovery is needed.

In a multi array or multi datacenter environment the PIT mirror is usually transferred to the recovery array every few hours, in what is called a consistency group, but the transaction logs are continuously replicated to the recovery array allowing you to have the current logs up to the point of disaster in which you can use to roll forward to a point in time on the recovery machine.

When I implemented my first split mirror backup I kept another database up doing log shipping for months until I was comfortable doing this type of recovery. Doing this also allowed us to test recovery and rolling forward a lot more than we would have without a split mirror. We also found uses for split mirrors outside of DR. We started using split mirrors for QA, DEV and reporting environments. On the smaller dbs we could take a split of a database when an application error occured, allowing developers to troubleshoot on real data, without the worry of harming the production database. And we could do all of this without effecting the performance and never taking the live database down.

To be honest with you I didn't trust it myself until I had implemented it. This method is actually more reliable and provides much faster recovery than the traditional backup methods; especially when replicating to geographically diverse data centers.

If someone has the hardware or budget for this I would hate for them to pass this up, especially considering the safety that it brings you. If nothing else it is worth doing for the ability to test recovery and to train new dba's on how to recover a database, which many of them don't get a chance to do until they have to do it for real.

Out of curiosity who was your storage vendor? Another problem I have with many of the storage guys is that they usually do not understand how to properly configure SANs for max performance with databases.
Go to Top of Page

JeffK95z
Starting Member

Canada
19 Posts

Posted - 12/14/2011 :  11:49:56  Show Profile  Reply with Quote
anyone ever get more info on this?

I'm currently trying to get SAN level snapshots using IBM v7000.

Goal: Provide point in time copy of our production 2TB DB to a research and statistics department.

Current method is:

  • Backup 2TB DB (lives on SSD's) to 4 sets of SAS disks. - 30 minutes

  • Remap the 4 backup disk LUN's to research SQL Server - 1 minute

  • Restore to large pool of SAS disks - 1.5 hours



total time is 2 hours, but we want it faster so we can do multiple 'restores' during the day if needed

Thanks in advance!
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 12/14/2011 :  12:43:57  Show Profile  Visit robvolk's Homepage  Reply with Quote
If you truly need point-in-time recovery, SAN snapshots probably won't meet your needs (as you're already experiencing). If they're just doing research and can manage with read-only copies of data, look at log shipping. It's perfectly suited to such a scenario.

For future reference please post a new topic for questions, rather than add to a 2 year old thread.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000