January 27, 2015 at 9:32 am
Hi all,
Sorry for double posting this (I also posted on the msdn forum with no definite answer)
I have posted a similar question before but did not get a clear answer. My question is, After breaking the mirroring database to allow testing on the DR site, is the only way to re-establish the mirroring again to copy the backups from the actual production site ?. Can something be done on the DR site to put back the mirroring after the test ?. We can not take the production site down for this test. Thanks
SQL Server 2012 SP2
Windows 2008 R2.
January 27, 2015 at 10:46 am
Calvin Duffy (1/27/2015)
My question is, After breaking the mirroring database to allow testing on the DR site, is the only way to re-establish the mirroring again to copy the backups from the actual production site ?.
What is the mode of the mirror sesasion and do you have a witness?
Why did you feel you had to break the mirror?
Have you taken a full backup on the primary since breaking the mirror?
Calvin Duffy (1/27/2015)
Can something be done on the DR site to put back the mirroring after the test ?. We can not take the production site down for this test. ThanksSQL Server 2012 SP2
Windows 2008 R2.
Even if you do re establish the mirror session it doesnt involve taking down the primary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2015 at 11:00 am
The mirroring type is "High Performance (asynchronous)". There are no witness server(s). Need to write to the database (Mirroring db not the primary db). Can't do role switching. Can't take the Prod db offline. I wish I made the rules.
January 27, 2015 at 12:35 pm
If you break the mirror and set partner off, the LSN needs to get back into sequence for mirroring to pick back up. We've always just done the full sequence to re-establish the mirror since we didn't mind the delay in getting the mirrored DB re-established (a full day in some cases based on size and time to copy).
Interesting question though. I'm going to try the below to test if you can use a snapshot. I'm not hopeful though since I don't think a snapshot retains any LSN info.
1. Establish the mirror
2. Take a snapshot of the DR side
3. Break the mirror
---prevent additional full backups from taking place in primary during testing
---continue log backups on the primary and copy them to the DR side
4. Do the testing (I'm just going to insert a record or two on the DR side)
5. Restore the DB from the snapshot with recovery (not sure if this is even possible)
6. Restore log backups from the primary side
7. re-establish the mirror
January 27, 2015 at 1:00 pm
S. Kusen (1/27/2015)
If you break the mirror and set partner off, the LSN needs to get back into sequence for mirroring to pick back up. We've always just done the full sequence to re-establish the mirror since we didn't mind the delay in getting the mirrored DB re-established (a full day in some cases based on size and time to copy).Interesting question though. I'm going to try the below to test if you can use a snapshot. I'm not hopeful though since I don't think a snapshot retains any LSN info.
1. Establish the mirror
2. Take a snapshot of the DR side
3. Break the mirror
---prevent additional full backups from taking place in primary during testing
---continue log backups on the primary and copy them to the DR side
4. Do the testing (I'm just going to insert a record or two on the DR side)
5. Restore the DB from the snapshot with recovery (not sure if this is even possible)
6. Restore log backups from the primary side
7. re-establish the mirror
5. Restore the DB from the snapshot with recovery (not sure if this is even possible)
You can restore the database from snapshot with recovery only. Although you can include the statement "NORECOVERY" and it will not error out, it will still restore it with recovery.
6. Restore log backups from the primary side
You can not do that anymore since the database on the step 5 restored with RECOVERY.
We have tried and got one way working with a different database name without breaking the mirror.
- Take the DR instance offline
- Copy and rename the mdf and ldf files
- Attached the renamed files with sp_attach_db as a different database name.
- Have the application people test it on that without interrupting the original mirroring database.
January 27, 2015 at 2:24 pm
5. Restore the DB from the snapshot with recovery (not sure if this is even possible)
You can restore the database from snapshot with recovery only. Although you can include the statement "NORECOVERY" and it will not error out, it will still restore it with recovery.
6. Restore log backups from the primary side
You can not do that anymore since the database on the step 5 restored with RECOVERY.
Thanks for that follow up. I was just noticing that messing around here. I didn't think that a snapshot touched LSN's so that is pretty much confirmed.
January 29, 2015 at 9:37 am
To further this discussion about trying to use a snapshot, you cannot recover the database if a snapshot exists of the DB while it is in mirroring mode. You must drop the snapshot and then you can bring the DB online. Doesn't really help OP, but I wanted to follow up on my theoretical test (that had already been debunked anyway).
January 29, 2015 at 9:48 am
Calvin Duffy (1/27/2015)
The mirroring type is "High Performance (asynchronous)". There are no witness server(s). Need to write to the database (Mirroring db not the primary db). Can't do role switching. Can't take the Prod db offline. I wish I made the rules.
If you havent taken a full backup on the primary since mirroring broke you can resynch the LSN gap with a differential backup, instead of having to start from fresh
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2015 at 10:26 am
Perry Whittle (1/29/2015)
Calvin Duffy (1/27/2015)
The mirroring type is "High Performance (asynchronous)". There are no witness server(s). Need to write to the database (Mirroring db not the primary db). Can't do role switching. Can't take the Prod db offline. I wish I made the rules.If you havent taken a full backup on the primary since mirroring broke you can resynch the LSN gap with a differential backup, instead of having to start from fresh
"...........................since mirroring broke you can resynch the LSN gap with a differential backup," ----> That is if you alreay copied the FULL backup again to the DR server. If you make a backup from the DR server and restore it with "NORECOVERY" and then try to apply the Diff backup, it will not work. The Full backup must be from production. The Idea here was not to re-copy full backup from production again (In our case, it is too big and the network is too slow and the mirroring was established 8 months ago).
January 29, 2015 at 11:13 am
So, has a full backup been taken on the principal since the mirroring broke?
Use this query against the principal and then the mirror to ascertain if it has or not
select DB_NAME(database_id), differential_base_lsn
from sys.master_files
where database_id = DB_ID('somedb')
and type_desc = 'ROWS'
group by DB_NAME(database_id), differential_base_lsn
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2015 at 11:26 am
Perry Whittle (1/29/2015)
So, has a full backup been taken on the principal since the mirroring broke?Use this query against the principal and then the mirror to ascertain if it has or not
select DB_NAME(database_id), differential_base_lsnfrom sys.master_files
where database_id = DB_ID('somedb')
and type_desc = 'ROWS'
group by DB_NAME(database_id), differential_base_lsn
No full backup was taken after the mirroring broke. You still can not re-establish mirroring unless you copy the last full backup from production (and the others - Diffs & TRNs) once you broke the mirroring. Making a backup and restoring from DR does not work.
January 29, 2015 at 11:55 am
Calvin Duffy (1/29/2015)
No full backup was taken after the mirroring broke.
If a full backup has occurred on the principal since mirroring broke you're stuck with re initialising mirroring unless you can find all the log backups and apply them in sequence
Calvin Duffy (1/29/2015)
Making a backup and restoring from DR does not work.
That's good because that's not what I suggested 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2015 at 2:20 pm
Perry Whittle (1/29/2015)
Calvin Duffy (1/29/2015)
No full backup was taken after the mirroring broke.If a full backup has occurred on the principal since mirroring broke you're stuck with re iinitializing mirroring unless you can find all the log backups and apply them in sequence
Calvin Duffy (1/29/2015)
Making a backup and restoring from DR does not work.That's good because that's not what I suggested 😉
Once you put the DR database online, how are you going to re-establish the mirroring again without re-copying Full backup from production ?. Are you assuming the last full backup is already copied to DR site ?. If not, there is no Diff or TRN backup will help you to re-establish the mirroring again period.
January 30, 2015 at 5:24 am
no, your original post which i read said you broke the mirror session in this scenario a differential may be used to bridge any LSN gap, seems i had mis interpreted your scenario
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply