Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [availability-groups]

Availability Groups are a new feature of SQL Server 2012 that provide continuous data synchronization, automatic failover and secondary read access for one or many SQL Server databases.

-1
votes
0answers
38 views

Create Availability Group in SQL Server 2017 [on hold]

I have a setup of Windows Server Failover Cluster with 3 servers: one Primary, one Secondary, & one Domain Controller, & I have installed SQL Server 2017 and Microsoft Server Management Studio ...
0
votes
1answer
13 views

SQL SERVER Checking for Compatibility of the Database File Locations on the Server Instance that Hosts Secondary Replica

I am using SQL Server 2017 and SSMS 18.0 Version. During creation of availability group at validation step at primary Server (DBN1), i am facing that error i.e. the following required directories do ...
0
votes
1answer
46 views

Drop the user from Read only Secondary database Always ON server

How to drop a user from the secondary database in an AG when it is in read only mode and synchronizing in SQL Server 2012? The login and user have been deleted from the primary database, and we are ...
5
votes
0answers
80 views

Alwayson AG lease timeout and healthcheck timeout

We have a physical SQL Server 2016 Enterprise SP2 with two instances and each instance contains one AG. Sometimes it happens that one AG goes to resolving (without failover), the cluster resource is ...
0
votes
1answer
20 views

AlwaysOn availability group and Replication - expected behaviour

I'm currently doing a bit of testing with alwayson and replication. I currently have the below: Fully working cluster and Always on environment; DCTEST1, DCTEST2 and the listener name DCTEST_L. The ...
2
votes
1answer
82 views

An error occurred on the always on dashboard

I have a setup of Windows Server Failover Cluster and i am using SQL Server Service with one DC, One Primary Server and One Secondary Server. During Using SQL Server Management Studio, when i click ...
2
votes
0answers
17 views

Multiple Location AlwaysOn and Azure SQL Server [on hold]

AS with the majority of projects, the original scope may not be a viable option. We were using Azure Data Sync and transaction replication to keep the 4 site databases in sync. However, the customer ...
1
vote
0answers
34 views

Current Host Server for Availability Groups [duplicate]

We have a 4 Node multi Subnet Availability Group. When i go to the Cluster Manager I could see the Current host server is the Node 4 whereas the AG Primary node is Node 1. Will the AG go down if Node ...
2
votes
1answer
27 views

Patching FileShareWitness for AlwaysOn AG

Situation: We have an AlwaysOn High Availability group with 4 replicas (2 for DR) a listener and an FileShareWitness. I've been tasked with creating a set of instructions for patching all of the ...
2
votes
0answers
35 views

Connection to availability group listener on non-default port doesn't require port to be specified

I inherited some 2016 SQL Servers with an availability group and listener. The SQL instances are set up to use port 30001, and the AG listener is also set up to use port 30001. When I connect using ...
0
votes
1answer
20 views

How to monitor a file share witness for accessibility

In the context of a SQL Server Availability Group, what's the best way to monitor a file share witness for accessibility? I want to make sure the witness can be accessed by the Availability Group ...
0
votes
0answers
25 views

Installing Anti Virus on availability group

Is it worth the risk to use Anti Virus on Availability group databases. Even with following the guidelines given at https://support.microsoft.com/en-us/help/309422/choosing-antivirus-software-for-...
5
votes
1answer
166 views

How do I find out when a node was added to an availability group?

Ideally, I am looking for T-SQL that returns two columns: the node name, and the date/time that node was added to the availability group, for all nodes in a given availability group.
0
votes
2answers
24 views

Monitoring Maintenance Tasks in AlwaysOn Clusters

I have an environment with multiple AGs on a single server and they encounter a split situation in which some end up on one node and some end up on the other node (in a failover event, perhaps). When ...
0
votes
1answer
20 views

Network Packet Payload Error when Trying to Create Distributed Availability Group

I am attempting to create a distributed AG on SQL 2016 SP1, but keep getting the following errors. I have tried this in a VirtualBox (local desktop) and actual VMWare virtual environment but getting ...
1
vote
2answers
66 views

Always On Availability Group Failover Issue

I'm testing a PowerShell Disaster Recovery script and trying to fail over to an off-site AG DR (asynch) replica with this command ALTER AVAILABILITY GROUP [MyAG] FORCE_FAILOVER_ALLOW_DATA_LOSS On ...
3
votes
1answer
63 views

