High Availability-database images

Source: Internet
Author: User
Tags failover sql server runtime

SQL Server 2005 ranks SQL server in the ranks of enterprise-level databases. In terms of high data availability, SQL Server 2005 provides users with data mirroring, replication, failover clustering, and log transmission functions. The following describes the SQL Server 2005 image function. Database images can only be used in basic versions or later versions.
What is a database image?

Robidoux: database images are used to move Database Transaction processing from one SQL Server database to another SQL Server database in different SQL server environments. An image copy is a backup copy and cannot be accessed directly. It is used only when an error is restored.

Because it is a new feature introduced in SQL Server 2005, this feature can only be used in this version (SQL Server 2005 SP1) software. To some extent, it is a mixture of replication and log transmission: all your things are moved at the transaction level (replication) (log transmission) copying an image to your database also reduces the problems you may face when implementing log transmission or replication.

Replication involves a large number of mobile parts, and if your database plan changes frequently, it is also difficult to ensure that your replication runs smoothly. Log transmission also requires many operations to ensure synchronization, such as creating backups, copying backups, and restoring backups. If a process crashes, the entire process will crash.

How does a database image work?

Robidoux: the minimum requirements for database mirroring include two different SQL Server Runtime environments. The master server is called "Basic", and the second server is called "image ". The basic database is the database you actually use, and the image database is the backup copy of your database. When transactions are written to your basic server, they are also transmitted to and written to your image database.

In addition to basic and image, you can also introduce an optional component named "witness ". The witness database is the third SQL Server 2005 running instance. It is used for internal communication between the image and the basic when an error is recovered. This option is only used when you want to implement automatic error recovery. It provides a 2-to-1 voting capability. When one of my components cannot be reached, it requires error recovery. The witness server must be used only when you want to enable automatic error recovery.

What is the implementation method?
Database images provide three implementation methods. You can choose the method you want to use to recover errors.

High Availability: this operation mode allows you to synchronize transaction writes on two servers and supports automatic error recovery. To use this option, you must also use a witness server.

High Protection: This option allows you to synchronize transaction writes on two servers, but error recovery is manual. Because automatic error recovery is not part of this option, it does not use the witness server.

High Performance: This option does not care whether the write operations on the two servers are synchronized, so the performance is improved. When using this option, you can only assume that everything on the backup storage is successfully completed. This option only allows manual error recovery, so it does not use the witness server.

Always remember that automatically restoring your data to the second copy is the real benefit of the database image. Therefore, most implementations may use high availability methods. Other options still provide the built-in error recovery process, provided that you are extremely interested in automatic error recovery when an error occurs.

I. Introduction to images

Database Mirroring is a high-availability software solution that provides client failover within 10 seconds. Each database image configuration includes a primary server (including the primary database), an image server (including the image database), and a witness server. The witness server is optional. The master server and backup server must be independent server instances. The role of the master server is relative to that of the backup server. You can set the master server as the backup server automatically or manually, and the backup server as the master server. Unlike the primary server and backup server, the witness server cannot be used for databases. The witness server monitors the master server and the backup server, and ensures that only one of the two failover servers acts as the master server within a given period of time, thus supporting automatic failover. If a witness server exists, the session in the same step runs in "high availability mode". If the main server fails, automatic failover can be implemented. If the witness server does not exist, the synchronization session runs in "high-level protection mode". If a fault occurs, manual failover is required and data may be lost.

Figure 1: Two server Images

Figure 2: Two server images and one witness Server

After the database preparation is complete and the endpoint is created, you can enable the database image. After the image is started, each partner will start to maintain the information about its database and the status of another partner and witness server in the database. These status information allows the server instance to maintain the current relationship called "database image session. During a database image session, server instances monitor each other by periodically exchanging Ping messages.

After an image session is started, the backup storage identifies the log serial number (LSN) of the latest transaction completed on the backup storage, and requires the master server to provide transaction logs for all subsequent transactions, the main server generates a transaction log of the current activity to the backup server. the backup server immediately imports the imported log image to the disk. The master server continues to allow the client to connect to the master database. Each time the client updates the master database, the master server sends the transaction logs to the backup server when writing the logs, the backup storage will mirror it to the disk. At the same time, the backup storage starts from the earliest transaction log and applies the transaction to the backup storage to synchronize the master database and backup storage.

The following configuration instances refer to the following resources. I have not tested them yet.

Reference resources:
Http://tech.it168.com/db/s/2007-05-23/200705232029125.shtml
Http://tech.it168.com/db/s/2007-04-24/200704240837593.shtml
Http://www.2mysite.net/blogview.asp? Logid = 1140

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2007/07/30/1716156.aspx

