Mirroring the database in MS SQL Server 2005/2008

Автор:   Мукан Андрей, mukan@andriy.co

The basic principle used in the mirrored database - this link between two instances of MSSQL server (as a rule, they are two physically server located in a local network or the Internet) and maintenance complete identity of the database on both servers. One of the servers acts as the master, to him connected database users. The second server is a mirror and not supported in this state connections to the database. The main server for changes to the database delivers a mirror with a transaction log by which second base, the mirror is always identical to the core, the main base. Mirroring allows you to save performance in a database failure of one of the database server.

Mirroring supports 3 modes:

  • Asynchronous mode - changes in the core database delivered to the mirror does not instantly, with the main server able to continue working without waiting for confirmation on the implementation of transactions of the mirror. Minus the decision is obvious - if sudden failure of the core and the transition to the mirror server role principal may lose some data. Plus - do not fall into of performance.
  • Synchronous mode - changes in the underlying database after each transaction, delivered in the transaction log mirror server. The main server is ready to continue working after successful changes to the database on both servers. minus solutions - a significant drop in performance when communication between servers through the slow channels of communication (the Internet), local networks and low load performance drop is insignificant. advantage of this solutions - a complete identity of the database on each server in any time
  • Protected synchronous mode - change server roles on failure main can be performed automatically, using the third Server-observer. Negative decisions - should be used optional third server, plus - complete identity database principal and mirror servers, as well as automatic management of its status on each server.

Changing roles can be performed manually in the interface of SQL Managemebt Studio or in the script - see below. When you change roles mirror server is principal and is ready to accept connections of clients. The main server after disaster recovery becomes the mirror.

The article describes how to configure database mirroring in asynchronous mode, with manual switching of roles and recovery after failures.

Pretreatment

  • We have 2 servers with installed MSSQL Server 2008. function Mirroring the beginning of steady work in SQL Server 2005 after install SP4, so those who have installed SQL Server 2005 need to download and install this manufacturer service pack. Author of the article managed to set up mirroring on SQL Server 2005 SP4, however, seems to be exactly the same conditions a similar adjustment for other pairs of servers do not always lead to success, hard work crowning mistake MSSQLServer_1418 (description below). On SQL Server 2008, the success rate higher.
  • Address the main server - 192.168.1.11, address mirror - 192.168.1.12. Name of the database on the master server - TCU_DB
  • We use the authenticated connection to the certificates as the simplest method of authentication in the Internet environment. In LAN can be configured through the SQL interface mirroring Server Management Studio (Task -> Mirror) on Windows or SQL Server authentication. Read the Use Certificates for Database Mirroring
  • Open the firewall for both incoming and outgoing TCP port 5022. Also open ports TCP / UDP 1433-1434. Activate the SQL Server Configuration Manager Named Pipes protocol
  • Perform in SSMS (New Query for each block scripts) scripts from tables in order, in strict sequence. For each server Take the scripts from any appropriate column. performance of each Block scripts should end like this message "Command (s) completed successfully". If as a result of scripts you see red text message, it means that any errors.
  • At the core server in SQL Server Management Studio, right-click to database TCU_DB -> Properties -> Options -> Recovery model: Full -> OK

Setting up communication servers and databases

