Scalability and High Availability of Microsoft SQL Server 2000 (Part 4)

While clustering (covered in the first two parts of this series) provides high availability and distributed partitioned views (presented in the part three) helps increasing scalability, log shipping, discussed in this article, combines both types of functionality. As far as high availabilty is concerned, log shipping is inferior to clustering, since it does not guarantee up-to-point recoverability and automatic failover (failover has to be performed manually and requires several steps, including either changing the secondary server name to the name of the primary or repointing client applications to the secondary server, as well as running several stored procedures to switch roles between primary and secondary servers). In spite of these drawbacks, log shipping is frequently used to maintain warm standby server, which content is kept synchronized with the content of a primary server. The main advantage of log shipping over clustering is its hardware independence. Unlike clusters, which require fairly expensive and HCL-compliant hardware, servers configured with log shipping can run on dissimilar hardware and be located in separate geographical locations (while the distance between clustered servers is limited to the length of Fibre or SCSI channel, connecting them to the shared storage device). Log shipping also can be used to resolve database scalability issues by offloading read only activities (such as reporting) to a separate server. Log shipping is in many aspects similar to database replication, but is has considerably lesser impact on SQL server performance and is much easier to set up and maintain.

The basic concept used in log shipping is creating a replica (or replicas) of a source database (residing on a primary server) on a separate server (or servers), which typically are referred to as “secondary server(s).” The synchronization between the source and its replica(s) is automatic (once configured) and handled by copying the transaction log from the source and restoring it on the target(s). Besides the primary and secondary servers (containing, respectively, the original and replica databases), log shipping process utilizes a monitor server, responsible for overseeing the process and providing notifications in case of problems. Even though the monitor server functionality can be performed on the target or source server, such configuration is not recommended, from both a performance and availability perspective.

Servers participating in the log shipping need to be running SQL Server 2000 Enterprise (or Developer) Edition. Even though it is possible to configure SQL Server 7.0 as a target server (with SP2 or later), this is not intended to be a permanent solution (since the target database has to be placed in the “pending upgrade” mode, which prevents index creation and statistics updates). Permission to set up log shipping are granted only to members of sysadmin role, so make sure that the account you used to log on has the appropriate rights on the source, destination, and monitoring server. In addition, the account used by SQLServer Agent service needs to have permissions sufficient to access source and target servers. Note that, for obvious reasons, it is not possible to set up log shipping for databases that are using simple recovery model. Remember that prior to configuring log shipping, you should set up a network share on the source server, accessible by the SQLServer Agent account from source, monitoring, and destination server. If you plan to use the secondary database as primary (in case of the primary’s failure), you will need a share on the destination server, accessible by SQL Server Agent accounts. In this case, it is also important to add relevant logins to the target SQL server (this can be done using DTS Transfer Logins task). Finally, register all of the servers participating in the Log shipping in the SQL Server Enterprise Manager.

Log shipping is set up as part of Database Maintenance Plan Wizard available from SQL Server Enterprise Manager. Make sure that the focus is on the server containing the source database, open Management folder, and right click on Database Management Plans. From the context sensitive menu, select the New Management Plan… option (alternatively, you can edit an existing maintenance plan). Selecting New Management Plan will launch the Database Maintenance Plan Wizard. After clicking on the Next button on the first informative page of the wizard, you will be presented with the next page, from which you need to select the source database (you are limited to selecting only a single database at a time) and the checkbox below the database list labeled “Ship the transaction logs to other SQL Servers (log shipping).” The next few pages of the wizard will prompt you for Update Data Optimization Information, Database Integrity Check, Specify Database and Transaction Log Backup Plan and Disk Directory. You should configure these accordingly to your database maintenance policies (but keep in mind that they are independent from Log Shipping settings). On the Specify the Transaction Log Share page, you need to specify the share that will host the transaction log backups for the database you selected. Using the next page – Specify the Log Shipping Destination – you add one or more secondary servers. For each new server, you will need to specify:

  • target server name
  • transaction log destination directory on the target server (where the logs will be shipped)
  • name of the destination database, which can be one of the following:
    • a non-existing database – in this case, the wizard will perform full backup of the source database, restore it to the target server, and initialize it. You will also need to specify the location of the data and log files.
    • an existing database – select this option if you performed backup, restore, and initialization yourself, prior to launching the wizard (in this case you will need to only specify the database name). This might be a preferred approach with large databases.
  • database load state
    • no recovery mode – this prevents all types of user access to the database
    • standby mode – this allows read-only access between periods when transaction logs are restored. You also have the option of forcing termination of user connections to the database when the restore is scheduled (this is done by checking the checkbox “Terminate users in database”).
  • If you intend to use the destination database as the primary after the primary server failure, you need to select the checkbox “Allow database to assume primary role.” In this case, you will also need to specify the share in which transaction log backups from the secondary server will be stored. As indicated before, keep in mind, that failover is manual and will require either renaming the secondary server to the primary’s name or altering client applications so they point to the secondary server.

Once you’ve added all log shipping destinations and clicked on the Next button, you will have an option of performing a full database backup or using the most recent existing backup file. Note that this backup file has to reside on a disk, not a tape. The next page will prompt you for the log shipping schedule, including copy/load frequency, load delay, and file retention period. The values you choose should reflect criticality of the data and network and server load. The Log Shipping Threshold page allows you to specify a backup alert threshold and out of synch alert threshold. You will need to select the Log Shipping Monitor Server Information from the next page. As mentioned before, this should be separate from source and destination servers. The wizard will finally reach the last page, after the next two pages that deal with reporting, history of maintenance plans, and they are not log shipping specific, and initiate the log shipping process according to your choices. It will also enable Log Shipping Monitor on the monitor server and create a node for it in the Management folder of SQL Server Enterprise Manager. Using Log Shipping Monitor, you can track the progress of log shipping process, including individual backup, copy, and restore steps.

Removing log shipping is as simple as clicking on the Remove Log Shipping button from the Log Shipping tab of the Database maintenance Plan Properties dialog box (accessible from Database Management node of Management folder in SQL Server Enterprise Manager).

This concludes our series of articles on SQL Server 2000 scalability and high availability. As we explained, there are several options that are available to you and you should base your choice on requirements and available resources. In the next series of articles, I will cover topic that is becoming increasingly important in Microsoft technologies – XML related capabilities in SQL Server 2000.


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles