Presentation Transcript: SQL Server 2012 AlwaysOn Availability Group Connectivity

Article
1/17/2024

SQL11UPD01-TSCRIPT-03

This wiki is a transcript of a previously recorded video.

Slide: Title Slide

Welcome to introducing SQL Server 2012 AlwaysOn.

Slide: Introducing SQL Server 2012 AlwaysOn Availability Group Connectivity

In this video we’ll cover introducing SQL Server 2012 Availability Group Connectivity. My name is Jonathan Kehayias – I’m a SQL Server MVP, a Microsoft Certified Master for SQL Server 2008, as well as a Principal Consultant for SQLskills.

Slide: Simplified Failover Configuration

In SQL Server 2012 we get a much easier, much more simplified failover configuration for our database environment and the way that it’s significantly simplified is through the implementation of AlwaysOn Availability Groups – specifically inside of the Availability Groups one way that we get a really easy way to configure the environment is through the Availability Group Listener.

Slide: What Happens When…

In SQL Server 2008 R2 and prior, if you have a failover of a primary database and you’re using database mirroring, for your applications to be able to fail over seamlessly from principal to mirror as a part of the failover, they have to have the failover partner connection string parameter configured in the connection string to specify what the mirror server name is going to be.

With that, there are some limitations. The biggest one being that you only have one failover partner that can be specified inside of the connection string.

If you’re using log shipping to provide a disaster recovery or warm-standby server for failover, in the event of a catastrophe, you have to go manually reconfigure your application to redirect all the clients to the failover server.

The only way that you get a really seamless failover is with failover clustering or database mirroring, and you only have… there are limitations associated with how many failover partners that can be specified for database mirroring.

So in SQL Server 2012, with AlwaysOn Availability Groups we have a Listener that gets created as a part of the Availability Group inside of Windows Server Failover Clustering and the Listener actually provides a virtual name that our application will connect to. Associated with the Listener is an IP address, just like you would have for your failover clustered instance virtual name that you would typically have with a SQL Server failover cluster.

The way this works is Windows Server Failover Clustering maintains the virtual name as a cluster resource that is owned by a single node at a time. This allows you to configure applications to connect to the virtual name, and in the event of a failover they’ll seamlessly be redirected to whichever secondary replica becomes the primary after the failover completes.

Slide: Availability Group Listener

The Availability Group Listener is a Windows Server Failover Clustering resource for the Availability Group cluster resource group and it has a dependency that exists on an IPv4 or IPv6 IP address. The specifics of the IP address can be defined using the DDL for creating an Availability Group Listener inside of Transact-SQL or it can be defined using the UIs as a part of configuring a new Listener inside of the environment.

The Availability Group Listener only runs on the current primary node in the Availability Group and the way applications should be configured is so that they connect to the Availability Group Listener’s virtual name. This gives the application seamless redirection of their connections and there are two different types of redirection that can occur associated with an Availability Group Listener.

The first one is the seamless failover – similar to what you get with a traditional SQL Server failover clustered instance and its virtual name, where the actual virtual name moves from cluster node to cluster node as a part of the cluster resource group and the applications are seamlessly redirected as part of the resource group moving.

The second type of redirection of connections that you can get is read-only intent connections, which we’ll cover more further later in another video. When an application makes a read-only intent connection, it can actually be automatically redirected to one of the readable secondaries that are configured inside of the Availability Group in the environment, allowing you to offload read-only workloads from the primary server to a secondary that’s been configured for allowing read-only connections to occur to it.

Slide: Availability Group Listener Backwards Compatibility

With the Availability Group Listener, there’s full backwards compatibility with database mirroring – so if you have an application that’s been deployed to hundreds of clients and it’s a thick client type of application that all the connection strings are maintained in local configuration files, or local ODBC configurations in the DSNs on the server, or on the clients (excuse me), that connection string can actually be leveraged to allow support for a single secondary configuration using an Availability Group, while you make changes to the application or make a deployment plan to be able to make changes to all the hundreds of clients that are out there with this connection string that specifies the server and failover partner specification in the connection string.

This makes it require minimal work to actually leverage upgrading to SQL Server 2012 with Availability Groups and it allows the seamless migration from your database mirroring environment currently over into an Availability Group configuration.

If you have multiple secondaries defined, as long as your connection strings specify a specific server – for example on screen we have ServerA as the AG Primary and ServerB as the Availability Group secondary, if you’re connecting directly to ServerA and you have a failover partner specification for ServerB, if you had an alternate ServerC in the environment, that was another secondary, your application would not be able to connect to ServerC without having changes made. If you have an Availability Group Listener defined, your application would be able to seamlessly fail over between ServerA, ServerB , and ServerC using the Availability Group Listener as the server connection.

Slide: Summary

In summary, in SQL Server 2012 we get a much easier high-availability story and solution associated with the use of Availability Groups in AlwaysOn and the Availability Group Listener that is managed by Windows Server Failover Clustering.

This gives us the seamless failover of our applications after an Availability Group failover through the use of the virtual network name, and we have backwards compatibility with database mirroring to allow us to easily upgrade our environment to using an Availability Group, and then we can make changes to our application to really leverage the full support of the power behind the Availability Group Listener and the virtual name and having seamless failover across multiple nodes, not just two in the environment.

Return to [[SQL Server 2012 Developer Training Kit BOM (en-US)]]

Share via