MAIN (PRINCIPAL). 192.168.1.11 MIRROR (MIRROR). 192.168.1.12
1. Create milestones and certificates in folders C: \ Certs on principal and mirror servers
  1. --> This is the setup of the PRINCIPAL (master) in a mirroring environment
  2. USE master
  3. GO
  4. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
  5. GO
  6. --> Create a name not using "master" or "principal", remember: the roles can
  7. --  switch but the certificates remain the same.
  8. CREATE CERTIFICATE SQLSrv1_mirroring_cert
  9.   WITH SUBJECT = 'SQLSrv1 certificate',
  10.    START_DATE = '2011-01-01 00:00:00',
  11.    EXPIRY_DATE = '2030-01-01 00:00:00'
  12. GO
  13. CREATE ENDPOINT Mirroring_Endpoint
  14.   STATE = STARTED
  15.   AS TCP (
  16.    LISTENER_PORT = 5022,
  17.    LISTENER_IP = ALL)
  18.   FOR DATABASE_MIRRORING (
  19.    AUTHENTICATION = CERTIFICATE SQLSrv1_mirroring_cert,
  20.    ENCRYPTION = REQUIRED ALGORITHM AES,
  21.    ROLE = ALL)
  22. GO
  23. --> Backup certificate and transfer to SQLSrv2
  24. BACKUP CERTIFICATE SQLSrv1_mirroring_cert TO FILE = 'C:\Certs\SQLSrv1_mirroring_cert.cer';
  25. GO
  1. --> This is the setup of the MIRROR (slave) in a mirroring environment
  2. USE master
  3. GO
  4. --> Make sure a master key is set
  5. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
  6. GO
  7. -->
  8. CREATE CERTIFICATE SQLSrv2_mirroring_cert
  9.   WITH SUBJECT = 'SQLSrv2 certificate',
  10.    START_DATE = '2011-01-01 00:00:00',
  11.    EXPIRY_DATE = '2030-01-01 00:00:00'
  12. GO
  13. CREATE ENDPOINT Mirroring_Endpoint
  14.   STATE = STARTED
  15.   AS TCP (
  16.    LISTENER_PORT = 5022,
  17.    LISTENER_IP = ALL)
  18.   FOR DATABASE_MIRRORING (
  19.    AUTHENTICATION = CERTIFICATE SQLSrv2_mirroring_cert,
  20.    ENCRYPTION = REQUIRED ALGORITHM AES,
  21.    ROLE = ALL)
  22. GO
  23. --> Backup certificate and transfer to SQLSrv1
  24. BACKUP CERTIFICATE SQLSrv2_mirroring_cert TO FILE = 'C:\Certs\SQLSrv2_mirroring_cert.cer';
  25. GO
2. Exchange certificates between servers. On each server, should be 2 certificate in the folder C: \ Certs
Create usersthat are bound to certificates
  1. USE master
  2. GO
  3. --> Create a login for the other server process
  4. CREATE LOGIN SQLSrv2_mirroring_login WITH PASSWORD = 'password';
  5. GO
  6. --> Create a user for the new login
  7. CREATE USER SQLSrv2_mirroring_user FOR LOGIN SQLSrv2_mirroring_login;
  8. GO
  9. --> Associate the certificate with the user.
  10. CREATE CERTIFICATE SQLSrv2_cert
  11.   AUTHORIZATION SQLSrv2_mirroring_user
  12.   FROM FILE = 'C:\Certs\SQLSrv2_mirroring_cert.cer'
  13. GO
  14. --> Grant the user access to the endpoint
  15. GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLSrv2_mirroring_login]
  16. GO
  1. USE master
  2. GO
  3. --> Create a login for the other server process
  4. CREATE LOGIN SQLSrv1_mirroring_login WITH PASSWORD = 'password'
  5. GO
  6. --> Create a user for the new login
  7. CREATE USER SQLSrv1_mirroring_user FOR LOGIN SQLSrv1_mirroring_login;
  8. GO
  9. --> Associate the certificate with the user.
  10. CREATE CERTIFICATE SQLSrv1_mirroring_cert
  11.   AUTHORIZATION SQLSrv1_mirroring_user
  12.   FROM FILE = 'C:\Certs\SQLSrv1_mirroring_cert.cer'
  13. GO
  14. --> Grant the user access to the endpoint
  15. GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLSrv1_mirroring_login]
  16. GO
Create a Full Backup of the database in the root directory C: \
  1. --> Create a FULL BACKUP of each database to be mirrored
  2. BACKUP DATABASE TCU_DB TO DISK = 'c:\TCU_DB_backup.bak'
