Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to achieve the mirror function in SQLServer. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

I did not configure the result in the domain environment, perhaps because the domain user, because I did it in the production environment, to change the domain user needs to restart SQLServer, so this method is abandoned and can only be in the form of certificate. Environment: host: 192.168.10.2 (code A) Image: 192.168.10.1 (code B, for convenience for a moment) (limited conditions I did not have a witness server. ) SQLServer2005 on both servers first configure the host to execute the following SQL replication code on the host:-- create the host database master key USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; GO-- create a certificate on 10.2 for the database instance CREATE CERTIFICATE As_A_cert WITH SUBJECT =' As_A_cert', START_DATE = '09canplink 2011prime, EXPIRY_DATE =' 01and01and2099' GO-use the certificate created above to create a mirror endpoint for the database instance CREATE ENDPOINT Endpoint_As STATE = STARTED AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE As_A_cert, ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL); GO

Note: here we should pay attention to setting the mirror port of the database. 5022. -- back up the certificate on 10.2 and copy it to 10.1 BACKUP CERTIFICATE As_A_cert TO FILE ='D:\ As_A_cert.cer'; GO Note: back up certificate An and copy certificate A to mirror server B. The copy code for configuring the mirror server is as follows: USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; GO-- create a certificate for the database instance on 10.1B CREATE CERTIFICATE As_B_cert WITH SUBJECT =' As_B_cert', START_DATE = '09comp2Universe 2011, EXPIRY_DATE =' 01comp01and2099' GO-create a mirror endpoint CREATE ENDPOINT Endpoint_As STATE = STARTED AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE As_B_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL) for the database instance on 10.1B using the certificate created above; GO

-- back up the certificate on 10.1B and copy it to 10.2A BACKUP CERTIFICATE As_B_cert TO FILE ='D:\ As_B_cert.cer'; GO also copy the backed-up certificate B to server A. Set up an account for mirror login and execute it on A-- exchange certificates,-- synchronous Login replication code is as follows: CREATE LOGIN B_login WITH PASSWORD = 'password'; CREATE USER B_user FOR LOGIN login; CREATE CERTIFICATE As_B_cert AUTHORIZATION B_user FROM FILE =' D:\ As_B_cert.cer'; GRANT CONNECT ON ENDPOINT::Endpoint_Bs TO [B_login]

The copy code is executed on B as follows:-- Exchange certificates,-- synchronous Login CREATE LOGIN A_login WITH PASSWORD = 'password'; CREATE USER A_user FOR LOGIN As_A_cert.cer'; GRANT CONNECT ON ENDPOINT::Endpoint_As TO login; CREATE CERTIFICATE As_A_cert AUTHORIZATION A_user FROM FILE =' D:\ As_A_cert.cer'; GRANT CONNECT ON ENDPOINT::Endpoint_As TO [A_login]

Remember that the port 5022 of the two servers is not occupied, and make sure that the two servers can connect and perform the next steps. The mirror is half done. Next, the copy code of the Test library on server An is as follows:-- the host performs a full backup USE master; ALTER DATABASE Test SET RECOVERY FULL; GO BACKUP DATABASE Test TO DISK ='D:\ SQLServerBackups\ Test.bak' WITH FORMAT; GO BACKUP LOG Test TO DISK ='D:\ SQLServerBackups\ Test.bak'; GO

Copy the backup file to B. Be sure to perform a full backup. Complete welcome database on B server. There are a lot of problems here. One by one. If we directly execute the following SQL. The copy code is as follows: RESTORE DATABASE Test FROM DISK ='D:\ Back\ Test.bak' WITH NORECOVERY GO RESTORE LOG Test FROM DISK ='D:\ Back\ Test_log.bak' WITH FILE=1, NORECOVERY GO [code] may report message 3154, level 16, status 4, the database backup in line 1 backup set is different from the existing 'Test' database. Message 3013, level 16, status 1, line 1 may be caused by different backup set names of the two databases, looking for reasons for failure for a long time, so use the following sp_addumpdevice method to do it. Use sp_addumpdevice to build a restore device. This ensures that the backup file is the database of the data. [code] exec sp_addumpdevice 'disk','Test_backup',' E:\ backup\ Test.bak' exec sp_addumpdevice 'disk','Test_log_backup',' E:\ backup\ Test_log.bak' go

After success, we will complete the recovery copy code as follows: RESTORE DATABASE Test FROM Test_backup WITH DBO_ONLY, NORECOVERY,STATS; go RESTORE LOG Test FROM Test_log_backup WITH file=1, NORECOVERY; GO

Here, if the database has been backed up multiple times before, Ken will produce multiple backup sets. So the file here cannot be specified as 1. This error may be message 4326, level 16, status 1, line 1 the log in this backup set terminates at LSN 36000000014300001, the LSN is too early to be applied to the database. Newer log backups that contain LSN 36000000018400001 can be restored. You can use this sentence to query the backup set of the backup file restore headeronly from disk ='E:\ backup\ Test_log.bak' to find the last sequence number and assign it to file. It is also important to note that NORECOVERY needs to be specified on the first full restore. At this point, all the preparatory work has been completed. We begin to execute mirroring. First, execute ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022'; on the mirror server, then execute ALTER DATABASE Test SET PARTNER =' TCP://192.168.10.2:5022'; on the host, so that the mirrors of the two servers are synchronized.

Delete the mirror:

ALTER DATABASE Test SET PARTNER OFF

If there is a problem with the host, execute the copy code on the host as follows: USE MASTER Go ALTER DATABASE Test SET PARTNER FAILOVER Go

Summary: if the step in the middle of setting up an image goes abroad, be sure to delete what you delete when you need to re-execute it. -- query image select * from sys.endpoints-- Delete port drop endpoint Endpoint_As-- query certificate select * from sys.symmetric_keys-- Delete certificate Delete the certificate before deleting the primary key DROP CERTIFICATE As_A_cert-delete the primary key DROP MASTER KEY-delete the image alter database set partner off-delete the login drop login sp_addumpdevice syntax copy code is as follows: sp_addumpdevice [@ devtype =] 'device_type', [@ logicalname =]' logical_name', [@ physicalname =] 'physical_name'] where the parameter is: @ devtype: device type The supported values are disk and tape, where disk is a disk file Tape is any tape device supported by windows. Logicalname: the logical name of the backup device, the device name. @ physicalname: physical name and path of the backup device

The above is how to achieve the mirroring function in the SQLServer shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.