Log Shipping How To Fail Over
NB Details on this page have not been ratified
These are procedures for failing over the primary sql server database to the secondary database so that the secondary database is live. Typically there are two scenarios for this. Either a controlled failover where the primary server database/server is available (with or without log shipping being reversed), or the primary server database is unavailable and we require the secondary to become the new primary. In the latter scenario there will be data loss, but only up to the log backup shipping time plus any uncommitted transactions beyond this.
CONTROLLED SWITCHOVER FROM PRIMARY TO SECONDARY (WILL BREAK LOG SHIPPING)
1 .Ensure logins and agent jobs are copied across to secondary.
2. Ensure users are logged out of the database.
3. Ensure the lop shipping backup job is not running and run the log shipping backup job
4. Disable the log shipping backup job on the original primary server
5. Run copy and restore jobs on the secondary server to ensure the final logs are shipped across and restored to the secondary database.
6. Disable copy and restore jobs on the secondary server.
7. If primary database still online then bring database offline to ensure no further updates or connections made.
8. Run RESTORE DATABASE <database name> with RECOVERY; on the secondary database to bring the secondary database online.
9. Ensure logins and agent jobs are copied across.
10. Redirect the application to the new primary server.
CONTROLLED SWITCHOVER FROM PRIMARY TO SECONDARY (WITH REVERSE LOG SHIPPING INTACT)
1 .Ensure logins and agent jobs are copied across to secondary.
2. Ensure users are logged out of the database
3. Ensure the lop shipping backup job is not running and run the log shipping backup job
4. Disable the log shipping backup job on the original primary server.
5. Run copy and restore jobs on the secondary server to ensure the final logs are shipped across and restored to the secondary database.
6. Disable copy and restore jobs on the secondary server.
7. Run a tail backup of the log from the primary which will bring the primary offline into restoring state. Save this in the logship folder.
8. Restore the above log on the secondary server with recovery.
You will now have a syncronised database which is online. Log shipping can now be set up to run the other way. Choose already initialised in the options to avoid restoring the entire db.
9. Redirect the application to the new primary server.
PRIMARY DATABASE UNAVAILABLE FAILOVER
1 .Ensure logins and agent jobs are copied across to secondary if available.
2. If logs available then try to run backup log tail.
3. On secondary server run copy and restore jobs to bring up to date.
4. disable copy and restore jobs on secondary.
5. If tail log backup taken of old primary then apply a restore of this log to secondary with recovery
6 .If no tail log taken then run RESTORE DATABASE <database name> with RECOVERY;
7. Database now online with new primary, logging will have to reset up the other way if old primary restored.
These are procedures for failing over the primary sql server database to the secondary database so that the secondary database is live. Typically there are two scenarios for this. Either a controlled failover where the primary server database/server is available (with or without log shipping being reversed), or the primary server database is unavailable and we require the secondary to become the new primary. In the latter scenario there will be data loss, but only up to the log backup shipping time plus any uncommitted transactions beyond this.
CONTROLLED SWITCHOVER FROM PRIMARY TO SECONDARY (WILL BREAK LOG SHIPPING)
1 .Ensure logins and agent jobs are copied across to secondary.
2. Ensure users are logged out of the database.
3. Ensure the lop shipping backup job is not running and run the log shipping backup job
4. Disable the log shipping backup job on the original primary server
5. Run copy and restore jobs on the secondary server to ensure the final logs are shipped across and restored to the secondary database.
6. Disable copy and restore jobs on the secondary server.
7. If primary database still online then bring database offline to ensure no further updates or connections made.
8. Run RESTORE DATABASE <database name> with RECOVERY; on the secondary database to bring the secondary database online.
9. Ensure logins and agent jobs are copied across.
10. Redirect the application to the new primary server.
CONTROLLED SWITCHOVER FROM PRIMARY TO SECONDARY (WITH REVERSE LOG SHIPPING INTACT)
1 .Ensure logins and agent jobs are copied across to secondary.
2. Ensure users are logged out of the database
3. Ensure the lop shipping backup job is not running and run the log shipping backup job
4. Disable the log shipping backup job on the original primary server.
5. Run copy and restore jobs on the secondary server to ensure the final logs are shipped across and restored to the secondary database.
6. Disable copy and restore jobs on the secondary server.
7. Run a tail backup of the log from the primary which will bring the primary offline into restoring state. Save this in the logship folder.
8. Restore the above log on the secondary server with recovery.
You will now have a syncronised database which is online. Log shipping can now be set up to run the other way. Choose already initialised in the options to avoid restoring the entire db.
9. Redirect the application to the new primary server.
PRIMARY DATABASE UNAVAILABLE FAILOVER
1 .Ensure logins and agent jobs are copied across to secondary if available.
2. If logs available then try to run backup log tail.
3. On secondary server run copy and restore jobs to bring up to date.
4. disable copy and restore jobs on secondary.
5. If tail log backup taken of old primary then apply a restore of this log to secondary with recovery
6 .If no tail log taken then run RESTORE DATABASE <database name> with RECOVERY;
7. Database now online with new primary, logging will have to reset up the other way if old primary restored.
Comments