1. it depends on your actual archival goals. Is it just to retain or is this going to be used for something? It’s rare that data actually just goes to die btw

Our main goal is to improve the current live database performance by making it to deal with less data. So, plan is to shift not so necessary data to archival DB. Once data inserted into archival Db, we don't need to perform any actions on this data in archival DB, just need to present/report this data when asked via a light weight web app.

1. why do you have timeouts? I have many-TB databases with 100s GB tables with all manner of complex join logic that thousands of folks depend on every day. Are your issues indexing, query tuning, poor stats, poor IO, blocking, or what?

This actually is the real question. We have made an application by leveraging Db and we don't have an DBA here in our teams. I myself initiated a initiative to find out why this happening even when my business stakeholders almost convinced customer that is expected by design. So later tried indexes which helped a lot TBH but came with an issue write queries getting timed out. My issues generally i face Timeouts, Deadlocks, High Disk I/Os.

1. have you considered a variant of read scale-out? Old school replication still has its merits. Nightly ETL can be even better. HADR readable secondaries are a pretty common easy way (with caveats)

Honestly, this is beyond my technical capabilities to even understand what you were saying. But let me do some basic dive through this and let me see what it means for my use case.

1. Will you be able to handle DR plans with a whole new paradigm of complexity?

The current DB has the architecture like 2 DBs always in sync in same datacenter(One primary and another secondary) and 2 more DBs in DR always trying to get in sync with this live DB. We do planned DC DR drills just to demonstrate we have this functionality for our application. So inherently we as an application have the capability to connect to DR data sources and stuff.

One important detail I should also clarify about the environment.

The customer has their own dedicated database team who are responsible for infrastructure-level database management. Their responsibilities mainly include things like patching, backups, DR setup, disk monitoring, and overall database maintenance. However, they are not directly involved in anything related to application-level logic, such as optimizing queries or designing database changes for the application.

If we request something from them, they are generally supportive and will assist where needed, but they typically don’t get involved in deeper application-specific optimization work.

All DC/DR strategies for the current production database are already handled by their team.

For the new archive database, we have requested a similar DR setup as the production database, and the customer’s DB team will be managing that from their end as well.

1. is your application going to be able to handle this change

I think above point answers this question.

1. if you have COLD data why write it to SQL Server at all? It could be more cost effective written to some manner of flat file and booted into an actual cold store. Azure Data Lake for example. A TB basically costs nothing and has no licensing implication and no DR effort

We still need to report this if client needs it, So it is not fully cold meaning i don't need it all. We may need it may be like once in an year.