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 (N'master', N'model', N'msdb', N'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
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
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:
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.