New answers tagged

0

Regarding Asynchronous and Synchronous connections, this is not about speed of the connection, but about the results of the synchronization. Syncronous - The purpose is to make sure that the Primary server and the synchronously updated Secondary server are kept in synch. The transaction is only complete once the Primary and Secondary server transactions ...


3

There are a number of different factors involved but it is entirely possible to have a replica that is synchronous be faster or slower than an asynchronous one. To list a few: Replica is under too much load Hardware specification differences (i.e. synch runs ssd and async runs rotational) or synch is using 8 cores @ 3.6 GHz and async is using 4 cores @ ...


0

There is no reason to move the cluster core resources. They are NOT part of the availability group and function entirely on their own. There is no need to even have these on the same server as any of your AG resources. The core cluster group provides the administrative point for the cluster which is completely independent from any other resources on the ...


0

Currently Invoke-Sqlcmd does not support the ability to connect to a read-only secondary replica. The SQL Server, and some of the PowerShell community, however started working with the Program Manager at Microsoft to get SQLPS and SSMS improvements organized. A Trello board was opened in the past month that we are showing by votes what priority we want ...


0

The solution is quite simple. The issue is that the secondary node is set to 'Readable'. This locks the log file and any operation to shrink it will always fail with error 'The log for database [databasename] cannot be shrunk until all secondaries have moved past the point where the log was added. Change the configs for the AG and make the secondary ...


1

