SQL Server database Mirroring (ii) Full mirroring script configuration in a domain environment

SQL Server database Mirroring (i) Single-server mirror script configuration without witness

SQL Server database Mirroring (ii) Full mirroring script configuration in a domain environment

After a few days, build the domain, the server joined the domain and then test another situation.

Test environment:

Primary server:

IP = 192.168.2.10

INSTANCENAME = MSSQLSERVER

Listener_port = 5022

Mirror server:

IP = 192.168.2.10

INSTANCENAME = Mssqlservera

Listener_port = 5023

Witness server:

IP = 192.168.2.11

INSTANCENAME = MSSQLSERVER

Listener_port = 5022

"1. Database backup Restore "

--Principal: Set the database "recovery mode" to "full" mode use master; ALTER database [DBName] SET RECOVERY full  go--body: Backing up databases use master; BACKUP database [DBName] to  DISK = ' C:\Databases\MSSQLSERVER\DBName.BAK ' with Init,format  GO  --Mirroring: Restoring Databases ( NORECOVERY) use master; RESTORE DATABASE [DBName] from  DISK = n ' C:\Databases\MSSQLSERVER\DBName.BAK '  with FILE = 1, MOVE n ' DBName ' to n ' C: \databases\mssqlservera\dbname.mdf ',  MOVE n ' dbname_log ' to n ' C:\Databases\MSSQLSERVERA\DBName_log.ldf ',  Nounload, NORECOVERY, STATS = 10GO

"2. Create a database master key and certificate, back up the Exchange certificate"

--Main body: use master; CREATE MASTER KEY Encryption by PASSWORD = ' [email protected] '; CREATE CERTIFICATE cert_kk_db1_mssqlserver with SUBJECT = ' Cert_kk_db1_mssqlserver ', start_date = ' 2015-03-20 '; BACKUP CERTIFICATE cert_kk_db1_mssqlserver to FILE = ' C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserver.cer ';--Mirror: Use Master CREATE MASTER KEY Encryption by PASSWORD = ' [email protected] '; CREATE CERTIFICATE cert_kk_db1_mssqlservera with SUBJECT = ' Cert_kk_db1_mssqlservera ', start_date = ' 2015-03-20 '; BACKUP CERTIFICATE cert_kk_db1_mssqlservera to FILE = ' C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserverA.cer ';-- Witness: Use master; CREATE MASTER KEY Encryption by PASSWORD = ' [email protected] '; CREATE CERTIFICATE cert_kk_db2_mssqlserver with SUBJECT = ' Cert_kk_db2_mssqlserver ', start_date = ' 2015-03-20 '; BACKUP CERTIFICATE cert_kk_db2_mssqlserver to FILE = ' C:\Databases\CERTIFICATE\Cert_kk_db2_mssqlserver.cer ';--Exchange certificate ( Mutual copy certificate):/* Principal certificate (copy to) ———— > Mirror, witness image certificate (copy to) ———— > body, Witness Witness certificate (copy to) ———— > body, Mirror */

"3. Create a database login account and user and restore the certificate"

--Create a domain User: userformirror--sqlserver use [Network Service] to start the instance service-the principal (the certificate that restores the image and the witness): Using Mastergocreate LOGIN [kk\ Userformirror] from WINDOWS; Gocreate USER userformirror for LOGIN [Kk\userformirror]; Gocreate CERTIFICATE [cert_kk_db1_mssqlservera]authorization [userformirror] from FILE = ' C:\Databases\CERTIFICATE\ Cert_kk_db1_mssqlservera.cer '; Gogrant CONNECT on Endpoint::endpoint_for_mirror to [Kk\userformirror]; Gocreate CERTIFICATE [cert_kk_db2_mssqlserver]authorization [userformirror] from FILE = ' C:\Databases\CERTIFICATE\ Cert_kk_db2_mssqlserver.cer '; Gogrant CONNECT on Endpoint::endpoint_for_mirror to [Kk\userformirror]; go--mirroring (Restore principal and witness certificates): Use Mastergocreate LOGIN [kk\userformirror] from WINDOWS; Gocreate USER userformirror for LOGIN [Kk\userformirror]; Gocreate CERTIFICATE [cert_kk_db1_mssqlserver]authorization [userformirror] from FILE = ' C:\Databases\CERTIFICATE\ Cert_kk_db1_mssqlserver.cer '; Gogrant CONNECT on Endpoint::endpoint_for_mirror to [Kk\userformirror]; Gocreate CERTIFICATE [Cert_kK_db2_mssqlserver]authorization [Userformirror] from FILE = ' C:\Databases\CERTIFICATE\Cert_kk_db2_mssqlserver.cer '; Gogrant CONNECT on Endpoint::endpoint_for_mirror to [Kk\userformirror]; go--Witness (Restore principal and mirrored certificates): Use Mastergocreate LOGIN [kk\userformirror] from WINDOWS; Gocreate USER userformirror for LOGIN [Kk\userformirror]; Gocreate CERTIFICATE [cert_kk_db1_mssqlserver]authorization [userformirror] from FILE = ' C:\Databases\CERTIFICATE\ Cert_kk_db1_mssqlserver.cer '; Gogrant CONNECT on Endpoint::endpoint_for_mirror to [Kk\userformirror]; Gocreate CERTIFICATE [cert_kk_db1_mssqlservera]authorization [userformirror] from FILE = ' C:\Databases\CERTIFICATE\ Cert_kk_db1_mssqlservera.cer '; Gogrant CONNECT on Endpoint::endpoint_for_mirror to [Kk\userformirror]; GO

