Part 3-Part 2-plan to build an SQL Server Image

This article follows the previous chapter: SQL Server image Introduction

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

As the saying goes: to do good deeds, you must first sharpen your tools. Planning how to deploy and use images can reduce unnecessary risks. This article will be shown in the form of three steps, but note that this is not the only standard, specific analysis of the specific situation.

Step 1: understand the environment

When creating an SQL Server image, you must first understand the environment you want to deploy to determine the image configuration items. This is not only a prerequisite for image configuration, but also a task for deploying SQL Server and even building a data platform and Other High Availability services. The following are some common problems that need to be understood:

1. Is the server ready?
2. Is the database ready?
3. Do you know the required service account?
4. Know the database size?
5. Performance of the backup storage and the primary server
6. Need to be combined with other technologies?

The following describes the six points in detail:

Are the servers ready:

According to the image requirements, you must use SQL Server 2005 or later versions. SP1 is the first version that fully supports the image function. Ideally, the operating system used by the master server and the backup server must be the same as the SQL Server version. The SQL Server version must be enterprise or standard. In addition, the data files and log files of the database must be in the same drive letter and directory name. If they are inconsistent, an error is reported when the main database sends transactions to the image database because they cannot be identified.

If a witness server is introduced, it can run in a working group or express version.

Are the databases ready:

First, make sure that no filestream option is used by the filestream file group because filestream operates local files through a T-SQL and the image cannot read files on the master server.

Second, the image environment requires the full recovery mode.

Do you know the required service account:

In the deployment process, the simplest thing is to use a domain account. If you use the same service account, you do not need to authorize it in the endpoint. If you use a local system account to run the image, you must use certificate authorization instead of Windows authorization. When using a certificate, pay attention to the expiration time of the certificate. Like other best practices, if you cannot use a domain account, we recommend that you use a dedicated account.

Whether to know the database size:

If a large image library is required, possible risks must be taken into account during initialization. This step may take several hours. If the business itself is busy at this time, and the image library needs to catch up with the progress of the main database, it will lead to serious performance problems, at least the network transmission pressure will be very high.

In this case, you can use the log shipping function for transmission. Be sure to use the NORECOVERY option instead of the STANDBY option. As mentioned in setting up an image, the image library must use the NORECOVERY status.

 

Performance of the backup and master servers:

Ideally, the performance of the backup storage should be close to that of the master server, because the failure may take over all requests of the master server for a short time. If the backup storage performance is too low, this slows down user response. In extreme cases, the backup storage may not be able to withstand the pressure of the original master server in a short period of time. That is to say, the backup storage may also stop responding, which will eliminate the original intention of building an image. After all, image building is mainly for this situation.

 

Need to be combined with other technologies:

In enterprise applications, only one high-availability technology is rarely used, and images may be used together with replication, log transmission, and even clusters. More rigorous tests are required for hybrid use.

 

Step 2: Understand the application:

In addition to understanding the hardware of the image environment, you also need to understand the software, that is, the applications running in this environment. Some of these applications are black boxes, especially third-party software. You need to consider the following aspects:

1. How is an application connected to the server?
2. Is there any component that does not support automatic Failover?
3. Whether the application depends on other libraries
4. Whether the application depends on external resources

How is an application connected to the server:

To support images, applications must use SQL Native Client, ADO. NET 2.0 Data Provider, or JDBC 1.1 Driver for SQL Server. The connection string must use the Failover partner attribute. If you build an image without adding the Failover Partner attribute, you must manually modify the connection string of the application every time you perform the Failover operation, which affects the business continuity of the application.

Are there components that do not support automatic Failover:

If the DTS package, SSIS package, or external applications use a connection protocol that does not support images, You need to evaluate the impact of Failover and develop response policies. A common solution is to copy these components to the backup storage and connect them to the backup storage.

Whether the application depends on other libraries:

An image is a high-availability solution at the database level. If an application needs to use multiple databases to run collaboratively, mirroring only one database is not feasible. In this case, all dependent databases can be mirrored and the trigger can be used to detect the image status. If one database meets the Failover status, all databases are forced to be Failover. This requires additional programming.

Whether the application depends on external resources:

If the application depends on the resources of the local server, Failover will cause exceptions to the application. In this case, you can consider placing external resources on the shared folder and using the UNC address for access.

 

Step 3: Test Plan:

1. We recommend that you use a dedicated domain account and archive the account on the primary server and backup storage to prevent other maintenance personnel from remembering the account and password after a long time.
2. Sa is not recommended for image libraries.
3. If the CLR depends on the TRUSTWORTHY configuration, it must be configured after the Failover is initialized. You can solve this problem by using the same database owner. That is to say, the image library and the main database should be fully consistent during the construction process, including the database owner.
4. During the image configuration process, make sure that all database backup jobs are disabled. Both full backup and log backup will affect the failure of Backup recovery.
5. Make sure that the image is configured in full mode.
6. Make sure that the name and path of the data files and log files of the relevant databases on the backup and master servers are the same. By the way, the system library cannot be used as an image.

 

Practical suggestions:

1. Use an image server that is as close as possible to the performance of the main server.
2. Using a dedicated network for data transmission in an image environment, network and disk I/O are often common bottlenecks of images and other high-availability technologies. Especially when a large transaction volume is transferred.
3. In high-performance mode, do not use the witness server; otherwise, there is a risk of service loss. When the witness cannot connect to the subject or image, another partner Will offline because of the loss of arbitration.
4. Use the same drive letter and file path.
5. Perform stress testing in the testing environment. Ensuring that the image environment is not a guise, but a feature that truly assists in business continuity.
6. In the production environment, run in asynchronous mode. If the performance is satisfactory, switch to the synchronous mode. If the synchronous mode is also satisfied, add the witness server.
7. SQL Server is recommended to use 2005 SP2 (with CU6) or 2008. 2008R2 is recommended.
8. Make sure that the image and the master Server are of the same SP and SQL Server versions.
9. Use the same sorting rules.
10. The maintenance plan does not support the image function and requires additional programming to process the state field in sys. databases. This article introduces SQL Server image routine maintenance.
11. Save the image configuration script and file. This allows quick reconstruction and version control.
12. Do not set the partner's timeout time to less than 10 seconds. A too small timeout will affect the normal operation of the image, but in practice, the longer the image, the better. The upper limit is generally 30 ~ 50 seconds.
13. You can use logshipping to synchronize images during initialization. Logshipping can also be used as an auxiliary function in high-performance mode.
14. Monitors the suspect_pages system table in msdb, which is used to repair torn pages.
15. Avoid using the same vswitch or vro to connect the subject and image. The main reason is to avoid affecting the entire network environment due to the failure of both vswitches and vrouters.
16. Make sure that the ports required by the image are not occupied. Some ports are required for the image, although not mandatory, but must be specified. Therefore, the network must not only be connected, but also the port can be Telnet, and firewall configuration should also be considered.

This article does not show each vertex, but it will be resolved in the following articles as much as possible. Image building in a domain environment and image building in a non-Domain environment can be seen in the next two articles: Configuring an SQL Server image-non-Domain environment: http://blog.csdn.net/dba_huangzj/article/details/27652857
Configure an SQL Server image -- domain environment: http://blog.csdn.net/dba_huangzj/article/details/28904503