One of the classics as far as best practices is concerned is to exclude SQL Server files and folders on your antivirus programs running on the server. We all know this statements and often I see DBAs not adding any extra care to this recommendation. There are not many blogs that call them out explicitly.
Although antivirus software is a very important part of security planning, it is important to understand the effect it has on SQL Server files. After rebooting a server, if the antivirus software locks a SQL Server file before SQL Server can gain access to that file, potential issues ranging from SQL Server not being able access those particular files to possible database corruption may occur. In a number of cases I have seen SQL Server refusing to start sometimes or the CPU / IO seems to be stalling sometimes. As you can see there are a number of these random errors one might get because of not implementing a simple best practice. Therefore, it is recommended that the following files be excluded from all antivirus scans:
- SQL Server data files (typical extension is .mdf, .ndf, and .ldf)
- SQL Server backup files (typical extension is .bak and .trn)
- Full-text catalog files
- Trace files (typical extension is .trc)
- SQL Audit files
- SQL query files (typical extension is .sql)
- Directories holding Analysis Services data and partitions
- The directory that holds Analysis Services temporary files used during processing
- Analysis Services backup files
In addition to excluding SQL Server and Analysis Services files, it is recommended to exclude the following list of processes from antivirus scans:
- SQLServr .exe
- ReportingServicesService .exe
- MSMDSrv .exe
For environments where SQL Server is clustered, exclude the C:\Windows\Cluster directory and the Quorum drive.
Though this list is not exhaustive, I am sure there might be a few more that I might have missed. This was a humble shot at this topic and I am sure as seasoned DBA’s you might have a little more to include. Do let me know under the comments section and I am sure this can be a great source of reference for others searching on this topic too.
Additional resources here.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
