Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Change SQL Server log shipped database from Restoring to Standby Read-Only


By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | More > Log Shipping

Attend these FREE MSSQLTips webcasts >> click to register


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 the 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.

launch log shipping status report

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.

log shipping status report

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
Check LS secondary db mode by t-sql

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.

Check LS secondary db mode in SSMS

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
Run sp_change_log_shipping_secondary_database

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.

Check secondary database mode after changing it to standby mode

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.

TUF file generated in 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.

log shipping status report after changing restore mode to standby

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.

Verified objects are shipped to secondary database
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:



Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, October 11, 2016 - 1:27:00 PM - Misael Back To Top

 Great work !!! tahnk you for sharing.

 


Wednesday, December 02, 2015 - 1:43:58 AM - Santosh Gunda Back To Top

Too Good My Friend !! Thank You, and Keep up the good work !!


Tuesday, October 20, 2015 - 8:34:54 AM - Ramakrishna Back To Top

very good very nice post yar.

 

Thanks,

Ramakrishna


Tuesday, May 05, 2015 - 4:08:33 AM - shivani Back To Top

very Help full

 


Learn more about SQL Server tools