An Introduction to Sql 11 (Code Name Denali) -Part IV (Contained Database in CTP 1)
Table of Content
- Introduction
- Background
- The Problem with the current (non contained) database
- What is Contained Database?
- Some terms we should know
- A four step approach to create a Contained Database
- Converting a Non Contained Database to a Contained Database
- Back up a contained database
- Restore up a contained database
- Some more facts about contained database
- References
- Conclusion
One of the hottest and awesome developments by Microsoft in the technology field was come into picture on 8th November, 2010 when they released the
Community Technology Preview 1 (CTP 1) version of Sql Server 2011(Code name Denali). The
CTP1 is available both in 32-bit and 64-bit versions.
As expected, Denali has brought some new features for Sql lovers may that be developers, Administrators or Business Intelligence (BI) professionals.
Part I describes about the features and enhancements in SSMS. Part II focuses on the new development and enhanced features from T-Sql perspective. Part III looks into the enhancements made from SSIS angle. In this series we will explore the Contained Database feature .
We will focus on other features of Denali CTP-I in the subsequent series.
In the last few years, Microsoft has brought many technologies under the developers' hood. A drastic change has been made in the Sql Server jargon with the advent of Sql Server 2005(code name Yukon) and in the subsequent releases like Sql Server 2008(code name Katmai) and Sql Server 2011(code name Denali), the same pace has been kept with introduction to new features and enhancements as well as improvements. This article will give us an in-depth view about the contained database, what it is, its usage and many more stuffs.
Before going to describe what Contained Database is, let us see the reason for which it has come into existence.
Some of the problems with the current database are listed as under
- Loss of Information during database movement or deployment
When we move the database from one Sql Server instance to another, then information such as login, job agent information cannot be shipped. This is because these information are application specific and henceforth resides inside the Sql Server instance. Recreating such task again on the new Sql server instance is a time consuming, error prone process.
- Diversion from Application Development to Application Deployment
There may be hindrance while deploying the application in the server as there is a high possibility of environmental mismatch e.g. there may not permission about new login creation, command line facility like "xp_cmdshell" may be disable, database collation use by the application may not be same after deployment as it is initially determine by the server collation.
- Security concern in Application Administration
It becomes difficult to administer and maintain a single database because the login and job agent information is available across the database instance, thereby, allowing the user to grant permission for the entire instance which yields unnecessary access to other database leading to security vulnerability.
As the name suggests, it is a kind of data base that is self contained i.e. it carries all the database settings and metadata information needed to set up the database. It is instance or server independent, has no external dependencies and has self contained mechanism of authenticating users. As it is independent of database instance, it ensures that database collations will not be an issue while deploying onto a different server.
A contained database, keeps all necessary information and objects in the database like Tables, Functions, Constraints, Schemas, types etc. It also stores all application-level objects in the database, such as Logins, Persisted error messages, Application-level agent jobs, System settings, Linked server information etc.
The advantage of having such database is that they can be moved easily to another server and we can start working on it instantly without the need of any additional configuration since they do not have any external dependencies.
By this time, I think we have some theoretical concept as what contained database is, why it came into picture. Now it's time to have some hands on in it. The below is a four step approach of creating a Contained database.
Step 1: Enable "Contained Database Authentication" property on the SQL Server instance Level.
Step 2: Create a database and set its CONTAINMENT property to Partial.
Step 3: Create a Contained User within the newly created contained database.
Step 4: Login to the Contained database using the user that exists in Contained Database.
We will look into each and every steps in the below paras.
--Enabled Advanced options
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
--Enabled Database Containment
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go
Step 2: Create a database and set its CONTAINMENT property to Partial 1.Create a new database by the name "TestContainedDB".
2.Right click on the "TestContainedDB" and click "Properties"
3.Visit the Options tab and select the Containment type as "Partial"
The same can be achieved by using the below T-Sql script
USE [master]
GO
CREATE DATABASE [TestContainedDB]
CONTAINMENT = PARTIAL
ON PRIMARY
( NAME = N'TestContainedDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NILADRIDENALI\MSSQL\DATA\TestContainedDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestContainedDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NILADRIDENALI\MSSQL\DATA\TestContainedDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TestContainedDB] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestContainedDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TestContainedDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestContainedDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestContainedDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestContainedDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestContainedDB] SET ARITHABORT OFF
GO
ALTER DATABASE [TestContainedDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TestContainedDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestContainedDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestContainedDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestContainedDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestContainedDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TestContainedDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestContainedDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestContainedDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestContainedDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestContainedDB] SET DISABLE_BROKER
GO
ALTER DATABASE [TestContainedDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestContainedDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestContainedDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TestContainedDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TestContainedDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestContainedDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TestContainedDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TestContainedDB] SET READ_WRITE
GO
ALTER DATABASE [TestContainedDB] SET RECOVERY FULL
GO
ALTER DATABASE [TestContainedDB] SET MULTI_USER
GO
ALTER DATABASE [TestContainedDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TestContainedDB] SET DB_CHAINING OFF
GO
ALTER DATABASE [TestContainedDB] SET DEFAULT_FULLTEXT_LANGUAGE = 1033
GO
ALTER DATABASE [TestContainedDB] SET DEFAULT_LANGUAGE = 1033
GO
ALTER DATABASE [TestContainedDB] SET NESTED_TRIGGERS = ON
GO
ALTER DATABASE [TestContainedDB] SET TRANSFORM_NOISE_WORDS = OFF
GO
ALTER DATABASE [TestContainedDB] SET TWO_DIGIT_YEAR_CUTOFF = 2049
GO
Step 3: Create a Contained User within the newly created contained database 1.Visit the "Users" node under the "Security" node of the "TestContainedDB".
2.Right click on the "Users" node and click on the "New User".
3.Create any user and password. Here the user name is : TestUser and the Password is : testuser
4.Check the "db_owner" checkbox from the Membership tab.
The same can be achieved by using the below T-Sql script
USE [TestContainedDB]
GO
CREATE USER [TestUser]
WITH PASSWORD='testuser',
DEFAULT_SCHEMA=[dbo]
GO
Once done, we can find that our user has been created
Step 4: Login to the Contained database using the user that exists in Contained Database After the third step, let's log out of SSMS. Again login to SSMS as carry out the below steps
1.In the login name and password boxes, enter the login name and the password of the user created for the "TestContainedDB" i.e. User Name as "TestUser" and Password as "testuser".
2.Next, click on the "Options" button and visit "Connection Properties"
3.In the Connect to Database box, let us type in the name of the contained database which is "TestContainedDB" in our case
4.Click on the "Connect" button now and we are in our contained environment
In this section we will look into how we can convert a Non Contained Database to a Contained Database.
Initial setup
Create a database say "NonContainedDB" by issuing the below script
USE [master]
GO
CREATE DATABASE [NonContainedDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'NonContainedDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NILADRIDENALI\MSSQL\DATA\NonContainedDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'NonContainedDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NILADRIDENALI\MSSQL\DATA\NonContainedDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [NonContainedDB] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [NonContainedDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [NonContainedDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [NonContainedDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [NonContainedDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [NonContainedDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [NonContainedDB] SET ARITHABORT OFF
GO
ALTER DATABASE [NonContainedDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [NonContainedDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [NonContainedDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [NonContainedDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [NonContainedDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [NonContainedDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [NonContainedDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [NonContainedDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [NonContainedDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [NonContainedDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [NonContainedDB] SET DISABLE_BROKER
GO
ALTER DATABASE [NonContainedDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [NonContainedDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [NonContainedDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [NonContainedDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [NonContainedDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [NonContainedDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [NonContainedDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [NonContainedDB] SET READ_WRITE
GO
ALTER DATABASE [NonContainedDB] SET RECOVERY FULL
GO
ALTER DATABASE [NonContainedDB] SET MULTI_USER
GO
ALTER DATABASE [NonContainedDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [NonContainedDB] SET DB_CHAINING OFF
GO
Next add a table say "tbl_Players" by issuing the below script
-- Drop the table if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Players' AND type = 'U')
DROP TABLE tbl_Players
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE tbl_Players (
PlayerID INT IDENTITY,
PlayerName VARCHAR(15),
BelongsTo VARCHAR(15),
MatchPlayed INT,
RunsMade INT,
WicketsTaken INT,
FeePerMatch NUMERIC(16,2)
)
--Insert the records
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 400000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,800000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988000)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 999999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 888888)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444499)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 22222)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,99999)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,12)
INSERT INTO tbl_Players(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)
Also, add a stored procedure to the database by issuing the below script
If Exists (Select * from sys.objects where name = 'usp_SelectRecordsByPlayerName' and type = 'P')
Drop Procedure usp_SelectRecordsByPlayerName
Go
-- Create the stored procedure
Create Procedure [dbo].[usp_SelectRecordsByPlayerName]
( @PlayerID int )
As
Begin
Select
PlayerID
,PlayerName
, BelongsTo
, MatchPlayed
,RunsMade
,WicketsTaken
,FeePerMatch
From
tbl_Players
Where PlayerId = @PlayerID
End
This means that, now we have a database "NonContainedDB" that has two database objects a table "tbl_Players" and a stored procedure "usp_SelectRecordsByPlayerName". At present this database is a non contained one. Our objective is to make this database to a contained one.
Step 1:
As a first step we will create a new server-level login and will create a user for that login for the "NonContainedDB". Let us issue the below script against the master database
--Create a login on the server
CREATE LOGIN NonContainedUser
WITH PASSWORD = 'somepassword@123'
--Create a "non-contained" users for the login on the server
USE NonContainedDB
GO
CREATE USER NonContainedUser FOR LOGIN NonContainedUser
GO
Step 2:
Now let us identify the non contained database objects for the database "NonContainedDB". For that reason, let us issue the below script
USE NonContainedDB
GO
SELECT
class_desc
,feature_name
,feature_type_name
FROM sys.dm_db_uncontained_entities
The output is as under
We can ignore ROUTE. So there are two non contained database objects as highlighted.
For identifying the non-contained user in the database we can issue the below script
USE NonContainedDB
GO
SELECT dp.name
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
AND sp.is_disabled = 0
And it will give the below result
Step 3:
Right click on the "NonContainedDB" and click "Properties". Visit the Options tab and select the Containment type as "Partial".
Alternatively, we can achieve the same by issuing the below T-Sql script
USE master
GO
ALTER DATABASE NonContainedDB SET CONTAINMENT=PARTIAL;
GO
And after this issue the below script
USE NonContainedDB
GO
EXEC sp_migrate_user_to_contained @username = N'NonContainedUser',
@rename = N'keep_name',
@disable_login = N'disable_login'
The sp_migrate_user_to_contained stored procedure is needed in order to contain the users that are associated with Sql Server logins. It will convert the Sql Server logins to users with password.
Now let us again run the same query for identifying uncontained users in the database
USE NonContainedDB
GO
SELECT dp.name
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
AND sp.is_disabled = 0
And the result is as under
So we can figure out that, the "NonContainedUser" no longer appears. This means that it has been changed to contained user. Also the login has been disabled at the server level.
Step 4:
Let's log out of SSMS. Again login to SSMS. In the login name and password boxes, enter the login name and the password of the user created for the "TestContainedDB" i.e. User Name as "NonContainedUser" and Password as "somepassword@123". Next, click on the "Options" button and visit "Connection Properties". In the Connect to Database box, let us type in the name of the contained database which is "NonContainedDB" in our case. Click on the "Connect" button now and we are in our contained environment
We can take backup of a Contained Database in the same way we take for Uncontained Database. We can do this either through
a)Without T-Sql Script
1.Login to SQL 11 ("Denali") via the SSMS
2.In the object explorer, navigate to "TestContainedDB"
3.Right click, go to Tasks->Backup
b)With T-Sql Script
We can even take the database backup by issuing the below script
BACKUP DATABASE TestContainedDB
TO DISK='<File Path>\ TestContainedDB.bak'
Like backup, we can restore the database either through
a)Without T-Sql Script
1.Login to SQL 11 ("Denali") via the SSMS
2.In the Object Explorer, navigate to the "Databases" node
3.Right-click, and go to Restore Database
b)With T-Sql Script
RESTORE DATABASE TestContainedDB
FROM DISK='<File Path>\TestContainedDB.bak'
But we may encounter the below error while we run the script
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.
It is clear from the message that, we need to Enable "Contained Database Authentication" property on the SQL Server instance Level which is turned off by Default. Issuing the below script fixes the problem
--Enabled Advanced options
sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
--Enabled Database Containment
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go
Alter Database <Database Name>
does not work anymore in case of contained database. Instead, a new option called CURRENT has been added which ensures that if we move the database to a new instance or change the database name, the command will still work.
Henceforth, the statement
ALTER DATABASE CURRENT
Works for contained database.And
Alter Database <Database Name>
Works for non contained database.
Understanding Contained Databases
In this article, we have seen what contained database is, why it came into existence, how it solved the issues created by non-contained database, how we can create a contained database, conversion of a non-contained database to a contained one etc. Hope this article has given the necessary idea to start working on contained database. We will explore more features of Denali CTP 1 in the subsequent articles.
Thanks for reading
