ReadyRoll vs DLM Automation

Sean_B · August 4, 2017 10:34PM

I am new to RedGate. My manager has purchased us licenses, but I am a bit confused:

1. What is the difference between ReadyRoll and DLM Autoamtion? Their purposes seem to overlap considerably.
2. When would I use ReadyRoll?
3. When would I use DLM Automation?

Current Tools we Use: TFS, Visual Studio 2015, Red Gate, Telerik

AlexMBanks · August 7, 2017 9:55AM

You're right that they overlap in that they serve the same purpose - to automate the database build/test/deploy cycle.

The difference is in their approach: ReadyRoll uses (mostly) a traditional migrations-based approach, where you code up the changes as if you're working directly on a database (ALTER, ADD/DROP COLUMN, INSERT, etc.). It then runs the pre-written scripts in a pre-determined order. It gives developers complete control over the SQL that is written, but also gives them complete responsibility.

DLM Automation uses a few other tools, namely SQL Source Control and SQL Compare. SQL Source Control uses the state-based approach, in that you make your changes on the database and these are always checked into SVN as CREATE statements (generated by the tool). When you build/deploy, SQL Compare will look at your source (SVN, a database, or scripts folder) and your target (usually a database) and generate a migration script to get from A to B. This means you don't need to spend time writing a lot of SQL for simple changes, which is great, but it does mean that you are trusting the tool to get it right every time.

This is a quick summary - both tools provide some 'hybrid' features - ReadyRoll has Programmable Objects and SQL Source Control has Migration Scripts.

Your team will probably have a preference or standard for using one approach vs the other. Learn the process first, then the tools - both have great features and unique advantages and disadvantages.

Grant · August 7, 2017 12:11PM

If you're doing the vast majority of your work against the database in VisualStudio, it might be easier to go with ReadyRoll. If, on the other hand, you're doing the majority within SSMS, it might be easier to go with SQL Source Control. Then you also have to account for Migrations vs. State as mentioned above. The idea is to have multiple ways to get the job done so that we can better support your process rather than try to force you into a particular process.

Sean_B · August 8, 2017 11:44PM

I think my confusion is the two approaches do not seem to have clear implementations for both approaches. At least not clearly to my understanding.

ReadyRoll appears as stated: a migrations based DLM approach. This is done by creating a ReadyRoll Visual Studio Project and making the necessary project configurations thereafter. Readyroll is a dedicated Visual Studio Project Type, but where is the Visual Studio Project type equivalent for State based approaches?

There may be a way to implement a state based approach using VS and Redgate, but it's not nearly as elegant as the Migrations approach with ReadyRoll.

I find the same issue with Microsoft and the converse: Microsoft has a clean and elegant State based Solution with it's own VS Project Template: Data Project Template (using SSDT to drive the work).

How would I setup a Visual Studio Database project for State based approach using Red Gate tools?

It appears as if the built in Data Project offered by Microsoft is not supported or compatible with Red Gate Source control either, so I am still confused as to how to use RG for a State based approach.

What I am tasked to do in my workplace is to use RedGate tools, Visual Studio 2015 Enterprise, and TFS 2012 to come up wtih a Demo for both approaches.

I do understand the principles behind both approaches. My problem is the State side implementation from Red Gate remains a mystery. ReadyRoll seems to fit quite well into the Migrations part. I can Demo Ready Roll no problem. It's the State based approach I am at a loss as to how to setup with Red Gate tools. I can configure RG Source Control to point to TFS, but this does not associate the source control files to an actual Visual Studio Project. This seems to a serious incompleteness relative to ReadyRoll with Migrations.

Grant · August 9, 2017 2:03AM

The state-based approach wouldn't be in Visual Studio. SQL Source Control, our state-based tool, works with SQL Server Management Studio. That might be the issue. You can connect it to TFS and all the attendant tools. You just can't do it with Visual Studio.

David Atkinson · August 9, 2017 4:44PM

@Sean_B Out of interest, what is it about the state-based approach that you need over ReadyRoll's approach?

Sean_B · August 9, 2017 4:56PM

Thank you Grant. Yes, that's the missing piece of the puzzle. I can see a state-based approach from Sql Management Studio that works directly with the Version Control Back end. I just could not wrap my head around how we look at these files in the solution itself.

I will show this to management and try to explain the structure is different for State. This does help a lot Grant thank you. I was beating myself up thinking there was something I am missing, but apparently how I understood this working is actually how it works. lol

Sean_B · August 9, 2017 5:01PM

Hi David. My need is not really at all that big of a deal. We currently don't manage database changes here other than with the default appoach (write .sql files by hand and pass to dba to run).

I recently suggested to management we look at some tooling and showed him a 7 min video of what Red Gate source control does and he was impressed.

I have been tasked to choose a few tools and demo them. The way I understand db change management though there are two fundamental approaches: migrations or model/state approach.

I am trying to put a demo together for both approaches for three tools to demo for management soon.

Microsoft apparently does not have a Migrations approach tool unless I have missed it?

