Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Some SQL Server 2012 upgrade advice for ISVs

I have spent a lot if time recently briefing Independent Software Vendors (ISVs) on SQL Server 2012,so I thought a consolidated post on the subject might be useful for those planning to develop solutions on top of SQL Server 2012. 

New Features that will just work

By this I mean there are some new things in SQL Server which you can take advantage of without changing your application.

Always On allows you to make an application highly available by combining the best parts of mirroring and clustering without the need to have a SAN or other shared storage. Not this is in enterprise edition.

Report Alerting allows end users to setup conditions in simple interface on any report and get an email when those conditions are met.  This needs SQL Server standard & SharePoint Foundation (the free one) or higher.

New Features that you can take advantage of in your application

Development  SQL Server now has SQL Server data tools that you can deploy to Visual Studio 2010 to make application lifecycle management easier. For example simple tools to edit and compare schemas and data tier applications to make deployment of your application easier.  There is also Distributed Replay, which allows captured profiler traces to be replayed on another environment which might be a later version of SQL server or simply a test server. the tools can either be installed as part of installing SQL Server or via the web platform installer

Security.  The key security feature in SQL Server 2012 for ISVs will be contained database security which will allow you to have all the security credentials built into the database you are using.

File Table.  This builds on filestream to expose a new type of table a file table as a folder that can be used as any normal file folder except that each file and subfolder will now be stored as a row in the File Table.  This might be useful in storing any unstructured data as part of your application.  Note that full text search and the new semantic search work well with File Tables. I have post here on setting that up too

T-SQL. There are a few new functions in T-SQL, that might be relevant.

Self Service BI

In many situations the end user will want to combine data from your application with other sources.  The new self service BI capabilities in SQL Server 2012 can make it easier for users to do this in Excel and for this work to be scaled up and deployed to the rest of the business. To get the best out of this in your application you might consider:

  • Creating a suite of reports designed to expose the dimension type information (product lists, chart of accounts etc.) that can then be consumed by the user as odata feeds in PowerPivot for Excel (odata is built into reporting services). 
  • Creating a BI Semantic model to map how your data is structured and add extra business logic (calculations and aggregations) so that business users can quickly build their own analytics and report in such tools as the new Power View.  note: This requires SQL Server BI edition and SharePoint enterprise

What won’t work

There are a only a few things that won’t work in SQL Server 2012 that are in SQL Server 2008 R2. Microsoft has a process for announcing which features will go; in any given release there are a list of deprecated features, those that won’t be supported in a future release. This means there is plenty of advanced warning, both to stop using the feature if you are already and not to use a deprecated feature in any new design work.

In SQL Server the list of features that are no longer supported is very minor; i.e. if it works in SQL Server 2008 / SQL server 2008 R2 it will also work in SQL Server 2012:

  • System stored procedures:
    • sp_ActiveDirectory_Obj
    • sp_ActiveDirectory_SCP
    • sp_ActiveDirectory_Start
  • the Surface area configuration (SAC) tool
  • and various command line switches to install SQL Server so if you are deploying SQL Server as part of an application you’ll need to change the install script. I mention this because various tools like the upgrade assistant will pick up what code is in your database and profiler and the SQL Server deprecated features object will track your usage of features that are going to be obsolete, there aren’t really any tools to check your installation process.

Upgrade Advisor and Upgrade Assistant

Two confusingly named tools exist to put some science into your upgrade planning, the SQL Server Upgrade Advisor is a Microsoft tool, and the Upgrade Assistant is also free and provided by a top gold partner Scalability Experts.  The Upgrade which does a high level check of compatibility issues moving from one version to another, and the Upgrade Assistant is a detailed tool for preparing making trace replays to confirm that the code that is actually executing in an application works in the new version, so this can be used to track an installation code executing in multi tier applications as well as the objects inside any given database.  If you’re an ISV you’ll probably want to use both in your testing.


DTS won’t be supported in SQL Server 2012 for more on this check TechNet and my post on the subject.

 Anyway I hope that’s useful, full details on SQL Server 2012 Editions & Licensing are here, and for more information on those new features visit the main SQL Server 2012 Resource Centre.