I am using SQL Server 2016 Standard Edition.
I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02. Users should be able to read from database in both server at any time and transactional replication allows that.
I am thinking of a failover strategy to failover to SQL02 and then back to SQL01 if there is such a situation (patches/maintenance).
I know that transactional replication is not a
HA solution, but since I am using SQL Server 2016 Standard edition I have Log shipping/Database mirroring options only.
Current failover strategy for a patch/maintenance in SQL01:
For me this looks like a solution I can use. Possible issues I see are:
Do you see any issues other than these?
Please add your suggestion(s).