Redgate SQL Source Control recommended workflow for dev-test-live databases

Question

We are trying to get started with SQL Source control and have some questions.

This is what I am aiming towards. Does this look like it will work?

1. Modify dev database tables/procs
2. Commit to dev git branch on dev PC
3. Push changes to central repo
4. Repeat steps 1-3 for each change
5. Merge dev branch into test branch
6. Use SQL Source ConGtrol "Get Latest" on test branch
7. Apply changes to test DB
8. Repeast steps 5 - 8 but from test to live

Note: - Using "Shared database" development model.

Questions:

• Does this look like it will work?
• Can SQL Source Control apply the changes to the test and live databases
  • or do I need to purchase a copy of SQL Compare for the dev server to perform this task?

• Fix the diagram here: http://goo.gl/l2mJdt
• More notes & experiences in my Google Doc

Answer 1

Yes, this does seem to work as long as you use the right connection model.

It is not considered best practice by Red Gate (Here's why). They prefer you to also purchase SQL Compare.

You can simply connect to all databases using dedicated model, but you loose the ability to see who has modified a particular object, but you can merge patches from live.

I prefer this:

• Developers connect using Shared model
  • then you can see who has modified each table/proc/etc.
• We also keep a working folder on the dev server using shared model.
  • This allows us to use get-latest to update dev with patches from live.

It could be simpler if a mixed model feature is released (Vote here).

Answer 2

Great that you're deploying a versioned copy of your database changes from the repository, that's really good continuous delivery practise in my eyes.

Have a few suggestions about your questions (I have my Red Gate hat on)

• Usually wouldn't recommend connecting SQL Source Control to your live environment. It polls to look for changes, and that is may not something you want on your live system. The recommendation is to use SQL Compare instead to make one off deployments to UAT/Production systems. Alternatively the Red Gate Deployment Manager product may be of interest to you.

• You ask above about Shared/Dedicated mode in test. It doesn't matter if you are using a shared database for your developers in your dev branch, and then a dedicated model in your test branch. If the only changes to the test database are coming from one place (e.g. your git deployments) then it's probably better to run that database in dedicated mode.

I've drawn a diagram with some tweaks to yours. Not sure if you are using a CI server, but I've added in where that could fit into the process too. This diagram assumes dedicated modes for the two developers, but that could be a shared database instead.

Answer 3

Yes, I believe this should work ok. Traditionally, the issue around merging branches has caused trouble with migration scripts although the beta of Migrations V2 is tackling this problem as well as others.

If you have some sort of build system linked to your repository, you could probably automate the latter part where it deploys to test with the use of the SQL Automation pack - for example something like TeamCity could be triggered by you doing the merge and then automatically update Test to save you manually needing to do it.