5. We transfer the resulting backup of the database with the master server to mirror, the root of drive C: \
6. Restore database from backup (if the position database files on mirror is different from the location of database files on the main you can use the interface SSMS, which indicate the position database files or use the script "MOVE", for example "RESTORE DATABASE [TCU_DB] FROM DISK = 'C:\TCU_DB_backup.bak' WITH NORECOVERY, MOVE N'TCU_DB' TO N'D:\SQL\TCU_DB.mdf', MOVE N'TCU_DB_log' TO N'D:\SQL\TCU_DB_log.ldf'"):
  1. USE MASTER
  2. GO
  3. --> Copy a backup of the database(s) to be mirrored
  4. --> Restore them using NORECOVERY
  5. RESTORE DATABASE [TCU_DB]
  6.   FROM DISK = N'C:\TCU_DB_backup.bak'
  7.   WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
  8. GO
7. Start the mirroring on the mirror:
  1. USE MASTER
  2. GO
  3. --> Set up partnering between the mirror and the principal (use a fully qualified name or an IP address)
  4. ALTER DATABASE TCU_DB SET PARTNER ='tcp://192.168.1.11:5022'
  5. GO
8. Starting mirroring at the main:
  1. USE master
  2. GO
  3. --> Restore these databases to the MIRROR using NORECOVERY
  4. --> Set up partnering between the mirror and the principal (use a fully qualified name or an IP address)
  5. --> Set up partnering between the principal and the mirror (use a fully qualified name or an IP address)
  6. ALTER DATABASE TCU_DB SET PARTNER ='tcp://192.168.1.12:5022'
  7. GO

Note 1:

If after step 8, you get a message "Msg 1416, Level 16, State 31, Line 1 Database "TCU_DB" is not configured for database mirroring. ", it is necessary to repeat the performance, paragraphs 7 and 8, but with a minimum interval of time between them.

Note 2:

If after step 8, you get a message "Error: 1478. The mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. " or " Msg 1412, Level 16, State 0, Line 4 The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log. ", you should make a backup of the database log on master server, followed by a recovery in the mirror:

MAIN (PRINCIPAL). 192.168.1.11 MIRROR (MIRROR). 192.168.1.12
  1. BACKUP LOG [TCU_DB] TO DISK = N'C:\TCU_DB_transaction_log_backup.trn' WITH NOFORMAT, NOINIT, NAME = N'TCU_DB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
  2. GO
Transfer the resulting backup log from the master server to mirror, the root of drive C: \
  1. RESTORE LOG [TCU_DB] FROM DISK = N'C:\TCU_DB_transaction_log_backup.trn' WITH FILE = 1,  NORECOVERY, NOUNLOAD, STATS = 10
  2. GO

Then repeat the performance of paragraphs 7 and 8

Note 3:

If after step 8, you get a message "The server network address "TCP: / / 192.168.1.12:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. ", then check availability of the mirror server at this address, open the TCP port 5022 in the firewall listening on that port on the mirror and the main servers using the netstat-abn , as well as try connect to this port from one server to another using command telnet 192.168.1.11 5022 and telnet 192.168.1.12 5022 .

Additional links for solving possible problems:

Change of server roles. Restoring a database on the mirror

  • Changing roles (main <-> Mirror):
    • Or in SQL Server Management Studio on the main server right click on a database TCU_DB -> Task -> Mirror -> Failover
    • Or script:
      1. ALTER DATABASE TCU_DB SET PARTNER FAILOVER
  • Restoring a database on the mirror after the failure of the main. When restoring data loss is allowed, which was discussed in beginning of the article, so be careful and cautious:
    • When you perform an authoritative restore, mirror base becoming a major and be able to receive connections from clients:
      1. ALTER DATABASE TCU_DB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    • The former main base after restoration will be automatically mirror, but will expect the team to continue mirroring. To do this script in SSMS:
      1. ALTER DATABASE TCU_DB SET PARTNER RESUME
  • In case of refusal mirrored database after recovery to take any action is not necessary - the base is synchronized with the master in the shortest time

