July 26, 2022 at 8:33 pm
I will be running the application upgrade process which will run for couple of hours and behind the seen it will make couple of table changes such as create the new table with the field changes and then insert data into the new table from old table and rename it back to same as old table name etc. So since my environment is always on AG with couple of secondary replicas. I know I will have to test this process, but need some thoughts? If the application hits the bigger tables that being modified will throw errors I guess. So if we redirect the application to the secondary replica would that help or I would need to remove the replica from availability group as best practices?
July 27, 2022 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 28, 2022 at 6:25 pm
You should do nothing except run your upgrade process.
Do not redirect clients, do not remove replicas, do not remove databases.
You should have agreed downtime in order to complete this work.
You start removing pieces you need to start rebuilding the AG pieces and reseeding databases which will just take the maintenance window to a larger outage.
July 28, 2022 at 8:06 pm
Thanks. Since the application cannot be down. We have 2 secondary replica, 1 for DR and another for reporting with Async mode. So I am thinking to remove the reporting secondary from AG and bring it available for the application so it will do read only since the application doesn't write anything to the database. Once the upgrade is done I will sync and add the replica back to AG. Do you see any issues?
July 29, 2022 at 6:30 am
You need to have some downtime, breaking the AG, reconfiguration of the application to point to the removed replica all require some downtime.
If the application doesn’t write, ensure you have the correct read only routing enabled and that the application does indeed use the Application Intent option in the connection string. So it will auto redirect the reads to a secondary.
Then you could suspend data movement on the ASYNC while the upgrade works and then resume later to make it catch up.
But seriously you need downtime.
July 29, 2022 at 4:10 pm
I like the idea of suspend data movement. Would this cause the log to grow until the upgrade runs? My understanding is the backup jobs will be still running on the primary? I forgot to mention that I have another secondary which is in Sync mode with automatic failover. So when I configure read only routing I would still need to configure for the ASYNC replica not for the SYNC replica right?
July 29, 2022 at 6:20 pm
Yes the log on the primary will continue to grow as it hasn’t sent the data to the ASYNC replica so you need to make sure you have enough log space for that.
July 29, 2022 at 9:09 pm
Read only routing will not work if the data movement suspended.
August 1, 2022 at 8:08 am
Can I suggest you also look at your application upgrade process. The standard I have been used to is to minimise risk.
There should be no DB changes made that could stop the current version of the application running. (This includes the table name change you propose to do, that should be banned)
DB changes are typically harder or more disruptive to back out than reverting to an old version of application code, so plan to avoid any need for a DB backout. All changes you need should be planned to avoid any downtime.
Any DB changes needed for the new version of the application are done and verified in advance of rolling out the new application code. New columns can be added while the application is in use. Populating the new data should be planned to avoid workload peaks.
When the DB has been upgraded and data verified, the application code can be upgraded. If the new application code does have a problem, the old code can be reinstated and will continue to work.
I have worked in a Devops environment where new stuff got rolled out twice each day, so it all had to be done without downtime. One main lesson learned was that avoiding the need for downtime also avoided points of buisness risk.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 2, 2022 at 9:20 pm
I have checked and found that this application does not have that flexibility when there is DB changes that could stop the current version of application running. Any thoughts?
August 3, 2022 at 9:38 am
It may be time to experiment with a non-production implementation of your application. Make a copy of your test DB and point the application at that. Then apply all the table updates you need to the copy and see if anything breaks in the app.
The only likely problem is if the app is doing a SELECT * and would threfore get more data back than it is expecting. You don't need me to tell you how dangerous SELECT * is in an app, and any occurrence of this should be fixed using the current DB version.
Also, you have not said if your app is in-house or a package. If it is a package then other people might have some insights in how to do the upgrade. Who knows, maybe the vendor may also have some clues. If it is in-house then you have full control over the process, so you should be able to make it work.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 4, 2022 at 2:52 am
Does data suspend in Always on AG will cause tempdb to grow as well or only Log growth?
August 5, 2022 at 5:42 pm
I will be testing this.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply