My IT department installed an antivirus with SQL Server

Time for another short blog post, and this one combines two topics I am very passionate about: security, and SQL Server performance.

Let’s start by talking about “antivirus” and what that means in today’s world. The term antivirus (AV) itself is outdated; traditionally, AV products detected malicious activity through fixed patterns of code or patterns of behaviour. Modern malicious software (malware) can be extremely sophisticated, and is able to bypass these detections very easily, being quite clever using PowerShell and fancy stuff in-memory. When we discuss AV software, we should be thinking anti-malware, not anti-virus.

Most of us have heard horror stories about AV products causing performance issues for as far back as we can remember. It scans memory, it scans disk, it by necessity requires CPU.

SQL Server is a complex beast. It contains a lot of operating-system-type code to allow blisteringly fast levels of performance without always needing to call out to the underlying operating system. This is what allows it to run on Linux as well as Windows: it has a limited footprint on the operating system, compared to other applications. This behaviour, however, could be construed as malicious activity by the average malware detection engine: high CPU utilization, heavy RAM usage, and continuous disk reads and writes.

SQL Server be busy, fam.

This is why it is documented that we should exclude SQL Server from any AV (anti-malware) detection products, so that it can get on with doing what it does best.

Yes, it’s formally documented. This is why we should read documentation when installing things. While it’s super-easy to click “Next,” “Next,” “Next,” that should not be the case with a complex product like SQL Server.

So if your IT department insists on putting an AV product on your server (which isn’t a bad thing provided that it’s actually looking for modern malware), or if you use the extremely awesome and free and built-in Windows Defender on Windows Server 2016 or higher, you can (and should) add exclusions for MDF, LDF, and TRC files, and let SQL Server perform at its peak.

For reference, this is a high-level list of exclusions recommended in the KB309422 knowledge base article linked previously. Yes it’s a big list. SQL Server is a massive product that requires care when installing.

• SQL Server data files
• SQL Server backup files
• Full-Text catalog files
• Trace files
• SQL audit files
• SQL query files
• The directory that holds Analysis Services data
• The directory that holds Analysis Services temporary files that are used during Analysis Services processing
• Analysis Services backup files
• The directory that holds Analysis Services log files
• Directories for any Analysis Services partitions that are not stored in the default data directory
• Filestream data files
• Remote Blob Storage files
• The directory that holds Reporting Services temporary files and Logs (RSTempFiles and LogFiles)
• Extended Event file targets
• Exception dump files
• In-memory OLTP files
• DBCC CHECKDB files
• Replication executables and server-side COM objects
• Files in Replication Snapshot folder

This is a list of recommended processes to exclude from scanning (note the period in the folder name, which includes all versions of SQL Server in named folders):

• %ProgramFiles%\Microsoft SQL Server\.\MSSQL\Binn\SQLServr.exe
• %ProgramFiles%\Microsoft SQL Server\.\Reporting Services\ReportServer\Bin\ReportingServicesService.exe
• %ProgramFiles%\Microsoft SQL Server\.\OLAP\Bin\MSMDSrv.exe

Share your server antivirus horror stories in the comments below.

Photo by Micah Williams on Unsplash.