Log shipping and database mirroring are both high availability and disaster recovery options available in SQL Server. In this article, we will discuss how to configure log shipping on a mirrored database.

What is Mirroring?

Database mirroring is a high availability and disaster recovery option available in SQL Server. It involves sending transactions from the principal database to the mirror database. When the principal database goes down, the mirror server comes online. Mirroring is configured at the database level and has only one mirror database. It supports automatic failover if a witness server is used.

What is Log Shipping?

Log shipping involves taking log backups on the primary server, shipping the log backups to the secondary server, and restoring them on the secondary server. A backup job is created on the primary server to take backups as per a schedule. Copy and restore jobs are configured on the secondary server to run as per a schedule. Log shipping supports manual failover only.

Configuring Mirroring

Before configuring mirroring, make sure that the database is in full recovery mode. Simple and bulk-logged recovery models are not supported in mirroring. The principal server, mirror server, and witness server (if used) should be on the same SQL Server version. Partners should be running the same edition of SQL Server, although the witness can be any edition that supports database mirroring.

To configure mirroring:

1. Login to your primary SQL Server and take a full backup and a transactional log backup of the database you are going to mirror. For example:

  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
  

2. Copy the backup files from the primary server to the secondary server in your desired location.
3. Login to your secondary server and restore the database from the backups with the NORECOVERY option. For example:

  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
  

4. On the primary server, navigate to the database, right-click on it, select Properties, and click on Mirroring.
5. Click on Configure Security, click Next, and select yes to include a witness server if desired.
6. Enable the witness server and click Next.
7. Input the listener port numbers for the principal server, mirror server, and witness server, which will be used to communicate over the network.
8. If all the SQL Server instances are running on the same service account, leave the service accounts section blank. Otherwise, input the accounts as per your needs.
9. Click Next and Finish.
10. Click on Start Mirroring.

Once the mirroring is started, the database on the primary server will act as the principal database, while the mirror database will be in restoring mode.

Configuring Log Shipping on a Principal Database

Before configuring log shipping, make sure that the database is in full or bulk-logged recovery model. In this case, since we are combining mirroring and log shipping, the database should be in full recovery model.

To configure log shipping on a principal database:

1. Login to the primary server and navigate to the principal database.
2. Right-click on the database, select Properties, and click on Transaction Log Shipping.
3. Check “Enable this as a primary database in log shipping configuration”.
4. Click on backup settings to configure and schedule the transactional log backups.
5. Input the shared path where the transactional log backups will be taken. This shared path should be accessible from the secondary server where the mirror database exists.
6. Click on schedule to set the frequency of transaction log backups. For example, you can schedule the backup job to run every 15 minutes.
7. You can also set the retention period for backup files and enable backup compression as per your needs.
8. Once the backup settings are configured, click on Add to add the secondary server instance.
9. Connect to the secondary instance using Windows or SQL Server authentication and input the secondary database name of your choice.
10. Choose the appropriate option to initialize the secondary database in log shipping. For example, you can select the option to take a full backup of the database and restore it on the secondary server.
11. Navigate to the Copy Files tab and create a folder in the local drive. Input the folder path to copy the backup files from the shared path provided in the backup settings. Schedule the copy job as per your needs.
12. Navigate to the Restore Transaction Log tab and schedule the restore job as per your needs. You can choose between no recovery mode and standby mode. In standby mode, the secondary database will be in read-only mode and users can read the data.
13. Once the log shipping setup is completed, you will find a backup job on the primary server that takes transaction log backups as per the schedule you configured. The copy job will be created on the secondary server to copy the transaction log backup files from the shared path to the local path. The restore job is created on the secondary server to restore the copied transaction log backups in sequence.
14. Monitor the backup, copy, and restore jobs to ensure everything is working as expected.

Configuring a Backup Job on a Mirror Server

When mirroring fails over, the mirror database becomes online and acts as the principal database, while the original principal database goes into the restoring state. The backup job on the initial primary server does not take backups in this scenario. To continue log shipping, we need to configure the backup job on the current principal server.

To configure a backup job on a mirror server:

1. Login to the primary server and navigate to the principal database.
2. Right-click on the database, select Properties, and click on Mirroring.
3. Click on failover to manually failover the mirroring. Now the mirror database becomes the principal database.
4. Login to the current primary database server and navigate to the principal database.
5. Right-click on the database, select Properties, and click on Transaction Log Shipping.
6. Check “Enable this as a primary database in log shipping configuration”.
7. Click on backup settings to configure and schedule backups.
8. Input the same shared path provided while configuring the log shipping initially.
9. Click on the schedule button to set the frequency of transaction log backups.
10. Once the backup job is created on the current primary server, it will start taking transaction log backups of the database to the shared path provided in the backup settings. The copy and restore jobs on the log shipping secondary server will run as usual without any change in copy and restore settings.
11. Monitor the backup, copy, and restore jobs to ensure everything is working as expected.

By following these steps, you can configure log shipping on a mirrored database in SQL Server, ensuring high availability and disaster recovery for your critical databases.