Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Mirroring + Log Shipping together Expand / Collapse
Posted Wednesday, July 30, 2014 3:02 AM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 342, Visits: 809

I'm setting up a database with mirroring for HA and log shipping for DR. The mirroring works fine. But I am tripped up on something with the log shipping. I set up the log shipping from the principal to our DR server, no issues. But I need to setup the same log shipping from the mirror (after failover, activated as principal). Per the MS link below, I need to failover to the mirror, then repeat the log shipping setup using "stored procedures". What does this refer to? Is it possible to set up the mirror on SERVER2 for log shipping in advance or must we do it after failover?

SERVER1 (Principal) ----------mirroring------> SERVER2 (Mirror)
SERVER1 (Principal) ----------log shipping --> SERVER3 (DR copy)
failover to SERVER2
SERVER2 (now Principal) -----log shipping --> SERVER3 (DR copy) ??? how to setup ???

Thanks in advance

Set up log shipping on the new principal (previously mirror) as the primary database.
Do not perform any setup from a secondary.
You must use the same backup share that you used in step 4.
The Transaction Log Shipping interface in SQL Server Management Studio supports only one primary database per log shipping configuration. Therefore, you must use stored procedures to set up the new principal as primary.
Perform another manual failover to fail back to the original principal.

Post #1597621
Posted Wednesday, July 30, 2014 3:11 AM



Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 7,974, Visits: 16,002
JarJar (7/30/2014)
??? how to setup ???

This link details the following

Technet: How to enable log shipping (TSQL)

To enable log shipping

2.On the primary server, execute sp_add_log_shipping_primary_database to add a primary database. The stored procedure returns the backup job ID and primary ID.

3.On the primary server, execute sp_add_jobschedule to add a schedule for the backup job.

4.On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job.

5.On the primary server, enable the backup job.


"Ya can't make an omelette without breaking just a few eggs"
Post #1597626
Posted Wednesday, July 30, 2014 8:01 AM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 342, Visits: 809
thanks. i need some sanity check here...

i failed over from my principal (SERVER1) to mirror (SERVER2), now the mirror is the principal.

On SERVER2, i ran the stored proc sp_add_log_shipping_primary_database and sp_add_jobschedule. it creates the jobs and they are running fine (log backups are created).

On SERVER3, my DR server, i run sp_add_log_shipping_secondary_primary and sp_add_jobschedule. it creates the Copy and Restore jobs (copies logs from SERVER2).

On SERVER3, i run the sp_add_log_shipping_secondary_database but the database already exists because it was created when i set up log shipping on my original principal on SERVER1.

Msg 32013, Level 16, State 1, Procedure sp_add_log_shipping_secondary_database, Line 80
A log shipping entry already exists for secondary database MIRRORTEST.

Am I supposed to delete and recreate the log-ship database MIRRORTEST on SERVER3? Doesn't seem right. What am I missing?

If I wasn't clear the end of the day, I want to log ship from SERVER1 TO SERVER3 when SERVER1 is principal and from SERVER2 to SERVER3 when SERVER2 is principal (after failover).

Post #1597737
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse