March 13, 2014 at 11:52 am
Hi all,
I am configuring DB mirroring on principle database and the database has Full,Diff,TranLog backups job running everyday on Sql Agent.
In order to configure Mirroring on principle database should I disable/delete TranLog backup job on Sql agent, just like Log shipping? or I can have mirroring and Log backup job going on principle or I can't have both same time?
March 13, 2014 at 11:59 am
mirroring is not like logshipping, it does not have its own log backups and copy over logs, it replicates at the transaction level. Therefore you MUST continue with your log backup jobs else the transaction log will fill.
---------------------------------------------------------------------
March 13, 2014 at 12:13 pm
Thank you for your input and quick response. I really appreciate it.
March 13, 2014 at 3:36 pm
smtzac (3/13/2014)
Hi all,I am configuring DB mirroring on principle database and the database has Full,Diff,TranLog backups job running everyday on Sql Agent.
In order to configure Mirroring on principle database should I disable/delete TranLog backup job on Sql agent, just like Log shipping? or I can have mirroring and Log backup job going on principle or I can't have both same time?
You can have them both at the same time on Principle DB.
--
SQLBuddy
March 13, 2014 at 3:55 pm
smtzac (3/13/2014)
Hi all,I am configuring DB mirroring on principle database and the database has Full,Diff,TranLog backups job running everyday on Sql Agent.
In order to configure Mirroring on principle database should I disable/delete TranLog backup job on Sql agent, just like Log shipping? or I can have mirroring and Log backup job going on principle or I can't have both same time?
Hi
thanks for your email, as George said log backups must continue. The agent jobs on the principal can run and maintain your backup strategy, logs especially otherwise the principal t-log will just keep growing and you don't want that.
You may want the same jobs on the mirror too for if the roles reverse. If you make your backup script intelligent to ignore databases that are not online and deploy this to the principal and the mirror.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 13, 2014 at 4:26 pm
when principle server goes down (server level), in an asynchronous DB mirroring, what is the best option and/or steps for production environment?
In Log shipping if primary server goes down, all we have to do is apply T-Logs (also tail log if possible) on secondary database and bring that database online. What are the best options in Asynchronous mirroring to keep the database running?
March 14, 2014 at 1:37 am
You can modify the steps mentioned in the below url based on your need for asynchronous mirroring session.
Regards,
Kumar
March 14, 2014 at 2:51 am
smtzac (3/13/2014)
when principle server goes down (server level), in an asynchronous DB mirroring, what is the best option and/or steps for production environment?
best option for what, are you referring to recovering the database?
smtzac (3/13/2014)
In Log shipping if primary server goes down, all we have to do is apply T-Logs (also tail log if possible) on secondary database and bring that database online. What are the best options in Asynchronous mirroring to keep the database running?
Right, forget log shipping it has no relevance here, this is database mirroring, they're totally different.
To keep the database running in asynch a failover has to be manually initiated and you have to accept the possibility of data loss. Generally do not force the service until you're sure the principal is dead
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 15, 2014 at 6:31 am
Yes. Recovering the Database. I don't think you can switch Principle server to Mirror if Principle is down, can you?
March 15, 2014 at 9:03 am
yes you can, from the failover with command
ALTER DATABASE database_name
SET PARTNER = FORCE_SERVICE_ALLOW_DATA_LOSS
---------------------------------------------------------------------
March 15, 2014 at 10:06 am
smtzac (3/15/2014)
Yes. Recovering the Database. I don't think you can switch Principle server to Mirror if Principle is down, can you?
Yes of course you can, but it's a manual failover using
[Code="sql"] alter database [yourdb] set partner force_service_allow_data_loss[/code]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 15, 2014 at 2:53 pm
what happens when principle comes back on? or will users know they are connecting the mirror? would plz share your knowledge on this or recommend any website which has 'Asynchronous' mirroring when disaster happens?
Thanks you
March 15, 2014 at 3:08 pm
The old principal will take up its role as the new mirror. Users may not be aware they are connecting to a mirror, and ideally should not care. There may have been a noticeable outage though.
read this thoroughly http://technet.microsoft.com/en-us/library/ms189977(v=sql.100).aspx
---------------------------------------------------------------------
March 15, 2014 at 3:49 pm
to add to George, the old principal will become the mirror but the session will be suspended. You will need to manually resume mirroring using
alter database [yourdb] set partner resume
-----------------------------------------------------------------------------------------------------------
"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