Database two-Machine hot standby (code implementation)

SQL Server 2008 provides 3 high availability scenarios: How to distribute/subscribe, how to transfer transaction logs, and how to mirror them.

The former host (a server), and the standby (b server) on the database can be accessed simultaneously (of course, the database on B is a part of the data delay), the latter two of the database on the B server is in the "restoring" state, not read and write (that is, cannot be divided into task load).

Distribution/Subscription Method: Granularity for table/stored procedure/function/view ... The level. Asynchronously transfers an object change/or transaction/or snapshot on a server to the distributor and then to n subscribers.

Transaction routing Log mode: granularity is database level. A server back up the database, put to B to restore, and then a and then periodically back up the transaction log (in the shared directory), B regularly go (shared directory on a server) copy the backup transaction log, and then periodically restore up. (There can be multiple backup servers, b1,b2,b3 ....) ）。 Cons: Master/Standby needs to be manually switched, and once switched, all configurations are re-provisioned.

Mirroring mode: Granularity is database level. A server backs up the database and transaction log, puts it on B to restore, and then each transaction on a is synchronously/asynchronously committed to B. (one host for a standby machine). can be automatically switched or manually switched, configured once.

First, this document mainly describes the installation of SQL Server mirroring function, in the host, the operational steps required by the standby machine

Second, the need for the environment:

1. Operating system: Window server R2

2. Database: SQL server2008 R2 Developer Edition

III. installation setup steps: (assuming the database name is Testbackup)

1, install windows2008 R2

2, the Master and standby machine all modify the computer DNS suffix (This step can also be skipped)

On the My Computer---properties---computer name tab---The Change button---the other button---the input suffix, For example, tomisoft.net and so on, need to pay attention to 2 points: first, the DNS suffix of the master standby machine is exactly the same, the second is not with the actual domain name regardless of the external network or intranet conflict; Restart the computer after the restart, after restarting, pay attention to modify windows\system32\drivers\etc\ Hosts file, adding the exact computer name and IP correspondence to each other.

3. Install SQL server2008 r2 with hybrid authentication mode

4. Create DATABASE, configure host

Create a primary database that requires database mirroring

To make database mirroring for this database, you must change it to use the full recovery model

Execute the script in the host database as follows:

Use master;

ALTER DATABASE Testbackup

SET RECOVERY full;

5. Host BACKUP Database

A full backup of the host database can be done through the graphical interface,

6, Standby machine RESTORE Database:

First set up in the database with the same name as the target database empty library, and then the host through the full library backup completed database backup files to the local, can be scripted or graphical operation of the way to restore, if through the script, execute the following statement:

RESTORE DATABASE testbackup from disk= ' D:\TB. BAK ' with NORECOVERY

If you are using a graphical interface, be aware that you need to use the "with non recover" option when restoring data.

If the execution succeeds the database will look like this:

7, the host for transaction log backup

Transaction log backups of the database on the host through a graphical interface

8, Standby Restore transaction log backup:

Copy the transaction log files from the host backup to the local computer, and then restore the database logs from the graphical interface on the mirror server, selecting the Restore with NORECOVERY in the options when restoring the transaction log

9-12 steps can also be skipped, when configured in SQL Server Management Studio, you can use the SQL Server account password as the authentication method. (Graphical interface Configuration method: Right-click on the database to be mirrored on the host, select the Properties menu, and then select the Mirror tab tab)

9. Create a Certificate: (primary backup not performed)

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 a mirror relationship to a database of two instances of SQL Server 2008, it is only necessary to do one of the following operations;

Open SQL Server Management Studio, create a new query, execute the script:

Host:

Use master;

CREATE MASTER KEY Encryption by PASSWORD = ' Tomisoft ';

CREATE CERTIFICATE host_a_cert with SUBJECT = ' host_a CERTIFICATE ',

start_date = ' 01/01/2009 ';

Standby machine:

Use master;

CREATE MASTER KEY Encryption by PASSWORD = ' Tomisoft ';

CREATE CERTIFICATE host_b_cert with SUBJECT = ' Host_b CERTIFICATE ',

start_date = ' 01/01/2009 ';

10. Create the endpoint of the connection: (The main standby machine is executed separately)

Host:

CREATE ENDPOINT Dbmirrep

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:

CREATE ENDPOINT Dbmirrep

State = STARTED

As

TCP (listener_port=5022, listener_ip = All)

For

Database_mirroring

(authentication = CERTIFICATE Host_b_cert, encryption = REQUIRED algorithm AES, ROLE = All);

11. Back up the certificate for Interconnection: (Main and standby machines are executed separately)

Host:

BACKUP CERTIFICATE host_a_cert to FILE = ' D:\database \host_a_cert.cer '

Standby machine:

BACKUP CERTIFICATE host_b_cert to FILE = ' D:\database\HOST_B_cert.cer ';

Note here that the relevant directory is to exist

12, the exchange of certificates, the other side of the certificate copy to the local, that is, the host copy to the standby machine, the copy of the standby machine to the host

Host execution:

CREATE LOGIN host_b_login with PASSWORD = ' tomisoft ';

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:: Dbmirrep to [Host_b_login];

Standby Machine Execution:

CREATE LOGIN host_a_login with PASSWORD = ' tomisoft ';

CREATE USER host_a_user for LOGIN Host_a_login;

CREATE CERTIFICATE host_a_cert AUTHORIZATION host_a_user from FILE = ' D:\database\HOST_A_cert.cer ';

GRANT CONNECT on ENDPOINT::d bmirrep to [Host_a_login];

13, the host to establish the image:

In the target database properties for host A, select mirror, click ' Configure Security ... ' To configure the host and mirror machine name needs to be set to, shape: Tomisoft201.tomisoft.tomi Otherwise the mirroring relationship will be unsuccessful (not necessarily this way, or it can be used directly: IP address + port, or host name + port, which needs to be equipped with Hosts file. In this case, set up the service account, will be error, but does not affect.)

After the standby machine waits for the host to perform the database mirroring, the mirror database becomes the following mode, and if not, it can be refreshed by:

(d) Switchover of mirroring failure

1, the main machine after the processing:

If the host is behind the machine (the database displays: "Subject, Disconnected", the standby database becomes the following mode:

, the following script is executed in the standby machine, and the mirror library becomes the main available library for reading and writing:

ALTER DATABASE Testbackup SET partner Force_service_allow_data_loss

Note: The SQL service that found the principal database in the test must be started, otherwise the backup database is still in the ' recovering ' state after executing the above statement and is still unavailable. The correct state after switching indicates that the standby machine has become the current subject, and the previous body has been suspended.

2, after the host recovery processing:

If the host recovers, the following script is executed to reset the host to the host, and the current mirror still restores the mirroring mode:

First the host will execute the script:

Use master;

ALTER DATABASE testbackup SET PARTNER SAFETY full; --transaction security, synchronous mode

Standby Execution Script:

Use master;

ALTER DATABASE testbackup SET PARTNER RESUME

Note: Results after successful execution

After successful execution:

ALTER DATABASE testbackup SET PARTNER FAILOVER

Note: Execute the BACKUP database and restore the principal role to the original principal database.

3. Other Notes:

If the host is unable to recover and wants to host the current mirror, the mirror needs to be re-established

Under normal circumstances, if you want to make a primary standby switch, the host will be stopped artificially, the mirror as a host, you can perform the main backup of the host Exchange:

Use master;
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER

When the host recovers after the machine, by default, the transaction security level is set to full, that is, synchronous run mode, and SQL Server 2005 Standard Edition only supports synchronous mode.

Execute in Host:

Use master;
ALTER DATABASE <DatabaseName> SET PARTNER SAFETY Full--transaction security, synchronous mode (found during the test, when the backup machine performs a switchover of the main standby script, if the host does not execute the script first, the opportunity execution fails)

The following asynchronous patterns are generally not recommended:

Alterdatabase <DatabaseName> SET PARTNER SAFETY OFF; --transaction insecure, asynchronous mode