Introduction
Contained Databases is a new feature which is available in SQL Server 2012. A contained database is a database that will store all its metadata within the database thereby not storing any configuration information within the master database of the SQL Server Instance where the Contained Database is created. A contained database is isolated from other databases which are available on the instance of SQL Server.
The biggest advantage of using this feature is it makes it easier to move a contained database from one instance to another instance of SQL Server 2012. In this article we will take a look at how to configure and implement Contained Database feature of SQL Server 2012.
How to Configure a Contained Database Feature in SQL Server 2012
Different ways to configure Contained Database feature in SQL Server 2012 are:-
- Using SQL Server Managment Studio
- Using a TSQL script
Configure Contained Database Feature in SQL Server 2012 Using SSMS
1. Connect to SQL Server 2012 Instance using SQL Server Management Studio and then right click the SQL Server Instance and choose Properties from the drop down list.
2. In Server Properties choose Advanced Page and set the value as True for Enable Contained Databases as highlighted in the below snippet and then click OK to save the changes.
Configure Contained Database Feature in SQL Server 2012 Using TSQL Code
Execute the below TSQL to configure contained database feature in SQL Server 2012 at instance level.
USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GO
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GO
Once the above TSQL Script is executed successfully the Contained Database feature will be enabled at the SQL Server 2012 instance level.
How to create a Contained Database in SQL Server 2012
Once Contained Database feature is enabled successfully at SQL Server 2012 Instance Level then the next step will be to create a contained database.
1. Connect to SQL Server 2012 Instance
2. In Object Explorer, right click Databases and choose New Databases…. option from the drop down menu
3. In General Page enter the name of Contained Database as ContainedDatabaseSQL2012 and click on Options page on the left side pane of New Database window
4. In Options page choose Containment Type as “PARTIAL” from the drop down as shown in the below snippet
5. Finally click OK to create your first Contained Database in SQL Server 2012.
 |
How to create a Contained Database in SQL Server 2012 Using TSQL Query
Execute the below mentioned TSQL query to create a Contained Database.
CREATE DATABASE [ContainedDatabaseSQL2012]
CONTAINMENT = PARTIAL
ON PRIMARY
(
NAME = N'ContainedDatabaseSQL2012',
FILENAME = N'D:\MSSQL\DATA\ContainedDatabaseSQL2012.mdf',
SIZE = 3072KB,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'ContainedDatabaseSQL2012_log',
FILENAME = N'D:\MSSQL\DATA\ContainedDatabaseSQL2012_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10%
)
GO
CONTAINMENT = PARTIAL
ON PRIMARY
(
NAME = N'ContainedDatabaseSQL2012',
FILENAME = N'D:\MSSQL\DATA\ContainedDatabaseSQL2012.mdf',
SIZE = 3072KB,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'ContainedDatabaseSQL2012_log',
FILENAME = N'D:\MSSQL\DATA\ContainedDatabaseSQL2012_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10%
)
GO
Create a SQL Server or Windows Login which will have access to the Contained Database
A contained database can be accessed by a SQL Server or a Windows Login. This login should be created within the Contained Database.
- To create a login Expand Databases …. Expand ContainedDatabaseSQL2012…. Expand Security …. Right click Users and select New User… from the drop down menu as shown in the snippet below.
- To create a New Database User, enter User Name, enter Password, Confirm Password and enter Default schema as “dbo” as shown in the snippet below and click OK.
Create New SQL Server User Using TSQL code
USE [ContainedDatabaseSQL2012]
GO
CREATE USER [ContainedDatabaseLogin]
WITH PASSWORD=N'Str0ngP@$$', DEFAULT_SCHEMA = [dbo]
GO
GO
CREATE USER [ContainedDatabaseLogin]
WITH PASSWORD=N'Str0ngP@$$', DEFAULT_SCHEMA = [dbo]
GO
How to Connect to Contained Database
In Connect to Server, Login tab enter Server Name, choose Authentication, enter Login and Password as shown in the below snippet and click on Connection Properties tab.
In Connection Properties tab specify the database name as shown in the snippet below and finally click the Connect button.
In the below snippet you could see that using the contained database login you could just see only one database to which your login belongs and the login doesn’t have access to Database Engine.
Conclusion
Now that you have successfully contained database you can easily easily create the Backup of the Contained Database and Restore it on any other instance of SQL Server 2012.
Read more: http://www.mytechmantra.com/LearnSQLServer/Contained-Databases-SQL-Server-2012/#ixzz3h4czgjji
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook
