SQL Server 2005 Mirroring Build Guide

Reprint: http://www.cnblogs.com/killkill/archive/2008/05/23/1205792.html

First, the image introduction

1. Introduction

Database mirroring is the move of database transactions from one SQL Server database to another SQL Server database in a different SQL Server environment. The image cannot be accessed directly; it can only be accessed in the case of an error recovery.

The minimum requirements required to perform database mirroring include two different SQL Server runtime environments. The primary server is called a "host" and the second server is called "standby". The host database is the database you are actually using, and the mirror database is the backup copy of your database. When transactions are written to your base server, they are also transferred to and written to your mirror database.

In addition to basic and mirroring, you can also introduce another optional component called "Witness". The witness server is the third running instance of SQL Server 2005, which is used for internal communication between the base and the mirror when determining when to perform an error recovery. This option is only used when you want to implement automatic error recovery. It achieves 2:1 of the ability to vote when one of my components is unreachable and therefore requires an error recovery. The witness server only needs to be used if you want to implement automatic error recovery.

2. Advantages

The following table is a comparison of the official SQL Server availability solution, and the recovery model currently used in my center is "backup/restore" in "cold Backup", which generally means "hot backup" is more usable than cold backup, restores faster, and is more suitable for my center's current situation. Failover clustering in hot backup is the highest availability without cost considerations, but failover clustering requires a disk array and the building itself is more complex. Database mirroring is not built with much hardware requirements, at least not as high as the "failover cluster" requires shared storage.

2. Disadvantages

(1) Because SQL Server is a product of an instance of multiple databases, database mirroring technology is database-level based, so every time a new database is added to the primary database, the database must be added to the standby and a mirroring relationship is established for the new database.

(2) The database login name and user is stored in the master database, the master database can not be mirrored, so each operation of the database login and user also need to maintain a copy,

(3) The database operation can not be maintained accordingly.

(4) Microsoft claims that the mirror can make the client transparent to the fault, but the actual test found that only the specific conditions to achieve transparency, and transparent client support is feasible (. NET Framework 2.0, Microsoft JDBC Driver 1.1 or more).

(5) Database mirroring is not supported for cross-database transactions and distributed transactions.

In several other ways, only "hot backup" failover clustering does not have these problems.

First, configure the main standby 1, physical connection

To connect the primary and standby database as follows:

2. Check the SQL Server 2005 database

Only SQL Server 2005 Standard, Enterprise, and Development editions can establish data mirroring. Other versions of Express are only available as a witness server. If it is not clear what version, execute the following statement to view:

1Select @ @version;

To make database mirroring for this database, you must change it to use the full recovery model. To do this with Transact-SQL, use the ALTER DATABASE statement:

1 Usemaster;
2ALTER DATABASE <Databaename>
3SETRECOVERY Full;
4

Second, the primary and standby instances of interoperability

Interoperability can be achieved by using a domain or certificate, consider the implementation of the simple, the following method of selecting a certificate implementation. Note: Implementing the "Master and Standby DB instance interoperability" operation only needs to be done once, for example, in order to make the mirroring relationship for the 5 databases in two instances of SQL Server 2005, it is only necessary to do one of the following operations: each of the primary and standby instances (not the database) to do the interoperability.

1. Create certificate (master and standby can be executed in parallel)

--Host execution:

1 Usemaster;
2CREATEMASTERKEYEncryption byPASSWORD= 'Killkill';
3CREATECERTIFICATE Host_a_cert withSUBJECT= 'host_a Certificate' ,
4start_date= '01/01/2008';
5

--Standby Machine execution:

1 Usemaster;
2CREATEMASTERKEYEncryption byPASSWORD= 'Killkill';
3CREATECERTIFICATE Host_b_cert withSUBJECT= 'Host_b Certificate',
4start_date= '01/01/2008';
5

2. Create the endpoint of the connection (master and standby can be executed in parallel)

--Host execution:

1CREATEENDPOINT endpoint_mirroring
2 State=STARTED
3 as
4TCP (Listener_port=5022, Listener_ip=  All )
5 for
6Database_mirroring
7(Authentication=CERTIFICATE Host_a_cert, encryption=REQUIRED algorithm AES, ROLE=  All );
8

--Standby Machine execution:

1CREATEENDPOINT endpoint_mirroring
2 State=STARTED
3 as
4TCP (Listener_port=5022, Listener_ip=  All )
5 for
6database_mirroring
7(Authentication=CERTIFICATE Host_b_cert, encryption=REQUIRED algorithm AES, ROLE=  All );
8

3. Back up the certificate for interconnection (master and standby can be executed in parallel)

--Host execution:

1BACKUPCERTIFICATE Host_a_cert to FILE = 'D:\SQLBackup\HOST_A_cert.cer';

--Standby Machine execution:

1BACKUPCERTIFICATE Host_b_cert to FILE = 'D:\SQLBackup\HOST_B_cert.cer';

4. Interchange Certificate

The certificates that are backed up to D:\SQLBackup\ are interchanged, that is, the host_a_cert.cer is copied to the D:\SQLBackup\ of the standby machine. Host_b_cert.cer Copy to host D:\SQLBackup\

