This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What are the basic steps to configure a Database Mirroring?
- Configuring security and communication between instances.
- Configuring endpoint.
- Creating logins for other servers service accounts.
- Grant connect permission to this logins on endpoints.
- Create mirror database by taking full and Transaction Log backup from principle server and restore it in mirror server with NORECOVERY.
- Establish mirroring session using ALTER DATABASE command.
What are the important performance monitor counters for Database Mirroring?
For Principal server:
- Log Bytes Sent/sec: Number of bytes of the log sent to the mirror per second.
- Log Send Queue KB: Total kilobytes of the log that have not yet been sent to the mirror server.
- Transaction Delay: Delay (in milliseconds) in waiting for commit acknowledgement from the mirror.
- Log Bytes Flushed/sec: The rate at which log records are written to the disk.
- Disk Write Bytes/sec: The rate at which the disk is written to.
For Mirror Server:
- Redo Bytes/sec: Number of bytes of the transaction log applied on the mirror database per second.
- Redo Queue KB: Total kilobytes of hardened log that remain to be applied to the mirror database to roll it forward.
T-SQL Script to find basic information about the instances which are involved in Database Mirroring.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT DB_NAME(database_id) AS 'DatabaseName' , mirroring_role_desc , mirroring_safety_level_desc , mirroring_state_desc , mirroring_safety_sequence , mirroring_role_sequence , mirroring_partner_instance , mirroring_witness_name , mirroring_witness_state_desc , mirroring_failover_lsn FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL; |
T-SQL Script to find information about the witness server.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Database_name , safety_level_desc , safety_sequence_number , role_sequence_number , is_suspended , is_suspended_sequence_number , principal_server_name , mirror_server_name FROM sys.database_mirroring_witnesses; |
What are the limitations of Database Mirroring?
- Limited number of databases can be mirrored per SQL instance.
- Mirrored database cannot be queried, backed up, or basically touched in any way (snapshot can be taken however)
- Only supports one to one server pairings.
- Cannot mirror a database more than once.
- Synchronous mirroring is intolerant to latency and bandwidth constraints (Asynchronous mirroring has no problems with latency or bandwidth).
What is alternative of Database Mirroring after SQL Server 2012?
Microsoft has now declared the database mirroring feature to be deprecated. Users of SQL Server are advised not to use this feature and not to develop new applications for it. As a substitute, Microsoft recommends AlwaysOn High Availability Groups.
Can we configure mirroring on 32 bit version or only on 64 version?
Yes, you can move SQL Server data back and forth between x64, x86, and IA64 architectures. The data and log files themselves do not store anything that indicates the architecture and work the same on either 32-bit or 64-bit.
Can we configure mirroring between 32 and 64 bit machine?
A database mirroring session can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.
Due to hard errors, What are the possible database mirroring failures?
- A broken connection or wire
- A bad network card
- A router change
- Changes in the firewall
- Endpoint reconfiguration
- Loss of the drive where the transaction log resides
- Operating system or process failure
- DNS is not working.
- Cables are unplugged.
- Microsoft Windows has a firewall that blocks a specific port.
- The application that is monitoring a port fails.
- A Windows-based server is renamed.
- A Windows-based server is rebooted.
Due to soft errors, What are the possible database mirroring failures?
- Network errors such as TCP link time-outs, dropped or corrupted packets, or packets that are in an incorrect order.
- A hanging operating system, server, or database state.
- A Windows server timing out.
- Insufficient computing resources, such as a CPU or disk overload, the transaction log filling up, or the system is running out of memory or threads.
T-SQL Script to to change time-out value.
|
1 |
ALTER DATABASE database_name SET PARTNER TIMEOUT 120; |
T-SQL Script to Resume or Pause the Database mirroring session.
Script to Pause:
Script to Resume:
What is High Protection operating mode?
It is pretty similar to High Availability mode except that Witness is not available, as a result failover is manual. It also has transactional safety FULL i.e. synchronous communication between principal and mirror. Even in this mode if the network is poor it might cause performance bottleneck.
What are End Points and its usages?
An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.
Which are the system stored procedure to monitor database mirroring?
- sp_dbmmonitoraddmonitoring
- sp_dbmmonitorchangemonitoring
- sp_dbmmonitorhelpmonitoring
- sp_dbmmonitordropmonitoring
Which are the other ways to monitor database mirroring?
Using profiler, performance counter, SQL Server Management Studio, Database Mirroring Monitor tool and SQL Server log.
