4 ms·

> My cries to use git were unheeded (would have required upskilling everyone on the team). What is the best practice workflow using git with SQL server views/p

> My cries to use git were unheeded (would have required upskilling everyone on the team). What is the best practice workflow using git with SQL server views/procedures? Can you actually somehow track changes in the views/procs themselves so that if someone happens to run ALTER VIEW, git diff is going to show something?

manigandham 8y ago
It's mostly about using git to ensure a consistent snapshot as changes are made rather than updating one procedure at a time. If you are asking about SQL text then a good formatter would make it easy to see the physical diffs. Otherwise there are various vendors and tools that parse SQL and show the logical diffs in queries and schemas, along with doing deployments, backups, syncing changes, etc.
dwd 8y ago
Every conversation I've ever had came back to using RedGate SQL Compare to diff databases and TeamCity for CI. You basically shouldn't allow anyone to modify anything without it being scripted (bonus points if it comes with a rollback and is repeatable for testing). Your scripts then all go into Git.
- RegBarclay 8y ago
  RedGate's SQL Change Automation (formerly ReadyRoll) is pretty slick. DbUp is a good, free alternative. I second the rollback and repeatability bonus. Every script should leave the database in either the new state or the previous good state no matter how many times it's run.
  - beefield 8y ago
    > Every script should leave the database in either the new state or the previous good state no matter how many times it's run. I wonder if there were somewhere a website to describe good idioms to achieve this?
    - dwd 8y ago
      Where I've seen this process work, testing for repeatability was part of the peer review process where any sql scripts to be reviewed were executed. Having a second person run a script is the best way to ensure no mistakes.
bonesss 8y ago
You can version your scripts & DB as they get pushed with a common tracking number. Personally I think it's best to establish a workflow where manual changes to the DB would be pointless and likely to result in them being overwritten. Outside of commercial tools dedicated to the purpose: you can query the DB for the content of the procedures/tables and compare them to a given set of scripts, or the most recent expected version. Auto-generated ORM models can be used to validate table/view composition for a given DB/App version, as well. Having these capacities baked into the versioning and upgrade process can do a lot over time to correct deviating schemas and train developers away from meddling with DBs outside the normal update procedure :)

manigandham 8y ago

It's mostly about using git to ensure a consistent snapshot as changes are made rather than updating one procedure at a time. If you are asking about SQL text then a good formatter would make it easy to see the physical diffs. Otherwise there are various vendors and tools that parse SQL and show the logical diffs in queries and schemas, along with doing deployments, backups, syncing changes, etc.

dwd 8y ago

Every conversation I've ever had came back to using RedGate SQL Compare to diff databases and TeamCity for CI. You basically shouldn't allow anyone to modify anything without it being scripted (bonus points if it comes with a rollback and is repeatable for testing). Your scripts then all go into Git.

RegBarclay 8y ago
RedGate's SQL Change Automation (formerly ReadyRoll) is pretty slick. DbUp is a good, free alternative. I second the rollback and repeatability bonus. Every script should leave the database in either the new state or the previous good state no matter how many times it's run.
- beefield 8y ago
  > Every script should leave the database in either the new state or the previous good state no matter how many times it's run. I wonder if there were somewhere a website to describe good idioms to achieve this?
  - dwd 8y ago
    Where I've seen this process work, testing for repeatability was part of the peer review process where any sql scripts to be reviewed were executed. Having a second person run a script is the best way to ensure no mistakes.

bonesss 8y ago

You can version your scripts & DB as they get pushed with a common tracking number. Personally I think it's best to establish a workflow where manual changes to the DB would be pointless and likely to result in them being overwritten. Outside of commercial tools dedicated to the purpose: you can query the DB for the content of the procedures/tables and compare them to a given set of scripts, or the most recent expected version. Auto-generated ORM models can be used to validate table/view composition for a given DB/App version, as well. Having these capacities baked into the versioning and upgrade process can do a lot over time to correct deviating schemas and train developers away from meddling with DBs outside the normal update procedure :)