Skip to main content

Database mirroring states and Transition

Database states for each server are kept during the database mirroring session, recorded on each partner server, and reported by the sys.database_mirroring catalog view. The mirroring_state column returns a number for the state, and the mirroring_state_desc column returns the descriptive name for the state. State information about the witness is also reported from the same catalog view.

In addition to the states reported for each database, there are three phrases that are useful in describing the servers and databases involved in database mirroring.

1. Exposed - The data on the principal is exposed when it is processing transactions but no log data is being sent to the mirror. When a principal database is exposed, it is active with user connections and processing transactions. However, no log records are being sent to the mirror database, and if the principal should fail, the mirror will not have any of the transactions from the principal from the point the principal entered the exposed state. Also, the principal's transaction log cannot be truncated, so the log file will be growing indefinitely.

2. Cannot serve the database - When a principal server does not allow any user connections to the database and any transactions to be processed. When a witness has been set, if the principal server cannot form a quorum with another server, it will stop serving the database. It will not allow user connections and transactions on the principal database, and will disconnect all current users. As soon as it can form a quorum again, it will return to serving the database.

3. Isolated - A server is isolated when it cannot contact any of the other servers in the database mirroring session, and they cannot contact it. A server may be operational but communication lines are down between it and both other servers in the database mirroring session. In that case, we'll call the server isolated. If a witness has been set, then, if the principal server becomes isolated, it will no longer be able to serve the database, because there is no server in the session with which it can form a quorum.

When safety is FULL, the principal first enters the SYNCHRONIZING state and as soon as it synchronizes with the mirror, both partners enter the SYNCHRONIZED state. When safety is OFF, the partner databases start with the SYNCHRONIZING state. Once the mirror has caught up, the state goes to SYNCHRONIZED and stays there regardless of how far behind it is. For both safety settings, if the session is paused or there are redo errors on the mirror, the principal enters the SUSPENDED state. If the mirror becomes unavailable, the principal will enter the DISCONNECTED state.


In the DISCONNECTED and SUSPENDED states:
* When a witness has been set, if the principal can form a quorum with the witness or mirror server, the principal database is considered exposed. That means the principal database is active with user connections and processing transactions. However, no log records are being sent to the mirror database, and if the principal should fail, the mirror will not have any of the transactions from the principal from the point the principal entered that state. Also, the principal's transaction log cannot be truncated, so the log file will be growing indefinitely.
* When a witness has been set, if the principal cannot form a quorum with another server, it cannot serve the database. All users will be disconnected and no new transactions will be processed.
* When safety is OFF, the principal database is considered exposed, because no transaction log records are being sent to the mirror
Labels:

Comments

Post a Comment

Popular posts from this blog

Self Introduction and Responsibilities of a SQL Server DBA.

Hi, Self Introduction:                      I am Vang chew bigger , currently I am working with   Citronics , Here my role is SQL Server Database Administrator , I am having 3+ yrs Exp in SQL SERVER DBA with T-SQL, I have experience on SQL Server 2000, 2005 and 2008, I have a great experience on installation and configuration of SQL SERVER versions, and also applying the patches, hot fix, services packs and RTM’s accordingly, involved in upgrading on in place and side by side as per client requirement, and also Migrating the database on SQL SERVER 2000 to 2005 and 2008, Solid experience on configuring and maintenance of High availability SQL Server solutions, including Log shipping, Database Mirroring, Replication(Transactional and Merge) and SQL Server Clustering., having experience on performance tuning on server level, database level and query level, Responsible for working with application developers in identifying,...
3 comments
Read more

Shrinking a SQL Server Log File with Database in Always ON Availability.

Shrinking a SQL Server Log File with Database in Always ON Availability.                    Once we had a situation in our production server that the database which is in Always ON Availability group has been grown to the maximum size.  Many of them say that we can't shrink the log file as the database is in Always ON and we should take the downtime to perform the operation. But we shrink-ed the database log file to 5 GB from 180 GB. Steps are as below: --To Check the Number of pages allocated by the LogFile. DBCC LOGINFO --To check the file sixe sp_helpdb [SQLPantry] --To check the Log file Size DBCC SQLPERF(LOGSPACE) The above statement will show the logspace of all db at present situation. --To check the Log_reuse_wait description of a log file. select log_reuse_wait_desc,* from sys.databases The above statement will show the status of the log whether it has to be backuped i.e., LogBackup, Nothing, Ac...
1 comment
Read more

SQL Server 2022 Features in Detail.

SQL Server 2022 Features in Detail. 1. Data Virtualization: Data virtualization in SQL Server 2022 enables you to access and query data from various sources as if they were part of a single database. This eliminates the need for complex data integration processes and allows for seamless querying of distributed data. 2. Machine Learning Services Enhancements: SQL Server 2022 offers enhanced integration with machine learning frameworks like TensorFlow and PyTorch. This enables you to train and deploy machine learning models directly within the database, bringing AI-driven insights closer to the data. 3. Improved Security: The Data Discovery and Classification capabilities in SQL Server 2022 help identify sensitive data and apply appropriate security measures. Always Encrypted technology ensures that sensitive data remains encrypted in various scenarios. 4. Big Data Clusters: Big data clusters allow you to manage and query large datasets across SQL Server, Hadoop, and Spark clusters. This...
Post a Comment
Read more