Configuring a SQL Server AlwaysOn High Availability Group
SQLShack
SQL Server training Español Configuring a SQL Server AlwaysOn High Availability Group
March 6, 2019 by Priyanka Devre In this article, we will learn the step by step configuration of SQL Server AlwaysOn High availability Group for two nodes. Once nodes are added to the cluster group, we will able to use the AlwaysOn feature in the SQL server. In the following steps, we will create a Service account to connect SQL server instances. The Service account will be used to connect the SQL instance from both the node servers. Create a Service Account
We will log in to the Domain controller and perform the following steps. Open Server Manager, Tools and Click Active Directory Users and Computers. In Active Directory Users and Computers window, Right-Click Users > New > User as shown in the following Fig. In this step, we will create a Service account username, Specify the service account login name and Click Next Specify a Password and tick the options for User cannot change password and Password never expires. We need to select Password never expires option because, if the password expired, the service account login would fail, and it will cause a problem to the high availability configuration. Click Finish to complete the service account creation process. Configure Administrator permission windows user and login for the service account
Once the Service account created on the Domain controller, we need to add service account as a member of the administrator account on both nodes. Connect to both node servers SQL1 and SQL2 and perform below steps Add Service account as an Administrator Create a Windows user for the Service account Create a Windows authentication login for a service account login in the SQL database instance Open Server manager and Select Tools and Computer Management In the Computer management window, Select Groups and Right-Click Administrators and Go to Properties Add User, Enter the Service account login name and Check names and Click OK Note – Once clicked on the Check Names, it may ask to enter username and password pop-up window. In the pop-up window, we need to register a domain controller username and password and make sure the location should select as a directory. In the following Fig. We will create the Service account as a windows user in Control Panel > User Account > Click on Manage User Accounts. Enter a Service account name and domain name and Click Next, Select Administrator and Finish Next, we will create a windows authentication login for the service account as shown in the following fig. Take remote desktop connection of SQL1 with the service account windows user, connect to the SQL Server database engine instance SQL1 with default windows authentication login as shown in the following fig. Enable AlwaysOn Feature
Once SQL Server instance is connected, we need to decide database which will take part in SQL Server AlwaysOn High Availability. In this demonstration, we will create a new database STUDENT, and then we will create a table and add a few records into it as shown in the following fig. Next, Expand SQL Server AlwaysOn High Availability option, we will get an error while expanding the AlwaysOn option The AlwaysOn feature must be enabled for the server instance ‘SQL1’ before you can create an availability group on this instance. To enable AlwaysOn, open the SQL Server Configuration Manager, select SQL Server Services, right-click the SQL Server instance name, select Properties, and use the AlwaysOn High Availability tab of the SQL Server Properties dialog. (ObjectExplorer) To enable AlwaysOn feature, Open SQL Server Configuration Manager, Right-click SQL Server instance and go to properties, Select AlwaysOn High Availability tab section and tick checkbox for Enable AlwaysOn AvailabilityGroups Note: – We also need to make sure nodes where we will enable AlwaysOn feature is part of Windows Failover Cluster. Once we enabled, Apply and Click OK. Restart the SQL Server services. As shown in the following fig. we will able to use SQL Server AlwaysOn High Availability feature. Right-click Availability Groups and Click New Availability Group. Configuring Availability Group
An AlwaysOn Availability Group contains a set of user databases; we need to select user databases which will be part of the Availability group. These databases called as an Availability database. Specify Availability Group name and click Next As shown in fig. we will use database STUDENT. To proceed further, we need to perform one essential pre-requisite step, a Full backup of the database. So, take a Full backup of STUDENT and click Next Next, Specify Replica step has four sections. Replica, Endpoint, Backup Preferences, and Listener Replica– Replica is a server. There are one primary replica and multiple replicas. In SQL server 2012, It supports up to 4 secondary replicas, while in SQL Server 2014, it supports up to 8 replicas. The primary replica is primary source server or production server. A secondary replica is a server which maintain a backup copy of the primary server availability database. On the Primary replica, it allows to perform Read and write operations while on the secondary replica only read operations. In this case, SQL1 is our primary replica and SQL2 is a secondary replica. So, we will add SQL2 as a secondary replica as shown in the following fig. Initial Role – It specifies the role of replica whether Primary or secondary. Automatic Failover – It failovers functional role from primary replica to secondary replica by an automated method with no data loss. Synchronous Commit – In the Synchronous-commit mode, failover can happen by either Automatic or manual way with no data loss. So, in this case, we will use synchronous commit with automatic failover. Once clicked Add Replica, connect to the server, Select SQL2 and Connect Once we add secondary server SQL2 as a secondary replica, the primary role of SQL2 is secondary. In the Endpoint section, it will show configured endpoint URL, port, endpoint name, and the service account name for SQL1 and SQL2 nodes. Backup Preferences – Backup preferences indicate the backup location of the availability group. It provides four backup options. Prefer Secondary – Automatic backup will occur on a secondary replica Secondary only – Automatic backup must occur on a secondary replica Primary – Automatic backup must occur on a primary replica Any Replica – Backup will occur on any replica On the secondary replica, we can only take a log backup, and Copy-only backup, Differential, and Full back up are not allowed on a secondary replica. While on the primary replica, we can perform Full, log, and Differential backups. Listener– Listener is a server through which nodes get to communicate with each other. The Listener has all information about availability group. We need to select Create an Availability group listener option and specify Name, Port and static IP as shown in the following fig. In Data Synchronization step, Select Full where it performs Full and Log backup and puts it into shared location path. Create a Shared location
Connect to node SQL1 and choose desired drive location and folder, Right-click folder and Go to Properties, Select Security and click Share. It will show a pop-up window to add a user to whom, share permission need to grant. Provide the service account name and Click Add; it will show pop-up window to check names, once service account name found in the domain name, click Share. In the Validation step, SQL Server performs validations against any required actions, and It will provide warning or error messages for missing required steps. It performed all validation steps successfully, and wizard operation completed as shown in the following fig. After successful completion, we will verify the database at secondary replica SQL2. As we can observe, Database STUDENT is in Synchronizing state, and it is in sync with the primary replica, and we can read data successfully. Table of contents
Domain Controller and Domain Client Node setup for AlwaysON High Availability iSCSI, iSCSI Initiator, Quorum Configuration and SQL Server Cluster Installation Configuring a SQL Server AlwaysOn High Availability Group Author Recent Posts Priyanka DevreI am From Bangalore, India. I work with SQL Server for Infosys in India. I work in High Availability AlwaysON, Disaster recovery, performance tuning, Back-up and restore, and Cloud infrastructure. Latest posts by Priyanka Devre (see all) Add new articles, drop the article, change the snapshot folder path and Data filter rows in SQL Server Replication - April 2, 2019 SQL Server replication: Configuring Snapshot and Transactional Replication - March 15, 2019 SQL Server replication configuration: Peer to Peer and Merge Replication - March 15, 2019 Related posts
SQL Server Always ON Availability Group Log Backup on Secondary Replicas Read Scale Availability Group in a clusterless availability group SQL Server Statistics in Always On Availability Groups Isolation levels behavior in SQL Server Always On Availability Groups Data synchronization in SQL Server Always On Availability Groups 110,898 Views Follow us
Popular
SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices Trending
SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples Solutions
Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server Categories and tips
►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ►Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ▼Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy