Database deployment projects in a non model environment

The setup

A development server running SQL Express 2008. A production server running SQL Server 2005 standard edition. A web application with a database store
in the development environment ready for a first test on production - call it staging for this purposes. Our development database had grown organically with schema changes on the fly driven by incremental features on the other tiers. At a certain point in time we made use of databasescripter to help with recreating the database schema as we are a distributed team (of two). The databasescripter served its purpose, but taking into consideration that the database schema was not the core focus of our project, it required too much TLC. The migration to production also posed a problem as that server was running SQL 2005.

The requirements

• Have a simple way to manually kick off the deployment of our production database to SQL 2005.
  
• The database settings should not be modified nor should the database itself be recreated (thats a limitation of our hosting provider).
  
• The method should drop existing schema objects before recreating them.
• Data need not be preserved.
  
• This process should be triggered from inside the Visual Studio 2010 environment.

My solution

My first try was using the Package/Publish SQL feature of the web project in Visual Studio 2010. An overview of its use is here "Database Deployment with Visual Studio 10", though its pretty clear from the property sheet itself how to use it. After setting up everything I continuously hit the problem that the database did not get deployed. The output window didn't even register any activity at all - blank. Finally I think I tracked it down to the deployment method I was using. Our hosting provider doesn't allow for any 1-click or IIS package deployment, its a shared hosting setup. This means that whilst the website itself was published (i.e. copied) fine using ftp, there wasn't anything after that. I haven't confirmed in the documentation whether this is as designed, but I can well imagine it to be (i.e. I am almost certain it is so). Though this was inadequate for our purposes I do like the fact that you essentially define a "from" database and a "to" database using connection strings and the database schema is transferred across. I don't know for sure but I suspect (i.e. I am almost certain it is so) this leverages the same infrastructure as the Database Deployment projects which is what I ended up using. For dedicated hosting solutions I can imagine this would be great.

Second try was using "Declarative Database Development" as I had a full installation of Visual Studio 2010 at my disposal. This was in the end really easy:

• Create a new SQL Server 2005 Database Project.
• Bootstrap the project contents from you "from" database by using Properties -> Import Database Objects and Settings.
• Fill in your "to" database in the the Target Database Settings of the Deploy tab in the project settings.
• Hit "Deploy" from the Project context menu.
  
• Great success!.

Now to be completely accurate I did encounter some quirks along the way mostly cause of my head first dive into this. After importing from my development database the project detected that the schema was from SQL 2008 i.e. SQL version 10.0.x. This meant that the DatabaseSchemaProvider (DSP tag in the project file) was set to something like Sql100.*. And trying to deploy to SQL 2005 resulted in an error. Now since I knew there wasn't anything SQL 2008 specific about our schema I hand edited the project xml file and changed the DSP to Sql90.*. Also due to the restrictions of our hosting provider I had to delete some of the imported SQL objects: setting up a dedicated user for example.

All in this was a surprisingly painless experience - as it should be for requirements so simple and basic.