What is SQL Server log shipping? FAQ's and Technique about Log Shipping and Questions and Answers

What is SQL Server log shipping?

SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations 

A log shipping session involves the following steps:

• Backing up the transaction log file on the primary SQL Server instance
• Copying the transaction log backup file across the network to one or more secondary SQL Server instances
• Restoring the transaction log backup file on the secondary SQL Server instances

Log Shipping in SQL Server is a very old and popular feature most widely used to mostly enable disaster-recovery solution for the application databases. SQL Server Log shipping allows us to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

Log shipping is a Database level not server level if you add database users it will reflect on secondary but not login could not reflect due to server level limitation to reflect login we need sp_help_rev_login by manual work

Monitoring Log Shipping

After you have configured log shipping, you can monitor information about the status of all the log shipping servers. The history and status of log shipping operations are always saved locally by the log shipping jobs. The history and status of the backup operation are stored at the primary server, and the history and status of the copy and restore operations are stored at the secondary server. If you have implemented a remote monitor server, this information is also stored on the monitor server.

You can configure alerts that will fire if log shipping operations fail to occur as scheduled. Errors are raised by an alert job that watches the status of the backup and restore operations. You can define alerts that notify an operator when these errors are raised. If a monitor server is configured, one alert job runs on the monitor server that raises errors for all operations in the log shipping configuration. If a monitor server is not specified, an alert job runs on the primary server instance, which monitors the backup operation. If a monitor server is not specified, an alert job also runs on each secondary server instance to monitor the local copy and restore operations.

One of the common log shipping scenarios is the environment with two servers (SQLServer-1 – primary and SQLServer-2 – secondary), two SQL Server instances (SQLInstance-1 and SQLInstance-2), and one SQL Server database named SQLDB-1 with log shipping running on it

Another common configuration is the environment with three (or more) servers (SQLServer-1 – primary, SQLServer-2 – secondary, and SQLServer-3 – secondary), three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3), and one SQL Server database named SQLDB-1 with log shipping running on it

Operating modes

There are two available modes and they are related to the state in which the secondary, log shipped, SQL Server database will be:

• Standby mode – the database is available for querying and users can access it, but in read-only mode
  • The database is not available only while the restore process is running
    • Users can be forced to disconnect when the restore job commence
    • The restore job can be delayed until all users disconnect themselves
• Restore mode – the database is not accessible

Advantages and disadvantages of using SQL Server log shipping

SQL Server log shipping is primarily used as a disaster recovery solution. Using SQL Server log shipping has multiple benefits: it’s reliable and tested in details, it’s relatively easy to set up and maintain, there is a possibility for failover between SQL Servers, data can be copied on more than one location etc.

Log shipping can be combined with other disaster recovery options such as AlwaysOn Availability Groups, database mirroring, and database replication. Also, SQL Server log shipping has low cost in human and server resources

The main disadvantages in the SQL Server log shipping technique are: need to manage all the databases separately, there isn’t possibility for an automatic failover, and secondary database isn’t fully readable while the restore process is running

Setting up the database log shipping environment

SQL Server log shipping is based on execution of predefined SQL Server jobs. The SQL Server log shipping feature is available in all SQL Server editions except the Express edition. All the databases intended to be used for log shipping must be in the Full or Bulk logged recovery model

Another important prerequisite is running SQL Server Agent on both servers. Security policies must be defined in order for SQL Server Agent to have permission to read and write in the backup folder. Note that SQL Server agent on the secondary server must be able to read from the primary server’s backup folder

The database backups can be compressed, but that requires additional CPU time. Most common configurations use network locations for storing the backups

The database log shipping setup needs to be initiated from the principal server using the SQL Server Management Studio wizard. The first step defines transaction log backup settings:

• A network path to the backup
• How long backup files should be kept before deleting
• An alert if no backup is taken
• The backup job itself
• Schedule of the job
• Schedule type
• Frequency
• Duration

The next step defines secondary databases which involve choosing the secondary SQL Server instance and secondary database. The full database backup, from the primary database, must be restored on the secondary server before log shipping commences

After initializing the secondary database you must define the copy folder where the transaction log backups from the primary server will be stored

The final step involves choosing from two available modes: The No recovery – Restore mode and Standby mode. You can also delay the restoring process and set up an alert if no restore occurs within the specified time

Once the log shipping is ready for use, it will run in the background, and if the problem occurs the alert will signalize the problem

Log shipping FAQ's:

• What editions of SQL Server is log shipping available in?
  • 2012 – Enterprise, Business Intelligence, Standard, and Web
  • 2008R2 – Datacenter, Enterprise, Standard, Web, and Workgroup
  • 2008 – Enterprise, Standard, Web, and Workgroup
  • 2005 – Enterprise, Standard, and Workgroup
