No tags yet.
Before we begin:
While the gui/wizard for setting up Availability groups is super easy, I'm going to go through utilizing T-SQL. This gives us an "Under the hood" look at the way these are setup. I will provide the full script at the end of the post.
As an FYI, I will be using SQL 2017 Developer edition running on Windows Server 2016 in my virtual lab. (Running on Hyper-V).
Installing SQL Server:
The installation of SQL Server on each node is a basic Standalone installation. I see no need to go through that as part of this blog series. (Though I do suggest using an automated install!)
Post Install Steps:
We are going to have to enable the Always on feature. Go into the SQL Server Configuration Manager, right click the SQL Server service and select properties.
On the "AlwaysOn High Availability" tab, tick the check box to enable the feature. It will require a service restart. So go ahead and do that on all nodes involved so that you may proceed with the configuration.
Create Endpoints:
Next you basically have to create mirroring endpoints on each server. I used the default tcp port of 5022. Keep this in mind if you're utilizing a firewall in between these severs. You can also check on it by querying the sys.endpoints table.
Database(s) Pre-requisite:
The databases that you will have in your AG should be in FULL recovery mode (since AG's utilize the transaction log) and have had atleast one full backup performed before you can proceed with it. I will be using the Adventureworks2014 database for demo purposes.
Create the Availability Group
Notice how the endpoints are referenced. I also set the Availability Mode to Asynchronous_commit and the failover mode to manual for ease of setup. These settings can always be adjusted later on.
Join the secondary nodes:
On each node (or use SQLCMD mode) make it so that it is joined to the availability group.
Backup on primary and Restore on Secondary node(s) with NORECOVERY:
Join Databases to Availability Group:
Finally we can run the final command on each SECONDARY node to join the database to the specified AG.
And that's it. The full script is included at the bottom of this post. Read the comments so you know which node to execute the script blocks on (or alter the script with SQLCMD mode).
Stay tuned for future posts on the AlwaysOn series where I will discuss failover configurations and testing, monitoring and alerting. and how to add additional databases to an existing AG.