SQL Server
Image Service OverviewAuthor: Zheng Zuo Date: 2007-12-30
1. OverviewWhen implementing the SQL Server 2005 MSC cluster, I spent some time learning the SQL Server 2005 image service technology. Database Mirroring is a new feature of SQL Server 2005. It allows you to mirror the database content in one SQL Server to another. In the case of an error, restore errors by using an image database. After SQL Server 2005 SP1 is launched, Microsoft provides full technical support for it. The image service technology can be used securely in the production environment. This article aims to provide the system architecture personnel who need to develop or make decisions on the SQL Server High Availability implementation scheme by understanding the SQL Server database image service between the SQL server cluster and the SQL Server database image. A reference.
2. database imageDatabase Mirroring is a software solution for improving database availability. The image is based on the database level and is only applicable to databases that use the full recovery mode. The simple recovery mode and the large-capacity log recovery mode do not support database images. Database images are implemented by moving database transactions from one SQL Server database to another SQL Server database in different SQL server environments. An image copy is a backup copy. Generally, it cannot be accessed directly. It can be restored only when an error occurs on the master server. The backup server provides services on the master server. To access the image database, read-only access can be made through snapshots.
2. 1. Features of the Image ServiceCompared with the hardware requirements of the SQL Server's MSC cluster, the SQL Server image service is a software-based high availability solution. Unlike the SQL Server MSC cluster, the SQL Server Image Service has the following features: 1. software-based high availability solutions, low hardware costs. 2. Fast failover recovery, usually within 10 seconds. 3. Failover for a single database based on database-level implementation.
2. Server role in the image1. Principal server ). The host database accepts user connection and transaction processing requests. 2. Mirror Server ). Carries the image database and acts as a hot backup of the main database. 3. Witness server ). Monitors server status and connectivity for automatic failover.
2. 3. Image session cycle1. Session Initialization. The transaction log of the image request, which is synchronized with the master server. 2. Session process. The main server transmits the log records to the backup storage. Each role monitors the session Status of each other. 3. Terminate the session. In case of failover, the Administrator terminates the database image. 4. failover. In a high-availability database image model, when the primary database is unavailable, automatic failover is triggered to restore the service within a short period of time (<10sec ).
3. database image Operation ModeDatabase images can be operated in three modes: high-availability mode, high-level protection mode, and high-performance mode. The features of the three operation modes are compared below.
| Operation Mode |
Transaction Security |
Transmission Mechanism |
Arbitration required? |
Witness Server |
Failover type |
| High Availability |
Full |
Synchronization |
Y |
Y |
Automatic or manual |
| High-level protection |
Full |
Synchronization |
Y |
N |
Manual only |
| High Performance |
Off |
Asynchronous |
N |
N/ |
Only force |
3. 1. high-availability Operation ModeServer role principal server, mirror server, and witness server. Application scenarios require high service availability and automatic failover to ensure data integrity.
. High-level protection modeServer role principal server, backup storage. For scenarios with high server integrity requirements, the Department requires automatic failover and low service availability requirements.
3. High-Performance ModeServer role principal server, backup storage. In application scenarios, the main server and the backup server are far away from each other, and the communication link has a significant delay, which requires high performance and data integrity.
4. database image failover
4. 1. automatic failoverFor high availability mode only, the transaction security is full. 1. each instance in the session detects the existence of the physical server SQL server instance body, Image Database 2. when the subject is invisible to the image and the witness server, the Failover image is converted to the new master database. After the master database is restored, the automatic failover function is disabled if the witness service is disabled.
4. Manual failoverFor high availability and advanced protection modes, the transaction security is full. 1. Perform the manual transfer operation on the subject. 2. The user currently connected to the subject is disconnected. The manual failover mode can be used as a method for Rolling server upgrade, similar to rolling upgrade of nodes in the MSC cluster.
4. Force ServiceFor high-performance mode only, the transaction security is off. 1. forcibly enable the service on the image database may cause data loss. Generally, the application is in high-performance mode. Note: The forced service mode can also be applied in high-availability mode. For example, the image and the witness server are unavailable, the condition that the service needs to be quickly restored.
5. Database Image MonitoringFor database image monitoring, msdn mentions two types of monitoring information: 1. database image metadata in the directory view. 2. database image performance counters. We recommend that you view the msdn document for more information.
6. Reference resourcesIf you are interested in SQL Server 2005 image service technology, we recommend that you check webcast, A technet instructor, to use database images to ensure high availability of database applications. This article introduces the SQL Server image service part of the content from this webcast, the final interpretation right belongs to the original author. Use Database images to ensure high availability of database applications (webcast) http://www.microsoft.com/china/technet/webcasts/class/ SQL _2005_2.mspx? Postid = 33315 questions to consider when using SQL Server 2005 database mirroring functionality http://support.microsoft.com/kb/907741/zh-cn SQL Server 2005 database mirroring profile http://publish.it168.com/2006/0328/20060328025801.shtml? Positioncode = 1545 SQL Server 2005 High Availability mirror feature http://tech.it168.com/db/s/2007-04-24/200704240837593.shtml SQL Server 2005 books online http://www.microsoft.com/downloads/results.aspx? Pocid = & freetext = SQL % 20 server % 202005% 20% u8054 % u673a % u4e1b % u4e66 & displaylang = ZH-CN
