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.

learn more… | top users | synonyms (1)

3
votes
2answers
70 views

How is an AAG listener implemented? Where does it run?

I'm fairly new to SQL Server but generally well versed in RDBMS and network topics. I'm trying to wrap my head around AlwaysOn Availability Groups. I've read a handful of articles on MSDN and Googled ...
1
vote
0answers
58 views

Collecting DB Used Space (MB) from SCOM on an Always-On DB

My client has asked for database sizing for capacity planning. I am trying to use System Center Operations Manager (SCOM), however when I get to the Always-On Cluster, it doesn't seem to display the ...
5
votes
1answer
2k views

SQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database '…' version 782

While testing an upgrade from SQL Server 2014 SP1 (12.0.4422.0) to SQL Server 2016 CTP 3.2 (13.0.900.73) I was following the recommended update process and ran into an issue where the database would ...
1
vote
2answers
142 views

Shrink Transaction Log While Using AlwaysOn Availability Group 2014

I'm running a SQL Server 2014 Availability Group with a single secondary in asynchronous mode. My logs in all the Availability Group databases keep growing and the built-in shrink tool only takes ...
3
votes
2answers
585 views

Availability Group Listener

I'm looking at AlwaysOn availability groups. The more I look at it the more it appears that the availability listener group is a single point of failure. Where exactly does the listener actually run? ...
1
vote
0answers
41 views

Trans Replication and Always On Listener Group Name

We have currently (SQL Server 2012 STD) which is continuously replicating from 8 different database server for reporting purpose to server which is on SQL 2008R2. We would like to create an Always On ...
5
votes
1answer
57 views

Checking SQL AlwaysOn backup target with fn_hadr_backup_is_preferred_replica, different answers with different permissions

I've implemented AlwaysON backup with Ola's script, those are running fine on designated server configured for backup. I would like to automatically check backups using sql-scripts fired from a ...
3
votes
1answer
111 views

SQL Server 2014 Always On .. can i have replication without a listener

I am still a novice with SQL Server Always On, having recently converted from a standalone server to an always on configuration. I currently have 2 nodes in the same subnet configured in my ...
3
votes
0answers
33 views

Incremental statistics with failover

We have two AlwaysOn availability replicas - secondary readable. We do have an incremental statistics on a large table. When a failover happens, SQL Server tries to update statistics on that table ...
0
votes
2answers
95 views

Avoid user login Directly to AlwaysOn High Availability Primary

I have setup an AlwaysOn High Availability in Sql Server 2014. my IP addresses are according to this : 192.168.1.189 -- Listener 192.168.1.180 -- Replica 192.168.1.181 -- Primary Right now users ...
6
votes
0answers
286 views

Why does resource in AlwaysOn fail?

I have two SQL Servers (12.0.4100.1) and Availability Group (AG) configured on them. Sometimes some server hangs. It responds on ping, but RDP connection are not available, SQL Server doesn't respond ...
0
votes
1answer
63 views

Availability Group failover stuck in Resolving State

I am setting up a lab for testing automatic failover for SQL Server I have a 2 node WSFC (SQL-CLUSTER-TEST-01) with the nodes SQL-TEST-01 SQL-TEST-02 A listener SQL-HA-GRP-01 My initial testing ...
1
vote
0answers
23 views

Database Consolidation / AlwaysOn Availability Groups

How can I calculate the potential savings in RAM, Storage and CPU by consolidating a certain number of servers into single server? How can I go about consolidating the BI servers' and the OLTP ...
2
votes
0answers
74 views

How to avoid Network issues for SQL Server 2012 AlwaysOn

I have SQL Server 2012 and have setup AlwaysOn for High Availability. The infrastructure is on Azure VM's. After moving to this setup, we are frequently observing Network issues which either causes ...
3
votes
1answer
281 views

Misaligned Log IO Issues and Unmatched LUN sector sizes in AlwaysOn

I'm running into a little bit of a snag on a high log send queue in a 2014 SP1 Asynchronous Mode AlwaysOn Environment with a secondary replica in Azure. For one of our higher transaction volume DB, ...
0
votes
0answers
150 views

AlwaysOn ReadOnly Routing

I am having some trouble setting up the AlwaysOn readonly routing. I'd appreciate if someone can help me solve this issue. Setup: 3 Nodes in Primary Datacenter (NODE1, NODE2, NODE3) 1 Node in DR ...
1
vote
2answers
104 views

