Redgate SQL Change Automation - Explore/Adopt?

[StephanieHerr]

For new projects with a backend SQL Server DB or Azure SQL DB, it would be great to use Redgate's SQL Change Automation solution (aka dogfooding).

[fffej]

Dogfooding would get a big thumbs up from me and I'd put it in Adopt.

[garethbragg]

I agree it's a thing we should do (assuming the use case is suitable).

What products would it be relevant for? What are they doing now, if not using SCA?

Are there any instances of us doing this anywhere?

(I'm wary of putting something in Adopt because we "think it should" be there, rather than reflecting where we actually are.)

[tugberkugurlu]

I would be up for this if we have our systems hosted and we control the database deployment (e.g. Azure SQL DB case, etc.). However, there are still trade-offs which I am not sure we would want to take:

• We are releasing our products to our customers environment and we need to get from one version of the database schema to the latest during the deployment. If we were to use SCA, how would that look? Want to be the licensing of the product here as we need to ship this to our customer's environment along with the product now?
• In products where we use Entity Framework Core, we are able to generate huge amount of boilerplate query/update/insert T-SQL code safely through it. We also have a mechanism to fall back to T-SQL if we need to. How would this look like then? Do we need to write all the T-SQL by hand then? Is this something we want to do?

Currently, two products successfully use EF Core (one of them is a stable one, SQL Clone and the other is at the private preview stage, SQL Data Catalog).

Therefore, I am slightly against this considering our current state. We can try this, see the results but I wouldn't say this is Adopt.

[StephanieHerr]

@tugberkugurlu - for remote deployments, check out https://documentation.red-gate.com/sca3/tutorials/worked-examples/embed-change-script-into-application

Unfortunately, I think you would need to write all the T-SQL by hand. @way0utwest - is there anything that can help here?

I'd actually love for you to try it and give us feedback. If it doesn't work for some reason, that's what we need to know, but I understand you have other priorities. Maybe some kind of timebox?

I think SQL Monitor also uses EF.

The dtw project to kill RavenDB in DLM Dashboard and replace it with a backend SQL Server also went EF.

I'm not sure what our web team is doing.

I've talked to Steve Jones about possibly using SCA for SQL Server Central in the future...

[ChrisHurley]

We're using EF Core code-first as an ORM (I don't think our database access code contains any direct SQL querying), and it generates schema migrations for us (which we can then add custom SQL to for data migrations if required). As I understand it, using SCA would separate the in-app model from the database schema and require us to make the changes in two places and keep them in sync.

The system we have works fairly well in a situation where the database contains basically no code (there are no stored procedures and the only triggers are to defend against end users manually tampering with it) and it just exists to persist our application's model objects - there's never any development which is purely on the database. It's only ever changed to support changes to the objects the application needs to persist or the way the application needs to query them, so coupling to the application's persistence code makes sense.

I guess the perception I have is that SCA makes more sense when the database is more of an artefact in its own right, which perhaps isn't fully owned by one application, and which might contain code which needs to be maintained independently of any consumers?

[way0utwest]

Here are my thoughts.

Not using SCA in house shows there is confusion and resistance to our tools. We ought to better understand that, which could help us sell things. Plenty of developers say “I don’t need a tool, EF code first just works” and it may. I likely does here, though I’d argue that the work being done might not be well thought through from the database side of things. The modeling from Code First doesn’t necessarily build a good database structure. I'd also argue that the database used for SQL Clone isn't necessarily just going to be one application, as we'll have needs for integration with other systems, potentially import export of data, and more. A little view of the database side of modeling would be good, but I can't do more than advocate this to developers.

For the EF stuff, the migrations you are getting from Code-First are the same migrations you'd get from SCA. In fact, you could do the code first stuff and then import those migrations into SCA and edit them. If you (Chris et al) haven't tried this, I'd think the SCA team ought to come sit with you for an hour and go over the flow of developing and releasing the database code.

[tugberkugurlu]

I'd also argue that the database used for SQL Clone isn't necessarily just going to be one application, as we'll have needs for integration with other systems, potentially import export of data, and more.

I don't believe we will have or suggest any integrations through data storage technology. At least this is what I am aware of. Our technical strategy sort of steers us to this direction by stating that all capabilities should be API-led. How the data is stored is purely implementation level concern and integrating at this level could be very costly if the data storage technology turns out to be the wrong choice (ahem, #34) due to evolving demands and needs, in view of various aspects such as performance, new query models, etc.

As exampled here, I strongly believe that this should pretty much the way we open our capabilities to the outside world: https://documentation.red-gate.com/display/SDPS1/Working+With+Classification+REST+API

[ChrisLambrou]

FWIW, I think we should make an important distinction between products and services where Redgate directly manage and maintain the databases (think of hosted offerings) vs products that use a database deployed within the customers' own environments (i.e. Clone, Monitor, Data Catalog, Dashboard, etc.). For the former, we really ought to be dog-fooding our own stuff. It's embarrassing that we're not doing this already. For the latter, SCA is just inappropriate. In those cases, things like EF migrations (Clone, Data Catalog) and Monitor's home-grown migrations approach are a much better fit.

[ChrisLambrou]

That said, it's been a little while since I've worked with our SCA offerings, and I'd be really interested to know how SCA could support a migrations-base approach (that's really similar to the home-grown system we have in Monitor, which has been rock-solid and easy to work with over the years).

[way0utwest]

I'd urge you all to watch some SCA video or get with the team. You ought to know how our products work.

