When I fail an availability group between subnets, I am finding that the DNS entry in DNS is staying. So what happens is the Availablity Group listener has 2 records in DNS, one for each IP. This causes the App to timeout at times, since DNS will return either of the two IP's.
View 9 RepliesI am trying to build out an AlwaysOn AG with 2 nodes each in a different subnet (in AWS if that matters), windows 2012r2 / SQL 2014 RTM
I created a AG Listener with 2 ip address, 1 for each subnet (checked that neither ip address are used). But whenever i failover the AG to the secondary, and try and connect via the listener it fails,
I am trying to connect via SSMS from the primary instance. and just time out, If i roll over to the primary i can connect no issues, I've tried playing with the connection settings, upping the time out to 30 secs, adding the MultiSubnetFailover=true. etc but not getting any joy.
We have a SQL 2014 AlwaysOn availability group running on two Windows 2012 R2 servers that are in the same subnet. We created a new server in a second subnet, installed SQL, joined the server to the Windows cluster, added a new IP resource for the new cluster, and performed the other remaining steps to add a new AG replica to the SQL instance on this new server. When we try to move the core cluster resources to the new node to test failover, we get an error. Here's the command we've been using:
Move-ClusterGroup "Cluster Group" -Node node3
and it returns the error: The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group...I've checked the ownership of the cluster groups and the cluster resources and it looks like they are set appropriately:
>Get-ClusterGroup | Get-ClusterOwnerNode
Cluster Object Owner Nodes
---------------- ---------------
Available Storage {}
Cluster Group {node1,node2,node3}
SQLAG {node1,node2,node3}
[code]....
We've double-checked that all IP resources are in the right subnets and that the dependencies for the Cluster Name resource and the Listener Name resource are set appropriately. I'm not sure what else to check since the PowerShell commands seem to indicate that node3 is an owner of the appropriate resources. What other things need to be checked or if the ownership being checked isn't the same as what PowerShell is checking?
We are implementing a multi-site (Windows Server Failover Cluster) WSFC to enable Always On between our primary and DR site. We are not going to use SQL clustered instances. We are not planning to use shared disks. Each node is running a standalone instance of SQL 2012.
I have successfully configured a 3 node multi-site Windows failover cluster with no shared storage. For quorum, I have defined a File Share Witness (FSW). The FSW has voting rights and is in the DR site. The setup looks like this –
WSFC –
•Node A – Site #1 (voting right = 1)
•Node B – Site #1 (voting right = 1)
•Node C – Site #2 (voting right = 0)
•FSW – Site #2 (voting right = 1)
Again - There are no shared disks in our setup. We are not going to use SQL clustered instance. We are going to use Always On with these 3 nodes.
SQL Always On –
•Node A – Site #1 (Primary Replica)
•Node B – Site #1 (Readable Secondary)
•Node C – Site #2 (Readable Secondary)
All the setup including the “availability group” works properly under this setup. However, a failover to site #2 under DR situation is not working and I know why but don’t know what needs to be done to fix the problem.
The following works fine –
•Automatic failover between nodes A and B (same site – site #1)
•Forced failover to node C in site #2 provided at least one of the nodes in site #1 is up (non – DR situation) - this will ensure the cluster is up
The following is not working –
•Forced failover to node C in site #3 when both nodes in site #1 are lost (true DR situation) – This is because the cluster is not up at this point.
I know I have to bring the cluster up somehow and I have not been able to do so by restarting the cluster service.
I tried to run the command to start cluster service.
Question –
How can I FORCE the cluster to come up in Site #2 on node C when it has no voting rights?
I have always worked with even number of nodes and shared disks with traditional clustering. I am not sure what needs to be done in this scenario with 3 nodes and a FSW.
OS - Windows 2012 R2 Standard Edition.
DB - SQL 2012 Enterprise edition
Total 3 nodes participates for AO setup, 2 Nodes for Local HA and 1 Node for another datacenter for DR. All the 3 Nodes are same domain name and member.
1. Local First 2 Nodes are same subnet XXX.XX.44.XX
2. DR Node another subnet XXX.XX.128.XX
Does it require to add two different IP address while creating cluster name? Not using shared disk SAN storage etc.. I am using Node majority quorum witness setting for failover.
I have MasterDataServices installed on a server and the database is in an AlwaysOn Availability Group.The MDM front end currently is set up incorrectly and is referencing NodeA (primary node) for its database.I want to fix this but im unsure if i should:
-point the MDM front end to the cluster name, so it wont matter what server the databsae is on?
-create an Availability Group Listner, which would also decoupple the database referenced from a particular server.
I have getting issues when i am creating listener for always On . Error shown as below
Can not bring the Windows server fail over cluster (WSFC) resources online. (Error Code 5942). The WSFC service may not be running or may not be accessible in its currents states, or the WSFC resources may not be in a state that could accept the request.
For information about this error code see "system error code" in windows development documentation
The attempt to create network name and IP address for the listener is failed. The WSFC service may not be running or may not be accessible in its currents states or the value provide for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate network name and IP address with network administrator. (Microsoft SQL Server error 41066) ...
I am trying to setup an AG on a cluster, I was able to define where I want my replica, but I need to setup the ip for the listener of that one
When I am adding the Ip at the listener I am getting the following message:
The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057. The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. For information about this error code, see "System Error Codes" in the Windows Development documentation.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)
Browsing around I found this guide: [URL] ......
And I make sure that the cluster has permissions to "Read all properties", "Create Computer Objects", even to "Create all child" permissions
I also added those permissions to the account that starts sql at that cluster and I am logged as that and I am still getting the error....
We are running SQL Server 2012 Enterprise on Windows 2012 R2. We have set up A WSFC with the primary Node in our East Coast Data Center and the Secondary Node in our West Coast Data Center each node is in a dfferent subnet. Since we have high latency between the sites we run in Asynchronous mode with manual failover only. My quesion concerns Quorum, everything I read would indicate we need a third node (odd number) (e.g. a fileshare Witness) with only the file share and the primary node having a vote...What I don't see is any need for a file share if we can only do manual failover.
View 7 Replies View RelatedHow to find when (date/time) a database was last removed from AlwaysOn Listener?
Is there any query (DMV) to find the details easily?
We are rolling out the use of Availability Group listeners to our SQL Server 2012 Environment which has a 2 node multi-subnet cluster. The Primary is R/W and the Secondary is a non-readable node that would be manually failed over to in a DR scenario
I have set up the AGL and asked the sysadmins to create a DNS record in both subnets with fixed IP's.
The issue I have having is that when I ask the app developers to connect to the databases using the AGL it is totally random whether the AGL resolves to the Primary or DR node - as a result that are having problems getting their apps to connect.
I was thinking of asking the sys admins to remove the DNS record in the DR subnet and then add it back in should we need to fail over - but I was thinking there must be a better way.
I am setting up SQL 2014 always on. I was able to set up the replicas between 2 servers in the same subnet.Their IP addresses are say like this:
100.20.200.200
100.20.200.201
When I am trying to introduce another node into the cluster which has IP address like 100.10.101.102, I am getting an error that the server isn't reachable.
Is it possible to ONLY allow a login to the availability group listener, but block logins to server instance/nodes?
So: MySQLServerA and MySQLServerB are in an avail group "MyAvailGroup".
I want users to login to MyAvailGroup's listener, but I do NOT want them to login to the actual hosts/nodes directly.
Is that possible?
I have setup two node SQL 2012 Always ON cluster with file share witness. I have configured two networks, one for heartbeat and second for public.My question is do I need a third network/ subnet for AG Listener? Which network does the SQL user databases in the AG group use to replicate between the replica servers?When I do the SQL backups, do I need to backup primary and secondary replicas or just backup the databases in the secondary replica?
View 4 Replies View RelatedMore often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
How many nodes can you have in a cluster with SQL 2012 alwaysOn.
I understand that availability groups are limited to 5 nodes but if you had a 10 node cluster and decided to create multiple availability groups using various nodes within the 10 nodes but never exceeding 5, is that possible?
Or is there a counter or some validation from SQL AlwaysOn that actually hard limits to a grand total of 5 nodes in a cluster?
Can we make SQL 2012 Always ON over the normal SQL Cluster 2012.
View 3 Replies View RelatedWe have an existing SQL Server 2012 Enterprise cluster with 2 nodes (active-active) and uses Windows 2008 R2 OS. We are looking for a way to increase HA as well as offload backups to secondary server and it was suggested that AlwayOn could be an option.
The questions I have are:
1) Is it possible to turn on AalwaysOn feature on an existing cluster?
2) If yes to above, does the secondary replica need to exist as a node on the same cluster or can it be on a completely different cluster?
3) If the secondary replica is on the same cluster (i.e. we add a 3rd node to existing 2 node cluster), can that node be provisioned with storage from a completely different SAN? (i.e. Node 1 and Node 2 accesses LUNs on SAN1 and Node 3 accesses LUNs on SAN2).
How can we change the backup replica in Always on Cluster. I am not able to take backup because of this.
View 7 Replies View RelatedHow can we find the cluster failover count in always on ?
As my AG is configured as synchronous mode , AG went offline and we manually restarted the AG service when we check the properties on AG role they r in default setting ?
I know now that AlwaysOn feature HAS to be installed/configured on a Windows Clustering environment, BUT the secondary replicas, like the Disaster recovery replica residing in a different Data Center HAS to be also in a Windows Clustering environment or can it reside on a SINGLE SQL Server INSTANCE?.
View 6 Replies View RelatedI have a two node HA Always on group using a Listener. I would like to force a certain AD group to always be forced to the secondry node as they would only ever need to run select statements. If there an easy way to do this without using logon triggers?
View 6 Replies View RelatedI ran into a Kerberos authentication issue because of a missing AOAG SPN. Some of the tickets that granted me access to the nodes of the AOAG cluster were using the encryption type that I would expect. However, the MSSQLSvc SPNs were not using what I would expect!
klist
#XX> Client Somebody@somedomain.com
Server: RPCSS/MySQLServer@somedomain.com
KerbTicket Encryption Type: AES-256-CTS-HMAC-SHA1-96
#XX> Client Somebody@somedomain.com
Server: MSSQLSvc/MySQLServer@somedomain.com
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
#XX> Client Somebody@somedomain.com
Server: MSSQLSvc/MyAOAGListener@somedomain.com
KerbTicket Encryption Type: RSADSI RC4-HMAC(NT)
I can't seem to figure out what the next step should be, and the infrastructure admins are stumped as well. How to proceed?
My environment has a 4 node cluster , 2 in primary and 2 in sec dc. Storage is sperate for both.
Need to setup always on for 4 Instances there on the 2 nodes of the primary dc. Is there any restriction in setting up always on for multiple instances for a cluster.
I have had a serious issue with a production AlwaysOn cluster whereby the service did not successfully transition to the secondary node and I cannot find the root cause of the issue.
Some details: It is a 2 node cluster (same datacenter) with a shared disk quorum, Windows Server 2012, both are virtual machines running on VMWare vSphere 5.5. SQL Server version is 2012 Enterprise SP2 CU6
The failover occurred because of a network incident (a spanning tree recalculation caused a connection timeout between both nodes). Initial entries in the SQL Log look normal for this event, for example:
05/08/2015 11:18:06: A connection timeout has occurred on a previously established connection to availability replica 'FIN-IE-PA078' with id [6910F4A9-87E7-4836-BA79-0F41BE90266D]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
05/08/2015 11:18:06: AlwaysOn Availability Groups connection with secondary database terminated for primary database 'UserManagement' on the availability replica with Replica ID: {6910f4a9-87e7-4836-ba79-0f41be90266d}. This is an informational message only. No user action is required.
[code]....
My interpretation of this is that the cluster failover attempts failed, because the network condition still persisted. The network interruption lasted approximately 2 minutes, and I would have expected the cluster to come back online at this point, after the restart delay period as suggested in the last entry in the error log. However this did not happen.
we have to build high availability SQL 2012 cluster for VDI and we have two options. One option is to build a server cluster with combination of failover and mirroring and other option is to build failover cluster with AlwaysOn.We are not sure which option to chose. We have contacted Microsoft support to provide us some documents and instructions for failovermirroring combination but they have send us instructions for AlwaysOn option.
What would be best way to build high availability cluster for VDI? Also, since first option is very complicated.
To set up AlwaysOn Feature on cluster environment how many VM's we would require.
We have already have 2 VM's for cluster but am not able to configure AlwaysOn.
How can one enabledisable Availability Group listener? I know AG listener can be deleted and added but didnt find anything to disable or enable that.
View 1 Replies View RelatedI got 5 SQL Servers 2008R2 (2x CPUs with 96-384Gb of RAM) with total 1500DBs and around 10Tb of allocated data. Now
I am forced to upgrade to 2012 and 2014, but the old hardware is overkill.
Is it possible to consolidate this to a large SQL AlwaysOn cluster?
We pros/cons could it be to have 2 or 4 nodes?
2 or 4 sockets servers?
My goal was to use ~20 cores Enterprise license on active server.
We had a big issue today during maintenance work in our SQL environment.
So our environment:
- 2x SQL Server 2014 Enterprise on Windows Server 2012 R2 (SRV1 and SRV2)
-- Both Hyper-V VMs on different Hosts
-- Both configured to an Windows Failover Cluster and AlwaysOn Availability Group (AG1)
-- AG Listener: AG1_lis
-- No shared storage (each Hyper-V Host has its own local storage)
-- Asynchronous Mode
-- SRV1 is primary, SRV2 is secondary SQL node
What happened?
- Shutting down Windows on SRV2 due hardware maintenance
- Cluster goes offline, AG1 goes offline
-- Error message: "Stopped listening on virtual network name 'AG1_lis'."
-- Error message: "The availability group database "DatabaseXY" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization."
Results?
- AG1_lis wasn't available for our applications and they stopped working properly because database connection was lost!
I think, I HOPE, this is not the normale behaviour when one node is shutting down (especially the secondary node!)
I have a 3-nodes AlwaysOn cluster (Windows Server 2008 R2 SP1 + SQL Server 2012 RTM), Node Majority quorum, the quorum vote for each node is 1.
Today the AlwaysOn AG was suddenly down due to the cluster service on node 1 stopped and can't be started.
The error in eventlog is -
The cluster database could not be loaded. The file may be missing or corrupt. Automatic repair might be attempted.
The Cluster Service service terminated unexpectedly. It has done this 2 time(s). The following corrective action will be taken in 120000 milliseconds: Restart the service.
The failover cluster database could not be unloaded. If restarting the cluster service does not fix the problem, please restart the machine.
The Cluster Service service terminated with service-specific error The system cannot find the file specified..
The error log in cluster log is -
0000156c.000008f8::2012/09/05-08:09:36.057 INFO [DM] Key RegistryMachineCluster.restored does not appear to be loaded (status STATUS_OBJECT_NAME_NOT_FOUND(c0000034))
0000156c.000008f8::2012/09/05-08:09:36.057 WARN [DM] Node 1: Failed to unload restored hive from the registry with error STATUS_INVALID_PARAMETER(c000000d)
0000156c.000008f8::2012/09/05-08:09:36.057 INFO [DM] Node 1: loading local hive
0000156c.000008f8::2012/09/05-08:09:36.057 ERR [DM] Node 1: failed to unload cluster hive, error 2.
Now the cluster service can't be started on node 1, error code 2. Looks like the clusdb in C:windowscluster is missing or corrupted. How to restore the clusdb file? And how to prevent this happen again?
All nodes were well patched, AlwaysOn and cluster related hotfixes were all installed. [URL] .... doesn't wok.
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
I have an issue when connecting with application Intent=readonly when using Listener name. I can connect to the read only secondary with application Intent=readonly if I use the server name. It gives a host does not exist error when using listener. This happens only if I am using the application Intent keyword. I can connect for normal readwrite operation using listener name.
View 3 Replies View Related