It doesn’t matter who you are, you or someone you know has been dramatically impacted by a computer virus. It is a painful process to go through and moreover you feel violated, angry, and deflated when it happens to you. Once this happens we vow never to let anything like this happen again, only for it to, well, happen again even when we have anti-virus software running on our machines because we unknowingly installed malware through a trojan.
In any case, it can compromise our computers, data, and network security…and potentially our sanity. So to combat this, we turn to the anti-virus experts to protect us and our networks. This can come with a number of side-effects of it’s own, some of which we will discuss within the scope of this article.
I can’t count the number of times I have been put in contact with an IT person fighting this very issue. From the get-go, they perceive me as a threat because they assume that I am a “software guy” and I am going to try and take away their control of the network. The problem is that is only partially true. I am a “software guy” but I am also a network administration, network designer, data center and cloud engineer, and Windows architecture expert among other titles. My tenure at MicroFour started in hardware, actually. Before you could purchase a touchscreen off the shelf, they had to be built by hand. My first job at MicroFour starting in 1991 was to build the touchscreens that we made available to our users. We were pioneers in the software world when it came to interfacing with a touchscreen.
Fast forward to today and our network has grown significantly. We have data centers that must have over a 99% availability 24/7 around the world. Our internal network is split across multiple locations with varying levels of employee ability and access. All of this requires a safe and secure network while providing the highest levels performance for our users. Which leads me to the point of this article. Though we need to have anti-virus on the network and run regular scans, real-time anti-virus will kill databases and SQL Server performance.
Understanding the Problem
I don’t want to get overly complex here so that the problem can be fully understood. To begin, SQL Server is a relational database that runs on a server or within a server environment to house business critical information, a.k.a data. SQL Server will use as much memory as the server on which is running will give it (and sometimes more if not configured correctly). Each time the data is updated, it is first written to memory and then eventually back to the hard drive for permanent storage. There are many ways to configure and optimize this, but for the sake of this discussion, this simplistic explanation is sufficient. So if we have a 4 GB size database and allow SQL Server to use this much memory, then we will eventually have 4 GB of memory being used by SQL to cache off the entire database for optimization and speed.
And in a perfect environment this is very true. Depending on the hardware specs, the memory read/write speed and hard disk read/write speed can differ significantly as can the cost. So we will use some average numbers. The read/write speed to memory in this average environment is 12,800 MB/second and the read/write speed to the hard disk is 175 MB/second. You can already see the difference in the speed. The read/write access of the memory is roughly 75 times better.
Now enter the real-time anti-virus. When enabled and allowed to scan the SQL Server instances the real-time anti-virus re-scans each change to memory as it is cached or written. The larger the database, the slower things will get. The anti-virus scanning process is extremely invasive and at times, it may perceive some of your data as a threat and throw it out…literally. Thus creating levels and layers of corruption in your data. At the very least, it will degrade performance greatly. When SQL Server memory is scanned with a real-time anti-virus, performance may fall lower than 1 MB/second write times or worse in extreme cases. That is over 12,500 times slower!!! It won’t start that bad, but as the database grows so will the memory usage, and in turn the performance hits.
In one example of performance degradation, one of our customers had real-time anti-virus running on their SQL Server machine. They started complaining that saves were taking longer and longer. In one example, they indicated that they waited 2 hours for a chart encounter to save. What!?!?! Once we turned off the real-time anti-virus, the save was less than 1 second. The performance hindrances are not always this extreme. But it is a guarantee that it will get worse over time and all of a sudden the data will become corrupt or performance will be non-existent.
The simple solution is to turn off real-time anti-virus for any SQL Server machine. Depending on the version of anti-virus being run there can be many differently configuration options such as memory exclusion, folder exclusions, etc.. But when not sure, turn off the real-time anti-virus altogether and ensure that scheduled scans run during off-business hours.
The best solution really exceeds the scope of this article, but several policies should be enforced in order to best protect the network.
- Secure the Internet traffic by sending all inbound Internet traffic through a centralized scan on a hardware device such as a firewall before being allowed into the network.
- Secure the network by preventing LAN users from installing applications or accessing anything outside of the scope of their business duties.
- Use a content filter to prevent access to websites that do not pertain to business duties.
- Ensure that all email attachments are scanned prior to downloading. Most email services, including Google+ provide this type of support. As do many firewalls.
By following these basic 4 steps, even for small networks, over 90% of the threat of viruses being introduced to the network will be eliminated. Talk with your IT person if they have questions. There are cost effective easy to configure firewalls that can help with steps 1,3, and 4 such as SonicWall (TZ100 and TZ200 series) and Barracuda brands among others.