Database Log Shipping

Database Log Shipping is been around since SQL Server 7.0, It's a High Availability solution at the Database Level. It is categorized as a "Warm Standby" which means that the Secondary Database Server or the Fail-Over Partner is not always in-sync with the Primary Database Server and the main reason for it is because the Primary Server does not send the transaction as it happens to the second database server. Log Shipping is controlled by the SQL Server Agent Job, what happen is the primary server has a backup job that back-ups the transaction log at a specified intervals to a network share, then the secondary server will copy the transaction log and applied it in the Secondary Server.

I have 3 server for my demo for database log Shipping, Server 1 would be my Domain Controller and DNS Server (Domain), Server 2 would be my primary SQL Server (SQL1) and Server 3 would be my Secondary or Fail-Over Partner (SQL1).


The first thing you need to do is to log-on to our Primary Server which is SQL1 and then create a folder and name it as "Primary Logs". Make our Primary Logs folder a Shared Folder and make sure that the Service account that is running the SQL Server Agent has administrative access to our Primary Logs folder, always remember that it is a best practice that the Account that is running our SQL Server Agent is a Domain Account. Our Primary Logs Folder would served as a Repository of our transaction log backup.



Go to our Secondary Server which SQL2 and then create a Folder named "Secondary Transaction Logs", make that a shared folder and make sure that the Account that is running our SQL Server Agent has administrative rights. The Transaction Logs from our Primary Server will be copied over our Secondary Server so that our Secondary Server will be in-sync in case of a Fail-Over.






We can now open our SQL Server Management Studio and connect to our Primary Server (SQL1), in this demo I'm using a Windows Authentication, you can also use SQL Server Authentication as long a the account has administrative access on the Primary Server.




After establishing a valid connection to our Primary Server, we can now create our new database for our Log Shipping, right click on the Database Folder and hit "New Database".









Type-in the Database name of our New Database, in this demo I used "Employee" and then leave the Primary Filegroup and the Transaction Log on it's default value, but always remember that in a Production Environment it is recommended that you Seperate the Physical disk of your filegroups.









After successfully create our new database, we can now configure our database log shipping. Righ-Click on the "Employee" Database and click the database properties.

In the Database Properties in the select page, click the Transaction Log Shipping and make sure that you enable the primary server in a log shipping configuration, and then click on the Backup Settings.