Shulou(Shulou.com)06/02 Report--

Preparedness Server Planning Server Role Machine Name/Role Name Software IP Principal Server RepAsql Server 2008 r2192.168.0.10 Mirror Server RepBsql Server 2008 r2192.168.0.20 Witness Server win7sql server 2008 r2192.168.0.30

2. server-related settings

1. Create a folder Certificates are used to store certificates ShareFolders is used to store shared files and facilitate file transfer between servers. 2. Ensure that the data folder paths of the principal server and the mirror server are the same. 3. Ensure that the recovery mode of the database is FULL. Configure the mirror with a certificate, backup the RepA database, and restore it to RepB.

Create database master key

USE master;GOCREATE MASTER KEY ENCYPTION BY PASSWORD ='Pa$w0rd';/* Delete master key USE master;DROP MASTER KEY Use the same method in RepB to break master key

Create a certificate and encrypt it with the master key

USE master; GO CREATE CERTIFICATE Host_A_Cert WITH Subject = 'Host_C Certificate', Expiry_Date = '99991231' /* delete a certificate USE master; DROP CERTIFICATE HOST_A_Cert Create HOST_B_Cert certificate in RepB using the same method

3. Create endpoints (i.e. listening)

CREATE ENDPOINT EDP_Mirror STATE = STARTED AS TCP( LISTENER_PORT = 5022, --Communication port used by mirror endpoint LISTENER_IP = ALL) --IP address of interception FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE Host_A_Cert, --Certificate authentication ENCRYPTION = DISABLED, --Do not encrypt the transmitted data, if encryption is required, can be configured as SUPPORTED or REQUIRED, and can choose the encryption algorithm ROLE = ALL) --Endpoint supports all database mirroring roles, can also be set to WITNESS(witness only), or PARTNER(mirror partner only) In RepB use the same method, ××× name, to create endpoints

4. Backup certificate

BACKUP CERTIFICATE Host_A_Cert TO FILE = 'C:\ShareFolders\Host_A_Cert.cer'; Do the same on RepB, backing up Host_B_Cert Both RepA and RepB must have certificates for all servers in their Certificates folder, which can be copied via shared folders

5. Create user and login accounts

Create login account to mirror server USE master; CREATE LOGIN Host_B_Login WITH PASSWORD = 'Pa$$w0rd'; Create user and map to login account CREATE USER Host_B_User For Login Host_B_Login; Use the same method on RepB to create user and login accounts for RepB, note the name change

6. Authorize users with certificates

Create a new certificate and import it with the certificate copied from the partner server, then authorize the account created above CREATE CERTIFICATE Host_B_Cert AUTHORIZATION Host_B_User FROM FILE = 'C:\Certifications\Host_B_Cert.cer'; Do the same on RepB, noting the name change

7. Login account authorization access port

GRANT CONNECT ON ENDPOINT::EDP_Mirror TO Host_B_Login; Do the same on RepB, noting the name change

8. Backup and restore database

Full backup of database on RepA and restore to RepB with Norecovery option (using restore files and filegroups)

9. Start mirroring

Note the sequence, now execute on RepB ALTER DATABASE MirrorDB SET PARTNER = 'TCP://RepA:5022'; GO Then execute on RepA ALTER DATABASE MirrorDB SET PARTNER = 'TCP://RepB:5022'; GO Over. Next up is configuring the witness. Configuring the witness.

1. Configure witness master key certificates, endpoints, and authorizations similar to RepA and RepB. Copy certificates to ensure that each server has three server certificates

Execute the following statement on RepA and RepB to create endpoint permissions for the witness

USE master;CREATE LOGIN Host_C_Login WITH PASSWORD = 'Pa$$w0rd';GOCREATE USER Host_C_User For Login Host_C_Login;GOCREATE CERTIFICATE Host_C_CertAUTHORIZATION Host_C_UserFROM FILE = 'C:\Certifications\Host_C_Cert.cer';GOGRANT CONNECT ON ENDPOINT::EDP_Mirror TO Host_C_Login;GO

3. Principal server RepA joins witness server

ALTER DATABASE MirrorDB SET WITNESS = 'TCP://win7:5022'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.