Everyone, I wanted to pass along a few Windows firewall tweaks that I have found useful with building a SQL server on Windows 2008 server or higher. The following simple tweaks can really help with issues you may see involving general connectivity as well as performance issues that may happen due to TCP autotuning.
For a general SQL Server build on Windows 2008 server or higher, the following Windows Firewall tweaks can be made to allow proper communication with the server as well as communication between a server and other SQL servers or failover cluster servers running SQL. Make sure to run these from an elevated command prompt.
- Add the following rule to allow SQL communication from domain scope with management studio: netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
- Configure the TCP autotuning. Starting with Windows Vista/7 and newer OS’s, they have the ability to automatically change their own TCP Window auto-tuning behavior to a more conservative state regardless of any user settings. To disable autotuning which can cause some issues with SQL connectivity at times, you can use the command netsh int tcp set global autotuninglevel=disabled
- Configure RSS tuning. The receive-side scaling setting enables parallelized processing of received packets on multiple processors, while avoiding packet reordering. It avoids packet reordering y separating packets into “flows”, and using a single processor for processing all the packets for a given flow. This may be one that you want to play around with as well, however to enable rss use the following command. Disabling is as simple as replacing the enabled with disabled- netsh int tcp set global rss=enabled
- If DB Mail is needed, make sure your AV client is set to allow port 25 as many filter this due to mass mailing worms. McAfee in particular has an access protection module that needs to be set to allow port 25 traffic to send email from the box which is disabled by default
- Failover cluster connectivity can be allowed to internal cluster servers by adding an internal custom DOMAIN scope rule that allows the IP Address of the Cluster node name as well as the individual IPs of each node themselves. You can simply set this rule to allow All Programs to those internal IPs.
