CODEWITHSUNDEEP
sqldatabasetfsbranch
user1450824
user1450824

Reputation: 245

TFS branching and databases

I have a project that we need to set up a simple branching strategy on to enable development of new features while still being able to fix bugs on another branch.

The problem I am having is with databases.

The database schema is part of TFS as a Database project, but to have enough testdata we did at one point take a backup of a live database and have used that for testing during development.

Currently the databases (tree in total) are located on a workgroup server with one set of databases for each developer, and each set of databases are updated once in a while with sql scripts the other developers create when they change the schema.

My question: How can we reorganize this so that each branch is selfcontained and we can easily switch from one branch to another. I have already looked into using SQL express and placing the databases inside the branch, but this did not work out well. We have also looked into creating scripts with all the data and rebuild the database from the database, but this turned out to take too much time, and some developers tended to forget to do it often enough.

Any ideas?

Upvotes: 5

Views: 1989

Answers (2)

Jirka Hanika
Jirka Hanika

Reputation: 13529

Visual Studio database projects are not the optimal tool to support database upgrades while preserving data.

It may be useful to rethink the relation between fresh installs and upgrades. For many companies, upgrades are an afterthought and upgrade scripts are created based on deltas of fresh installation scripts. This leads to duplicate maintenance, inconsistency, and heavy dependence of details of the "current" schema on the upgrade path.

I would therefore recommend

  • maintaining only upgrade scripts (with exceptions where declarative source code is preferred and full refreshes are possible upon every upgrade)
  • decide whether you do or do not have to support downgrades
  • ensure on infrastructure level that each upgrade script is applied at most once
  • basically never edit an upgrade script, try to stick to adding new ones at the end of the application sequence

It is a pretty invasive change, and it requires a change of mindset, so evaluate your long term upgrade needs carefully first. An indication that you need to go through the change is an expectation of a lot of parallel development or maintenance in multiple release and development branches.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300529

Visual studio supports database projects. Use that and then hopefully branching will be relatively straightforward.

You can restore a database backup, and then use GDR to sync/upgrade your database schema. You would need to script data changes.

It does involve quite a bit of initial setup effort.

Upvotes: 3

Related Questions