In recent years, Microsoft has introduced many interesting technologies that are firmly in the arsenal of developers. Major changes were incorporated into SQL Server 2005, after which SQL Server 2008 developed and enhanced success. Denali carries a lot of new tools, as well as extensions of functionality for existing ones. In this article we will take a closer look at one of the new tools, which I am sure will appeal to database developers. This tool feature is offline databases (Contained Database). Consider what they are, how to work with them, to which other things can be applied.

What is wrong with current databases?

Before proceeding to the description of the essence of independent databases, we consider why they were invented and what the current implementation does not suit the developers.

Here are some of the key issues:

• Loss of information during the deployment of the base or move it between servers.
  While the database is being moved between servers, information such as logins, passwords, SQL Server Agent jobs, etc. cannot be moved along with the database. Because this information belongs to the database server. Re-creating the objects mentioned by hand is not the most pleasant thing, which also takes a lot of time and does not guarantee protection against errors.
• Differences between Application Development and Deployment.
  When deploying an application, developers may face various troubles, ranging from the mismatch of fine settings of the environment, such as permissions to create new logins, a disabled command line (xp_cmdshell), server language settings.
• Security issues in the administration of the application.
  It is very difficult to administer and maintain a stand-alone database because, for example, SQL Server Agent jobs are available across the entire server, which entails an increase in the privileges of individual users. However, these elevated rights give access to other parts of the server that are not desirable to open. All of this can lead to serious security problems.

After identifying the key shortcomings of the existing bases, we proceed to the description of a new type.
')

Offline databases

Already, based on the name and the described shortcomings of the old bases, I think you can guess what the beauty of the new type of bases is. Offline databases store all the information you need to work and configure yourself. Such databases are completely independent of the SQL server settings, do not have external dependencies, and contain all authentication mechanisms. It also does not matter what language setting is set on the server.

Tables, functions, procedures, constraints, schemas, types, libraries, views, logins, SQL Server Agent jobs, system settings, linked servers (all linked servers) are all stored in the database.

Naturally, the main advantage of such a base will be the ease of unfolding and transfer. It is enough just to deploy the base and it is immediately ready for operation. No more forgotten scripts for users, their roles, agents, and so on.

Terms to Remember

Application boundary - the boundary between the server instance and the application code. By application code is meant the entire database with all the objects that may be needed in the course of work.

Application Model (application model) - within the boundaries of the application there is a place where the development and management of the application is underway.

Contained (contained) - a user entity that is completely contained within the application boundary.

Uncontained (not contained) - user entity that crosses the boundaries of the application.

Non- contained database (dependent database) —a database for which property containment = NONE. The database depends on some objects belonging to the server instance.

Fully contained database (offline database) - a database that does not allow any objects or functions to cross the boundaries of the application.

Partially contained database (partially dependent database) - a database that allows some objects to operate across application boundaries. Available in CTP 1.

Contained user (offline user)

There are two types of such users:

• A user who is authorized by the database.
• A user who uses Windows tools and his data is not contained in the database.

4 steps to create an offline database

I think that at the moment there is already enough theoretical knowledge and concepts about how such a database works, and it is time to warm up a little “in the field”. The following 4 steps describe how to create an offline database.

Step 1. Allow the use of offline databases at the server level.

Step 2. Create a database and set the autonomy mode as partial. The CONTAINMENT property must be Partial.

Step 3. Create a standalone user in the new database.

Step 4. Enter the new database under the account of an offline user.

Now we will consider each step in detail and in pictures.

Step 1. Allow the use of offline databases at the server level.

Join an instance of the new SQL Server 2011 and from the Object Explorer (Object Explorer) open the context menu for the server. In the context menu, select the Properties item.



Go to the Advanced page and you need to set the value of the Enable Contained Databases property to TRUE .



The same can be achieved using a script.

 --Enabled Advanced options --       Advanced 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 the autonomy mode as a partial

Create a new database and call it TestContainedDB .

After creation, open its properties through the context menu



Open the Options tab and select for the Containment type option : the Partial property.



The same can be achieved using a script.

  USE [master]
 GO

 CREATE DATABASE [TestContainedDB]
  CONTAINMENT = PARTIAL
  ON PRIMARY
 LOG ON
 GO

 ALTER DATABASE [TestContainedDB] SET COMPATIBILITY_LEVEL = 110
 GO 

Step 3. Create a standalone user in the new database.

In the new database, go to the Security node, then Users, and use the context menu to create a new user.



Set the account name and password. For example, let it be testuser \ testuser.



Specify that the user will own the database. To do this, on the Membership page, check the db_ owner checkbox .

The same actions can be performed using TSql

  USE [TestContainedDB]
 GO
 CREATE USER [TestUser]
 WITH PASSWORD = 'testuser',
 DEFAULT_SCHEMA = [dbo]
 GO 

Once the described actions are completed, you can make sure that the user has appeared in the system.

Step 4. Enter the new database under the account of an offline user.

To demonstrate the step, you must complete the work in SSMS, and log in again following the steps described.





In the fields for user name and password, enter the information that was set during the creation of the user for the autonomous database. In our case, this is testuser \ testuser.



After that, you need to click on the Options button and go to the Connection Properties tab.



On this tab, you must specify which database we are going to join. In this case, it is TestContainedDB .

Now you can press the button Connect, and we will find ourselves in the autonomous environment of the base.

Base conversion to autonomous

