Press "Enter" to skip to content

Category: Availability Groups

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

A Review of Distributed Availability Groups

Published 2020-09-11 by Kevin Feasel

Joey D’Antoni shares some thoughts on distributed Availability Groups in SQL Server 2016 and later:

I’m writing this post because I’ve been mired in configuring a bunch of distributed availability groups for a client, and while the feature is technically solid, the lack of tooling can make it a challenge to implement. Specifically, I’m implementing these distributed AGs (please don’t use the term DAG as you’ll piss off Allan Hirt, but more importantly its used in Microsoft Exchange High Availability, so it’s taken) in Azure which adds a couple of additional changes because of the need for load balancers. You should note this feature is Enterprise Edition only, and is only available starting with SQL Server 2016.

Read on for some of the positives around distributed AGs, as well as some negatives (mostly around the lack of tooling).

Comments closed

Restoring a Database in an Availability Group

Published 2020-09-08 by Kevin Feasel

Rajendra Gupta walks us through the process of restoring a database which is currently in an Availability Group:

You might think a question here– We can take production database backup and restore it on the development database. What difference does it make in a standalone database restore or availability group database restore?

Database restore works with the standalone database, but if the database is configured in the availability group, we cannot directly restore the database. It requires additional steps because of the AG configurations. Our database should be in the same state (AG synchronized) after the database restores as well.

In this article, let’s cover the steps to restore an existing availability group database in the SQL Server Always On Availability Group.

Read on for the answer.

Comments closed