By Andrew Fryer

There’s a perception that developers and IT Professionals don’t always get along. Possibly the reason for this is the friction that can arise during deployment, part of which involves a database like SQL Server. Some of the causes for this in SQL Server projects I have seen are:

  • The IT team aren’t going to let the developers loose on the production environment so deployment needs to be scripted, from dev to test to production
  • That is then further complicated when an existing application gets an upgrade.
  • Once the application is installed it doesn’t work as well as expected, you’re getting odd errors or performance is poor.

There are several features in SQL Server 2012 to address these issues based on changes that were made in SQL Server 2008 R2.  The most important of these is the Data-tier Application (confusingly abbreviated to DAC).  What this does is to bundle up a SQL Server project which you create as part of your solution in Visual Studio 2010 into an XML file  (the file is zipped and has a .dacpac extension) that can then be deployed to a production SQL Server/SQL Azure either from management studio or using PowerShell.  This can be done in SQL Server 2008 R2 but in SQL Server 2012 there are now SQL Server Developer Tools which make it much easier to do schema comparisons e.g. compare production to what is in development, as you can see here:

[image taken from the SQL Server Developer Tools team blog]

Contained Databases also helps here as all the security can be put in the database as well as other metadata normally sorted in the master &  msdb databases.

The other good thing about data tier applications is that you can monitor them in SQL Server Management Studio using the SQL Server Utility (this applies to SQL Server 2008 R2 and 2012)…

clip_image004

…however this is only part of the story, as this is only showing you what’s happening inside SQL Server.  To understand end to end application performance you need System Center - specifically Operations Manger 2012.  This has sophisticated application monitoring built into it which details problems with end to end performance from client through the service to the database, and allows to quickly drill down from an error to performance problem to the cause/bottleneck and pass the code or SQL statement to the developers for resolution.

One final niggle is also dealt with in SQL Server 2012 - you only need one version of Visual Studio to do your developments in. Currently this would be Visual Studio 2010 sp1. However when VS11 comes out there will be a new version of the Developer tools released to work with that and you won’t need to continue to use VS 2010 as well.

clip_image002Andrew Fryer
IT Pro Evangelist, Microsoft

http://blogs.technet.com/b/andrew/

Andrew is an IT Pro Evangelist who joined Microsoft in 2007. He has been focused on business intelligence and data management, but is evolving to cover the wider issues facing many IT professionals in the UK, such as cloud computing,  infrastructure optimisation against uncertainties about the economy and the environment.