Contained Databases in SQL Server 2012
Related Topic: TSQL Enhancements in SQL Server 2016>
Next Topic: COMPRESS and DECOMPRESS
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.
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
sp_configure 'contained database authentication', 1
RECONFIGURE WITH OVERRIDE
sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
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
NAME = N'ContainedDatabaseSQL2012',
FILENAME = N'D:\MSSQL\DATA\ContainedDatabaseSQL2012.mdf',
SIZE = 3072KB,
FILEGROWTH = 1024KB
NAME = N'ContainedDatabaseSQL2012_log',
FILENAME = N'D:\MSSQL\DATA\ContainedDatabaseSQL2012_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10%
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
CREATE USER [ContainedDatabaseLogin]
WITH PASSWORD=N'Str0ngP@$$', DEFAULT_SCHEMA = [dbo]
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.
• COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
• STRING_SPLIT and STRING_ESCAPE T-SQL Enhancement in SQL Server 2016
• SERVERPROPERTY T-SQL Enhancement in SQL Server 2016
• TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016
• DROP IF EXISTS T-SQL Enhancement in SQL Server 2016
Thank you for taking your time to read
this article. Let's be Connected....
- How to Detect Virtual Log Files in SQL Server Transaction Log File
- How to Attach Database Without a Transaction Log File in SQL Server
- How to Drop Database in SQL Server by Closing Existing Connections
- Identify Deadlocks Using Graphical Deadlock Chain Event in SQL Server Profiler
- COPY_ONLY Backups in SQL Server a Rarely Used Feature by DBAs
Last Updated On: May 14, 2016
Please leave your Valuable Comment or Let us know how this article helped you: