How to quickly master "log transfer" in SQL Server"

How to quickly master "log transfer" in SQL Server"

A cluster is an effective solution to achieve high availability. Sometimes it is counterproductive. Moreover, it is very expensive. Therefore, database administrators can use Log transfer instead of clusters to provide high availability.

Log transfer is a process in which transaction log files in a database can be transferred to the backup database in sequence, and a "almost" Hot Backup is created for the database. The log transfer function is set in the database engine of SQL Server 2000 and processed in it. Therefore, it automatically restores the process to the backup server without manual operations by the database administrator. Only when the operation on your product server fails, you need to manually restore the process to the backup server. (Note: Although both SQL Server 7.0 and 2005 have the log transfer function, this article mainly targets SQL Server 2000 .)

◆ Why use Log transfer?

Log transfer is an effective solution to high availability. As a high-availability solution, log transfer is much cheaper than a cluster. This is because there are hardware requirements for using the cluster function, and log transfer is not required.

Logs are transferred between the database and the database rather than between the server and the server. Therefore, it is possible to store the backup database on the server that you have used for other purposes. However, if the transfer fails, there may be problems. In this case, you can use the backup database. This option is available.

Log transfer is relatively easy to install. SQL Server provides a complete Wizard to help you install the process.

Log transfer allows you to store redundant data distributed in different geographic locations, which is hard to achieve by the SQL Server cluster function. This feature is outstanding because, when your data center is under disaster, you can still recover it from the backup server. In the same data center, if you use the cluster function, you will be in trouble.

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

◆ Factors to consider when using log Transfer

When using log transfer as a high-availability solution, we must consider the following factors. Since there is an incubation period from the original database to the backup database, it is not necessarily a feasible solution for your company to achieve high availability. The latency is set by the database administrator. The latency is also shortened as needed, but it cannot be avoided.

The restoration function is not set in log transfer, which means that these logs are temporarily unavailable when the logs are transferred to the backup server. Therefore, the database administrator must complete a series of operations before putting the backup database online. These steps include:

Store the backup tags in the original database that have been stored on the backup data server. Once all the tags are stored, the database must be restored and put online.

Once all databases are stored online, all applications that need to access the database need to change their own links. If you cannot direct your application to the database you just recovered as soon as possible, you will be able to discard all your efforts.

An SQL Server instance can be used to monitor log transfer. This instance can be in the original database, backup database, or individual database. Any version of SQL Server can be used for SQL Server monitoring.

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