March 19, 2018 at 2:03 pm
I have always-on availability group on two nodes cluster with read intent routing. When I stop availability group role in WSFC at that point; I am not able to insert in any database part of thavailability group.
March 20, 2018 at 7:10 am
Depends through which route you are trying to access to the databases, when you disable the WSFC functionality you can't use the listener that you created in the DNS server.
you'll have to connect to the databases through the server\instance name itself, the AG stops working when you disable the WSFC.
If you want to add a database in the AG you'll just need to enable the WSFC on the server that you want to add the database from and add it through the primary replica and put a backup of that database in a shared file server where the primary replica can read from.
if i'm not mistaken with the SQL Server 2016 and Windows Server 2016 you don't need to be in the same domain (please correct me if i'm wrong)..
March 20, 2018 at 8:42 am
mnadirwk - Monday, March 19, 2018 2:03 PMI have always-on availability group on two nodes cluster with read intent routing. When I stop availability group role in WSFC at that point; I am not able to insert in any database part of thavailability group.
Stopping the role will as already highlighted, take the listener offline, without the listener the read only routing config is unusable.
Why are you taking the role offline in the first place?
What are the databases set to, synch or asynch?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 20, 2018 at 9:30 am
Database set to sync mode. I understand listener is stand on WSFC role but the problem is that i can not read or write in e.g Server A (primary) and I can only read Server B (Secondary) if i down the role and connect directly on Node(Host). why i am do it? to find single point of failure. At least one Node should be accommodate read-write connection.
March 20, 2018 at 1:17 pm
mnadirwk - Tuesday, March 20, 2018 9:30 AMDatabase set to sync mode. I understand listener is stand on WSFC role but the problem is that i can not read or write in e.g Server A (primary) and I can only read Server B (Secondary) if i down the role and connect directly on Node(Host). why i am do it? to find single point of failure. At least one Node should be accommodate read-write connection.
What you are trying to do is connect to the Node A but the failover is on the Node B, all you have to do is make a failover from the Node B and make the Node A the primary replica, all the changes made to the Node B not made in the Node A will synchronize on the process of failover.
Please correct me if i'm wrong.
March 20, 2018 at 2:29 pm
Dear; I create perfect alwayson solution as per my perspective on two node cluster. The failover works great, the read intent connection routing works great, I already check with the profiler. The question comes from my senior is that If WSFC role down are you able to re-direct app on any read-writeable database without crashing role and listener? both instances give me following error if the role is down
Msg 983, Level 14, State 1, Line 1
Unable to access availability database 'DATABASE_NAME' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.
because both databases status changes into (Not Synchronizing) after role down. it is clear listener will not work because of cluster resource down but why databases individual databases also not accessible.
It is the single point failure, isn't it?
is there any remedy for that.
March 21, 2018 at 4:40 am
If the WSFC role is down then this AG is down, if the cluster goes down (did you configured any witness) all AG on that cluster goes down. I don't think that it is a single point of failure, it is logical component and you have redundaPost replyncy (two node that can host this role).
March 22, 2018 at 6:33 am
Have you set up a witness share? For a windows cluster you need an odd number of nodes so you can get node majority. So two servers in a windows cluster additional need a file share too, Azure has a cluster share you can use too.
I like to explain it by thinking windows has a cluster service at the OS level and it’s purpose is to work out what services can run cluster aware applications. Sql server sits on top of this service and runs the databases hence sql server controls what is the primary and secondary databases at any point not the windows cluster. The listener is a names resolution thing so your clients connect to the right database server.
High Availbility is different to database mirroring which is what your senior maybe getting confused with. HA is better when done right 🙂
March 22, 2018 at 10:19 am
mnadirwk - Tuesday, March 20, 2018 9:30 AMDatabase set to sync mode. I understand listener is stand on WSFC role but the problem is that i can not read or write in e.g Server A (primary) and I can only read Server B (Secondary) if i down the role and connect directly on Node(Host). why i am do it? to find single point of failure. At least one Node should be accommodate read-write connection.
If the replicas are set to synch mode and the primary is lost ( which it is when you offline the cluster role ) then the secondary(s) enter the resolving state and you would need to manually force service.
Do not stop the group via Failover Cluster Manager, the group should be managed via TSQL or Powershell cmdlets
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply