AOAG WindowsServerClusters v18.4.16
AOAG WindowsServerClusters v18.4.16
AOAG WindowsServerClusters v18.4.16
configuring Always
On Availability
Groups (Windows)
Information in this document, including URL and other Internet website references, is subject to change without
notice. Unless otherwise noted, the companies, organizations, products, domain names, email addresses, logos,
people, places, and events depicted in examples herein are fictitious. No association with any real company,
organization, product, domain name, email address, logo, person, place, or event is intended or should be inferred.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under
copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or
transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any
purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering
subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the
furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other
intellectual property.
Microsoft, Windows, Windows Server, PowerShell, and SQL Server are either registered trademarks or trademarks of
the Microsoft group of companies.
Introduction .............................................................................................................. 3
Objectives.................................................................................................................. 4
Overview of failover clustering and Always On Availability Groups ............ 6
Configuring the sample application .................................................................. 28
Executing a manual failover ................................................................................ 33
Monitoring availability groups ............................................................................ 35
Summary and additional information ............................................................... 39
Appendix ................................................................................................................. 40
• Three computers running Windows Server on which SQL Server is installed. These computers are
deployed to the back-end subnet and joined to the domain.
• One Windows Server 2016 domain controller. The domain controller has the following applications
installed:
o SQL Server Management Studio (SSMS) to connect to SQL Server.
o Visual Studio (VS) Code to modify application configuration.
o Node.js application to read data.
Note: It is not a best practice to run SSMS on a domain controller, or log on to a domain controller to modify
source code or administer a database. This configuration is for lab purposes only.
Domain
Controller
WIN-AGDC
AG Node AG Node AG Node
WIN-AGNODE01 WIN-AGNODE02 WIN-AGNODE03
SSMS
Node.js
SQL Server SQL Server SQL Server
MyExpenses
• Transact-SQL
2. Expand the AlwaysOn High Availability node and the Availability Groups node.
3. To launch the New Availability Group Wizard, right-click AlwaysOn High Availability and select New
Availability Group Wizard.
5. On the Specify Availability Group Options page, enter the name of the new availability group in the
Availability group name field. This name must be a valid SQL Server identifier that is unique on the
cluster and in your domain. The maximum length for an availability group name is 128 characters. We'll
use MyExpenseAG.
6. Next, specify the cluster type. Here we’ll choose WSFC. For details, see Specify Availability Group
Options Page.
Replicas Use this tab to specify each instance of SQL Server that will host a secondary
replica. Note that the server instance to which you are currently connected
must host the primary replica.
In this lab, we’ll be using our second and third nodes (WIN-AGNODE02 and
WIN-AGNODE03) as secondary replicas. To configure this, select Add
Replica, type WIN-AGNODE01, and click Connect. Follow the same steps to
add WIN-AGNODE02 and WIN-AGNODE03.
Listener Use this tab to create an availability group listener. By default, the wizard does
not create a listener. We’ll create a listener later in the lab.
• Full database and log backup. Select this option if your environment meets the requirements for
automatically starting initial data synchronization.
• Join only. If you have manually prepared secondary databases on the server instances that will
host the secondary replicas, you can select this option. The wizard will join the existing secondary
databases to the availability group.
• Skip initial data synchronization. Select this option if you want to perform your own database and
log backups of your primary databases. For more information, see Start Data Movement on an
Always On Secondary Database (SQL Server).
11. On the Summary page, review your choices for the new availability group. To make a change, select
Previous to return to the relevant page. After making the change, select Next to return to the
Summary page.
As an alternative to using the New Availability Group Wizard, you can use Transact-SQL or SQL Server
PowerShell cmdlets. For more information, see Create an Availability Group (Transact-SQL) or Create
an Availability Group (SQL Server PowerShell).
14. In the Object Explorer, expand AlwaysOn High Availability, and then expand Availability Groups.
You should now see the new availability group in this container. Right-click MyExpenseAg (Primary)
and select Show Dashboard.
4. Specify the name of the listener and port. Here we specified MyExpenseList for the Listener DNS
Name and 1433 for the Port. In the Network Mode list, select Static IP. Specify 10.0.2.125 as IP.
Select Add and specify the IP from the available IP range. Here we have used 10.0.2.125. Select OK.
It will take some time for the listener to be created.
5. Right-click the domain node in the tree (aoag.sqlserver.labs) and then select Reload.
7. Right-click the WIN-AGDC node in the tree and select Clear Cache.
5. You’ll need to update the database connection details for the app as well. Open
the ./server/config/server.config.js file and update the login password and port information accordingly.
User name: sa
Password: Pass@word1
Port: 1433
Change the value for host to the DNS name of the listener: MyExpenseList.
3. In a web browser, refresh the sample application page. Navigate to a couple pages to confirm that the
app is still operating.
• Fully capable System Center Operations Manager alerting and ticketing. These features provide
detailed knowledge that enables faster resolution of problems.
• A custom extension to Always On Health monitoring that uses Policy Based management
(PBM).
• Custom tasks that manage Always On availability groups from the System Center Operations
Manager console.
For more information, see System Center Monitoring Pack for SQL Server.
• Transact-SQL. Always On Availability Groups catalog and dynamic management views provide
information about your availability groups and their replicas, databases, listeners, and WSFC
cluster environment. For more information, see Monitor Availability Groups (Transact-SQL).
This Returns server property information about whether Always On Availability Groups is enabled
and, if so, whether it has started on the server instance.
• To monitor the WSFC cluster that hosts a local server instance that is enabled for Always On
Availability Groups, use the following view:
sys.dm_hadr_cluster
If the WSFC node that hosts an instance of SQL Server with Always On Availability Groups
enabled has WSFC quorum, sys.dm_hadr_cluster returns a row that exposes the cluster
name and information about the quorum. If the WSFC node has no quorum, no rows are
returned.
sys.availability_groups
This returns a row for each availability group for which the local instance of SQL Server hosts
an availability replica. Each row contains a cached copy of the availability group metadata.
You can find more information on Always On Availability Groups on Microsoft’s documentation site. And
download the latest version of SQL Server from the SQL Server downloads page at Microsoft.com
3. In the explorer panel, expand the config sub folder and select the file named db.config.js.
5. The default port in the sample db.config.js file expects TCP port 1433. In db.config.js, change this value
to 1433.
6. Return to the Node.js command prompt and enter node app.js.
7. You’ll be presented with 2 data load options. Choose option 1. The process will take a few seconds to
run. It will create a database named Expenses and populate the data needed to run the app.