SSAS: Deploying to renamed databases

Last year I logged an issue on the connect site around deploying from BIDS and renamed databases.

I got a couple of messages relating to this issue, one saying that it was being closed as it was a duplicate issue and then another one recently where it was updated to indicate that the issue has been fixed in SQL Server 2008. I think it is really great to get this sort of feedback that something is being done, unfortunately it does not appear to be accurate as I recently tested this issue on SQL Server 2008 RTM and it still appears to be there.

So what exactly is this issue? Well, try out the following steps on a dev machine.

  1. Deploy the Adventure Works DW 2008 database.
  2. Open up Management Studio and rename
    "Adventure Works DW 2008" to "Adventure Works DW 2008 v2"
  3. Change the deployment properties of the Adventure Works DW 2008 project in BIDS
    to deploy to "Adventure Works DW 2008 v2"
  4. Now try to deploy the project.

It did not work did it?

So what is the expected behaviour here? Personally I expect that I would get a message about the database not being in synch or already existing and being asked if I was sure that I wanted to overwrite it. Instead all you get is the following:


And you cannot deploy at all.

This is happening because all objects in SSAS have a name property and an ID property and when you deploy a database from BIDS it sets the ID to the same value as the name. The product team created these two properties so that internally everything could be linked using the ID property and then users could be free to change the names of objects without breaking anything. This is a great goal, but as you can see we are not quite there yet.

I have another one for you - Consider the following scenario.

You are working on some changes to a database, you want to keep the current version around for benchmarking, but you expect that the new version will soon supercede the old one. So you open up SSMS and rename the database from "Adventure Works DW" to "Adventure Works DW - old". Now you deploy the updated Adventure Works DW project ready to do your side by side testing.

What you will get is a dialog like the following:


Which is unfortunately similar to this dialog:


Which is what you get when you tell BIDS to process an object when there are changes in the project which have not yet been deployed.

If you read the first dialog carefully and if have an appreciation for the fact that there are distinct ID and Name properties you will see what is going on and that what BIDS is telling you is that you are about to overwrite your renamed database on the server with the one in BIDS - probably not what you want. But, if you don't read it carefully, it does not look like a critical warning and if you skip to the last line you probably just see "Would you like to continue?" which you obviously do and then you will curse BIDS for "deleting" your renamed database.

I think the deployment of a database from BIDS should use the following logic.

Deploy Flowchart

I could be wrong, but I am guessing that this is probably not something that is likely to be changed in BIDS any time soon. And honestly, if you are aware of what is going on I don't really see it as being too much of an issue.

We already have some requests for additional deployment functionality in BIDS Helper, maybe I will see if I can address this issue too as part of that piece of work.

What is probably more of an issue is that the problems with renamed databases seem to extend to the Synchronize functionality as detailed in this newsgroup thread

Technorati Tags: ,

Print | posted on Sunday, September 28, 2008 9:50 PM