Monday, May 10, 2010

SSAS does not work if copied from another machine as a VM -- Why?

Did you know that even if you create an exact copy of the sql server virtual machine, the analysis services will not work?

I faced this problem last week.

The exisitng development machine (lets call it DEV1) stopped working and was giving lots of errrors.
So we thought that we will create a copy of the exisitng production system as a virtual machine and use that as the development machine and we created an exact copy of the production system as a virtual machine but gave it a different name (DEV2).
Then decomissioned the original development machine.

When I tried to access the sql server database on the new development machine (DEV2) the access was fine. But when I tried to access the analysis services through the management console, there was no response.

So I thought I had two options to make analysis services to work --

  • Option 1: Reload sql server from scratch and ssas and SSRS and then copy all databases and cubes from the production server.
  • Option 2: Try renaming the new development machine (Dev2) to the old development machine (Dev1)

Since option 2 was easier I thought I would try that first. And bingo the trick worked.

But I still don't know why analysis services does not work from a copy.

Can anyone of you help me understand why?

No comments:

INSERT EXEC failed because the stored procedure altered the schema of the target table.

This morning I was trying to call a stored procedure and insert the results in a temp table using the following code  -- create table #...