October 4, 2012 at 8:49 am
Out current production backup stategy is nighly backup with tran log backups every 15 mins though out the day. This is currently a 2000 instance.
We will be porting to 2008 R2 with the same backup stategy. In the long term we want to establisha mirror to our DR site. I am comfortable establishing a mirror to that site and would only be used in the most catasophic situation. The pipeline to the DR site will be small to start with and changes have been made to avoid large spikes in the tran log files.
My questions are.
Is it okay to still backup the principal db daily and still have tran log backup every 15 minutes thru out the day and what effect will that have on the mirror?
If the principal has to be restored to a point in time using the daily backup and tran log files what effect will that have on the mirror?
Thanks in Advance
October 4, 2012 at 9:43 am
TJ-356724 (10/4/2012)
Is it okay to still backup the principal db daily and still have tran log backup every 15 minutes thru out the day and what effect will that have on the mirror?
With the mirror session in place you will need to take regular transaction log backups.
TJ-356724 (10/4/2012)
If the principal has to be restored to a point in time using the daily backup and tran log files what effect will that have on the mirror?Thanks in Advance
If you wanted to restore the Pincipal to an earlier point in time then you would need to re create the mirror session.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 4, 2012 at 10:17 am
Thanks Perry
So that I am clear.
Our non mirror environment is a full backup at 1:00am with tran log backup every 15 mins from 6:00am thru to 11:00 pm.
When the mirror is established do that schedule on the principle? The mirror will be using asychn.
If the principle fails and requires a restore the mirror must be terminated, the database restored to the point in time required. Is that correct? In a asychn environment does the morning backup and tran log backups bother the mirror?
October 4, 2012 at 12:06 pm
TJ-356724 (10/4/2012)
The mirror will be using asychn.
This only alters the way the data is committed between the Principal and Mirror.
TJ-356724 (10/4/2012)
If the principle fails and requires a restore the mirror must be terminated
You cannot restore the Principal to an earlier point in time and then expect the mirror session to resume with the Mirror synchronised to a later point in time
TJ-356724 (10/4/2012)
In a asychn environment does the morning backup and tran log backups bother the mirror?
What is it you think the backup chain will break, please explain?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2012 at 5:53 am
Hi again Perry
I was just curious if any type of data was being passed to the mirror via a trn log backup or full db backup and how the mirror view the fact that a tran log or full backup was taking place on the principle.
October 5, 2012 at 5:59 am
And by the way Perry there is no witness
October 5, 2012 at 6:07 am
Hi TJ,
Yes its required you take a Transaction backup of Principal server & restore in mirror then it will easy to reconfigure mirroring and if you not restore Tran backup you will get error while configuring...
Satish
October 5, 2012 at 6:13 am
Thanks Satish
I was referring to trans log backups and full backup of the principle after the mirror was established. I apologize fro not making that clear.
October 5, 2012 at 6:28 am
TJ-356724 (10/5/2012)
And by the way Perry there is no witness
you generally won't have in asynch mode 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2012 at 6:36 am
TJ-356724 (10/5/2012)
I was just curious if any type of data was being passed to the mirror via a trn log backup or full db backup
No, that's not how mirroring works, Log Shipping will send log backups but miroring won't. Even in Log Shipping Primary backups do not affect the LS plan.
Books Online details the following
SQL Server Books Online
Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.
TJ-356724 (10/5/2012)
and how the mirror view the fact that a tran log or full backup was taking place on the principle.
The LSN marker points will be synchronised between the principal and the mirror, in essence the mirror will know when a full, diff or log backup is taken on the principal but it doesn't affect the session
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2012 at 6:56 am
Thanks,
I just want to make sure that we have a plan other than the mirror for the principle. We would view using the mirror as the absolute last resort (example: the building burns down in the middle of the night.) We want to immediately address any corrupted data issue (or any other issue) locally on the principle via a restore of the nighly full backup and then applying trn files if needed up to certain point in time if required and knowing that the mirror will be broken. The issue is the the current pipeline (bandwidth) to the mirror is small(tiny) to move significant data from the mirror that would be needed.
From a safety standpoint I agree that synch should be use but others sure do like the asynch performance.
Any thoughts?
October 5, 2012 at 7:57 am
If you ever had to restore the principal back to a prior point in time the mirror state would no longer match the principal.
If anything happens to the principal you fail to the mirror, when the original principal is back online you fail back to it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2012 at 8:03 am
Agreed, hopefully it will never happen
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply