Article 3-Part 2-Article 1 Introduction to SQL Server Images

Source: http://blog.csdn.net/dba_huangzj/article/details/26951563

What is an image? To put it bluntly, it's a mirror (no mirror? Do you always need to urinate without a mirror? Joke .. ). Here, the mirror has two meanings: 1. They are identical. The following describes in detail, including the storage path of the Database Name, data file, and log file. 2. The image library is not accessible when no processing is performed. Here is a professional explanation:

Database Mirroring has been introduced since SQL Server 2005 SP2. Although SQL Server Mirroring is listed as "will be discarded" since MySQL 2008, it has many advantages, it has been widely used till now. This article will introduce the basis of the image and compare it with other high-availability solutions provided by SQL Server. The SQL Server Literacy Series will separately introduce various high-availability solutions, so we will not introduce them too much here, mainly for comparison.

 

Terms:

This series will use many terms such as images and even high availability. Therefore, we will introduce the following concepts:

• Principal server, Principal: the source server that contains the active library in the image environment, which can be understood as the master server.
• Backup storage, Mirror: the backup storage that contains the target database in the backup storage.
• Witness server, Witness: an optional server used to monitor the master server and backup server. Its primary role is to perform automatic Failover ).
• Partner server, Partner: Compared with the image environment, the backup storage is the Partner server of the master server, and the backup storage is the Partner server of the backup storage.
• Endpoint: an object bound to the network protocol, allowing SQL Server to interact between networks through the Endpoint.
• Session: a Session that is active in an image environment and is used to maintain state information and relationships between servers. Simply put, it is the sender of information between partner servers in the image environment.
• Operating Mode: Operating Mode indicates the security level of the image environment. There are three Operating modes: high security Mode with automatic failover (synchronous Mode with witness server ), high Security mode without automatic failover (synchronous mode without witness servers) and high performance mode (asynchronous synchronization Without Witness servers ).
• Role: The Role in the image environment. At the same time, a specific server can only be one of the three roles: subject, image, or witness.

 

Running Mode:

At a large level, there are only two modes for SQL Server images: high security mode and high performance mode. The main difference between the two modes is the operation after the transaction is committed. You can view the running mode from Figure 1-1.

In high-performance mode, the master server can submit transactions without waiting for the response from the backup server.

In high security mode, you need to synchronize the transaction to the image and get a response before finally committing the transaction of the main server.

 

Note: No matter which mode is used, the master database must be configured as the full recovery mode.

 

Figure 1-1 SQL Server image running mode

High Security Mode, High-Safety Mode:

 

This mode is a synchronization mode, which can be divided into a high-security mode with automatic failover (that is, a witness server) and a high-security mode without automatic failover (that is, no witness server. If the witness server is not configured, the [High Security (synchronization) with the automatic failover function] option will be gray, that is, not optional.

The biggest difference between the two is whether to introduce the witness server. As mentioned above, the witness server can be used as an arbitration to detect the status of the subject server. Once the witness server cannot connect to the subject server, the session is automatically switched to the backup storage. If the backup storage does not have a witness, you need to switch the backup storage manually.

In high security mode, transactions must be committed on the Image Library before they can be committed in the main database. This also means that the entire program must wait for the image to submit the transaction before it can be finally submitted, if the network conditions are unsatisfactory, the entire operation process will be affected. The high-security mode supports standard and enterprise versions, and the subject and backup server must be of the same version. For example, either the Standard Edition or the Enterprise Edition is supported.

If you need the highest level of image security, you can use the witness server for arbitration. The witness server is not required, but is required for the automatic Failover (Failover) function. The witness server can use Workgroup or Express.

The witness server is used to check whether the connection between the master database and the image database is normal in the image environment. The witness server does not actually execute the Failover, but only informs the mirror server: "The main server is down ". Even if the witness server goes down, it simply cannot automatically Failover without affecting the image environment. The witness server can be understood to be used only to answer: is the main server down? Figure 1-2 is a high-security mode with a witness Server

 

 

Figure 1-2 high security mode with witness Server

When a performance problem occurs, perform one-step Detection Based on the steps shown in Figure 1-2.

High-Performance Mode:

 

This mode is asynchronous and can only be manually Failover, so there is no need to set the witness server (it can be set, but it doesn't make any sense .). This mode may cause data loss. Compared with the high-security mode, this mode does not need to be confirmed by the backup storage. Therefore, it is a good choice in environments with poor network conditions. Figure 1-3 shows the high-performance running mode.

Figure 1-3 high-performance Running Mode

 

Synchronous and asynchronous processing:

 

From Figure 1-1, we can see that the three running modes can be divided into two types of processing, synchronous and asynchronous. When the image is running in synchronization mode, the database's SAFETY option is FULL. When the image is asynchronous, the database SAFETY option is OFF. Both high-security mode and high-performance mode adopt asynchronous processing. Table 1-1 lists the main features of the two modes:

Table 1-1 synchronization and asynchronous modes:

Mode	Version requirements	Data Loss	SAFETY Option	Performance impact	Recovery speed	Failover
Synchronization	Standard/Enterprise	0 loss	FULL	Network may affect performance	Fast	Automatic
Asynchronous	Enterprise Edition	Data may be lost.	OFF	Small impact	Depends on the amount of transactions to be committed	Not automatic

Figure 1-4 SQL Server image running mode selection

The running mode and importance of SQL Server images directly affect configuration, budget, fault detection, and performance optimization. You need to make an assessment in the early stage and select a mode that meets the current SLA requirements.

Session:

 

After configuring the database image, you can start the image session. In the interaction between all servers in the image environment, session is used to maintain the status information of the other server. In essence, starting a session is to start the synchronization process between the master database and the image database.

Pause and resume sessions:

 

When the server encounters a performance problem, you can temporarily stop the database session because of the pressure on the image. However, it is important to note that the pause session will cause the log to remain active and cannot be truncated. If the pause session lasts for too long, this will cause the rapid growth of log files and cause a series of performance problems. For more information about log backup, see SQL Server literacy. Address: http://blog.csdn.net/dba_huangzj/article/details/26844859

SSMS pause session:

You can use the method shown in Figure 1-5 to temporarily perform an image session.

 

Figure 1-5 pause a session

T-SQL pause, resume session:

You can execute the following script on the master database or image database to pause and resume the session:

Alter database AdventureWorks2008R2 set partner suspend; -- pause the session alter database AdventureWorks2008R2 set partner resume; -- RESUME the session

After the database image session is started, the master server sends a transaction to the backup server. All transactions that are not sent to the backup server are collected to the send queue ). In high security mode, send queue is created only when the image library is paused. In high-performance mode, not only is the image paused, but sending queue may occur even if the server is in high usage, slow network, or a large redo queue on the image server, or for other reasons.

In the image library, transactions that have been transferred but not written into the image library will be stored in the redo queue. If the redo operation fails, the backup storage suspends the session until the problem is resolved.

The queue introduction will be introduced in the sixth article "Monitoring and optimizing SQL Server images" in this series. Http://blog.csdn.net/dba_huangzj/article/details/26846203

Note: A database can only have one image database. If you need to maintain multiple copies, you can add images through log transmission.

Image status:

The SQL Server image status may include the following:

• SYNCHRONIZING: synchronization in progress. It usually appears when the database image is enabled for the first time, indicating that the backup storage is catching up with the master server.
• SYNCHRONIZED: the synchronization has been completed. Most of the time it is in this State. Once an explosive transaction is transferred to the image database, the state will change from SYNCHRONIZED to SYNCHRONIZING. In high security mode, this status usually does not cause data loss. It only indicates that the backup storage is being synchronized. However, in high performance mode, data loss may occur.
• Suincluded: SUSPENDED. This status occurs when the master server does not send a transaction to the backup storage. (If the backup storage is still running, only Failover is used, but if the image library is disconnected, it will appear ). When an error occurs when you manually pause the image session or redo log.
• PENDING_FAILOVER: this status occurs only when the master server becomes an image server and the user is disconnected. In this status, both the master server and the backup server are in this status. However, the witness server displays one of the following statuses: CONNECTED/DISCONNECTED/UNKNOWN.
  • CONNECTED: indicates that the witness server can connect to one of the partners, and the other two representatives cannot connect to the partner server. In this case, the database becomes unavailable. If the image environment uses the witness, if the backup storage is DISCONNECTED and the backup storage crashes, the database (even on the master server) becomes inaccessible. Therefore, when the WITNESS is disconnected, you can disable the WITNESS and use alter database <DB> set witness off to disable arbitration.
  • DISCONNECTED: This occurs when no partner in the image environment can connect to the other party.

You can use the sys. database_indexing ing directory view to view the image information.

Switch roles:

 

Compared with other high-availability features, you can easily switch roles from an image to an SQL Server image. You can switch roles in the following three ways:

Manual Failover:

Use the T-SQL statement:

Use mastergoALTER DATABASE <DB> set partner failover -- execute

Use SSMS:

 

Figure 1-6 manual Failover using SSMS

Note: manual switching is not supported in high-performance mode.

Automatic Failover:

In high security mode with a witness server, when the master fails to connect or stops working, it automatically switches to the backup storage. When the original master server is re-connected, the original master server will become an image server in the image environment.

Forced switchover that may cause data loss:

This switching mode supports high-performance and high-security modes without witness servers, which can be achieved using the following T-SQL statement:

Alter database <DB> set partner FORCE_SERVICE_ALLOW_DATA_LOSS -- run

Transparent client redirection:

Provided by SQL Native Client (SNAC), the application can be automatically redirected to the image database in the image environment. You can add the Failover_Partner keyword to the connection string. The application needs to add a function to retry the connection.

SQL Server2008 image improvement: log stream compression:

Based on minimizing the impact of network bandwidth, 2008 introduces the log stream compression function. However, the compression and decompression function naturally increases the CPU overhead.

Automatic page restore:

In the past, page corruption was a headache and hard to recover. 2008 of the image function restores the corresponding page of the Image Library to the page of the main library to restore data. However, some page images cannot be replied, such as the file header (page 0), Database startup page (boot page, page 9), SGAM, and PFS. However, the image can be restored in the following situations:

• Error 823: OScyclic redundancy check (CRC) failure
• Error 824: logical errors including a bad page checksum or torn write
• Error 829: page has been marked as restore pending

Versions required for SQL Server image functions:

As shown in figure 1-7, the editions required for each function in the SQL Server image are as follows:

 

Figure 1-7 versions required for each function in the SQL Server Image

Other high availability comparison

As of SQL Server 2012, the built-in high-availability Cluster, mirror, Replication, Log Shipping, and AlwaysOn (2012 ). Among them, AlwaysOn has basically implemented the combination of clusters and images, so this article does not compare images with AlwaysOn. Only compare with other parts. For more information, see the official documentation:

Http://msdn.microsoft.com/zh-cn/library/ms190202 (v = SQL .105). aspx

The following briefly introduces the comparison between images and other parts:

Cluster advantages:

• This part refers to the Cluster earlier than 2012. Based on the Windows Failover Cluster, It can automatically detect the health status of SQL Server and perform automatic Failover (automatic Failover ). In addition, the switching time is almost equal to the start time of the SQL Server service. Unless a large number of transactions require redo, it generally does not delay for a long time, together with the high-security operating mode with witness servers, it is called the zero-latency high-availability technology before 2012. The other two cannot achieve automatic failover and zero latency.
• Through the virtual network name, the client can transparently access the active instance without modifying the program connection string. This is further improved than the image because the image has only one image library, therefore, after the first successful Failover, if no processing is performed, the original master database in the image environment will be brought online again.
• You can specify upgrade and maintenance for inactive nodes from 2008.

Disadvantages:

• If a shared disk is used, the entire Cluster will be paralyzed if there is a problem with the shared disk.
• Inactive nodes remain in the stopped status, so they cannot share the load and cause resource waste.
• High implementation cost, requires at least 3 machines and must be in the domain.
• Failover is performed on the entire instance. Unlike the image, if a Failover is required for only one or a few databases, the image can be transferred separately, but the Cluster cannot, this will cause a few unrelated libraries to be implicated.

Cluster has been translated into a Cluster, but it doesn't matter. You can understand this. I personally prefer to use English.

Replication)

Replication is inherently not a high-availability technology, but is actually used for data synchronization. Replication is not the first choice for high-availability scenarios.

Advantages:

• Object-level synchronization can be refined to columns and rows.
• The subscriber Library (the target database in the replication environment) is readable and supports read/write splitting.
• Multiple databases can subscribe to one database. Latency can reach seconds.
• You can use different SQL Server versions.

Disadvantages:

• Automatic failover is not provided.
• The object 0 is not guaranteed to be lost.
• Fault Detection is difficult, and the error information is often not obvious.
• The definition of a table has certain limitations. For example, transaction replication requires that the table must have a primary key.

Advantages of Log Shipping:

• The target database can be used as a report. In addition, the pressure on the main server is very small.
• Supports redundant copies for remote warm-up.
• The Mechanism is simple and fault detection is easier.

Disadvantages:

• Different Versions of SQL Server are not supported.
• The latency is certain and cannot be fully synchronized.
• Automatic Detection and transfer are not supported.
• When the log is restored, the target database cannot be accessed externally.
• Synchronization is in the Database Unit.

 

The following is a summary of the table in "SQL Server 2012 Practice Guide for Implementation and Management:

Function	Cluster	Log Transmission	Images	Copy
Protection Level	Instance	Library	Library	Database objects
Data Loss	/	Possible	None in synchronization mode	Possible
Automatic failover	Yes	No	In high security mode	No
Transparent to clients	Yes	No	Yes, but you need to set the string	No
Downtime	Service-based restart	Long	Equal to the recovery time	Long
Multiple slave Databases	No	Yes	No	Yes
Readable backup copy	/	Yes	No	Yes
Defense Against misoperation	No	Yes	No	No
Resist disk faults	No	Yes	Yes	Yes
Whether specific hardware is required	Windows Cluster	None	Disks and networks with good requirements	None
Impact on Performance	Low	Medium	Medium	High
Supported versions	2000 start	2000 start	2005 start	2000 start