SQL – Creating a SQL Server AlwaysOn on Amazon EC2, Part 4

In Part 2 we have configured a Windows Failover Cluster and in Part 3 we have installed and prepared a SQL Server core services. Now we are ready to configure an AlwaysOn Availability Group.

This is how our network looks at the moment: AG01To create an Availability Group you run a “New Availability Group Wizard…”:

AG2Give Availability Group a name of your taste:

AG3Select a database you want to be part of the AlwaysOn group from the list. I have only “Test” DB:

AG4By the way, if you have not done a Full backup you would get a message saying “Full backup is required”.

Next is the interesting part. First we need to add the cluster nodes that will be serving this Availability Group. Select “Add Replica…”:

AG5When nodes are added, we also need to configure Replicas according to our needs [picture above]:
Initial Role – this is how I want my roles to be initiated after this wizard is completed. Later roles can be changed.
Automatic Failover – by selecting this I’m making sure that in case of a Primary node failure the other will take the role automatically.
Synchronous Commit – by selecting this I’m making synchronization type “Synchronous Commit”.
Readable Secondary – I want to be able to perform database read operations from the instance that is performing a Secondary role, therefore “Yes” is specified

Next tab is called “Endpoints”, and it’s going to be populated automatically:

AG6A server instance uses an endpoint to listen for the AlwaysOn Availability Groups messages sent from availability replicas hosted by other server instances. In other words, an endpoint is used to communicate between the primary and the secondary replicas of the availability group. I think of them as an internal intercommunication. You might want to change the port number if the suggested are not good because of the firewall or some other considerations. By the way, this is not something new, mirroring also uses same type of endpoints.

Next tab is called “Backup Preferences”:

Prefer Secondary – Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default option.
Secondary only – Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.
Primary – Specifies that the backups should always occur on the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.
Any Replica – Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

Next tab is called “Listener” and this is the most important component in my opinion:

AG8If the Endpoints are used to intercommunicate between cluster nodes, Listener on the other hand allows clients to automatically find the primary node, a node that is currently serving the Availability Group read/write database replica. Even more, if you specify a special parameter you can say that you actual want to access a database in a secondary replica. Listener is also a virtual network name (VNN) and it will be created as an AD object.

We can use port 1433 again because it will be attached to the listener dedicated IP addresses. So, no collision with the SQL Server main IP/port.

As you can see from the screenshot above, having a Listener is not a mandatory thing. Why? Because database users can still connect to the Primary database replica [read/write replica] directly by specifying a name of a server that is holding it. But if the Primary replica moves to another node then your users must change the connection address on their end manually. With the Listener all this redirection magic happens automatically.

By the way, remember IP addresses that I have allowed in Amazon on the secondary NICs:

AG71These are the ones I’m using for the Availability Group Listener.

Next step in the Wizard is asking us how we want to synchronize our Availability Group nodes. Remember a share that we have created in the Part 3 – “\\WIN2012DC\Replica”: AG9And that is it. If everything was prepared correctly we should see this nice list of successful results:

AG10Lets look at the Availability Group in SSMS:

AG12Availability Group Listener must have been created in AD:

AG13Also check your DNS server. You must find an A record for every Listener IP [we are having a multisubnet configuration]:

AG14Allright, in the next Part we will test our newly created AlwaysOn Availability Group.


Leave a Comment here

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s