SSAS: Deploying to renamed databases

Last year I logged an issue on the connect site around deploying from BIDS and renamed databases. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281595

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:

image 

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:

image

Which is unfortunately similar to this dialog:

image

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

Comments on this post

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
ive experienced this myself in SSAS2005. The way i get around this is to script entire SSAS database to file, modify the IDs manually and then run the script to create my new database.

not fun but.. but a reasonable workaround
Left by Bhavik on Oct 03, 2008 4:22 PM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
Hi,

SO does that mean there is no way around to deploy, if you have changed the ASdatabase name in SSMS ?.
I'm running into exactöly the
same problem. In BIDS I tried to impor the SSAS DB as new project and tried deploying it, I couldn't. Is there any other clean or dirty workaround to have the ASDB deployed ? . I have atleast 5 such databases and really don't wanna recreate from scratch.

appreciate any feedback.

Thanks
Brian
Left by Brain on May 08, 2009 11:55 PM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
It's even worse than this excellent article outlines:

1. Database name X, ID Y
2. Delete database X
3. Deploy from BIDS to database X
4. New database X has ID Y
Left by Tom Chester on Oct 30, 2009 8:01 AM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
@Tom In my testing BIDS always seemed to try to deploy a database with an ID that matched the name property. I will have to see if I can reproduce your issue. (maybe after the PASS conference, which is coming up next week). :)
Left by Darren Gosbell on Oct 30, 2009 10:11 AM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
Here are this and some other articles on SSAS Deployment:

http://ssas-wiki.com/w/Articles#Deployment
Left by Sam Kane on Feb 25, 2011 3:20 AM

# How to Edit the ID of a SSAS Database.

Requesting Gravatar...
By some miracle I found this. If you click a little button at the top of the solutions explorer in Visual Studio that say 'show all files' when you roll over it, you will see a file called Something.Database. View the code of that file and you can edit an <ID> element. This will change the ID of the database on deployment.
Left by Adam Gilmore on Mar 09, 2012 5:29 PM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
Adam - YOU ROCK. That little trick just enabled me to deploy a most excellent sandbox copy of my production project on SSAS 2008 R2....MANY THANKS!
Left by Greg Larsen on Mar 28, 2012 10:35 AM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
Adam, if you do that and you try to deploy having for example, roles created, you will get an error. I have realize that if you are under this situation there must be other internal IDs that got broken so you can not deploy. In my case I get an error like this: role ID with ID = xxxx doesn't exist in collection... and if I go to my role, that actually exists, I see that my Data Sources tab is just blank...
Left by Jon on Mar 13, 2013 10:56 PM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
I have created a Connect Item in hope that Microsoft will add the ability for administrators to update the SSAS database ID (not just the name):
https://connect.microsoft.com/SQLServer/feedback/details/1134252
Left by Rob N on Feb 17, 2015 11:53 AM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
@Rob N - not sure it that is likely to happen. The database ID is immutable because it's the key for the internal structures. Generating a GUID for the ID and allowing some of the XMLA calls to pass the Database Name instead of the ID might be another approach to solving this issue.
Left by Darren Gosbell on Feb 17, 2015 5:25 PM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
Hit F7 to show the code. in the second line you can change the ID
Left by Tim on Aug 18, 2017 6:26 PM

# re: SSAS: Deploying to renamed databases

Requesting Gravatar...
This happens when there is a Database already deployed with the same ID but with a different name.

In my situation, The database name in BIDS was named "MyDB" and on the server there was two databases :
- MyDB ( with ID = MyDB_Prod)
- MyDB_BKP ( With ID = MyDB)

this confused BIDS during the deployment.
Left by Abderrhaim OUabiad on Dec 14, 2017 10:56 PM

Your comment:

 (will show your gravatar)