Press "Enter" to skip to content

Category: Availability Groups

Checking for Missing Failover Cluster Dependencies

Published 2021-02-01 by Kevin Feasel

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

Published 2020-12-21 by Kevin Feasel

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

Published 2020-11-20 by Kevin Feasel

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

Published 2020-11-19 by Kevin Feasel

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

Published 2020-11-10 by Kevin Feasel

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

Published 2020-10-30 by Kevin Feasel

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 https://www.sqlrx.com/steps-for-installing-sql-server-alwayson-availability-groups/ , 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

Automatic Seeding of Availability Groups

Published 2020-10-28 by Kevin Feasel

Jamie Wick takes us through some considerations when trying out automatic seeding of availability groups:

In SQL Server 2016 Microsoft introduced Automatic Seeding for Availability Groups (AG). The Automatic Seeding process streams the database files directly to the secondary server(s) using the database mirroring endpoints, removing the need to restore the databases, before joining them to an AG.

Read on for Jamie’s thoughts and notes.

Comments closed

Mixed MultiSubnetFailover Support on AGs

Published 2020-10-07 by Kevin Feasel

Andy Mallon continues a line of thought:

In yesterday’s post, I showed how to configure an availability group (AG) to use the RegisterAllProvidersIP=0 when you can’t get clients to connect using the MultiSubnetFailover=true connection string attribute.

I mentioned that you have to make some trade-offs when you set RegisterAllProvidersIP=0, and included this comparison:

But….when if you can eat your cake and have it, too?

In some cases, you’ll have some applications & clients that are not able to use MultiSubnetFailover=true, and other clients that can. Perhaps you’re working on updating a bunch of legacy Java apps to move from old jTDS drivers to the current Microsoft JDBC drivers that properly support MultiSubnetFailover=true. Parts of your codebase have been updated, and you want them to make use of the connection string attribute for fast cross-subnet failover. But other parts of your codebase are still being updated and rely on the RegisterAllProvidersIP cluster parameter to be false. Wouldn’t it be nice to have both?

Read on to learn how.

Comments closed

Multi-Subnet Availability Groups and MultiSubnetFailover

Published 2020-10-06 by Kevin Feasel

Andy Mallon takes us through an all-too-common scenario:

With a default configuration, multi-subnet AGs require that the clients connecting to them include MultiSubnetFailover=true as a connection string attribute. This attribute tells the driver to expect DNS to provide multiple IP addresses for the Listener name, and to try all of them to find the correct IP to connect to for that network name. Clients that do not specify this attribute will get multiple IPs and not know how to handle them properly–most drivers will pick up one of the returned IPs at random (or maybe just seemingly random), and try to connect to that. This can result in random (or seemingly random) connection failures when it picks the wrong IP.

However, not every client or application will support this connection string attributes. In my experience there are two extremely common reasons that you can’t use MultiSubnetFailover=true:

Read on to see what you can do in that case.

Comments closed

PolyBase and Availability Groups

Published 2020-09-30 by Kevin Feasel

Rajendra Gupta has a detailed article on working with PolyBase in an Availability Group:

In this 28th article for SQL Server Always On Availability Group series, we explore the high-availability for the SQL Server PolyBase(SSB) external tables using AG groups.

There’s a lot of detail in the article and it’s worth reading in conjunction with Nathan Schoenack’s post. Someday I’ll get to the blog post on my backlog around PolyBase and AGs, especially with scale-out clusters. Someday.

Comments closed