Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring and SQL Agent Job on principle


Mirroring and SQL Agent Job on principle

Author
Message
Tac11
Tac11
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 1110
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?
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6348 Visits: 13687
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.

---------------------------------------------------------------------
Tac11
Tac11
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 1110
Thank you for your input and quick response. I really appreciate it.
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8803 Visits: 16560
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" ;-)
Tac11
Tac11
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 1110
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?
KumarVelayutham
KumarVelayutham
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 1281
You can modify the steps mentioned in the below url based on your need for asynchronous mirroring session.

http://www.mssqltips.com/sqlservertip/2701/steps-to-apply-a-service-pack-or-patch-to-mirrored-sql-server-databases/

Regards,
Kumar
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8803 Visits: 16560
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" ;-)
Tac11
Tac11
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 1110
Yes. Recovering the Database. I don't think you can switch Principle server to Mirror if Principle is down, can you?
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6348 Visits: 13687
yes you can, from the failover with command

ALTER DATABASE database_name
SET PARTNER = FORCE_SERVICE_ALLOW_DATA_LOSS

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search