Systems Engineering and RDBMS

Archive for the ‘Protocols’ Category

Why does the IP address and subnet mask matter?

Posted by decipherinfosys on June 19, 2009

We had an interesting issue that relates to networking and SQL heartbeat with one of our clients that we would like to share with our readers.

One of our clients has their servers distributed across 3 data centers in US. Let us call them A, B and C. They have a VPN tunnel connecting all their sites together using Cisco ASA firewalls. Their IT staff access the entire infrastructure through one data center (A) which has remote access VPN enabled in the Cisco ASA. They recently reported an issue of not being able to access the servers in SQL cluster across remote access VPN located in data center (B). However, those servers are accessible from data center A, C and within B itself and others servers in data center B were accessible through remote access VPN.

As their remote VPN connection terminates at Cisco ASA at data center (A), various teams were involved to find out the cause. The following were checked to identify the cause of this issue:

1. It was ensured that the remote access VPN subnet (10.1.1.x) is added to the crypto-map on the site to site VPN configuration.
2. We also made sure all the servers are connected to the same switch and residing on same VLAN.
3. There was no specific access list (ACL) or firewall or IPS or F5 configuration that was blocking traffic to the database servers from remote access VPN subnet (10.1.1.x).
4. We ensured all the servers have the same IP default gateway configured.

During packet tracing, it is found that the traffic reaches to the ASA at data center A and also reaches data center B. It is found that the traffic was not going back from the servers to the remote access VPN subnet. We realized that there would be something wrong on the SQL cluster servers and started looking in depth on its network configuration. We identified that SQL server’s heart beat NIC was configured with the IP address of 10.0.0.x with a subnet mask of 255.0.0.0 (/8) allowing to have 16777214 hosts where only 2 IP addresses are needed for heart beat. So, all the incoming traffic from remote access VPN was forwarded to the heart beat NIC on the SQL servers and not going back to the remote access VPN ASA.

Having a subnet mask of 255.255.255.252 on SQL servers heart beat network would have allowed it to have only two IP addresses that are needed for heartbeat on the SQL cluster. As it is a production SQL network, we did not want to change the heart beat network’s IP address or subnet mask. As an alternative workaround, we used persistent route in Windows 2003 to configure the remote access VPN traffic to reach the correct NIC and gateway. A helpful article on windows 2003 routing can be found here. Once we added the persistent route, the remote access VPN users were able to access the SQL servers.

Lesson’s learned:

1. Make sure you aware of all the network addresses and subnets involved in all the locations.
2. Assign a subnet mask for the required number of host addresses.

Posted in Networking, Protocols, SQL Server, Technology | Leave a Comment »

MS SQL Server Protocols Documentation

Posted by decipherinfosys on January 17, 2009

Yesterday, MSFT released documentation on SQL Server protocols that are implemented and used in SQL Server 2008.  You can download it from here.

Posted in Protocols | Leave a Comment »

ODBC Tracing

Posted by decipherinfosys on January 17, 2009

Today morning, we got a call from one of our clients about a performance issue with one of their vendor applications.  After talking to the vendor, we started running the ODBC trace since this application was using an ODBC connection and we suspected that a recent upgrade of MDAC (Microsoft Data Access Components) on this system might have led to these issues.  The ODBC driver manager has tracing capabilities that help us record the function calls made by the vendor application and logs those into a log file.  The tracing is performed by a trace DLL that captures all the calls that get made between the vendor application and the driver manager as well as the driver manager and the driver.

So, how can we go about enabling the ODBC trace?  You can do it through the GUI or you can also do it via the registry.  Let’s take a look at both the approaches:

GUI:  On the ODBC Data Source Administrator, you will see a tab called Tracing…

odbc_trace

On the tracing tab, if you want to run a machine wide trace, then check the check box for “Machine-Wide tracing for all user identities” to help enable machine wide tracing.  The default is per user tracing.

The log file path name can be specified.  The tracing starts when you click on the button in this GUI to start the trace and it can be stopped from there itself.  The trace file contains a log of each ODBC function call that gets made along with the data types and the values for all of the arguments.  All input functions as well as returned functions along with the return codes and error states are logged.

Registry/ini file:

The other way to enable the ODBC tracing is by using the registry.  The registry entry for machine wide ini file setting is at:

HKLM\SOFTWARE\ODBC\Odbc.ini

and the one that is user specific is at this location:

HKCU\SOFTWARE\ODBC\Odbc.ini

Where HKLM: HKEY_LOCAL_MACHINE and HKCU: HKEY_CURRENT_USER

So what decides which key is being used?  In the ODBC GUI before you have seen that one can create a system data source or a user data source – system data source is available to all the users where as the user data source is available only to the current user.  The system data sources are under HKLM and the current user ones are under HKCU.  So, under Odbc.ini, you can set the Trace and TraceFile keywords and setting these enables/disables the tracing.

Through the application code:  If you are using ODBC in your application, through the API also you can have this functionality of enabling/disabling the tracing which can be made configurable as well as pretty granular.  This, in our opinion, provides much better instrumentation of the code.  One can do this by calling SQLSetConnectAttr and set the SQL_ATTR_TRACE attribute in the connection to SQL_OPT_TRACE_ON.  So, by doing this you would be enabling/disabling it for the connection duration.  When you want to turn it off, set it to SQL_OPT_TRACE_OFF.  SQL_ATTR_TRACEFILE is where you specify the name of the trace file.

Posted in Protocols | 1 Comment »