SQL Server 2012 – Features Update

After attending a meeting with a Microsoft engineer I have some more good news on Denali. Unfortunately the meeting ran short and we didn’t get through all the content so below I could not extrapolate on some points. I still listed them all below.

Performance Datawarehouse	This has existed in SQL Server 2008, however now it comes with a wizard and is much easier to setup. Basically you install a new SQL Server and run through a wizard (using a domain account) to implement the Performance Data warehouse simply by selecting the remote boxes you want to monitor. The ETL is automatically done for you and collects data on a 15 minute interval. Very cool.
CodeName “Juneau”	New SQL Server Developer tools integrated with Visual Studio. At the time of this writing, it’s on version CTP3. This tool looks excellent. It promises to make database development much easier and wrapped in source control. It’s easy integration with Azure seems like it will make Azure more attractive. You can view videos here: http://msdn.microsoft.com/en-us/data/hh297028. Download here.
Filetable	Store files (ppt, word) and link them into sql server. Get transactional support for accessing files. You can Drag drop files into a directory, and then dynamically access them via SQL Server. When files are added, searches can be run against the documents using Full Text Search.
Semantic search within FT	An optional parameter that captures the top 100 statistically relevant words from a document. Also captures the top 10 similar documents based on the document that is found, you can find more details here.
Faster Spacial Performance	Provided parity between the geometry and geography type. Improved the CLR performance of the CLR objects and spacial index handling. New default index mechanism which gives more predictability for spacial operations. Nearest neighbor queries now take seconds to sub seconds.
Full globe spacial support	New spacial objects including circle and arc to represent objects that span more than one Hemisphere. All spacial features can be found here.
Sequences	A sequence is an object that can be created on the fly that essentially provides an identity key. More details here.
Paging for Result Sets	Result sets can now be paged by using the OFFSET and FETCH parameters for the ORDER BY clause. Offset specifies the starting row number, where FETCH provides the number of rows. Both these parameters would need to be passed in via a stored procedure.
Local DB Runtime	I believe this is to help support Azure development. If you are not connected to the cloud, you can still develop. Here is the MSDN article.
Partitioning	The Enterprise Edition of SQL Server can now support 15K partitions per table. The previous limitation was 1000 partitions per table.
Column Store Index codename “Apollo”	Creates an index on existing table and stores the column data together rather than the row data together. They report speeds increases of 100X for certain queries. This new feature uses the same technology built into Analysis Services that allows large amounts of data to be sliced & diced. Susan Price from the SQL Server Database Engine explains that this enables much better compression because there are more similarities for the data stored in columns rather than for rows. It’s main use seems to be to speed up queries typically used within a Data Warehouse (Star joins, aggregations, filtering). Since data warehouse queries only touch 10-15% of the columns in a source table, this translates into a big speed increase not having to pull all row data. Hear if from the source here. Also watch an example presentationhere (If you don’t mind Non-Ansi queries)
Faster full text search	Full text search has been optimized to be quicker by implementing parallelism. Their goal was to support 100 million large documents and up to about 350 million small documents.
User defined Server Roles	Server level roles can be custom defined… YAAAA! Finally 🙂
Contained Databases Option	When a database is specified as “contained”, then it does not rely on the server-level (or instance) settings of the SQL Server in order to operate. Therefore it can be easily moved. This includes all settings including Security. Article here
Distributed Replay	Save and replay sql statements from client machines in a particular order, oryou can run asynchronously. If you do run asynchronously, the order cannot be guaranteed. MDSN Article.
Audit Enhancements	Can be secure to be untouchable by DBA’s and also go to a centralized location.
SQL Server Always On	Formerly referenced as HADR (High Availability Disaster Recovery). This feature can be thought of as a combination of Log Shipping and database mirroring. It centers around an applications needs rather than specific databases. Meaning that if one database fails, then all databases associated to the failover group (or application) will be repointed. It also has the same synchronization modes as database mirroring allowing for transaction to be committed on up to two secondaries prior to being committed (synchronous) or allowing transactions to be committed on a primary group then distributed and later committed on secondaries. Secondary servers are also Active (readable) without being in standby mode for the log to recover.
Codename “crescent”	A business analyst tool that’s part of reporting services allowing the user to create rich visuals to help describe source data. More information.
Azure Enhancements	Each SQL Azure DB has 3 replicas providing automatic replication and failover. MS is banking on Azure it seems. Of the 1200 people on sql server dev team.. half of those are on SQL Azure. It still has a 10GB limit currently.

On the Horizon

Two indications of what may be in the version following Denali are:

• Enterprise Metadata – Likely will be integrated with Master Data Services.
• Federation – The ability to scale out SQL Servers (multiple servers) rather than scaling up. I’m banking this will be an extension of their SQL Server Always On technology since the groundwork is already there. This will allow much larger throughput with much lower hardware costs.

More Denali items I do not have specifics on. Some of these can be found in the features pack.

• php driver
• odbc for linux
• utf – 16 support
• jdbc 4.0 driver
• tsql & clr enhancements
• dac enhancements
• win32 access to database files
• Management pack for HA