Log shipping and its configurations

Log shipping 

What is log shipping??

Log shipping is a term used to describe the process of taking a transaction log backup from a primary database, and restoring the transaction log backup on a secondary database.

What is the need for log shipping:

Among the more common reasons:

• To have a standby database ready in case of a service disruption to the primary database 
• To have a read-only copy of the primary database available on another server, in order to lighten the load on the primary server 
• To have multiple copies of the database available in different locations 

 The backup is the easy part. We just need to ensure that your database is running the full

or bulk-logged recovery models.                       

SQL Server provides a GUI interface to assist us in setting up log shipping between a primary database and one or more secondary databases. However, it is only available in certain editions of SQL Server.  In SQL Server 2000, it is available only in the Developer and Enterprise editions. In SQL Server 2005 and above , it is available in all editions except the Express edition. Using the SQL Server log shipping configuration may not always be suitable, for e.g. if the primary and secondary databases are not linked. We can always set up log shipping manually, but we will need to address the above mentioned issues.

NOTE: While it is possible to ship logs from a SQL Server 2000 database to a SQL

Server 2005 instance by setting up log shipping yourself, the database can only be in a

recovery state, not in a read-only state. This is because when SQL Server 2005 makes the

database read-only, it upgrades the database's internal structures too, making it impossible to apply further non-SQL 2005 transaction logs.

Prerequisite to do log shipping:

1. Database should be Backed up from primary server and Restored to secondary server.
2. Both servers should be connected
3. SQL Server agent’s service account must have read/write permission to the below network shares.
1. \\Primary Server\logshipping.
2. \\Secondary Server\logshipped. 

Backup / Restoration Database:

1. Take a full backup of “DBA” database to a local folder in Primary server
2. Copy the backup file to a local folder in Secondary server
3. Restore the backup file with “NoRecovery”
4. Change the database to “Standby mode” while restoring.

Do’s for logshipping:

1. Always take backup using the below command.

         BACKUP DATABASE mdb TO DISK=<@tmstmp> WITH INIT, COPY_ONLY 

    Note: change the disk name in the above command before execution

1. Shrink log using DBCC Shrinkfile is possible
2. Any user created in primary should be in disabled status (except SA and SYSadmins)
3. Any issue on Logshipping should be fixed within 48 hours.

Don’ts for logshipping:

1. Don’t run Full backup, Log backup(includes truncate) or differential backup.
2. Don’t change database settings at source except altering or creating database files.
3. Don’t enable any user in primary server (except SA and SYSadmins)

CONFIGURATION:

How to Configure Logshipping