I have been playing around earlier CTP versions of Denali and focus was mainly on contained databases.Today I had a chance to review Contained Databases on SQL 2012 RC 0 and this post mainly revolve around the same.

Microsoft article http://msdn.microsoft.com/en-us/library/ff929071(v=sql.110).aspx provides a very clear explanation of contained database as   ‘A contained database is a concept in which a database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed. Users connect to the database without authenticating a login at the Database Engine level. Isolating the database from the Database Engine makes it possible to easily move the database to another instance of SQL Server. Including all the database settings in the database enables database owners to manage all the configuration settings for the database.’

I started to build my first contained database of SQL 2012 RC 0 –

1. Using SSMS I first gave a database name called ContainedDatabase

2. Under options you can find a new containment type called Partial,I choose this as this is one of the requirements for contained databases.

3.  I clicked OK to create the database and as expected an error came up directly

The message is very much informational and it clearly states that sp_configure value ‘contained database authentication’ must be 1 in order to create a contained database.

4. I changed the value as per the requirement.

SP_CONFIGURE ‘show advanced options’,1
RECONFIGURE
GO
SP_CONFIGURE ‘contained database authentication’,1
RECONFIGURE
GO
SP_CONFIGURE ‘show advanced options’,0
RECONFIGURE
GO

5. I then tried to create the database by clicking OK,and it worked and the database was created.

6. Now to prove the point ‘A contained database is a concept in which a database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine’ I decided to create a user for the contained database.

I selected SQL User with a password and under membership I selected db_owner

7. The user was created and if I  try to connect to the database using this user directly you will get the famous 18456 error

Error: 18456, Severity: 14, State: 5.
Login failed for user ‘ContainedUser’. Reason: Could not find a login matching the name provided.

This happens as I had missed to mention the database name specifically while connecting

8. So now after giving the database name explicitly I am able to connect using the contained user!

Now as I was able to connect to the database the next check was to ensure that I can restore this contained database to another server/instance.Earlier before contained database,we will have to move the logins too when ever we do a restore.

I took  a backup of the contained database using simple TSQL –

BACKUP DATABASE [ContainedDatabase]
TO DISK=’C:\Test_Backups\ContainedDatabase.bak’

I already had setup another instance of SQL 2012 in the same server,so I went ahead and did a restore using TSQL –

RESTORE DATABASE [ContainedDatabase]
FROM DISK=’C:\Test_Backups\ContainedDatabase.bak’
WITH
MOVE ‘ContainedDatabase’ TO ‘C:\SQL_Data\ContainedDatabase.mdf’,
MOVE ‘ContainedDatabase_log’ TO ‘C:\SQL_Log\ContainedDatabase_log.ldf’,
REPLACE,
STATS=1

Yes,you might have guessed it right! An error comes up as soon as I fire the restore command –

Msg 12824, Level 16, State 1, Line 1

The sp_configure value ‘contained database authentication’ must be set to 1 in order to restore a contained database.  You may need to use RECONFIGURE to set the value_in_use.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

The solution for this is to change the sp_configure value to 1

SP_CONFIGURE ‘show advanced options’,1
RECONFIGURE
GO
SP_CONFIGURE ‘contained database authentication’,1
RECONFIGURE
GO
SP_CONFIGURE ‘show advanced options’,0
RECONFIGURE
GO

After changing the value,the restore command work well and I was able to restore the database to the second Instance.

As expected the earlier user which we had created,ie ContainedDBUser was able to connect to this database under Instance B without any issues.

I will discuss more on contained databases in the coming blog posts and thanks for reading.