Sql Server 2014 AlwaysOn

hello this is John Aitken latinum solution in this video I'm going to demonstrate sequel server is always on technology in our virtual environment we have two sequel server setup that will be joined together in an always-on access group by the name of a g1 those network environment also has in domain controller and we do have some client software so that we can simulate the always-on technology will be showing three main points in this demonstration one is the ability to setup a contained database so that security can move with the database and not the server two is the ability to build and always-on access group and an always-on listener so that your clients can connect to that access group and three would be to set up a read-only replica so that read-only applications would be able to read from the secondary databases and not the primary so let's get started we're going to begin by building a contained database Microsoft introduced this technology in 2012 a contained database allows a security account to be assigned to a database but not to the server instance the advantage of this is that database can be moved from server to server without having to also move the log on accounts that go along with that database so let's get started on sequel 1 right now we currently have an adventure works LT 2012 database we are going to configure that to be a contained database and then we are going to create a user for that contained database to start this process I first had to run a configuration on the server so out-of-the-box sequel server instances do not allow containment so we needed to do our SP configured contain data based authentication set that equal to 1 and then we needed to run a reconfigure so that it would allow those changes to take effect next we're going to go into our adventure works database so when I right click venture works go down to properties I'm going to go to options and under containment type I'm going to choose a partially contained database I'm also going to ensure that the compatibility level is in sequel 2014 compatibility mode now our database is ready for partially contained accounts to create a security account associated with the database instead of normally going to security on the server instance we are now going to go just into the security section on the database we're going to right click on users we're going to add a new user and you'll see the first option that comes up here is sequel user with password this will allow the password to be stored with the database rather than the server instance you still can also choose sequel super with logon to attach to logon or Windows user when you create a Windows user if you want to do a containment you need to leave the logon name blank so I've created my user account I've assigned them to a schema and I'm also going to assign them to the dbo administration role right now what you will notice is that user account now exists under my database but if I look under security of the instance that user account is not found if I go over to my application I will show you that my connection string looks very similar I'm trying to connect to sequel one the adventureworks catalog user IDs Sam and then the password that's being provided nothing else is unique about the connection for a contained database the only thing that you have to have is the catalog has to be the same as the database containment account is if we go ahead and run our client now you'll see that we were able to connect and access our data the advantage of our contained database is that really the ability to move that database so if I go into sequel one and I'm going to detach that database I'm now going to use my operating system to copy the files from sequel 1 over to the sequel to data directory so they're gone here they're now over here and then we're going to go into sequel 2 and we are going to attach that database again security is still defined in the database user not on the server instance itself I can change my connection string so now point to sequel 2 and you'll see my application runs again so the advantage of this is that one user account can be moved from database to database server without having to map logins without having orphaned user accounts within your database well this is nice we still don't want that capability of having to go in and change our connection string so that's where always-on technology really plays a big role here so we're going to go ahead and set up an always-on availability group I'm going to start by moving my database back to my original sequel server 1 I want that to be my main primary database location I'm going to recopy that data filed back over to my original server I want to ensure that it was removed from the secondary server and then I'm going to go in and reattach that to my original location for always-on to work correctly I'm also going to go ahead and make a new backup of this database I need to back this database up into a shared location that all servers on my network will be able to access now we're ready to set up an always-on availability group so if I go down to my always on high availability and I'm gonna go ahead and run the new availability group wizard it first asked me for the name of my group so we're gonna call that a g1 and then it asked me for which database do I want to be part of this group the nice thing about always-on is you can actually have multiple databases a part of the same availability group so if you've got an application that relies on two or three databases all of those databases can be moved with the availability group I don't have to move them one at a time next I'm going to choose my replicas so I'm going to allow automatic fell over with synchronous commits and I will allow these replicas to be read-only we'll talk about the read-only functionality in just a minute I'm going to add in my second replicas in 2014 you can have up to eight replicas servers and I'm going to set the exact same settings for my secondary replicas you'll also notice in 2014 you can add Windows Azure as a replica server I'm going to choose the directory location of my backup and it's gonna check to make sure that everything is set up and ready to go okay our validation has ran and you see that everything is successful you notice there is a warning on the listener and that is okay when you first set up always on if you do not have an initial listener you're gonna get a warning we're gonna go back in and set that up here in just a second here we get a nice little summary of what it's going to do you can't script this out if you don't want to run this right to second you want to run this at a later time and then we go ahead and we can finish and it is gonna actually create are always on availability group tells me everything is successful it closed and now knows a couple of things we'll see that we have our Valley Billy group showing we noticed that on sequel one it is the primary group if we go down to sequel - we will see that availability group available also but we'll see that it's a secondary group you know if the venture works is now on sequel - also showing a restoring state but if i refresh that it should already be synchronized and we're ready to go so at this point in time i need a listener so even though I've got the availability group up and running I still don't have a listener to allow client connections so we can go back over to our availability group we're gonna see our availability group listeners and I'm going to go ahead and click and say add listener we're gonna give the listener a name this will be the Machine name so I'm gonna go ahead and call it AG one also but this can be any name that you choose that clients would connect to I'm gonna choose the standard 1433 sequel port and then I'm gonna use a static IP address and you'll notice on the subnet it will allow me to land a listener group to any subnet that is managed these are managed subnets also through the windows clustering service I had okay now and now I have a listener available for my clients to connect to you'll see that if I try to ping any g1 now it's going to answer just like a computer would answer on our network I can now go into my client application and I can change my data source location to a g1 go ahead and run my client and you notice my connection works successfully the nice thing about always-on if a error occurs or if I want to move the primary to the secondary I can initiate a fell over so I'm going to go ahead and launch a manual fell over and now my sequel one is my secondary my sequel 2 is my primary if I go back to my application go ahead and click to rerun my data you'll see that I'm still accessing my data you might have noticed a slight pause there so as it was switching servers the application did have to rebuild its connection from sequel a sequel to but no errors and our application still runs just fine so I'm going to go ahead and close that application out I'm going to go ahead and move my database back over to sequel 1 now when I say move I'm just moving really the right capability both databases do exist on each server it's just the only one server which is the primary is able to accept write requests ok our next step is to implement a read-only replica when we set up our availability group we allow both databases to be readable secondaries and they're both set up with what we call a read intent only and what that means is you have three options you have no reading 10 only and yes if the database is set to no it will when it's a secondary replicas will not allow any connections into the database it's basically unavailable for anything when it is set to yes it will allow all connections into the secondary database regardless if they are for read write or read only but it will error out if a write attempt occurs read and 10 only will only allow connections to be made that are actually marked as a read-only connection so we're going to go ahead and leave that setting set in our though that'll agree the next thing we have to do is set up availability rowdy so to do this we're going to alter our availability group and specifying a read-only rallying address and this address is going to be identical to the connection address for that actual server so when I'm on sequel one I'm going to show my connection to sequel one on sequel to the connection to sequel to I'm then going to go into the each the availability group and I'm going to build the actual route so if sequel one is the primary it's going to read only route to sequel to the sequel to is the primary it's going to read only route to sequel one if you had more than two servers in your replication group you could actually add additional servers into this routing now let's see the application in action if I go look at client 1 I'm gonna leave the connection string the same here so by not specifying and intent the application assumes that my intent is to read right when I run client woman all of its access is going to be against sequel 1 and we can see that by going into profiler I've got a profile setup on sequel 1 I'll go ahead and run that re-query my data and you'll see all the execution took place some sequel 1 that is because again this application has a read write intent if I go into client 2 which is the same application the only difference now is in my connection string I have specified an application attempt of read only when I go ahead and run application to it you'll see the access of data but you notice that the select statement is running on sequel to let's show that one more time if I go to one that select statement gets implemented on sequel 1 if I go to 2 that select statement gets implemented on sequel to so my applications based on their intent are routing to either the primary a replica server this is a really nice way where you can offload read-only applications such as reports internet applications that don't allow data entry to that secondary server because always-on is kept in sync all the time the route and the reporting that you're seeing is real it's real data real time data it's just offloading that setting sequel does do a good job of implementing temporary statistics on that always read-only replica so that you will get the best performance out of that I hope this demonstration was useful to you and look for future demonstrations from Jonathan Antin their solution

Channel: John Ecken

Views: 20,416

Keywords: Server (Film Character), Microsoft SQL Server (Software), SQL (Programming Language), AlwaysOn

Id: QJSo23eu0T4

Length: 18min 46sec (1126 seconds)

Published: Sun Jul 20 2014