Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats

    • 7,339,518 Views

Archive for June 19th, 2009

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 »

Installing SP3 on SQL Server 2005 cluster

Posted by decipherinfosys on June 19, 2009

A good post by Sander Stad on SQLServerCentral on preparing SQL Server 2005 in a clustered environment for SP3 – here.  We are scheduled to do that in a couple of weeks for a client and were researching any known issues faced by other folks – this article would be very helpful when we start testing it in our lab next week.

Posted in SQL Server | Leave a Comment »

Some more free training materials from MSFT

Posted by decipherinfosys on June 19, 2009

Windows 7 Introduction – here.

This is an older one but a very good one for those looking for an introduction to SSIS 2008 – here.

Windows 2008 Fundamentals – here.

Silverlight Fundamentals – here.

Windows Server 2008 R2, new features – here.

Posted in .Net Development, SQL Server, Technology, Windows | Leave a Comment »

Can I capture a select statement without a trace?

Posted by decipherinfosys on June 19, 2009

This was the question asked by one of our readers:

Hi! Thanks for the wonderful posts on your blog.  I have learnt a lot from the different articles that you guys put out.  I am an Oracle DBA who for the past couple of months have been doing SQL Server DBA work as well.  In Oracle, using FGA (Fine Grained Auditing), I can easily capture a select statement.  In SQL Server, the only way that I have seen so far is to do it via a profiler trace.  Is there any functionality that I am not aware of which will give me the same results?

The answer to this question is Yes – a feature in SQL Server 2008 called “SQL Server Audit” allows us to do audits for the select statements as well but prior to SQL Server 2008, the only way to capture it was using a profiler trace.  Before we start digging into this capability, here is a previous blog post from us in which we had covered the auditing capabilities in Oracle – here and the ones in SQL Server – here.

Let’s see how we can implement this feature.  But before we start, please be aware that this feature is available only in Enterprise Edition.

First, we have to create a server audit object using the “Create Server Audit” command.   This operation needs to be performed in the master database:

USE MASTER
GO
CREATE SERVER AUDIT AUD_DEC_TEST TO FILE(FILEPATH=’C:\DecipherTest\AuditDemo\’)
GO

Now, the next step is to create a “Database Audit Specification” in the database in which we want the monitoring to be done.

USE DECIPHERTEST
GO
CREATE DATABASE AUDIT SPECIFICATION DEC_TEST_AUD FOR SERVER AUDIT AUD_DEC_TEST
ADD (SELECT ON dbo.EMP_ACTIVITY  BY Public)
GO

So, we have now created a database audit specification with a select on the employee activity table and assigned it to the instance level audit object.  As far as the audit groups and the different actions are concerned, there are too many of them to cover here – refer to this MSDN article to get the entire list.

Now, one thing that you need to be aware of that when you create the server audit object and the database audit specification, it is disabled by default which is the right thing since it needs to be a conscious action to put an audit like this in an enabled state.  When we were playing it with first, we had no idea that was the case and could not figure out what the issue was till we started reading the BOL.  So, be aware of it.  You can look at the status using the is_state_enabled column of sys.server_file_audits for the server audit object and sys.database_audit_specifications for the database audit specification.

Let’s go ahead and enable these now:

USE MASTER
GO
ALTER SERVER AUDIT AUD_DEC_TEST WITH (STATE=ON)
GO
USE DECIPHERTEST
GO
ALTER DATABASE AUDIT SPECIFICATION DEC_TEST_AUD WITH (STATE=ON)
GO

So, now that it is enabled, how can we go about reading the data?  We can use the in-built function fn_get_audit_file.  When you look into the file for the first time without it having captured any data, you will see a record which will be for enabling the audit.  Now, let us first run a couple of select statements against our table:

select * from dbo.EMP_ACTIVITY            /*Ran it twice from different sessions*/
select * from dbo.EMP_ACTIVITY WHERE EMP_MASTER_ID = 20

We would expect to see 4 records now when we execute the function – one for the event enabling, 2 for the first statement and 1 for the statement with the filter condition.

SELECT statement, *
FROM fn_get_audit_file (‘C:\DecipherTest\AuditDemo\*’,NULL, NULL)

And here is the abridged output:

Audit_Select

See, how easy it is to capture it now in SQL Server 2008?  Be aware though about the overhead that this might cause on your servers – we have not done any benchmarks (yet) to see what the impact might be…will post the results when we do.  Read up on the technet article mentioned in the references section below to learn more about this wonderful feature in SQL Server 2008.

Have a great weekend guys.

Resources:

  • TechNet article – here.
  • Audit Action Groups and Actions – here.

Posted in SQL Server | 4 Comments »