Running Antivirus software is critical part of server security but it is also important to understand the impact and effect it has on SQL Server. Imagine this: You’ve installed Anti-Virus on a server that already has SQL Server running, once the server reboots, it is highly likely that the Anti-virus software depending upon the scanning schedule will scan the SQL Server folders and lock SQL Server files before SQL Server can gain access to those files. When this happens, SQL Server would not only be able to access those files but also, there’s a possibility of data corruption since both SQL Server and the Anti-Virus programs are trying to access the files by putting an exclusive lock on them.
Many DBA’s would simply exclude .MDF and .LDF files from being scanned by Anti-Virus but that’s not enough. There are other important files that can also have an impact on SQL Server.
Here’s the recommended list of items that needs to be excluded from all Anti-virus scans:
1. SQL Server Data Files (files with extensions like .MDF, .NDF)
2. SQL Server Log Files (files with extensions like .LDF)
3. SQL Server Backup files (files with extensions like .BAK and .TRN)
4. Full-Text Catalog Files
5. SQL Server Audit Files
6. SQL Server Query Files (Extensions like .SQL)
7. Trace/Profiler Files (Extensions like .TRC)
8. Entire Directory that is holding Analysis Services files that is used for processing Cubes and used for queries that are reading files in to the data folder and the temp folder.
9. Folder where Analysis Services Backups Files Reside
10. Exclude SQLServer.Exe file (This is the main database engine file)
13. Cluster Directory (Usually it is C:\Windows\Cluster) *If your servers are clustered
14. Cluster Quorum Drive
15. FILETABLE and Filestream folders
Taking care of excluding the above items will make sure your Anti-Virus software doesn’t interfere with any SQL Server operations. However, keep in mind that you may experience some slowness when running Anti-Virus software on the same server as SQL Server but at-least it will not corrupt your data and will not lock any SQL Server operations.