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 Anti-Virus locks SQL Server files, SQL Server service would not only be able to access those files but 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. You must prevent from getting into this situation. (At least on Production server)
Many DBAs would simply exclude .MDF and .LDF files from being scanned by Anti-Virus but thats 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:
- SQL Server Data Files (files with extensions like .MDF, .NDF)
- SQL Server Log Files (files with extensions like .LDF)
- SQL Server Backup files (files with extensions like .BAK and .TRN)
- Full-Text Catalog Files
- SQL Server Audit Files
- SQL Server Query Files (Extensions like .SQL)
- Trace/Profiler Files (Extensions like .TRC)
- 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.
- Folder where Analysis Services Backups Files Reside
- Exclude SQLServer.Exe file (This is the main database engine file)
- Cluster Directory (Usually it is C:\Windows\Cluster) *If your servers are clustered
- Cluster Quorum Drive
- FILETABLE and Filestream folders
- Database Mail file/folders
Taking care of excluding the above files/folders will make sure your Anti-Virus software doesnt 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.