Both log shipping and mirroring are high availability and disaster recovery options available in SQL Server. This article will review on how to configure log shipping on a mirrored database.
What is mirroring
- Database mirroring is high availability and disaster recovery option available in SQL server
- The transactions are sent from principal database to mirror database
- Mirror server comes online when principal goes down
- Mirroring is configured at database level and has only one mirror database
- Supports automatic failover if witness server is used
What is log shipping
- Log shipping takes the log backup on primary server, ships the log backups to secondary and restore them on secondary server
- A backup job is created on primary server and takes backup as per schedule
- Copy and restore jobs are configured on secondary server and runs as per schedule
- Supports manual failover only
Here is a series of step by step instructions to configure log shipping along with mirroring on a database so that in case of failover from principal to mirror the log shipping continue to work.
Configuring Mirroring
Pre-requisites
- Database should be in full recovery mode. Simple and bulk logged recovery models are not supported in mirroring
- Principal server, mirror server and witness server (If used) should be on same SQL server version
- Partners should be running same edition of SQL Server. Witness can be any edition that supports database mirroring
Login to your primary SQL Server and take one full backup and one transactional log backup of the database you are going to mirror.
In this case I am configuring mirroring on database called “Sample”.
Below is the T-SQL script to take full back of the database. Replace the location of the backup as per your need.
|
1 2 |
BACKUP DATABASE [Sample] TO DISK = N'D:\SQL_Backup\Sample.bak' WITH NOFORMAT, NOINIT, NAME = N'Sample-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
Below is the T-SQL script to take transaction log backup.
|
1 2 |
BACKUP LOG [Sample] TO DISK = N'D:\SQL_Backup\Sample_T1.trn' WITH NOFORMAT, NOINIT, NAME = N'Sample-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
Copy the backup files from primary server to secondary server in your desired location.
Now login to your secondary server and restore the database from the backups with no recovery option.
Below is the T-SQL Script to restore full backup. Replace the location of your backup file in the script.
|
1 2 |
Use master RESTORE DATABASE [Sample] FROM DISK = N'X:\Sample.bak' WITH FILE = 1, MOVE N'Sample' TO N'I:\MSSQL\Data\Sample.mdf', MOVE N'Sample_log' TO N'I:\MSSQL\Data\Sample_log.ldf', NORECOVERY, NOUNLOAD, STATS = 5 |
Below is the T-SQL Script to restore transaction log backup.
|
1 2 3 4 |
Use master RESTORE LOG [Sample] FROM DISK = N'X:\Sample_T1.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 GO |
Now on the primary server, navigate to the database and right click on the database, Select Properties and click on Mirroring
Click on Configure Security, click Next and Select yes to include a witness server.
Witness server is optional in mirroring and supports automatic failover when configured using high safety with automatic failover operating mode.
Enable witness server and click Next.
Input the listener port numbers for principal server, mirror server and witness server which will be used to communicate over the network.
If all the SQL Server instances are running on same service account leave the service accounts section blank else input the accounts as per your need.
Click Next and Finish. Then click on Start Mirroring.
In the above step you may encounter issues and mirroring does not start if there is no proper access on end points or if the ports are blocked by firewall.
Once the mirror is started the database on primary server looks like below and act as Principal database. the mirror database will be in restoring mode.
Configuring Log Shipping on a Principal Database
Pre-requisites:
- The database should be in full or bulk logged recovery model. But in this case, it is combination of mirroring and log shipping and database should be in full recovery model
Login to the primary server and navigate to the principal database. Right click on the database and select Properties. Click on Transaction Log Shipping and check Enable this is as a primary database in log shipping configuration.
Click on backup settings to configure and schedule the transactional log backups. Input the shared path where transactional log backups will be taken. The shared path you set should be accessable from the secondary server where mirror database exist and aslo it should be available accessable from seconadry server of your logshipping database.
Click on schedule to set the frequncey of transaction log backups. In this case I scheduled it the backup job to run for every 15 minutes. We can set the retention period for backup files, so that the backup files greater than retention period will be deleted. Backup compression is also available which compress the transaction log backups. Set the compresssion option as per your need.
Once the backup settings are configured. Click on Add to add secondary sever instance. Connect to the secondary instance using windows or SQL server authentication and input the secondary database name of your choice. There are different ways to initialize the secondary database in log shipping. As I do not have secondary database already initialized on the secondary server. I selected the first option which will take the full backup of the database and restore it on the secondary server.
Now navigate to Copy Files tab. create a folder in local drive and Input the folder path to copy the backup files from shared path provided in the backup settings.
Click on schedule to schedule the copy job. You can also configure when to delete to the copied files as per your needs.
Navigate to Restore Transaction Log tab and schedule the restore job as per your need. In this case I scheduled restore job to run for every 15 minutes.
We have two restore options available one is no recovery mode and the other is standby mode.
In no recovery mode the secondary database is not available for users and will be in restoring state. In standby mode the secondary database will be in read only mode and users can read the data. When stand by mode is used make sure you enable disconnect users in database when restoring backups.
Once the log shipping setup is completed. You will find Backup job on primary server which takes the transaction log backup as per schedule you configured.
The copy job will be created on secondary server and copy the transaction log backup files from shared path to local path.
The restore job is created on secondary server which restores the copied transaction log backups in sequence.
Configuring a backup job on a mirror server
When mirroring fails over, the mirror database becomes online, act as a principal database and the principal database goes into restoring state. The backup job on the initial primary server does not take backups as the database is in the restoring mode.
To continue log shipping, we need to configure the backup job on the current principal server.
To simulate the failover, Login to the primary server and navigate to the principal database right click on it select Properties. Click on Mirroring and click on failover to manually failover the mirroring. Now the mirror database becomes principal database.
Login to the current primary database server and navigate to principal database. Right click on the database and select Properties. Click on Transaction Log Shipping and check Enable this is as a primary database in log shipping configuration.
Click on backup settings to configure and schedule backups. Input the same shared path provided while configuring the log shipping initially.
Click on the schedule button to set the frequncey of transaction log backups. Once the backup job is created on current primary server it starts taking the transaction log backups of the database to to shared path provided in the backup settings.
The copy and restore jobs on log shipping secondary server run as usual with out any change in copy and restore settings.
Monitor the backup, copy and restore jobs to make sure every thing is as expected.
Steps summary
- Configure mirroring on the database (synchronous with full saftey)
- Configure log shipping on the principal database
- Manually failover the mirroring
- On current principal database enable transaction log shipping and configure backup using same shared path
- Monitor the log shipping jobs
Table of contents
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019