SQL Server AlwaysOn from multiple origins to a single secondary replica server

I started working in a small company which have three separate databases, for three different systems. They are SQL Server 2008, 2012 and 2014, installed in different servers. We'd like to set up an ...
0
votes
1answer
57 views

SQL Server - How does Always On in a shared nothing config maintain transaction durability during a failover?

I've been reading up on Always On, and I was wondering how transactional durability is maintained during a failover when using a shared nothing configuration. The only things I can find on google ...
0
votes
0answers
25 views

SQL alwayon azure VM

Please help me I have to finish the task tomorrow morning The Scenarios as below : ALL VMS in AZure SQLSRV1(192.168.1.X) SQLSRV2(192.168.1.X) both in the same cloude service with ip 104.40.151....
0
votes
1answer
63 views

BACKUP Detected Corruption in the database log

I took a backups of a few db's on server A (2008R2), Then restored it to server B (2012). One database had TDE enable, so I disabled it, took a back up then proceeded to server B. The other database, ...
1
vote
0answers
70 views

AlwaysOn Primary Showing as Question Mark

If I look in the dashboard for my AlwaysOn implementation I can see my groups are synchronised and no data loss. They also failover successfully but when looking in the Group folder list under ...
0
votes
1answer
30 views

SQL server 2012 Enterprise [duplicate]

Will CDC work with SQL Server Always on Availability groups? Do you need to set up CDC on both/multiples nodes if using Availability Clusters?
0
votes
0answers
72 views

How do I alter containment to none for partially contained db with default language?

Experimented with Contained DBs in an Always-On Availability Group and decided against them. I want to UN-contain the databases now but when I run: alter database <myDB> set containment = NONE ...
0
votes
1answer
134 views

SQL Server 2012 AlwaysOn performance best practice

