How to configure log shipping in SQL Server 2014

There are many different ways that you can configure SQL for high availability, mirroring, failover cluster using shared disks, AlwaysOn, log shipping, replication.  This guide describes how to configure mirroring in SQL Server 2014.

Log Shipping
It provides database redundancy by sending transactional log backups periodically to a standby server or servers for the entire database. Transaction logs are automatically backed up, copied, and restored on the standby server(s). If the active server goes down, the standby server can be brought up by restoring any remaining shipped logs and then the database is recovered for use.

Lab:
====
Add the following DNS entries on the Domain Controller
dc.mylab.local – 192.168.11.200 – DC
server1.mylab.local – 192.168.11.201 – SQL1
server2.mylab.local – 192.168.11.202 -SQL2

Download SQL 2014 R2 Eval
http://www.microsoft.com/download/en/details.aspx?id=29066

Server1.mylab.local
1. Intall two standalone SQL servers and make sure both SQL Server services accounts run as a domain user
2. Configure a network share on DC and a second network share on server2 and grant it access to SQL service account permissions, both on NTFS and share level
3. Right-cllick on Database > Properties, Transaction Log Shipping, click Backup settings.

Server1-2016-07-09-08-45-34

Specify a network share where the logs will be shipped to, I created a network share on my DC and called in \\DC\MyShare

Server1-2016-07-09-08-45-48

Once you click on OK in the above screenshot click on Add, and connect to the secondary SQL server.

Server1-2016-07-09-08-47-17

Specify a second network share located on the secondary SQL server.  I create one called \\Server2\server2_myshare

Server1-2016-07-09-08-49-12

Leave the defaults here

Server1-2016-07-09-08-49-23

All done

Server1-2016-07-09-08-49-41

 

 

Server2.mylab.local
Nothing needs to be done here configure mirroring, everything is configured from server1.mylab.local.

After enabling log shipping, the secondary database will show up as: Test (Restoring), if the primary server goes down you have to manually bring this database online by running the below command.
RESTORE DATABASE Test WITH RECOVERY

Server2-2016-07-09-08-50-03

 

This entry was posted in Microsoft and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *