2016 SQL Standard - Always on Availability Group questions

%3CLINGO-SUB%20id%3D%22lingo-sub-1885021%22%20slang%3D%22en-US%22%3E2016%20SQL%20Standard%20-%20Always%20on%20Availability%20Group%20questions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1885021%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20any%20assistance.%20I%20setup%20two%202019%20servers%20with%20SQL%202016%20Standard%2C%20with%20the%20hopes%20of%20setting%20them%20up%20with%20Always%20on%20Availability.%20I%20am%20at%20the%20point%20were%20the%20databases%20are%20synchronized%20between%20each%20server%2C%20but%20each%20time%20I%20perform%20a%20failover%20test%2C%20the%20Secondary%20data%20(which%20becomes%20primary)%20is%20unreadable.%20Event%20viewer%20logs%20display%20the%20following%20message%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22The_Boz_0-1605282820843.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233446i642C58AA457293EA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22The_Boz_0-1605282820843.png%22%20alt%3D%22The_Boz_0-1605282820843.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20recommendation%20is%20to%20ensure%20that%20the%20Replicas%20have%20%22Readable%20Secondary%22%20set%20to%20yes%2C%26nbsp%3B%20but%20that%20option%20is%20not%20available%20in%20SQL%202016%20Standard.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22The_Boz_2-1605282503386.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233445i16D55294A5E7AF23%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22The_Boz_2-1605282503386.png%22%20alt%3D%22The_Boz_2-1605282503386.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20I%20have%20also%20read%20forums%2C%20which%20indicates%20that%20I%20just%20need%20to%20%22Resume%20Data%20Movement%22%20after%20failover%2C%20but%20each%20time%20I%20check%2C%20the%20option%20is%20already%20enabled%20(Suspend%20is%20presented%20as%20an%20option).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20followed%20multiple%20guides%20when%20setting%20this%20environment%20up.%3C%2FP%3E%3CP%3EThey%20are%20two%202019%20servers%2C%20which%20are%20part%20of%20Windows%20Failover%20Cluster%3C%2FP%3E%3CP%3EThe%20SQL%20databases%2C%20do%20you%20an%20Admin%20service%20account%20between%20each%20other.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20other%20recommendations%20or%20suggestions%20would%20be%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

Thank you in advance for any assistance. I setup two 2019 servers with SQL 2016 Standard, with the hopes of setting them up with Always on Availability. I am at the point were the databases are synchronized between each server, but each time I perform a failover test, the Secondary data (which becomes primary) is unreadable. Event viewer logs display the following message:

The_Boz_0-1605282820843.png

 

 

 

1. recommendation is to ensure that the Replicas have "Readable Secondary" set to yes,  but that option is not available in SQL 2016 Standard.

The_Boz_2-1605282503386.png

 

2. I have also read forums, which indicates that I just need to "Resume Data Movement" after failover, but each time I check, the option is already enabled (Suspend is presented as an option).

 

I followed multiple guides when setting this environment up.

They are two 2019 servers, which are part of Windows Failover Cluster

The SQL databases, do you an Admin service account between each other.

 

Any other recommendations or suggestions would be greatly appreciated. 

 

Thank you,

 

0 Replies