Is it best practice to have 2 Primary then 1 Secondary for SQL Server AlwaysOn Availability Groups? My purpose for having 2 Primary instances is to balance the database load. (e.g. my db1 and db2 is ...
2
votes
1answer
124 views

Availability Groups - Can async replica delay failover of sync replica?

Availability Groups SQL Server 2012. Node 1 and Node 2 in primary data centre - sync mode for HA Node 3 in dr data centre - async mode for DR If a manual failover is performed between node 1 and ...
0
votes
1answer
92 views

Why does sys.fn_hadr_backup_is_preferred_replica take so long? Is there a better way?

I am using sys.fn_hadr_backup_is_preferred_replica to query the databases on each of my Availability Group replicas to figure out if the database should be backed up on that replica - then, if the ...
2
votes
1answer
103 views

Why take full backups when you can do COPY_ONLY?

With a SQL Server 2012 availability group, you can only take COPY_ONLY full backups on replica. You can make regular BACKUP LOG. As it's possible to restore logs backups after a database restoration ...
2
votes
1answer
243 views

SQL Server backup status report

We have 30-40 SQL server instances in different servers (2012 with AlwaysOn). Is there any kind of report that can be made which captures the daily backup status and sends a mail, perhaps, or at least ...
1
vote
0answers
80 views

Securing High-Availability on Partitioned Database

I have a 90GB SQL 2012 database I am sharding over 3 database and I plan on growing as more clients are coming. A new customer came to us with a demand to know on we secure high-availability in our ...
5
votes
2answers
196 views

Always On Availability Group, Always redirect user to read only instance

We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are ...
2
votes
1answer
292 views

SQL Server 2012 Always On Setup with two nodes

I have AlwaysOn setup with two data nodes and one witness. I faced an issue where my PRIMARY Server restarted abruptly. Here're some issues I faced during this timeframe. Due to abrupt restart of ...
2
votes
1answer
74 views

Is there a Windows Performance Counter which contains the “Log Send Rate” for Availability Groups?

I wish to be able to get the "Log Send Rate kb/s", as per the SSMS AG dashboard (which is also available via log_send_rate column on the DMV sys.dm_hadr_database_replica_states) I'd like to get it as ...
3
votes
2answers
109 views

SQL SERVER 2014 Standard - High Availability Listener solution

The company has SQL Server 2014 Standard licenses and we're standing the servers up in a VMware environment. My initial plan is to create 2 instances on each DB server and run Instance1 on SQL1 as ...
0
votes
0answers
41 views

Is there a way to set up DNS to point to Always-On Read-Only node w/out ApplicationIntent?

I know that we can use ApplicationIntent=ReadOnly to connect to the secondary read-only nodes in an AG setup. Is it possible in some way to tell the DNS server which node is which on failover so we ...
1
vote
1answer
52 views

Always On Architecture Question

I have three servers Server1, Server2 and Server3. Server1 contains DB1, DB2 and similarly Server2 Contains DB3 and DB4. Can I use Server3 to host the AG groups of Server1 and Server2. I have drawn a ...
0
votes
1answer
33 views

Restoring a database that belonged to an AG [duplicate]

I'm trying to perfect our disaster recovery steps... We take backups of a database that, in production, is in an AG. It is the only database in the AG. If we lose the building, I need to be able to ...
1
vote
1answer
2k views

RegisterAllProvidersIP 1 or 0 For The Cluster Resource

I have set up clustering across two sites, at each site there is a single instance of SQL 2014 enterprise and I am using Availability Groups. I know I need to set RegisterAllProvidersIP for the AG ...
2
votes
1answer
256 views

SQL Server AG databases went into 'Synchronizing/In recovery' after upgrading to 2014

We have 3 replica AG setup: 2 replicas are in sync/automatic failover the other(DR Server, different subnet) in asynchronous/manual mode All these replicas were on SQL Server 2012. Recently we ...
3
votes
1answer
534 views

Load balancing with Windows Clustering and AlwaysOn Availability Groups

I have some questions regarding Clustering and AlwaysON. If I need to build a 3 node cluster - 2 active and one passive. Each node will be 16 CPU and 128 GB RAM. Does this mean that I will have ...
1
vote
0answers
67 views

IP from a different subnet is registered even after node and resource were removed

We run two SQL server at primary site in AlwaysOn High Availability Group on subnet 10.10.0.0/24 A third SQL server is running on secondary site in a different subnet 10.10.1.0/24 We have had third ...
1
vote
1answer
520 views

Geo-clustering using SQL Server 2012 Enterprise with AlwaysOn

We are trying to take our current SQL 2012 Enterprise edition 3 node cluster (OS 2012) and add a 4th node to the cluster that will be at another location. And everything I find is telling me to use ...
2
votes
1answer
204 views

Availability Groups and Transactional Replication

My client has a setup as shown below. AG Group A ---------> Server A (DB--> A) ----> Server B ( DB--> A) Transactional Rep --> Server A ( DB-->A ) ---> Server C ( DB--> B) AG ...
4
votes
1answer
65 views

SQL Server - Company wants HA for single instance with 1 userDB - sanity check and questions

I am new to SQL Server (2 months) and learning at a gov company (europe). The job is to make a HA solution for this 1 productive instance (1 database). Following is given SQL Server 2012 Windows ...
0
votes
0answers
55 views

SQL Server 2012 , TDE key rotation for a database in Availabilty groups

I need to do TDE Key Rotation for a database in availability groups in SQL Server 2012 for PCI Compliance. Need help please on procedure to follow .
4
votes
1answer
115 views

Availability Group log_reuse_wait = Replication

We have a SQL 2014 Availability Group with 3 replicas of our primary database - 2 in our main data center and 1 in our DR center. We did a large index rebuild on the primary which obviously needs to ...
1
vote
3answers
633 views

shrink database in always on availability

hi i was using this query for shrinking the database log file USE Tfs_CoE; GO ALTER DATABASE Tfs_CoE SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (Tfs_CoE_Log, 1); GO ALTER DATABASE Tfs_CoE SET RECOVERY ...
0
votes
1answer
476 views

Log Shipping copy job doesn't delete old files on a secondary node that is part of AlwaysOn configuration

I have the following setup, all servers are SQL 2014 Enterprise: 2 node AlwaysOn availability group; third server for off-site DR. Log backups are being taken on the primary node, copied to the ...
3
votes
2answers
88 views

DTC and AlwaysOn

We're heading into a new infrastructure: HyperV, Violin storage & dualsite setup We're looking into AlwaysOn for dual site, but we are using DTC on many of our systems. Do you know if the ...
2
votes
1answer
553 views

Rename Availability Group and AG script

I have two questions for you guys. I really appreciate your help. 1.- Is there a way to rename an Availability Group? 2.- Is there a way to get the T-SQL statements in order to re create a ...
2
votes
1answer
174 views

SQL Server 2012 Always On AG - DBEngine Service account locking

I am having a strange issue and am hoping you guys might be able to help! Problem: I have a 2 node SQL Server 2012 Availability Group Cluster utilising a FSW. Both nodes are using the same DBEngine ...