"4. Create a database mirroring endpoint"

--Subject: (domain account: kk\userformirror,uthentication = CERTIFICATE certificate) use master; CREATE ENDPOINT [Endpoint_for_mirror] AUTHORIZATION [Kk\userformirror]state=startedas TCP (Listener_port = 5022, LISTENER_IP = All) for data_mirroring (ROLE = PARTNER, authentication = CERTIFICATE Cert_kk_db1_mssqlserver, encryption = R equired algorithm AES) go--Mirror (main mirror same server, port not the same: Listener_port = 5023): Use master; CREATE ENDPOINT [Endpoint_for_mirror] AUTHORIZATION [Kk\userformirror]state=startedas TCP (Listener_port = 5023, LISTENER_IP = All) for data_mirroring (ROLE = PARTNER, authentication = CERTIFICATE Cert_kk_db1_mssqlservera, encryption = REQUIRED algorithm AES) go--Witness (ROLE = WITNESS): Use master; CREATE ENDPOINT [Endpoint_for_mirror] AUTHORIZATION [Kk\userformirror]state=startedas TCP (Listener_port = 5022, LISTENER_IP = All) for data_mirroring (ROLE = WITNESS, authentication = CERTIFICATE Cert_kk_db2_mssqlserver, encryption = R equired algorithm AES) GO

"5. Start Mirroring"

--Note the execution order: mirror-to-body-and-witness-Mirror: (partner is the principal server) use master; ALTER DATABASE [DBName] SET PARTNER = ' tcp://192.168.2.10:5022 ' go--body: (PARTNER is the mirror server) use master; ALTER DATABASE [DBName] SET PARTNER = ' tcp://192.168.2.10:5023 ' go--body: (Witness as witness server) use master; ALTER DATABASE [DBName] SET WITNESS = ' tcp://192.168.2.11:5022 '; GO

Configuration Complete! Interface View

"6. Create the database Mirroring Monitor Job

--Execute use msdb in the main preparation;  EXEC sys.sp_dbmmonitoraddmonitoring  --exec sys.sp_dbmmonitorhelpmonitoring  --exec sys.sp_ Dbmmonitorresults dbname,0,0  --exec sys.sp_dbmmonitorchangemonitoring  --exec sys.sp_ Dbmmonitordropmonitoring  

"7. Test

--Subject: Arbitrarily change, wait for the switch after the use DBName;  SELECT * FROM [dbo]. [MyTable]  UPDATE [dbo]. [MyTable] SET NAME = ' master '  DELETE TOP (1) from [dbo].[ MyTable]      --Body: Manual mode for main and standby switch use  [master]  GO  ALTER DATABASE DBName SET PARTNER FAILOVER; GO  

manually stopping the service of the principal, you can see that "mirror" becomes "principal"

When the original principal's service starts, it turns out to be "mirrored." (You can also switch it back to the body at this point)

"7. Related Scripts "

SELECT * FROM Sys.certificatesselect * from Sys.endpointsselect * to Sys.database_mirroring_endpointsselect * from SYS.D Atabase_mirroring SELECT * from sys.database_mirroring_witnesses use master; ALTER DATABASE [DBName] set SAFETY full;--set to High security mode ALTER DATABASE [DBName] set PARTNER resume;--restore mirror ALTER DATABASE [DBName ] Set PARTNER failover;--toggle main standby ALTER DATABASE [DBName] set PARTNER force_service_allow_data_loss;--force restore mirror ALTER DATABASE [ DBName] SET online;--online database

"8. Questions"

/* Problems that may occur when the "problem" starts mirroring (note: The above script is correct, here is the error case): ALTER DATABASE [DBName] SET PARTNER = ' tcp:// 192.168.1.10:5023 ' hint error is as follows: Msg 1418, Level 16, State 1, Row 2nd server network Address "tcp://192.168.1.10:5023" is unreachable or does not exist. Check the network address name and check that the ports on the local and remote endpoints are working correctly. "Problem" found that the IP was mistaken, the 192.168.1.10 changed to 192.168.2.10 re-execution: use master; ALTER database [DBName] SET PARTNER = ' tcp://192.168.2.10:5022 ' Go hint error is as follows: DB "DBName" is enabled for database mirroring. "Resolve" stop mirroring, reconnect alter DATABASE [DBName] SET PARTNER OFF;  Goalter database [DBName] Set PARTNER = ' tcp://192.168.2.10:5022 ' GO "problem" link Mirror error: (IP and port can connect to) ALTER DATABASE [DBName] Set WITNESS = ' tcp://192.168.2.11:5022 ' hint error: Msg 1456, Level 16, State 3, line 1th cannot send ALTER DATABASE command to remote server instance ' tcp://192.168.2.11:502 2 '. The database mirroring configuration has not changed. Make sure that the server is connected, and then try again. "Resolution" Authentication was authorized for windows at that time, changed to certificate create ENDPOINT [Endpoint_for_mirror] ... authentication = CERTIFICATE */

SQL Server database Mirroring (ii) Full mirroring script configuration in a domain environment