New SQL Server Database Options with SQL Server 2012

Microsoft SQL Server Denali (SQL Server 2012) is the next version of Microsoft SQL Server after SQL Server 2008 R2 release. In this SQL tutorial, SQL Server database administrators and T-SQL developers will see differences between latest SQL Server RTM relase SQL Server 2008 R2 database options and SQL Server 2012, CTP3 database options.

I'll try to explain what is new in SQL Server 2012 with database properties screenshots that will help database professionals compare two versions of SQL Server.

You can make this comparison between two SQL Server versions, SQL Server 2008 R2 and SQL Server CTP3 by connecting to two database instances using SQL Server Management Studio and displaying a user database's database properties

Compability level and Containment type

When connet to SQL Server 2012 instance using SQL Server Management Studio, choose a database from Databases node in the Object Explorer and right click on the database to display Database Properties window. When the database properties window is displayed, go to database Options tab.

The first screenshot is from a SQL Server 2008 R2 database. You will realize that the highest SQL Server database compatibility level was "SQL Server 2008 (100)" up to the SQL Server Denali release.

With the SQL Server Denali CTP releases, a new database compability level is introduced for the SQL Server 2012 editions SQL Server "Denali" (110)

You see two new SQL Server database options in the SQL Server Database Properties window for SQL Server Denali CTP3:
Compability level, and
Containment type for new SQL Server database

Containment databases are a new enhancement with SQL Server 2012. Containment databases include all database objects within the database, in order to increase SQL Server instance independency of the database itself.

The current Containment database options in SQL Server Denali CTP3 is None, and Partial
SQL Server introduced partial containment databases since not yet all database objects are independent of the SQL Server instance. In future versions, probably Full containment database option will be introduced and none of the database objects will be dependent to SQL Server instance. So a full containment database will be easily moved to an other SQL Server instance and will work without any problem and no additional configuration by database administrators.

FileStream database options

Other DB options

Database Recovery Options