The Starplex forum

Set up Transaction log shipping SQL 2008

Got any useful information to offer? or perhaps you need to ask for help.

Set up Transaction log shipping SQL 2008

Postby Deathlok » Fri Dec 16, 2011 1:40 pm

SQL Server 2008 R2
How to: Enable Log Shipping (SQL Server Management Studio)

This topic explains how to enable and configure log shipping using SQL Server Management Studio.

Prerequisite: Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server. This is a share of the directory where the transaction log backups will be generated. For example, if you back up your transaction logs to the directory c:\data\tlogs\, you could create the \\primaryserver\tlogs share of that directory.

To enable log shipping
1. Right click the database you want to use as your primary database in the log shipping configuration, and then click Properties.
2. Under Select a page, click Transaction Log Shipping.
3. Select the Enable this as a primary database in a log shipping configuration check box.
4. Under Transaction log backups, click Backup Settings.
5. In the Network path to the backup folder box, type the network path to the share you created for the transaction log backup folder.
6. If the backup folder is located on the primary server, type the local path to the backup folder in the If the backup folder is located on the primary server, type a local path to the folder box. (If the backup folder is not on the primary server, you can leave this box empty.)

Important
If the SQL Server service account on your primary server runs under the local system account, you must create your backup folder on the primary server and specify a local path to that folder.

7. Configure the Delete files older than and Alert if no backup occurs within parameters.
8. Note the backup schedule listed in the Schedule box under Backup job. If you want to customize the schedule for your installation, then click Schedule and adjust the SQL Server Agent schedule as needed.
9. SQL Server 2008 Enterprise supports backup compression1. When creating a log shipping configuration, you can control the backup compression behavior of log backups by choosing one of the following options: Use the default server setting, Compress backup, or Do not compress backup. For more information, see Log Shipping Transaction Log Backup Settings2.
10. Click OK.
11. Under Secondary server instances and databases, click Add.
12. Click Connect and connect to the instance of SQL Server that you want to use as your secondary server.
13. In the Secondary Database box, choose a database from the list or type the name of the database you want to create.
14. On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.

Note
If you choose to have Management Studio initialize the secondary database from a database backup, the data and log files of the secondary database are placed in the same location as the data and log files of the master database. This location is likely to be different than the location of the data and log files of the primary database.

15. On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder into which the transaction logs backups should be copied. This folder is often located on the secondary server.
16. Note the copy schedule listed in the Schedule box under Copy job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.
17. On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.
18. If you chose the Standby mode option, choose if you want to disconnect users from the secondary database while the restore operation is underway.
19. If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.
20. Choose an alert threshold under Alert if no restore occurs within.
21. Note the restore schedule listed in the Schedule box under Restore job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.
22. Click OK.
23. Under Monitor server instance, select the Use a monitor server instance check box, and then click Settings.

Important
To monitor this log shipping configuration, you must add the monitor server now. To add the monitor server later, you would need to remove this log shipping configuration and then replace it with a new configuration that includes a monitor server.

24. Click Connect and connect to the instance of SQL Server that you want to use as your monitor server.
25. Under Monitor connections, choose the connection method to be used by the backup, copy, and restore jobs to connect to the monitor server.
26. Under History retention, choose the length of time you want to retain a record of your log shipping history.
27. Click OK.
28. On the Database Properties dialog box, click OK to begin the configuration process.
It ain't the years honey, it's the mileage.
User avatar
Deathlok
Doomsday Machine
 
Posts: 253
Joined: Wed Jan 19, 2011 5:49 pm
Location: United Kingdom

Return to Hints and Tips

  • Who is online

    Users browsing this forum: No registered users and 14 guests

cron