Comments on: 100 Percent Online Deployments: Stage and Switchhttp://michaeljswart.com/2018/01/100-percent-online-deployments-stage-and-switch/
Database WhispererSat, 15 Dec 2018 13:27:37 +0000hourly1https://wordpress.org/?v=4.9.9By: Michael J. Swarthttp://michaeljswart.com/2018/01/100-percent-online-deployments-stage-and-switch/comment-page-1/#comment-213704
Fri, 19 Jan 2018 21:24:19 +0000http://michaeljswart.com/?p=5796#comment-213704Yes, lack of good tools is an issue. I would not consider our tool a great tool either. At least not for general consumption. But it works very well for our company. You wouldn’t believe the number of shortcuts we can take because our tool only needs to work for our company. For example, we don’t need to worry about things like assemblies, functions, linked servers or partitioned tables. We can ignore a huge number of object types that I haven’t mentioned because we simply don’t use them. 🙂
]]>By: Sergei Ryabkovhttp://michaeljswart.com/2018/01/100-percent-online-deployments-stage-and-switch/comment-page-1/#comment-213702
Fri, 19 Jan 2018 16:52:04 +0000http://michaeljswart.com/?p=5796#comment-213702Hi Michael,

Count me even more impressed, even if your script suggestion tool doesn’t handle all situations. Even Microsoft doesn’t seem to have developed SSDT from scratch: based on what I have heard, they licensed Redgate’s SQL Compare code in a one-time deal at some point, however I failed to find any proof of that transaction online.

Lack of good tools is one of the major factors that hinder SQL Server database CI/CD in my opinion. SSDT has limitations, Redgate DLM requires an almost $3,000 PER DEVELOPER license. Redgate’s SQL Compare SDK, which comes with a $800-dollar license for SQL Compare Pro allows you to distribute the app you create with the SDK up to 10 users (or machines) free of charge, but Redgate wants a fee for every additional machine/user after that. It might still be cheaper than developing things yourself from scratch, like you did. Sounds like it was expensive. By the way, I am not endorsing Redgate, even though I do like their tools, I just don’t know of any alternatives to SSDT and Redgate, assuming you want to be able to have the exact state of your database schema in source control (use state-based approach). As I said earlier, until yesterday, I haven’t heard of anybody combining the migration-based and the state-based approaches. I am pretty sure there are no commercial or open-source tools that do that. Maybe your company should consider selling the database tech you guys developed!

There is some hope that Microsoft is working on redoing SSDT right now: one can run SQL Server on Linux, the early versions of SQL Server Operations Studio, a cross-platform addition to (replacement for?) SSMS are out, but you still can’t run CI/CD for SQL Server on Linux, because SSDT is still Windows-only. Something has gotta be in the works. Maybe the new cross-platform SSDT will be better or, at least, open source. I guess we’ll see relatively soon.

That tool was written from scratch and I understand it was a lot of work. Using a third party SDK would have made the work easier, but because it’s meant to be used by every developer on their local machines, we wrote our own rather than use the kit. The scripts that are generated are just simple scripts, but they cover the majority of migration cases. Let’s say you have a database and have made some changes:

Restore an empty db at the previous version

Run the migration scripts that are checked in. The databases should be pretty close now.

Get a list of objects from both and compare

For differences, spit out the appropriate script

Which is easier said than done, especially for that last step. The thing that makes it easier, is that we make use of helper procedures (See the automation section in Blue Green Details)

Let me return the compliment and say ‘What a fantastic answer!’ It sounds like you have found a way to combine the state-based and the migration-based approaches. I have not heard of anyone doing that, until now. I really like your script suggestion tool and the automated checks you use to make sure that the schema snapshots and the migration scripts are consistent.

If you don’t mind, I’d like to ask a clarifying question about your script suggestion tool. Did you write it from scratch or is it based on some kind of third-party SDK (e.g. Redgate SQL Compare SDK)?

We use the migration-based approach like you mentioned. But surprisingly, our “source of truth” for what the definition should be is another set of create object scripts which are checked in separately. We do have the full definitions of all database objects in source control that you mentioned. And we use automated checks to avoid having our create-scripts diverge from migration-scripts.

During development, the check (Previous Clean Schema + Migration = Clean Schema) needs to pass before any changes can get merged into our main branch.
During deployment, we check that existing schema matches expected schema before the deployment process begins. We also have do a schema check for Green.

You did mention that there are two migrations here. Blue->Aqua and Aqua->Green. Although that’s true, our entire multi-step deployment process is so automated and coordinated that we usually think of it as two steps of a single migration. We don’t do a schema check for aqua.

So migration scripts becomes the responsibility of the developer. But the work is often easy to automate for simple cases. We make heavy use of a tool we wrote that can detect discrepancies between PreviousClean+Migration and Clean and suggest scripts. 90% of the time the suggested scripts are sufficient.

So to answer your question, I think it may be possible to use a state-based approach, because we do (in a way). But SSDT doesn’t help us too much except for the simplest changes. Our script suggestion tool we wrote helps us out much better. So I don’t think that any migration tool, can 100% always generate online-friendly schema changes. But there’s definitely opportunity for tools out there to be better than they are.

]]>By: Sergei Ryabkovhttp://michaeljswart.com/2018/01/100-percent-online-deployments-stage-and-switch/comment-page-1/#comment-213672
Thu, 18 Jan 2018 11:46:07 +0000http://michaeljswart.com/?p=5796#comment-213672Thank you for the great series! Linking to it will help me (and I believe a lot of other people) communicate on this important subject.

I chose to comment in this particular post because the SSDT error illustrates what might be an important planning consideration for people at the beginning of the database DevOps journey: do I use a state-based approach to migrations or a migration-based approach? With state-based approach, we declaratively describe the end state of the database and let the tools, usually with a bit of help from developer, figure out how to get us there. That’s the approach that tools like SSDT and Redgate tools other than ReadyRoll take. With state-based approach, state is explicit, migrations are implicit. In this series, there would be three states: blue, aqua, green. If we were using SSTD, we would have three revisions of the SSDT database project and we would do two deployments: one for the aqua revision and the other one for the green revision.

With migration-based approach, we define each migration by creating migration scripts, i.e. explicitly defining the path between neighboring states of the database. With migration-based approach, migrations are explicit, and have to be manually coded by a developer, and the state of the database is implicit. In this series, we would create two explicit migrations: blue -> aqua and aqua -> green. This is the approach that (I believe) this series implicitly used. There are some frameworks that help with migration-based approach (e.g. ReadyRoll mentioned above – not an endorsement, just an example), but usually such framework focus on the organization of migration scripts (e.g. some consistent naming convention), recording of migrations that have been completed in a database table and providing some runner for the migration scripts. The hard work of creating migration scripts must be done by a developer.

In my experience, a lot of teams choose state-based approach because it is nice to see full definitions of all database objects in source control and not have to worry about hand-crafting the migration scripts. They might also lack expertise to hand-craft complex online-friendly migration scripts.

Here, finally, comes the question: based on your experience, do you think that it is possible to use state-based approach to provide online migrations using tools like SSDT on non-trivial OLTP databases? Or if the current state of state-based migration tools is such that they won’t be able to do the job of generating online-friendly database migration scripts?