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

Open your mind to Business Intelligence

At the beginning of this year, I have decided to have a look through some videos, demos and tutoials on Business Intelligence, particularly on what Microsoft has to offer.

Rafal Lukawiecki has done a fantastic work in summarising and presenting Business Intelligence and its application to any organisation (i.e. for revenue-driven and non-revenue driven organisation), n who is interested in improving their performance, This presentation was recorded live at the Stockholm Business Intelligence Conference on 21 April 2009. The technology used are a little outdated now, i.e. Microsoft Office 2007, Microsoft Sharepoint 2007 and Microsoft SQL Server 2008. However, the concept presented is truly worth the time, especially for emerging BI professionals and Business Users.

The presentation has been divided into 4 comprehensive sessions, each with enough information to understand the concept at high level allowing for viewers to research it further to master it.

Session 1: Improving Insight and Decision Making Using Microsoft Business Intelligence and SQL Server 2008

Session 2: Delivering Business Intelligence through Microsoft Office 2007

Session 3: Aggregating Knowledge in Data Warehouse and Multidimensional Analysis (+ additional breakouts for Public Sector, BI for Sales, Executive BI)

Session 4: Finding Hidden Intelligence with Predictive Anaysis of Data Mining

I hope when Denali comes out, we will be lucky to have Rafal presenting a similar session with Micrrosoft’s new BI stack.

Special thanks to Rafal Lukawiecki for the presentation and Channel 9 MSDN for facilitating the video streaming!