Anti-virus is a devilish beast. If you don’t implement it, someone’s sure to log onto your SQL Server and download some free tool that puts you at risk as soon as you’re not looking. If you do implement it, sometimes it can make you crazy.
And often it’s not even up to you: you need to follow corporate policy.
Here’s what you should know if you’ve got an anti-virus tool on your SQL Server.
Set the right Anti-Virus folder, file, and process exclusions
Good news: you don’t have take my word on what types of files and directories your anti-virus software should exclude. Microsoft lays it all out for you in KB 309422
Special cases: Keep outta my Address space
If you use any of the following products, read KB 2033238, “Performance and consistency issues when certain modules are loaded into SQL Server address space”:
- McAfee VirusScan Enterprise
- McAfee Host Intrusion Prevention
- Sophos Antivirus
- PI OLEDB provider
Virtualization Guests need staggered schedules
Scheduling matters. VMware recommends that you set anti-virus scans to run at non-peak hours in your guests, and to set scheduling so that multiple guests don’t all fire up burn all their resources at the same time on a single hosts.
Need proof? See page 41 in the Performance Best Practices for VMware vSphere® 5.5 Guide.
Windows Failover Clusters
Check out KB 250355 for special steps for Failover Clusters. (Thanks, Gary, for pointing out this has a bit more info than KB 309422 mentioned above!)
If you’re Running Anti-Virus, Run it in Pre-Production, too
Anti-virus isn’t just for production servers. If you run into any problems, you want to be able to check for a repro outside of production, too, right?
Some additional considerations if you have SQL Server Failover Cluster Instance:
http://support.microsoft.com/kb/250355 (Antivirus software that is not cluster-aware may cause problems with Cluster Services)
Oh, good point. KB 309422 mentions excluding the cluster folder, but it doesn’t mention that temp directory. I’ll add a link to the article for that in case not everyone reads the comments– thanks!
A very useful post. We are running Sophos and were unaware of Sophos issue.
Keep the cluster-exclusions in mind when installing Trend Micro’s Deep Security 😉
Specifically for Sophos, I have all SQL Servers in their own OU then have a GPO for the SQL OU to set this registry key: [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\SAVOnAccess]
Additionally exclusions as described in KB 309422 are applied via Sophos policy settings.
How about the COM folder for snapshot.exe, etc for replication. I want to make sure replication processes are not intercepted by the antivirus. All those replication processes live in the COM folder, e.g.
%ProgramFiles%\Microsoft SQL Server\100\COM all exe’s here in 2008 R2
Or for SQL 2005
%ProgramFiles%\Microsoft SQL Server\90\COM
I haven’t heard of virus scanners causing problems with that, but I can’t think of a downside to excluding them, either.
Thank you for the article it added some much needed information about the Issues McAfee can cause.
Any experience or recommendations with Palo Alto TRAPS on a SQL Server?
or scanning SQL Servers?
Never even heard of it.
I have a list of SQL AV exclusions that are needed. Article has substituted the local Windows folder with %installdrive% and the SQL Server version with
\Program Files\Microsoft SQL Server\.\Reporting
Does it need the actual SQL Version 13.0.1305 or 2016 ?
Ian – for questions, head to a Q&A site like https://dba.stackexchange.com.
Another issue is with Datacollection: the cache files can get locked by Trend Micro Deep Security, memory usage can increase to 1,5GB RAM. On a 4GB RAM VM you will get memory exhaused errors (A significant part of sql server process memory has been paged out) and datacollection jobs may fail every now and then.
This article nicely addresses the real-world of wrestling with company anti-virus policies and how they impact our servers, but can we also discuss our vision fro a ‘more perfect world’? (so we at least have some nice food-for-thought for our bosses to read…)
I am increasingly feeling like we should be having risk versus reward discussions regarding AV policies, both for performance and for the random removal of critical server files (which has happened to us multiple times in the wild on production servers). Ideally, shouldn’t we approach antivirus differently on servers and especially database servers? For example, should server AV products every actually remove suspicious files or should they just log and alert so as not to interrupt critical processes? And does it even make sense to perform real-time file-access scanning or should we treat AV scanning like any other after-hours maintenance routine?