I’m anti-virus. Heck, we’re all anti-virus. But you gotta make exceptions when you’re running SQL Server. Click play already, you hypochondriac.

Full Transcript:

Holy cow, more videos!

My name is Erik with Brent Ozar Unlimited, and today we’re going to talk very briefly about configuring anti-virus for SQL Server and why it’s important, because bugs might crawl all over your database picture. Who wants that?

Anti-virus is a bit of a devilish beast, because let’s face it, you have a server, it’s really powerful. Someone’s going to want to watch YouTube on there, full high-def, 4K YouTube videos. What else could a person dream of that would make life better?

Even if you have internet access disabled, someone might go and copy files over on here, thinking that they’re perfectly safe and sound, it might be some crazy monitoring tool, or I don’t know, whatever else people copy over to servers, and anti-virus can help keep you safe. You know, in today’s day and age, there’s all sorts of malware, ransomware, other viruses and stuff going around, and they can spread very, very quickly, especially if people aren’t careful.

Anti-virus though, it can also hurt performance. Thankfully, Microsoft has given you a complete list of applications and exclusions that you need to make for them when you run anti-virus. SQL Server is not an exception. If you Google Microsoft anti-virus exclusion list, you come to this page, and this page has a table of contents that is so nicely hyperlinked I can’t even believe it’s Microsoft. If you click on the S, it brings you down here, and the very last one of the S’s – last but not least, is SQL.

When you click on SQL, it brings you to a page titled, How to choose anti-virus software to run on computers that are running SQL Server. You don’t have to pay too much attention to warning about running SQL Server 2005 unless you’re on SQL Server 2005, in which case, I feel terrible for you for different reasons. Not because you might get a virus.

But if you scroll down, this article goes over all the security risk factors, what are high-risk servers, like you know, if the server is opened to the internet, it’s publicly facing – it’s obviously much higher risk than some sandbox server tucked away in a data center that you need three levels of fingerprint iris scanners to get into and access, so you have to be careful there.

It will go through the different tool types, and then most importantly, when you get down to the bottom is this section, where it goes over directories and file name extensions to exclude from virus scanning. Unfortunately, right now, this list stops at SQL Server 2012. This isn’t a big deal for SQL Server 2014, but it probably becomes a bigger deal in SQL Server 2016 and 2017, with the inclusion of Python and R and all of the other gidgets and gadgets and doodads that Microsoft is adding in. There may be more files and directories at play that you have to exclude.

Microsoft hasn’t published that list yet, but this is still a very good starting place. And you still need to exclude your MDF files, your LDF files – those are data and log files, your transaction log backups, your full backups, you need to exclude those as well. If you have any trace files you have to exclude those, audit files, query files, in directories that hold different components in SQL Server like analysis services and reporting services. SQL Server 2012, of course, has these paths with 11 in.

If you’re on a newer version, you’re going to have to replace that with your version in there. For SQL Server 2014, that’s going to be 12, for SQL Server 2016 that’s going to be a 13, and for SQL Server 2017, if and when it finally comes out before some awful apocalypse happens, then there’s going to be a 14 in there.

There are also some additional considerations for clustering that you’re going to want to read about down here. Now, this is a short video because really, the work is on you to just read the article and go along with what it says, but it’s something that you have to do because if anti-virus fires up, it can start scanning the SQL Server executable, other binary files. It can start scanning data and log files, and there have been known issues where some anti-virus applications would actually corrupt databases when they ran and when they hit these files, so you have to be extra careful about setting up anti-virus on your SQL Servers. Corruption is tough enough when it’s not caused by anti-virus. Thanks for watching.