There are many different ways that you can configure SQL for high availability, mirroring, failover cluster using shared disks, AlwaysOn, log shipping, replication. This guide describes how to configure an AlwaysON Availability Groups in SQL Server 2014.
To configure AlwaysOn Availability Groups you install at least two standalone SQL servers, both SQL servers need to have a “Failover Cluster” feature installed. There is no shared storage or heartbeat network but both SQL servers have to be added to the failover cluster.
For a two node SQL cluster a quorum is required or the cluster will go offline if one of the nodes goes offline. The way it works is if a node can’t reach the quorum it will take itself offline. The recommended set up is to have SQL1, SQL2 and quorum disk/share in different geographical location for high availability. Once AlwaysOn is configured it creates a Listener IP/port where applications can connect to, the failover cluster name can also be used by apps to establish a connection and provide high availability. AlwaysOn can fail over a group of databases automatically, this is an advantage over mirroring where it can only be configured per one database at a time.
SQL Always on Availability Group
========================
-Requires SQL Enterprise License
-No more shared disks or heartbeat network, db can be stored on local storage
-Servers must be in the same domain as both SQL servers use the same service account (SQL 2016 no longer requires for both servers to be in same domain)
-Configure all drive letters exactly the same across all SQL servers (E: for Data, F: for logs)
-During installation wizard both SQL servers will be installed as standalone “New SQL Server stand-alone installation…”
-Always on Creates a Listener IP/port, all apps will connect to this IP/port
Lab (3 Server 2o12 VM’s, 1 CPU, 2GB Ram)
====================================
Add the following DNS entries on the Domain Controller
dc.mylab.local – 192.168.11.200 – DC VM
server1.mylab.local – 192.168.11.201 – SQL1 VM
server2.mylab.local – 192.168.11.202 -SQL2 VM
SQLCluster.mylab.local – 192.168.11.203 – SQL Cluster IP
MyListener.mylab.local – 192.168.11.204 – AlwaysOn creates a listener IP/port that applications can connect to instead of the SQL Cluster IP.
Download SQL 2014 R2 Eval
http://www.microsoft.com/download/en/details.aspx?id=29066
Failover Cluster
1. Install the “Failover Cluster” feature on both of the SQL servers (server1 and server2)
2. Once both are installed, on server1 click Administrative Tools > Failover Cluster Manager, Create Cluster
3. During the Create cluster wizard add both SQL servers to the cluster.
4. Assign a cluster name and provide an IP for the cluster.
5. We need to configure quorum or the cluster will go offline if one of the SQL nodes goes down.
6. Create a share on DC and assign read/write permissions to it for the following computer accounts on NTFS level and share level, SQLCluster, Server1, Server2
7. Right-Click Cluster > More actions > Configure Cluster Quorum Settings > Select the quorum witness > Configure a file share witness
8. Now on the failover cluster manager page you can see the summary and the File Share Witness
Server1 SQL Installation
1. Start the installer
2. Choose “New SQL server stand-alone installation…”
3. Install SQL features All “Database Engine Services” and “Management Tools – Complete”
4. Configure a service account for SQL Services
5. In Database Engine Configuration add current user as administrator
6. Adjust “Data Directories” tab if you like (E: for data F: for logs).
7. Open “SQL Server Configuration Manager” right click on the SQL Service > Properties > AlwaysOn High Availability tab, enable.
8. Restart SQL Server service
Server2 SQL Installation
1. Start the installer and configure a standalone SQL server, exactly same steps as server1
2. We are creating Always On Availability groups do not choose “New SQL Server failover cluster installation”
3. Open “SQL Server Configuration Manager” right click on the SQL Service > Properties > AlwaysOn High Availability tab, enable.
4. Restart SQL Server service
Configuring SQL Always On Availability Groups
1. On server1 log into SQL Management Studio
1. Create a database called “Test”, also create a full backup of the database. Full backup is necessary or the next step will fail as AlwaysOn does a pre-req check.
3. Connect with SQL Management studio on server1 righ-click on AlwaysON High Availability > New Availability Group Wizard.
4. Choose the databas that you would like to add to the group
4. Name the group, now click on “Add Replica” and add the second SQL server, place checkmark beside SQL1 & SQl2 for automatic failover, click yes for readable
5. Now click on Listener TAB, Create an availability group listener, Network Mode static and provide DNS name for the listener, port 1433. Normally this would cause a port conflict as SQL already listens on port 1433. AlwaysOn Listener binds itself onto the SQL service and listens on the same port.
6. Add a network share that both servers can access
7. Finish the wizard
8. Right-click on MySQLGroup and choose “Show Dashboard”
Testing if failover is working
========================
1. Create an ODBC connection from DC to mylistener.mylab.local, we can see from the AlwaysOn dashboard Server1 is the primary server. You can also point it to the SQLCluster which works as well.
2. Force power off server1 to simulate a failure, you can see we lost two pings during the failover and server2 has started accepting mylistener.mylab.local requests.
3. The ODBC connection is still working even though server1 is down, mylistener is correctly forwarding to server2 for high availability
4. Dashboard after bringing the VM back up,server2 is now the primary
