Networking

Why should the DBA care about Network Firewalls?

All to often I see people online asking some sort of question about connecting to their CoLo’ed SQL Server and they connect directly over the Internet.  This is nuts people.  If you can access your SQL Server via Management Studio from anywhere on the Internet so can people who would love to break into your SQL Server and use the machine for their own uses.

SQL Servers shouldn’t every be directly accessible from the Internet.  Even if you have to use public IPs to host the machines, make sure that there is a firewall setup between that server and the public Internet so that no one has any sort of direct access to the machine from outside of the data center.

How do you manage the SQL Server in this case?  You use the router’s built in functions to setup a point to point VPN with your office router so that you can securely communicate with the servers in the CoLo without sending that data in plain text over the Internet.

For that matter while you are locking down the SQL Server, suggest that the Web Servers be locked down as well.  The only ports that they should have open are 80 and 443 unless you are running streaming servers, or known FTP servers.

If your servers have been sitting exposed on the public Internet then I highly suggest that you install an Anti-virus on them and check for Viruses, malware, etc that’s doing stuff you don’t want it to be doing.

Several years ago I was doing some work for a company that had Windows 2003 servers sitting directly on the Internet without an Anti-Virus and with no firewall.  When I got to the machines and took them off the Internet for cleaning there were over 200 viruses on the machines that they had no idea were on there.  There complaint was that the machines were running slow, and network costs kept going up.  God only knows what sort of network traffic these viruses were generating as they did what ever it was they were trying to do.

Denny

Windows 7 and Cisco VPN Software

We use Cisco VPN Software to access the office from home, Starbucks, etc.

There are several threads on the subject on the net (I used this one).  There are a couple of workarounds which are mentioned including backing up the ndis.sys file and putting it back after the install but before the reboot.  (This didn’t work for me, my laptop still blue screened.)

Another fix was to run the installer in Administration mode (I opened a command prompt using the “Run As Administrator” from the right click) then ran the installer.  (This did work for me.)

One annoying thing was when I imported our PCF file the group password wasn’t imported.  I had to use a Cisco VPN Client Password Decoder website to get the password from the PCF file to put into the address book.  Everything worked fine after that (at least so far).

Hopefully someone else finds this useful.

Denny

My first day with Windows 7

Today was my first day using Windows 7 as my primary OS.  I took the time this afternoon to install Windows 7 and start getting everything set back up. Continued »

Decreasing Network Traffic and Network Latency When Using Replication

When dealing with a high load replication environment network latency and network traffic can become as issue. There are a few settings you can play with, within the subscription agents themselves such as the –PacketSize, -UseInprocLoader, and –SubscriptionStreams. While working with these settings may or may not help (personally I’ve had very limited success getting them to improve the data flow rate) there are some other techniques you can use.

The best technique to you is to include your stored procedures as articles within the publication. This will allow SQL Server to instead of replicating the entire record from one server to another, it will simply run the stored procedure on the subscriber with the same input settings as it was run with on the publisher. If you have very complex stored procedures, you’ll want to evaluate the increase load on the subscribers CPU against the reduction of network traffic between the distributor and subscriber.

This technique is critical to use to reduce the network traffic if you have a multi-tier replication topology. This is where your subscribers are also publishers to subscribers of their own. This is because of the way that the replication writes data to the subscriber. Replication uses stored procedures on the subscriber to update the values of the record. Assuming a table which looks like this:

CREATE TABLE Employee
(EmployeeId INT IDENTITY(1,1),
LastName NVARCHAR(50),
FirstName NVARCHAR(50),
DepartmentId INT)

The stored procedure which Microsoft uses for replication will look like this.

CREATE PROCEDURE MS_Employee_Update
@EmployeeId INT,
@LastName NVARCHAR(50),
@FirstName NVARCHAR(50),
@DepartmentId INT,
@Columns INT,
@KeyField INT
AS
UPDATE Employee
SET EmployeeId = CASE WHEN @Columns & 1 = 1 THEN @EmployeId ELSE EmployeeId END,
LastName = CASE WHEN @Columns & 2 = 2 THEN @LastName ELSE LastName END,
FirstName = CASE WHEN @Columns & 4 = 4 THEN @FirstName ELSE FirstName END,
DepartmentId = CASE WHEN @Columns & 8 = 8 THEN @DepartmentId ELSE DepartmentId END
WHERE EmployeeId = @KeyField
GO

While this technique works great for a two tier replication topology, we suddenly have a problem when we try to replicate from the second tier to the third tier. On the second tier all the columns have been updated, no matter which columns have actually been updated. This will cause an undo amount of replication traffic between the second and third tier servers as every column must now be replicated instead of just the values which have changed.

When you replicate the stored procedures as well as the tables when the stored procedure is fired to update the data on the publisher, that same stored procedure will be fired on the subscriber thereby reducing the amount of data which needs to be sent from the second tier to the third tier as again only the stored procedure execution is replicated. On very wide tables this reduction of data can reduce your network traffic load up to 70% (or higher depending on the data being changed and the width of the table).

You can verify that SQL Server is indeed using the stored procedures by using SQL Profiler to profile the distribution process on the subscriber. If you see your stored procedure names being fired then it’s using this technique. If you see procedures named by Microsoft, then it is not.

This technique will only work, if your application has no inline SQL. If you use a combination of stored procedures and inline SQL within the application, the data which is changed by stored procedure execution will be replicated using this technique; while the data which is changed by the inline code will be replicated using the traditional technique. If you have dynamic SQL within your stored procedures the SQL Server will not be able to use the replicated procedure to update the data on the subscriber. The dynamic SQL will prevent the SQL Server from recognizing that the stored procedure is what caused the data change, and the data will be replicated using the normal technique.

Denny