Looking for alternatives to the database project.

Question

I've a fairly large database project which contains nine databases and one database with a fairly large schema.

This project takes a large amount of time to build and I'm about to pull my hair out. We'd like to keep our database source controlled, but having a hard getting the other devs to use the project and build the database project before checking in just because it takes so long to build.

It is seriously crippling our work so I'm look for alternatives. Maybe something can be done with Redgate's SQL Compare? I think maybe the only drawback here is that it doesn't validate syntax? Anyone's thoughts/suggestions would be most appreciated.

Answer 1

Keeping Dev/live db in sync:

There are probably a whole host of ways of doing this, I'm sure other users will expand further (including software solutions).

In my case I use a two fold approach:

(a) run scripts to get differences between db (stored procs, tables, fields, etc)

(b) Keep a strict log of db changes (NOT data changes)

In my case I've over time built up a semi structured log thus:

Client_Details                  [Alter][Table][New Field]
{
    EnforcePasswordChange;
}

Users                       [Alter][Table][New Field]
{
    PasswordLastUpdated;
}

P_User_GetUserPasswordEnforcement           [New][Stored Procedure]
P_User_UpdateNewPassword                [New][Stored Procedure]
P_User_GetCurrentPassword               [New][Stored Procedure]
P_Doc_BulkDeArchive                     [New][Stored Procedure]

ignore the tabing, the markdown has messed it up.

But you get the general gist.

I find that 99% of the time the log is all I need.

Answer 2

Please consider trying SQL Source Control, which is a product designed to work alongside SQL Compare as part of a database development lifecycle. It's in Beta at the moment, but it's feature complete and it's very close to its full release.

http://www.red-gate.com/products/SQL_Source_Control/index.htm

We'd be interested to know how this performs on a commit in comparison to the time it takes for Visual Studio to build your current Database Project. Do you actually need to build the project so often in VS that it's a problem? How large is your schema and how long is an average build?