Creating a Custom Coded Log Shipping Solution

If you have determined that coding your own log shipping solution is the best way to implement log shipping in your environment, all the considerations apply as they do to the built-in feature, but you need to create everything manually that is already included with SQL Server 2000 Enterprise Edition. This includes the following:

• A process to restore the point-in-time full backup to the secondary server.

• A process to back up, copy, and restore the transaction logs. Sometimes the easiest way to create transaction log backups is to use the Database Maintenance Plan Wizard because it creates unique file names for the transaction log backups without requiring you to code your own logic.

• A process to change the roles of the servers. This will be custom for each solution.

• A process to transfer users and logins. If your version of SQL Server has the stored procedure sp_resolve_logins in the master database, you should be able to use the same process as detailed for the built-in feature (using the DTS transfer logins task and bcping out the syslogins table) in the role change to synchronize logins. Otherwise, follow the information found in the section Transferring Logins, Users, and Other Objects to the Standby in Chapter 14.

• Monitoring functionality (if it is desired to see the status other than in, say, SQL Server Agent jobs).

• Testing procedures to verify that everything is working properly.

• Custom alerts and notifications.

  On the CD

  For an example of a custom log shipping solution you can use or extend that also includes compression, see the file Custom_Log_Shipping.zip, which contains all the relevant Transact - SQL scripts and documentation. The scripts do not configure any additional functionality such as dealing with logins. The scripts do cover the backing up, copying, and applying of the transaction logs, as well as the monitoring of the process.