Demo Transcript: Configuring a SQL Server 2012 AlwaysOn Availability Group Listener

SQL11UPD01-TSCRIPT-04

This wiki is a transcript of a previously recorded video.

Configuring An AlwaysOn Availability Group Listener

Hi – welcome to introducing SQL Server 2012 AlwaysOn. This is the demo for configuring SQL Server 2012 AlwaysOn 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 with SQLskills.

In a previous demo we configured out Availability Group inside of the environment. We also enabled our database for partial containment so that our database-level users can authenticate in the database and their credentials follow the database from server to server.

In this demo we’re going to take a look at how do we create our Availability Group Listener to allow for the client connectivity to follow… to automatically fail over seamlessly from server to server inside of our… inside of using our Availability Group.

Now the first thing that you have to do to create the Availability Group Listener is you need to find out which host in the Availability Group is the primary. Right now that would be our EMU-SQL2 instance, and on this instance we can right-click on our Availability Group and do Add Listener.

For our Listener we’re going to provide a DNS name – in this case it’s going to be EMUAG1Listener. And once we’ve created our AG1 Listener we need to provide a port. This is running on the default port for SQL Server: 1433. Our network mode… we’re going to specify a static IP address and we’re going to add it to the available subnet, in this case 192-168-31-zero – and our IPv4 address is going to be 192-168-31-70.

Once we’ve added our IP address and our Listener name, if we click script, then that will give us the DDL command that’s actually going to be executed against our Availability Group to add this in. Or we can click OK, and when we click OK, if we jump out to our Windows Server Failover Cluster, we’ll see that it’s adding the resources into our resource group for our Availability Group. And the IP address is added and it’s a dependency for the Availability Group Listener name to come online, which is what the virtual network name for our applications to connect to should be.

So if we jump over to our OLTP application and we change our connection strings so that it connects to EMUAG1Listener, and we tell it to change. Even though we had it connect to the Listener, when we start our workload it’s going to route to the primary replica in our environment – which is currently EMU-SQL2.

Now if we go back to our Availability Group and we do a failover… and we send it back to EMU-SQL1… and we watch our application… there will be a brief period of connection failure while the failover actually occurs and the database is brought online, but what we’ll see inside of our application is that it… as soon as the instance is back online and has a new primary, the application will immediately reconnect to that primary, which in this case is EMU-SQL1, through the Listener and that’s the automatic transparent redirection that occurs inside of the environment.

We can also fail over from AG1. Now if we fail over to EMU-SQL3, keep in mind that EMU-SQL3 was not enabled for automatic failover. It is a synchronous replica inside of our environment, so it is a viable secondary for us to fail over to.

However, if we do make a failover to EMU-SQL3, there is not automated failover. If something happens to EMU-SQL3, it won’t automatically fail back to 2 or 1, so we probably don’t want to do this for a very long time but if we needed to for some maintenance reason we can actually send the Availability Group over to there. The Listener will time out temporarily while the instance is down and failing over, and as soon as it comes back online with the primary on EMU-SQL3, as the failover completes our application will reconnect and now it’s up and running again against EMU-SQL3.

So any of the replicas in our environment can actually be a primary in the configuration as long as it’s viable for failover. However, if you’re running on a secondary that’s not enabled for automatic failover, you may not be able to... it may not fail back if something happens on that secondary. So that’s a consideration to keep in mind.

And with that, we’ll stop our workload and close our UI, and that completes our AlwaysOn Availability Group connectivity for SQL Server 2012 demo.

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