SQL Server Mirroring

SQL Server 2005 Image authoring

Here are the steps to proceed:
-- ===========================================
--whether it is the principal server, the mirror server, or the witness server
--Except for special instructions, you need to ensure that the following operations are performed in the master Library
Use master
GO

-- ===========================================
--(1) establishing the mirror principal database
--This operation is performed on the principal server
--A. Build a test database
CREATE DATABASE Db_mirror
On (
NAME = Db_mirror_data,
FILENAME = N ' C:\DB_Mirror.mdf '
)
LOG on (
NAME = Db_mirror_log,
FILENAME = N ' C:\DB_Mirror.ldf '
)
ALTER DATABASE Db_mirror SET
RECOVERY full
GO

--B. Full backup
BACKUP DATABASE Db_mirror
To DISK = N ' C:\DB_Mirror.bak '
With FORMAT
GO

-- ===========================================
--(2) initializing the mirror principal database
--This operation is performed on the mirror server
--assuming that a full backup of the principal database has been replicated to C:\DB_Mirror.bak
RESTORE DATABASE Db_mirror
From DISK = N ' C:\DB_Mirror.bak '
With REPLACE
, NORECOVERY
--If the mirror database file is to be placed in the specified location, enable the Move option below
--, MOVE ' db_mirror_data ' to N ' C:\DB_Mirror.mdf '
--, MOVE ' db_mirror_log ' to N ' C:\DB_Mirror.ldf '
GO

-- ===========================================
--(3) The database mirroring endpoint on the principal server and the certificate used for authentication
--This operation is performed on the principal server
--A. Certificate for database Mirroring endpoint Authentication
IF not EXISTS (--Use the database master key to encrypt the certificate
SELECT * from Sys.symmetric_keys
WHERE name = N ' # #MS_DatabaseMasterKey # # ')
CREATE MASTER KEY
Encryption by PASSWORD = N ' abc.123 '

CREATE CERTIFICATE Ct_mirror_srva
With
SUBJECT = N ' Certificate for Database Mirror ',
start_date = ' 19990101 ',
expiry_date = ' 99991231 '
GO

--B. Back up the certificate so that the certificate is established at the other end of communication with this endpoint
BACKUP CERTIFICATE Ct_mirror_srva
to FILE = ' C:\CT_Mirror_SrvA.cer '
GO

--C. Database mirroring endpoint  
CREATE ENDPOINT edp_mirror 
State = started 
as TCP ( 
Listener_port = 5022, --The communication port used by the mirroring endpoint  
Listener_ip = All)    --The IP address of the listener  
    for Database_mirroring ( 
authentication = CERTIFICATE Ct_mirror_srva, certificate authentication  
Encryption = DISABLED,                       -does not encrypt transmitted data, if encryption is required, can be configured as supported or REQUIRED, and the encryption algorithm   can be selected;
ROLE = All)                                    --Endpoint supports all database mirroring roles, or can be set to WITNESS (witness server only) , or PARTNER (mirroring partner only)  
GO

-- ===========================================
--(4) The database mirroring endpoint on the mirror server and the certificate used for authentication
--This operation is performed on the mirror server
--A. Certificate for database Mirroring endpoint Authentication
IF not EXISTS (--Use the database master key to encrypt the certificate
SELECT * from Sys.symmetric_keys
WHERE name = N ' # #MS_DatabaseMasterKey # # ')
CREATE MASTER KEY
Encryption by PASSWORD = N ' abc.123 '

CREATE CERTIFICATE ct_mirror_srvb
With
SUBJECT = N ' Certificate for Database Mirror ',
start_date = ' 19990101 ',
expiry_date = ' 99991231 '
GO

--B. Back up the certificate so that the certificate is established at the other end of communication with this endpoint
BACKUP CERTIFICATE ct_mirror_srvb
to FILE = ' C:\CT_Mirror_SrvB.cer '
GO

--C. Database mirroring endpoint  
CREATE ENDPOINT edp_mirror 
State = started 
as TCP ( 
Listener_port = 5022, --The communication port used by the mirroring endpoint  
Listener_ip = All)    --The IP address of the listener  
    for Database_mirroring ( 
authentication = CERTIFICATE ct_mirror_srvb, certificate authentication  
Encryption = DISABLED,                       -does not encrypt transmitted data, if encryption is required, can be configured as supported or REQUIRED, and the encryption algorithm   can be selected;
ROLE = All)                                    --Endpoint supports all database mirroring roles, or can be set to WITNESS (witness server only) , or PARTNER (mirroring partner only)  
GO

