Use SQL Server log transfer to achieve high availability of databases

"Guide" This article focuses on SQL Server 2000, which describes how to use SQL Server log transfer to implement high availability of a database.

Clustering is an effective solution for achieving high availability, sometimes counterproductive. And it's very expensive. As a result, database administrators can use log transfers instead of clusters to provide high availability.

Log transfer is a process that enables the transaction log files in a database to be transferred to the Backed-up database in turn, creating an "almost" hot backup of the database. The log transfer feature is set up and processed in SQL Server 2000 's database engine. So it automatically completes the process of restoring to the backup server without requiring the database administrator to manually operate. Only if your product server operation fails, you need to manually complete the recovery process to the backup server. (Note: Although there are log transfer features in SQL Server 7.0 and 2005, this article is primarily for SQL Server 2000.) )

Why do I use log transfers?

Log transfer is a measure of high availability and is very effective. Also as a measure of high availability, the biggest advantage of log transfer relative to the cluster is that it is much cheaper. This is because there are hardware requirements for using cluster features, and log transfers are not required.

The log transfer is between the database and the database, not the server and the server, so it is possible to store the backup database on a server that you have used for other purposes. However, if the transfer fails, there may be a problem, at which point you can swap back up the database and this option is available.

Log transfers are relatively easy to install. SQL Server provides a very complete wizard to help you install this process.

Log forwarding allows you to keep redundant data distributed across geographically diverse locations, which is difficult to do with SQL Server cluster capabilities. This is a remarkable feature, because when your data center is in disaster, you can still recover it from your backup server. And in the same data center, if you're using clustering, you get into trouble.

Another advantage of log transfer is that you can use the backup database as a reporting database, which is a good choice for many companies. But if you decide to use this backup database for reporting purposes, you must be aware of its limitations. When using logs from the original database, SQL Server requires a unique channel to be specified, so the report cannot be performed concurrently when the log file is being applied.

Related factors to consider when using log transfer

When using log transfer as a high-availability scenario, we must consider the following factors. Because there is a latency from the original database to the backup database, it is not necessarily a viable solution for your company to achieve high availability. Latency is set by the database administrator, and time is shortened as needed, but can never be avoided.

The recovery feature is not set in the log transfer, which means that the logs are temporarily unavailable when the logs are transferred to the backup server. Therefore, the database administrator must complete a series of actions before putting the backup database on the Web, including the following:

Store the backup tags stored in the original database on the backup data server. Once all the tags have been stored, the database must be restored and placed on the web.

Once all the databases are on the Web, all applications that need access to the database need to change their links. If you can't point the application as quickly as possible to the database you just recovered, you'll have nothing to do.

An instance of SQL Server can be used to monitor log transfers. This instance can be in the original database, back up the database, or in a separate database. Any version of SQL Server can be used for SQL Server monitoring.

Note: Database logins must be performed simultaneously between the original database and the backup database.