This time we are going to talk about the contained database and the problems that it solves.
I guess all of us have had the same problem. When we restore a backup from one SQL Server to another, we cannot logon because the logins are stored in the master database. When we move a database in another server, the logins are not included.
In SQL Server we need logins and user databases. When we restored a database, we may need to move the logins as well. In SQL Server there were many ways to move the logins. The first method is the copy database wizard using the SQL Server Management Studio. The image below shows how you can launch the wizard.
In the steps of the wizard, we have an option to copy additional objects like the logins, master stored procedures, jobs and other objects, as shown below.
Another method is the SQL Server Data Tools (SSDT) in SQL Server 2012 or in Business Intelligence Development Studio (BIDS) in earlier versions. You can create a SQL Server Integration Services project and use the transfer logins tasks as shown below:
The third method is the favorite one and it uses stored procedures. For more information you can refer to this link: http://support.microsoft.com/kb/246133.
As you notice, it was not easy to transfer the logins. That’s why Contained Databases were added to SQL Server 2012.
Contained databases
The main idea is to remove the independency between the Database Engine and the database. The contained databases are independent databases that do not require a login. Let’s start with an example.
Getting started
In this example I am going to create a contained database, backup it and restore it in another SQL instance. To start we are going to enable the contained database authentication in both SQL Servers:
exec sp_configure 'contained database authentication', 1; GO RECONFIGURE ; GO
Now let’s create a database:
CREATE DATABASE testCD
Let’s enable the contained database in the testCD database:
USE [master] GO ALTER DATABASE [TestCD] SET CONTAINMENT = PARTIAL GO
We are going to create 1 user in the new database:
USE [testCD] GO CREATE USER [sqlCentral] WITH PASSWORD=N'sqlcentral' GO
The next step is to backup the database:
BACKUP DATABASE [testCD] TO DISK = N'C:\backup\testCD.bak'
On another SQL Server, make sure that you enabled the contained database property for the instance and then restore the database:
USE [master] RESTORE DATABASE [testCD] FROM DISK = N'C:\backup\testCD.bak'
And the final step is to login to the testCD database using the user created:
You will need to press the Option button and go to the connection properties. In the Connect to database, write testCD and finally press the connect button.
If everything is OK, you will be able to connect to the database without a login !
Conclusion
In this example we explained the main problems caused by the logins and databases and we also reviewed how to solve the problem in earlier versions and finally with SQL 2012.
References
