The release of Microsoft SQL Server 2012 has introduced many new features that increase scalability, manageability, availability, programability, and security across the enterprise.
Enhancements Of SQL Server 2012
Online Index Rebuilds
SQL Server 2005 introduced the ability to rebuild an index online with a few limitations that have remained consistent through SQL Server 2008 R2.
1. you could not rebuild an XML or Spatial (SQL 2008 or later) index online.
2. you could not rebuild an index online if the index contained the large object datatype columns xml, varchar(max), nvarchar(max), varbinary(max), image, text, or ntext.
3. In SQL Server 2012, if you needed to do online index maintenance on indexes with these limitations, your only option was to reorganize the index.
Starting with SQL Server 2012, you can now perform online index rebuilds for indexes containing xml, varchar(max), nvarchar(max), or varbinary(max) data types. You still cannot rebuild indexes online that contain image, text, or ntext data types, but these data types are deprecated and should be replaced with the new (max) data types anyway. The enhancements made to online index rebuilds could easily span multiple categories, as rebuilding indexes online allows for more flexible management capabilities.
When a checkpoint occurs, SQL Server writes the dirty pages in memory to disk to provide a consistent point for database recovery. Although checkpoints occur automatically, based either on workload or triggered by certain actions, such as a database backup, there are a few things you can do to influence when they occur. Prior to SQL Server 2012, you could set an instance wide recovery interval setting using sp_configure or manually issue a database checkpoint using the T-SQL CHECKPOINT command. The default behavior for SQL Server is to try to maintain a recovery time of one minute. Increasing the recovery interval setting causes SQL Server to take checkpoints less often, whereas decreasing the recovery interval will cause checkpoints to occur more often.
New to SQL Server 2012 is the ability to override the instance level recovery interval at the database level using the TARGET_RECOVERY_TIME option of the ALTER DATABASE command. The recovery time can be set using seconds or minutes. For example, to change the recovery time for the AdventureWorks database to five minutes, you can issue the following command:
ALTER DATABASE AdventureWorks SET TARGET_RECOVERY_TIME = 5 MINUTES
Typically, you will want to keep the default behavior; however, if you run into performance issues because of numerous checkpoints or you would like a faster recovery time, you have the option. Keep in mind that changing the target recovery time can lead to performance issues and may not increase your recovery time at all if your workload has long running transactions.
Clustering has always been a major factor when determining a high availability strategy and Microsoft continues to add features to improve this technology.
In SQL Server 2012, Microsoft has added support for multi-subnet clustering, an improved and more flexible failover policy, as well as the placement of tempdb on a local drive.
Being able to place the tempdb on a local drive can offer major performance gains especially if you add solid state drives into the equation. Multi-subnet clustering has been supported on Windows since Windows Server 2008; however, it was not supported in SQL Server.
Now that multi-subnet clustering is available in SQL Server 2012, this opens the door to a native geographically dispersed clustering solution within SQL Server. You can find more on multi-subnet clustering at read more
In addition, SQL Server 2012 uses the Microsoft cluster service (MSCS) as a key component in the new AlwaysOn feature.
AlwaysOn is the big new high availability/disaster recovery feature in SQL Server 2012. It was initially referred to as HADRON (High Availability Disaster Recover Always On). AlwaysOn basically provides the best of both worlds in clustering and database mirroring.
AlwaysOn uses clustering technology for fail-over, while also keeping multiple mirrored copies of the database. AlwaysOn removes the disk as a single point of failure formerly encountered with clustering, as well as provides new features, such as a read-only copy of the database, which was not previously allowed by database mirroring.
There have been some nice features added in SQL Server 2012 to enhance your management capabilities. You may not easily find many of the new features from within the GUI, but they open several options from an administrative perspective. For example, you can now use the plan cache as a work load for the Database Engine Tuning Advisor. You can use many of the new features, such as the Extended Events GUI, to attain more insight into your servers. In addition, you can use features, such as Contained Databases,to attain more granular control of your environment.
Although Extended Events have been available since SQL Server 2008, Microsoft has added an Extended Events user interface in SQL Server 2012 to ease usability. In addition to the standard user interface, Microsoft has also included a Wizard, as shown in Figure 1, to further help with configuration.
Prior to SQL Server 2012, Extended Events had a very sharp learning curve and parsing the xml data was unpleasant to say the least.
SQL Server Management Studio has been enhanced in several areas,when it comes to restoring databases as well. First, Microsoft has added an easy way to check for and repair corruption in database pages, as shown in Figure 2. The repair pages grid displays any records that appear in the suspect_pages table in the msdb database.You can also execute a DBCC CHECKDB WITH PHYSICAL_ONLY command against the database to populate the grid.
Finally, we simply select the Add button to populate the grid manually.
Another interesting new feature is the visual backup timeline, as shown in Figure 3
The backup timeline shows you when your backups were taken, as well as the type of backup. You can move the slider along the timeline at any point within the transaction log backup to create a STOPAT datetime parameter for the restore command
A contained database is a new feature in SQL Server 2012 that eases many of the issues related to external database dependencies, such as logins or references to other databases. A contained database has everything it needs to function properly within the database itself.
A fully contained database has no external dependencies, making movement between servers without breaking functionality easy.
Fully contained databases will not be completely implemented in SQL Server 2012.