Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
Business Intelligence 6
SQL Server 2012 5
Azure 3
SQL Server 3
Data Warehousing 3
Cloud 2
MDX 2
SharePoint BI 2
Tabular 2
Uncategorized 1

Archive · View All
March 2012 5
November 2011 4
February 2012 4
May 2012 3
July 2012 1
December 2011 1
January 2012 1

Simran Jindal's Blog

SQL Server 2012 – New Features – Contained Databases

Dec 5 2011 12:00AM by simranjindal   

A very common problem that has plagued both the DBAs and developers for a long time is migrating databases to different SQL Server instances for application development and testing purposes OR for various failover conditions. Databases are not really independent units. They have many server dependent objects like logins, SQL Server Agent jobs,  linked servers,  different collations etc. A lot of these dependencies cause problems when working with temporary objects and therefore it is always challenging to migrate databases for e.g., for testing and quality assurance.

SQL Server 2012 introduces a new feature called Contained Databases, which intends to reduce and eventually eliminate SQL Server instance dependencies that databases currently have. The intention of this feature is to make database migrations easier with less work involved in reproducing and validating the instance level dependencies.

SQL Server 2012 introduces a new feature called Contained Databases, which intends to reduce and eventually eliminate SQL Server instance dependencies that databases currently have. The intention of this feature is to make database migrations easier with less work involved in reproducing and validating the instance level dependencies.

Following is a list of some of the issues that are solved in the SQL Server 2012 RC0 release

  1. We can now create a user with a password at the database level – a “contained database user“. The user authentication is done at the database level and the applications just need to change their connection strings. This solves a very common problem of “Orphaned Users“. In case of a failover, users would be able to connect to the secondary server, without creating logins on the instance hosting the secondary.
  2. Collation conflict between the SQL Server instance and the database is one common problem that every DBA and developer has experienced. In SQL Server 2012, tempdb will automatically create the temporary objects by using the collation of the “Contained Database”,  instead of the collation of the server. This works very well  if you are working with one database or multiple databases having the same collation. The issue with queries joining temporary tables from multiple databases with different collations is still not resolved.
  3. We can get a list of queries and objects that are not contained or cannot be separated from the instance by using DMVs (sys.dm_db_uncontained_entities (Transact-SQL) and events (database_uncontained_usage_event). In other words, SQL Server 2012 provides the information about when the migration crosses the containment boundary.
  4. Using the contained databases, a cloud environment can be simulated with clear boundaries between the database and the SQL Server instance.

Given all the advantages of contained databases, the only containment option SQL Server 2012 gives is PARTIAL - which means that it allows the features that cross the application boundary – containment is only observed not enforced. In the future releases, we can hope for further extension of this feature dealing with other dependencies. Even though SQL Server 2012 does not provide fully contained databases, it is a great first step towards database autonomy. I have had many incidents in my past jobs where I had to migrate databases (as part of application development) to new servers and it was not something I really enjoyed doing.  I am really glad that this feature is finally available. Though not complete but it is a step in the right direction.

Reference Links:
Books Online – Partially Contained Databases

Updated – January 9, 2012
Step-by-Step guide to implement Contained Databases – Sarabpreet Singh @sqlservergeeks.com

 



Republished from Simran Jindal's Blog [4 clicks].  Read the original version here [1 clicks].

simranjindal
397 · 0% · 102
2
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"SQL Server 2012 – New Features – Contained Databases" rated 5 out of 5 by 2 readers
SQL Server 2012 – New Features – Contained Databases , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]