• Does the secondary need to be licensed?
  • I am not the licensing police, and I am not Microsoft – check with your licensing representative to clarify your exact situation. Generally, you can have one warm standby server. However, the second someone starts using it for reporting, testing, or anything else, you need to license it like any other server.
• Log shipping is compatible with backup compression. What edition of SQL Server do I need to take advantage of compression?
  • 2012 – Enterprise, Business Intelligence, or Standard
  • 2008R2 – Datacenter, Enterprise, or Standard
  • 2008 – Enterprise
  • 2005 – Not available
• When log shipping is set up, Agent jobs are created to alert me if a backup, copy, or restore fails. How do I get notified?
  • You need to go into the Agent job, pull up Notifications, and choose your method – email an operator, or write to the event log, for example.
• Are my logins shipped from the primary to the secondary?
  • No, they are not. You’ll need to set up a separate method to sync the logins.

• Does this replace, or can it be combined with, our existing daily full and log backups?
  • TL; DR – no.
  • You’ll still want to take regular full and/or differential backups. Log shipping only takes one full backup – at the beginning – and that’s only if you specify that it does so. It can also be initialized from an existing full backup.
  • Taking two log backups in separate jobs will break the log chain, however. If you implement log shipping, it will replace your current transaction log backup job.
• What’s the difference between the secondary being in “Restoring” vs. “Standby”?
• • Restoring means the database is not accessible. Standby means it is read-only. You make this decision when you set up the log shipping.
  • If the database is in Standby mode, users can query it – except when a log backup is being restored. You need to decide if a restore job will disconnect users, or if the restore is delayed until after the users are disconnected.

Three types of Log Shipping Techniques:

Recent SQL Server releases have had a major focus on high availability. Early previews of the next release of SQL Server, code-named Denali (SQL Server 2012), show that this trend will continue. Somewhere in the midst of the new availability technologies and feature sets, log shipping seems to have fallen by the wayside. Although log shipping received a much-needed face lift with SQL Server 2005, there were only minor enhancements in SQL Server 2008.

Although log shipping isn’t as widely used as it once was, it’s still a vital tool that has many applications, such as using warm standbys for disaster recovery, migrating large databases, and providing read-only access to data. Log shipping isn’t difficult to understand or use. There are three techniques that can save you a lot of time and make working with log shipping painless: reversing log shipping, switching between log shipping and database mirroring, and converting log shipping to transactional replication.

Reversing Log Shipping

Reversing log shipping is an often overlooked practice. When DBAs need to fail over to a secondary log shipping server, they tend to worry about getting log shipping back up later. This is especially true in the case of very large databases. If you're using log shipping as your primary disaster recovery solution and you need to fail over to the secondary log shipping server, you should get log shipping running as quickly as possible. With no disaster recovery failover in place, you might be running exposed.

Reversing log shipping is simple. It doesn’t require reinitializing the database with a full backup if performed carefully. However, it’s crucial that you remember the following:

• You need to preserve the log sequence number (LSN) chain.
• You need to perform the final log backup using the NORECOVERY option. Backing up the log with this option puts the database in a state that allows log backups to be restored and ensures that the database’s LSN chain doesn’t deviate.
• The primary log shipping server must still be accessible to use this technique.

To fail over to a secondary log shipping server, follow this 10-step process:

1. Disable all backup jobs that might back up the database on both log shipping partners.
2. Disable the log shipping jobs.
3. Run each log shipping job in order (i.e., backup, copy, and restore).
4. Drop log shipping.
5. Manually back up the log of the primary database using the NORECOVERY option. Use the command
   

   
   
   BACKUP LOG [DatabaseName]
     TO DISK = 'BackupFilePathname'
     WITH NORECOVERY;
   
   

   where DatabaseName is the name of the database whose log you want to back up and BackupFilePathname is the backup file’s pathname (e.g., Z:\SQLServerBackups\TLog.bck).
6. Restore the log backup on the secondary database using the RECOVERY option, and bring the secondary database online. The primary and secondary databases have now switched positions.
7. Back up the log of the new primary database (optional).
8. Restore the log on the new secondary database using the NORECOVERY option (optional).
9. Reconfigure log shipping.
10. Re-enable any backup jobs that were disabled.

Note that step 7 and step 8 are listed as optional because they’re not required for establishing log shipping. However, I recommend performing these steps to ensure that the log shipping configuration will proceed without any problems.