An example of a sequence of actions in a typical situation

Situation: The primary (main) server powered off or out of system. Want to connect clients to the backup database (mirrored) database

Action to connect to the standby database:

  • Connecting to the server in the mirror and to restore mirror database to execute the script SSMS:
  • ALTER DATABASE TCU_DB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
  • Restart SQL Server Services
  • Base should have a status (Principal, Synchronized). If the base has a status of "In Recovery" and it did not change after restarting the service MSSQL, you should restart your computer. If the database status "Principal, Disconnected", you may be difficulties in posleduschem return to the usual work. This question is not fully understood, may be a supplement to this article. So far had fully recover mirroring from the beginning.
  • Clients can work with the database on the mirror server, which has now become the main

After repair and (or) the inclusion of the former primary server:

  • Connect to it and open the SSMS:
  • The base has a status (Mirror, Suspended / Restoring...)
  • Execute the script:
  • ALTER DATABASE TCU_DB SET PARTNER RESUME
  • Get the result- Command(s) completed successfully.
  • Restart SQL Server Services
  • Base should have the status(Mirror, Synchronized / Restoring...)
  • Use SSMS monitoring tool for mirroring databases (such as - described below) to make sure that this instance synchronized with the main base. It takes some time to changes in the main base were transferred to the mirror.
  • Go to the mirror server (which is currently is important):
  • The base has a status (Principal, Synchronized)
  • Make a backup database just in case (optional).
  • Change the server role. To do this, execute the script:
  • ALTER DATABASE TCU_DB SET PARTNER FAILOVER
  • Database status becoming (Mirror, Synchronized / Restoring...)
  • Go to the former principal, who again became the main, database status- (Principal, Synchronized)

Clients can work with the database on the master server so same as before the emergence of this situation.

Conclusion

SQL Server Management Studio provides a tool for monitoring mirroring and synchronization on the server. To do this, SSMS mirror server right-click the mouse on the database TCU_DB -> Tasks -> Launch Database Mirroring Monitor -> Register mirrored database

If you have a need to clean the database created from certificates, and users logins, as well as in cases where need to start setting up again, run the SQL Server Management Studio the following code:

  1. USE MASTER
  2. GO
  3. IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SQLSrv2_cert')
  4. DROP CERTIFICATE SQLSrv2_cert
  5. GO
  6. IF EXISTS (SELECT 1 FROM sys.sysusers WHERE name = 'SQLSrv1_mirroring_user')
  7. DROP USER SQLSrv1_mirroring_user
  8. GO
  9. IF EXISTS (SELECT 1 FROM sys.sysusers WHERE name = 'SQLSrv2_mirroring_user')
  10. DROP USER SQLSrv2_mirroring_user
  11. GO
  12. IF EXISTS (SELECT 1 FROM sys.syslogins WHERE name = 'SQLSrv1_mirroring_login')
  13. DROP LOGIN SQLSrv1_mirroring_login
  14. GO
  15. IF EXISTS (SELECT 1 FROM sys.syslogins WHERE name = 'SQLSrv2_mirroring_login')
  16. DROP LOGIN SQLSrv2_mirroring_login
  17. GO
  18. IF EXISTS (SELECT * FROM sys.endpoints WHERE name = 'Mirroring_Endpoint')
  19. DROP ENDPOINT Mirroring_Endpoint
  20. GO
  21. IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SQLSrv1_mirroring_cert')
  22. DROP CERTIFICATE SQLSrv1_mirroring_cert
  23. GO
  24. IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SQLSrv2_mirroring_cert')
  25. DROP CERTIFICATE SQLSrv2_mirroring_cert
  26. GO
Note: This article was obtained through a machine translation. We would appreciate any comments.

Статья включена в следующие темы:

Вверх