Tuesday 25 August 2015

Setting up log shipping with MSSQL

Log shipping is a process that allows you to create a secondary copy of a database for failover / backup purposes by transporting logs from one database to the other by means of backing up and restoring logs between the primary and secondary (AKA standby) database.

TYpically log shipping should be performed between two of the same MSSQL version, although it is possible to perform it between different versions - not all variations are supported (check firstly!)

The SQL Server Agent handles and processes the log shipping and is typically setup on the primary source.

You should ensure the database you wish to mirror has it's recovery plan to either 'Full' or 'Bulk Loggged'

To setup log shipping you should firstly go to the database's properties >> Transaction Log Shipping >> and tick 'Enable this as a primary database in a transaction log shipping configuraiton'. Proceed by hitting the 'Backup Settings' button to specify when you wish to backup the database logs e.g. every 15 minutes.

Specify the backup location, job name, compression settings and so on and hit OK.

Specify the secondary database in the 'Secondary Databases' section by clicking on 'Add'. Specify the secondary database and server. Within the 'Initialize Secondary Database' tab we will select "Yes generate a full backup of the database...", within the "Copy Job" tab specify the destination folder for where the coppied logs from the primary server will be stored on the secondary server and give the copy job a name. Within the 'Restore Transaction Log' tab select whether the (secondary) database can be read during a restore operation ('No Recovery' or 'Standby' Mode.)

Hit OK and within the database properties >> 'Transaction Log Shipping' tab again and ensure 'Use a monitor service instance' is ticked (under Monitor Service Instance section) - this will provide you will details of transaction log history and help you keep track of operations.)


Post a Comment