FIM 2010 R2 Disaster Recovery solution: Setting up SQL Log Shipping for the Sync Engine database

There are several DR solutions for the FIM database, all depends on the time of recovery that you are comfortable with. You can do a daily full backup and copy that to the DR server daily,  that gives you a 24hr data lag when you have to go to DR. You can also do SQL Mirror or Cluster or Replication or the relatively simple one which is Log shipping. Log shipping basically involves the automatic copy of your transaction logs to the DR DB server.

Overview

A job on the production SQL server will copy the transaction logs every 15 minutes to a folder the server. A job on the DR server will copy the files from the production and another job will apply the transaction log every 15 minutes.

sqllogshipping

While it is a relatively simple process since the wizard really does everything for you, the per-requisites are very important. You miss that and you are dealing with errors galore.

Prerequisites

  1.  The primary and DR database should use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning.
  2.  Before you configure log shipping, you must create a share on the Prod server to make the transaction log backups available to the DR 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\tlogsbackup\, you could create the \\primaryserver\tlogsbackup share of that directory. Create a folder on the DR server called TLogsProd where the prod transaction logs will be copied to.
  3.  Create a domain account for SQL, call it FIMSQLAgent. This is very important, use a domain account for your SQL service and also the SQL agent. Add the SQL account to the FIM Sync Admins group and add it to the share and the NTFS of the TLogsBackup folder of the Prod Server. Add the account also to the TlogsProd folder on the DR server. Change the log on account for the SQL Service and SQL Agent service on the Prod and DR servers to run under the domain account.

Configure Log Shipping

  1. Using SQL Server Management Studio. Start the SQL Mgmt Studio with an account that has SA rights to the Prod and DR servers. One thing I have is that my FIM Sync Admins group has SA rights on the Prod and DR servers, this may not always be the case of some environments. Is it needed? I am not really sure, there are some SPs that will be created by the wizard that need SA rights, I would say definitely run the wizard with an SA level account on both DBs.
  2.  Right click the FIM Sync database and then click Properties.
  3.  Under Select a page, click Transaction Log Shipping.
  4.  Select the Enable this as a primary database in a log shipping configuration check box.
  5.  Under Transaction log backups, click Backup Settings.
  6.  In the Network path to the backup folder box, type the network path to the share you created for the transaction log backup folder.
  7. Leave the default delete files older than 72 hrs, you can select another archive time if you want.
  8.  Click OK.
  9.  Under Secondary server instances and databases, click Add.
  10.  Click Connect and connect to the instance of SQL Server that you want to use as your DR server.
  11.  In the Secondary Database box, choose a database from the list or type the name of the database you want to create.
  12.  On the Initialize Secondary database tab, choose the option that database already initialized. Choose, Yes, generate full backup of the primary database and restore it into the secondary database. This is the best option I have found, it makes it easy. The Log shipping principle is that you start with a full backup and then your transaction logs are generated sequentially, if you miss the sequence, your logs will no longer be applied. Also your prod database should be restored on the DR server with the no-recovery option so that transaction logs can be applied. The wizard does it all for you.
  13. 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 on the DR Server. This folder is often located on the secondary server.
  14.  On the restore transaction log tab select “No recovery” mode.
  15.  Click OK.
  16.  On the Database Properties dialog box, click OK to begin the configuration process.
  17.  The wizard will show and perform the steps
  • Backup the Prod Database
  • Restore the Prod DB to DR.
  • Verify the restore.
  • Create a job on the Prod server to copy the transaction logs to a folder. Scheduled for every 15mins
  • Create a job on the DR server to copy the transaction logs from the Prod server every 15 mins
  • Create a job on the DR server to apply the transaction logs every 15 minutes.

Next i will deal with what will you do to the DB and Log shipping when you move to DR and back to Prod.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s