Configuring SQL log shipping & switch restoring mode from Standby to Restoring & Restoring to Stanby
In this article, we will discuss configuring the SQL Server log shipping and SQL Server log shipping Configuring changes from two restoring mode Standby to Restoring and Restoring to Standby.
SQL Server log shipping is one of the disaster recovery technology to create a database copy on another instance of the same server or the SQL instance of another server. In SQL log shipping, first, it restores a full backup of a database and then send the continuous transactional log from a primary server to a secondary server using backup SQL agent job on the primary server and copy and restores SQL agent job on the secondary server.
In a log shipping configuration, the following servers are involved.
Primary server – It is a source database server which sends the transactional log to another server.
Secondary server -It will maintain a secondary copy of the primary server database and will use for reading operation to reduce the load on the primary server.
Monitor server – It will monitor log shipping activity; It is not mandatory to add a monitor server in the log shipping configuration.
Once SQL log shipping configured, the database state on the secondary server is either in two restoring mode as shown in the following.
Standby mode – If the database is in standby mode, the database is online, and users can only read data from the database.
No recovery mode – If the database is in restoring state, users can't read the data.
In this log shipping demonstration, I will use the following server and database
SQL1 – Primary server
SQL2 – Secondary server
Database – AdventureWorks2014
The Primary and secondary servers should in the domain system and log on through service account.
Create a window authentication login in both the primary and secondary SQL server instance for a service account.
We need to create a shared drive folder to store backup files on the primary server, and the same folder should get access from the secondary server to copy backup data and restore it on the secondary server.
Service account login should have read and write permissions to the shared folder.
SQL Server services should run under the service account user.
Connect to the primary server SQL1.
Right-click a database and click Properties
In the Database properties window, On the left side, select the Log Shipping page option and tick the checkbox option Enable this as a primary database in a log shipping configuration. By default, enable log shipping option is disabled.
In the database properties window, click the Backup settings option to configure backup job settings.
In the log backup settings window, we will provide the Network path to the backup folder. We need to provide only network path to store backup files if we are configuring SQL server log shipping on the domain network. If we set the log shipping on two instances on the same server, we can provide a local path.
Once we configured backup settings for log shipping, In the database properties, under secondary server instances and database click Add to set the secondary server.
In the secondary database settings window, click Connect, and a pop-up window will appear and select a secondary instance SQL2 and click connect.
Once we connected to SQL2, we can see SQL2, and database of primary server AdventureWorks2014 appeared as the following.
In the secondary database properties window, three option we need to configure.
Initialize Secondary Database
In this section, we will decide to restore mode of the backup of the primary server database into the secondary server.
In the first option, SQL Server log shipping will first create a full backup of the primary database and restore it on the secondary database.
In the second option, first, we need to take backup of a primary server database and restore it on the secondary server.
In the copy files section, we need to provide the destination folder path where backup of the primary database will get copied from the primary server. It will generate a copy job which will copy backup files from the primary server to the destination folder path on the secondary server.
Restore Transaction Log
In this section, the log shipping process will restore the transaction log to the destination server with two restoring modes.
In the No recovery mode, once a database log backup is restored, the database will be in restoring state means it will not be available for use to users. It will not be available till we restore it WITH RECOVERY option manually.
In Standby mode, once the backup restored, the database will be in standby node means In-Read only way. The user can access a database, and they can perform only read operations on a database.
In the following window, the log shipping configuration process completed.
Once log shipping configured successfully, we can see database AdventureWorks2014 is in Standby/Read-only mode as shown in the following.
We will verify data from the primary and secondary server. We will execute SELECT statement on a table Address and we will able to get records on the secondary server as it is in Read-only mode. So we have restored the primary server database on the secondary server till the point.
Once log shipping configured, we can check the backup job schedule, and the last backup created details as the following.
In this step, we will perform data verification on the primary and secondary server. In this demonstration, we will use a table PhoneNumberType in Person schema. On a primary server, we will fetch records, and there are three records on the primary server as the following.
Now, we will insert one new record into a table PhoneNumberType as the following. After log shipping configuration, new transactions added on the primary server database will be reflected on the secondary server once Backup job on the primary and Copy and restore agent job on the secondary job completed.
Connect to a secondary server and Go to SQL Server agent and open Job activity monitor.
We need to execute a copy job by right-clicking a copy job and start the SQL agent job as shown in the following.
When I started a job, it got failed as shown in the following. Why was it abandoned? I investigated by logging a secondary server and checked the SQL Server services configuration setting. SQL server agent was running under the default SQL Server agent service NT Service\SQLSERVERAGENT, and we need to change it to service account login.
After changing the login account for SQL Server agent service, we will run below copy and restore jobs on the secondary server.
We will verify records on the table PhoneNumberType, and we can see the latest data which we added on the primary server, is reflected on the secondary server as shown in the following.
In this way, we have configured log shipping for standby restoring mode. Now we will discuss changing restoring mode from Standby mode to No recovery mode and No recovery mode to standby mode.
Change from Standby to Restoring
In this section we will discuss, SQL log shipping restoring mode change from standby to no recovery mode.
Right-click a database and go to properties and go to secondary server settings.
As shown in the following, under Restore Transaction Log tab, Select No recovery mode as a database restoring state and click OK.
Then go to Job activity monitor on the primary server and Run backup job as shown in the following.
Once backup job on the primary server completed. We will execute a copy and restore SQL agent job on the secondary server as shown in the following.
Refresh the SQL Server secondary instance, and we can see database switched to restoring mode. In restoring method, users will not be able to access data from the secondary server.
Change from Restoring to Standby
In this section, we will discuss, changing restoring mode from No recovery mode to standby mode. We will change this restoring mode using SQL script.
Once we configured log shipping, log shipping related tables created in the MSDB database. Table log_shipping_secondary_databases is one of the secondary servers as shown on the following.
Table log_shipping_secondary_databases has table column restore_mode. Value of Restore_mode column is either 0 or 1.
· Standby mode = 0
· Restoring mode = 1
Currently, the database is in restoring mode, and we need to change it to standby mode so that we will change restore_mode column value to zero as the following.
Once it changed we will run a backup job on the primary server, copy and restore agent job on the secondary server. The database state changed from restoring mode to standby state.