Availability Group backup preference not changing to primary

I have an availability group setup and I'm using Ola Hallengren backup scripts. At setup of the Availability group, the default option of Prefer Secondary was set, where it should have been changed to ...
0
votes
1answer
27 views

Is Scale out deployment for SSRS needed in an Availability group?

This is my first attempt at setting up SSRS, so I apologize if any question appears basic. I've researched as much as I can and am confused about the below things. I have my environment described ...
0
votes
1answer
56 views

Setting up AlwaysONAG in SQL server 2016 on windows 2016

I am going to set up AG's for the very first time, so with little or no experience, please guide me on below understanding: If i am setting up 4 node AOAG with below scenario: Node1-NY Data Center - ...
0
votes
1answer
39 views

Difference ReadOnly connections through sqlcmd vs SSMS

I configured my HA SQL Server to have a readonly. When I entered the following command sqlcmd -S servername -E -d db_name -K ReadOnly It points correctly to the secondary server. Then in SSMS I ...
0
votes
1answer
39 views

Get DAG members

Via transact SQL with this query: SELECT ag.[name] as 'AG Name', ag.Is_Distributed, ar.replica_server_name as 'Replica Name' FROM sys.availability_groups ag, sys....
2
votes
0answers
48 views

Failure to create availability group

I"m having a problem creating a new Availability group in a windows cluster. I have 3 nodes in the cluster. Two nodes are running as a FCI and a single stand-alone node that's a member of the windows ...
2
votes
1answer
76 views

Issue with adding a database to an Availability Group

I am having an issue with a particular AG where I am adding a DB (test123 below) to the AG and on primary it will show everything OK as follows: However, on secondary it shows with an exclamation ...
0
votes
1answer
52 views

Can a query whose execution plan uses parallelization use the cores of a processor on a another server that's part of the same Availability Group?

If I have two servers (server A and server B) that are part of the same Availability Group failover cluster, and a query running on server A has an execution plan that uses parallelization, is it ...
0
votes
0answers
41 views

Always on availability group database not truncating log files and keeps growing

In our environment we have 4 Node Replica 2 nodes in Synchronous Mode and 2 Nodes are in Asynchronous mode. Log back ups are happening every 15 minutes. The application just went live yesterday and ...
0
votes
0answers
12 views

ZERO granted memory to all queries with AlwaysOn replication

we came across the following issue that is best described by the image below. To sum up, all (yes, ALL) of the queries this SQL Server was receiving were stuck at RESOURCE_SEMAPHORE and had 0 (NULL) ...
0
votes
1answer
49 views

Multiple Availability Groups on one instance

I want to add second availability groups to Always on group and I need to change the port to 5023 but it is read-only as shown below: The first availability group has an endpoint which is listening ...
1
vote
1answer
21 views

SQL 2017 CU10 on Windows Server 2019 - Availability Group Listener fails to obtain IP from DHCP

We have a few instances of SQL 2017 CU10 Standard on several Windows Server 2016 machines, and normally we create Basic Availability Groups with Listeners obtaining IPs from our DHCP (192.168.112.0/22 ...
0
votes
1answer
36 views

Can an AlwaysOn Availability Group be used to mitigate downtime when there's replication syncing issues on the primary server?

Once in a while I'll run into syncing issues with my subscriber server in a transactional replication setup. Taking a new snapshot of the publisher causes significant downtime on my subscriber server. ...
0
votes
0answers
57 views

Always On Availability Groups does not have permissions

I have configured "Always On" on SQL server 2017, and also created a backup maintenance job using the wizard, but when I executed the backup job I got following error: Could not process the operation....
4
votes
1answer
113 views

SQL Server Primary Login Restrictions

I have read only routing setup and working fine. I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from ...
1
vote
0answers
17 views

AlwaysOn Replicas set to manual but failing automatically

My two synchronous-commit SQL AO replicas are set for manual failover mode but are failing over automatically. When I look through the logs and extended events, it looks just like what would happen if ...
0
votes
1answer
17 views

Microsoft SQL secondary read-only DB for SSRS?

I know that Microsoft SQL has AlwaysOn with a read-only secondary replica. However the cost of enterprise edition for all of our SQL servers makes this not possible. We have SSRS configured to run ...
0
votes
1answer
35 views

Availability Group Upgrade 2012 to 2016 Data Move

I'm working with a team to upgrade a four node AlwaysOn Availability Group from SQL Server 2012 to 2016. Two of the nodes are local and two are in Azure. The database holds a large amount of data (2 ...
0
votes
0answers
12 views

Encrypting File Sytem for FileStream Folders _ Using existing free space for EFS Processing

Can any one help me on below case related to EFS for File Stream folders. I am with SQL Server 2014 SP2 and Availability groups on Windows Server 2012 R2 Standard. I have 800 GB of FileStream ...
0
votes
1answer
26 views

Security Considerations for AG vs Log Shipping

we are considering removing our log shipping server and adding another node to our Availability Groups. While I understand that this should be better cause it's a/synchronous and will move the data to ...
0
votes
1answer
24 views

SQL Server Connection property applicationIntent

I have a doubt in ApplicationIntent connection property of SQL Server. The doc say it has two values ReadWrite and ReadOnly. If I set the value as ReadOnly, will it allow only read queries or will it ...
0
votes
1answer
59 views

MultiSubnet Failover with RegisterAllProvidersIP 1

I am using a 2 node SQL AG (2014 EE) (each on a different subnet) and the Listener is configured with 2 Static IPs. The client application uses SQL Native Client 11.4. My understanding is that this ...
0
votes
1answer
81 views

Pinging SQL Server Availability Group Listener sometimes fails

I have a client with a Server Availability Group. Let's say the AG listener is named AG1 . The AG1 listener is set to static IP with two IP address. When I do a nslookup of AG1, the DNS entry has the ...
0
votes
0answers
126 views

SQL Server 2016 Availability Group (Primary Unable to Read/Write)

We have a 3 replica SQL Server 2016 availability group. When the secondary synchronous goes unhealthy the database that resides on the primary becomes unreadable / unwritable, at this time the primary ...
0
votes
0answers
16 views

alwayson configuration

I am recently have a new project to implement AlwaysOn on 2016 SQL Server with two node synch/automatic failover for production Server. However, I need some recommendation on how can I implement that ...
0
votes
1answer
30 views

Measuring Availability Group synchronization lag - Clarifications

Can I get some help on below As I have started and learning about the Measuring of Availability Group synchronization lag between replicas inclusive of BCP , I want to understand and learn that does ...
1
vote
2answers
27 views

Upgrade SQL Server 2014 AG to SQL Server 2017 AG

Is it possible to migrate SQL Server 2014 AG (Windows 2012, SQL Server 2014) to SQL Server 2017 AG (Windows 2016, SQL Server 2017) using rolling upgrade method? What I mean is Build 2 SQL Server ...
5
votes
1answer
167 views

Strange blocking on readable secondary after reboot

We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the ...
1
vote
2answers
49 views

Stored Procedure on a read-only AlwaysOn Availability Group replica

We have a read-only reporting server that is part of the AlwaysOn availability group. Our developer wants to create some stored procedures. My question is where should the stored procedure be ...
4
votes
1answer
260 views

Why are these T-SQL jobs from different SQL Server instances executed on the same instance (AlwaysOn Availability Groups)

Recently, our blocked processes dashboard has been reporting blocked processes around the time when we do our statistic update. The reason was quickly found: an update statistics job step (T-SQL) ...
1
vote
1answer
38 views

SQL Server TDE - Database backups and post restore behaviour

Below is my SQL Server details: SQL Server 2014 Enterprise SP2 GDR with Availability Group configured. List item NOTE: The Databases are FileStream Enabled. Below are the series of actions I did ...
1
vote
1answer
67 views

Set up new publication on SQL Server 2014 database which is part of an availability group

I like to setup a publication (transactional replication) on a SQL Server 2014 database which is part of an availability group. We have a primary and one secondary (non-readable) replica. On the ...
2
votes
1answer
43 views

SQL Server 2014 AOAG + Replication?

I have an existing SQL Server (2014) setup using Always On Availability Groups. All the documentation says I should not try to install replication (aka distributor role) on any of the servers ...
4
votes
3answers
241 views

Availability Group database stays in Reverting for too long after a failover

Architecture: I have 2 Node Sync-Commit AlwaysOn configuration running on Multi-Subnet Failover Cluster. Primary node is in Europe and Secondary node is in US. I have only one database in the ...