For those that might not know Microsoft introduced Database Projects awhile back, but they had some issues where the scripts created didn't always work and the schema comparisons didn't work well either. Being a BI Developer at that time I didn't spend much time looking at it, just heard what the other DB Developers were complaining about. Database Projects were meant to be a way for DB Developers and DBAs to help get the database "source code" into version control and make it easier to create scripts for deployment into multiple environments.
So, with SQL Server 2012 there were some changes made to the Database Projects and from what I have seen using it lately they are some very good improvements. But, the side effect is that there are 2 tools with a very similar name that are slightly different. The 2 tools are SQL Server Data Tools and Microsoft SQL Server Data Tools (wow big difference huh, one with Microsoft in front one without).
SQL Server Data Tools (SSDT)
For those that have installed any version of Microsoft SQL Server 2012 you know that you can install SQL Server Data Tools as part of the database instance install. This is the tool that uses the Visual Studio 2010 Shell and is the replacement for the Business Intelligence Development Studio (BIDS) in previous versions of SQL Server. Since they wanted to add more functionality then just BI development projects the tool needed to be renamed. SSDT still works with all of the BI projects as before.
Microsoft SQL Server Data Tools
This is the web install version that is available for anyone to download and install without any requirements for SQL Server 2012. As with SSDT this is built on the Visual Studio 2010 Shell. The difference with this version is that it only does the Database Project development (including SQL Azure). If you install only this version of SSDT you will NOT be able to do any of BI development unless you also have the SSDT from SQL Server 2012 installed.
Not sure why Microsoft went down this route and I find this very confusing and it gets even worse based on the version of Visual Studio 2010 that you have installed. If you want to do both BI Projects and Database Projects you will need to install the SSDT from the SQL Server 2012 install media AND the Microsoft SQL Server Data Tools from the web link above. But, even with this there are some differences in the projects! The pieces that I found which are missing are only in the Database Projects and are specific to building these projects using the new SQL Server Database Projects.
Below is what the SQL Server Database Project structure looks like when you create it using the web download of SSDT and no version of Visual Studio 2010 installed:
The screenshot below is against the exact same SQL 2012 database, the only difference is that I created this first as a new SQL Server Data-tier Application Project and then converted it to a SQL Server Database Project (you do this by right-clicking on project file in the Solution Explorer and selecting Convert to SQL Server Database Project...) on a machine that had Visual Studio 2010 Ultimate (Premium also works from what I found researching this) and the SSDT web install:
Notice the differences? There are some folders and scripts that are not being created by the "lite" version, like the Scripts folder with the Pre-Deployment/Post-Deployment files along with others. Based on what I have started to script I find the Pre/Post Deployment scripts to be very useful since they are pulled in automatically when you create the deployment scripts. I use the Post-Deployment script to setup SQL Agent jobs or anything else that can be done after the database has been created.
You maybe asking, can I just create that folder structure manually in the "lite" project and have it automatically pull that in? Microsoft has actually included all of the pre/post deployment scripting capabilities in the new SQL Server Data Projects and you can create them anywhere in your project by adding a new script item to your project and selecting the Pre-Deployment Script or Post-Deployment Script types that appear in the Add New Item dialog. While I don't understand why the initial creation of the projects has changed so that these folders/scripts are not automatically included at least they are both capable of doing it you just have to know how to add it.
I have verified that if you create the SQL Server Database Project as a converted SQL Server Data-tier Application Project in Visual Studio Ultimate/Premium and then open it in the "lite" version it does still see all the extra folders and it will script them correctly.
So this is what I have found after playing around for awhile and just about pulling out my hair when I couldn't figure out why one worked differently then the other. I believe that most of these differences comes from how the SQL Server Data-tier Application Projects (DAC) were setup and those differences in that project type are being carried over when it is converted to a SQL Server Database Project. I still find it very confusing that creating the project in one way and converting to the "new" way causes so many of these differences. Microsoft could have addressed this by making sure that the new SQL Server Database Projects for SQL Server 2012 have the exact same structure as the previous version, but it seems that they see each of these "new" project types as a chance to start over and do it differently.
