SQL High Availability and Disaster Recovery Using AlwaysOn

hi there welcome everybody this is a re-recording of the sequel server 2012 high availability and disaster recovery webinar my name is Jeff king I'm a sequel & bi platform architect working for Microsoft I have with me on the phone today a colleague Josh Robinson who is also a sequel and bi platform architect Josh I don't know if you just want to quickly introduce yourself yeah along with Jeff I cover our sequel customers not specifically on focus on the mid-atlantic whereas Jeff is focused on greater South East again we have the same role different territories okay thanks Josh so we're going to go over what the meeting objectives are today so we're going to take a look at obviously sequel Server 2012 and what always-on and high-end availability in disaster recovery means inside of sequel Server 2012 it's obviously changed quite a bit over the last ten years so we're going to also take a look at sort of some of the legacy architecture and the legacy capabilities that were part of older versions of this product you know everything from database mirroring log shipping replication some of those things and we're going to talk about how those have changed with sequel Server 2012 and really where the benefit and advantages are within the application as far as you know over the last 10 years and the changes that have been made up to this point and we're going to go through a demo that will actually create will stand up and and configure a 3-node a high availability cluster you will use sequel server always-on for that and then also as part of that josh is going to go through some really great slides on sample topology so you know taking your existing environments and what those look like and actually turn them into a new always-on sequel server 2012 enabled high availability disaster recovery solution so we've already been through introductions but I just wanted to point out that you know we've got our our email addresses Geoffrey King at microsoft.com and Josh Robinson Josh Robinson at Microsoft comm you know we both been consultants in our previous lives and have worked together actually prior to even coming to Microsoft on on different projects so you know we're not just going through the manuals and and regurgitating this information this is actually you know stuff that we've worked with we've been there and have gone through the pains of configuration as well so we hope to bring some of that insight you know when we have some of these these conversations with you and then just to quickly review our our gov sequel program so this is the first session in a series of three that we're running for the month of May you know and we're launching this gov dot sequel training program and the idea behind this is that you know we know that there aren't a ton of free resources and resources out there that you can connect with on it from a developer standpoint so we wanted to create a program that allowed you to have access to you know both Josh and myself and you know it's an opt-in program so we know we're not going to be sending you spam we're not going to be doing things that you know we shouldn't be doing but it's really about giving you the resources and access to the resources that you need on a on a day-to-day you know basis and then also you know coming up with some ideas around you know how how you use the product so you know where you get to a place where you've got real-world business problems and we can help walk you through solving some of those through some of these webcasts and training events and if you'd like to sign up please email me Jeffrey King at Microsoft comm and I'll make sure you're included so before we get too far into the the always-on conversation I did want to mention just some of the the high-level features of of sequel Server 2012 and you know I like to show this slide because you know back in the day when I was a consultant you know we were working with you know sequel 2004 word but when sequel Server 2008 came out I never really understood why we have the r2 or the second release of that Pro I never really quite got why they didn't actually just name it the next version and that of course you know I thought that until I actually saw the 2012 release and you know I figured out pretty quickly that there were just so many features that have been baked into the the new code base or the new product release that you know really started to make sense to me why they had the 2008 r2 versus 2012 and you know that across all dimensions of our product capabilities you know there's been some really big changes you know from high availability which we're going to talk about today to scalability and performance you know we've got new compression components that are actually built into the tool using some of our our technology that we had in Excel so the X velocity compression engine is now actually built into our code base from a sequel server engine perspective you know we've got some new security and manageability components you know that allow us to track users and and some of the you know events that occur in the system on a much more finite level so also you know we've had some additional BI components as well which are huge the addition of products like Power View or features like Power View I should say that are you know directly integrated inside of our office products as well as inside of SharePoint as well so I will go through each one of these but I did want to just show this slide to illustrate that you know we really do have a very you know diverse product now you know and compete very very closely with Oracle you know and really obviously the big difference between us and them now is no longer features but it's it's all about the cost and we're you know you get a lot of bang for your buck with sequel so let's talk a little bit about some of our legacy capabilities from a high availability and disaster recovery perspective now you know these have been around for many years and have sort of laid the foundation of sequel server and you know I've got sort of the the four primary types that that come as part of you know the product and have been developed over the last few years obviously all of these are available within our sequel 2008 product but the first is failover clustering our second is mirroring you know the third is replication and then finally a log shipping and each one of these have their own set of unique features you know server level fail overs for clustering where you've got two servers you know with with one interconnected stand or sorry the two servers are interconnected and sharing a database that served up through a stand you know so sort of a less complex you know way to failover at a server level you know then you go into sort of the more granular approach with mirroring where you can actually have individual databases that failover between servers with mirroring replication and log shipping a couple of these scenarios only support automatic failover you know one of them are actually sorry two of them support server level object failover as well so really the point of this slide is not to go through each one of these but to really show you that there we really up to this point there really wasn't an all-encompassing that brought all of these capabilities together so let's quickly look at the high availability disaster recovery architecture and what this means and this is you know fairly basic stuff but I wanted to just go over this and what we're going to do is we're going to bring this all together with Josh's slides which will show you you know how this all plays together in the new world and then you always on world so with sequel server failover clustering as I mentioned just a few seconds ago and you've got two servers you know on top of those servers you have a Windows cluster one of the servers acts as an active node and one is passive and you know you have your sequel server instance that is shared sort of across these and the server B is ready to pick up the load if server a fails now the underlying storage mechanism is is always going to be ass and in this case you know this allows them to basically use the same disk structure you know on failover so it's an active passive environment one of the things that historically you know has been part of sequel failover clustering is that it's always been very difficult to set up Geographic failover you know and the primary reason being that you know windows failover clusters you know built out across subnets just just don't nicely in terms of you know having one single failover cluster and some of those things have changed in the new world which we'll review just a few minutes so my next slide covers sequel database mirroring and log shipping and this is where you know we've got sort of it's a mortar more of a granular level than failover instances you know in this case you've got a sequel server principle server you chosen a database that you want to have mirrored and copied over to a mirror server you know that we have a couple of different capabilities in terms of ways that that copy occurs the underlying mechanism that drives everything is of course log reduce so you know everything that happens on the primary server is then rewritten on the secondary server and depending on whether that synchronous or asynchronous you know that sort of determines at what schedule those log rewrites happen if you've got a synchronous environment and that synchronous means that they're basically going to be no data loss between the two servers you know the the log reduce have to occur on the secondary server before they're committed on the first server and so that ensures that if you know the first server goes offline that the second server will have an exact copy of the data at all times now of course there is some latency you know that you have to worry about with this type of backup process but you know for the most part it's sort of the core foundation of what mirroring and you know is in the sequel server world um in addition to that if you've got database mirroring in place you know there really are some disadvantages a little bit in terms of you know you need to have a sequel server witness server if you want to have automatic failover so automatic failover just out of the box does not come you know you don't just turn on mirroring and then automatic failover automatically occurs it's you really need to set up that third server so if you're looking at you know more hardware to manage you've got additional licenses that you have to buy to make that all work together and in this view of the world and in this HADR configuration we've added log ship as well so you've got your you know your your mirror set up locally for your high availability and then in your disaster recovery center you've got your your warm standby which is using log shipping and log shipping is very similar to mirroring as well the only difference is is that you know you're basically you define the schedule at which the log gets captured sent over to the disaster recovery center and then is rewritten into into the invar into the actual database environment okay so let's talk a little bit now about availability groups and what's changed so the biggest thing I think and the nicest feature that comes wrapped into this or the nicest benefit I guess that's wrapped into this is that along with you know bringing together some of these components so you recall that first you know slide that I showed where we're comparing all of the different features of each each you know backup solution that we have essentially by bringing these all together using something called an availability group we've actually increased failover by two times so we've reduced downtime by half and you know this is a sort of a stepping stone for us in terms of how we've moved into the new world of of high availability so we're talking about you know greater uptime based on these types of configurations you know what we can do now is we can take a set of databases that define our Tier one mission-critical workload so if you've got a big server that's running a whole bunch of different databases you can just define a specific group of databases and fail those over to a secondary server so you don't have to fail all of the databases just that one particular group so that could be your tier one mission critical workloads only you know and then the benefit there is that you don't have to have a server that there's going to support an entire instance so instead of filling everything over you just failure select databases over and you can fail them over to a lower powered server also there's no longer a need for sand storage so you can actually use direct-attached storage you know with your your environments now which you know when you think about the cost hardware that really impacts you know how much you're spending and what your IT budgets are so just to review that the final slide I'm going to show you with with features here you know what I wanted to do is just show you that our new always-on solution really does bring together all of the components across the dimensions that we have you know in our older legacy architectures for backup and recovery and you can see here in our always-on category we've got server level protection database level protection automatic failover we support server level objects so all of your security and all of all of the underlying stored procedures and views and all those things will actually failover as well we also support readable secondaries so if you know you've got a disaster recovery solution in place obviously you're not typically going to be using that for anything because it's your dr environment you know we now have the ability to start using those licenses from a readable secondaries perspective and really have very granular control over those secondaries and what we're doing you know within them from a rewrite perspective and then of course we also support multiple secondaries now so it's not just about having one we can have multiple replicas you know spread out in different locations so to review I'm not going to spend too much time on this because we're going to go into a lot of this in detail during our demo um but you know some of the changes that we've made and some of the really nice features are we have a total of up to four secondaries per environment now we're two of those secondaries can be synchronous and the other two would obviously be asynchronous you know from an integration perspective we've also got the automatic failover capability that I mentioned earlier we're right we're using something called a virtual network name and this is a you know basically gets written into our configuration component that gets written into the your DNS system so you would have a you know basically a name that your application would connect two that would have an IP address associated with it and that name would sit in in front of your failover cluster in your you know you're always on solution and depending on or sorry regardless of what happens to the underlying servers you know whether if one fails and it fails over to the secondary server the application to the application that's going to be seamless and it's going to be a seamless failover because it's pointing to that virtual network name and therefore you know automatically the DNS will just reroute and pick up the new connection at the at the secondary server so we're going to take a look at how this all works during the demo also as part of this we have some new dashboards that we can look at you know that give us a very nice picture and very clear view of our environment health and we can drill into some of the details behind you know performance of each server we can also initialize things like failover and some of the you know if you're patching servers and you want to do some of the manual components and all be done through this dashboard that we've got now as I mentioned you know this is all about having active secondaries as well so from an efficiency perspective and utilization of your secondary hardware you know we have the ability to support readable secondaries you know and those can be running your reporting those could be running backups you know and what the design here is it's really designed to reduce workload on your primary server so if you've got cubes that you need processing when you've got a data warehouse you're running some ETL to build you know this can all be done on your secondary servers one of the nice things to from a configuration perspective and how the environment fails over you know traditionally when we talk about you know failover and sequel server you know especially with failover cluster instances we've been dealing with quorum and you know where we've got the servers in a cluster are all voting on this on the health of the environment you know now we actually have a new failover policy that sits inside of the always-on application and allows us to actually configure the failure condition level and this doesn't need to be you know all of these things together it can be any one of these things we sort of design the failover rules so it's almost like a set of business rules that define when the system is going to failover so it can be any one of these things it can be a cute an accumulation of you know one three and five on this list if we want it you know so we're we've got a sequel server process we've got you know an orphan spin lock and then a deadlock you know and if all of those things are true then we failover so really gives us a lot more finite control over what defines failover for us also also as I mentioned we have a health dashboard that allows us to go go in and actually determine the health of each server in our cluster and we can actually you'll notice that there are links associated with each one of those servers so you can drill into that and do some additional things behind the scenes and what's not showing in this is that which we'll see in just a few minutes in our demo is we have the capability to actually failover manually and start you know a different basically configuration tasks within this view on the on the entire cluster so to quickly review how an actual failover would work I've got a diagram here that shows how a configuration or just a very basic configuration would look like and this is actually very similar to what we're going to build in our demo today but it has all of the different components and the most important one I think is is the virtual network name is what I want to draw attention to here in terms of the changes from what we used to do to what we're doing now so you can see on we have server a which is our primary database server so our transactional workloads are writing to server a server B is our secondary our replicas in our configuration server C is a third replica that's acting as a secondary and you can see within server a we've got our availability group that's built on top of our database and that's tech ag-1 and then it has a virtual network name attached to it and actually pointing all of the traffic essentially to this server to manage its writes so so let's imagine that the server fails so we have this nice X that shows up and all of a sudden we can't write to this database anymore so what's going to happen is automatically the environment is going to recognize that that server a is offline and server B needs to pick up the load and so the tech he1 availability group will kick over a server B and there's a mirror of that particular database on server B and then the virtual network name will then point to server B and so from an application perspective it has been totally transparent so the application hasn't doesn't know that anything's actually changed other than there might have been a few failed writes while it was trying to redo its connection or redo those rights so the server B kick picks it up so ruby is of course in a synchronous state so we won't have lost any data when this occurs and you know now we're ready to get going again so let's talk a little bit about the readable secondaries and you know what these are actually doing as well and how how how the rolls can actually switch between these and this is really the point of this slide is you know one of the nice things about always-on is that you know where you've got a primary and secondary notes the notes themselves can actually flip rolls very very easily so in this case we've got a primary server and a secondary server and we've got some database log synchronization that's happening between the two servers and we're running our reports off of instance B so the primary is capturing all of our transactional workloads you know we've got some reports so a secondary workload you know that's not stressing our primary is now running off our our secondary server so let's imagine that we have a crash on our primary now regardless of the fact that we're running workload or a read-only workload off of our secondary server what's going to happen from a configuration perspective is that that secondary server is now going to become our primary so it's going to failover you can see that the instance B has now become our primary and then basically going to reverse roles where the database log synchronization will be happening from instance B over to instance a you can see that instance a has now picked up the the reporting capability that we had you know originally with instance a so they've just totally reversed roles and this is really nice in that you know when the environments come back online you figured out what's going on you've solved the problem you know you bring that that primary server back up again doesn't necessarily need to be failed over again to get it back to its original state we can just move forward in this manner so I'm going to hand off to Josh here in just a second but I wanted to quickly just review what a traditional high availability disaster recovery architecture would look like and this is actually indicative of a customer meeting that I recently had where we've actually deployed always on and Josh is going to talk about the different configurations that we would support to replace this in just a second but this is essentially you know an environment that uses sequel failover cluster instances and we've got a primary data center that has two nodes and then we've got a secondary data center that has our D our environment there's four servers across our entire cluster two of those servers have you know I'm sorry there are two windows failover clusters that have to support this you recall that you know we don't support cross subnet windows failover clustering in you know the older versions and so you know this has always been a bit of a maintenance headache in terms of you know keeping two clusters alive you know for servers we've you know got multiple sequel server licenses in this place in this case and then sand replication that exists between our shared storage you know to keep those synchronized as well so let's take a look at how this changes in the new world of always-on Josh yeah thanks Jeff so I'm just going to share my screen and you should see a slide come up and so Jeff has been introducing always-on from a high level perspective and what I'm going to actually show are some of the reference architectures now I'll be coming at these from a high level but if you'd like to get implementation details on low level you know technical details all of these are available in I'm just going to put this pin in various white papers that have been accomplished if you could reshare Josh I'm actually not seeing the the yeah of course see just kind of quickly stop sharing and share my screen again and then just let me know when you see it Jeff okay well and for my side it looks like it's coming across I don't know okay that's great I've got it good yeah and again so a lot of the technical implementation details the actual steps you would take some of the technical um nuances are all available in a series of white papers that have been published by our sequel cat or customer advisory team they'll just be approaching these from a high level and the three reference architectures that I'm going to look at will exclusively be using are always on technologies and those are once again always on failover clustering and always on availability groups so the first reference architecture exclusively uses um sequel server fail of our cluster instances now this is something that's been around for years and many of our customers have successfully implemented builder request for instances there are some interesting updates in sequel 2012 for this this architecture however and just to lay out what it looks like from a high level in our example we've got four nodes to at the primary site to at the remote or the disaster recovery site and as you can see there's a single Windows Server failover cluster that all four of these notes belong to in the past to do geo diverse or multi-site on Windows sorry sequel server failover cluster instances you would have had to do a stretch VLAN so with sequel 2012 you no longer need to do that so that's that's one of the things that makes this this architecture in this topology much more accessible in sequel 2012 another thing that is is a key to this architecture is storage replication so basically this is provided by the storage vendor so if you have a stand then you would use stand storage replication and this maintains a copy of the databases at each of the sites whether it's a primary or disaster recovery disaster recovery or even if you have a secondary disaster recovery site even though there are separate storage volumes at each site sequel server looks at this like a shared storage solution it's not aware of the fact that behind the scenes storage replication is taking place so all of these nodes are seeing the exact same thing as far as sequel server is concerned um one second okay and just some important attributes of this solution so the unit of failover for both high availability and disaster recovery is the entire sequel instance so one second I'm going to page down here there we go okay now the whole slides should be visible so the entire sequel server for instance is the unit of failover so that includes sequel agent jobs that would include logins that would include all databases not just you know some databases so this is very much an all-or-nothing proposition the database is required to be in oops it doesn't matter whether the database is in full recovery or simple recovery mode so if you're looking at setting up disaster recovery and high availability for a data warehouse which may use simple recovery mode then this solution might be the right one to choose for that some of the other solutions require full recovery mode and may be less desirable for like a data warehouse or scenario like that and another interesting thing that that's almost something to be aware of that that may not make this desirable for all situations is that the DR copy of the data is not readable so as you look at these four servers active passive passive passive the only server that you can read and write data from and to is node one so notes two three and four are you know waiting for that failover waiting for that time when they might be ated so so that is a possible downside to this this architecture that will address in in one of our further reference architectures um Jeff are the slides coming across clearly so I just want to confirm that ok good they are hey Josh yeah I'm seeing them fine so the next architecture that I'd like to talk about moving away from sequel server failover cluster instances exclusively this one will focus exclusively on availability groups for both high availability and disaster recovery so just to set up the servers you can see we have three nodes node one and two are at the primary site and node 3 is at a remote for disaster recovery site so the you know one of the highlights of this architecture is that we just page down here oh I should mention that all all three notes would need to belong to the same Windows Server failover cluster that is one of the underlying requirements for both failover clustering instances and availability groups and then just to paint the rest of this slide on the screen you can see we have a synchronous replica setup on node 2 and an asynchronous replicas setup on node 3 and just to talk about this architecture for a second one of the highlights is that it's based on a non shared storage um so unlike the prior instance where you do have shared storage even when it's at the remote site sequel server you know is basically fooled into thinking that it's shared storage although behind the scenes ambiance and replication this is surely 100% based on a non shared storage solution and the reason for that is that each sequel server in the topology has its own copy of the data and it doesn't need to access sheriff bridge so in this architecture you can see that we've got multiple secondaries in this case notes 2 and in the first as I mentioned is configured to be a synchronous replica and it's located in the same data center as a primary the second replica is asynchronous and as I mentioned it's in our remote data center so already you can see some differences from database mirroring unlike the database mirroring which only allowed a single replica with always-on availability groups you can configure up to four replicas in addition to your primary on any of those replicas can be configured to allow read-only workloads and Jeff is going to demonstrate that and that there's great benefit there because you don't even have to take them offline to read from them like some of the other high availability and disaster recovery technologies you can read from them while they're online while they're being updated um the types of read activities that you might you know use them for would be to take backups to load a data warehouse solution or even to do online transactional reporting against your production system without affecting your primary you know node one database if you were to look at a solution like this before sequel 2012 you probably would have implemented database mirroring for the local high availability and logs shipping for the remote disaster recovery and so you know you would have had to use multiple technologies so with sequel 2012 you can leverage just a single technology availability groups and then you get all the benefits that I mentioned multiple secondaries read-only secondaries um you know and the ability for automatic failover um this slide shows just a simple scenario with two data centers um I think it's important for me to note that there are a lot of variations to this architecture that you could implement you could have multiple data centers as I said you can have up to five replicas including the primary the the unit of bill over for this is the database or a group of databases so unlike failover cluster instances in which you fail over the entire sequel server instance you do have more granular control in this case although you would have to implement something outside of availability groups to bring jobs and logins and those types of things over objects that exist at a server level would not be failed over as part of the database not failover process any databases that are set up to have a replica do need to be in full recovery mode so that's just another thing to note here the last reference architecture that I want to highlight today it starts out looking a lot like the failover cluster instance architecture with primary and secondary site nodes 1 2 3 & 4 as you can see a shared storage solution at each of the sites however that's where things change you do still have a Windows Server cluster however that is used for the availability groups in this case in terms of the sequel server failover cluster and since you'll have two of those one at each of the data centers and then what happens is you'll a availability groups on top of those two sequel server failover cluster in census and rather than using a third party storage replication solution you can actually leverage availability groups to send the data from the primary site to the disaster recovery site now this is interesting in a couple of ways so one of those ways is that you don't have to get involved in a you know high end storage replication solution you can use out-of-the-box equal server technology that the will demonstrate to set up the the synchronization of the replicas another thing that you basically unlock with this apology is that secondary that dr site is now a readable replica of the primary so whereas before you had notes 2 3 & 4 that were passive in this case you now have no three which is an active read-only copy of your primary database um so you have a lot more value there this line um so again some of the benefits of this this solution would be that the unit of failover for the local high availability is the sequel server instance the unit of failover for the broader multi-site disaster recovery solution would be the database so if you were to fail from the primary site to the disaster recovery site you would be failing the databases over you need to have a second tier you know the solution in place to bring sequel agent jobs and logins and other server level objects so there is a little bit more configuration that would need to take place outside of of just the availability groups between the two sites and we have white papers that cover all of that the databases would need to be in full recovery mode and as i mentioned that dr site now becomes a potential active the only replica that you can perform work on and like the other solutions um there are a lot of variations of this topology you can have multiple data centers i'm showing just two here for simplicity sake you can have multiple replicas you can have more than two nodes in each of the failover cluster instances if you if you wanted to have three nodes your primary site you can absolutely do that not all of the replicas need to be failover cluster in census so maybe the primary site you would have a sequel server failover cluster and maybe at the dr site you would have a smaller bill over close for instance but then maybe you would have a third site that would just be a single sequel server it would not be a failure request for instance so there's a lot of flexibility there and also you can have multiple availability groups you may have some databases that don't need to have disaster recovery you take backups and and maybe you don't need full disaster recovery between sites so you can choose not to include all databases in the in the site to site zest recovery availability your solution as I mentioned all of these are available there's white papers that cover these in great detail and actually cover the implementation steps should you wish to do those in a lab or in your production environment okay Jeff back to you all right thanks Josh was a great overview so what we're going to do now is we're going to go ahead and get get into the actual demo itself so I'm just going to going to spin up my my virtual environment here and let me go ahead and share that with you guys my Josh oh if you want to just let me know when you can see that it looks like it's still loading Jeff okay and it's just gonna cross the my screen okay thank you alright so the first thing we're going to review is is how to build an hadr three node cluster and you can see here I've got a demo lab environment that you actually go onto if you if you go ahead and Bing just Microsoft virtual labs you'll get a link that will take you to where this this lab can be launched from and this is actually a real virtual environment that we've got for servers we've got a sequel server domain controller we've got sequel to and sequels or starts equal one sequel to and sequel 3 and you can see we can be a lot of different things that we can do each one of these servers so what we're going to do is we're like I said we're going to build a three node cluster that is going to take a base and create an availability group around that database and using that database and on top of that we're going to build a virtual network name that we're going to actually write into DNS which is why we've got our domain controller as part of our demo environment today and then we're going to actually launch an OLTP application that will will fail over and we'll see what happens in the underlying architecture as that failover occurs so the first thing we have to do in this environment is ensure that we've actually got our cluster built and the the core foundation and principle behind sequel server always on is built on top of Windows cluster so we're going to launch our server manager and this is going to allow us to take a look at the actual cluster health and make sure that all the nodes are online and active in this environment so select tools and then we'll go to our failover cluster manager take a second to load the snap in here here we go now we can see through our menus if we take a look at our nodes we have a healthy environment with sequel 1 sequel to and sequel 3 and they're all online and in up and join to our cluster so full green across the board now I'm a closed cluster manager and what we can do is now we can actually go into sequel server and start to build our availability or are always on availability group I'm going to go back to my search screen and we'll just quickly launch sequel server management studio so everything that we're building here is actually built in the context of sequel server management studio it's our one stop shop tool and has been for many years for for you know any kind of database administration of course we've extended some of this now into sequel server data tools as well which comes as part of sequel server 2012 I won't get into the details but you know we're definitely expanding our footprint a little in terms of functionality and we'll go ahead and connect to sequel one so although we've got three different environments and four if you include our domain controller we're actually going to do all of our work via sequel one today and it's just going to go out and automatically connect and create the things that it needs to create on those secondary and third servers so we'll go ahead and connect to sequel one now we can review the databases that we've got in this environment so we can see we've got a couple of copies of adventureworks and always on lab DB some contoso stuff up here so we've got a report server looks like we've got reporting services deployed on this in this particular instance so what we want to do is we actually want to create an availability group for our always-on lab DB and so to do that I'm going to expand if we go down in the menu or the folder tree here we can see we've got and always on a high availability folder so if I expand this see we've got something called availability groups and I can right click on availability groups and I see something called a new availability group wizard so I'm going to select that and now we can start to configure our availability group I'll click Next and we're going to name this availability group always on lab AG so the o is on lab availability group and now we can select the databases that we actually want to include in our availability group and you'll notice that some are grayed out and some are selectable we can actually include multiple so different from something like database mirroring where we're selecting a single database to Mirror we can actually bring multiples together in groups which is what's nice about the always-on capability now notice that some of these are actually grayed out and what this means is that there's essentially a set of rules that each one of these databases needs to adhere to to be considered for inclusion in an availability group and one of those rules is that we need to have the recovery mode configure to full and the reason for that is that a sequel server backup and backs up and restores within the context of the recovery model for each one of these databases so recovery models essentially are designed to control the behavior of the transaction log and that's associated with each database and of course the transaction log is what's being used to manage the synchronous and asynchronous rights and so you know the recovery model is tracking you know how our transaction is logged you know will it allow backup you know what kind of restore options are available and so similar to database mirroring availability groups require the full recovery mode and you'll notice here that are always on lab dB does not have the full recovery mode enabled so we need to go ahead and actually turn that on so let's just minimize our view here and I can come over and we'll right click on our always on the database lab database here and we'll say new query and I'm going to create this or change the the configuration of the always-on lab DB just using straight command-line so just bear with me while I type this so we're going to say use master altered database and we're going to select our database name lab DB and then we'll set our recovery context full with no weight and so the last thing I'm writing here is basically we're configuring a backup and the thing that has to happen very similar to replication is the very first time that this goes out and actually creates the availability group it's going to create a backup of this database on each server that we've you know designated be part of that availability group so we're going to use our network share on sequel one for this we'll name it always on lab DB WK okay I think this looks good just double-check my syntax okay I think we're looking good so I'm going to go ahead and execute this command you can see it's gone ahead and created our backup for us and then it's also made our configuration change so we're good to go there so now I'm going to go back over to my availability group wizard and just quickly refresh you can see now that always-on lab DB actually now meets our prerequisites so I can select this we can move forward so this is where we actually define our replicas so you know we've got our database group defined and now we're ready to actually start adding replicas to this environment so I've got a obviously two other nodes that I want to include in this environment and to do that I'm going to select my add replicas and then we do sequel one we're going to change this to sequel to and click connect we go sequel three click connect okay great so now we've got all three of our replicas in place and we can see that we've got some parameters that we can define for each one of these you recall that we can have two synchronous replicas per veil or per environment so where we're going to select one will make that synchronous we'll select two and we'll make that and we'll set it up to automatically fail over between sequel 1 is equal to in addition to that I'm going to go over to sequel 3 and whoops just refocus my window here I've got the ability to actually define a readable secondary and so we can choose which one of our servers will actually accept read requests and in this case we'll make number 3 our third server as our our server that manages basically our offline or our read-only reporting so we'll click Next and now we get to choose our data synchronization process so you'll recall that I mentioned that there needs to be a copy of each database on every server across all of our replicas before we start and what this is doing is is actually going to automatically go out and create that copy for us and it's just asking us what what preference we would like would we like to do a full backup you know it could be that we've already backed up and restored our database environment on those servers you know outside of this this wizard so we have the ability to you know join only or actually even skip this process so it's going to go through a set of of checks and give us a you know stop light report of where we can or where we need to make some changes and the one thing that I'm noticing here so we've got a check for compatibility of the database file location so what this means is that essentially it's gone out and looked for a directory on our secondary and third server that it can store the backup in and that directory just doesn't exist so we need to quickly just go over to those servers and create that directory so we're going to go ahead do that and to do that I'm just going to take a shortcut here and we'll click our run menu and we'll go to sequel to see dollar sign pull up the root of C here and we'll create a new folder poised on lamp I'm sorry I'm stew always on and then within always on will create lab database great so that takes care of sequel to copy this and we're just going to back out we'll do the same on sequel three and I'm just going to paste that same directory and copied it into sequel 3 so now we should have all of the appropriate directories in place so let's rerun our validation and see here we've got all green except for this one warning and what this is is basically warning us of is that we don't have a listener set up so this is the virtual network name I was talking about before that allows us to actually you know basically point an application to it and have the automatic failover occur seamlessly and we're going to set that up in just a second so I'm not going to worry about that so we'll click Next and finish and now it's going to go through and actually create our always-on availability group for us we can expand our details node here and actually see what's occurring in real time you know the fact that it's it's actually creating our backups and you know writing those to those particular servers and then making the configuration changes that it needs to alright great so the wizard completed successfully so now we can close this and if I just quickly refresh expand our database tree again and see here now that always-on lab DB is showing a synchronized state so this just means that it's now part of that availability group and that it's ready to be highly available now one thing that we haven't done yet is set up our our virtual network name and our actual what we call the availability group listener and this is what's going to allow us to create that seamless or transparent connection to our availability group from our applications perspective so if I expand now my always-on high availability folder structure I can see here there's the name of the always-on lab AG database group and then if I expand this I've got a set of options where one of these is called the availability group listeners and see there's nothing actually in there if I expect wanted to expand this further I could see the replicas that I've got in my cluster sequel one two and three you know which databases we actually are making highly available and there's nothing under our availability group listener so that's what we're going to set up so I'm going to right-click and just select add listener and our listener name will be aw lab LSN so short for listener will create our default port for sequel 1433 and i'm actually I don't have DHCP configured in my domain controller so we're going to go ahead and just add an ipv4 a straight IP address here nine one nine two one six eight dot 125 double check this okay let's click okay so this is actually going out up to our domain controller and it's creating our listener DNS name and associating it with our IP address let's give this a couple more seconds and while this is happening I'm going to launch command line and I just want to make sure that our listener has been configured so I'm just going to quickly ping it you can see here I've I've pained aw lab LSN and we're getting a reply from the IP address that we created so looks like it's set up okay so let's now take a look at manually failing this over we're going to obviously set up our OLTP application in just a minute but I wanted to show you how easy it is to manage and maintain this high availability group using our dashboard so you can see we've got our availability group here we right-click it again and I've got an option to show a dashboard this is going to bring up a screen of basically all of our on our nodes in our availability group we've got sequel one sequel to and sequel 3 we can see the role that they're playing obviously we defined a synchronous between our synchronous right between singles sequel 1 and sequel - and sequel 3 is acting as our report server that's in an asynchronous mode so you can see it's constantly in a synchronizing state it's never quite caught up with sequel one or our primary database you can see about some groups that we can create here now what I wanted to draw your attention to is the wizard that we have in the right-hand corner here and we've got an option to actually start failover wizard and this is where we get into some of the nuances as it relates to ease of use for for failing over so we can start this nice wizard to fail one of the server's over and sequel one is currently our prior primary replica this is actually giving us some some data around each one of these or the you know the current state of each one of these replicas and we take a look at sequel 2 we can see that the failover readiness is we've got a nice green checkmark there's no data loss and this is obviously of course because we've got synchronous you know failover configuration setup between this so we're not going to have any data loss it's equal 3 it's giving us the ability to failover to it we could select it if we wanted but it's saying that there could be a potential data loss based on the fact that it's an asynchronous connection so in this case I'm going to fail over to sequel to click Next of course we'll have to connect to it and provide credentials to do this click Next again and then we'll finish this up and so what this is doing if we look in the background it's a we should hopefully see this change state sometimes the refresh rates are slightly different but it's actually failing over to our sequel 2 and there we have it and you can see sequel 1 is showing us offline this will come back up in just a second as the refresh you know maybe I can actually do a manual refresh here yeah there we go so sequel 1 is now back online its role has changed it is now our secondary server and you can see it's it's gone down we've dropped down to 1 because it's not the basically one one server view in this in this dashboard because sequel 1 is no longer our primary it's acting as a secondary alright so that's in a nutshell you know how you would fail over a server in our environment so let's now launch a an actual application that is going to write to our our server or our cluster and then let's initiate a failover so from here I'm just going to minimize my sequel server management studio for a second and I've got an application that has been loaded onto this server and into this environment called just an OLTP application and launch this so we can see what's going on alright so um I can see underneath the window here we've got provider data source we have the ability to control what this particular application connects to you from a sequel server perspective and what this will allow us to do is where it's going to just do some random writes into our our database structure and so to enable this the first thing that I want to show you is that if we change our data source here so we know that based on our recent failover that's equal to is now our primary database and so if I change the connection you see we're connected to if we take a look at the properties up on the top here connected to always-on lab DB on sequel 2 and so I can actually begin some read and write so we can see we've got a certain number of inserts per second that are happening and how many rows are actually being written you'll notice that we're still using sequel 2 so what I want to do now is actually show you that in addition is equal to we also have our listener configured and so let's actually change the connection string to a W lab LSN which was the listener that we had created and select the Change button and you'll notice up at the top here that even though we've changed our data source connection here and your named it a w lab LSN we're still connected to always-on lab on sequel 2 and if I begin my writes again my rights are occurring and now this is seamless the actual appliqué tour the database itself or the database server sorry itself is totally transparent to the application at this point so let's fail this over now and see what happens so recall that we've got automatic failover between sequel 1 and sequel 2 and if I launch management studio again and let's select sequel 1 and we're going to write new query we're actually going to do them a syntax based failover so we're going to say alter availability group and then always on lab G is the name of our availability group then they're going to say failover let me just focus this so that we can see the application in the background all the screen refresh here ok so let's go ahead and kick off this failover okay so you'll notice I've executed this and let's refocus our OLTP application you can see here we've got some connection failure retries that are occurring so in the background our failover has occurred and already in a few short seconds it's now picked up our our secondary node so now we've got we're connected to if we take a look at our string up here we're connected to always-on lab dB and that's actually showing connection now to sequel one so you know with very little effort we failed this over the connection picked up very very quickly and is rolling along again so that's really illustrates the speed and ease at which you know we can initialize some of these these fail overs so I'm going to close this now let's some just navigate back over to our dashboard and launch this again and you can see we our dashboard has updated itself again and we've got sequel one is designated as our primary server again so just completely reversed roles all right so just to get into the the final piece of the demo here so that really is the full demonstration of creating an availability group building it you know failing it over manually failing and over by code you know I could have easily gone in and just you know taken the sequel server service and you know actually stopped that you know so it would have been you know many different ways we can manage these types of failure points and of course obviously if the machine itself fails you know based on a set of business rules that you define that that would enable failover as well um so let's as a final demonstration here I'm just going to minimize that and let's close our LTP application let's launch a reporting application and I want to show you some of the capability around around reporting so once again we have the ability to control the connection string so this is a basic reporting application remember that we setup sequel three as our our read only database and so let's go and connect to sequel two and see what happens okay so you'll notice that it's basically giving us a warning and saying the target database always on lab DB is participating in an availability group and is not currently accessible for queries so yeah just pointing out the fact that we have not in fact enabled that particular database for read-only so I can quickly make that change and I could easily you know designate sequel 3 in here if I wanted to but I wanted to show you how to change it in the wizard and it's as easy as right-clicking on our availability group selecting properties taking a look for sequel to there we have it and let's define this as a readable secondary yes and click OK it's going to go through that change now let's launch our our reporting application again and select change you can see now we're actually connected to our always-on lab DB on sequel 2 we can kick this off and it's going to actually start returning query or returning rows and records from that particular database so that is really the end of our demo today so we've shown you how to build a three node availability group make some configuration changes to both the databases and the environments initialize failover in multiple ways and then also you know connect an OLTP application and see how the failover occurs and impacts the environment as well as launched a reporting application and showed you how the read-only workload would work in this type of configuration so I'm going to just quickly jump back over to my slides here so I'm going to go back to my powerpoint presentation and I just want to bring up this slide again just to remind you give that a couple seconds to hopefully show up so essentially I just wanted to review our gazebo training program so this was the first as I mentioned in a few different webinars that we've got we've got a few more coming up on the next couple of weeks on tabular bi as well as on Big Data and Josh will be running that that particular webinar but essentially you know just this is an opt-in program you guys are welcome to attend this is going to be posted on YouTube this video will be so anybody that sees us on YouTube and is interested in learning more as well as and is in the government sector in the southeast or anywhere else actually in the US for that matter please let me know is feel free to email me we'll be conducting web-based training events I'll also be emailing on occasion we've got product patches and releases and things like that so so thank you very much for for joining us today that concludes my presentation and well we'll talk to you soon

Channel: Jeff King

Views: 32,548

Rating: 4.8130841 out of 5

Keywords: SQL Server, SQL 2012, alwaysOn, Disaster Recovery, High Availability, Jeff King, Josh Robinson, Business Intelligence, Government, SQL, Technology

Id: Jfmjxdh1_Ds

Length: 68min 31sec (4111 seconds)

Published: Tue May 21 2013