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

This article introduces the relevant knowledge of "what are the knowledge points of sqlserver about mirror mirroring". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The basic principle of mirror mirroring: the add, delete, modify and query operations performed by the principal database on the main server are redone in the mirror database of the mirror server by log method. After the principal database creates a mirror, it starts a separate transaction log sending thread, maintains a virtual send queue, then reads the transaction log, compresses it, and then sends it to the mirror node. After receiving it, the mirror node writes it locally to a redo queue file on disk, and then obtains the transaction log from the redo queue asynchronously by another thread. It is then distributed to the application thread (process unit) for playback.

Two modes of data mirroring

Synchronous mirroring operation: in transaction delivery, the master server must wait for the mirror server to return the message that the log was successfully received before the master server continues to write the next transaction log to disk and commit to the mirror server. This kind of mirroring does not cause data loss, but there is a transaction delay for mirroring operations.

Asynchronous mirroring operation: in transaction transfer, the master server does not wait for the receipt of the log returned by the mirror server, but continues to write a transaction log to the physical disk and submit it to the mirror server. This kind of mirror operation has high performance. However, the data of the mirror server may be lost when the master server goes down.

1. To build a mirror, you must first make a full backup and log backup of the master database, and restore the integrity and logs to the slave database by norecovery, and then right-click the database in the master database-> properties-- > mirror, and refer to the graphical interface to build it step by step. The following is the error of building mirror in the master database, because when restoring from the database, only the database is restored and the log is not restored.

The mirror database, "XX", 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.

2. In the process of building an image in the graphical interface, the pop-up endpoint name will be created automatically, and there is no need to create it manually. The pop-up endpoint name and port can be defined by yourself. The default is Mirroring and 5022.

3. After you have built the tool for monitoring mirror, right-click the database-> Tasks-- > Launch Database Mirroring Monitor. This tool has the same effect as the master library and slave library of log shipping. This is different from the fact that the Transaction Log Shipping Status master library of log shipping is only responsible for the master library and the slave database is only responsible for the slave library.

4. After the main library is built, the status display behind the main library (Principal,Synchronized), and the status display behind the secondary library (Mirror,Synchronized/Restoring..)

5. After building, both the master library and the slave library have a job named "Database Mirroring Monitor Job". Even if the mirror of the database is removed, the master-slave job is still there, and a new database mirror is added, the master / slave job is still the same, and no new job is added. After the job is deleted, even if there is a database mirror, the job will not be rebuilt automatically, but the job will be rebuilt the next time the database mirror is added.

6. Since you cannot read from the mirror database of the library, you can read it by creating a snapshot database from the library to determine whether the mirror is really synchronized.

7. If you want to achieve automatic master-slave switching, that is, automatic failover, you must have a witness server.

8. If the master library fails and the slave state changes to (Mirror,Disconnected/In Recovery), execute the following statement to restore the read and write state of the slave library (you must first execute the second statement to delete the snapshot of the slave library, otherwise the third statement cannot be executed)

ALTER DATABASE testdb SET PARTNER OFF

Drop database testdb_snapshot

RESTORE DATABASE testdb WITH RECOVERY

9. If the master database needs to delete the slave database and then re-build the image of the master database after removing the remove image, but the slave database is still displayed (Mirror,Disconnected/In Recovery), and the slave database cannot be deleted, and the status is still (Mirror,Disconnected/In Recovery) after performing ALTER DATABASE testdb SET PARTNER OFF from the slave database, you need to configure the image in the master database first, and then you will report that the image construction is not successful. This time from the library status display (In Recovery), from the library at this time can be deleted directly. If the slave library still cannot be deleted, configure the image in the master database first, and then report that the construction of the image is unsuccessful, and then restart the slave database instance. The slave database status is generally displayed as (suspect), and the slave library can be deleted directly.

10. Manual failover requires transaction security to be set to FULL, and manual failover is supported only when the partners are connected together and the database is synchronized, that is, when the database is in SYNCHRONIZED state. Log in to the main database and execute the following statement. The above 8 is not manual failover, because the database is not in synchronous state but Disconnected/In Recovery.

USE master

ALTER DATABASE testdb SET PARTNER FAILOVER

11. If the slave library starts to build the mirror after using the full standby and log backup of the master database for restore norecovery, but in the process, the mirror has not been built and the master database backs up the logs, and the mirror cannot be built successfully. The following error will be reported. Only by re-restore norecovery the logs backed up by the master database to the slave database can the mirror be built normally.

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

