Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Mirroring and SQL Agent Job on principle Expand / Collapse
Author
Message
Posted Thursday, March 13, 2014 11:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:43 AM
Points: 139, Visits: 497
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?
Post #1550865
Posted Thursday, March 13, 2014 11:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:57 AM
Points: 5,888, Visits: 13,062
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.

---------------------------------------------------------------------

Post #1550867
Posted Thursday, March 13, 2014 12:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:43 AM
Points: 139, Visits: 497
Thank you for your input and quick response. I really appreciate it.
Post #1550872
Posted Thursday, March 13, 2014 3:36 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1550942
Posted Thursday, March 13, 2014 3:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 6,755, Visits: 14,406
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"
Post #1550948
Posted Thursday, March 13, 2014 4:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:43 AM
Points: 139, Visits: 497
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?
Post #1550956
Posted Friday, March 14, 2014 1:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 6:36 AM
Points: 102, Visits: 1,076
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
Post #1551039
Posted Friday, March 14, 2014 2:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 6,755, Visits: 14,406
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"
Post #1551056
Posted Saturday, March 15, 2014 6:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:43 AM
Points: 139, Visits: 497
Yes. Recovering the Database. I don't think you can switch Principle server to Mirror if Principle is down, can you?
Post #1551467
Posted Saturday, March 15, 2014 9:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:57 AM
Points: 5,888, Visits: 13,062
yes you can, from the failover with command

ALTER DATABASE database_name
SET PARTNER = FORCE_SERVICE_ALLOW_DATA_LOSS


---------------------------------------------------------------------

Post #1551487
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse