Typically an Analysis Services database has a DatabaseID the same as the database name. However, if you rename a database, the DatabaseID will stay the same. This can be a source of confusion and it can lead to useful operational techniques.Whenever anyone connects to an Analysis Services database, they do so by selecting a particular database name. So when you rename a database, users will see and use the new name. However, SSIS packages that perform Analysis Services processing tasks select the database by the DatabaseID. Also, Analysis Services keeps the DatabaseID as a unique identifier. So when you rename an Analysis Services database, existing SSIS packages will be looking for the old DatabaseID.
So if you have an Analysis Services database, say EasternMining, and you rename it to Eastern Mining, then users will only connect to it by asking for Eastern Mining, which is fine. However if you try to restore a database called EasternMining (which doesn't exist anymore) it will fail, even though you have enabled Allow Database Overwrite. The message will be something like Backup and restore errors: An object with the same 'databaseid' ID but different 'databasename' Name already exists on the instance. (The AllowOverwrite flag only applies to the Object ID, not to the object name.) I find the message a bit misleading, but what it is saying is that a database with that DatabaseID exists already.
Unfortunately, you cannot change the databaseID, without doing something like backing up and restoring the database. Interestingly, an xmla create database statement will overwrite an existing database and DatabaseID. If you want to know the DatabaseID for an Analysis Services database, just right click on the database and select properties.
I have previously blogged on how you might use this to maintain close to continuous availability when you need to deploy a new Analysis Services cube structure. http://richardlees.blogspot.com/2009/05/new-analysis-services-database-version.html
5 comments:
Unfortunately this "confusion" with DatabaseID and DatabaseName extends through to the Deployment operation in BIDS. I have blogged about it here http://geekswithblogs.net/darrengosbell/archive/2008/09/28/ssas-deploying-to-renamed-databases.aspx (including a link to an issue I logged on the connect site)
Hi Richard,
Thanks for this tip. As Darren noted this is a really frustrating error when trying to use the deployment wizard. I'm glad that you presented this solution.
Mark W
I solved this particular scenario by creating a New SSAS project with desired 'DATABASEID'. Then I added all other DSVs, Dimensions to the existing cube. I also gave the same database name when deploying. I dropped the existing database with different databaseid and I deployed the newly created Analysis Services Database.
That's good Sandeep. I actually use the DatabaseID when I am publishing a new version of a production cube. This helps minimise the downtime for cube users. See http://richardlees.blogspot.com/2009/05/new-analysis-services-database-version.html
Not being able to rename the database id is a current limitation with SSAS, I have created the following Connect Item so Microsoft will hopefully resolve the issue: https://connect.microsoft.com/SQLServer/Feedback/Details/1134252
Post a Comment