For databases we use internally, I'd still argue that users will want to get data out, we may build tools to import or export data, or really, make it easy for them to do so. We ought to consider better data storage techniques. That's the data modeling rant, which if often ignored when you do code first.

I'll also argue that in any of these tools, the way you've done it, or that has worked is a consideration, but you're falling into the same trap as our customers. You work around holes and problems that SCA solves.

Here's how I'd envision this working:

1. you make changes in EF, or nHibernate, or whatever think you want.
2. SCA imports those changes into scripts.
3. If necessary, you edit/modify these scripts.
4. You use SCA to script and deploy those changes, including distributing the scripts to customers.

That's really it. You ignore the EF migrations. They can generate, but you don't deploy from them.

[ChrisLambrou]

I'd urge you all to watch some SCA video or get with the team. You ought to know how our products work.

I've watched most of the videos, and I've either developed and/or actively used most of our SCA tools, but I'm still finding it hard to imagine what a great approach to using SCA for products where the db is outside of our direct control looks like. EF migrations, on the other hand, supports this out of the box. SCA needs to better this.

I definitely agree with you about the data modelling issues. ORMs in general, and EF in particular, make it very easy to produce dreadful database designs. SCA wins hands-down in this regard. 😁

[garethbragg]

Based on this discussion, I don't think we can put SCA in the Adopt category of the tech radar. There's clearly too much to figure out for it to be our defacto choice at the moment.

I'll open a PR to put this in Explore.

[ChrisHurley]

We actively discourage people from integrating with our database - we provide an API to interact with Clone in the correct manner, and the only code we have added to it is to prevent people deleting rows from it. Users modifying the database and expecting that to work has been a source of support tickets, and one of our anxieties around using a SQL Server database was that people might write things that interact with it directly, which might break at any time. (We also considered the use of a document database at the time for some of these reasons, but it had deployment and maintenance disadvantages.)

We don't throw random domain objects into EF Core; we do have objects specifically created to describe the intended database schema, so I'm not sure how we'd do a better job without improving our own knowledge/experience around designing schemas. I think in general we'd prefer to avoid too much cleverness in the database as long as we can avoid it.

It does feel bad to not be dogfooding, I agree (and I certainly think we should be looking into using SCA for our internal systems). And there are some small changes we've introduced in the database which aren't reflected in EF Core (because they're not directly part of the model). But if we did start to use SCA I'd want to ensure that the process was as integrated into our development as what we currently have, and ideally maintained a single source of truth about the database model.

[way0utwest]

I think Explore is a fine place here. I'll volunteer Mr. Hurley or Mr. Lambrou here as they seem to doubt that this is very useful. I wouldn't argue it's better, but I would argue that using this and experimenting to better understand how it works, especially compared to EF, is a good move for Redgate. This isn't about cleverness, but rather building better products at Redgate.

If SCA substantially slows you, then there is work to do, but it's good feedback to give internally.

[fffej]

This is a tough one - changing from EF --> SCA doesn't seem like something we'd do (high engineering cost, doesn't directly add any value) and I only want to put SCA in Explore if we've actively got a team committed to using it. At the moment there (sadly) doesn't seem to be any teams in a place to do this.

Perhaps @musmuris or @SimonHolness have an opportunity to use it in our internal systems and could commit to exploring it there?

[SimonHolness]

We're certainly not against dogfooding (drinking our own champagne?) and some tools (Prompt, Compare) get lots of use up here, but with the schema-change related tooling we're often not great exemplars. Our schema(s) are really quite stable. Although we use EF, it tends to be a hand-rolled database-first approach and we don't use migrations. Not that we're averse to them for some reason, just because in general those aren't the big, thorny problems we're dealing with. Our 'main' schemas get small changes on average every couple of months.

FWIW our current process is to create a change script with Compare and source control that script and the new schema (all our schemas are already source controlled). Most systems will be either provisioned clean from source control or cloned from live (possibly with masking), so update scripts are probably only run against one or two systems at most.

[TheEadie]

FWIW our current process is to create a change script with Compare and source control that script and the new schema (all our schemas are already source controlled).

This is the process SCA follows so it would seem to be a good fit if we were looking to trial using it internally. I can understand it might not be the highest priority now but it would be good to get feedback on the product.

[fffej]

Since it sounds like SCA could be a good fit for CORE, I'm going to create a PR for this (#51).

I think the push for this needs to come from the versioning teams to demonstrate why this is a good fit for our internal systems.

[musmuris]

This comes round ever so often. We have looked into the previous systems - SQL source control & ReadyRoll - but each time the people looking at it concluded it wouldn't buy us anything.

Very happy to talk again and look at what the current products does, but we should look into why we haven't taken it in the past and if there are changes that could be made that make it compelling for us - as there might be more people out there who also find it compelling then.

As Simon says, we don't change schema's that often and the effort to learn the tool each time outweighs the effort to just write the SQL, or just spin up SQL Compare to do the changes. (For the record we don't do code first with EF - we just write C# to match the tables as needed and make sure EF doesn't attempt to change the schema)

[way0utwest]

I'll leave one last comment here. This isn't just about you (any team, any developer). This is about making Redgate better. Don't look at the rate of schema change, but rather at the process for doing these changes.

If developers don't find value in this, then we should have the db devops and sca teams, and certainly UX, sitting with them as they make an attempt and discover why they find problems and a lack of ingenious simplicity. If they find it hard to use, then let's determine what the rough edges are.

If this is simply "we think it's slower", then I'm sure we can get support or SCA to spend a few minutes helping you relearn things.