Demo Transcript: Maximizing Hardware Utilization with SQL Server 2012 AlwaysOn Readable Secondary Replicas

SQL11UPD01-TSCRIPT-06

This wiki is a transcript of a previously recorded video.

Maximizing Hardware Utilization with AlwaysOn Readable Secondary Replicas

Hi – welcome to introducing SQL Server 2012 AlwaysOn. This is the demo for maximizing hardware utilization with SQL Server 2012 AlwaysOn Readable Secondaries. 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.

So for configuring our Readable Secondaries in our environment – the first thing that we have to do is we have to go to the Availability Group properties for our Availability Group on our current primary replica for Availability Group. In this case it’s EMU-SQL2.

If we go into the properties dialog, we can change our Readable Secondary mode for each of our replicas. The default configuration’s going to be No – meaning that no ReadIntent or read-only connections can be made to the replica while it’s in a secondary role. The only time it can be connected to is when it’s in the active primary role.

The second option is ReadIntent-only, which allows connections when the connection string include the Application Intent equals ReadOnly option as part of its connection string, and the last is yes, which would allow all connections to the server but it would restrict them to being read only for the Availability Group. And that could be useful in scenarios where you may have a report writer who wants to do an ad hoc or something that they just need to be able to connect to the server directly, and in that case they wouldn’t connect to the Availability Group Listener – they would connect to the fully-qualified domain name of the server. For example, if they wanted to, right now with EMU-SQL1 being in a secondary role, they could connect to it using the EMU-SQL1 name and be able to run a read-only ad hoc request against the AdventureWorksDW database there.

So what we’re going to do is we’re going to set all of our secondaries up as ReadIntent-only connection available, and then we’re going to open a script that is inside of our Availability Group demo for setting up read-only routing. And what this script is going to do is it’s going to modify each of the replicas and provide their read-only routing URL, which is the TCP address and port for the instance that’s running on that server, and then we’re going to set up our read-only routing lists associated with each of our servers when they’re going to be in a primary.

So when EMU-SQL1 is a primary here, the read-only routing list is going to redirect ReadIntent connections to our EMU-SQL2 instance first and then our EMU-SQL3 secondary inside of our environment second. And for 2 it’s going to route to 3 and then 1, and 3 it’s going to route to 1 and then 2.

Now once we execute this and alter our Availability Group configuration. If we drop down to our OLTP application, we also have our reporting application open; both of them are configured to talk to the Availability Group Listener to make connectivity and if we execute our reporting workload it connects to EMU-SQL2, which is the current primary. However, if we go change our connection string and we add the Application Intent equals ReadOnly parameter to it and we change our connection we’ll see immediately it reconnects but it reconnects to EMU-SQL 3, which is our first Readable Secondary inside of our configuration for EMU-SQL2 being a primary.

Now if we start our workloads, and then we go over to EMU-SQL3 and we shut it down… and we look at our reporting application, we should see the connection fail and eventually our Listener is going to reroute our read-only connections for this application over to EMU-SQL1, which will become the next Readable Secondary in the list. And it has reconnected and continues to execute.

Now if we bring SQL3 back online… and what we’re going to do is we’re going to start our OLTP workload as well, which is running against SQL2 right now… and we’re going to stop the SQL service on SQL2, simulating a failure of our primary in our environment. And if we look at our two applications again… our OLTP workload is disconnected momentarily while it is trying to fail over to EMU-SQL1, which is our automatic failover partner for the configuration. And if you notice our reporting workload was taken offline while the failover was occurring and it has now reconnected back out to EMU-SQL3, which is the next Readable Secondary since EMU-SQL2 is offline while EMU-SQL1 is online.

So this gives an idea about how the Readable Secondary routing works and it keeps rerouting those ReadOnly Intent connections to the next Readable Secondary inside of our environment rather than having that workload running against our primary and causing additional load against the primary while it is in that role.

With that, that concludes our demo on making usage of Readable Secondaries inside of our environment and getting better maximizing our hardware utilization with SQL Server 2012 AlwaysOn.

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