-- ===========================================
--(5) Complete the transport security mode configuration of the principal server database mirroring endpoint on the mirror server
--This operation is performed on the mirror server
-A. Establish a certificate on the principal server (assuming that the certificate backed up on the principal server has been copied to C:\CT_Mirror_SrvA.cer)
CREATE CERTIFICATE Ct_mirror_srva
From FILE = ' C:\CT_Mirror_SrvA.cer '

--B. Create a login
CREATE LOGIN Login_mirror_srva
From CERTIFICATE Ct_mirror_srva

--C. Grant connect permission to the database mirroring endpoint
GRANT CONNECT on Endpoint::edp_mirror
To Login_mirror_srva
GO

-- ===========================================
--(6) Complete the transport security mode configuration of the mirror server database mirroring endpoint on the principal server
--This operation is performed on the principal server
-A. Establish a certificate on the principal server (assuming that the backed up certificate on the mirror server has been copied to C:\CT_Mirror_SrvB.cer)
CREATE CERTIFICATE ct_mirror_srvb
From FILE = ' C:\CT_Mirror_SrvB.cer '

--B. Create a login
CREATE LOGIN login_mirror_srvb
From CERTIFICATE ct_mirror_srvb

--C. Grant connect permission to the database mirroring endpoint
GRANT CONNECT on Endpoint::edp_mirror
To Login_mirror_srvb
GO

-- ===========================================
--(7) Enable database mirroring on the mirror server
--This operation is performed on the mirror server
ALTER DATABASE Db_mirror SET
PARTNER = ' tcp://srva:5022 '
GO

-- ===========================================
--(8) Enable database mirroring on the principal server (default is High security mode, so no mode setting is necessary)
--This operation is performed on the principal server
ALTER DATABASE Db_mirror SET
PARTNER = ' tcp://srvb:5022 '
GO

-- ===========================================
--(9) Configuring the witness server
--This action is performed on the witness server
--A. Completing the transport security mode configuration of the database mirroring endpoint on the witness server
--(a). Certificate for database Mirroring endpoint Authentication
IF not EXISTS (--Use the database master key to encrypt the certificate
SELECT * from Sys.symmetric_keys
WHERE name = N ' # #MS_DatabaseMasterKey # # ')
CREATE MASTER KEY
Encryption by PASSWORD = N ' abc.123 '

CREATE CERTIFICATE ct_mirror_srvwitness
With
SUBJECT = N ' Certificate for Database Mirror ',
start_date = ' 19990101 ',
expiry_date = ' 99991231 '
GO

--(b). Back up the certificate so that the certificate is established at the other end of communication with this endpoint
BACKUP CERTIFICATE ct_mirror_srvwitness
to FILE = ' C:\CT_Mirror_SrvWitness.cer '
GO

--(c). Database mirroring endpoint  
CREATE ENDPOINT edp_mirror 
State = started 
as TCP ( 
Listener_ Port = 5022, --The communication port used by the mirroring endpoint  
Listener_ip = All)    --The IP address of the listener  
    For database_mirroring ( 
authentication = CERTIFICATE ct_mirror_srvwitness,-certificate authentication  
Encryption = disabled,                            -does not encrypt the transmitted data, if encryption is required, can be configured as supported or REQUIRED, And can choose the encryption algorithm  
ROLE = All)                                          --Endpoints support all database mirroring roles, can also be set to WITNESS (witness only), or PARTNER (mirroring partners only)  
GO

--B. Completing the transport security mode configuration of the database mirroring endpoint on the principal server
--(a). Establish a certificate on the principal server (assuming that the certificate backed up on the principal server has been copied to C:\CT_Mirror_SrvA.cer)
CREATE CERTIFICATE Ct_mirror_srva
From FILE = ' C:\CT_Mirror_SrvA.cer '

--(b). Establish login
CREATE LOGIN Login_mirror_srva
From CERTIFICATE Ct_mirror_srva

--(c). Grant connect permission to the database mirroring endpoint
GRANT CONNECT on Endpoint::edp_mirror
To Login_mirror_srva
GO

--C. Completing the transport security mode configuration of the database mirroring endpoint on the mirror server
--(a). Establish a certificate on the mirror server (assuming that the backed up certificate on the mirror server has been copied to C:\CT_Mirror_SrvB.cer)
CREATE CERTIFICATE ct_mirror_srvb
From FILE = ' C:\CT_Mirror_SrvB.cer '

--(b). Establish login
CREATE LOGIN login_mirror_srvb
From CERTIFICATE ct_mirror_srvb

--(c). Grant connect permission to the database mirroring endpoint
GRANT CONNECT on Endpoint::edp_mirror
To Login_mirror_srvb
GO

