Press "Enter" to skip to content

Category: Availability Groups

A Warning on Using Distributed Network Names

Allan Hirt has a warning for us:

DNNs are supported as of SQL Server 2019 CU2 and require Windows Server 2016 or later. I wrote more about them in my blog post Configure a WSFC in Azure with Windows Server 2019 for AGs and FCIs. Go there if you want to see what they look like and learn more.

Right now, I cannot wholeheartedly recommend the use of DNNs for listeners or FCIs if you are using Enterprise Edition. Why?

Read on to learn why.

Leave a Comment

Availability Groups and the Shakes

Niko Neugebauer coins a term:

Disclaimer: I am using the word shake by my own initiative and no Microsoft Documentation ever to my knowledge ever mentioned that situation. Those shakes are represented most of the time as health events to the cluster, such as the Lease Timeout resulting in a sudden attempt of Failover.
Why did I choose that word ? I don’t know. Honestly. 🙂

Read on to see it in context around hosts, CPU, and especially I/O.

Leave a Comment

Preparing an Availability Group for VM-Level Replication

David Klee takes us through an interesting scenario:

If you have a SQL Server Availability Group (AG) and the VMs are being replicated to a disaster recovery site (cloud or on-prem), chances are the networking topology is not the same at the second site. These replication technologies can include VM replication, SAN LUN replication, or replicating server-level backups to the second site. It is quite complex to have the same network subnet existing at both sites, so usually, the secondary site contains a different networking subnet structure. It means that the servers being brought up at the secondary site are going to receive different IP addresses.

The Availability Group architecture, especially with its dependency on the Windows Server Failover Cluster (WSFC) layer, are quite intolerant of having these IP addresses changed. The utilities performing the failover might not even be aware of the WSFC-specific components that need to be adjusted.

Click through to see what you can do.

Leave a Comment

Changing IP Addresses in an Availability Group

Sreekanth Bandarla is ready to make a change:

In this blog post, let’s see how to change all the IP addresses involved in a typical Always on Availability group configuration. In my setup, I have an AG with two replicas and a listener. See below to get an idea of my current environment on which I am going to change all the underlying IP addresses.

Click through for a step-by-step process, as well as a few things to remember.

Comments closed

Checking for Missing Failover Cluster Dependencies

Chad Callihan ran into an error creating a new database:

A tool that restores a model type database and does a bit of configuration work was failing. I took a look at the stores procedures and started to go step by step. It didn’t take long before getting this error message when attempting to restore/create a database:

Msg 5184, Level 16, State 2, Line 3
Cannot use file ‘D:\sql_log\CC_Test_name_4.ldf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

Click through for the solution.

Comments closed

Sync Logins between Availability Group Replicas

Taryn Pratt has a process:

Always On Availability Groups can support up to nine availability replicas, and while we don’t use anywhere near that many replicas in each of our clusters, we do have 2 replicas per cluster (3 servers total), with the replicas being used as a readable secondary.

Since we use readable secondaries in our environments, the application needs to connect to both the primary and the secondary servers with the same login. The catch is, logins don’t automatically sync across replicas. If the logins don’t sync, the application won’t connect to a secondary, which results in login failures.

Read on for one way to solve the problem.

Comments closed

Monitoring Availability Groups via DMV

Rajendra Gupta continues a series on Availability Groups:

In the previous article, Explore dynamic management views for monitoring SQL Server Always On Availability Groups, we explored the DMV’s for the Availability group for Windows Failover Clusters.

This article takes a further step and covers useful DMV related to availability replica and databases. Let’s start our journey with this article.

Click through for the article.

Comments closed

Database DevOps and Availability Groups

Kendra Little has some considerations for us:

One question which comes up periodically from our Microsoft Data Platform customers is whether there is anything special that they need to do when implementing version control, continuous integration, and automated deployments for a production database which is a member of a SQL Server Availability Group. 

The good news is that deployments are quite straightforward. You’ve most likely already configured a listener for your applications to connect to the Availability Group and automatically find the writeable database. To do a deployment, you simply need to connect to the listener as well.

There are a few other considerations which are helpful to think about when building your approach to database DevOps, however.

Check out the article for the details.

Comments closed

Policy-Based Management for Availability Groups

Rajendra Gupta takes us through the policies in Policy-Based Management which relate to Availability Groups:

SQL Server Always On uses the Policy-Based Management(PBM) for determining its health. In the earlier articles, we discovered AG dashboard features to monitor synchronization status, data loss, replica states. It executes the PBM policies on availability replicas (primary and secondary), availability group database and organizes the results in a dashboard.

The primary replica contains information for all replicas, their synchronization states. It has sufficient information to compute the health for all availability groups. If we launch the AG dashboard from the primary and secondary replica, we can note the difference in monitoring.

This is perhaps the most under-developed and under-utilized feature in SQL Server. It is conceptually so powerful and what’s in there shows some of that power, but to this day, so much is still lacking in PBM.

Comments closed

Installing an Always On Availability Group in SQL Server 2019

Ginger Daniel takes us through the process of setting up an Always On Availability Group in SQL Server 2019:

With SQL Server 2012 Microsoft introduced the AlwaysOn Availability Group feature, and since then many changes and improvements have been made.  This article is an update to our previous article , and will cover the prerequisites and steps for installing AlwaysOn in your SQL Server 2019 environment.

Click through for a checklist of pre-requisites and installation + configuration steps.

Comments closed