AlwaysOn Availability Groups
AlwaysOn Availability Groups – How to setup AG between a clustered and standalone instance (Part 1)
In this article we are going to explore how to configure an Availability Group between a clustered instance and a standalone instance, showing, step-by-step, how to setup a possible Disaster Recovery environment.
Introduced on SQL Server 2012, the Availability Groups brought the expectation to be an improved version of the database mirroring, which will be discontinued soon. The AlwaysOn Availability Groups was improved on SQL Server 2014, giving the capability of have more replicas, better troubleshooting possibilities and improving its availability. Comparing the Availability Groups with the database mirroring, in a very high level, we gained the possibility of have a listener to dynamically redirect the connection to the current active instance and also the capability of distribute the read workload between readable replicas. However, only the primary replica is able to write.
If you search about “how to setup an AlwaysOn Availability Group” probably you are going to find some articles out there, but all of those talking about the typical setup, where the Availability Group is being done between two standalone instances. In this article I’m going to do something different, the idea here is configure the Availability Group between a clustered instance and a standalone one, this way we can explore the differences between the typical and the not so typical
This said, let me make an example of what is going to be our lab environment:
For this setup, I will be using SQL Server 2014, Enterprise edition, in a Windows Server 2012 R2 environment. However, this will also work in an environment with SQL Server 2012.
Requisites
In order to go ahead with this, we need to already have something done. Basically we will need a clustered instance installed in an AlwaysOn Failover Cluster, of course, and a standalone SQL Server instance installed in another server, which must also be part of the Windows Failover Cluster. Just a note here: the term AlwaysOn, is a brand used by Microsoft that is grouping two technologies: the Failover Cluster and the Availability Groups.
In order to make it work, we are going to use the famous AdventureWorks2014 sample database . This database will be initially placed in the clustered instance SQLSRVCLT01 and in order to successfully have this being synchronized to the W2012SRV03 instance, we need to assure that the same path used to store the database files exists on both instances, and preferable with disks with the same size …
In our case, the database AdventureWorks2014 is stored in the following paths:
- F:\SQL_DATA\AdventureWorks2014_Data.mdf
- G:\SQL_LOG\AdventureWorks2014_Log.ldf
As you can see in the image bellow, we have all the tree nodes added to the same cluster.
The clustered instance is only installed in the nodes W2012R2SRV01 and W2012R2SRV02, so it will never be active at W2012R2SRV03.
Let’s Start!
To begin, I’m going to show how to configure the basis of an Availability Group, with two readable replicas.
By the way, when we talk about Availability Groups, we are talking about database replicas. Even a main database is a replica, the “Primary Replica”. All the others are “Secondary Replicas”, it doesn’t matters if you have 2, 3 or 4 replicas…
First of all, we need to activate the AlwaysOn High Availability feature in the instances. In order to perform that, open the SQL Server Configuration manager and go to the “SQL Server Services Node”. Let’s start from the standalone instance.
Now, look for “SQL Server (<INSTANCENAME>)”, right-click it and go to “Properties”. In the properties window, choose the tab “AlwaysOn High Availability” and check the box “Enable AlwaysOn Availability Groups”.
Now you can click “Ok” and restart the instance. You can do this, right there, in the Configuration Manager.
Follow the same steps for the clustered instance. The catch here is that you need to perform this in the node where the instance is active.
As you can see, if you try to enable the feature from a passive node, the following message will be shown:
This way, perform the steps from a node where an instance is active.
Once more, restart an instance. As this is a clustered instance, do this from the Failover Cluster Manages, by taking the SQL Server service Offline and then Online again.
Yes! We are done with the first part!!!
Preparing the database
Now that our instance is ready to support Availability Groups, it’s time to take care of the database that we are going to join to the Availability Group.
As said in the beginning of this article, we are going to use the AdventureWorks2014 database. This database is already attached to the clustered instance. I choose this one to be the start point, but you can choose any other “replica”.
In order to make the database a real candidate to be placed into an Availability Group, we need to meet some prerequisites:
- A database must be in the FULL recovery mode
- A database should have at least one FULL backup
- A database needs to have at least one T-Log backup
With an intention to assure this, let’s check the database properties and change the Recovery Model from “Simple” to “Full”.
Now we need to perform one FULL backup of the database. I’m going to store this backup to use in another step in this article.
With the full backup done, we can perform the T-Log backup. Notice that I’m storing the backups in a fileshare on W2012R2SRV03. The reason is that I’m going to restore this database in the standalone instance running on that server.
We are doing this in a lab, so the environment is fully controlled by me, but in a real environment, you may have Backup Agent running continuously, and making transaction log backups periodically… Make sure to stop those backups during this configuration period, otherwise you won’t be able to synchronize the databases between the replicas.
With the FULL and transaction log backups done, it’s time to restore a database in the other instance, the future Secondary Replica. In our case, this will be the instance W2012R2SRV03. So let’s work in the restore of the FULL backup previously made.
Make sure to set the “Recovery State” to “RESTORE WITH NORECOVERY”.
After the completion of this restore, we can start the transaction log backup.
Again, make sure to select the option to RESTORE WITH NORECOVEY, as shown:
After those steps, we will have the following scenario, where the clustered instance has a database in the normal “online” state and the standalone instance has the database in the restore state.
Now we are ready to start creating the Availability Group!! So check the second part of this article in order to see the next steps.
In this article we introduced the basic configuration and requisites in order to have an Availability Group setup between a clustered instance and a standalone instance. In the continuation of this article we will show how to create the Availability Group itself,
AlwaysOn Availability Groups – How to setup AG between a clustered and standalone instance (Part 2)
In continuation to the part 1, on how to setup a SQL Server Availability Groups, let’s now check how to create the Availability Group itself and put the databases in sync.
For this purpose, we will be using the “New Availability Group” wizard. To access this option, expand the “AlwaysOn High Availability” node and right click the “Availability Groups” folder.
A new window will be presented, with a summary of in what the wizard will help us. You can see an example in the next image.
Click “Next” to proceed, and let’s start the actual configuration. In the first step we need to input an Availability Group name. This is just a name to help us identify a group, nothing related with the listener.
I will be using the name “AgHowTo” in order to demonstrate all the steps to follow. Fill the name, as desired, and click “Next”.
On this second step, we will be asked to select all the databases that we want to include in our Availability Group.
Notice that there’s a column “Status” where the message “Met the prerequisites” is being shown. To meet the prerequisites, the wizard means that our database is in the FULL recovery mode, and that we have a FULL backup (at least one, to start the chain), and a subsequent transaction log backup or a differential backup.
If the database is not meeting the basic requisites, just verify the referred points, and click in the “Refresh” button, in order to have an updated list of the available databases.
Select all the databases that you want to include on this Availability group, by picking the checkbox in the first column. Now click “Next”.
It’s time to add the replicas! Notice that a primary replica (the current instance) is already added. In order to add another replica, click the “Add Replica” button, perform the login, which can be by Integrated Security or SQL Server Login, and a new replica will be available in the “Replicas” list.
Notice that you can always include more than one replica. Currently, we have a limit of 8 replicas for SQL Server 2014 and a limit of 4 replicas for SQL Server 2012.
To proceed with this article, I added only one more replica, the standalone SQL Server. You need to take a decision of what will be the role of your replicas. You can choose to have readable, read-intent, or non-readable secondary. This option makes all the difference, when you are looking to have a load-balancing solution, a high availability solution or just a disaster recovery instance.
Notice that the synchronization mode is also very important, where you need to choose to have a Synchronous commit – you need to receive an ack message from the secondary replica in order to successfully complete the transaction – or an Asynchronous one, where the acknowledge message is not required.
In that same step, you can also set if a certain replica will be available for automatic failover. In case of this need, remember that you need to have this replica marked as “Synchronous Commit” in order to assure that no data is lost in the case of an unplanned failover.
To finalize this step, just as information, you can also add an Azure Replica, but this is something to talk on another occasion…
Click “next” to proceed.
Now it’s time to select the preference for the initial data synchronization. In our case we will pick the “Join only” option, because we already have a replica with the full and t-log backups restored and open to receive new data, as the database is “In Recovery” mode.
You have two more options, the first is the “Full” option. Going to this way, you will need to refer some fileshare where SQL Server will perform and save the required backups of the selected databases and will use it to restore each one in the added secondary node.
The last option is “Skip initial data synchronization”. Use this option if for any reason you don’t want to synchronize the databases now.
<quick note>
In order to go ahead and add a new database in the Availability Group, you will need to perform all the steps already described in the part 1 of this article, and from the secondary node right-click the database under the folder “Availability Databases” and choose the first option “Join to Availability Group” .
A new window will be shown, in order to confirm your intention. You just need to click “Ok” and, if the database in the secondary instance is ready, this should work fine and start synchronizing the respective secondary replica with the primary replica immediately.
</quick note>
We are almost there! So now SQL Server will run some validation tests, and if we are ok we can click on “Next” and go to summary.
On this step, we just need to review the options taken and advance, by clicking on “Finish”.
Now we have some action here SQL Server will execute all the steps in order to create the desired availability group and synchronize the replicas as we defined in the previous steps.
With the success of the Availability Group wizard execution, we will now have a brand new Availability Group in our instance, and all the included databases would be in sync. In order to verify the configuration, connect in all the replicas and check if everything is matching with what we have defined.
One more part of our configuration is done! With this, we are already able to say that we have an AG, and this is actually an enough step for some business requirements. But Availability Groups
AlwaysOn Availability Groups – How to setup AG between a clustered and standalone instance (Part 3)
We have already configured our Availability Group, now we need to make it flexible and accessible. It’s time to check on how to create a listener in order to make a single access point for you AG!
In continuation to our previous article, we are going to pass to another phase of this setup, as we already have our database in sync and safe, or highly available, depending of the chosen mode/architecture.
Notice that the Availability Groups is so flexible, that we can have multiple groups, in the same base cluster, with databases active on different instances. This way, aside of have a very good solution of high availability and disaster recovery, we will have a great option to balance the utilization of our hardware.
In the previous picture, you can see an example of a configuration involving 3 standalone instances, with 3 Availability Groups setup. If we balance the databases in a proper way, on each Availability Group (AG01, AG02 and AG03) we will be able to keep each Availability Group active in a dedicate node, having the others with a secondary role, which can be read-only, or just closed to connections and acting as a standby database. This situation is comparable to a cluster of two nodes with two clustered instances installed and active on opposite nodes, what people call “active-active” cluster (wrongly by the way).
With the scenario just described, the use of a listener is highly recommended, allowing us to failover the Availability Group without having to worry about change connection strings.
The Listener
So, now it’s time to configure the listener! But first, what is a listener and for what is this used for?
As we have the virtual AD Computer, in a SQL Server clustered instance, we will have the listener in the Availability Group in order to offer a single point of access to a group of databases. To create a listener, we will also need an IP and Computer in the AD, and don’t forget that the cluster computer should have full access to the SQL Server computer account.
In our case we will be using the following cluster, as shown in the part one:
So, the AD Computer name “W2012R2CLT01” should have full access tour listener “SQLSRVAG01”, associated to the VIP 190.100.100.51. Don’t forget to disable the Computer “SQLSRVAG01” after create, otherwise the listener creation is going to fail.
You may be asking: Is the listener required? The answer is “no”! You can survive without the listener, like we always survived when using the database mirroring. Its role is just redirect the connections to the right route. By default it will redirect us to the Primary Replica, where the database is both readable and writable.
In a scenario of a Disaster Recovery environment, where you have a production instance in a datacenter and the disaster recovery instance in another one, is common to see this approach, as the secondary databases aren’t even readable!
Setting up the Listener
So let’s now check the steps to add a listener to an Availability Group!
Assuming that you have the VIP, the AD Computer and that all prerequisites are met, let’s start!
Connect, using the SQL Server Management Studio, to the instance where the Primary Replica for your Availability Group is active. Expand your group as shown.
Now right-click on “Availability Group Listeners”, and choose the “Add Listener” option.
A new window will be opened. Fill the “Listener DNS Name” with the name of your AD Computer Name. In our case SQLSRVAG01. In the “Port” field, choose an adequate port, something that is not being used.
Now in the “Network Mode”, choose the “Static IP” option, and click in the “Add” button in the bottom right of the window.
Anther window will pop, and it’s time to set the IP of our listener.
Select the proper subnet, related to your IP and fill the IPv4 Address.
Now, click ok in this window, and in the remaining one. And this is what you will have:
In the failover cluster perspective, this is how it looks like:
Notice the dependence between the computer name and the virtual IP. As we are looking to the Failover Cluster Manager now, is a good time to say that, even being feasible to do changes from the Failover Cluster Manager, don’t do this! All the changes need to be made from the SQL Server side, or rephrasing, all the changes must be made from the SQL Server Management Studio! Otherwise you risk to break your configuration.
Testing the connectivity
Now that our listener is online and listening, we can check if it is working fine. First let’s see if the connectivity from the application server is opened, using the famous Telnet Client. In Windows, go to “Run” and write “cmd”, press enter.
Now, in the command line, write the following:
“telnet <your listener name> <listener port>”
As bellow:
If you execute the command and see the following (empty black screen), that means that you were able to connect!! So all good.
If your result is like the following, something is wrong:
In that case, check if you didn’t misspell something (the hostname or the port), if all looks good, you might check the Windows Firewall, if enabled, and add the exception for this IP/Port. Another try is use the listener IP, instead f the computer name. If the connection using the IP works, you need to check why the name resolution is not working. Talk with the Domain/DNS/System Administrators in order to troubleshoot that.
If all looks good on Windows point of view, and if the instance/listener are really only and should be replying, you need to check with the Networking team of your company, on order to have this unlocked. Always pas to them the following information:
- Source IP and hostname (from where you are connecting).
- Destination IP and hostname.
- Destination port.
In the second part of this article, we explained the basic concepts of an Availability Group listener, checked what are the prerequisites, how to actually create a listener using a computer name and a static virtual IP and finally how to test if our application server has connectivity to our newly created access point to the availability groups. Keep tuned, as this series is still not done!