Chapter three--second part--second article plan to build SQL Server image

This article follows the previous chapter: Introduction to SQL Server mirroring

The source of this article: http://blog.csdn.net/dba_huangzj/article/details/27203053

As the saying goes: 工欲善其事 its prerequisite. Planning how to deploy and use mirroring can reduce a lot of unnecessary risk. This article will be presented in the form of three steps, but be aware that this is not the only standard, specific case analysis.

First step: Understanding the Environment

When you build a SQL Server image, you must understand the environment that you are deploying before you can decide which configuration items to mirror. This is not only the premise of mirroring configuration, but also the task of deploying SQL Server and even building a data platform and other high availability. Here are some common questions to understand:

1. Is the server ready?
2. Is the database ready?
3. Do you know the required service account number?
4. Do you know the size of the database
5. Performance of the mirror server and the principal server
6. Whether it is necessary to combine with other technologies

Here is a detailed introduction to these 6 points:

Whether the server is ready:

Depending on the requirements of the mirror, you must use a version of SQL Server 2005 SP1 above, and SP1 is the first version that fully supports the mirroring feature. Ideally, the principal and mirror servers use the same operating system and SQL Server version as possible. For SQL Server versions, you must be an enterprise or Standard Edition . In addition, the database data files and log files in the same drive and directory names must be consistent, if not consistent, when the main library to send transactions to the mirror library will cause an error because it is not recognized.

If a witness server is introduced, it can be run on a workgroup or express version.

Whether the database is ready:

You first need to make sure that no filegroups are using the FILESTREAM option because FileStream is manipulating local files through T-SQL, mirroring cannot read files on the principal server in the mirror server.

Second, the mirrored environment requires a full recovery model.

Do you know the required service account number:

In the deployment process, the simplest 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 are running mirroring using the Local System account, you must use certificate authorization to override Windows licensing. When you use a certificate, you need to be aware of the certificate's expiration time. As with other best practices, it is recommended that you use a dedicated account operation if you cannot use a domain account

Whether you understand the size of the database:

If you need to make a large library of mirrors, you should consider the possible risks during initialization. This step can take several hours because the general step is to make a full backup, then transfer the backup to the mirror server and then restore it, and then make the log backup on the principal database and restore it to the mirror. If the business itself is busy at this time, coupled with the mirror library to catch up with the progress of the main library, will lead to serious performance problems, at least the network transmission pressure will be very large.

For this scenario, you can use the log shipping feature for transmission, and be careful to use the NORECOVERY option instead of the standby option. In the build image article, it is mentioned that the mirror library needs to use the NORECOVERY state.

Performance of the mirror server and the principal server:

Ideally, the performance of the mirror server should be close to the principal server, as the failover may take over all requests from the principal server for a short period of time, and if the mirror server performance is too low, it can cause the user to respond slower. In extreme cases, the mirror server may not be able to withstand the stress of the original principal server in the short run, which means that the mirror server may also stop responding, which will cause the image to be built without any intention. After all, building the image is mainly for this situation.

Whether you need to combine with other technologies:

In enterprise applications, only a single, highly available technology is rarely used, and mirroring can be used with replication, log transfer, and even clustering. When used in combination, more rigorous testing is required and will be mentioned later.

Step two: Learn about the application:

In addition to understanding the hardware part of the mirrored environment, you should also understand the software section, which is the application running in this environment, some of which are "black boxes", especially third-party software. There are a few things to consider in this regard:

1. How the application is connected to the server
2. Whether there are components that do not support automatic failover
3. Whether the application relies on other libraries
4. Whether the application relies on external resources

How the application is connected to the server:

If you need to support mirroring, your application needs to use SQL Native Client, ADO 2.0 Data provider, or JDBC 1.1 Driver for SQL Server. and the connection string needs to use the failover partner property. If you build the image without adding the failover partner property, you will need to manually modify the application's connection string each time you failover, which can affect the business continuity of the program.

Is there a component that does not support automatic failover:

If you use a connection agreement that does not support mirroring, such as a DTS package, an SSIS package, or an external application, you need to evaluate the impact of failover and develop a coping strategy. The common approach is to copy these components to the mirror server and configure the connection to the Mirror library.

Whether the application relies on other libraries:

Mirroring is a high-availability scenario at the library level, and it is not feasible to mirror only one library if the application needs to work together with multiple databases. In this case, you can mirror all the libraries you rely on, and use triggers to detect the state of the mirror, forcing all libraries to be failover if a library's state satisfies failover. This requires additional programming.

Whether the application relies on external resources:

If an application relies on the resources of a native server, failover can cause an application to be unexpected, in which case you might consider placing an external resource on a shared folder and accessing it with a UNC address.

Step Three: Inspection plan:

1. Establish the required account on the principal server and the mirror server, it is recommended to use a dedicated domain account, and to do the filing, to avoid other maintenance personnel or time too long after not even remember the account password.
2. The Mirror library does not recommend using SA as owner.
3. If the CLR relies on trustworthy configuration, it needs to be configured after initializing failover. can be resolved by using the same database owner. That is, the image Library and the main library in the construction process should be as consistent as possible, including the database owner.
4. During the mirroring configuration process, ensure that all database backup jobs are disabled, and both full and log backups will affect the mirror server recovery failure.
5. Ensure that the mirror is configured in full mode.
6. Ensure that the data files and log file names and paths of the relevant databases on the mirror server and the principal server are exactly the same. Incidentally, the system library cannot be mirrored.

Practice recommendations:

2. Use a mirror server that is as close as possible to the principal server performance.
4. With private networks for data transfer in mirrored environments, network and disk I/O are often a common bottleneck for mirroring and other highly available technologies. Especially in the event of mass transit.
6. Do not use the witness server in high-performance mode, otherwise there is a risk of service loss, and when the witness cannot connect to the principal or mirror, another partner will be offline because of the loss of quorum.
8. Use the same drive letter and file path.
10. Perform stress tests in the test environment. Make sure that the mirroring environment is not a cover-up, but a function that really helps business continuity.
12. In a production environment, run asynchronously, if the performance is met, switch to synchronous mode, and then add the witness server if the synchronization mode is also met.
14. SQL Server preferably uses the 2005 SP2 (with CU6), or 2008, 2008R2 is recommended.
16. Make sure that the mirror and principal server are the same SP and SQL Server version.
18. Use the same collation.
20. The maintenance plan does not support mirroring and requires additional programming to handle the state field in sys.databases. Described in the "SQL Server mirroring Daily Maintenance " article.
22. Save the configuration scripts and files for the image. For rapid reconstruction and version control.
24. Do not set the timeout time of the partner to less than 10 seconds. A small timeout can affect the normal operation of the mirror, but in practice it is not as long as it is, and the general limit is 30-50 seconds.
26. You can temporarily use logshipping synchronization when initializing a mirror. LogShipping can also be used as an accessibility feature in high-performance mode.
28. Monitor suspect_pages system tables in msdb for repairing torn pages.
30. Avoid using the same switch or router for connecting principals and mirrors. The main reason is to avoid the failure of the switch and router to affect the entire network environment.
32. Make sure that the ports required for mirroring are not occupied, and that building a text will be delayed. The mirror needs some ports, although not mandatory, but to specify, so the network not only to connect, but also serve something port can telnet, firewall configuration should also be considered.

There is no expansion for each point in this article, but as far as possible, it will be resolved in a few subsequent installments.