Red Gate covers both approaches well. I was just a wee bit confused about the difference in implementation (subtle difference, but important to note).

I don't decide what approach to use, I will let our esteemed managers decide that. I am just here for technical implementations.

I think the choice between approaches really depends on the nature of the product and the team that supports it. I think both approaches have their place.

David Atkinson · August 9, 2017 5:13PM

You're right. Microsoft only offers the state-based approach in Visual Studio.

Apologies for the "difference in implementation". We're actively working on this, and you will see improvements in this area over time.

Although ReadyRoll is a Visual Studio add-in, most ReadyRoll users have SSMS open and use this to apply changes to their dev instance. It doesn't matter which tool you use to make your changes. Back in Visual Studio, ReadyRoll's DBSync window will enumerate these changes and will offer to save them to the project.

Do let us know if you need any further help understanding the tools for the purposes of your demo!

Sean_B · August 9, 2017 5:21PM

I don't mind the difference in implementation, I was just confused for a while trying to understand it.

Once Grant confirmed RedGate implements the two approaches in a different way things all made sense to me. In my head I was expecting a VS Project Template for State/Model as well.

It would be great if there was, just all it ReadyModel or something, I dunno...

I have to demo for management soon. I expect them to prefer model approach to Migration, but I've been wrong before...

It' just harder to sell the Model approach for Red Gate to management right now. Management here wants to see everything in Visual Studio. I do prefer Red Gate overall. I will do my best to improvise.

Thanks guys. This clears up a lot of confusion.

Grant · August 9, 2017 10:51PM

Sean_B wrote: »

Thank you Grant. Yes, that's the missing piece of the puzzle. I can see a state-based approach from Sql Management Studio that works directly with the Version Control Back end. I just could not wrap my head around how we look at these files in the solution itself.

I will show this to management and try to explain the structure is different for State. This does help a lot Grant thank you. I was beating myself up thinking there was something I am missing, but apparently how I understood this working is actually how it works. lol

Happy I could help at all.

Grant · August 9, 2017 10:54PM

And since you are in the midst of exploring this, a very recent story was posted by a client on why they chose DLM over SSDT. It might be worth a read.

AlexMBanks · August 10, 2017 12:15PM

Sean_B wrote: »

I don't mind the difference in implementation, I was just confused for a while trying to understand it.

Once Grant confirmed RedGate implements the two approaches in a different way things all made sense to me. In my head I was expecting a VS Project Template for State/Model as well.

It would be great if there was, just all it ReadyModel or something, I dunno...

I have to demo for management soon. I expect them to prefer model approach to Migration, but I've been wrong before...

It' just harder to sell the Model approach for Red Gate to management right now. Management here wants to see everything in Visual Studio. I do prefer Red Gate overall. I will do my best to improvise.

Thanks guys. This clears up a lot of confusion.

In case it's of any use, we have been using the state-based approach in recent months but are considering switching over to ReadyRoll, which we're evaluating at the moment. The reasons for this are numerous, but here are a few:

ReadyRoll is deterministic. You write the script, you know exactly what SQL will be executed and in what order. You have all the responsibility (if deployment fails, you've done something wrong), but you also have all the control. With the State approach, the order of execution is not quite deterministic and you must trust the tool to get the dependencies right (if deployment fails, it's possible the tool has done something wrong). This makes our release teams somewhat nervous, as the final production SQL is generated at deploy time. Of course this can be reviewed and verified before running, but it's not quite the same as seeing an ordered set of scripts.
We've had a few edge cases where dependencies were not picked up correctly and needed a workaround. This is usually as simple as adding 'SELECT 1 FROM dbo.Table' at the top of a migration script to ensure it's picked up as a dependency, but it's difficult to know in advance when this is necessary. As I said, it was an edge case we were dealing with, and most of the time this works as expected. And it must be said that Redgate support have been great - whenever we've had issues they've responded promptly with an explanation and a solution.
The migrations approach is arguably more traditional, so you may find people are already comfortable with this. The state approach takes a bit of getting used to, particularly because you will need to revert to the migrations approach for some changes, e.g. to avoid data loss.
The tools for the state approach cut down a lot of boilerplate code writing and also make your source control cleaner to navigate, since everything is in its own file.

Obviously these observations are all situation-dependent and may be less or more important to others - they're just a few thoughts we've had over the last few weeks.

David Atkinson · August 10, 2017 12:23PM

Yes, in ReadyRoll the saved scripts are exactly what get executed, which gives you fine-grained control. Worth bearing in mind that ReadyRoll will auto-generate the migration scripts for you, which under the hood uses a comparison engine just like the state model does. The difference is that it's doing this at development-time, rather than at deployment-time. The distinction is how early in the development cycle the scripts are generated. ReadyRoll very much empowers developers to be responsible for not only the change in schema but how the change gets deployed.

Over time we intend to bring the ReadyRoll experience into SSMS, so although you need to switch back to VS today, it will improve as we push out new releases.

ReadyRoll vs DLM Automation

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk