SQL Server 2012, The Good and The Bad


As a SQL Server specialist, hearing new updates on SQL Server always excites me. SQL Server 2012 provides plenty of features, such as Columnstore Index and AlwaysOn features would immensely help my previous projects. The PowerView feature and the integration of BI capabilities of SQL Server 2012 with Sharepoint 2010 is definitely not something to dismiss very easily.  These areas below are the new features in SQL Server 2012 that I would consider as being important for my past and upcoming projects, and I wlil discuss a few of the goods and the bads.


1. SQL Server Data Tools (SSDT)

The Good: This will replace BIDS. SSDT is using Visual Studio 2010 Shell and has revamped the look and feel of SSIS in some degree.

The Bad: After installing SQL Server 2012 RC0, I am unsure how to add some third party SSIS data flow component. It may mean that if you are using Balanced Data Distributor SSIS Data Flow component, it requires some testing first.

Further reading: http://msdn.microsoft.com/en-us/data/tools.aspx


2. ColumnStore Indexes

The Good: This would immensely help those Data Warehouse databases that are large (or getting larger and larger). Those fact tables with millions of records would definitely benefit this feature. There are some caveats to this; one in particular is that it only comes in the Enterprise Edition of SQL Server 2012.

The Bad: The table with ColumnStore indexes cannot be updated as per SQL Server 2012 release. This may not be critical as there is a workaround on this: http://msdn.microsoft.com/en-us/library/gg492088(v=SQL.110).aspx#Update


3. Rapid Data Analysis through PowerView and PowerPivot

The Good: Business Analysts and Data Analysts who are exploring the data, can start with simple, powerful and presentation ready reports using PowerView and PowerPivot.

The Bad: PowerView is only available with Sharepoint 2010 BI integration – as per the SQL Server 2012 RC0. This may change in the final version.

Further reading: http://msdn.microsoft.com/en-us/library/hh213579(v=sql.110).aspx


4. Tabular Model SSAS

The Good: This feature would help with leveraging data warehouse design with less effort from building overly complex cubes, for powerful and highly performing data analysis and business reporting.

The Bad: Self joins are not permitted, Composite Keys are not supported, Unique Lookup columns are required, only Single Active Relationship is supported and only One Relationship for each source column is allowed. Choosing DirectQuery mode is not fully compatible with Vertipaq mode. Careful consideration is required before switching all the way to DirectQuery mode. .

Further reading: http://msdn.microsoft.com/en-us/library/hh212945(v=sql.110).aspx


5. Licensing / editions

The Good: If you have Software Assurance Agreement with Microsoft, you may not be affected greatly by the costs incurred due to the new pricing model.

The Bad: Planning is required whether to choose Standard, BI or Enterprise edition appropriately and it has been reported that the new licensing model will incur more costs in most cases.

Further Reading: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx, http://redmondmag.com/articles/2011/11/05/microsoft-unveils-sql-server-2012-licensing-and-pricing.aspx


The above are my take of SQL Server 2012, I strongly urge you to use it and to test it out. You may agree or disagree (or come up with different points to consider) with the above.  Please share your thoughts by leaving comments.


Last but not least, don’t forget to attend SQL Server 2012 Virtual Launch Event on March 7th: http://www.sqlserverlaunch.com/ww/Home

SQL PASS Summit 2011 Recorded Sessions

I have finally received my copy of the recorded sessions of SQL PASS Summit 2011 in DVD format last week.  So, obviously this brought back memories on my first ever co-presenting with Victor Isakov at the Summit.

A copy of the “Important Trace Flags Every DBA Should Know” presentation is available form Victor’s website: http://victorisakov.files.wordpress.com/2011/10/sql_pass_summit_2011-important_trace_flags_that_every_dba_should_know-victor_isakov.pdf

By the way, if you bought a copy of the DVD set, Marcella McKeown would have emailed you about the mission recorded sessions. In case you didn’t get the email, see below details from Marcella:

Application and Database Development
Working With XML in SQL Server
Kendal Van Dyke, Insource Technology

SQL Azure
This Ain’t Your Father’s Cloud
Buck Woody, Microsoft and Kevin Kline, Quest Software

BI Architecture, Development and Administration Topics
Common Analysis Services Design Mistakes and How to Avoid Them
Devin Knight, Pragmatic Works

BI Client Reporting and Delivery Topics
End-user Alerting with SQL Server Code Name “Denali” Reporting Services
Lukasz Pawlowski, Microsoft

These sessions are now available for download here:



To stream the sessions, please go to:



You will need your PASS Summit username and password to access the sessions.


So, the learning continues…!