SQL Server 2005 ranks SQL server in the ranks of enterprise-level databases. In terms of high data availability, SQL Server 2005 provides users with data mirroring, replication, failover clustering, and log transmission functions. The following describes the SQL Server 2005 image function. Database images can only be used in basic versions or later versions.
What is a database image?

Robidoux: database images are used to move Database Transaction processing from one SQL Server database to another SQL Server database in different SQL server environments. An image copy is a backup copy and cannot be accessed directly. It is used only when an error is restored.

Because it is a new feature introduced in SQL Server 2005, this feature can only be used in this version (SQL Server 2005 SP1) software. To some extent, it is a mixture of replication and log transmission: all your things are moved at the transaction level (replication) (log transmission) copying an image to your database also reduces the problems you may face when implementing log transmission or replication.

Replication involves a large number of mobile parts, and if your database plan changes frequently, it is also difficult to ensure that your replication runs smoothly. Log transmission also requires many operations to ensure synchronization, such as creating backups, copying backups, and restoring backups. If a process crashes, the entire process will crash.

How does a database image work?

Robidoux: the minimum requirements for database mirroring include two different SQL Server Runtime environments. The master server is called "Basic", and the second server is called "image ". The basic database is the database you actually use, and the image database is the backup copy of your database. When transactions are written to your basic server, they are also transmitted to and written to your image database.

In addition to basic and image, you can also introduce an optional component named "witness ". The witness database is the third SQL Server 2005 running instance. It is used for internal communication between the image and the basic when an error is recovered. This option is only used when you want to implement automatic error recovery. It provides a 2-to-1 voting capability. When one of my components cannot be reached, it requires error recovery. The witness server must be used only when you want to enable automatic error recovery.

What is the implementation method?
Database images provide three implementation methods. You can choose the method you want to use to recover errors.

High Availability: this operation mode allows you to synchronize transaction writes on two servers and supports automatic error recovery. To use this option, you must also use a witness server.

High Protection: This option allows you to synchronize transaction writes on two servers, but error recovery is manual. Because automatic error recovery is not part of this option, it does not use the witness server.

High Performance: This option does not care whether the write operations on the two servers are synchronized, so the performance is improved. When using this option, you can only assume that everything on the backup storage is successfully completed. This option only allows manual error recovery, so it does not use the witness server.

Always remember that automatically restoring your data to the second copy is the real benefit of the database image. Therefore, most implementations may use high availability methods. Other options still provide the built-in error recovery process, provided that you are extremely interested in automatic error recovery when an error occurs.

I. Introduction to images

Database Mirroring is a high-availability software solution that provides client failover within 10 seconds. Each database image configuration includes a primary server (including the primary database), an image server (including the image database), and a witness server. The witness server is optional. The master server and backup server must be independent server instances. The role of the master server is relative to that of the backup server. You can set the master server as the backup server automatically or manually, and the backup server as the master server. Unlike the primary server and backup server, the witness server cannot be used for databases. The witness server monitors the master server and the backup server, and ensures that only one of the two failover servers acts as the master server within a given period of time, thus supporting automatic failover. If a witness server exists, the session in the same step runs in "high availability mode". If the main server fails, automatic failover can be implemented. If the witness server does not exist, the synchronization session runs in "high-level protection mode". If a fault occurs, manual failover is required and data may be lost.

Figure 1: Two server Images

Figure 2: Two server images and one witness Server

After the database preparation is complete and the endpoint is created, you can enable the database image. After the image is started, each partner will start to maintain the information about its database and the status of another partner and witness server in the database. These status information allows the server instance to maintain the current relationship called "database image session. During a database image session, server instances monitor each other by periodically exchanging Ping messages.

After an image session is started, the backup storage identifies the log serial number (LSN) of the latest transaction completed on the backup storage, and requires the master server to provide transaction logs for all subsequent transactions, the main server generates a transaction log of the current activity to the backup server. the backup server immediately imports the imported log image to the disk. The master server continues to allow the client to connect to the master database. Each time the client updates the master database, the master server sends the transaction logs to the backup server when writing the logs, the backup storage will mirror it to the disk. At the same time, the backup storage starts from the earliest transaction log and applies the transaction to the backup storage to synchronize the master database and backup storage.

The following configuration instances refer to the following resources. I have not tested them yet.

Reference resources:
Http://tech.it168.com/db/s/2007-05-23/200705232029125.shtml
Http://tech.it168.com/db/s/2007-04-24/200704240837593.shtml
Http://www.2mysite.net/blogview.asp? Logid = 1140

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2007/07/30/1716156.aspx

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.