I think after describing the advantages of an offline database and how to create it, you wondered whether it is possible to transfer the existing database to an offline mode. Can. Such a process will now be demonstrated. Since the demonstration will be conducted on a test base, we will create it using the script below:

  USE [master]
 GO

 CREATE DATABASE [NonContainedDB]
  CONTAINMENT = NONE
  ON PRIMARY
 GO

 ALTER DATABASE [NonContainedDB] SET COMPATIBILITY_LEVEL = 110
 GO

 IF (1 = FULLTEXTSERVICEPROPERTY ('IsFullTextInstalled'))
 begin
 EXEC [NonContainedDB]. [Dbo]. [Sp_fulltext_database] @action = 'enable'
 end
 GO 

Then create a table with data.

  - Drop the table if it exists
 - delete 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 a table
 CREATE TABLE tbl_Players (
	 PlayerID INT IDENTITY,
	 PlayerName VARCHAR (15),
	 BelongsTo VARCHAR (15),
	 MatchPlayed int,
	 Runsade int,
	 WicketsTaken INT,
	 FeePerMatch NUMERIC (16.2)
 )

 --Insert the records
 - make entries in the table
 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, 3,600,000)

 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) 

To complete the picture, add the stored procedure.

  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
		 , Runsade
		 , Tickets tickets
		 , FeePerMatch
	 From
	 tbl_Players
	 Where PlayerId = @PlayerID
 End 

As a result, we should have a database, a table and a stored procedure. Now the database is working in a normal, dependent mode and our goal will be to turn it into a standalone database.

Step 1

In the first step, you will need to create a new user at the server and user level for the database. This can be done using this script:

  --Create a login on the server
 - Create a user at the server level
 CREATE LOGIN NonContainedUser
 WITH PASSWORD = 'somepassword @ 123'

 --Create a "non-contained" users for the login on the server
 - we create the autonomous user
 USE NonContainedDB
 GO
 CREATE USER NonContainedUser FOR LOGIN NonContainedUser
 GO 

Step 2

Now we need to determine which objects belong to the associated database. To do this, you can run the following code

  USE NonContainedDB
 GO

 SELECT
	 class_desc
	 , feature_name
	 , feature_type_name
 FROM sys.dm_db_uncontained_entities 

The result should be as in the screenshot below.



You can ignore ROUTE. So, according to the script, we have 2 objects in the associated database.

To determine the users belonging to the server, you can run the following 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 

What will give us as a result of

Step 3

Based on NonContainedDB, you need to call the context menu and select Properties . Then go to the Options tab and select Partial for the Containment type property.

Another way to do this is to write a script.

  USE master
 GO
 ALTER DATABASE NonContainedDB SET CONTAINMENT = PARTIAL;
 GO 

After the configuration is set, it is necessary to migrate users to an autonomous base.

  USE NonContainedDB
 GO
 EXEC sp_migrate_user_to_contained @username = N'NonContainedUser ',
                                   @rename = N'keep_name ',
                                   @disable_login = N'disable_login ' 

The procedure sp_migrate_user_ to_ contained is needed for migrating users to an autonomous base. It converts server-level users to autonomous database users. After the procedure, you can run the script again to determine the dependent users.

  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:



You can make sure that NonContainedUser no longer appears. This means that the user has been changed and his account has been blocked at the server level.

Step 4.

Disconnect from the current server and call the connection form. In the fields for the name and password, enter the data of the user we have migrated to the autonomous base. Further in the connection options, specify everything as mentioned above, in the connection section with the autonomous database.

After connecting to the row about the database should be something similar.

Backup offline database.

It is done in the same way as archiving a regular database. So this process can be done from the user interface or using a script.

Interface

Join the database using SSMS, in the Object Explorer (Object Explore) find the desired database. In the context menu, go to Tasks> Backup

Script

You can backup the database using a simple script.

  BACKUP DATABASE TestContainedDB
 TO DISK = '<File Path> \ TestContainedDB.bak' 

Restore archive database

Again, there are two ways: through the user interface and using a script.

Interface

Through the interface, everything is done in the same way as archiving.

Join the database using SSMS, in the Object Explorer (Object Explore) find the desired database. In the context menu, go to Tasks> Restore

Script

  RESTORE DATABASE TestContainedDB
 FROM DISK = '<File Path> \ TestContainedDB.bak' 

During the recovery database you can get the message:

Msg 12824, Level 16, State 1, Line 1

The sp_configure value is set to 1. You may need to use the RECONFIGURE to set the value_in_use.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

From which it becomes clear that you need to activate the Contained Database Authentication option in the SQL Server settings at the server instance level. This setting is disabled by default. Run the script below to fix the problem.

  --Enabled Advanced options
 - enable the ability to edit options
 sp_configure 'show advanced', 1;
 RECONFIGURE WITH OVERRIDE;
 go
 --Enabled Database Containment
 - enable authentication for offline databases
 sp_configure 'contained database authentication', 1;
 RECONFIGURE WITH OVERRIDE;
 go 

More information about offline databases.

Authentication methods supported by autonomous databases remain the same:

• SQL Server Authentication
• Windows Based Authentication

Database change has changed. CREATE / ALTER DATABASE now work differently. The Alter Database < database name> expression no longer works. Instead of the base name, you must specify the service word CURRENT.

  ALTER DATABASE CURRENT 

Additionally, offline databases can be read here .

This is a translation of the Niladri Biswas article . He has more interesting articles about the new SQL Server 2011. If I liked the translation, then I can put the rest of the parts in translation. Articles are very large and it is better to break them. As a result, 4-5 parts will be released.

Transfers from the cycle:
MS SQL Server 2011: Autonomous databases , new Sequence object , Offset operator , error handling , With Result Set construction , new in SSMS .