Geeks With Blogs

News


Dylan Smith ALM / Architecture / TFS

In Visual Studio 2010 we had “Database Projects” that allowed us to design/develop/deploy databases.  In Visual Studio 2012 this was overhauled and is now part of SSDT (SQL Server Developer Tools).  While the core functionality is extremely similar there are some differences between the two that I’m going to try and describe in this post.

PRO: Visual Table Designer

In 2010 we only had a basic text editor for editing the various .sql files.  In 2012 We get both the T-SQL and graphical table designer side-by-side.

Table Designer

PRO: Simpler File/Folder Structure

In 2010 the default folder structure is rather complex (even if you have a trivial database).  In 2012 the default folder structure is much simpler, flatter, and it only creates folders for object types that you actually import (unlike 2010 where it created placeholder folders for all object types even if you didn’t have any).

In addition, 2012 generates less files when you do a database import.  It keeps everything related to a specific table in a single file.  In the above example of the Products table, that is 1 file (Products.sql) in 2012, in 2010 it would have been 14 files (table + 10 defaults + 1 pkey + 2 fkeys).  I much prefer the new structure.

2012 Folder StructureDB Folders

 

PRO: Fewer Build Outputs / Config Files

In 2010 when you build a Database Project it outputs the following files:

  • dbschema – the xml description of the database objects
  • sqlcmdvars – a set of values for any custom sqlcmd variables you may have defined
  • sqldeployment – config values that control the deployment process
  • sqlsettings – database properties
  • postdeployment.sql – post-deployment script
  • predeployment.sql – pre-deployment script
  • deploymanifest – manifest file with links to the other 6 files

In 2012 when you build you get the following output:

  • dacpac – the description of the database objects (this includes pre/post deployment scripts and database properties)
  • publish.xml – the publish profile that contains deployment config values and sqlcmd values

If you want to provide DB deployment files for multiple environments things get even worse in 2010.  In 2012 you would have one dacpac, and a publish profile for each environment.  In 2010 you need separate copies of the sqlcmdvars/sqldeployment/deploymanifest files.  So if we imagine we have 3 environments (DEV, QA, PROD), under 2010 we would require 13 files – under 2012 we require only 4 files.

PRO: Support In SQL Server Tooling

In 2010 the deployment tooling (VSDBCMD.exe) was all custom tools specific to the Database Project system.  In 2012 it uses DACPAC files which are a pre-existing SQL Server concept.  The SQL Server toolset that DBA’s know and love (SSMS) already includes support for performing operations with DACPAC files.  This may help get buy-in from the DBA’s when you propose giving them a DACPAC rather than a SQL script at deployment time.

PRO: Available in all Visual Studio SKU’s

In 2010 you needed to have at least Visual Studio Premium to take full advantage of Database Projects.  The .sqlproj in 2012 is provided as part of SSDT (SQL Server Developer Tools) which is free.  You can use it even if you are only using the free VS Express Edition.

CON: Data Generation Plans are Gone

SSDT does not include any equivalent for the Data Generation Plan feature in 2010 (it also didn’t have Database Unit Testing when it first shipped, but that was added in the December 2012 SSDT Update).

CON: Refactor Doesn’t Include Pre/Post Deployment Scripts

In 2012 we have similar Refactoring support (rename columns/tables) and it will find all references and update them all at the same time.  However, in 2012 it will not update any references in the Pre/Post Deployment Scripts (in 2010 it would).  This is compounded by the fact that my Post-Deployment Scripts tend to be doing a lot more work now that Data Generation Plans are gone (I generate my sample data in my Post Deployment Script instead).

CON: (Some) Deployment Options Missing in 2012

You can specify a bunch of configuration values that control the deployment process (.sqldeployment file in 2010, the Publish Profile in 2012).  Some options that existed in 2010 are missing in 2012.  The main one that I’ve run into is the Ignore Column Order option is gone – this allows you to indicate to the deployment tooling that if 2 tables are the same except for a different order of columns do nothing.

CON: Provider Extensibility Model Gone

In 2010 there was a Provider Extensibility Model that allowed 3rd parties to author alternative Providers that could plug-in to the 2010 Database Projects providing support for non-SQL Server Databases.  The only one I know of was the Oracle Provider from Quest (called TOAD Extension For Visual Studio), but it was used by *a lot* of Oracle developers.  In 2012 it is a strictly SQL Server-Only tool.

Conclusion

So which one is better, 2010 or 2012?  While the PRO list is actually longer than my CON list, most of the PRO’s are superficial improvements that I could live without if I had to, or work-around them (I don’t need a GUI table designer, fewer files/folders is nice but not a killer feature, etc).  The CON’s are generally things that I can’t workaround (need to develop against Oracle? Too Bad) and can be big headaches.  In my not-so-humble opinion, I feel like SSDT tooling with VS 2012 is a small step backwards over what we had in 2010.

 

Update:

It was pointed out to me by @Gregory_Ott that I missed one (if anyone knows of anymore I missed leave a comment and I’ll keep updating this post).

CON: Code Analysis Rule Authoring

In 2010 there was an extensibility point allowing you to author your own T-SQL Code Analysis Rules.  In 2012 this is no longer possible (http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/1bc90f25-d3c7-4f52-a4e3-8e2cec2ff135/).

Posted on Sunday, June 2, 2013 4:06 PM | Back to top


Comments on this post: VS 2010 Database Projects vs 2012 SSDT Projects

# re: VS 2010 Database Projects vs 2012 SSDT Projects
Requesting Gravatar...
The issue of not being able to rename columns (by refactoring) in VS2012 has been a pain. We needed to rename a column, so did it the noddy way by just renaming it in the code (and all the references) but the DB publish failed because it was dropping the column then recreating a new column (with the new name). It didn't recognise that it only needed to rename the column. The column was not nullable so it failed, but even if it was nullable, the data would be lost. Luckily the data wasn't important in this case.
Left by Gus Flannegan on Nov 19, 2013 3:10 AM

# re: VS 2010 Database Projects vs 2012 SSDT Projects
Requesting Gravatar...
@Gus - You can still Refactor - Rename columns in 2012 (the same way you did in 2010). The only limitation - as I noted - is that it won't update any pre/post deployment scripts. It will however do a proper sp_rename instead of a DROP and ADD, so your data will remain intact.
Left by Dylan Smith on Dec 12, 2013 9:04 AM

# re: VS 2010 Database Projects vs 2012 SSDT Projects
Requesting Gravatar...
How can I install SSDT for VS 2013 ? any support ?
Left by pregunton on Jan 28, 2014 4:56 AM

# re: VS 2010 Database Projects vs 2012 SSDT Projects
Requesting Gravatar...
How can I get Tailspin Toys ?
Left by pregunton on Jan 28, 2014 9:12 AM

# re: VS 2010 Database Projects vs 2012 SSDT Projects
Requesting Gravatar...
@pregunton SSDT is included with VS 2013. When you go through the VS installer it is one of the options that is installed by default.

For MS sample apps like Tailspin Toys I just use the Brian Keller VM that has these sample apps already loaded: http://blogs.msdn.com/b/briankel/archive/2013/08/02/visual-studio-2013-application-lifecycle-management-virtual-machine-and-hands-on-labs-demo-scripts.aspx
Left by Dylan Smith on Jan 28, 2014 10:14 AM

# re: VS 2010 Database Projects vs 2012 SSDT Projects
Requesting Gravatar...
Can someone update if custom code rules are possible now with SSDT?
Left by MM on Feb 26, 2014 11:29 AM

# re: VS 2010 Database Projects vs 2012 SSDT Projects
Requesting Gravatar...
AFAIK custom rules are still not possible with SSDT, although according to this forum thread from Sept 2013 it sounds like the SSDT team is planning to introduce this functionality at some point: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bbaa508d-b412-464e-94ec-0745cf90a4c9/ssdt-code-analysis?forum=ssdt
Left by Dylan Smith on Mar 24, 2014 7:52 AM

# re: VS 2010 Database Projects vs 2012 SSDT Projects
Requesting Gravatar...
MS just released a new version of SSDT (for VS 2012) today that includes Code Analysis Extensibility: http://msdn.microsoft.com/en-us/data/hh297027

They also updated their public samples project to include samples of custom CA rules: https://dacsamples.codeplex.com/

I'm told an updated release for VS 2013 SSDT is coming soon.
Left by Dylan Smith on Mar 25, 2014 11:43 AM

Your comment:
 (will show your gravatar)


Copyright © Dylan Smith | Powered by: GeeksWithBlogs.net