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.
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.
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/).