Many times, we feel the need to implement a solution to create a synchronized copy for our Production database. This may be required for various purposes like implementing a warm standby server, synchronizing test databases with production databases or simply to verify the validity of the production database backups. Log shipping in SQL Server 2000 and 2005 can be used for such tasks. While Log Shipping is a good solution, it often requires experienced DBAs to setup and maintain and may not be suitable for small SQL Server setups. This article presents a way to implement a simple solution to synchronize database with another one using Database Maintenance Plans and T-SQL - sort of a custom log shipping solution.

For the purpose of this article, we use SQL Server 2000. In the example, the primary database is called EPH located on server SQLSVR1 and the database to be kept synchronized is called EPH_Standby on server SQLSVR2. The EPH database has one data file - EPH_Data.mdf and one transaction log file - EPH_Log.ldf. Following steps are used to implement the custom log shipping solution:

Step 1: Database Maintenance Plans in SQL Server 2000 and Database Maintenance Tasks in SQL Server 2005 SSIS offer a convenient way to set up routine tasks like backups. We create a Database Maintenance Plan for the EPH database on SQLSVR1. This plan will take a full backup every hour and a transaction log backup every 15 minutes. We leave the default options unchanged for file extensions in the plan - BAK for full backups and TRN for transaction log backups. At the end of Step 1, we will have two SQL Server Agent jobs - one to take full database backups and another to take Transaction log backups. The schedules may have to be tweaked a bit to ensure that the full db and transaction log backups don't clash with each other every hour. These files can then be applied in the proper sequence to create a synchronized copy of the database.

Step 2: Next we create a new database called dbDataMirror and its tables on SQLSVR2. Run the script DataMirror_Objects.sql (in the Resources section below). This script will create two tables - DataMirror and Parameter_Lookup and will also add the records containing the parameters used by the main script - DataMirror.sql. [DataMirror] is the table used to track the progress of applying the backup files. Parameter_Lookup is a generic lookup table used by the mirroring script.

Following are the parameters used by the synchronizing process. These can be customized as per requirements. The PL_Type field contains a constant value 'DataMirror' to identify the set of parameters required by the script. The PL_Name field contains the below parameters - each parameter as a record in the table:

The PL_Value field contains the values of each of these above parameters.

Step 3: Then we create a scheduled SQL Server agent job on SQLSVR2 called EPH_Standby_DataMirror. This can be done manually or using the script EPH_Standby_DataMirror.sql (in the Resources section below). The job invokes the osql command-line utility to execute a T-SQL batch file called DataMirror.sql.

The script DataMirror.sql (in the Resources section below) is the main script, which performs the syncronization by restoring the backups in the correct sequence. It performs the following tasks:

1. Checks if new files have arrived in the backup directory by comparing the files with those present in the DataMirror table. This is done by executing a 'dir' command on the backup directory through the xp_cmdshell extended stored procedure:

Select @strCmd = [PL_Value] From [Parameter_Lookup] Where [PL_Type] = 'DataMirror' And [PL_Name] = 'Backup_Directory'
Set @strCmd = 'dir /b /s ' + @strCmd

The listing from the above command is taken into a temporary table. The DataBackup_FilePattern and LogBackup_FilePattern as supplied in the Parameter_Values table are trimmed and only the remaining part - the timestamp onwards is left. This enables the script to determine the correct sequence to apply the logs.

2. Verify the validity of each backup file with RESTORE VERIFYONLY statement.

3. If the backup file is valid, the script applies the full or transaction log backups in the proper order. With SQL Server 2000 Database Maintenance Plan, full DB backups are created with the file naming convention: <DB Name>_db_YYYYMMDDHHMM.bak. Transaction log backups are created with the naming convention: <DB Name>_tlog_YYYYMMDDHHMM.trn. The timestamp in the filename is used to arrive at the correct sequence of applying the backups. The RESTORE contains WITH MOVE clauses in case the database being synchronized does not have the same logical and physical names as the primary database. As we have seen, these can be customized using the Parameter_Lookup table.

The restores are done with the NORECOVERY clause so that subsequent logs can be applied automatically in the next runs. If any errors are encountered, the script exits with failure, causing the job to fail. In this way, the EPH_Standby database is synchronized by applying backups from the EPH database - similar to what is achieved using Log Shipping.

Conclusion

Log shipping is a great mechanism to implement a warm standby server. But in SQL Server 2000, log shipping is supported only in the Enterprise Edition. Organizations with other editions, like Standard Edition, can take advantage of the solution presented here to achieve similar results. Similarily, for simple tasks like maintaining a test or development database synchronized with data from a production database, a full scale log shipping solution may not be really required. In such situations, the simple solution presented in the article can come in handy.