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
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
|
It is possible to manually configure log shipping from SQL Server 7.0 to SQL Server 2000. The best use of this is to facilitate an upgrade from SQL Server 7.0 to SQL Server 2000 where you need to minimize the amount of downtime in the server switch. There are some constraints for doing this, though, namely the following:
SQL Server 7.0 must be configured with at least SQL Server 7.0 Service Pack 2.
The pending upgrade option must be set on SQL Server 7.0. This is a database-level option that will only affect the database that it is configured on. Setting pending upgrade means that indexes cannot be created and statistics cannot be generated. Therefore, you should not allow log shipping to occur for a lengthy period of time, as it will affect performance of the SQL Server 7.0 database.
Because recovery models are new to SQL Server 2000, truncate log on checkpoint and select into/bulk copy cannot be set as options on the SQL Server 7.0 databases.
When restoring the database under SQL Server 2000, you must use NORECOVERY only. STANDBY is not supported when an upgrade is required.
Replication cannot be configured between the server running SQL Server 7.0 and the SQL Server 2000 instance if log shipping is going to be used. If you are currently replicating using these servers, you have to script your configuration and unconfigure replication.
As with custom log shipping, there is no monitoring functionality for log shipping between SQL Server 7.0 and SQL Server 2000. You have to code your own.
| Note |
Transaction logs for SQL Server 2000 are not backward-
compatible. Therefore, it is not possible to apply transaction logs
(or full
|
The following steps show how to configure log shipping between SQL Server 7.0 Service Pack 2 or later and SQL Server 2000.
Install and configure an instance of SQL Server 2000.
Perform full backups for all databases on SQL Server 7.0.
Restore the database that will be log shipped on your SQL Server 2000 instance using NORECOVERY. Here is example syntax:
RESTORE DATABASE mydb FROM DISK = ' C:\mydbbackup.bak ' WITH NORECOVERY
If requiring a new location:
RESTORE DATABASE mydb
FROM DISK = ' C:\mydbbackup.bak ' WITH NORECOVERY MOVE ' data file ' TO ' x:\newlocation\dbdatafile.mdf ', MOVE ' log file ' TO ' x:\newlocation\dblogfile.ldf '
Make sure that truncate log on checkpoint and select into/bulk copy are not selected as options for the database being log shipped from SQL Server 7.0.
Create a location for the transaction logs to be stored.
Execute the following command using
Using the Database Maintenance Plan Wizard, back up your transaction logs on a regularly scheduled basis. Use the directory created.
| Warning |
If this is an actual SQL Server 7.0 to SQL Server 2000 upgrade and not just a way of creating a test database or something similar, stop all traffic and users from accessing the database at this point to ensure that if you have a problem, the database is in the state it was prior to the switch to SQL Server 2000. You will then have no data loss should you need to go back to SQL Server 7.0. |
Manually apply each transaction log generated. The following is example syntax:
RESTORE LOG mydb FROM mydb_log1 WITH NORECOVERY
When it is time to restore the final transaction log, the syntax is slightly different. You now bring the database online and make it available for use. Here is sample syntax:
RESTORE LOG mydb FROM mydb_finallog WITH RECOVERY
Ensure that all users, objects, and other items not brought over as part of the transaction logs exist under SQL Server 2000.
If necessary, redirect any applications to the new SQL Server
2000 database. Test all applications against the new database on
SQL Server 2000 to ensure that everything functions as it did under
SQL Server 7.0. If this is an upgrade, do not allow end users to
access the new server if it has not been
If you need to use the SQL Server 7.0 installation again, disable the pending upgrade option with the following Transact-SQL statement: sp_dboption 'database name', 'pending upgrade', 'FALSE'