First published on MSDN on May 29, 2018
SQL Server deployment architecture uses Log Shipping for disaster recovery. Database Mirroring is configured within the primary data center and Log Shipping is configured within the cross-data center. To achieve automatic failover, synchronous database mirroring with a witness (a third SQL Server instance) is configured. When zero data loss is required, the database mirroring high-safety mode (synchronous) setting is enabled to help ensure zero data loss between the two servers located in the primary data center. To improve database availability within the primary data center, a third SQL Server instance is configured to act as a witness to enable automatic failover between the database mirroring partners.
If a primary data center outage renders both database mirroring partner instances unavailable, log shipping is used for disaster recovery. Log shipping involves ongoing transaction log backups of the principal databases. These transaction log backups are then copied to a SQL Server instance in the disaster recovery data center. Incoming transaction log backups are restored in sequence on an ongoing basis.
Log shipping can be performed on SQL Server 2012 and above. The below steps are specific for SQL server 2012.
The steps describe how to perform log shipping using GUI.
1. Assume machine on which log shipping is performed as primary.
PRIMARY = //MACHINE-NAME/ (or) //IP-ADDRESS/
PRIMARY = SECONDARY but it can also be different.
2. Create a database => AdventureWorks on PRIMARY
use AdventureWorks
create table employee ( name varchar(20), age int);
insert into employee values ('variable', 20)
select * from employee
3. Create database => Adv1 on Secondary.
Logshipping has to be done from Adventureworks to Adv1.
Note: Log shipping can only be applied at the database level.
4. Setting recovery model
5. Setting the transaction log shipping
6. Setting primary server setting
For testing purposes set the scheduled time or 1 min and in case of actual scenario leave it to default 15 min.
7. Adding secondary server
8. Secondary server = Primary server in our scenario
9. Restoring in secondary
Once jobs are running properly we have to perform breaking of logshipping.
Again go to properties of the database in PRIMARY SERVER. In Transaction Log shipping uncheck the check box which states enable transaction log shipping. This will disconnect the server. Once disconnected, the new database on the secondary will show restoring.
For bringing Adv1 online use the following command:
RESTORE DATABASE [Adv1] WITH RECOVERY
Note: To test log shipping - insert a record in AdventureWorks just before breaking log shipping step and wait till the jobs complete. Once the jobs complete and we break log shipping the changes will get reflected in Adv1.
Authored by Gunjan Jain
SQL Server deployment architecture uses Log Shipping for disaster recovery. Database Mirroring is configured within the primary data center and Log Shipping is configured within the cross-data center. To achieve automatic failover, synchronous database mirroring with a witness (a third SQL Server instance) is configured. When zero data loss is required, the database mirroring high-safety mode (synchronous) setting is enabled to help ensure zero data loss between the two servers located in the primary data center. To improve database availability within the primary data center, a third SQL Server instance is configured to act as a witness to enable automatic failover between the database mirroring partners.
If a primary data center outage renders both database mirroring partner instances unavailable, log shipping is used for disaster recovery. Log shipping involves ongoing transaction log backups of the principal databases. These transaction log backups are then copied to a SQL Server instance in the disaster recovery data center. Incoming transaction log backups are restored in sequence on an ongoing basis.
Log shipping can be performed on SQL Server 2012 and above. The below steps are specific for SQL server 2012.
The steps describe how to perform log shipping using GUI.
1. Assume machine on which log shipping is performed as primary.
PRIMARY = //MACHINE-NAME/ (or) //IP-ADDRESS/
PRIMARY = SECONDARY but it can also be different.
2. Create a database => AdventureWorks on PRIMARY
use AdventureWorks
create table employee ( name varchar(20), age int);
insert into employee values ('variable', 20)
select * from employee
3. Create database => Adv1 on Secondary.
Logshipping has to be done from Adventureworks to Adv1.
Note: Log shipping can only be applied at the database level.
4. Setting recovery model
5. Setting the transaction log shipping
6. Setting primary server setting
For testing purposes set the scheduled time or 1 min and in case of actual scenario leave it to default 15 min.
7. Adding secondary server
8. Secondary server = Primary server in our scenario
9. Restoring in secondary
Once jobs are running properly we have to perform breaking of logshipping.
Again go to properties of the database in PRIMARY SERVER. In Transaction Log shipping uncheck the check box which states enable transaction log shipping. This will disconnect the server. Once disconnected, the new database on the secondary will show restoring.
For bringing Adv1 online use the following command:
RESTORE DATABASE [Adv1] WITH RECOVERY
Note: To test log shipping - insert a record in AdventureWorks just before breaking log shipping step and wait till the jobs complete. Once the jobs complete and we break log shipping the changes will get reflected in Adv1.
Updated Mar 13, 2019
Version 2.0
