SQL Server 2005 Mirror Build Manual (sql2005 Database Synchronous mirroring Scheme) _mssql2005

A brief introduction of mirroring

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. Mirroring is not directly accessible, it can be accessed only in the case of error recovery.

The minimum requirements required for database mirroring include two different SQL Server running environments. The primary server is called a "host", and the second server is called a "standby". The host database is the database you actually use, and the mirror database is an alternate copy of your database. When transactions are written to your base server, they are also routed 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 basic and mirroring when it comes to determining when error recovery occurs. Use this option only if 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 error recovery. The witness server needs to be used only if you want to implement automatic error recovery.

2. Advantages

The following table is a comparison of the official SQL Server availability solution. At present, the recovery model used in my center is "backup/restore" in "cold Backup", in general, "hot backup" is more usable than "cold backup", the recovery is faster, it is more suitable for the current situation of my center. The availability of failover clustering in hot backup is the highest without cost considerations, but failover clustering requires disk arrays and the complexity of building itself is high. Database mirroring is not built with much hardware requirements, at least not as high as "failover clustering" requires shared storage.

2. Disadvantages

(1) Since SQL Server is a product of multiple databases, database mirroring technology is based on the database level, so each new database for the primary database must add a database for the standby and establish a mirroring relationship for the new database.

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

(3) Database operations can not be the corresponding maintenance.

(4) Microsoft's so-called mirror image can make the client to the fault transparent, but the actual test found that only meet specific conditions to achieve transparency, and transparent client support is feasible (. NET Framework more than 2.0, Microsoft JDBC driver more than 1.1).

(5) Database mirroring is not supported across database transactions and distributed transactions.

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

First, configure the main standby machine 1, physical connection

Connect the primary database as shown in the following illustration:

2. Check SQL Server 2005 database

Only SQL Server 2005 Standard Edition, Enterprise Edition, and Development Edition can establish data mirroring. The other version, Express, can only serve as a witness server. If you don't really know what version, execute the following statement to view:

SELECT @ @version;

To perform a database mirroring of this database, you must change it to use the full recovery model. To do this in Transact-SQL, use the ALTER DATABASE statement:

 Use master;
 ALTER DATABASE <DatabaeName> 
 SET RECOVERY full;

Second, the main standby instance interoperability
Interoperability can be achieved using a domain or certificate, consider the implementation of the simple, the following method of selecting the certificate implementation. Note: Implementing the "exchange of primary and standby database instances" operation only needs to be done once, for example, to build a mirror relationship for 5 databases in two instances of SQL Server 2005, you need to do the following only once, or to understand that each pair of primary standby instances (not databases) is interoperable.

1, create the certificate (master ready to be executed in parallel)

--Host execution: use

master;
CREATE MASTER KEY Encryption by PASSWORD = ' Killkill ';
CREATE certificate Host_a_cert with SUBJECT = ' host_a certificate ', 
start_date = ' 01/01/2008 ';

--Standby Machine execution: use

master;
CREATE MASTER KEY Encryption by PASSWORD = ' Killkill ';
CREATE certificate Host_b_cert with SUBJECT = ' Host_b certificate ', 
start_date = ' 01/01/2008 ';

2, create the endpoint of the connection (host can be executed in parallel)

--Host execution:

CREATE ENDPOINT endpoint_mirroring state 
= 
started 
as TCP (listener_port=5022, listener_ip =  ALL] 
for database_mirroring 
(authentication = Certificate Host_a_cert, encryption = REQUIRED algorithm AES , role = all);

--Standby Machine execution:

CREATE ENDPOINT endpoint_mirroring state 
= 
started 
as TCP (listener_port=5022, Listener_ IP = All) 
for database_mirroring 
(authentication = Certificate Host_b_cert, encryption = REQUIRED Algori THM AES, role = ALL);

3, back up the certificate for the interconnection (the main standby can be executed in parallel)

--Host execution:

BACKUP certificate host_a_cert to FILE = ' D:\SQLBackup\HOST_A_cert.cer ';
 --Standby Machine execution:

BACKUP certificate host_b_cert to FILE = ' D:\SQLBackup\HOST_B_cert.cer ';

4. Exchange Certificate
The certificate that is backed up to D:\SQLBackup\ is exchanged, that is, the D:\SQLBackup\ that Host_a_cert.cer copied to the standby. Host_b_cert.cer replicated to the host D:\SQLBackup\

5, add login name, user (master standby can be executed in parallel)
The following actions can only be run through the command line and cannot be completed through the graphical interface. (The indefinite number of SQL Server2005 is SP2 as of the end of document writing)

--Host execution:

CREATE LOGIN host_b_login with PASSWORD = ' Killkill ';
CREATE USER host_b_user for LOGIN Host_b_login;
CREATE Certificate Host_b_cert AUTHORIZATION host_b_user from FILE = ' D:\SQLBackup\HOST_B_cert.cer ';
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_b_login];

--Standby Machine execution:

CREATE LOGIN host_a_login with PASSWORD = ' Killkill ';
CREATE USER host_a_user for LOGIN Host_a_login;
CREATE Certificate Host_a_cert AUTHORIZATION host_a_user from FILE = ' D:\SQLBackup\HOST_A_cert.cer ';
GRANT CONNECT on endpoint::endpoint_mirroring to [Host_a_login];

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

1. Manually sync login name and password
One of the drawbacks of mentioning database mirroring in the first chapter is the inability to maintain the login name, so we need to manually maintain the login.

Typically, a database will have several users as the user who accesses the database, and the database will have the corresponding login name, but there is no corresponding login in the standby, for example, a business system using ' myuser ' as a login to access the database, but there is no ' myuser ' in the standby name, As a result, the business system cannot log on to the database once the primary standby is switched, which is known as "orphaned user"

Execute the following statement in the primary database:

Use master;
Select Sid,name from syslogins;

Find out the appropriate username and SID, for example: The above ' myuser '

Execute the following statement in the standby database:

Use master;
EXEC sp_addlogin 
@loginame = ' <LoginName> ', 
@passwd = ' <Password> ', 
@sid = <sid>;

The ' LoginName ' here is the login name in the primary database, and the SID is the SID that is found by the SQL statement above.

For example, the SID and name of the query are shown below.

Sid Name
---------------------------------- -----------------
0x074477739dca0e499c29394fffc4ade4 Cz_account

The SQL statement that establishes the login name:

Use master;
EXEC sp_addlogin 
@loginame = ' Cz_account ', 
@passwd = ' password ', 
@sid = 0x074477739dca0e499c29394fffc4ade4;

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

2. Prepare the database of standby machine
To undertake the above, this section describes how to synchronize data within a master-standby database.

You can try to restore from a fully-prepared file that you just used, and you need to use the "with non recover" when you restore the data. As shown in the figure:

If the execution of a successful database becomes this way:

3, establish a mirror

Because is the experiment, did not configure the dual network card for the server, the IP address and the graph are somewhat different, but the principle is same.

--Host execution:

ALTER DATABASE shishan SET PARTNER = ' tcp://10.168.6.45:5022 ';

-If the main execution is unsuccessful, try executing the following statement in the standby:

ALTER DATABASE shishan SET PARTNER = ' tcp://10.168.6.49:5022 ';

If the execution succeeds, the primary database will render the icon as shown in the previous illustration.

If the build fails, prompting a similar database transaction log not synchronized, the data (log) of the primary database is not synchronized, and a "transaction log" Backup should be implemented in the primary database and restored to the standby database to ensure consistent data within the primary database. Back up the transaction log as shown in the figure:

You need to select Restore WITH NORECOVERY in the options when restoring the transaction log, as shown in the figure:

The SQL statement that establishes the mirror is executed after a successful restore.

Iv. Test Operations

1. Main Standby Exchange

--Host execution:

Use master;
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;

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

--Standby Machine execution:

Use master;
ALTER DATABASE <DatabaseName> SET PARTNER force_service_allow_data_loss;

3, the original primary server recovery, you can continue to work, need to reset the mirror

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

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

--Turning off transaction security switches the session to asynchronous run mode, which gives you the best performance.

Use master;
ALTER DATABASE <DatabaseName> set PARTNER SAFETY full;--transaction security, synchronization mode
ALTER database <DatabaseName> Set PARTNER SAFETY off; -Transaction not secure, asynchronous mode