I am sure other people are doing this.. I am just seeking some advice on best ways of going about doing this.
I have a web-server I am creating as a hobby - it supports my mobile apps. I have 2 machines I use to work on the code and because of that need to keep them in sync. I would prefer to work with a local DB for development, and then deploy the DB to the host when I want to publish the site. The problem is that with 2 machines, I am unsure how to keep the DBs in sync. What's more, I am really not that good with databases, and so I want something like the Schema comparer that comes with VS to allow me to sync the host DB with my local DB.
Here are the tools I have at my disposal:
1. Visual Studio 2010
2. Local SQL Express
3. Hosting server which allows me to use SQL.
4. Assembla/svn - I use that for my source control.
What I am currently doing:
Instead of having a local DB, I use a remote DB on my host for development. The good thing about this is that I dont need to sync the DBs on my 2 machines (since I am using the same remote one for development). The bad thing about this is that I cannot use the tooling that comes with VS to sync the schema because my host blocks certain properties that are needed on the source DB.
So.. How do you guys do this?
I do this by having schema maintenance in code - basically a lump of DDL (data definition language - i.e. SQL for mainting the schema) and by including a version table in the database.
You can then run code that says:
Downside is a small amount of work to maintain the code. Upside is that it becomes trivially easy to keep your database schemas in sync.
I have a long example somewhere on here - I'll try and find it again when I have time.