Servers

SQL Server Firewall and performance tweaks

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. 

Subscribe to VirtualizationHowto via Email 🔔

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Brandon Lee

Brandon Lee is the Senior Writer, Engineer and owner at Virtualizationhowto.com and has over two decades of experience in Information Technology. Having worked for numerous Fortune 500 companies as well as in various industries, Brandon has extensive experience in various IT segments and is a strong advocate for open source technologies. Brandon holds many industry certifications, loves the outdoors and spending time with family.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.