With a few minor adjustments, this 10-step process works with multiple secondary log shipping databases. You perform the same basic steps, keeping in mind that the other secondary databases will still be secondary databases after the failover. After you back up the log on the new primary database, you should use the NORECOVERY option to restore that backup on all the planned secondary databases. You can then add them as secondary databases to the new primary database.

Switching Between Log Shipping and Database Mirroring

Log shipping and database mirroring are both initialized with a full backup and both rely on the LSN chain for synchronization. These similarities make it possible to switch between the two without reinitializing with a full backup. This technique is useful when you have either log shipping or database mirroring configured and you want to switch to the other technology for a short time. The switch can go either way. For example, I use this technique when I need to perform maintenance on log shipping servers.

Switching between log shipping and database mirroring doesn’t involve manually performing any database backups or restores. It’s as simple as dropping whichever technology is configured, then configuring the other. However, a key concept to remember is that you need to prevent any backups from occurring when neither technology is configured.

To switch between log shipping and database mirroring, follow these steps:

1. Disable all the backup jobs that back up the database on both partners.
2. Remove the secondary database from log shipping or drop database mirroring.
3. Disable the log shipping jobs and run the jobs in order (i.e., backup, copy, and restore) if you removed the secondary database from log shipping. This step isn’t needed if you dropped database mirroring.
4. Configure log shipping or database mirroring.
5. Re-enable any backup jobs that were disabled.

When a failover will be short-term, I recommend switching between log shipping and database mirroring because this technique requires much less work to set up than reversing log shipping. When a failover is intended to be long-term, reversing log shipping is the best technique to use.

Converting Log Shipping to Transactional Replication

Initializing transactional replication from a backup was introduced in SQL Server 2005. What some people might not realize is that the backup used for initialization doesn’t have to be a full backup. You can initialize a subscriber with a log backup or differential backup. For example, I used to lead a team that was responsible for a 1.8TB OLTP database that was replicated to two replica servers. Our general process for configuring replication for this database was to start by setting up log shipping. We could generally get to this point in the process within a reasonable time frame, thanks to a lot of work we did to optimize the backup and restore processes. We then completed the replication setup the following day, knowing that the subscriber database had been kept synchronized.

The process for converting log shipping to transactional replication is fairly simple. The following steps detail the process, starting from the point at which log shipping is configured but replication isn’t:

1. Configure the replication publication as usual (i.e., configure the distributor, enable the publisher, and create the publication).
2. Disable all backup jobs for the database.
3. Disable the log shipping jobs.
4. Run the log shipping jobs in order (i.e., backup, copy, and restore).
5. Remove the secondary database from the log shipping configuration.
6. Change the publication to allow initialization from backup files. This is done by changing the Allow initialization from backup files option from False to True in the Publication Properties dialog box, which Figure 1 shows. Note that it isn’t possible to set this option in step 1 if you’re using the GUI. (You can, however, set this option in step 1 if you’re using T-SQL code to create the publication.)
7. Back up the publisher’s log. This backup will be used to initialize the subscriber.
8. Restore the log backup on the secondary database using the RECOVERY option.
9. Create the subscription on the publisher using the sp_addsubscription stored procedure in Listing 1 (more on this shortly).
10. Create the subscription on the subscriber.
11. Re-enable the backup jobs.
12. Drop log shipping on the publisher if desired.

Most of these steps can be performed using the GUI in SQL Server Management Studio (SSMS). However, to create the subscription on the publisher in step 9, you must use the sp_addsubscription stored procedure because the GUI doesn’t support the options required for initializing the subscriber from a backup. The sp_addsubscription stored procedure in Listing 1 creates a pull subscription for the TwoTBDatabase database on the SQLSrv02 server.

USE TwoTBDatabase;

EXEC sp_addsubscription
  @publication = N'TwoTBDatabase_pub',
  @subscriber = N'SQLSrv02',
  @destination_db = N'TwoTBDatabase',
  @subscription_type = N'Pull',
  @sync_type = N'initialize with backup',
  @backupdevicetype = N'Disk',
  @backupdevicename = N'e:\bak\TwoTBDatabase.trn',
  @article = N'all',
  @update_mode = N'read only',
  @subscriber_type = 0;

Here’s how to customize the stored procedure for your environment:

• Set the @publication parameter to your publication’s name.
• Set the @destination_db parameter to your database’s name.
• Set the @subscriber parameter to your server’s name.
• Set the @backupdevicename parameter to the pathname of the log backup created in step 7.
• Set the @subscription_type parameter to pull if you’re creating a pull subscription. (The default setting is push, so you don’t need to include this parameter if you’re creating a push subscription.)

