SQL11UPD01-TSCRIPT-02
This wiki is a transcript of a previously recorded video.
Related content assets:
Welcome to SQL Server 2012 AlwaysOn. This demo will show how to configure SQL Server 2012 Availability Groups. My name is Jonathan Kehayias – I’m a Microsoft SQL Server MVP, Microsoft Certified Master for SQL Server 2008, as well as a Principal Consultant for SQLskills.
Flipping over to our EMU-SQL1 VM inside of our environment, which is the first node of three inside our Windows Server Failover Cluster for our Availability Group.
What we’re going to do to start of our demos for AlwaysOn, we’re going to create a new login. We’re going to name it DWUser and we’re going to give it a strong password. And we’re going to map that user to our AdventureWorks data warehouse database and there is an issue the release-candidate version of this UI that for some reason, if you watch back on the video it will show that it was a DB Owner in the checkbox when we created but it actually isn’t when it actually pushes the script down to SQL, so we have to come back in and change this. I believe this has been fixed for the RTM release of Management Studio, but that’s just an additional step that we’re going to have to do.
One of the pre-requisites for our Availability Group is that we have to have to a Windows Server Failover Cluster built across our nodes that are going to participate inside of the Availability Group as replicas.
So here with have the EMU-AG failover cluster that’s been built, and it’s across out EMU-SQL1, 2, and 3 nodes. There are no services or applications inside of the cluster currently and there is no shared storage because we’re not building a SQL failover cluster – we’re building a Windows failover cluster to support Availability Groups.
Now we also have a pre-requisite for each of the instances – we need to go into the Server Configuration Manager and open the properties for the database engine instance and we need to enable AlwaysOn Availability Groups using the AlwaysOn High Availability tab inside of the SQL Server Properties here. So this has been done of each of my nodes, but you need to make sure that you do it on each of your systems. There is a PowerShell script that can allow you to do this remotely to multiple systems from a single system if you’re setting up a complex environment or an environment with multiple servers.
To enable or to create our Availability Group, the first thing we’re going to do is go into our New Availability Group wizard. On the introduction page we’ll just click next. We’re going to name the Availability Group EMU-AG1 and our Select Database page will list all of the databases that are inside of the currently connected server that we’re creating the Availability Group on. It will also give us a status associated with those databases – whether they meet the pre-requisites for being in an Availability Group or not. If they don’t, it will tell you why.
For example, if you haven’t taken a full backup of the database yet, it won’t meet the pre-requisites to be able to participate in the Availability Group until you take the first full backup. The same goes for if you have auto-close enabled – that will prevent it. What you can do if you have something that fails the status check – you can jump back over to Management Studio really fast, make the property change for the database, and if you come back into the Availability Group wizard, you can click refresh – that will open up – if you click refresh down here at the bottom, that will recheck the statuses for the databases and allow you to progress through the UI once it meets the pre-requisites.
So we’re going to select the AdventureWorks data warehouse database and our config database for our data warehouse because these two need to fail over between the replicas together to maintain application functionality in our environment.
When we get over to the Specify Replicas, we’re going to add a replica and we’re going to connect to SQL2 and we’re going to specify that SQL1 and 2 are both automatic failover which automatically enables them for synchronous commit inside of the environment. We could set up a Listener here, we could change backup preferences and we could set our readable secondary options but for the purposes of this demo and the future demos we‘re going to leave those alone just to their default and we’re going to click next.
Now the great about thing about AlwaysOn Availability Groups and the wizard, is that you get the option for it to perform full automated data synchronization in the environment to setup the replicas by taking backups of the primary to a network share that’s accessible to all of the replicas in the environment. And in this environment we’re going to back up to EMU-SQL1\Backup, which is a network path that all of the replicas can talk to. You wouldn’t typically want this to be on one of your replicas – you would want this to be a network-attached storage area or a UNC path to a file server possibly or a backup location inside of your environment.
When we click next, what it’s going to do is it does a validation and it checks all the configuration options associated with what we’ve done. It checks our shared network location, makes sure that all the instances can talk to it and access it they way that they need to. It checks for free disk space on each of the instances to make sure that we’re not pushing a database over an instance that doesn’t have space for it.
And if there was an issue you could go correct the issue, rerun validation, and once you’ve got a valid environment you could click next and we get a summary page that tells us everything about all the selections we’ve made along the way in the wizard and it also provides the option to be able to script out our Availability Group configuration so that we could actually run. It generates a SQLCMD mode script that can against all of the instances or replicas that have been configured as a part of our Availability Group setup.
If you generate that script and we can take a look at this, it gives you the warning that this script must be run in SQL CMD mode. And the way that you would do that would be to go to Query and set SQL CMD mode and then it’s going to connect to the EMU-SQL1 server, it’s going to create our Listener endpoint, it’s going to start the endpoint and grant connect to the endpoint for our server accounts. And then it’s going to do the same thing for EMU-SQL2 and then what it’s going to do is it’s going to start up the AlwaysOn health session inside Extended Events which is going to provide monitoring for our Availability Group once it’s been formed, and it’s going to do that on each of the replicas in our environment.
It’s going to create our Availability Group in the environment, it’s going to join EMU-SQL2 to that Availability Group, then it’s going to do the backups of our databases and the restores of our databases and then it will configure the databases inside of our Availability Group.
Now if we go back to our wizard, and we just click finish, it’s actually going to create and form our Availability Group and it gives us a status along the way while it’s doing things. So it’s configured the endpoints, it’s created the event session or started the event session.
One thing to notice here is it says ‘validating the quorum vote configuration’. Now the reason this throws a warning and it’s just a warning, it’s not a major problem – and if you click on it you’ll get a link to a Books Online reference that allows you to configure the voters inside of your environment. The reason we get this warning is that we have three nodes inside of our Windows Server Failover Cluster but only two of them have been configured for our Availability Group and because of that we have a node that is a voter insider of the quorum configuration that actually isn’t participating in our Availability Group and that could have a failover or availability concern for the environment and it raises the warning. Right now we’re not going to pay a whole lot of attention to that because we’re going to fix this by adding the third replica into the environment as well.
If we click close and we can close our SQL CMD script, now that we have our Availability Group formed if go look we now have a cluster resource group for our Availability Group inside of the Failover Cluster Manager. The only resource is actually the Availability Group itself.
So what we can do is we can go to our Availability Replicas and we can add a replica and when we click the Add Replica is will open up and we can click next. First thing we have to do is we have to connect to our existing replicas. So you can do that with Connect All or the connect button up here – if you have multiple the Connect All tends to be a little easier.
And on the Specify Replicas page we can click Add Replica and we’re going to connect to EMU-SQL3 and we are going to specify that this is a synchronous commit replica. Now we can only have two automatic failovers but we can up to three synchronous commit. And once we’ve specified it as a synchronous commit replica, we can click next. We get the same options for the full data synchronization and it runs the same Availability Group validations associated with the addition of this new replica. And we have the option to script this out as well, but if we click finish, and more details, it will configure our endpoints. And one thing to note here is because we’ve added our third replica into the environment we no longer have the warning associated with our cluster quorum and majority of voters here.
And once this completes… right now it’s synchronizing in the environment… and now we have our secondary online.
So with that, we’ve completed the configuration of our Availability Group inside of the environment and we are ready to progress with the next demo inside the courseware.Vie
Return to SQL Server 2012 Developer Training Kit BOM (en-US)