SQL Server database image (2) complete image script configuration in the domain environment, SQL Server Image

SQL Server database image (2) complete image script configuration in the domain environment, SQL Server Image

SQL Server database image (1) image script configuration without witness on a single server

SQL Server database image (2) complete image script configuration in the domain environment

After several days of intermittent modification, the domain has been set up. After the server is added to the domain, this situation is now tested.

Test environment:

Master Server:

IP = 192.168.2.10

InstanceName = MSSQLSERVER

Listener_ports = 5022

Backup storage:

IP = 192.168.2.10

InstanceName = MSSQLSERVERA

Listener_ports = 5023

 

Witness Server:

IP = 192.168.2.11

InstanceName = MSSQLSERVER

Listener_ports = 5022

[1. database backup and restoration]

-- Subject: SET "RECOVERY mode" of the DATABASE to "FULL" mode USE master; alter database [DBName] set recovery full go -- Subject: backup database use master; backup database [DBName] to disk = 'C: \ Databases \ MSSQLSERVER \ DBName. BAK 'with init, format go -- image: restore database (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, and back up and Exchange Certificates]

-- SUBJECT: USE master; create master key encryption by password = 'kk _ 2012 @ '; create certificate Cert_kk_db1_mssqlserver with subject = 'cert _ kk_db1_mssqlserver', START_DATE = '2017-03-20 '; backup certificate Cert_kk_db1_mssqlserver to file = 'C: \ Databases \ CERTIFICATE \ Cert_kk_db1_mssqlserver.cer '; -- image: USE master; create master key encryption by password = 'kk _ 2012 @'; create certificate failed with subject = 'cert _ kk_db1_mssqlserverA ', START_DATE = '2017-03-20'; backup certificate failed to file = 'C: \ Databases \ CERTIFICATE \ certificate'; -- witness: USE master; create master key encryption by password = 'kk _ 2012 @ '; create certificate Cert_kk_db2_mssqlserver with subject = 'cert _ kk_db2_mssqlserver', START_DATE = '2017-03-20 '; backup certificate Cert_kk_db2_mssqlserver to file = 'C: \ Databases \ CERTIFICATE \ Cert_kk_db2_mssqlserver.cer '; -- exchange certificates (mutual CERTIFICATE copy):/* subject CERTIFICATE (copy) ----> image, witness image certificate (copy to) ----> subject, witness certificate (copy to) ----> subject, image */

[3. Create a database Logon account and user and restore the certificate]

-- Create a domain user: UserForMirror -- SQLServer USE [network service] to start the instance service -- subject (restore the image and the certificate that you witnessed): USE masterGOCREATE LOGIN [KK \ UserForMirror] from windows; gocreate user UserForMirror for login [KK \ UserForMirror]; gocreate certificate [AUTHORIZATION] AUTHORIZATION [UserForMirror] from file = 'C: \ Databases \ CERTIFICATE \ certificate'; gogrant connect on endpoint :: endpoint_For_Mirror TO [KK \ UserForMirror]; gocreate certificate [Cert_kk_db2_mssqlserver] AUTHORIZATION [UserForMirror] from file = 'C: \ Databases \ CERTIFICATE \ certificate'; gogrant connect on endpoint :: endpoint_For_Mirror TO [KK \ UserForMirror]; GO -- image (Restore subject and witness Certificate): 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 \ users'; gogrant connect on endpoint :: endpoint_For_Mirror TO [KK \ UserForMirror]; gocreate certificate [Cert_kk_db2_mssqlserver] AUTHORIZATION [UserForMirror] from file = 'C: \ Databases \ CERTIFICATE \ certificate'; gogrant connect on endpoint :: endpoint_For_Mirror TO [KK \ UserForMirror]; GO -- Witness (restore the certificate of the subject and image): 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 \ users'; gogrant connect on endpoint :: endpoint_For_Mirror TO [KK \ UserForMirror]; gocreate certificate [Cert_kk_db1_mssqlserverA] AUTHORIZATION [UserForMirror] from file = 'C: \ Databases \ CERTIFICATE \ refer'; gogrant connect on endpoint :: endpoint_For_Mirror TO [KK \ UserForMirror]; GO

[4. Create a database image endpoint]

-- Subject: (domain account: KK \ UserForMirror, UTHENTICATION = CERTIFICATE) USE master; create endpoint [Endpoint_For_Mirror] AUTHORIZATION [KK \ UserForMirror] STATE = startedas tcp (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR data_indexing ing ): USE master; create endpoint [Endpoint_For_Mirror] AUTHORIZATION [KK \ UserForMirror] STATE = startedas tcp (LISTENER_PORT = 5023, LISTENER_IP = ALL) FOR data_configuring (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE failed, 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_indexing ing (ROLE = WITNESS, AUTHENTICATION = CERTIFICATE Cert_kk_db2_mssqlserver, ENCRYPTION = required algorithm aes) GO

[5. Start image]

-- Note the execution sequence: Image --> subject --> witness -- image :( PARTNER is the main server) USE master; alter database [DBName] set partner = 'tcp: // 192.168.2.10: 5022 'go -- Subject :( PARTNER is the backup server) USE master; alter database [DBName] set partner = 'tcp: // 192.168.2.10: 100' GO -- Subject :( WITNESS is the WITNESS server) USE master; alter database [DBName] set witness = 'tcp: // 192.168.2.11: 661'; GO

Configuration complete! Interface View

[6. Create a database image monitor job]

-- Execute use msdb; exec sys. sp_dbmmonitoraddmonitoring -- exec sys. Merge -- exec sys. sp_dbmmonitorresults DBName, 0, 0 -- exec sys. Merge -- exec sys. sp_dbmmonitordropmonitoring

[7. Test]

-- Subject: You can change it at will. After the switch, whether the USE DBName has been synchronized; SELECT * FROM [dbo]. [MyTable] UPDATE [dbo]. [MyTable] set name = 'master' delete top (1) FROM [dbo]. [MyTable] -- Subject: manual master-slave switchover USE [master] go alter database DBName set partner failover; GO

Manually stop the service of the subject. You can see that the "image" is changed to "subject"

When the service of the original subject is started, it is changed to an image ". (You can also switch it back to the subject)

[7. Related Scripts]

Select * from sys. certificatesselect * from sys. endpointsselect * from sys. database_mirroring_endpointsselect * from sys. database_sorting ing select * from sys. database_mongoing_witnesses USE master; alter database [DBName] set safety full; -- SET to high security mode alter database [DBName] set partner resume; -- restore the image alter database [DBName] set partner failover; -- Switch master-slave alter database [DBName] set partner FORCE_SERVICE_ALLOW_DATA_LOSS; -- force restore the image alter database [DBName] set online; -- ONLINE DATABASE

[8. Problem]

/* The above problems may occur: [problem] the problems occurred when the image was started (Note: The above script is correct. Here is an error case ): alter database [DBName] set partner = 'tcp: // 192.168.1.10: 8080' the following error message is displayed: Message 5023, level 16, status 1, 1418 server network address "TCP: // 192.168.1.10: 5023 "cannot be accessed or does not exist. Check the network address name and whether the port on the local and remote endpoints is running properly. [Problem] the IP address is incorrect. Change 192.168.1.10 to 192.168.2.10 and then execute: USE master; alter database [DBName] set partner = 'tcp: // 192.168.2.10: 100' GO. The error message is as follows: the Database "DBName" has been enabled for the database image ". [Solution] Stop the image and reconnect to alter database [DBName] set partner off; goalter database [DBName] set partner = 'tcp: // 192.168.2.10: 5022 'go [problem] An error occurred while connecting to the image: (both the ip address and port can be connected to) alter database [DBName] set witness = 'tcp: // 192.168.2.11: 5022 '. The error message is as follows: message 1456, level 16, status 3, 1st lines cannot send the alter database command to the remote server instance 'tcp: // 192.168.2.11: 8080 '. The database image configuration has not been changed. Make sure that the server is connected and try again. [Solution] AUTHENTICATION was granted for Windows at that time, and changed to certificate create endpoint [Endpoint_For_Mirror]… AUTHENTICATION = CERTIFICATE */