July 8, 2011 | SQL Server

SQL Server v.Next (Denali) : Breaking change to system databases / database_id / DB_ID()

Currently you may have code that makes decisions based on sys.databases.database_id or the built-in DB_ID() function. I have seen a lot of code out there that checks the database_id, and behaves differently based on the assumption that a value between 1 and 4 (as well as 32,767 – the resource database) means it is a system database, and anything in between (from 5 to 32,766) means it is a user database. I also see code that checks the name of the database (IN ('master', 'model', 'msdb', 'tempdb')).

In Denali, if you install SQL Server Integration Services, all of the packages and other metadata, as well as operational metrics, are stored in an "SSIS Catalog" now. The catalog is called SSISDB but, other than the fact that it also exists under the Integration Services node in Management Studio's Object Explorer, it is just another database and adopts the next available database_id. On my system, with 11 user databases, the database_id for SSISDB was 16. As you probably already know, there is nothing in sys.databases to indicate that a database is shipped with SQL Server or is otherwise designated as a "system" database. And there is no revealing property exposed by DATABASEPROPERTYEX(), either (nor by DATABASEPROPERTY(), which of course is deprecated in Denali anyway).

What this means is that you *may* want to consider updating any such code to also check for the name SSISDB. Why do I say *may*? Well, I don't know all of the scenarios you're currently using that cause you to differentiate between system and user databases. It could be for backup purposes, index maintenance, making snapshots, who knows… depending on the task, you may want to conditionally consider SSISDB a system database or a user database. What I do know is that if you want to consider SSISDB a system database, you won't be able to lump it in with other system databases relying solely on database_id / DB_ID().

As another note, currently it seems there is no way to customize the name of the SSIS catalog when you add Integration Services (whether that is during initial SQL Server setup, or after the fact). What this means for folks who already have a user database named SSISDB before they install Integration Services and create the catalog? Well, unless your database magically has the schema that the UI expects, when you try to expand Integration Services in Object Explorer in SSMS, you get this exception (click to embiggen):

 

Here are the interesting and search-friendly parts of the error message:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'SSISDB.catalog.catalog_property'. (Microsoft SQL Server, Error: 208)

If you drop or rename the existing SSISDB database, then Management Studio works as expected – you can right-click Integration Services and choose "Create Catalog" and it will create the SSISDB database for you.

Since we can't test upgrades using the CTPs (as they only ship in Evaluation Edition), I am not sure what is going to happen if you upgrade in place when you have SSIS installed *and* you have your own user database called SSISDB. It's possible, I guess, that SSIS will just take over that DB and inject its own schema, but then what if there are conflicts (e.g. you already have a schema named catalog).

Also not sure what happens to all of the surrounding functionality if you decide to manually rename the database, I'm not sure. I'm not an SSIS guy, so I don't really have the means to test this, and I don't see any official documentation or blogs that talk about this scenario.

For some of this, I guess there's no way to know until RTM – but to play it safe, if you currently happen to have a database called SSISDB, you might consider changing it now if you use SSIS and are planning to move to Denali.

 

5 comments on this post

    • Peter - July 8, 2011, 5:56 PM

      Similar in a way to the Reporting Services databases, although at least you can sort of select a name for those.

    • Paul Randal - July 10, 2011, 4:28 AM

      Good to know Aaron – thanks for publicizing

    • Ray - July 10, 2011, 5:38 AM

      Heeeey – a new word in the English language
      (click to embiggen):
      That should stymy  those overseas developers for a good 6 months 🙂

    • Charles Kincaid - July 11, 2011, 4:45 PM

      This is kind of like certain table namea.  For example one project the Data Technologies group had some real estate holdings.  Can you guess a perfectly logical table name here?  Imagine the fun of then trying to let management studio draw an ERD.
      This then does not come as a complete surprise.  Thanks for the heads up.

    • Pradeep - July 7, 2016, 11:06 AM

      Thanks Aaron. Great stuff.

Comments are closed.