All about SQL server mirroring
pre-request for SQL server mirroring
High-performance mode
Transaction safety
Witness state
OFF
NULL (no witness)2
High-safety mode without automatic failover
FULL
NULL (no witness)
High-safety mode with automatic failover1
FULL
CONNECTED
10) to check the mirror state
SELECT mirroring_safety_level_desc, mirroring_witness_name, mirroring_witness_state_desc FROM sys.database_mirroring
11) protection modes
high performance
high safety
high safety with automatic fail-over
12)Restrictions for DB mirroring:
Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.
A mirrored database cannot be renamed during a database mirroring session.
Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Database mirroring is not supported with either cross-database transactions or distributed transaction
Maximum 10 databases per instance can support on a 32-bit system.
b) Database mirroring is not supported with either cross-database transactions or distributed transactions.
Advantages of mirroring include automatic fixing of corrupted pages and storage is not a SPOF
13)What is End Point? How u create end point?
An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.
Creation of an end point:-
Create endpoint <endpoint name> State=started/stopped/disabled
as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)
14)What is the default of end points (port numbers) of principal, mirror and witness servers? How to find the Port numbers?
The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024.
To Find Port Number:- SELECT name, port FROM sys.tcp_endpoints
15)In which Recovery model we can use in Mirroring?
In mirroring the principal and mirror databases are used only full recovery model
16)What is the syntax to stop the Database Mirroring?
Alter database <database name> set partner off
17)How to monitoring Mirroring?
There are six methods are available for monitoring the Database Mirroring
a) Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.
To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.
b) SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.
c) SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.
d) Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.
e) Profiler:- Profiler many events are providing the status of the Database mirroring
f) System Stored Procedures:-
? sp_dbmmonitoraddmonitoring
? sp_dbmmonitorchangemonitoring
? sp_dbmmonitorhelpmonitoring
? sp_dbmmonitordropmonitoring
18)What are the Database Mirroring states?
1) SYNCHRONIZING:-
The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.
At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
2) SYNCHRONIZED:-
When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.
If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.
If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
3) SUSPENDED:-
The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.
A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session
SUSPENDED is a persistent state that survives partner shutdowns and startups.
4) PENDING_FAILOVER:-
This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.
When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
5) DISCONNECTED:-
The partner has lost communication with the other partner
Database mirroring provides protection at the database level, whereas a cluster solution provides protection at the SQL Server instance level
19)What are the Disadvantages of Database Mirroring?
Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.
Automatic server failover may not be suitable for application using multiple databases.
20)query to check mirroring status.
select mirroring_state,mirroring_state_desc,mirroring_role,mirroring_role_desc,mirroring_safety_level_desc,mirroring_partner_name,mirroring_witness_name from sys.database_mirroring;
pre-request for SQL server mirroring
1. mirroring available after 2005
2.
Primary Db should be in full recovery mode
3.
mirrored Db in recovery mode
4.
Primary & Mirrored Db server has same
version & same Sservic pack same version (either Standard or Enterprise)
5.
witness server has been anything sql
express also
6.
Server should be on same domain name. becoz it
communicates with AD
7.
you cannot use this as a secondary read-only
database to achieve scalability
8.
Database name same on both servers
9.
Operating mode
High-performance mode
Transaction safety
Witness state
OFF
NULL (no witness)2
High-safety mode without automatic failover
FULL
NULL (no witness)
High-safety mode with automatic failover1
FULL
CONNECTED
10) to check the mirror state
SELECT mirroring_safety_level_desc, mirroring_witness_name, mirroring_witness_state_desc FROM sys.database_mirroring
11) protection modes
high performance
high safety
high safety with automatic fail-over
12)Restrictions for DB mirroring:
Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.
A mirrored database cannot be renamed during a database mirroring session.
Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Database mirroring is not supported with either cross-database transactions or distributed transaction
Maximum 10 databases per instance can support on a 32-bit system.
b) Database mirroring is not supported with either cross-database transactions or distributed transactions.
Advantages of mirroring include automatic fixing of corrupted pages and storage is not a SPOF
13)What is End Point? How u create end point?
An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.
Creation of an end point:-
Create endpoint <endpoint name> State=started/stopped/disabled
as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)
14)What is the default of end points (port numbers) of principal, mirror and witness servers? How to find the Port numbers?
The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024.
To Find Port Number:- SELECT name, port FROM sys.tcp_endpoints
15)In which Recovery model we can use in Mirroring?
In mirroring the principal and mirror databases are used only full recovery model
16)What is the syntax to stop the Database Mirroring?
Alter database <database name> set partner off
17)How to monitoring Mirroring?
There are six methods are available for monitoring the Database Mirroring
a) Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.
To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.
b) SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.
c) SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.
d) Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.
e) Profiler:- Profiler many events are providing the status of the Database mirroring
f) System Stored Procedures:-
? sp_dbmmonitoraddmonitoring
? sp_dbmmonitorchangemonitoring
? sp_dbmmonitorhelpmonitoring
? sp_dbmmonitordropmonitoring
18)What are the Database Mirroring states?
1) SYNCHRONIZING:-
The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.
At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
2) SYNCHRONIZED:-
When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.
If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.
If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
3) SUSPENDED:-
The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.
A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session
SUSPENDED is a persistent state that survives partner shutdowns and startups.
4) PENDING_FAILOVER:-
This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.
When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
5) DISCONNECTED:-
The partner has lost communication with the other partner
Database mirroring provides protection at the database level, whereas a cluster solution provides protection at the SQL Server instance level
19)What are the Disadvantages of Database Mirroring?
Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.
Automatic server failover may not be suitable for application using multiple databases.
20)query to check mirroring status.
select mirroring_state,mirroring_state_desc,mirroring_role,mirroring_role_desc,mirroring_safety_level_desc,mirroring_partner_name,mirroring_witness_name from sys.database_mirroring;
No comments:
Post a Comment