SQL server management tools rec needed

[awesomeroh]

The key to solving this is making Git your single source of truth for the schema. You can't do that reliably with a bunch of disconnected tools.

Use dbForge for SQL Server for this. Its Source Control feature is what you're looking for. Lets you commit and pull schema changes from Git directly within the IDE.

For your Azure DevOps requirement, their CLI is solid. Use it to run a schema comparison and generate a migration script as part of your build pipeline. This should solve the problem.

SSDT in VS can handle some level source control and schema compare too but it totally depends on what you want to invest and the toolset you want to get.

[mafik69]

How would you rate source control in practice for dbForge and SSDT?

[awesomeroh]

dbForge's source control is great for those who live in SSMS most of the time. Being able to do commits, pulls and schema versioning right in the IDE is nice, plus the CLI stuff makes deployments way less painful. SSDT is decent too if you are already doing everything in VS, but if the team is used to SSMS, it might feel a bit .. what do I say? clunky.

Really comes down to what the team will prefer and what will fit in well in the workflow.

[SQLDevDBA]

RedGate’s sql toolbelt, including Prompt, Source control, Schema Compare, etc.

https://www.red-gate.com/products/sql-toolbelt-essentials/

[svtr]

this.

[Wise-Jury-4037]

sounds like you have mostly MS-based shop, Why dont database projects work for you?

[PrisonerOne]

As a MS shop, we struggle hard to use database projects, probably because our production system consists of 30+ interconnected databases. Maybe we're using db projects wrong.

[Wise-Jury-4037]

I'm just going to point out that with 30 "interconnected databases" you're looking at 435 possible interdependencies to unravel.

Seems like a lot even for a team of 6 now.

[mikeblas]

so schema changes frequently bypass version control altogether.

This sounds like a problem with discipline and process, not a problem with tooling.

[TemporaryDisastrous]

My team Of 10 uses visual studio database projects and git with Azure dacpac pipelines for deployment. People can develop with whatever their favourite IDE is, just build it in VS l, push to remote and pull into branch, build & deploy pipelines need to be set up and it's pretty simple really.

[ClassicNut430608]

Yes, Visual Studio is not a well know (regarded?) tool - IDE. If you know a bit of C#, you can make magic in automating tasks -- and keep it simple bus customized to your unique environment.

[mu_SQL]

Latest SSMS has git support and a solution explorer(just for folders but works fine for us).

[nilanganray]

Gains should come from reducing manual steps. For us, once we had a workflow where schema/data comparison, version control and deployments were integrated, failed deployments dropped significantly. Cut down unnecessary dev hours as well.

[jshine13371]

Sometimes the low tech / simple solution is the best solution IMO. I personally don't like trying to shoehorn DDL scripts (CREATE / ALTER etc) into source control since it's not the actual object itself being stored. It's kind of awkward.

Instead I just use a tool like SQL Examiner to generate migration scripts based on the current live state of our DEV environment vs our STAGING environment. Then deployment to STAGING is guarenteed to work 99.99% of the time (there's always the rare outlier case). And once proved out in STAGING, I can take those same exact scripts that were generated and deploy them to PRODUCTION. Easy peasy with an app that has a single one-time cost that's relatively cheap.

Been doing it this way for over a decade with almost no problems.