.NET tool for (T-)SQL schema/data scripting?


  • And then the murders began.

    We have an app that uses Red Gate's Comparison SDK to script off schema and some data changes from a test database before it gets restored from a current backup of production, then reapplies it afterwards.

    Well, Red Gate's deprecated their Comparison SDK. Long-term, that means this tool will eventually break once we upgrade to any version of SQL Server newer than 2016.

    Red Gate claims that their DLM Automation tools are a replacement, but they aren't:

    • PITA to invoke from C#
    • Need to build an XML file to specify objects to sync/not sync, instead of being able to get a list of current tables and mark yes/no based on our criteria
    • Requires per-user licensing instead of per-developer

    The first two aren't insurmountable, but the last one is.

    (Also, given their new ReadyRoll tool, I suspect that DLM Automation is not long for this world. I used to be a big Red Gate fan, but over the last 5 years I've come to realize that they seem to practice the CADT model of development. So far, I've lost Deployment Manager, server-based DLM Automation licensing, and now the Comparison SDK.)

    Does anybody have experience with alternative tools for scripting out schema/metadata differences in a .NET app?



  • @unperverted-vixen
    I use native SQL Server Data Tools (SSDT) at my current gig for having a source controlled version of the database scheme and one click deploys. You can import the schema from current databases to create new projects, and then set up publish profiles similar to publishing a web project.

    I've seen some hinkiness if you're not methodical in how you set the project up or if you don't keep SSDT as your single source of truth for schema changes. And VS 2017 had some hinkiness where they tried changing SSDT from an add-on to core and then VS 2017 was falling behind VS 2015 in what SSDT / SQL 2016/7 features it supported, because the add on for VS 2015 would get published but no VS 2017 software update happened for months afterwards. It looks like the next version of SSDT is going to become an add on for Vs 2017 as well to rectify that problem.


  • Garbage Person

    Fucking SIGH. Was just about to kick off a project using the fucking redgate comparison SDK for this.

    Fuck it, we'll query the system tables on the databases and sort it out ourselves.

    I'm sitting at the office doing exactly that for indexes anyway.

    Apparently I'm not allowed to build anything these days without resorting to engineering from first principles.

    Fortunately, if you only care about stored procedures, views, functions, tables, those principles are pretty easy.


  • And then the murders began.

    @izzion We use SSDT as the single source of truth for our schema in staging/production (though we use DLM Automation to push the changes rather than publish profiles). I could make this work for staging (just tell Octopus to rerun the last deploy's schema step).

    Dev is trickier; some devs go SSDT -> database, others do database -> SSDT. But even if we could buckle down on that, we still don't have any sort of automated publish set up for that environment. We'd have to get that set up (whether it's publish profiles or Octopus Deploy or something else), and then find a way to make our web app trigger that publish that after the restore is completed.

    And that only solves restoring the schema; it doesn't deal with any of the data we need to persist. :)

    Thanks for the thought, though!


  • Discourse touched me in a no-no place

    @unperverted-vixen said in .NET tool for (T-)SQL schema/data scripting?:

    per-user licensing instead of per-developer

    Just double checking: is that per user of their tools, or per user of the database including every single customer that you have?



  • @weng said in .NET tool for (T-)SQL schema/data scripting?:

    Fucking SIGH. Was just about to kick off a project using the fucking redgate comparison SDK for this.

    Fuck it, we'll query the system tables on the databases and sort it out ourselves.

    I'm sitting at the office doing exactly that for indexes anyway.

    Apparently I'm not allowed to build anything these days without resorting to engineering from first principles.

    Fortunately, if you only care about stored procedures, views, functions, tables, those principles are pretty easy.

    Yup. If your company's budget or any reason pervert you to buy and use ready-to-use solution, it doesn't harm to write one yourself.

    Afterall, MSSQL now supports INFORMATION_SCHEMA. If you care only after ordinary things like table/view/stored proc/function, it ought to be easy,


  • Fake News

    @cheong said in .NET tool for (T-)SQL schema/data scripting?:

    If your company's budget or any reason pervert prevents you to buy and use ready-to-use solution, it doesn't harm to write one yourself.

    FTFY.

    Afterall, MSSQL now supports INFORMATION_SCHEMA. If you care only after ordinary things like table/view/stored proc/function, it ought to be easy,

    The only good thing that INFORMATION_SCHEMA has going for it, is that it allows for portability between SQL Server and other SQL RDBMS. Otherwise, you're far better off using sys-schema objects, DMVs, etc.


  • FoxDev

    makes a note to look into SSDT

    (we don't have our DB schema in source control)

    (yet)


  • Fake News

    @unperverted-vixen My employer has created a couple different tools to solve these problems in-house, with a few features specific to our deployment process. I'm glad to see that MS has finally caught on that something like this is really needed. Still, it sounds like there are some rough edges on SSDT. @Weng, I'd roll my own as well if I were in your position.


  • And then the murders began.

    @dkf said in .NET tool for (T-)SQL schema/data scripting?:

    Just double checking: is that per user of their tools, or per user of the database including every single customer that you have?

    Assuming I'm reading the license agreement correctly, it's per user of the tools. But that's still another 50-100 SQL Toolbelt licenses we'd have to buy at $3000 a pop. Even if I end up rolling my own a la @Weng, it would have to take me multiple years before just buying it would work out cheaper for the company...

    @raceprouk said in .NET tool for (T-)SQL schema/data scripting?:

    (we don't have our DB schema in source control)

    :doing_it_wrong:

    We didn't either until 4-5 years ago, admittedly. And we still don't have our static data in source control (as "this is what it should look like" - all of the scripts to change it in prod are source controlled at least), but that's probably never going to happen. :(



  • @lolwhat said in .NET tool for (T-)SQL schema/data scripting?:

    that MS has finally caught on that something like this is really needed

    SSDT was introduce in Visual Studio 2010 - The first release was in September 2012 - over 5 years ago.....

    Make me wonder just who is "finally catching on".... :) :)


  • Garbage Person

    @cheong said in .NET tool for (T-)SQL schema/data scripting?:

    @weng said in .NET tool for (T-)SQL schema/data scripting?:

    Fucking SIGH. Was just about to kick off a project using the fucking redgate comparison SDK for this.

    Fuck it, we'll query the system tables on the databases and sort it out ourselves.

    I'm sitting at the office doing exactly that for indexes anyway.

    Apparently I'm not allowed to build anything these days without resorting to engineering from first principles.

    Fortunately, if you only care about stored procedures, views, functions, tables, those principles are pretty easy.

    Yup. If your company's budget or any reason pervert you to buy and use ready-to-use solution, it doesn't harm to write one yourself.

    Afterall, MSSQL now supports INFORMATION_SCHEMA. If you care only after ordinary things like table/view/stored proc/function, it ought to be easy,

    We actually procured the Redgate SDK a few years back. As with all maintenance-and-process related things in my development backlog, it has been eternally on hold pending the completion of pointless shit that seagull management wants.

    We were about to kick off the project finally because my boss managed to shake loose the entire maintenance-and-process queue by mentioning some items in it to the VP during an escalation over some preventable incident, so my director is now feeling pressure from above to let us fix shit we could have fixed years ago.


  • Fake News

    @thecpuwizard Fair enough, you get one Internetpointzzzzz from me. 😛



  • @raceprouk said in .NET tool for (T-)SQL schema/data scripting?:

    (we don't have our DB schema in source control)

    At all? What's your disaster plan if you're wiped and have to start over?


  • FoxDev

    @blakeyrat said in .NET tool for (T-)SQL schema/data scripting?:

    @raceprouk said in .NET tool for (T-)SQL schema/data scripting?:

    (we don't have our DB schema in source control)

    At all? What's your disaster plan if you're wiped and have to start over?

    Azure DB backups. And yes, they work: We've done it before.


  • Impossible Mission - B

    @unperverted-vixen said in .NET tool for (T-)SQL schema/data scripting?:

    I used to be a big Red Gate fan, but over the last 5 years I've come to realize that they seem to practice the CADT model of development. then I saw what they did to Reflector, and I've had zero respect for them ever since.

    FTFY. The Parable of the Scorpion applies here.


  • And then the murders began.

    @masonwheeler I'd forgotten that that was Red Gate, to be honest - that whole affair happened before I got into .NET development.


  • Impossible Mission - B

    @unperverted-vixen Yeah, that was them. They didn't make the promise they broke, but when you buy out a product, you take upon yourself its liabilities too. When they violated that principle, they lost all trustworthiness.


Log in to reply