Workflow for SQL Server + Azure SQL development?

Posted on

Question :

Lets assume the following:

  • 5 devs all working on laptops
  • Each develops/debugs on Azure compute emulator (code) and SQL Server (db) – both running locally on their laptops.

I’d like to have

  • These 5 SQL ‘Servers’ need to sync with each other
  • BONUS: If the 5 SQL servers can also sync with (non-production) Azure SQL database!

How can I set this up? The way we’re setup, all dev’s eventually diverge from the ‘reference’ database (both schema and data) and pruning it is a PITA. I’m hoping there are some tools to alleviate this issue.

Answer :

Developers should produce code, and there are obviously many ways to sync code changes (source control). Never touch the database. Ever. If they need any change in the database, modify the code that deploys/upgrades the database. Rails’s Migrations are an excellent example of doing it the right way. Visual studio has Database Projects. You can always roll your own custom solution. Post-fact comparison tools I don’t like much, but you can say is a personal opinion.

I’ve been having a similar issue with 8 devs and we have been working through RedGate Data Compare/RedGate Source Control. We have reviewed using it in Prod with synching up multiple environments, and doing rollouts/deployments/rollbacks with it. Outside of a few bugs and setup nuances it’s been a great tool. It’s probably one of the most widely used out there actually. I also asked around and there’s generally 2 options:

-Roll your own. I’ve spoken with employees who worked in high positions at Yahoo/Herbalife, and both had their own in house tools. The Yahoo one was pretty well developed but the Herbalife one was limited.

-Use RedGate. Schema/Data compare can help you synch those up, but using source control in the multi-developer model would probably be what you are looking for.

They have lots of free trials and you can extend your trial by typing in “I need more time” as the passcode. Their support guys are good, but sometimes slow in response as they are based mainly in the UK.

Leave a Reply

Your email address will not be published. Required fields are marked *