Problem
We have an application for which SQL Server Log Shipping is configured as the disaster recovery (DR) solution. When we first setup log shipping we didn’t have a need to use this database as a read-only copy. The use of this database has grown and we have many more read requests than we originally planned. To overcome the server load, we decided to use the secondary server as a read-only server. Our goal is to use the primary server for normal OLTP operations and the secondary server for all reporting and SELECT transactions. The database is very large and we want to convert the secondary log shipped database to read-only, but we don’t want to have to redo the log shipping setup.
In this tip, I will explain the step by step process to change the secondary database from NORECOVERY (restoring) to STANDBY (read only) without having to reinitialize the log shipping configuration.
Solution
SQL Server Log Shipping allows you to automatically send transaction log backups from a primary database server instance to one or more secondary database server instances. In most cases, log shipping is setup so you have a warm standby server for disaster recovery. SQL Server offers the ability to have the secondary database in a restoring state where the database cannot be used or in a standby state which allows read-only activity.
Secondary log shipped databases can be kept in one of two modes:
- Restoring – This mode is also known as NORECOVERY mode and cannot be used for read operations.
- Standby / Read-Only – This mode is also known as STANDBY mode and can be used for read operations.
When you use the NORECOVERY mode, the database will be in a restoring state and inaccessible to users. When you use the STANDBY mode, the database will be in Read-Only state and users can access this database for read operations. Transactions which are in process on the primary or any uncommitted transactions cannot be read on the secondary database when the database is in a read-only state.
Steps to change restore mode of secondary database in SQL Server Log Shipping
Check the current SQL Server database state
As previously mentioned, we have SQL Server Log Shipping in place for an application. First, let’s see whether our log shipping is working or not. We will launch the log shipping dashboard report to check the log shipping status. Status should be GOOD to make sure both databases are in sync. To run this report, right click on server name, choose “Reports” and then “Standard Reports” followed by “Transaction Log Shipping Status” as shown below.
Once you will click on “Transaction Log Shipping Status”, a report will run and appear in the right pane of SQL Server Management Studio. Below is a screenshot of that report. We can see the status is shown as “Good” which means both databases are in sync. We can also get information about the last copied and restored files along with the time since the last copied and restore file was applied.
We can also get this information by running the below command in the msdb database of the secondary server. You can see the last copied and restored file date then check it with the last backup file date.
SELECT
secondary_server,
secondary_database,
primary_server,
primary_database,
last_copied_file,
last_copied_date,
last_restored_file,
last_restored_date
FROM msdb.dbo.log_shipping_monitor_secondary
Check the Current SQL Server Log Shipping Mode
Now check the secondary database mode either in SQL Server Management Studio or by using T-SQL. You can check it by running the below command. Look at the restore_mode column. If the restore_mode value is 1 then it is in Standby mode and if it is 0 then it is in Restoring mode. For our test database it is currently in the Restoring mode since the value is 0.
SELECT
secondary_database,
restore_mode,
disconnect_users,
last_restored_file
FROM msdb.dbo.log_shipping_secondary_databases
We can also check this setting in SQL Server Management Studio by expanding the database folder on the secondary server. Below we can see it is in a “Restoring” state.
Change a SQL Server Log Shipping Database to Read-Only
There are two options to make such changes for log shipping configurations: 1) by running the log shipping system stored procedure and 2) by making changes to log shipping using SSMS.
Run the below command to change this configuration setting using T-SQL:
EXEC sp_change_log_shipping_secondary_database
@secondary_database = 'Collection',
@restore_mode = 1,
@disconnect_users = 1
We can also change the restore mode from standby to restoring by running the same command with @restore_mode 0.
We can also apply this change in SSMS, which I will show in a different tip.
Check the SQL Server Log Shipping Change
Now, we can run the same script which we have executed above to check whether the restore mode has changed. We know 1 is for standby and 0 is for no recovery mode. We can see below that this has changed, but this change will not be reflected in SSMS until the next restore job has run successfully.
Run SQL Server Log Shipping Restore
Now go ahead and run the restore job on the secondary server. Once the restore has successfully completed, a *.tuf file will be automatically created under the data drive folder and the secondary database status will be changed to Standby mode in SSMS as well. Please see the below screenshot of the TUF file which is created under the data folder.
Check Log Shipping Status and Read-Only State
The restore job ran successfully, but we still need to check whether the Log Shipping configuration is working. Run the log shipping backup job on the primary server and once this job completes then run the copy and restore jobs on the secondary server to replicate all pending logs on the standby database. Once all three jobs complete successfully, we can run the transaction log shipping status report to check the log shipping status to see if both databases are in sync after this change. Here you can see the time stamp of the TUF file in the above screenshot and the time stamp of the last restored file in the below screenshot. It’s clear that all logs have been applied on the secondary database after the change was made.
Lastly, we can verify log shipping configuration by creating an object in the primary database and then run the backup, copy and restore jobs to check that the object is created in the secondary database. Run the below script on the primary server to create a dummy table named “Manvendra” in the primary database.
--Run this on primary database on primary Server
USE [Collection]
GO
CREATE TABLE [dbo].[Manvendra](
[Name] [nchar](10) NULL,
[Salary] [nchar](10) NULL
)
GO
Now run the log shipping backup job on the primary server and once this job completes run the copy and restore jobs on the secondary server to move the changes to the standby database. Once the restore job completes, check the secondary database to verify that the new table “Manvendra” has been created. As we can see in the below screenshot, table Manvendra has been created.
Next Steps
Make sure to test this solution in a lower life cycle environment before replicating it on production servers. Also make this change if it is needed, otherwise leave your existing log shipping configuration as it is.
Explore more knowledge in these tips:
