It can also be that someone migrates the DB in 2.7.3 (to postgres for example) and THEN upgrade such DB to 2.8.
And (at least as I see it) we do not have to release any tools for that. In fact I'd strongly discourage writing tool doing it. >From the experience of 1.10 - 2.* migration, If we release a tool, then we will have to handle (or at least it will be expected from us - maintainers) all kinds of problems with configurations, encodings, various configurations parameters of both Postgres and MsSQL etc. etc. We had a lot of experience with migrating 1.10 -> 2.x where there were some tiny problems with migration and - since we had upgrade check released https://pypi.org/project/apache-airflow-upgrade-check/ people expected it to work and even with most basic problems they could fix themselves they came back to us with "fix the tool" rather than look at the specific problems at hand they had and try to find solutions. People could have fine-tuned and modified their DBs and add their own "fixes" (and they might not be even aware of that as it was done a year earlier by some admins who already left etc. etc. Those were precisely the stories we saw when people migrated to Airflow 2. I seriously doubt we can write a robust "run me and it will work" migration tool for this case. And I would be very much against doing it. My suggestion if someone will be doing it, is to describe how you can do such migration with as low-level set of tools involving manual steps where the person doing the migration should be knowledgeable with the DBs and will be able to inspect and correct the states of the migration following clear-text information. It just needs to be done by someone to confirm it CAN be done with some effort from the side of the DB 1) export MsSQL DB to file 2) do set of those manual modifications or search & replace to make it works for Postgres (or MySQL) 3) Create empty Airflow DB using "airflow migrate" in Postgres (or MySQL) 3) Import the export to Postgres (or MySQL) Step 2 might require (and it should be very clear) manual adjustments to specific cases (like encodings etc. The export should be clear-text, with regular CREATE / INSERT statements so that in case of an problems, the users (not maintainers) will be able to figure out what other search/replace they need to do make it works. Plain SQL. No structure export/import - just data transfer. The target DB should be just standard DB of airflow created as usual with `airflow db`. And the process itself could have limitations It might take hours and GBs of disk space. Sometimes when it will be failing it might require to start the migration from scratch. It might neeed to run `airflow db clean` to limit amount of data in the file and to allow such manual modifications. It might only focus on migration of some data this way and then do manual "airflow CLI" export/import of data like connections, users etc. And it should involve the necessity of diagnosing and possibly manually fixing the export/import files if needs be, without expecting the imaginary "tool" doing it automatically. Our DB is relatively small, the relations are relatively few. But - looking at examples for the past, it will be far easier for each individual person to solve any specific problems they have in their own version of MsSQL than for us to write a robust and generic tool to fix those problems. And our discussions/slack might be a good place where such users could share their aproach with each other so that they can learn from each other - we could create such dedicated slack channel. We would help - of course - as usual, but without promises that there will be a working "one button" solutions. That's at least how I would personally approach this. Just explain and describe general approach and prove that it can work, but also set expectations that it might require an effort to complete it from the side of the of the MsSQL database. I think it would be quite unfair to set the expectations that we can have such a robust one-button migration. I don't think we can. But maybe that's the experience from seing some of the problems of people migrating from 1.10 to 2 several years ago - where all kinds of things (in MySQL usually) went often wrong. J. On Wed, Oct 25, 2023 at 7:12 PM Andrey Anshin <andrey.ans...@taragol.is> wrote: > > Need to habe at least a DB scheme upgrade supported for MsSQL in order to > be able to migrade to a new DB engine in 2.8.0 > > I think we need prohibit upgrade for unsupported DB > > E.g. if dialect not in ('postgresql', 'mysql', 'sqlite') then raise an > error > Keep upgrade db schema for unsupported DB might stop us to implement other > stuff around DB schema, e.g.: https://github.com/apache/airflow/pull/34112 > So my assumption that MsSQL will have support until Airflow 2.7.x > > ---- > Best Wishes > *Andrey Anshin* > > > > On Wed, 25 Oct 2023 at 21:00, Scheffler Jens (XC-DX/ETV5) > <jens.scheff...@de.bosch.com.invalid> wrote: > > > In response, off topic from Jareks statement: is somebody (already) on > the > > migration plan MsSQL->Postgres or do we need to find a volunteer not to > > delay 2.8 release? > > In a matter of preparing would be best if a "tool" is released wirh 2.7.3 > > else we need to habe at least a DB scheme upgrade supported for MsSQL in > > order to be able to migrade to a new DB engine in 2.8.0 w/o need of > > transformation of structures for people being stuck on 2.7.3 wirh MsSQL. > > > > Sent from Outlook for iOS<https://aka.ms/o0ukef> > > ________________________________ > > From: Jarek Potiuk <ja...@potiuk.com> > > Sent: Wednesday, October 25, 2023 2:21:29 PM > > To: Amogh Desai <amoghdesai....@gmail.com> > > Cc: dev@airflow.apache.org <dev@airflow.apache.org> > > Subject: Re: Keep Mssql support > > > > > The alternatives suggested by @Jarek Potiuk <ja...@potiuk.com> are > > something which is doable. We need to realise that these plugins are for > > the community and we can only support it if > > "majority" of the community uses it and is willing to maintain it :) > > > > Just to clarify - it's not about plugins, it's about "core" metastore > > support for MSSQL. The MSSQL provider will remain as is, but Airflow will > > lose the ability of using MsSQL as backend for > > scheduling/tasks/UI/executors/communication with workers via DB. > > > > The overhead / extra maintenance there is to make sure that all the > > queries, performance, locking and anything else airflow uses to make > "core" > > works with MsSQL. > > > > In Airflow 2.8 the only remaining backends will be MySQL 8 (as MySQL 5.7 > > reaches end of life in a week), and Postgres 12-16 (in 2 weeks Postgres > 11 > > reaches EOL and we will drop it). > > Those will be the only choices for Airflow 2.8 - providing that we will > > remove MsSQL as planned (condition for that is that we describe a viable > > migration path for MsSQL users). > > > > J. > > > > > > > > On Wed, Oct 25, 2023 at 1:23 PM Amogh Desai <amoghdesai....@gmail.com> > > wrote: > > > > > I agree with the comments and where this discussion has led to. > > > > > > The alternatives suggested by @Jarek Potiuk <ja...@potiuk.com> are > > > something which is doable. We need to realise that these plugins are > for > > > the community and we can only support it if > > > "majority" of the community uses it and is willing to maintain it :) > > > > > > > > > Thanks & Regards, > > > Amogh Desai > > > > > > On Wed, Oct 25, 2023 at 4:13 PM Kaxil Naik <kaxiln...@gmail.com> > wrote: > > > > > >> Yeah agreed, I don’t think it is worth keeping the support of MSSQL > > given > > >> the amount of usage vs the maintenance effort required. > > >> > > >> On Tue, 24 Oct 2023 at 23:07, Jarek Potiuk <ja...@potiuk.com> wrote: > > >> > > >> > Yes. Agree with Andrey. I think our experience from the last few > > years > > >> was > > >> > "very" bad. The number of mssql users is very small. And the time > that > > >> > maintainers and community members lose on various problems with it > is > > >> huge. > > >> > Quite often every time we added a new feature requiring some new db > > >> > functionality, quite a lot of overhead was spent by the one adding > new > > >> > features to solve the problems coming just from MSSQL support. It's > > not > > >> > "existing" issues - it's that it generally slows us down with making > > >> > changes > > >> > > > >> > I think there are two options for you when you. It's not "open > > issues", > > >> > it's the maintenance > > >> > > > >> > * switch to another backend (recommended). And it's not as complex > as > > >> you > > >> > think. You can also use managed DB with all that is needed > > >> > (backup/maintenance), you do not have to manage it yourself. There > are > > >> some > > >> > excellent postgres options available. > > >> > > > >> > * have your own fork airflow and keep the tests running and make > your > > >> copy > > >> > works for MSSQL if you insist on keeping it. Since you already seem > to > > >> be > > >> > ready to spend your engineering time on it, that seems doable. > > >> > > > >> > The second option I think might even be a business opportunity - for > > >> your > > >> > company or for anyone who would like to do it. Someone could even > > offer > > >> it > > >> > as a service or as a version to support it for others and make a > small > > >> > business out of it if you are really so committed to it I guess, > > >> including > > >> > support for any mssql problems. > > >> > > > >> > That would actually be awesome if someone does it. > > >> > > > >> > J. > > >> > > > >> > > > >> > On Tue, Oct 24, 2023 at 11:44 PM Andrey Anshin < > > >> andrey.ans...@taragol.is> > > >> > wrote: > > >> > > > >> > > I don’t think there is any possibility left to keep MS SQL Server > as > > >> DB > > >> > > backend for Airflow. > > >> > > > > >> > > I add Elad's message from the original discussion: > > >> > > > > > https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.apache.org%2Fthread%2Fr06j306hldg03g2my1pd4nyjxg78b3h4&data=05%7C01%7CJens.Scheffler%40de.bosch.com%7C6e16370a4474475d1b7908dbd554f6ea%7C0ae51e1907c84e4bbb6d648ee58410f4%7C0%7C0%7C638338333124062597%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=QdbRqzbHV0th6QqgrottUfhT%2FdQ1Pn8xOOlNytq4ojo%3D&reserved=0 > > <https://lists.apache.org/thread/r06j306hldg03g2my1pd4nyjxg78b3h4> > > >> > > Because it cleary describe what is happen with MS SQL as DB > backend > > >> for > > >> > the > > >> > > last 1.5 years > > >> > > > > >> > > > During this time we hoped it would become stable and widely > > adopted. > > >> > > > To my taste MsSQL a backend has left a niche and is *not* worth > > the > > >> > > > >> > > maintenance > > >> > > of it in our CI. > > >> > > > > >> > > I also want to note the following points > > >> > > - MS SQL have unstable tests in Airflow CI, and some cases we even > > >> don't > > >> > > run them for the last couple months (or even longer) > > >> > > - In additional it taking 2x memory than any other backend > > >> > > - Lack of ARM support, this is also quite important because it > > prevent > > >> > > maintainers to check some sort of things in their M1/M2 laptops > > >> > > - Additional backend required extra effort for any contributors > who > > >> want > > >> > to > > >> > > add new feature that touches DB > > >> > > > > >> > > > > >> > > This has always been an experimental feature which are described > in > > >> > AIrflow > > >> > > Release Process: > > >> > > > > >> > > > > >> > > > >> > > > https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fairflow.apache.org%2Fdocs%2Fapache-airflow%2Fstable%2Frelease-process.html%23experimental-features&data=05%7C01%7CJens.Scheffler%40de.bosch.com%7C6e16370a4474475d1b7908dbd554f6ea%7C0ae51e1907c84e4bbb6d648ee58410f4%7C0%7C0%7C638338333124218869%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2B%2FEleat699dUWHjfzNmFxTh0F%2Bhtvi5BEX1gi6eANck%3D&reserved=0 > > < > > > https://airflow.apache.org/docs/apache-airflow/stable/release-process.html#experimental-features > > > > > >> > > , I would recommend your team focuses on Airflow on Postgres > rather > > >> than > > >> > > hanging on to vague hope that MS SQL keeping in Airflow. > > >> > > > > >> > > Quite a few companies provide Managed Airflow, see: > > >> > > > > > https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fairflow.apache.org%2Fecosystem%2F%23airflow-as-a-service&data=05%7C01%7CJens.Scheffler%40de.bosch.com%7C6e16370a4474475d1b7908dbd554f6ea%7C0ae51e1907c84e4bbb6d648ee58410f4%7C0%7C0%7C638338333124218869%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=HMaVb8MTFdsJrSY6af4fgRRiHzkuuu2W0gJ4DUG%2FmLE%3D&reserved=0 > > <https://airflow.apache.org/ecosystem/#airflow-as-a-service> (this is > > >> not > > >> > > complete list) and AFAIK none of them use any other backend rather > > >> than > > >> > > Postgres, maybe one exception with Google Composer v1 which seems > > use > > >> > > MySQL, even on Azure Data Factory Managed Airflow use Postgres as > DB > > >> > > backend, see: > > >> > > > > >> > > > > >> > > > >> > > > https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flearn.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fconcept-managed-airflow%23architecture&data=05%7C01%7CJens.Scheffler%40de.bosch.com%7C6e16370a4474475d1b7908dbd554f6ea%7C0ae51e1907c84e4bbb6d648ee58410f4%7C0%7C0%7C638338333124218869%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=i5Iu4Sbg5f%2FYPrmoVun8d8Owk%2FWvxKxRwXr1oosuxrY%3D&reserved=0 > > < > > > https://learn.microsoft.com/en-us/azure/data-factory/concept-managed-airflow#architecture > > > > > >> > > > > >> > > ---- > > >> > > Best Wishes > > >> > > *Andrey Anshin* > > >> > > > > >> > > > > >> > > > > >> > > On Mon, 23 Oct 2023 at 23:01, agateaaa <agate...@gmail.com> > wrote: > > >> > > > > >> > > > Hi All: > > >> > > > > > >> > > > Mssql support was voted to be dropped. > > >> > > > > > > https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.apache.org%2Fthread%2Fr06j306hldg03g2my1pd4nyjxg78b3h4&data=05%7C01%7CJens.Scheffler%40de.bosch.com%7C6e16370a4474475d1b7908dbd554f6ea%7C0ae51e1907c84e4bbb6d648ee58410f4%7C0%7C0%7C638338333124218869%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=l0eY8qkZcX6Vz%2FCkTC%2Bfq%2BDFlbvRRd2wC3hhpacYJ2c%3D&reserved=0 > > <https://lists.apache.org/thread/r06j306hldg03g2my1pd4nyjxg78b3h4> > > >> > > > > > >> > > > One of our product requirements is that we can only use the > Mssql > > >> > > database. > > >> > > > The product that uses airflow is installed with a suite of 8-10 > > >> other > > >> > > > products that all use Mssql database as their database. > Preferably > > >> we > > >> > do > > >> > > > not want our customers to install another database like postgres > > or > > >> > MySQL > > >> > > > since it would involve extra overhead on their DBA team to > > maintain > > >> > (with > > >> > > > backup/restore functions) yet another database > > >> > > > > > >> > > > This has been already discussed and voted on but is there any > way > > we > > >> > can > > >> > > > keep experimental support if we pitch in to fix any mssql > related > > >> > issues? > > >> > > > > > >> > > > List of current mssql issues are here > > >> > > > * > > >> > > > > > >> > > > > > >> > > > > >> > > > >> > > > https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fairflow%2Fissues%3Fq%3Dis%253Aissue%2Blabel%253Abackend-mssql-experimintal%2Bis%253Aopen&data=05%7C01%7CJens.Scheffler%40de.bosch.com%7C6e16370a4474475d1b7908dbd554f6ea%7C0ae51e1907c84e4bbb6d648ee58410f4%7C0%7C0%7C638338333124218869%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=3SYq3%2BU0i%2BBilTmEayShi1uR2OnwaUlYvNHQE69kfaQ%3D&reserved=0 > > < > > > https://github.com/apache/airflow/issues?q=is%3Aissue+label%3Abackend-mssql-experimintal+is%3Aopen > > > > > >> > > > > > >> > > > Are there any other outstanding issues or can you please let us > > >> know a > > >> > > way > > >> > > > to identify mssql related bugs/problems that need to be > addressed? > > >> > > > > > >> > > > e.g. > > >> > > > * > > > https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fairflow%2Fdiscussions%2F35114&data=05%7C01%7CJens.Scheffler%40de.bosch.com%7C6e16370a4474475d1b7908dbd554f6ea%7C0ae51e1907c84e4bbb6d648ee58410f4%7C0%7C0%7C638338333124218869%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=NwOMkZexqX28RtVmwFoioRuuTTv2IPuHWFRrpTAJucA%3D&reserved=0 > > <https://github.com/apache/airflow/discussions/35114> > > >> > > > > > >> > > > > > >> > > > We are just trying to understand how much effort will be > required > > to > > >> > keep > > >> > > > supporting Mssql and if we can help with that in any way. > > >> > > > > > >> > > > Thanks > > >> > > > Ashwin > > >> > > > > > >> > > > > >> > > > >> > > > > > >
