What is Reverse Log Shipping? How to Swipe/fail over Roles of Primary and Secondary in Log shipping Explanation? Step by Step Reverse Log shipping
We should follow below 7 steps to reverse/swipe/fail over log shipping
1.Disable the log shipping backup job on the primary server.2.On the standby server, run the log shipping copy and restore jobs to restore any remaining transaction log backups.3.Disable the log shipping copy and restore jobs on the secondary server.4.On the primary server, create on last transaction log backup using the NO RECOVERY option.5.On the standby server, restore this transaction log backup using the RECOVERY option.6.On the standby server (which will now be the primary server), right click on the database and select Properties -> Transaction Log Shipping. Enable the database to become the primary database and configure the backup and secondary server settings.
before going to demo see some basics
Here primary server is M S S Q L 2012 D E S T S Q L
Video demo of reverse log shipping
We should follow below 7 steps to reverse/swipe/fail over log shipping
1.Disable the log shipping backup job on the primary server.2.On the standby server, run the log shipping copy and restore jobs to restore any remaining transaction log backups.3.Disable the log shipping copy and restore jobs on the secondary server.4.On the primary server, create on last transaction log backup using the NO RECOVERY option.5.On the standby server, restore this transaction log backup using the RECOVERY option.6.On the standby server (which will now be the primary server), right click on the database and select Properties -> Transaction Log Shipping. Enable the database to become the primary database and configure the backup and secondary server settings.
7.Run the job on new primary and new secondary job
before going to demo see some basics
What is Log Shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all. The key feature of log shipping is that it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers Databases in “s y n c h”.
What are the Benefits of Log Shipping?
Benefits of log shipping, let’s take a more comprehensive look:
- Log shipping doesn’t require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement.
- Once log shipping has been implemented, it is relatively easy to maintain.
- Assuming you have implemented log shipping correctly, it is very reliable.
- The manual fail over process is generally very short, typically 15 minutes or less.
- Implementing log shipping is not technically difficult.
What the drawbacks of Log Shipping?
- Log shipping fail over is not automatic. The D B A must still manually fail over the server, which means the D B A must be present when the fail over occurs.
- The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.
- Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.
- The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for fail over can still be used normally.
- When it comes time for the actual fail over, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user’s applications to the new standby server. In some cases, neither of these options is practical.
In which recovery model Log Shipping can be configured?
Log Shipping works with Full and Bulk Logged recovery model.
Is it possible to configure Log Shipping from lower version to upper version and Vice versa?
Yes it is possible to configure Log Shipping from lower to upper version. But it is not possible vice versa.
What is Log Shipping Monitor Settings?
This setting enables us to setup a monitor on the Log shipping through which we can monitor the log shipping process.
What all jobs are created after configuring the Log Shipping?
Internally when Log Shipping is configured, there are 4 jobs created between Primary Server and Secondary Server, they are Backup Job, Copy Job, Restore Job and Alert Job
- Backup job: This job is created on Primary Server; this job takes the transaction log backup of the Database on a scheduled time
- Copy Job: This job is created on Secondary Server, this job Copies the transaction log Backup from Primary Server to the Standby/Secondary Server.
- Restore Job: This job is created on Secondary Server; this job restored the copied transaction log backup on the Secondary Server.
What permissions are required for shared folders on Primary and secondary for the service accounts?
- For the backup job, read/write permissions to the backup directory are required to the following:
SQL Server service account on the primary server instance.
Proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.
- For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
- For the restore job, read/write permission to the copy directory are required by the following: The SQL Server service account on the secondary server instance. The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.
Here primary server is M S S Q L 2012 D E S T S Q L
our Secondary Server is M S S Q L 2012 SOURCE
Now we are going to swipe/fail over above two server
existing log shipping working fine as see log shipping report see below
you can also see log shipping status report T S Q L see below
SELECT secondary_server,
secondary_database,
primary_server,
primary_database,
last_copied_file,
last_copied_date,
last_restored_file,
last_restored_date
from m s d b.d b o.log_shipping_monitor_secondary
Now we have to follow these below 7 steps to reverse/swipe/fail over log shipping
1.Disable the log shipping backup job on the primary server.
2.On the standby server, run the log shipping copy and restore jobs to restore any remaining transaction log backups.
3.Disable the log shipping copy and restore jobs on the secondary server.
4.On the primary server, create on last transaction log backup using the NO RECOVERY option.
5.On the standby server, restore this transaction log backup using the RECOVERY option.
6.On the standby server (which will now be the primary server), right click on the database and select Properties -> Transaction Log Shipping.
Enable the database to become the primary database and configure the backup and secondary server settings.
7.Run the job on new primary and new secondary job
Let see steps one by one
1.Disable the log shipping backup job on the primary server.
just right click on primary server disable backup job
now it was in disabled status @ primary server
2.On the standby server, run the log shipping copy and restore jobs to restore any remaining transaction log backups.
We have to run copy and restore job @ secondary server as see below
copy and restore job running success @ secondary server
3.Disable the log shipping copy and restore jobs on the secondary server.
Disable the copy and restore jobs @ secondary server
4.On the primary server, create on last transaction log backup using the NO RECOVERY option.
we can achieve using GUI as see below
(or)
On the primary server, create on last transaction log backup using the NO RECOVERY option.
we can achieve through T SQL as see below
After we took tail log backup on primary server so db went to restoring mode as see below
5.On the standby server, restore this transaction log backup using the RECOVERY option.
we have restored those tail log backup with secondary server with recovery option to db is going to read-write mode from read-only mode.
Right click on server and click as Refresh now it shows db has read-write mode
6.On the standby server (which will now be the primary server), right click on the database and select Properties -> Transaction Log Shipping.
Enable the database to become the primary database and configure the backup and secondary server settings.
now we have new primary server is ready for configure log shipping without need database backup
just right click on db and choose properties
be aware if you not remove previous log shipping it will throw below error
click on enable option and click on backup settings as see below
Now we have to give backup location of new primary server as see below (note: we can remove existing backup and copy file )
Now we have to give new secondary server as secondary server as see below
we have to specify copy location for new secondary server as see below
we should tick the stand by option with select check box of disconnect and delay as 1 mints as see below for new secondary server
just click submit button to enable new log shipping configuration primary and secondary server it will create new primary backup job and new secondary copy and restore job as see below
just click refresh button on new primary server to see new backup job
new backup job is showing @ new primary server as see below
now we can delete old/disabled copy and restore job @ new primary server as see below
now we can have only newly created backup job @ new primary server as see below
we can also do same as in new secondary server as we have to remove old/disabled backup job as see below
after refresh @ new secondary server it looks like see below
now we can test it to update some values on new primary server as see below
7.Run the job on new primary and new secondary job
now we need to execute backup job @ new primary server as see below
Now we need to do same copy and restore job execute @ new secondary server as see below
after executed restore job @ new secondary server db is showing restoring but we need to click refresh button to show read only as see below
after querying new secondary server it reflected on new secondary server so swipe/fail over success
now we can see log shipping report @ new secondary server as see below
Happy Blogging Thanks for Reading
