Log Shipping

Log shipping is a method that is included with SQL Server Enterprise Edition to take a transaction log backup from a source server and apply it to one or multiple remote SQL Servers. It provides an easy, cost-effective way to create a degree of high availability in your SQL Server environment.

Note 

Before SQL Server 2000, you could code your own log shipping solution by using tools provided in the BackOffice 4.5 Resource Kit. Log shipping in this release can be done the same way against Standard Edition of SQL Server, but the method provided in Enterprise Edition makes this process much easier.

There are several components of log shipping. The primary server is the active database server that the production application is pointed to. The secondary server (or servers) holds the destination read-only database where the transaction logs will be copied and restored. Finally, a monitor server monitors both the primary and secondary servers. This server will display the status of the log shipping processes at any time. Another term you will need to be familiar with is role change. This is when the primary server is demoted to a secondary server and secondary is promoted to primary.

Note 

Because log shipping is done through transaction log backups, source databases must be either in Full or Bulk-Logged recovery model.

The Log Shipping Process

In log shipping, you decide how often you want the servers to synchronize. Log shipping generally uses the following steps to synchronize the database automatically when you create the log shipping plan (shown in Figure 10-1):

click to expand
Figure 10-1: Log shipping basic architecture

  1. Take a full database backup of your source database.

  2. Copy and restore the database onto any secondary database servers.

  3. Schedule point-in-time transaction log backups on the primary server.

  4. Schedule the copying of the transaction log backups to the secondary servers.

  5. Apply the transaction logs to the secondary servers.

  6. If a disaster occurs, a role change is necessary to promote one of the warm standby secondary servers to a primary server.

Caution 

The log shipping strategy may add extra stress on your network. Only implement this solution if your network has enough bandwidth to support copying large files over it in a timely basis.

Location, Location, Location

Where you place your log shipping pair is essential to the success of the solution. Log shipping was designed to help with redundancy in geographically distant locations. If your servers are in the same server room and a disaster happens where you lose the data center, your redundancy solution is worthless. At a minimum, ensure that your primary and secondary servers are on a different power grid and that the connectivity between the two locations is strong. By strong connectivity, I mean that you have a reliable, high-speed connection between the servers. Don't forget, in the event of a failure your applications and users will need to be able to communicate to the secondary server. Ensure that all the firewall rules for your system reflect that type of flexibility.

Tip 

Log shipping does not employ any type of compression during transmission of the files. Because of this, it can increase the demand on your network and increase latency on your SQL Server and on the rest of your network. You may want to consider adding a high-speed network card for log shipping communication or configure a private LAN between the log shipping pair servers to reduce the overall demand on the larger shared network.

Log Shipping Behind the Scenes

The msdb database is essential to driving the log shipping. It contains a number of stored procedures and tables necessary to the log shipping processes. In Table 10-3, you can see a list of stored procedures that are involved in orchestrating log shipping. These can also be accessed if you're developing your own custom solution.

Table 10-3: Stored Procedures that Help Orchestrate Log Shipping

sp_add_log_shipping_database
sp_add_log_shipping_plan
sp_add_log_shipping_plan_database
sp_add_log_shipping_primary
sp_add_log_shipping_secondary
sp_change_monitor_role
sp_change_primary_role
sp_change_secondary_role
sp_create_log_shipping_monitor_account
sp_define_log_shipping_monitor
sp_delete_log_shipping_database

sp_delete_log_shipping_monitor_info
sp_delete_log_shipping_plan
sp_delete_log_shipping_plan_database
sp_delete_log_shipping_primary
sp_delete_log_shipping_secondary
sp_get_log_shipping_monitor_info
sp_remove_log_shipping_monitor
sp_resolve_logins
sp_update_log_shipping_monitor_info
sp_update_log_shipping_plan
sp_update_log_shipping_plan_database

There are also some relevant tables that you may need to be aware of in case you're configuring log shipping between SQL Server 7.0 and 2000. When SQL Server 7.0 is in the topology, you may have to query some of these tables manually.

  • log_shipping_database Provides a list of databases that are configured to be shipped. It also contains a list of the maintenance plans that are associated with the shipped databases. This table is only utilized by the primary server.

  • log_shipping_monitor Contains the connectivity information for the monitoring server. This table is used by both the primary and secondary servers.

  • log_shipping_plan_databases This table contains a list of properties about the databases being shipped to and the source. This table is populated by the warm standby server.

  • log_shipping_plan_history This table contains the history of each plan and whether the attempt to synchronize the servers was successful. This table is only populated by the warm standby server.

  • log_shipping_plans This table contains information about each log shipping maintenance plan. This includes columns such as server name, path, and any jobs associated with the log shipping maintenance plan. This table is only used by the warm standby server.

  • log_shipping_primaries This table contains information about the primary server such as server and path names among other items. It also contains the last transaction log backup filename. This is populated by the primary server.

  • log_shipping_secondaries This table contains information about the secondary servers participating in log shipping such as server and path names. The most important information in this table is when the last transaction log backup file was last copied and loaded. This table is populated by the primary server.

  • sysdbmaintplans This table contains a list of maintenance plan information that the log shipping processes utilize.

  • sysjobs This table contains a list of associated jobs utilized by log shipping.

  • syslogins This table contains the logins used to access each of the servers participating in log shipping. Unlike the other tables, this system table is in the master database. The other tables are in the msdb database.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net