Useful queries while troubleshooting Database Mirroring

Useful queries while troubleshooting Database Mirroring

Query to check the associated ports with DB Mirroring
[sourcecode language="sql"]
SELECT type_desc, port FROM sys.tcp_endpoints;
GO
[/sourcecode]

Query to check the state of the DB Mirroring
[sourcecode language="sql"]
SELECT state_desc FROM sys.database_mirroring_endpoints
GO
[/sourcecode]

Query to check the service account connect permission on the DB Mirror endpoints
[sourcecode language="sql"]
SELECT 'Metadata Check';
SELECT EndPnt.name, SvrPerm.STATE,
CONVERT(nvarchar(38), suser_name(SvrPerm.grantor_principal_id))
AS GRANTOR,
SvrPerm.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SvrPerm.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SvrPerm, sys.endpoints EndPnt
WHERE SvrPerm.major_id = EndPnt.endpoint_id
ORDER BY Permission, grantor, grantee;
GO
[/sourcecode]

Query to check the DB Mirror timeout and resetting the DB Mirror timeout
[sourcecode language="sql"]
SELECT mirroring_connection_timeout
FROM
sys.database_mirroring
GO

ALTER DATABASE SQLDBPOOL SET PARTNER TIMEOUT 15
GO
[/sourcecode]

How many databases can be mirrored on a single instance of Microsoft SQL Server?

It is frequently asked by the DBAs or SystemAdmins or Customer that how many databases can be mirrored on a single instance of Microsoft SQL Server?

Answer of the above question is you can configure 10 databases for 32-bit operating system.On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.

For 64-Bit Operating system you can mirror more than 10 databases depending on the number of processors and worker threads. Many company has deployed more that 10 Databases as mirrored.

Database Mirroring Vs Log Shipping

Please read the below table to find out the diffrence between mirroring and log shipping.

Database Mirroring	Log-shipping
Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance.  Database mirroring can operate synchronously or asynchronously.	Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s).  Log shipping supports an unlimited number of secondary’s for each primary database.
Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.	Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule
Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level	Log-shipping can work on database and server level. You can configure multiple databases in logshipping
Data Transfer:    Individual T-Log records are transferred using TCP endpoints Transactional Consistency:  Only committed transactions are transferred Server Limitation:   Can be applied to only one mirror server Failover:   Automatic Failover Duration:  Failover is fast, sometimes < 3 seconds but not more than 10 seconds Role Change:   Role change is fully automatic Client Re-direction:  Fully automatic as it uses .NET 2.0/.Net 3.0	With Log Shipping: Data Transfer:    T-Logs are backed up and transferred to secondary server Transactional Consistency:  All committed and un-committed are transferred Server Limitation:   Can be applied to multiple stand-by servers Failover:   Manual Failover Duration:  Can take more than 30 mins Role Change:   Role change is manual Client Re-direction:  Manual changes required
Support only full recovery model	Supports full and bulk-logged recovery model
Mirror database is always in recovery mode. To read it you have use database snapshot.	You can use the stand-by option to read the database on standby server
Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages.	Not supported