I've been on New Orleans time all this working week and have been up late at night, not doing much, just hitting the ‘random’ button on DBA Reactions (and posting them on Twitter) when I came across this one on Anti Virus running on SQL file-types.
“Good one” I thought to myself. And then I wondered if any of the files that make up the cubes on our SSAS boxes were being scanned or whether they were excluded. We have some big cubes in our system, and by big i mean TB's worth of cubes, some of them over 700GB. So I fired up the System Center 2012 Endpoint Protection on one of the SSAS boxes, and sure enough, all files were being scanned. But should they? This Microsoft KB article confirmed my suspicions that they can be exempt from scanning: How to choose antivirus software to run on computers that are running SQL Server
I've included what it says Verbatim below under Sources. TL;DR essentially they recommend that whatever you have configured as DataDir, TempDir and BackupDir to be excluded from virus scanning. But it felt that this was too broad to exempt at this level; sure you can only have one data directory, but there's still no stopping people detaching cubes and moving them around the disk outside of the data directory, and the same can be said for backups. Plus it matters how your anti virus scanning policies are set up; The more policies that are set up, the more of a challenge it is for the guy that has to manage all the policies. And of course excluding folders means that it does not stop anyone from putting something bad in there. So for my own peace of mind I decided to get a list of all the file types that are stored in the “data” directory for my instance of SSAS. So firing up a PowerShell console I ran this command:
powershell Get-ChildItem G:\OLAP\Data\ -Recurse | Select-Object Extension | Sort-Object Extension | Get-Unique -asString > C:\SsasFileTypes.txt
opened up the txt file and got a list below in the txt file:
Extension --------- .ahstore .asstore .asstoreidx .astore .bin .bsstore .bstore .cub .data .det .dim .ds .dstore .hdr .hstore .khstore .ksstore .kstore .lstore .map .ostore .prt .sstore .xml
It's worth pointing out that in the PowerShell command that you have to add the ‘-asstring’ parameter otherwise you will just get back one extension. The reason for this is that if you didn't, the ‘get-unique’ command would just look for the unique object, which in this case is just one object with an extension type, and therefore just return the first one. It's technically correct, which is why we need to use the ‘asstring’ to work with the value of the property as opposed to the type of the property. If you want to know more about how this works then read this Scripting Guy article, it actually solved my problem because I was getting the one file extension problem whilst writing this otherwise very straightforward PowerShell command from the Scripting Guy blog on picking out Unique file names.
Anyway, back to the list: by applying some common sense to that list we can cherry pick the SSAS releated extensions out and just add them to the exempt list. For backup files, there is just one type (.abf) which can be added. In the data directory there is the .vmp file that needs to be added. Below is a list of file types used for cubes and given a very brief description of what they are.
Key Store Files: Key store file: .kstore Hash store file: .khstore String store file: .ksstore Property Store Files Main Stored File: .astore Hash Stored File: .ahstore String Stored File: .asstore BLOB Store Main Store File: .bstore String Store file: .bsstore Analysis Services Cube Files .cub Map Store Files Header File: .hdr Map Store File:.data Deleted member Store File:.hstore Analysis Services Dimension Files .dim SSAS Backup File .abf Analysis Services Data Store File Hierarchy Files: Decoding Table: .dstore Structure Store File:.lstore Set Store: .sstore Order Store:.ostore
So now the cubes are no longer scanned.
If you leave anti virus scanning to scan these and you have lazy processing switched on you will experience all manner of problems with corrupted aggregations. Essentially the anti virus locks one of the files whilst an update to the file is being made and causes a corruption. You will get an error very much like the one below.
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft .AnalysisServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional (ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters) The only way to correct the corruption is to run a ProcessUpdate on the corrupted dimension with Process affected objects set to true. Make sure that you include .vmp files in the excluded file-type list.