Log shipping allows you to keep two SQL Server databases synchronized for high availability. In a log shipping relationship, the primary server transfers copies of its transaction logs to the secondary server on a periodic basis. The secondary server receives those logs and uses them to update its copy of the database so that it is then in the same state as the database on the primary server.
Difficulty: Average
Time Required: One hour
Here's How:
- Create the target database on the secondary server by restoring a full backup of the database from the primary server. Be sure to include backups of the transaction logs by using the WITH NORECOVERY option.
- Create a network file share on the primary server (or another location available to both servers) that will store the transaction log backups.
- Connect to the primary server using SQL Server Management Studio and expand the Databases folder. Locate the database you wish to replicate, right-click on it and choose Tasks -> Ship Transaction Logs from the pop-up menu.
- Select the "Enable This as a Primary Database in a Log Shipping Configuration" check box.
- Click the Backup Settings button.
- In the network share textbox, enter the path to the share you created in Step Two. If the share is on the primary server, also enter the path in the second textbox.
- Click OK to close the Transaction Log Backup Settings window.
- In the Secondary Databases section of the Transaction Log Shipping properties page, click the Add button.
- Click the Connect button and provide connection details for the secondary server that will receive the transaction log backups from the primary server.
- Verify that the Secondary Database drop-down box contains the name of the database you created on the secondary server in Step One.
- Select the Copy Files tab.
- Provide the destination folder for the copied files on the secondary server. This is a temporary storage location, so you may choose any path you wish.
- Review the other settings and make any changes you deem appropriate, then click the OK button to continue.
- Repeat Steps Nine through Thirteen for any additional secondary servers if you wish to configure more than one transaction log recipient.
- Click OK to close the Database Properties window. SQL Server will then display a status window while it configures transaction log shipping.
What You Need:
- Microsoft SQL Server 2008
- SQL Server Management Studio
SHARE
