Karan Gulati (SSAS Maestro)

Problem Statement: Synchronization between different builds or versions of Analysis Services.

Solution: The Synchronize Database Wizard makes two Microsoft SQL Server Analysis Services databases equivalent by copying the data and metadata from a source server to a destination server. This wizard can also be used to deploy a database from a staging server onto a production server, or to synchronize a database on a production server that has changes made to the data and metadata in a database on a staging server.

Apart from Data it copies the metadata, which makes Synchronization between different builds unsupported and you can’t synchronize across major versions or service packs.

Description: Customer asking if AS supports TDE like encryption we got in SQL Engine.

Solution:

SSAS doesn’t support any data encryption on disk, in simple words – SSAS doesn’t do any kind of encryption.

What it supports:

HTTPS –

You can use Analysis Services using PUMP and configure pump with HTTPS

Windows File System encryption  –

You can use Windows File System encryption (Windows Server 2003) or BitLocker (on Windows Server 2008 and Windows Server 2008 R2) to encrypt the drive used to store cubes. Be careful, though; encrypting MOLAP data can have a big impact on performance, especially during processing and schema modification of the cube. We have seen performance drops of up to 50 percent when processing dimensions on encrypted drives though less so for fact processing. Weigh the requirement to encrypt data carefully against the desired performance characteristics of the cube.

References:

Encrypting File System in Windows XP and Windows Server 2003

BitLocker Drive Encryption

Use ROLAP partitions –

Use ROLAP for very confidential data and store the data in a TDE encrypted Database.

Caveat – ROLAP comes with its Performance cost, so keep that in mind.

Applies to Windows7, Windows 2008/R2 and Vista

SQL Server all builds >= SQL 2005

Generally, for troubleshooting SSAS Issues, we recommend to collect below mentioned Perfmon Counters

– All Counters Related to SSAS (MSAS_ or MSOLP$)

For default instance SSAS Counter Name suffix with MSAS.n and for named instance it start with MSOLAP$Instance Name

– System, Process, Processor, Physical Disk, Logical Disk, Memory , Page File

· Got to Run & type Perfmon

· Under Data Collector Set, right click on User Defined and Click New Data Collector Set

· Create New Data Collect Set and Give name as MSTrace & Select Create Manually, Click Next

· Click on Add required counters (Sample Interval as 10 Seconds)

· Here you can specify the Run as user which has Admin Access over box (in case you have logged with credential which is non admin)

• By default Location of Trace file is – %systemdrive%\PerfLogs\Admin\ but you can change it.

Right Click on Newly Created Trace – Mstrace and select Properties – In Properties under directory tab you can specify the location also you can schedule as per your requirement.

Once you are done with that click OK and Run trace as shown below

Reproduce the issue and collect traces, once you are done just stop traces and check for traces in location which you had provided in Properties.