SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Configure Anti-Virus on your SQL Server

This week I had an application team report the error “SQL Connection Lost”.  Fortunately they also reported the exact times of occurrence, which made it much easier to hunt down.  I did some digging through the SQL logs and DMVs, but did not find anything that indicated SQL was dropping the connection.  I also did some digging in the application logs, but I didn’t find anything more specific than the generic error the application team reported.  My next stop was to start combing the system logs to see what I could find.  The one and only thing that coincided with the times of the issue was a full AV scan.

IO is generally the biggest bottle neck for SQL and there is nothing that can generate some serious additional IO than a virus scan.  This application does a large nightly import of data, so we just changed the import time.  We could have also changed the AV scanning time, but that is managed by another group and would take way too long to get changed.  That fixed the issue, but changing the import time is not the long term solution we want.  We’ve got the AV team working on changing the scan time, so we can change the import time back to where we had it.  So how should we configure our AV software on our SQL servers?  Here are some tips.

  • AV software creates a lot of IO so make sure the scans don’t coincide with large SQL jobs like imports or SSIS packages
  • Create a scan exemption for .MDF file extensions
  • Create a scan exemption for .NDF file extensions
  • Create a scan exemption for .LDF file extensions
  • If you have drives dedicated to your database files, CONSIDER an exemption for that drive.  Did you see that word in bold in the prior sentence?  Be aware that this can be dangerous if something like a keylogger or executable gets dropped on that drive, because now you’ll never catch it.  It is far better to make exemptions based on file extensions as mentioned above.  I mention this only because it is an option, albeit a very dangerous one.
  • Create a scan exemption for .BAK and .TRN extensions.  I suggest you only do this if your backups are on the same drive as your database files (shame on you for that, but sometimes we have no choice).

Ryan Adams

Ryan Adams has worked for Verizon for 15 years. His primary focus is the SQL Server Engine, high availability, and disaster recovery. Previously he was a Senior Active Directory Architect and designed the company's worldwide Active Directory infrastructure. He serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter. He also serves as a Regional Mentor for PASS and holds the following certifications: MCP MCSA MCSE MCDBA MCTS MCITP.


Leave a comment on the original post [www.ryanjadams.com, opens in a new window]

Loading comments...