It sounds to me like you are suggesting that you require a highly available file share (see https://blogs.technet.microsoft.com/clausjor/2012/06/07/smb-transparent-failover-making-file-shares-continuously-available/). In order to do this you will obviously need Clustered storage (an ip and network name). It would be a little unusual to do this using your ...


0

Yes it can be done like normal. 32/64 bit can replicate to each other (you wouldn't think so but they do and Microsoft has documented it on MSDN). Also you can have an AG as a subscriber but you must use the name of the listener when setting it up. There's an MSDN article for this as well.


2

I would do it in this order: Change on Async Replica and restart SQL. Make sure everything is still synchronizing after restart Change Sync Replica and restart. Make sure everything is still synchronizing after restart Failover to Sync Replica Change on former Primary (Current Sync Replica), and restart. Make sure everything is still ...


1

Always On is not synonymous with (Always On) Availability Groups and instead is more a marketing term to describe a suite of Microsoft High Availability technologies (which I argue is at best ambiguous). The term actually started out as "Always On" (note the space) a long time ago as a pure marketing term (see my graphic as an example), but with the release ...


2

AGs are a lot more like a mix of mirroring and clustering than log shipping. Clustering would let you have multiple instances that fail over from one node to another node, but the nodes and disks are all absolutely distinct and only one instance runs at a time (though you can then load up other instances on the same set of nodes and make it more ...


1

There is one proper way to pre-stage the listener and one way to allow the cluster to create the listener itself. Please note that YOUR account is not what is used to authorize to AD to create the listener when creating it through FCM/Powershell or SQL Server, the CNO is used as security context. The official pre-stage way Find the OU with the CNO in it ...


0

The only workaround we found is as follows. Setup AlwaysOn Availability Group (VG-AdventureWorks2014), skip listener creation in the wizard or T-SQL. Go to cluster manager , create “client access point”. Enter the computer name and static IP created by your network and active directory administrator. The “client access point” ...


1

I would investigate building a .Net application that makes use of SQL Server Query Notifications to invalidate the cached data, and repopulate it from the source data. From the MSDN page linked above: Query notifications are used for applications that need to refresh displays or caches in response to changes in underlying data. Microsoft SQL Server ...


1

SQL Agent jobs are not contained within the Availability Group. When using SQL Agent jobs to manipulate a database within an Availability Group, there are all sorts of special considerations that need to be taken, particularly related to failover. What happens if the job is running during failover? The job will likely fail, as the replica it is running on ...


2

The terms are being confused sadly which is causing an issue. There's WCFS (Windows Clustering Foundation Services) which FCI (Failover Cluster Instance) runs on and requires shared disks. An AlwaysOn AG can also run on WCFS but it uses non shared storage and provides live read only replicas. In other words A FCI is a shared storage architecture and you ...


0

Don't forget you will need Enterprise Edition licensed for both nodes if you go down AG route with read only secondary, if your client is not going to use it for DR then its massive overkill in my opinion and very expensive. Transactional replication just requires standard edition and its very reliable if you set it up correctly and understand it, if there ...


0

The secondary replica can't be changed inside; and no you can't add indexes. You can do subsets and add indexes on the transactional subscriber of something but you will likely encounter issues if you ever need to modify your source tables (such as upgrading the application) or reload data for any other reason; it's good practice to remove it before any ...


0

Same kind of message appeared in our environment as well. What I understand is, as the DB was in AAG, and when the node (secondary AAG group) goes under restart, the DB start-up will be taken care of by AlwaysOn instead of the regular SQL Server 3-phase process! If the message appears without any secondary node restart, we may have to consider ...


0

I have successfully tested using a copy-only backup and the log backups that span the copy-only up to the point in time desired. You do need to have all the log backups. So if you have multiple replicas that you are using to do the backups(a failover happened for instance), you need to make sure and keep track of them. In my testing I simply set things up so ...


2

Couple of points of clarification. Nodes and replicas are not the same thing. Nodes refer to the Windows Failover cluster and replicas to AvailabilityGroup instances. So assuming you are running a 2 node WSFC, each hosting a SQL instance in an AvailabilityGroup, then yes, you will need another server to achieve quorum and avoid split brain behavior. The ...


0

After long struggle and network trace capture, I have following data points You can access the File stream shared folder using local machine name. You can access the File stream shared folder using Listener name from local machine which is part of cluster. You can not access the shared folder from any machine which is not part of cluster. You can access ...


0

The concepts of quorum, and owners are separate topics. Just because a member of the WSFC doesn't get a vote in quorum does not mean it can't own a resource. Additionally, SQL Server doesn't really play a role at all--the same concepts apply regardless of what type of clustered resource you're dealing with. Quorum: The quorum is the number of votes ...


2

Microsoft describes it's support for Availability Groups with SSRS in BOL. In particular is the section titled "Report Server Databases and Availability Groups." In my experience, having ReportServer and ReportServerTempDb be part of an AG works fine in most cases, but there is some peculiar behavior with subscriptions and failover--which is described in ...


2

I will answer your second question. If I do my backups in my secondary replica, the log files in my primary replica will continue to grow big since it doesn't get truncated coz the log is not backed up in the primary replica NO, this is where Availability Groups are so nice. No matter on which secondary replica you take transaction log backup it would ...


0

SQL Server backups are designed to be low impact and don't generally affect running systems. A correct backup and restore strategy takes into account a lot more than "when the server is not busy". A few examples: What is the business RTO and RPO for each of the 50 databases What is the DR expectation generally? What about log backups? These need to run ...


1

The statement you are reading in the Microsoft article says that you should not (and cannot) put a Distribution database inside an Availability Group. Why? Because the distribution database is a System database and Availability Groups will only fail over User databases. Therefore you will require another (4th) server in your situation to act as the ...


2

I do not believe your extracted quote is relevant to your problem. Your issue could be that your listener has multiple IP addresses. Under the default configuration, the listener will have multiple A records in DNS and your client will cache only one of them. Each time the cache expires it will randomly grab one of the IP addresses and sometimes it will get ...


1

Full recovery model and no log backups? AlwaysOn not pushing data? (so unable to re-use the log filespace)


0

If I have a 2-Node AlwaysOn Automatic Failover Cluster Instance with a shared disk and RAID-5 or RAID-10 to account for a SAN failure with the same setup in a second datacenter with data propagation via SAN Replication, is there a way to benefit from using AAG in this scenario? That depends, are you worries about any of the following scenarios? Strict ...


2

It sounds like you've done your homework to a large degree. Clearly you're aware of the log chain implications for COPY_ONLY backups. My advice to you would be to ensure you have a clearly though-out and tested restore plan. Without having tested your disaster recovery plan, you can never be certain it will work, and therefore it's not a plan. If you ...


0

@sean-gallardy I'm fully aware of these metrics and that is what I was collecting. Nevertheless, the SQL cluster was choking. And took more than 1 second (times from the client application). After raising an issue with Microsoft (which did not help). From the storage array there was no latency at all, so it wasn't the issue. Finally, I decided to ...


3

Turning my comment into an answer. There seems to be some misunderstandings of how certain performance counters work when it comes to availability groups. Only Synchronous Commit replicas will be counted toward Transaction Delay (ms)/Sec Asynchronous Commit replicas will not change this counter as they transfer no mirrored transactions (yes, this could have ...


1

To flesh out the accepted answer a little bit for anyone looking for code examples and order of operations, I had to: First create the Availability Group on the primary node Add a static subnet IP to the AG listener on the primary node. Connect to the secondary node and JOIN it to the Availability Group Connect BACK to the secondary node and add the ...


3

There's two main forms of failover you can use with SQL post 2012, The Windows Failover Cluster (WFC) which has been around for a while and the Always on High Availability Group(AO) WFC uses a single database which can move between multiple machines this method requires the database to be installed on shared drives (such as a SAN) so that when the cluster ...


1

Do you have to use a shared disk with AlwaysOn FCI in a 2-Node FCI? Yes if you are going with Always ON Availability Groups the shared disk(in some form) is a requirement. From BOL I will quote An AlwaysOn Failover Cluster Instance requires symmetrical shared disk storage such as a storage area network (SAN) or SMB file share. The shared disk ...


1

Let me make one thing clear before we go any further. ... SAN Replication or AAG, i.e. block-level or transaction-level AlwaysOn Availability Groups do not work at the transaction level. The unit of work is at the log block level. This is very different as we ship log blocks for all transactions, whether they are committed or not. The most likely ...



Top 50 recent answers are included