So lately on the project I am focusing on, we have been battling to help keep a solution's code base and also the connected database schema in synch (Database = SQL Server 2008).
Database changes occur fairly regularly (adding posts, constraints, associations, etc) and consequently it isn't uncommon for individuals to perform a 'Get Latest' from source control and discover that they should also rebuild the database too (and often they forget to complete the second).
We are not using VSTS: Database Edition (DataDude) however the standard Visual Studio database project having a script (batch file) which tears lower and recreates the database from T-SQL scripts. The answer is really a .Internet &lifier ASP.internet solution with LINQ to SQL underlying because the ORM.
Anybody have applying for grants a technique for take (automated or otherwise) which may keep everybody current using the latest database schema?
Continuous integration with MSBuild is definitely an option, only helps get any breaking changes committed, it does not help much within the scenario I outlined above.
We're using Team Foundation Server, in the event that helps..
We attempt to operate forward in the creation scripts.
i.e a big change towards the database isn't authorised unless of course the script continues to be examined and checked into source control.
But this assumes the database team is integrated together with your application team that is not often the situation inside a large project...
(I had been enticed to reply to this "with great difficulty")
EDIT: Tools will not assist you to in case your process is not right.
Ok although it is not the whole solution, you need to have an assertion within the Application code that links as much as the database to say the right schema has been used, this way a minimum of it might be apparent, and also you avoid quiet bugs the ones worrying that stuff went crazy out of the blue.
For the schema version, you could utilize some database specific functionality if available, however i personally would rather declare a schema version table and the version number inside, this way its portable and may be looked into having a simple choose statement
take a look at DB Ghost - you may create a dbp while using scripter within minutes after which manage all of your database code using the change manager. www.dbghost.com
This is just what DB Ghost was created to deal with.
We essentially do things how you are, using the generation script checked into source control too. I am the designated database master so that all changes towards the script itself are carried out through me. People send me scripts from the changes they've made, I update my master copy from the schema, operate a generate scripts (SSMS) to create the brand new DB script, after which check it in. I keep my copy from the code up-to-date with any changes which are being made elsewhere. We are a little shop which means this works pretty much for all of us. I recognize it most likely does not scale.
If you're not using Visual Studio Database Professional Edition, then you'll need another tool that may break the database lower into its elemental pieces to ensure that they're managable and changeable within an simpler manner.
I'd recommend seriously thinking about Redgate's SQL tools if you wish to maintain sanity total your database changes and updates.
Make use of a tool like RedGate SQL Compare to create the modification schema between a version from the database. After that you can make sure that file into source code control
Take a look only at that question: dynamic patching of databases. I believe it's similar enough for your problem to become useful.