Vidas Matelis Analysis Services Blog

SQL Server 2008 February CTP6 is out and I spent last few evenings installing it and going through new features. Here is my report. 

Attaching and detaching SSAS databases 

In new CTP Microsoft introduced option to attach and detach Analysis Services databases. This works in a very similar way like with SQL Server. When detaching SSAS database you have an option to specify Password. When attaching SSAS database you have an option to specify Folder, Password and specify if you want to make new database Read-only.

You can detach/attach SSAS database in SQL Server Management Studio, or by using XMLA command. When you detach database, SSAS creates detach log file. This file is used for database attach operation. If during detach you specified password, then this detach log file is encrypted. During attach operation you will need to specify the same password, so detach log file can be decrypted and used.

Example of XMLA command to detach Analysis Services database:

<Detach xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine“>
  <Object>
    <DatabaseID>Adventure Works DW</DatabaseID>
  </Object>
</Detach>

Example of XMLA command to attach Analysis Services database:

<Attach xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine“>
  <Folder>Z:\SQL2008-CTP6\Adventure Works DW.0.db</Folder>
  <ReadWriteMode xmlns=”http://schemas.microsoft.com/analysisservices/2008/engine/100″>ReadOnly</ReadWriteMode>
</Attach>

Cool thing about attaching SSAS database is that it does not have to be in SSAS data directory, and you can even attach database using windows shared folder. For example, during my tests I successfully attached database located in folder: \\Office1\SQL2008-CTP6\Adventure Works DW.0.db.

Read-only SSAS Shared Scale-out

In SSAS 2005, when you want to scale out SSAS installation, you simply setup 2 SSAS servers and restore the same SSAS database on both of them. Then you have some sort of network balancing solution that directs user requests to one of these machines. This works OK for smaller SSAS databases, but when database size grows to 100’s of GB, supporting SSAS databases on different servers becomes harder.

In SSAS 2008 Microsoft introduced new feature “Read-only SSAS shared scale out”. This feature allows two SSAS servers access the same database files that are located on SAN disk. Simplified setup steps are as follows:

• Process SSAS database on the server designated for processing.
• Detach SSAS database from server where it was processed.
• Copy database folder to shared Storage Area Network (SAN) disk
• Attach SSAS database to each Data Access servers in read only mode.

There are a few more small steps involved, but they are related to taking SAN offline and online.

I don’t have a SAN system at home to test these steps, but I tried to reproduce this type of setup using Virtual PC. I installed SSAS 2008 on 2 virtual machines and then on both of them I tried to attach SSAS database located on my real PC hard disk. I had no problems attaching that database to one SSAS server. But when I tried to attach same database to different SSAS servers at the same time, I was getting error message telling me that “is not a disk file or file is not accessible.”. I understand this makes perfect sense, as my setup is not a SAN disk, but this test gave me a good feel that setup with real SAN is quite simple.

Powershell

I noticed that SQL Server installs Windows PowerShell without giving you an option to opt out. That means each SQL server machine will have Windows Powershell installed. It is time to learn PowerShell scripting language! In BOL I found description about SQL Server provider for PowerShell. I could not find anything in BOL about Analysis Services provider for PowerShell. Why such discrimination? I hope Microsoft has plans on releasing it. At least Microsoft could licence Analysis Services PowerShell provider written by Darren Gosbell  :-).

Other

• Adventure Works example database still  reports 79 warnings during deployment. I would say this database has to be re-designed so that is follows best practice advice.
• I noticed that metadata queries run much faster.