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]

Create a 32-bit DSN on 64-bit machine for SQL Server

Problem
During the install of an application on a 64-bit Windows machine, the application was unable to list the ODBC System DSN because it had been setup as a 64-bit DSN and the application needed a 32-bit system DSN for connectivity to SQL Server. In this tip, we cover how to create a 32-bit DSN on 64-bit machine.

Solution
http://www.mssqltips.com/sqlservertip/2498/create-a-32bit-dsn-on-64bit-machine-for-sql-server/

How to configure SQL to listen on Multiple Ports?

Problem
Recently I came across a situation where an existing production SQL Server default instance was configured to use static TCP/IP port 48030 and the default 1433 port was disabled. A number of database applications are hosted on the same default SQL instance and these applications are connecting to SQL Server through port 48030. As per a new business requirement we need to host a new application database on the same SQL instance, but the application is unable to connect to the SQL instance because it us hard corded to use the default port 1433. In this tip we walk through how to configure a SQL instance to listen on multiple TCP/IP ports.

Solution
For solution please check http://www.mssqltips.com/sqlservertip/2493/configuring-sql-server-to-use-multiple-ports/ URL.