The rest of the parameters don’t need to be changed.

Combining the Techniques

You can use the three log shipping techniques separately or combine them. For example, I used all three techniques when my team ran into some problems after a production platform migration last year. Figure 2 shows the new production platform, which consists of a two-node failover cluster, one log shipping server, two replica servers, and a dedicated remote replication distributor.

Figure 2: The production platform in which the three techniques were applied

After completing the migration, we encountered some problems with the new failover cluster. To remediate the immediate performance problem, we failed over the database from the cluster to the log shipping server and reconfigured log shipping to one of the replica servers. The failover also gave us the ability to take the cluster offline as needed for troubleshooting.

After the cluster’s problems were resolved, we scheduled some downtime to fail back the cluster. During that time, we failed over the database from the log shipping server to the cluster, reversed the log shipping back to the log shipping server, and reconfigured the replica server as a subscriber to the publication on the cluster—all of which took less than 15 minutes. It took longer to reconfigure the front-end web servers to point to the cluster. Overall, the system was down for only a half hour.

As this real-world example illustrates, the three log shipping techniques that I discussed can minimize downtime when problems arise. They can also save you a lot of time when performing tasks such as initializing transactional replication and performing server maintenance. If you’d like to see a demonstration of the techniques, check out the video at the SQL Soldier web page. In this demo, I perform a log shipping failover, configure log shipping back to the original server, and configure replication to a third server.

1. What is High Availability in SQL Server?

A high availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized.SQL Server high-availability solutions improve the availability of servers or databases.

2. What all High Availability options are available in SQL Server?

At a high level, there are five main high availability options including a new feature set to be release with SQL Server 2012:

• Log Shipping
• Mirroring
• Replication
• Clustering
• Always ON

3. What is Log Shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all. The key feature of log shipping is that it will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers Databases in “synch”.

4. What are the Benefits of Log Shipping?

Benefits of log shipping, let’s take a more comprehensive look:

• Log shipping doesn’t require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement.
• Once log shipping has been implemented, it is relatively easy to maintain.
• Assuming you have implemented log shipping correctly, it is very reliable.
• The manual fail over process is generally very short, typically 15 minutes or less.
• Implementing log shipping is not technically difficult.

5. What the drawbacks of Log Shipping?

• Log shipping fail over is not automatic. The D B A must still manually fail over the server, which means the D B A must be present when the fail over occurs.
• The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.
• Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.
• The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for fail over can still be used normally.
• When it comes time for the actual fail over, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user’s applications to the new standby server. In some cases, neither of these options is practical.

6. In which recovery model Log Shipping can be configured?

Log Shipping works with Full and Bulk Logged recovery model.

7. Is it possible to configure Log Shipping from lower version to upper version and Vice versa?

Yes it is possible to configure Log Shipping from lower to upper version. But it is not possible vice versa.

8. What is Log Shipping Monitor Settings?

This setting enables us to setup a monitor on the Log shipping through which we can monitor the log shipping process.

9. What all jobs are created after configuring the Log Shipping?

Internally when Log Shipping is configured, there are 4 jobs created between Primary Server and Secondary Server, they are Backup Job, Copy Job, Restore Job and Alert Job

• Backup job: This job is created on Primary Server; this job takes the transaction log backup of the Database on a scheduled time
• Copy Job: This job is created on Secondary Server, this job Copies the transaction log Backup from Primary Server to the Standby/Secondary Server.
• Restore Job: This job is created on Secondary Server; this job restored the copied transaction log backup on the Secondary Server.

10. What permissions are required for shared folders on Primary and secondary for the service accounts?

• For the backup job, read/write permissions to the backup directory are required to the following:

SQL Server service account on the primary server instance.

Proxy account of the backup job. By default, this is the SQL Server Agent account on the primary server instance.

• For the copy job, read permissions to the backup directory and write permissions to the copy directory are required by the proxy account of the copy job. By default, this is the SQL Server Agent account on the secondary server instance.
• For the restore job, read/write permission to the copy directory are required by the following: The SQL Server service account on the secondary server instance. The proxy account of the restore job. By default, this is the SQL Server Agent account on the secondary server instance.
  

11. What is the copy Job in Log shipping and on which instance it’s created?

• Created in secondary server for every log shipping configuration.
• Copy the backup files from backup folder into copy folder.
• It deletes old files and old history from copy folder.
• On backup folder we have to grant read permission to secondary server account and read-write permissions on copy folder.

12. What is Monitor server?

An optional instance of SQL Server that tracks all of the details of log shipping, including:

• When the transaction log on the primary database was last backed up.
• When the secondary servers last copied and restored the backup files.
• Information about any backup failure alerts.