5, add login name, user (master and standby can be executed in parallel)

The following operations can only be run from the command line and cannot be completed through the graphical interface. (as of the end of document writing, the Server2005 of SQL is SP2)

--Host execution:

1CREATELOGIN Host_b_login withPASSWORD= 'Killkill';
2CREATE USERHost_b_user forLOGIN Host_b_login;
3CREATECERTIFICATE Host_b_certAUTHORIZATIONHost_b_user from FILE = 'D:\SQLBackup\HOST_B_cert.cer';
4GRANTCONNECT onendpoint::endpoint_mirroring to [Host_b_login];
5

--Standby Machine execution:

1CREATELOGIN Host_a_login withPASSWORD= 'Killkill';
2CREATE USERHost_a_user forLOGIN Host_a_login;
3CREATECERTIFICATE Host_a_certAUTHORIZATIONHost_a_user from FILE = 'D:\SQLBackup\HOST_A_cert.cer';
4GRANTCONNECT onendpoint::endpoint_mirroring to [Host_a_login];
5

Third, establish the mirror relationship

The following steps are for each database, for example: There are 5 databases in an existing host the following procedures are performed 5 times.

1. Manually synchronizing logins and Passwords

One of the drawbacks of database mirroring mentioned in the first chapter is that you cannot maintain logins, so we need to maintain logins manually.

In general, the database will have a number of users to access the database, and the database will have a corresponding login name, but in the standby is missing the corresponding login name, such as a business system using ' myuser ' as the login to access the database, but there is no ' myuser ' in the standby login name, Therefore, once the primary and standby switch, the business system will not be able to log into the database, this situation is called "Orphaned users"

Execute the following statement in the primary database:

1 Usemaster;
2SelectSid,name fromsyslogins;
3

Find the appropriate user name and SID, for example: ' MyUser ' above

Execute the following statement in the standby database:

1 Usemaster;
2execsp_addlogin
3@loginame = '<LoginName>',
4@passwd = '<Password>',
5@sid = <Sid> ;
6

Here the ' LoginName ' is the login name in the primary database, which is the SID that was found by the SQL statement above.

For example, the query gets the SID and name as shown below.

1SID Name
2
3---------------------------------- -----------------
4
50x074477739dca0e499c29394fffc4ade4Cz_account
6
7

The SQL statement that establishes the login name:

1 Usemaster;
2execSp_addlogin
3@loginame = 'Cz_account',
4@passwd = 'Password',
5@sid = 0x074477739dca0e499c29394fffc4ade4;
6

This concludes that the environment for the standby database is synchronized with the host, and the data in the database is not synchronized.

2. Prepare the database for the standby machine

To undertake the above, this section describes how to synchronize data within the master and standby database.

You can try to restore from the fully-prepared file you just used and use the "with non recover" option when restoring the data. ：

If the execution succeeds the database will look like this:

3. Create a mirror

Because it is an experiment, the server is not configured with a dual NIC, the IP address is a bit different from the diagram, but the same principle.

--Host execution:

1ALTER DATABASEShishanSETPARTNER= 'tcp://10.168.6.45:5022';

--If the principal execution is unsuccessful, try to execute the following statement in the standby machine:

1ALTER DATABASEShishanSETPARTNER= 'tcp://10.168.6.49:5022';

If the execution succeeds, the primary and standby database will render the icon as shown.

If the establishment fails, suggesting that similar database transaction logs are not synchronized, the data (log) of the primary and standby database is not synchronized, in order to ensure consistent data in the primary and standby database, a "transaction log" Backup should be implemented in the primary database and restored to the standby database. To back up the transaction log:

To restore the transaction log, select "Restore WITH NORECOVERY" in the options:

After a successful restore, the SQL statement that created the image is executed.

Iv. test operation

1. Primary and Standby interchange

--Host execution:

1 UseMaster
2ALTER DATABASE <DatabaseName> SETPARTNER FAILOVER;
3

2, the main server down, standby emergency start and start service

--Standby Machine execution:

1 Usemaster;
2ALTER DATABASE <DatabaseName> SETPARTNER Force_service_allow_data_loss;
3

3, the original primary server recovery, can continue to work, need to re-set the image

1--Standby Machine Execution:
2 Usemaster;
3ALTER DATABASE <DatabaseName> SETPARTNER RESUME;--Restore Image
4ALTER DATABASE <DatabaseName> SETPARTNER FAILOVER;--Switch Master and standby
5

4, the original primary server recovery, can continue to work

--By default, the transaction security level is set to full, which is synchronous run mode, and SQL Server 2005 Standard Edition only supports synchronous mode.

--Turn off transaction security to switch the session to asynchronous run mode, which allows for optimal performance.

1 Usemaster;
2ALTER DATABASE <DatabaseName> SETPARTNER SAFETY Full; --transaction Security, synchronous mode
3ALTER DATABASE <DatabaseName> SETPARTNER SAFETYOFF; --transaction insecure, asynchronous mode
4