How can we migrate to using VS2005's Database Projects?

Question

At my company, our current method of updating the database is to connect using the Server Explorer in VS2005, then modify the stored procedures by opening them and editing. The devs here seem to enjoy that "write and save it like it's code" mentality. It is pretty convenient, how it automatically turns Create into Alter and runs the scripts against the existing database when we need to tweak something.

Recently, this bit us pretty hard during a server crash when we lost a lot of changes that hadn't gotten backed up. I'm pushing to move our SQL development where it belongs: in DB Projects so we can put them into SVN along with the othe code. The alternative is nightly back-ups of the database.

I don't know much about DB projects though, or how the workflow with them is. I'm afraid that if I can't get something of similar utility to their current model, they just won't switch. Any thoughts on maintaining our current working model, but switching over to DB Projects?

Answer 1

If the developers make the rules (and your post sounds like they do), you can only proceed if the new workflow is "better" to them. Being a developer myself, I think that's the way it should be. I've seen some non-developers think up pretty nonsensical development processes, and force them on the developers to everyone's detriment.

If you're thinking about VS DB projects, you'd first test if VS DB actually works with your database. If it does, you'd have to set up a big chance in process: the "true" copy of the database is now in VS DB instead of the database server.

Another way out is to backup the development server regularly. If you back it up daily, and a transaction log backup every hour, it becomes very hard to loose a significant amount of work.

Or create a scheduled job that writes the entire database definition to a text file. (Script all objects in database.) These files are usually very small, so you can keep a long backlog.

Many respected bloggers seem to think storing database definitions in SVN is a good idea. See this coding horror post, or related Stack Overflow related question How do I version my MS SQL database in SVN.

Talk it over with the developers and see what you can agree on.