Database mirroring connection error

While setting up Database mirroring I came across an error which is too common on the web but too much of workaround and solutions.

Therefore I thought to write a blog post for the error. The problem we’re trying to solve is that you try to start database mirroring and you receive this error:

The server network address “‘TCP: //server FQDN Name:5022′” cannot be reached or does not exist

Database mirroring connection error 4 ‘an error occurred while receiving data: ’64(The specified network name is no longer available.)’.’ for ‘TCP: // server FQDN Name.com:5022’.

I assume that you have the following if not check each of the steps defined below.

1. Full Database Backup of the Database which will be used for DB Mirroring.
2. Restored the Backup on the Mirror Server in No Recovery mode.
   
3. Telnet the ENDPOINT (Port No: 5022 or as per requirement): Ports are opened at both the end (Principle and Mirror).

    

4. Check Endpoints have started on Principal and Mirror.
   SELECT * FROM sys.database_mirroring_endpoints
   SELECT name, port FROM sys.tcp_endpoints
5. Try setting up the DB Mirroring.
6. Start the mirroring.
7. Run netstat to see that end points have established a successful connection.

SQL Server 2008 Cluster installation on Windows 2003 Server.

Download the Attachment using the link to check how to install SQL Server 2008 on Windows 2003 Cluster

SQL Server 2008 Cluster installation on Windows 2003 Server

Query Plan -Part I

This post is about how to see or get compiled query plans. I thought of writing this blog to help  DBA’s who run around to seek help how to get compiled query plans and see which one is being used for their query.

SQL Server follows some basis rules to generate the Query Plan.
1. Complier generates Complied plans for your Query.
2. Next is it creates an Execution Plans based on Compiled Query Plans.
3. Query Execution Happens, While Query Execution starts the optimizer look for indexes or Stats for best use).
To get all Query Plans in XML Format .

SELECT convert(xml,query_plan)    FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
 WHERE text like ‘%with (index(IX_CDS_Campaign_Login_UserID),NOLOCK)%’

To get Query Plan in XML Format for a Specific Query or StoredProc

In case u have an OLTP Environment where hundreds of request are flowing  and u want to get the Query Pan for a specific query , in the where conduction of the below u can give some specific sort of text which is written in the query.

 SELECT convert(xml,query_plan)    FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
 WHERE text like ‘%with (index(IX_CDS_Campaign_Login_UserID),NOLOCK)%’

To get Query Plan for a Specific Query or StoredProc  or all Complied Plans

    SELECT text,query_plan,plan_handle    FROM sys.dm_exec_query_stats AS qs
             CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp

SELECT text,query_plan,plan_handle  FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
 WHERE text like ‘%with (index(IX_CDS_Campaign_Login_UserID),NOLOCK)%’

In case u have different plans for the same query then u need to look into the Query Execution Plan for the reason.

Will write about the reason why different query plans are generated for the same query and how it affects the performance of the Database and System.

Hope this post will be helpful.