February 1, 2011 at 1:08 pm
Hi everyone,
Is there a way to put a database or entire server on a write suspend mode?
A little explanation on what we are trying to accomplish...we have a high transaction database (24/7 operation) that we need to make a copy of and mount on another server for reporting purposes. The database resides on a HP P4300 SAN, and we are trying to use HP's clone technology to make a clone of our database. However, we need a consistent copy of the database and since the database is always performing DML operations, I'm trying to figure out how we could accomplish this. Thanks in advance and sorry for the ambiguous post.
February 1, 2011 at 1:17 pm
Backup and restore is a consistent way to get a copy of a database. While a few transactions my ultimately be rolled back when the database is recovered at the end of the restore, the data will be consistent.
If you have another definition of consistency based upon some applications procession the only way would be to suspend processing while taking a backup then restart processing when the backup is complete.
The probability of survival is inversely proportional to the angle of arrival.
February 1, 2011 at 1:23 pm
either you can do database snapshot
secondly,
you can do backup and restore that won't hurt your user current running DML operations.
Backup runs checkpoint and don't take any locks on database and don't hurt your users.
As checkpoint is runned there will be consistency in backup.
February 1, 2011 at 1:45 pm
Those options will not work for us. The process from the HP SAN side is as follows:
1. Somehow put the SQL Server databases on write-suspend mode (or some other way of getting SQL Server to present a consistent-state database to us).
2. Make a thin provisioned LUN and present it to the 2nd server.
3. Allow writes to resume on the 1st server.
4. Bring up the database on the 2nd server.
It is the step 1 of this process that I'm having difficulty with. So, lets say that at midnight we want to make a copy of the database available to server 2 (using the process above), but there are transactions happening. Is there a way to have SQL Server to hold off writes and give us a consistent database to play with?
Currently, we are doing this on an EMC SAN, but we are moving to HP, so there has to be a way to do it, I am just not sure how EMC is doing it.
February 1, 2011 at 1:51 pm
The short answer is no. You will have to quiesce your application to insure no writes are taking place.
You can put the database in read only mode, but I don't think that will not have the effect you desire because without participation from your clients they will just start reporting errors of one sort or another.
The probability of survival is inversely proportional to the angle of arrival.
February 1, 2011 at 2:01 pm
Thanks sturner, I was afraid of that answer.
Any creative workaround(s)?
February 1, 2011 at 2:05 pm
shahgols (2/1/2011)
Those options will not work for us. The process from the HP SAN side is as follows:1. Somehow put the SQL Server databases on write-suspend mode (or some other way of getting SQL Server to present a consistent-state database to us).
2. Make a thin provisioned LUN and present it to the 2nd server.
3. Allow writes to resume on the 1st server.
4. Bring up the database on the 2nd server.
It is the step 1 of this process that I'm having difficulty with. So, lets say that at midnight we want to make a copy of the database available to server 2 (using the process above), but there are transactions happening. Is there a way to have SQL Server to hold off writes and give us a consistent database to play with?
Currently, we are doing this on an EMC SAN, but we are moving to HP, so there has to be a way to do it, I am just not sure how EMC is doing it.
SQL Server supplies an API that the SAN vendor can use to quiesce the database and that is what EMC uses to do this.
You need to talk to the SAN vendor (HP) about this. They should be able to supply the necessary software and procedure for doing this with a SQL Server database.
February 1, 2011 at 3:00 pm
Have you thought of Transactional Replication
February 2, 2011 at 4:53 am
In addition to what the others have suggested, what about log shipping or mirroring?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply