What is SQL Server Log Shipping:

 Log Shipping allows replicating databases using the database transaction log. The transaction log contains all the transactions in the database. The main idea is to copy the transaction log to a secondary database in order to replicate the information. So, you need a Primary Server with a database, configure log shipping to have some transactional log backups, and then copy it to a secondary database to have a replica.

Log Shipping Involves below steps

Backup Job – Backup the transaction log file on the primary SQL Server Instance or any backup share.

Copy Job – Copy the transaction log backup file across the network to one or more Secondary SQL server instances

Restore Job – Restore the copied transaction log on the secondary SQL server instance.

Terms related to Log Shipping

Primary Server -The instance of SQL Server, which is your production server is called as the primary server.

Primary Database - The database on the primary server which you want to backup to another server is called as the primary database.

All Log Shipping administrative activities are performed from the primary database with the help of SQL Server management studio.

Secondary Database - The worm standby copy of the primary database is called as secondary database.

The secondary database can be in below two states

• Recovering
• Standby

Monitor server

Monitor Server tracks all the details of log shipping. It includes below things

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

Monitor server is optional.

Note – We cannot make any changes in monitor server configuration without removing log shipping.

Operating Modes

There are two modes and they are based on the state in which the secondary database will be

• Standby mode – The database is available for querying and users can access it, but in read only mode. The database will not be available when restore process is running.
• Restore mode – The database is not accessible

TUF file

TUF stands for “Transaction Undo File”.

This file contains information regarding any changes that were made as part of incomplete transactions at the time the backup was performed.

It is required if a database is loaded in read state. In this state further transaction log backups may be applied.

It is created while performing log shipping to a server in standby mode.

If TUF file is corrupted or lost, log shipping will not work, and we need to setup it again.

WRK File

The WRK files are produced when the transaction log backups are copied from the backup location to a secondary server.

Once the copy operation completed successfully file renamed to “.trn” file.

It ensures that the files are not picked up by restore job until it successfully copied.

Permissions

The usage of sqllogship involves the use of Windows Authentication . The Windows Authentication account running the command must have access to the Windows directory and fulfill the requirements of SQL Server Permissions , which vary depending on whether the command includes the -backup, -copy, or -restore option.

Option	Directory access	Permissions
	-backup	Needs both read and write permissions for the backup folder.	The permissions needed are identical to those required for the BACKUP statement. Additional details can be found in the Transact-SQL documentation for BACKUP.
	-copy	Necessary permissions include the ability to read from the backup directory and the ability to write to the copy directory.	The permissions needed are identical to those required by the stored procedure sp_help_log_shipping_secondary_database.
	-restore	Access to both read and write functions is necessary for the copy directory.	The permissions required for this are identical to those needed for the RESTORE statement. Additional details can be found in the RESTORE (Transact-SQL) documentation.

 

sqllogship Application:

The sqllogship application performs a backup, copy, or restore operation and associated clean-up tasks for a log shipping configuration. The operation is performed on a specific instance of Microsoft SQL Server for a specific database.

 

sqllogship Application:

Primary Node: Log Shipping: SQL Jobs: sqllogship (Application Executable):

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Backup ...

Secondary Node: Log Shipping: SQL Jobs: sqllogship (Application Executable):

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Copy ...

C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Restore ...

Failing Over:

To fail over to a secondary database:

1.      Copy any uncopied backup files from the backup share to the copy destination folder of each secondary server.

2.     Apply any unapplied transaction log backups in sequence to each secondary database. For more information, see Apply Transaction Log Backups (SQL Server).

3.     If the primary database is accessible back up the active transaction log and apply the log backup to the secondary databases. You may need to set the database to single-user mode to obtain exclusive access before issuing the restore command, and then switch it back to multi-user after the restore completes.

4.      If the original primary server instance is not damaged, back up the tail of the transaction log of the primary database using WITH NORECOVERY. This leaves the database in the restoring state and therefore unavailable to users. Eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database.

5.     For more information, see Transaction Log Backups (SQL Server).

6.      After the secondary servers are synchronized, you can fail over to whichever one you prefer by recovering its secondary database and redirecting clients to that server instance. Recovering puts the database into a consistent state and brings it online.

7.      After you have recovered a secondary database, you can reconfigure it to act as a primary database for other secondary databases.

8.     If no other secondary database is available, see Configure Log Shipping (SQL Server).

Different ways to monitor Log Shipping for SQL Server databases

Log Shipping is a basic SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover or reporting purposes.

You can use the below items to investigate if there are any issues with your databases that are setup for Log Shipping.  We will cover each of these items and how they can be used.

• SQL Server Error Log
• SSMS Built In Report
• System Stored Procedures
• Query the MSDB database
• Application/System EventViewer Log

 

I’ve gotten a lot of great questions about log shipping, so I’ve put together an FAQ.

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

What are the phases of sql server database restore process?

There are two types of recoveries:

1) Restart Recovery

·        Every time an instance is restarted/ started the consistency of all the databases including master, model, msdb and tempdb is checked.

·        This process is an internal operation and initiated just to keep the entire instance clean and with integrity.

·        Taking database offline/online involves restart recovery for that database.

2) Restore Recovery

·        As per backup strategy whenever a restore is started and recovery is done per backup sequence.

·        This entire process of recovery initiated manually is called as Restore recovery.

Following the data copy phase involving copying of all the data, log, and index-pages from the backup media of a database to the database files, four consecutive phases that take place during SQL Server recovery are

1.       Discovery – is to find the logical structure of the Transaction log file.

2.       Analysis – is to find the best LSN starting from which rolling forward can be done during redo phase.

3.       Redo – is the phase during which the changes caused by active transactions (at the time of crash) are hardened onto Data files.

4.       Undo – is the phase where in, rolling back of the active transactions for consistency, takes place.

Discovery Phase:

The first phase of recovering a database is called discovery where all the VLFs are scanned (in serial and single threaded fashion) before actual recovery starts. Since this happens much before the analysis phase, there are no messages indicating the progress in the SQL Server error log. Depending on the number of VLFs this initial discovery phase can take several hours even if there are no transactions in the log that need to be processed.

This is the reason why it is preferred to have optimal number of VLFs in a log file.

For further information on influence of VLFs in Transaction Log throughput please refer point #8 in Kimberly Trip’s blog http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

 

Analysis Phase:

Preparation of ‘Dirty Page Table’ (DPT) and ‘Active Transaction Table’ (ATT) are the prime motives of Analysis phase. These two tables are put to use by SQL Server during subsequent redo and undo phases respectively.

To create DPT, SQL Server requires to make a note all the pages and their LSNs that might have been dirty (à not yet hardened) at the time of crash, from the transaction log (.ldf), so that during redo phase all such pages will be rolled forward and at the end of redo phase the database would be in such a state as if it was just before crash.

As all the pages prior to last checkpoint would have been already hardened and the pages after the last checkpoint are the ones that are dirty but yet to get hardened. Hence analysis phase starts (in the sense SQL Server starts reading using the .ldf) from the last checkpoint LSN till end of transaction log.

Scanning through the transaction log from the latest checkpoint till end of transaction log prepares the list of all pages that are dirty and obviously not hardened as they are after checkpoint. This list is the DPT. The minimum of all the LSNs available from DPT will be the minimum recovery LSN. Similarly using transaction log file, active transaction table is generated.

Redo Phase:

“Rolling forward all the changes that took place after the checkpoint and just before the crash so that at the end of redo phase the db would be in a state as if it was just before the crash”, is the intent of redo phase.

Hence making use of minimum recovery LSN obtained from DPT, starting from the minimum recovery LSN and till the LSN at end of transaction log, SQL server rolls forward (hardens) all the changes that are present in all the dirty ( not yet hardened) pages and brings the db to the desired state.

Undo Phase:

Ensuring that the data integrity is not hampered so that db can be opened for access is the aim of Undo phase. For this, all the changes made by all the transactions that were active at the time of crash are to be rolled back.

Hence, SQL server, using ATT , starting from LSN at the end of transaction log will rollback all the changes caused by all the active transactions till the LSN of beginning of oldest transaction(among the active transactions present in ATT), which is available from transaction log and opens the database for user access.

https://dba.stackexchange.com/questions/172003/very-slow-sql-server-database-recovery

 

RECOVERY vs NORECOVERY vs STANDBY

 

RECOVERY

RECOVERY is The default option. This indicates that the restore is complete and that once RESTORE is complete it is fully available. No further backups can be applied to the database as the db engine cannot guarantee consistency between what has changed in the fully recovered database and if the further backup files can be restored successfully.

use master

go

restore database adventureworks2014 with recovery

NORECOVERY

The database is in a state of RESTORING even after this RESTORE is complete as there are further RESTORE statements expected (eg t-log or differential). I’m just going to specify NORECOVERY here rather than restore from a full backup then a log backup as I do this in a further demo below.

use master

go

restore database adventureworks2014 with norecovery

 

STANDBY

Similar to NORECOVERY except that the database will accept read only connections. To do this any uncommitted transactions in the backup will be rolled back and stored in a transaction undo file (tuf.) Whilst users are running queries against the database no further restores can continue until all queries are complete (though this is not the case with log shipping.) When the next restore occurs, those uncommitted transactions in the tuf file will be rolled forward and the next log is restored.

use master

go

restore database adventureworks2014 with standby

Native SQL Server Backup Types and How To Guide

 

Full Backup

A full database backup backs up the whole database. It includes some part of the transactional log so that you could restore your database to the point when the full backup was finished. Usually, files with full database backup have ‘.bak’ extension. It is recommended to periodically create full backups, but since it contains transaction log along with whole database data it takes significant space. To create full backup use the following command:

BACKUP DATABASE Adventureworks TO DISK = 'adventureworks.bak'

 

Full Database backup internals

1.     Force a database checkpoint and make a note of the log sequence number at this point. This flushes all updated-in-memory pages to disk before anything is read by the backup to help minimize the amount of work the recovery part of restore has to do.

2.     Start reading from the data files in the database.

3.     Stop reading from the data files and make a note of the log sequence number of the start of the oldest active transaction at that point

4.     Read as much transaction log as is necessary.

 

Backup Start LSN: This is basically "Checkpoint LSN"( As mentioned by Paul Randal in Understanding SQL Server Backups) this is recorded when backup starts and checkpoint is fired.

Backup End LSN: This is basically reading the transaction log again and recording 2 things

1.     The LSN of oldest active transaction.

2.     The LSN when backup operation started, For this I am not sure whether this happens or not IMHO this should be same as Checkpoint LSN or Backup start LSN but may be SQL Server is doing this 2nd time to confirm. I believe it does only what I have mentioned in point 1.

Minimum LSN: The would be minimum of ( LSN of oldest active transaction, Checkpoint/backup start LSN ). The definition in the BOOK is little bit confusing.

 

Differential Backup

A differential backup contains only the data that has been changed since the last full database backup was created. Creating differential backup usually takes less time than a full backup, because you back up only modified data instead of backing up everything.

 

SYNTAX: BACKUP DATABASE TestDB  TO DISK= ‘PATCH’  WITH DIFFERENTIAL;  

GO 

 

Transaction Log Backup

A transaction log (T-log) backup is the most granular backup type in SQL Server because it backs up the transaction log which only contains the modifications made to the SQL Server database since the last transaction log backup. It’s effectively an incremental backup.

 

Transaction Log Backup

Backup log <Your database name> to disk = '<Backup file location + file name>'

 

SQL Server Backup Options

 

Backup compression

In addition, it is possible to compress your backup using the option WITH COMPRESSION. This option will compress your backup:

BACKUP DATABASE Adventureworks TO DISK = 'c:\backup\full.bak' WITH FORMAT, COMPRESSION

 

Copy-Only Backup

Use the COPY_ONLY option if you need to make additional full or transaction log backups which will occur beyond the regular sequence of SQL Server backups. To perform copy-only backup simply add the “COPY_ONLY” clause:

BACKUP DATABASE Adventureworks TO DISK = 'full.bak' WITH COPY_ONLY

 

File and Filegroup Backups

These backup types allow you to backup one or more database files or filegroups. To execute file backup use the following command:

BACKUP DATABASE Adventureworks FILE = 'File' TO DISK = 'File.bck'

Use this command to perform filegroup backup:

BACKUP DATABASE Adventureworks FILEGROUP = 'Group' TO DISK = 'Group.bck'

 

Partial Database Backup

Typically partial backups are used in a simple recovery model to make backups of very large databases that have one or more read-only filegroups. However, SQL Server also allows making partial backups with full or bulk-logged recovery models. Use the following T-SQL command to create a partial backup:

BACKUP DATABASE Adventureworks READ_WRITE_FILEGROUPS TO DISK = 'partial_backup.bak'

 

 SQL backup strategies and recommendations: