Source control for code is awesome, but when it comes to databases, I've never done it full. Sure, there's dumps to SQL files, and extracts, but it all seems just a bit too manual.
So, I've decided to try my luck at a proper database project for my MS SQL database in Visual Studio 2012. The MS SQL Server flavour is 2008 R2.
The current situation is 3 databases that I need to apply changes to, at various times.
My first database is the development database, located on a shared server, and connected via Windows Authentication.
The second database is the test database, located on the test server. Whilst I can connect to the machine via a VPN, the database still has Windows Authentication, and physically connecting to the database from my development machine is not an option.
And the third database is the production database. Also using Windows Authentication, and unreachable from my desktop, in any database connectivity sense.
At the time of deciding to have this database project, I was near the end of the development cycle, so pretty much all database changes I wanted to make were already applied. Though in planning for a new release, I always take a copy of the database from the previous release and rename it for the new release. So I had an older version to work with.
So I created a new SQL Server Database Project. Then I imported from the older database. At this point, I committed the project source to Git, so show the state of the database before all my current release changes have been applied.
Then I performed a Schema Compare with the new release, making sure to set the new database as the source, and the project as the target. I then did an Update, and committed that to Git as well. Nothing unusual so far.
The next challenge is to work out what changes I should require to upgrade the test database. Since the database project also imports security objects, such as logins, I want to make sure none of my development logins make it to the test and production databases. The database project tools and doco seem geared for when you can connect to the database to do a Publish, but it's not an option in this case.
The next best thing is to jump on the test system, and generate a .dacpac file from the test database, and bring that back to my development machine. From there, I can (hopefully) perform a Schema Compare and generate an SQL file that I can run on the the test server to get it up to speed.
But no. That would seem to not be an option for me at the moment. When I generate a dacpac file from SSMS 2008 R2, and try to use this in a Schema Compare, I get an Origin.xml file missing error. This post suggests that MS might fix the issue, but also says that you need to upgrade to SSMS 2012 to be able to generate v3 of the dacpac, the only version that will work as intended.
Unfortunately, my test system is only running 2008 R2 on Window 2008 Datacenter, and SSMS 2012 requires Windows Server 2008 Datacenter SP1. On the original post, I have quizzed the answer that suggested a fix to see if they were going to fix SSDT to handle older dacpacs that don't have an Origin.xml, or the less convenient option of forcing everyone to patch their OS being being able to install the latest SSMS.
So, for now, I will just do a Schema Compare between the older release database, the current database, and generate an SQL file that can be run on the test server and production server. In my manually maintained SQL upgrade scripts, I have some alter statements that introduce not null fields with no default, and also have code to populate those fields. I think I may have to run my manual scripts anyway, but at least I can extract the Stored Procedure code from the generated SQL to upgrade all of those, instead of manually visiting each one, doing a modify, copy and paste, and making sure permissions are appropriately applied.