12/27/2014

Jon Morisi's Configuring MS SQL Log Shipping

Contents

Create empty databases on the secondary server
Create folders for transaction logs
On the primary
On the secondary
Export logins 
Review Maintenance Plans 
Enable TCP/IP Protocols
Configure Log Shipping
On the primary
Configure Alerts 
On the primary
On the secondary
Verify Setup

Create empty databases on the secondary server

1.       In SQL Server Management Studio, right click the server and choose new database
2.       Be sure to configure the file locations to the same location as on the primary

 

Create folders for transaction logs

In a domain environment, make sure the SQL Service is running as a domain login.  When you configure the share give this user full control.  In a non-domain environment use the same username and password on both the primary and secondary.  This will create pass through authentication:
http://support.microsoft.com/kb/321247

On the primary

1.       Create a folder for the transaction logs on the Primary server:
2.       Configure this as a shared folder

On the secondary

1.       Create a folder for the transaction logs on the Secondary server:
2.       Configure this as a shared folder named

Export logins from the primary

1.       Use a query to export logins from the primary server. 
2.       Run script on secondary, to create logins on the secondary.  Make sure “Create empty databases on the secondary server” has been completed first.



Review Maintenance Plans
Transaction log backups should not be running on the primary. 
Backing up the transaction log independently will disrupt the log shipping process.

1.      Remove log shipped databases form any transaction log backup maintenance plan or job. If the log shipping databases are the only FULL recovery model databases, remove the Maintenance plan or job.
2.      If there is a Maintenance plan to remove transaction log files after x number of hours on the primary, remove any log shipped databases from the maintenance plan.  If the log shipping databases are the only FULL recovery model databases, remove the Maintenance plan or job.

Enable TCP/IP Protocols



Configure Log Shipping

On the primary

In SQL Server Management Studio, right click the database to configure and click properties

1. Click Enable this as a primary database in a log shipping configuration
2. Click Backup Settings
     a. Enter a network path to the backup folder by IP (EX: \\10.10.10.10\LS)
     b. If this is a local folder enter the folder location here
     c. Configure retention for Transaction logs here, default is 72 hrs
3. Click Add under,  Secondary server instances and databases
     a. Under Initialize Secondary Database - MS SQL server will backup and restore the primary database to the secondary server.
          i. Choose yes; generate a full backup of the primary database.
               OR
          ii. Choose yes; restore an existing backup o the primary database.
          Use option 1 for smaller databases.  If you use option 2 be sure to take a full backup and                copy this to the secondary, entering the local path under  Backup File”
     b. Under Copy Files enter the location created in "Create folders for transaction logs"
     c.  Under Restore Transaction Log choose from:
          i. No recovery mode - use for availability reasons only
          ii. Standby mode - read-only access is allowed, used for scalability.  If you chose the Standby mode option, click the radio button to disconnect users from the secondary database while the restore operation is underway.
4.  If a monitor server is required, click use a monitor server instance and then click settings.  Configure the instance connection in the resulting dialog box.

Configure Alerts for replication job failure

The following jobs and alerts should be automatically created during setup.  You will need to manually set the notifications to Email, your operator, when the job/alert fails:

On the primary

Jobs

1.       LSAlert_[primary server name]
2.       LSBackup_[database name]
Alerts
3.       Log shipping Primary Server Alert.
4.       Log shipping Secondary Server Alert.

On the secondary

Jobs

1.       LSAlert_[secondary server name]
2.       LSCopy_[primary server name]_[database name]
3.       LSRestore_[primary server name]_[database name]
Alerts
4.       Log shipping Primary Server Alert.
5.       Log shipping Secondary Server Alert.



Verify Setup

1. Review the error log
2. Verify trn files in the location created in "Create folders for transaction logs" on both the primary and secondary servers.
3. View the SQL Server Agent, Job Activity Monitor.  You should see the copy and restore jobs completing successfully.
4. View the Log Shipping Report – this can be done from the primary, secondary or monitor server.
     a. Right-click the server instance in Object Explorer, point to Reports, and point to Standard Reports.
     b. Click Transaction Log Shipping Status.