SQL11UPD00-TSCRIPT-03
This wiki is a transcript of a previously recorded video.
Related content assets:
Hi this is Roger Doherty, and this is part one of a four part demo exploring all the great new capabilities in SQL Server 2012. In this first demo we are going to look into a great new high availability and disaster recovery solution in SQL Server 2012 called AlwaysOn. So let’s jump in there and check it out.
So what I’ve got here is a very basic web application that we use in an on-premise data center to manage a product catalog. And it’s just a simple ASP.NET application that lets us do Create, Read Update and Delete type operations against a SQL Server back end. So you can see here I can just go and bang in new products, and I get a little visualization of the product like our Kinect Chair. I can edit them, I can delete them, very very simple database application here with ASP.NET talking to a SQL Server backend.
The other interesting thing about this app is that it’s got some integrated reporting from Reporting Services. So if I click on this link, I actually go out to a Reporting Services instance and I render this nice product catalog report which I can print out and check my stocking levels. And what you will notice here right now is that this report is running on something that we are calling the Primary Replica. It’s a mystery right now as to what that is so let’s dive into the underlying infrastructure that this database is actually running on and explain that in a little bit more detail.
So in SQL Server 2012 we have a new high availability and disaster recovery solution called AlwaysOn, and you can see it exposed here in Management Studio. And what we’ve done in advance is we’ve set up what we call an Availability Group. An Availability Group is a collection of replicas of the databases that are important to your application so that you can manage them as a unit and maintain high availability. So if I pop open the dashboard here we can see how our Availability Group is set up and how many nodes it uses. You can see here that we’ve got three nodes in our Availability Group, a primary, which is the current instance that that report was being served out of, and we’ve got a couple of secondary’s, one which is in a synchronized state that is available for automatic failover in case we have some kind of a problem with our database environment, and the second one which is available for manual failover. So the other interesting thing you can see here is that basically this Availability Group includes both of the databases that my application cares about. In this case our Product Catalog management system needs both the Products database and the Product Staging database. So I can set up an Availability Group that contains all of the databases that my application cares about and manage them as a unit which is very cool. You can see that right now the primary, which is where all the read and write activity is getting routed to, is this G7-03 server here. That was the same server, if you remember, that was showing up here in my report, so that’s where all my queries are getting routed.
A lot of people want the ability to not just use AlwaysOn for high availability, but they want to take advantage of some of the processing capacity that are available on these other secondary’s. So what we give you the ability to do is to route your read only workloads to what we call a Readable Secondary. And we do that in a very easy and transparent way. So let’s show you how that is actually done.
If I bail out of my web application here and pop into Visual Studio which is where our development environment is, you will see that I’ve got a solution here that includes my reports and my web application. So I’m going to actually go in here and pop open the data source for these reports and go and inspect that. So if I click on Edit you can see here’s the connection string that’s used to connect up to our AlwaysOn Availability Group listener. And if I click on my advanced properties here you will notice that we have this new property here called Application Intent. Using this I can basically right in my connection string say whether or not my application needs to do both reads and writes against my database, or whether it just needs to do reads only. In this case it’s only a report so this is a read only workload, right? So all I really have to do is just flip that, save out my connection string for my shared data source, and redeploy my project. Once those reports and data sources get redeployed, I can then go in and test to see whether or not my read only workload is getting re-routed. So let’s go ahead and fire up our web application again and test it out and see if we are going to use that readable secondary.
So here’s our trusty web application again, let’s go back into our safety stocking level report and see if it get’s routed to our read-only secondary. And indeed it did, our report is now getting serviced out of there. So what we’ve shown you is how you can use AlwaysOn not only to set up a great high availability and disaster recovery scenario, but also how to reduce your total cost of ownership by leveraging some of the processing capacity on those readable secondary replicas so that they are being used for more than just waiting for a failover event.
View Next Demo Transcript: A First Look At SQL Server 2012 (Part 2 - Data Quality Services) (SQL11UPD00-TSCRIPT-04)
Return to SQL Server 2012 Developer Training Kit BOM (en-US)