Microsoft Log Shipping Overview
Another, more direct method of creating a completely redundant database image is to use log shipping. Microsoft certifies log shipping as a method of creating an “almost” hot spare. Some folks even use log shipping as an alternative to data replication. Log shipping used to be referred to as “poor man's replication” when replication was an add-on product. Now that they are both included in the box, neither one is really any more expensive to implement than the other. Actually, replication can be cheaper to implement, as it is available in the Standard Edition, while log shipping officially is only available in the Enterprise Edition. Log shipping is comprised of three primary functions:
Makes an exact image copy of a database on one server (the source server) from a database dump
Creates a copy of that database on one or more other servers from that dump (these are called destinations)
Continuously applies transaction log dumps from that source database to the destination databases (dump, copy, restore sequence)
In other words, log shipping effectively replicates the data of one server (the source) to one or more other servers (the destinations) via transaction log dumps. Destination servers are read only. Figure 6.1 shows a typical log shipping configuration with two destination pairs. A destination pair is any unique source/destination combination. You can have any number of source/destination pairs. This means that you can have from 1 to N replicated images of a database using log shipping. Then, if the source server should fail, you can upgrade one of the destination servers to become the source server.
Figure 6.1. Log shipping with two “read only” destination servers and a separate monitor server.
As you can see in Figure 6.1, log shipping also uses a monitor server to help keep track of the current state of the log shipping. The monitor server is another SQL Server instance. A couple of jobs will be created under the SQL Server Agent on the monitor server. In addition, several tables in the MSDB database are used exclusively for keeping the log shipping information (these tables all begin with “log_shipping_”). The monitor server will keep track of when the last log was shipped from the source database to the destinations, keep track of the delays between the logs that get loaded on the destinations, and provide the answer of whether the delays are being exceeded or not.
Data Latency and Log Shipping
Determining the right delays to specify depends on the data latency you can tolerate and how quickly you need to be notified when log shipping breaks down. Your high availability service level agreement will dictate this to you. If there is a breakdown in log shipping, such as the loads on the destination are not being done or are taking longer than what has been set up, the monitor server will generate alerts. It is a good general practice to isolate the monitor server to a separate server by itself so that this critical monitoring of log shipping is not affected if the source server or any destination servers fail (as also seen in Figure 6.1).
Design NoteYou can set up log shipping to work entirely within a single SQL Server instance if you wish or between SQL Server instances on the same server. In this case, the source, destination, and monitor server could all reside on the same server machine. I'm not sure what purpose this would serve from a high availability or fail-over point of view; however, there is no restriction in this respect. |
The amount of data latency that exists between the source and destination database images is the main determining factor in understanding the state of your recoverability and fail-over capabilities. You will need to set up these data latency (delay) values as part of the log shipping configuration.
However, the gating factors for using log shipping as the method of creating and maintaining a redundant database image are
Data latency—The time that exists between the frequency of the transaction log dumps on the source database and when these dumps get applied to the destination DBs.
Sources and destinations must be the same SQL Server version (and must be Enterprise Editions).
Data is read-only on the destination SQL Server until the log shipping pairing is broken (as it should be to guarantee that the translogs can be applied to the destination SQL Server).
The data latency restrictions might quickly disqualify log shipping as a fool-proof high availability solution though. However, log shipping might be adequate for certain HA situations. If a failure ever occurs on the primary SQL Server, a destination SQL Server that was created and maintained via log shipping can be swapped into use at a moment's notice. It would contain exactly what was on the source SQL Server (right down to every user id, table, index, and file allocation map, except for any changes to the source database that occurred after the last log dump that was applied). This directly achieves a level of high availability. It is still not quite completely transparent, though, since the SQL Server instance names are different and the end-user may be required to log in again to the new SQL Server instance. But, unavailability is usually minimal.
Design and Administration Implications of Log Shipping
From a design and administration point of view, you need to consider some important aspects associated with log shipping:
User IDs and their associated permissions will be copied as part of log shipping. They will be the same at all servers, which might or might not be what you want. If you are going to create the database on the destination servers first you can use the special Data Transformation Services (DTS) task to transfer logins from the source server to the destination server.
Log shipping has no filtering. You cannot vertically or horizontally limit the data that will be log shipped.
Log shipping has no data transformation. No summarizations, format changes, or things like this are possible as part of the log shipping mechanism.
Data latency will exist. The amount of latency is dependent upon the frequency of transaction log dumps being performed at the source and when they can be applied to the destination copies.
Sources and destinations must be the same SQL Server version.
All tables, views, stored procedures, functions, and so on will be copied.
Indexes cannot be tuned in the copies to support the read-only reporting requirements.
Data is read-only (until log shipping is turned off).
If these restrictions are not going to cause you any trouble and your high availability requirements dictate a log shipping solution, then you can proceed with confidence in leveraging this Microsoft capability.