12. You cannot delete delete directly from the library, which is different from logshipping

13. Find the fault of the image. You can find relevant information from the logs of the master database and the slave database.

14. After the mirror database is upgraded, the database version COMPATIBILITY_LEVEL cannot be modified because mirror is read-only, and pausing mirror cannot be modified because the database is in restoring state

15. For more information about mirror, please refer to the view sys.database_mirroring.

16. The slave library of mirror cannot execute backup.

17. The level can be modified after the master database is upgraded, and the level cannot be modified after the slave database is upgraded. If the master database modifies the level, once the log of the master database is synchronized to the slave database, the level of the database corresponding to the slave database will be the same as the master database.

18. When datafile is added to the master library, datafile will also be added to the slave library, and the path is the same as that of the master library.

19. Files have been added to the mirror master library, but if there is no corresponding directory in the slave library, synchronization will be suspended by suspend, even if the slave library has default datafile and logfile paths. You can see the error message in the database log from the library: CREATE FILE encountered operating system error 3 (The system cannot find the path specified.) While attempting to open or create the physical file'e:\ XX\ YY.ndf'.

20. The database of FILETABLE cannot build mirror and will report an error A database cannot be enabled for both Database Mirroring and FILESTREAM or for both Database Mirroring and MEMORY_OPTIMIZED_DATA storage.

21. Encountered a strange error report Database 'mirror_1' cannot be opened while building mirror. It is in the middle of a restore . Finally, it is found that because the version of the database instance is 2016, and the SSMS version used when building mirror is 2017, the error will not be reported if you change SSMS to 2016. This can be regarded as the bug of sqlserver.

22. The snapshot database mirror_snapshot,NAME in which the mirror database mirror1 is created from the database must be equal to the same logical name of the data file in the main database. Filename defines it at will, which refers to the filename of the snapshot database.

Create database mirror_snapshot on

(NAME=mirror1,filename='G:\ DEFAULT.DATA\ mirror_snapshot')

As snapshot of mirror1

23. To monitor the recent synchronization of mirror database Db1, you can refer to the following statement (2 represents the last 4-hour row, if you change 2 to 1 for the last 2-hour row)

USE msdb

EXEC sp_dbmmonitorresults Db1,2, 0

24. The status of the master database has always been (Principal,Suspend). When you right-click "attribute"-"Mirroring--Resume" of the master library, the status of the master database is (Principal,Synchronizing), and after a while it is (Principal,Suspend). Check the log of the slave instance as follows:

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Solution: right-click the master database-attribute-Mirroring--Remove Mirroring, change the status of the main database to (Mirror,Disconnected/In Recovery), and then create a Mirror in the master database. An error will be reported and the status of the slave database becomes (restoring). At this time, the logs backed up by the master database are taken to the slave database to manually restore. After all the logs are restore, it is normal to create a Mirror in the master database.

25-and 8-core CPU, the largest memory 16GB environment in the database. It is found that when the log generated by Database1 reaches more than 500MB within 15 minutes, mirror is very likely to have the status of suspend. Check the log and find the following information. Even if you restart the database, you still can't solve this problem. You can only remove mirror, manually restore these logs from the database, and then build mirror.

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Database mirroring will be suspended. Server instance 'Instance1' encountered error 1204, state 4, severity 19 when it was acting as a mirroring partner for database' Database1'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

26. With regard to the choice of mirror and logshipping, the log generated by the database in a short period of time is very large, for example, if 500MB is generated within 15 minutes, then mirror is not as good as logshipping, because mirror consumes more memory, and mirror is prone to the state of suspend.

27. An error was encountered while building mirror: the server network address "TCP://dbalias:5022" cannot be accessed or does not exist. Please check the network address name and check whether the ports of the local and remote endpoints are running properly. (Microsoft SQL Server, error: 1418).

Solution idea

27.1. Whether telnet dbalias:5022 is connected or not

27.2. Check whether the sqlserver startup account of the master / slave machine has access to the other instance.

27.3. Restart the standby machine when both of the above points are normal.

27.4. If you have done the same in step 3 above and if it does not work, restart the host (you will encounter such a problem yourself, and you will not be able to build the mirror normally until the fourth step is completed)

28. Monitoring the status of mirror synchronous updates can be combined with stored procedure msdb.sys.sp_dbmmonitorupdate and system table msdb.dbo.dbm_monitor_data

This is the end of the introduction of "what are the knowledge points of sqlserver about mirror mirroring". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.