13. What are Log Shipping System Tables and in which database these are stored?

SQL Server Log Shipping System Tables

• log_shipping_monitor_primary – Stores one monitor record per primary database in each log shipping configuration.
• log_shipping_monitor_secondary – Stores one monitor record per secondary database in a log shipping configuration.
• log_shipping_primary_databases – Stores one record for the primary database in a log shipping configuration.
• log_shipping_secondary – Stores one record per secondary ID

14. What are Log Shipping System Store Procedures?

SQL Server will issue a series of steps to synchronize the information between primary/secondary server and the monitor server. This can be implemented by running the below undocumented log shipping stored procedures:

• s p_process logs hipping monitor primary
• s p_process log shipping monitor secondary

15. Is it possible load balance in Log Shipping?

Yes, it’s possible in log shipping, while configuring log shipping we have the option to choose standby or no recovery mode, there we select STANDBY option to make the secondary database read only.

16. What is STANDBY Mode on the secondary database?

We can reduce the load on our primary database by using a secondary server for read-only query processing. To do this, the secondary database must be in STANDBY mode.

17. What’s the difference between the secondary being in “Restoring” vs. “Standby”?

There are two options for configuration when we place the secondary database in standby mode:

• We can choose to have database users disconnected when transaction log backups are being restored. If we choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.
• We can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.

18. What is T U F file?

T U F stands for Transaction Undo file.

19. What will happen to Log Shipping if T U F file is corrupted or lost?

The log shipping will not work. We have to setup the Log Shipping again.

20. If you create a Job on the Primary database server, will it automatically be created on the secondary server or not?

No, it will not be created on the secondary server.

21. If you create a user on the Primary database, will it automatically be created on the secondary or not?

Yes, it will be created automatically on the secondary database.

22. If you add a file on the Primary database in the same location which exists on the target, will it automatically be created on the secondary or not?

Yes, it will be created automatically on the Secondary database if the file is added to the Primary database.

23. If you add a file on the Primary database in a location which does not exist on the target, will it automatically be created on the secondary or not?

No, Log Shipping will hang. We have to manually restore the Log backup with MOVE option on the secondary database to rectify the issue.

24. Is it possible to configure Log shipping on the database server with different collation?

No.

25. Can we configure Log Shipping between the different domains?

Yes, we can configure Log Shipping on the server residing in different domains.

26. What are the store procedures to monitor Log Shipping?

We can execute the below Log Shipping System Stored Procedure to monitor log shipping and get detailed information about log shipping.

• s p_help_log_shipping_monitor – This is the how SQL Server generates the Log Shipping Status report by executing
• s y s.s p_help_log_shipping_monitor – This procedure returns the log shipping status (whether it is healthy or not) as well as metadata such as primary and secondary database names, time since last backup, last backup file, last restore file, etc…
• s p_help_log_shipping_monitor_primary – returns all columns from the log_shipping_monitor_primary table for the specified primary log shipping database. It returns server name, database name, time of last backup, backup threshold, threshold alert and history retention period.
• s p_help_log_shipping_monitor_secondary – returns all columns from log_shipping_monitor_secondary table for the specified secondary log shipping database. It will return database name, server name, restore threshold, last copied file, time of last copy / restore and history retention period.

27. Can we setup multiple secondary databases in Log Shipping?

Yes, we can setup multiple secondary databases in Log Shipping.

28. Can we shrink log shipped database log file?

Yes, we can shrink the log file, but we shouldn’t use WITH TRUNCATE option. If we use this option obviously log shipping will be disturbed.

29. Can we take full backup of the log shipped database in primary server?

Yes it’s possible. We can take full backup of log shipped database and this won’t affect the log shipping.

30. What editions of SQL Server is log shipping available in?

• 2012 – Enterprise, Business Intelligence, Standard, and Web
• 2008 R 2 – Data center, Enterprise, Standard, Web, and Work group
• 2008 – Enterprise, Standard, Web, and Work group
• 2005 – Enterprise, Standard, and Work group

31. Can we take full backup of the log shipped database in secondary server?

No, we won’t be able to execute BACKUP command against a log shipped database in secondary server.

 References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

Resources

Ref:

h t t p://w w w .d b a mantra.com/sq l-server-d b a-interview-questions-answers-log-shipping/

h t t p://w w w . s q l shack.com/s q l-server-log-shipping/

https://www.brentozar.com/archive/2013/03/log-shipping-faq/

http://www.sqlshack.com/migrate-logins-database-different-server/

http://sqlmag.com/database-high-availability/3-log-shipping-techniques