Is it alright to move the .bak and .trn backups which are automatically created in a File Share Witness when a DB is added to an Availability Group? I did that and there were error IDs 1069 occurring, and the AG was unable to be brought up whenever I run a load intensive batch job ...
View 5 RepliesCame across this scenario in AlwaysOn Availability Group (two node), file share witness times out and RHS terminate and cause the cluster node to reboot. File share witness is for continuous failover and if the resource is unavailable my expectation was that it should go offline and should not impact Server or Sql Server. But its rebooting the cluster node to rectify the issue.
Configuration
Windows Server 2012 R2 (VMs)
Sql Server 2012 SP2
Errors
A component on the server did not respond in a timely fashion. This caused the cluster resource 'File Share Witness' (resource type 'File Share Witness', DLL 'clusres2.dll') to exceed its time-out threshold. As part of cluster health detection, recovery actions will be taken. The cluster will try to automatically recover by terminating and restarting the Resource Hosting Subsystem (RHS) process that is running this resource. Verify that the underlying infrastructure (such as storage, networking, or services) that are associated with the resource are functioning correctly.
The cluster Resource Hosting Subsystem (RHS) process was terminated and will be restarted. This is typically associated with cluster health detection and recovery of a resource. Refer to the System event log to determine which resource and resource DLL is causing the issue.
I've set up a SQL server 2014 cluster with AlwaysOn availability groups. Upon creating the AG i opted for full syncronisation to a specific SMB share.Now i want to change that share because it has to move to a new server. How can i do that? I found no settings in the SSMS for that.
View 3 Replies View RelatedI'm trying to move a log file of a database that is part of an availability group. I have been following steps from the article: [URL]
At first this worked fine for me in a test environment. When I tried it in a production environment the database on the secondary went into "Recovery Pending" state and I can't get it out.
I checked to ensure that the dB is looking in the right place for the log file, and it is. It just doesn't seem to actually use the new file. If I start and stop SQL service, the dB comes back up and is fine.
Here are the steps I'm going through and what is happening at each step:
--------------------------------------
:Connect DEVSQL --This is currently PRIMARY
USE[master]
GO
ALTER AVAILABILITY GROUP [DP-AG-DEV] MODIFY REPLICA ON N'DEVSQL' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))
[Code] ....
All is good so far. Both the Primary and the Secondard have had their logical files changed, which has not taken affect yet because there has been no failover.
--Make SQL10 the PRIMARY
:Connect SQL10
ALTER AVAILABILITY GROUP [DP-AG-DEV] FAILOVER;
GO
SQL10 is now the Primary for this AG. And, as expected, the database [AG-Test] is in "Recovery Pending" because it is now looking for the log file in the new location. I need to move the file to the new location.
:Connect DEVSQL
--Enable XP_CMDSHELL
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
[code].....
This is where the script is failing, returning the error:
Msg 1468, Level 16, State 5, Line 5
The operation cannot be performed on database "AG-Test" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
I can not get the dB to recognize the log file at it's new location.
If I restart the SQL Service, it comes back fine, which seems to indicate to me that it is not a permission problem and confirms that the file is in the right place.
How do I force SQL to look for the log file again without restarting the service?
I was looking to change the file growth setting in our AlwaysOn environment databases.We have a single availability group, one primary and one secondary replica. I learned that when changing the file growth setting on the primary databases (data file), the change flows though to the database on the secondary replica.However after doing the same with the log files, the file growth setting changed on the primary but the change did NOT propagate to the secondary.
Is the solution to apply the change directly to the secondary?here's the T-SQL code I used:
ALTER DATABASE myDB
MODIFY FILE ( NAME = N'myDB_log', FILEGROWTH = 512MB );
GO
SQL Server 2012 (11.0.5532)
I want to truncate my sharepoint config database and WSS_Logging database logs the size of sharepoint_config database is growing at a pace of ~10GB every week. I have scheduled a weekly full backup. Current .ldf file size is 113GB.
I am using SQL server 2012 with Always On High Availability feature. I am not able to set the recovery mode from Full to Simple as it gives me message that mirroring is running on both server.
In my case to reduce the log file what I need to do.
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies View RelatedWe have 2 data centers, site 1 and site 2. Site 1 is generally our primary, and site 2 is our Disaster Recovery (DR) site. I want to setup a SQL instance to have extremely high availability. Therefore I was looking at using DB mirroring, with synchronous data writing, high-safety and auto-failover. This requires the usage of a witness server.My problem with this setup, is that if the witness and principal are both at site 1, if site 1 goes away (power failure, asteroid impact, lol, anything else that would be a *true* DR scenario), then there is no failover to the mirror server at site 2. However, if you put the witness at site 2, anytime the WAN link between site 1 and site 2 has an issue, the DB will auto-failover to site 2. Is the reason for the recommendation of the witness being at the primary site because the thinking is that WAN link failure is likely more common than a *true* DR scenario that takes out all of site 1?
View 3 Replies View RelatedWe have just implemented a SQL 2012 always on environment. We have a primary and secondary server. I am confused about how to set up the backup plans. The application team was happy to tell me that in sql 2012 always on we can offload the backups to the secondary, thus reducing overhead on the primary server.
However, the secondary only supports copy only full backups. I am unsure how these would be useful in a disaster event? I could not apply any trx log backups on a copy only backup. This means I need to run my full backups on the primary server?
Can we set up always on availability groups in server 2012 standard edition.
View 3 Replies View RelatedWe have a client which they have production 2 node cluster environment. On it around 200 databases with single SQL instance.
Now client wants disatster plan for these 200 database. In these 200 database 3 db's are around 80 GB each databases remaing are less than 5 DB.Note: All these 200 db's are having produciton sites (i mean to say each db is having single site)
For this DR paln clinet is going to provide other DR server,they wants to setup DR between exsting produciton cluster instance to this DR server.
So in this case we have suggest SQL server AlwaysOn availability group.
Here my main question is can we keep all these databases in single AG? .If yes, guidlines to move up. if not, do we have any limitations.Also, best method to setup for this DR plan.
Merge replication on AlwayOn is configured, working fine on Original Publisher.When failover to possible publisher data is not being replicated.
Replication Monitor Error:
Message: Validation failed for the publisher 'RIMDNS' with error 21879 severity 16 message 'Unable to query the redirected server 'RIMDNS' for original publisher 'UNITEDKINGDOM' and publisher database 'TD_AO11' to determine the name of the remote server; Error
2, Error message 'Error 2, Level 16, State 1, Message: Named Pipes Provider: Could not open a connection to SQL Server [2]. '. '.
[Code] ....
How many maximum database's can we have in AlwaysON Availability Group?
View 3 Replies View RelatedThis is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.
View 1 Replies View RelatedWe have an AG scenario where we are using WFC on a 2 node cluster. We are then using AG for mirroring the databases to both nodes and have a listener.
What I want to do next is to establish another copy of the database at a remote location. But I don't want to add the 3rd system to the WFC. I am not a big fan of WFC and I have seen it causes many more problems. The 3rd system will be in a remote location and the network not 100% reliable. I have seen in the past that it causes the entire cluster to hang and causing my production to crash which I don't want.
I there a way to add a 3rd node to the mirror configuration. I don't know if I can add a 3rd node to the AG unless it is part of the same cluster.
I know I can configure log shipping, I am fine with it but in the source, I have no control of which node the DB will be. I am not sure if a log shipping scenario can be configured using the listener instead of the physical host.
How do I add my second (secondary) node in my AlwaysOn Availability Group, after adding my head node, and the secondary node is a virtual machine. See based on the attached file if it is the correct way?
View 2 Replies View RelatedWe've just started using AlwaysOn High Availability and run into a wierd issue. I have 1 particular database that is not syncing data to the secondary replica. But when i look at all the dashboards, everything is green, they all say synchronised - but if I query the data they are not.
The database has additional sql transactional 1 way replication a different server which i was wondering if was causing any problems, but it's working ok.
I'm just wondering if there's any other more detailed logs i can check to see why the data is not flowing. The availability dashboard and it's event log all says ok.
In the case of a manual failover, what happens to open transactions. Are they killed (rolled back), completed, a little bit of both?
Does the status of the query has any impact (are running queries handled differently then waiting ones, does the type of wait have an impact...)
I am using synchronous commit mode and all I seem to find is reference to the potential or absense of loss of data.
How to test always on availablity after configuring them.I have configured always on group with 1 active and 1 passive with readonly.I want to test from application.what are cases which we can have for testing.
View 5 Replies View RelatedMy 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 know that AlwaysOn creates a WSFC role in order to provide failover of the Availability Group Listener. But does it also "honor" the WSFC network setttings? What I'd like to do is isolate the client-side traffic from the database replication traffic. But it's not clear to me that AlwaysOn even uses that part of WSFC.
a) Can you totally isolate the traffic as I've described?
b) Does AlwaysON actually use WSFC network settings? If yes, then I guess I cannot do what I want, since my choices are Cluster & Client, or Cluster Only.
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 a SQLServer 2012 Always-On Availability (AAG) Primary and Secondary Node installation/environments. On the Primary node, we have some databases that have the TRUSTWORTHY option enabled (Set to ON). But when the databases are synched/added to the AAG the databases loose the TRUSTWORTHY property and are reset to OFF on the Secondary Node.Because of this,When the instance fails over to the Secondary Node the applications that were working don’t work anymore.
View 5 Replies View Relatedwe 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.
I have configured AlwaysOn HA setup with HyperV environments without shared disk and using quorum voting in file share witness.
1. I want to monitor AlwaysOn HA setup and AO Group database on daily basis.
2. To configure email alerts for proactive monitoring if unusual events occur.
Scripts for monitoring in that AO setup as well as AO group database ...
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.
I have a 2012 AlwaysOn DB Mirroring environment set up with two nodes. Both have 5 installs of SQL named instances.
The issue we are having is when we patch one server and fail everything over, some of the applications will error. Some of the applications had to have their web.config files updated with hostinstance name because it seems to not work with DNS.
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 noticed that after a SQL AlwaysOn failover, one of the DB in the secondary replica is stuck in Restoring state. The primary replica shows that it is in a synchronized state. These are the error logs from SSMS. How do I trace the cause of the problem?
Error: 5901, Severity: 16, State: 1.
Nonqualified transactions are being rolled back in database for an AlwaysOn Availability Groups state change. Estimated rollback completion: 0%. This is an informational message only. No user action is required
Error: 18400, Severity: 16, State: 1.
One or more recovery units belonging to database failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
The background checkpoint thread has encountered an unrecoverable error. The checkpoint process is terminating so that the thread can clean up its resources. This is an informational message only. No user action is required.
I recently configured SQL Server 2012 AlwaysOn Availability group using two nodes - a primary and one secondary read only replica. The group is residing on a windows 2012 cluster with an smb file share as the quorum. I am able to successfully failover through SQL and through the windows 2012 cluster. When I look at the group dashboard on the primary server and view the Operational state of each node I notice an odd value. The secondary role server is listed as Unknown. I also noticed that the Availability replicas node icons in object explorer are displaying the same icon on the primary server but on the secondary server, the primary server is shown as a server with a question mark.
Am I missing a permissions setting or is this normal behavior.
For example:
ServerA is the primary
ServerB is the secondary
ServerA lists the servers in Object Explorer as:
ServerA (Primary)ServerB (Secondary)
ServerB lists the servers in Object Explorer as:
ServerA ServerB (Secondary)
The primary is never listed a primary on the secondary server. Again failovers are working properly, but I want to be sure I am not missing a setting somewhere.
I'm doing a certification process using AlwaysOn, and was using the link below, and on the lower 90 hotfix, and instead of downloading one by one, and then upgrade one by one updates, is there any way to make it more faster or practical, or unfortunately have to do this one by one, so the download as the update? This rollup contains the latest version of the Windows system files that are updated after the release of SP1. URL...
View 2 Replies View RelatedI am new to always on management, I would like to know if exist a best practice for configuring maintenance plan on a alwayson system (two nodes) or is just running the maintenance plan wizard on both nodes.
View 8 Replies View RelatedI believe to configure maximum number of replicas it is required to have 5 nodes (1 Primary & 4 Secondary replicas). But how many databases can be included in one availability group, it think it is 32767. When I am referring to below URL it is mentioned something like "An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. " What does this 'sets' means, is that maximum limit for only eight databases or maximum groups should be eight?
[URL] ....