-- ===========================================
--(10) Complete the transport security mode configuration of the witness server database mirroring endpoint on the mirror server
--This operation is performed on the mirror server
-A. Establish a certificate on the witness server (assuming that the certificate backed up on the witness has been copied to C:\CT_Mirror_SrvWitness.cer)
CREATE CERTIFICATE ct_mirror_srvwitness
From FILE = ' C:\CT_Mirror_SrvWitness.cer '

--B. Create a login
CREATE LOGIN login_mirror_srvwitness
From CERTIFICATE ct_mirror_srvwitness

--C. Grant connect permission to the database mirroring endpoint
GRANT CONNECT on Endpoint::edp_mirror
To Login_mirror_srvwitness
GO

-- ===========================================
--(11) Complete the transport security mode configuration of the witness server database mirroring endpoint on the principal server
--This operation is performed on the principal server
-A. Establish a certificate on the witness server (assuming that the certificate backed up on the witness has been copied to C:\CT_Mirror_SrvWitness.cer)
CREATE CERTIFICATE ct_mirror_srvwitness
From FILE = ' C:\CT_Mirror_SrvWitness.cer '

--B. Create a login
CREATE LOGIN login_mirror_srvwitness
From CERTIFICATE ct_mirror_srvwitness

--C. Grant connect permission to the database mirroring endpoint
GRANT CONNECT on Endpoint::edp_mirror
To Login_mirror_srvwitness
GO

-- ===========================================
--(12) Enable the witness server for database mirroring on the principal server
--This operation is performed on the principal server
ALTER DATABASE Db_mirror SET
WITNESS = ' tcp://srvwitness:5022 '
GO

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- ===========================================
--The following actions can be used to determine the synchronization
--1. Querying database status
--The following script can be executed on the principal server and the mirror server, and the execution result is the state of the mirror
SELECT
Mirroring_role_desc,--the current role of the database in the mirroring session
Mirroring_state_desc,--mirroring the current state
Mirroring_safety_level_desc,--Mirror run mode
Mirroring_witness_state_desc--Connection to the witness server
From sys.database_mirroring
WHERE database_id = db_id (N ' Db_mirror ')
GO

--2. Data testing
--B. Execute the following statement on the principal server to establish the test table
CREATE TABLE DB_MIRROR.DBO.TB (
ID int)
WAITFOR DELAY ' 00:00:01 '
GO

-B. On the mirror server, create a snapshot database of the mirrored database so that the current data can be queried
CREATE DATABASE Snp_db_mirror
On (
NAME = Db_mirror_data,
FILENAME = N ' C:\SNP_DB_Mirror.mdf ')
As SNAPSHOT of Db_mirror
GO

--C. Querying from the snapshot database whether the test table is synchronized
SELECT * from SNP_DB_MIRROR.DBO.TB
GO

--D. Delete the snapshot database established by the test
DROP DATABASE Snp_db_mirror
GO

-- ===========================================
--The following action is used to remove the mirrored object from this sample configuration
--the backup of certificates and databases needs to be removed in the operating system's resource Manager
--1. Actions performed on the principal server
--A. Stop mirroring and delete the principal database
Use master
GO

ALTER DATABASE Db_mirror SET
PARTNER OFF
DROP DATABASE Db_mirror
GO

--B. Delete the mirroring endpoint
DROP ENDPOINT Edp_mirror
GO

--C. Deleting logins and certificates
DROP LOGIN login_mirror_srvb
DROP LOGIN login_mirror_srvwitness
DROP CERTIFICATE Ct_mirror_srva
DROP CERTIFICATE ct_mirror_srvb
DROP CERTIFICATE ct_mirror_srvwitness
GO

--2. Actions performed on the mirror server
--A. Deleting a mirrored database
Use master
GO

DROP DATABASE Db_mirror
GO

--B. Delete the mirroring endpoint
DROP ENDPOINT Edp_mirror
GO

--C. Deleting logins and certificates
DROP LOGIN Login_mirror_srva
DROP LOGIN login_mirror_srvwitness
DROP CERTIFICATE Ct_mirror_srva
DROP CERTIFICATE ct_mirror_srvb
DROP CERTIFICATE ct_mirror_srvwitness
GO

--3. Actions performed on the witness server
--A. Deleting endpoints
DROP ENDPOINT Edp_mirror
GO

--B. Deleting logins and certificates
DROP LOGIN Login_mirror_srva
DROP LOGIN login_mirror_srvb
DROP CERTIFICATE Ct_mirror_srva
DROP CERTIFICATE ct_mirror_srvb
DROP CERTIFICATE ct_mirror_srvwitness
GO

--------------------------------------------------------------------------------------------------------------- ----------------------------

Reprinted from: http://www.cnblogs.com/tianyue3107/archive/2010/01/06/1640554.html

SQL Server Mirroring