I’m currently working on a project that is replacing a suite of legacy applications (SQL Server 2008 R2 and ASP.NET) with a REST API with an underlying SQL Server 2008 R2 database. The developers working on the API are already using Visual Studio 2012 and the DB team (of which I am a part) are looking to upgrade our Visual Studio 2010 database projects to use SQL Server Data Tools (SSDT) in Visual Studio 2012.
I’ve been playing about with it and this is what I’ve found so far. Please note, this is still a work in progress and we have not yet completed the upgrade.
To make the upgrade process as easy as possible it was suggested that all the members of the DB team deploy SSDT for Visual Studio 2010. This would allow people to choose whether they want to use VS 2010 or 2012 once the solution had been upgraded. The Dec 2012 release of SSDT for VS 2010 can be found here: http://msdn.microsoft.com/en-us/jj650014
Once I’d installed SSDT for 2010 the next time I opened our DB solution (in VS 2010) the “Convert To SQL Server Database Project” wizard was displayed. There were two options available: “Yes, convert to SQL Server Database Projects” and “No, do not convert”. As I was not ready to do the conversion I chose “No”. Please note that there is a note under this option that says “You will not be prompted to convert these projects again”. I was able to use the solution in 2010 as before.
I thought I’d play about with the conversion wizard so I took a copy of our development branch and opened it in VS 2012. As I’d already said “No” to the upgrade I wasn’t shown the upgrade wizard window again but I was shown a window that had a list of the existing DB projects with a checkbox asking which ones I wanted to convert. I converted them all and the solution opened in VS 2012.
Doing this resulted in new .sqlproj files being created to replace the original .dbproj files, although the .dbproj files still exist. I also found that a <configName>.publish.xml file was created in each of the databases for each of the configurations that we have in our project (more on these files further on).
I also noticed that there was no “Schema View”. In SSDT you now get more in the “SQL Server Object Explorer” window. You’ll see you have two nodes: “SQL Server” which is used to connect to various SQL Servers; and “Projects” which replaces “Schema View” and shows the objects in each of the projects in the solution. In “SQL Server” you will see a connection to “(localdb)”. This is a SQL Server Express instance (I believe) and whenever you do a debug build and publish (or hit F5) the solution will get deployed to this instance. This allows you to do stuff on an instance of SQL Server without having to install the full version of SQL Server.
Now I thought I’d try and build the solution against my local machine (SQL Server 2008 R2 instance) and the first problem I found was that in our 2010 solution we have .dbschema files which are included as database references in some of the DB projects. Some of these .dbschema files were for some of our legacy databases but as SSDT can’t read .dbschema files I was getting lots of “unresolved reference” errors.
The .dbschema files have been replaced by .dacpac files in SSDT. You can create a .dacpac file from an existing .dbschema file by following the steps in this blog: http://blogs.msdn.com/b/ssdt/archive/2011/11/21/top-vsdb-gt-ssdt-project-conversion-issues.aspx#references. My legacy databases are still in use so I could just connect to it in “SQL Server Object Explorer” in VS 2012, right-click and “Create New Project”. This create a new SSDT project for my legacy database.
I then attempted to create the .dacpac file using the “snapshot”. This builds the project first but when doing that I got lots more “unresolved reference” errors. I added the referenced legacy databases as projects to the solution and created new database references to the other database projects. One thing to note is that you must make sure that the “Database Location” settings on the “Add Database Reference” box are entered correctly. By default it will assume you are using a database variable to reference objects from other databases. Our SQL objects are not reference in the project by using variables so I needed to delete the text from the “Database variable” box. You need to ensure that the “Example usage” string matches how you reference objects from other databases in your database code.
Now once I’d added my dacpac as a database reference the solution was just hanging on the “Analyzing Database Schema…” part, and it won’t perform a build until this completes. This is apparently a bug that was fixed in the SQL Server Data-Tier Application Framework (March 2013) patch. http://blogs.msdn.com/b/ssdt/archive/2013/03/06/sql-server-data-tier-application-framework-march-2013-available.aspx. I’m on a 64-bit machine so I just installed all 6 .msi files. This fixed the problem and the build started.
I was then getting more “unresolved reference” errors, this time any time a system table was referenced, e.g. “…has an unresolved reference to object [sys].[objects]”.
To fix this I created a new database reference on the project and selected the “master” database in the “System database” drop down box.
It turns out the references to the legacy databases are no longer required so I removed them leaving just my “master” DB references.
The schema checking in SSDT seems slightly tighter than in VS 2010 so I had to fix a couple of errors to do with data type mismatch in foreign key constraints but once this was done the solution built with no errors.
Now I wanted to check that I can still deploy the solution to my local DB. SSDT uses the term “publish” rather than “deploy” and from what I’ve seen so far you can only run the publish at the database level, not the solution.
The <configName>.publish.xml files are the things that control the publishing. (You can right-click the project and select “Publish” but this just open a blank “Publish Database” window that you’ll need to fill in). The .publish.xml holds the data used to populate the “Publish Database” window meaning you don’t have to complete it each time, and these can be passed in as parameters to MSBuild.
I double-clicked on the .publish.xml file for the configuration for my local machine and the “Publish Database” window was opened. I clicked on “Edit” and set the connection to my local instance. I then clicked “Save Profile” which wrote the connection to my local machine to the .publish.xml file. The next time I opened it, the connection string was there and I just had to click “Publish” and the database was created/updated on my local machine.
It seems you just need to create a .publish.xml file in each SSDT project for each of the environments you want to publish to. More details on the .publish.xml files can be found in part 2 of the following blog: http://programmaticponderings.wordpress.com/2012/08/01/convert-vs-2010-database-project-to-ssdt-and-automate-publishing-with-jenkins-part-23/
I am currently looking at automatically publishing to our different environments using MSBuild. In the meantime here’s a bit of information from people who have done the same: http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/d73a489f-1b03-4b42-ac2b-647b0d1b9462
Because I have installed SSDT for VS 2010 I’m able to open the converted solution in 2010 and 2012 at the same time and can use the .publish.xml files in either 2010 or 2012 to deploy to my local machine.
So far it seems a relatively painless process but I’ll see how I get on playing with MSBuild as I’ve not really used it before.
There is now an August 2013 update for the SSDT in VS 2012