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:
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.
Within the datadir there are two files with the .vmp file extension. Master.vmp is like the contents list of the database (and txn.vmp is the new copy), it contains all of the key databases files, and their versions. The system does a check that the versions in here match the versions in the file names. Various errors, files /databases removed, etc can occur on start-up or read of the files when there is a mismatch. Therefore during the two phase commit of a transaction update this plays a very important part.
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.
Extended Properties: Exception information: – System.AggregateException: One or more errors occurred. —> Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: File system error: The index data is damaged. Physical file: \\?\L:\OLAP\Data\AdventureWorks2012.0.db\Member.0.dim\7.Id.Aggregate Member.fact.map.hdr. Logical file: .
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.Follow @rPh0enix
Here is the excerpt from the KB article referenced above. TL;DR essentially they recommend that whatever you have configured as DataDir, TempDir and BackupDir to be excluded from virus scanning.
- The directory that holds Analysis Services data
Note The directory that holds all Analysis Services data is specified by the DataDir property of the instance of Analysis Services. By default, the path of this directory is C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\Data. If you use Analysis Services 2000, you can view and change the data directory by using Analysis Manager. To do this, follow these steps:
- In Analysis Manager, right-click the server, and then click Properties.
- In the Properties dialog box, click the General tab. The directory appears under Data folder.
- The directory that holds Analysis Services temporary files that are used during Analysis Services processing
Note For Analysis Services 2005 and later versions, temporary files during processing are specified by the TempDir property of the instance of Analysis Services. By default, this property is empty. When this property is empty, the default directory is used. This directory is C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\Data. If you use Analysis Services 2000, you can view and change the directory that holds temporary files in Analysis Manager. To do this, follow these steps:Analysis Services backup files
- In Analysis Manager, right-click the server, and then click Properties.
- In the Properties dialog box, click the General tab.
- On the General tab, notice the directory under Temporary file folder.
Optionally, you can add a second temporary directory for Analysis Services 2000 by using the TempDirectory2 registry entry. If you use this registry entry, consider excluding from virus scanning the directory to which this registry entry points. For more information about the TempDirecotry2 registry entry, see the “TempDirectory2” section of the following Microsoft Developer Network (MSDN) website:
- Note By default, in Analysis Services 2005 and later versions, the backup file location is the location that is specified by the BackupDir property. By default, this directory is C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\Backup. You can change this directory in the properties of the instance of Analysis Services. Any backup command can point to a different location. Or, the backup files may be copied elsewhere.
- The directory that holds Analysis Services log filesNote By default, in Analysis Services 2005 and later versions, the log file location is the location that is specified by the LogDir property. By default, this directory is C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\Log.
- Directories for any Analysis Services 2005 and later-version partitions that are not stored in the default data directoryNote When you create the partitions, these locations are defined in the Storage location section of the Processing and Storage Locations page of the Partition Wizard.
- Filestream data files (SQL 2008 and later versions)
- Remote Blob Storage files (SQL 2008 and later versions)