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
Sequences A sequence is an object that can be created on the fly that essentially provides an identity key. More details
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

One comment
Tassiana 29 Aug 2015 at 7:47 am

I have made similar corenr book marks and I love them. Yours are especially cute. One thing I did different is that attatched two small pieces of magnetic tape to the insides of the marker. That way when you get to only one or two pages left in the triangle the magnets take hold and the bookmark doesn’t fall off. Since I read in bed a lot, this has been a problem for me but no more thanks to the magnets

Featured Articles

 Site Author

  • Thanks for visiting!