Creating A Contained Database – SQL 2012/2014

“A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2014 helps user to isolate their database from the instance in 4 ways.

  • Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
  • All metadata are defined using the same collation.
  • User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
  • The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.”

MSDN Source reference – click here

T-SQL Code

EXEC sys.sp_configure N’contained database authentication’, N’1′

GO

RECONFIGURE WITH OVERRIDE

GO

USE [master]

GO

ALTER DATABASE [MyDemoDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT

GO

 

SQL Server 2014 Upgrade Advisor

Running the SQL Server 2014 upgrade advisor is a good idea when say planning an in-place upgrade from a previous version of SQL Server.

To do this, run setup from the installation disc, go to planning, run install upgrade advisor.

You might get a pop up such as below, this is resolved by downloading and installing the SQL 2014 feature pack components from this link. Click here.

sql2014_needs