Hi Folks,
Today I am going to discuss about the issue when setting up the mirroring
in SQL Server 2005 and 2008. It is very rare issue and this confirms that DBA should
have some good knowledge on other concepts like Windows Administration, Networking,
Storage and as well as Virtualization.
Let me explain the issue
I was struck up with some issues while implementing the DR for SQL Server 2005 to
SQL Server 2005, and SQL Server 2008 to SQL Server 2008.
Unfortunately I failed with same error for both the implementations.
Please find the details.:
SQL Server 2005 DR:
1) SQL Server 2005 Enterprise Edition with SP4 on principal and DR.
2) Windows 2003 with x64.
3) Firewall is blocked.
4) I used telnet and netstat and confirmed 5022 ports are open and able to communicate.
5) Database is in full recovery mode.
6) We have taken a full backup and restored it with NoRecovery on DR
and T-Log also applied with NORecovery.
7) when i try to start Mirror the below error i found
Same checks were performed from SQL Server 2008 to SQL Server 2008.
but SQL Server 2008 is runnning on Windows 2008.
As I mentioned earlier I have checked for Telnet, Netstat, FQDN and connectivity etc
from principal server to DR server. The issue what I am facing was not with any of the
above mentioned, Apart from TCP/ IP there are few other registry like TCP Chimney
were creating problem.
Resolution: TCP Chimney needs to be set to '0' by default but on the server
it was set to other value. so with the help of n/w team the issue was resolved.
Here is something about TCP CHIMNEY. Please make a note of this and use it
as a reference.
TCP Chimney Offload:
TCP Chimney Offload is a networking technology that helps transfer the workload
from the CPU to a network adapter during network data transfer. In Windows
Server 2008, TCP Chimney Offload enables the Windows networking subsystem to
offload the processing of a TCP/IP connection to a network adapter that includes
special support for TCP/IP offload processing
TCP Chimney Offload is available in all versions of Windows Server 2008 and
Windows Vista. Both TCP/IPv4 connections and TCP/IPv6 connections can be
offloaded if the network adapter supports this feature.
Courtesy: http://support.microsoft.com/kb/951037
Chimney might distract SQL Server Mirroring because if Principal and Mirror
have different configurations and network cards on each system behave differently,
then there can be an issue. The resolution for this is to disable TCP chimney and the
link for it is:http://support.microsoft.com/kb/942861
Another point is in Windows 2003 if we apply SP2, TCP Chimney option
is enabled by default and that leads to such network based issues and as Mirroring
is almost completely dependent on network and port numbers it gets affected.
Courtesy: http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx
you can find this option in the registry editor. Please find a screen shot of this
Today I am going to discuss about the issue when setting up the mirroring
in SQL Server 2005 and 2008. It is very rare issue and this confirms that DBA should
have some good knowledge on other concepts like Windows Administration, Networking,
Storage and as well as Virtualization.
Let me explain the issue
I was struck up with some issues while implementing the DR for SQL Server 2005 to
SQL Server 2005, and SQL Server 2008 to SQL Server 2008.
Unfortunately I failed with same error for both the implementations.
Please find the details.:
SQL Server 2005 DR:
1) SQL Server 2005 Enterprise Edition with SP4 on principal and DR.
2) Windows 2003 with x64.
3) Firewall is blocked.
4) I used telnet and netstat and confirmed 5022 ports are open and able to communicate.
5) Database is in full recovery mode.
6) We have taken a full backup and restored it with NoRecovery on DR
and T-Log also applied with NORecovery.
7) when i try to start Mirror the below error i found
Same checks were performed from SQL Server 2008 to SQL Server 2008.
but SQL Server 2008 is runnning on Windows 2008.
As I mentioned earlier I have checked for Telnet, Netstat, FQDN and connectivity etc
from principal server to DR server. The issue what I am facing was not with any of the
above mentioned, Apart from TCP/ IP there are few other registry like TCP Chimney
were creating problem.
Resolution: TCP Chimney needs to be set to '0' by default but on the server
it was set to other value. so with the help of n/w team the issue was resolved.
Here is something about TCP CHIMNEY. Please make a note of this and use it
as a reference.
TCP Chimney Offload:
TCP Chimney Offload is a networking technology that helps transfer the workload
from the CPU to a network adapter during network data transfer. In Windows
Server 2008, TCP Chimney Offload enables the Windows networking subsystem to
offload the processing of a TCP/IP connection to a network adapter that includes
special support for TCP/IP offload processing
TCP Chimney Offload is available in all versions of Windows Server 2008 and
Windows Vista. Both TCP/IPv4 connections and TCP/IPv6 connections can be
offloaded if the network adapter supports this feature.
Courtesy: http://support.microsoft.com/kb/951037
Chimney might distract SQL Server Mirroring because if Principal and Mirror
have different configurations and network cards on each system behave differently,
then there can be an issue. The resolution for this is to disable TCP chimney and the
link for it is:http://support.microsoft.com/kb/942861
Another point is in Windows 2003 if we apply SP2, TCP Chimney option
is enabled by default and that leads to such network based issues and as Mirroring
is almost completely dependent on network and port numbers it gets affected.
Courtesy: http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx
you can find this option in the registry editor. Please find a screen shot of this
Comments