MS SQL Backend can work with the MS SQL cluster. For creating the cluster using AlwaysOn technology. MS SQL Server Standard Edition supported only Basic Availability Groups:
Limit of two replicas.
No read access on secondary replica.
No backups on secondary replica.
No integrity checks on secondary replicas.
Support for one availability database.
Figure 1. AlwaysOn Availability Group
Requirements
To create MSSQL Server cluster require:
Windows Server 2016 and above.
MS SQL Server 2019 Standard Edition
Enable Always On availability group feature
For creation Always On availability group you need create failover cluster (see Create a failover cluster).
To enable Always On Availability Groups using SQL Server Configuration Manager:
Connect to the Windows Server Failover Cluster (WSFC) node that hosts the SQL Server instance where you want to enable Always On Availability Groups.
On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and click SQL Server Configuration Manager.
In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (“Instance Name”), where "Instance Name" is the name of a local server instance for which you
want to enable Always On Availability Groups, and click Properties.
Select the Always On High Availability tab.
Verify that Windows failover cluster name field contains the name of the local failover cluster. If this field is blank, this server instance currently does not support Always On
availability groups. Either the local computer is not a cluster node, the WSFC cluster has been shut down, or this edition of SQL Server that does not support Always On availability
groups.
Select the Enable Always On Availability Groups check box, and click OK (see figure below).
Figure 2. Enable Always On Availability Groups
SQL Server Configuration Manager saves your change. Then, you must manually restart the SQL
Server service. When the SQL Server service restarts, Always On will be enabled.
Requires membership in the sysadmin fixed server role and either “CREATE AVAILABILITY GROUP” server permission, “ALTER ANY AVAILABILITY” GROUP permission, or “CONTROL SERVER”
permission.
Also requires “CONTROL ON ENDPOINT” permission if you want to allow Availability Group Wizard to manage the database mirroring endpoint.
Databases in an availability group must be in full recovery model. The database needs to have a current full backup and transaction log backup. These backup files are not used for automatic
seeding, but they are required before including the database in an availability group. For details on how to make a full backup and transaction log backup, see Create a Full Database
Backup and Back Up a Transaction Log.
Logins don’t automatically sync across replicas. If the logins don’t sync, the application won’t connect to a secondary, which results in login failures. You can sync logins like this:
Run the following sql script on the primary replica:
SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+ CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, '+ N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';' FROM
master.sys.server_principals AS sp INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid] WHERE sp.name='LoginName';
where 'LoginName' - is the login for synchronization.
As a result of executing this script, a script will be obtained to recreate the login on the sec-ondary replica. This script will look like this:
CREATE LOGIN [LoginName] WITH
PASSWORD=0x0200FA4BA9433858753F8E66FA327B58ECEE19D0DC8BF8CD7285284E2EBF9E07E0307E0BEC3CADEB5ECC88FDF3FB31124FF365D003AF4514E656F13A64C0F1CF0A997E35ACE3 HASHED,
SID=0x5D8A73138DF8C54BB27C822AED041FDF;
where 'LoginName' - is the login for synchronization.
Run the resulting script on all secondary replicas.
Note: NOTE: If the login already exists, then it must be deleted.
Follow these steps to create an availability group:
In Object Explorer, connect to the server instance that hosts the primary replica.
Expand the Always On High Availability node and the Availability Groups node.
To launch the New Availability Group Wizard, select the New Availability Group Wizard command.
The first time you run this wizard, an Introduction page appears.
Figure 3. Create a new availability group
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 as a whole.
The maximum length for an availability group name is 128 characters. Next, specify the cluster type. The possible cluster types depend on the SQL Server version and operating
system. Choose WSFC.
Figure 4. Specify availability group options
On the Select Databases page, the grid lists user databases on the connected server instance that are eligible to become the availability databases. Select database to participate
in the new availability group. These database will initially be the initial primary database.
Figure 5. Select databases for the availability group
On the Specify Replicas page, specify and configure primary and secondary replicas for the new availability group. This page contains four tabs.
Replicas Tab 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.
Option name
Description
Server Instance
Displays the name of the server instance that will host the availability replica.
Automatic Failover
Determines failover mode. Select "Automatic Failover option" for each replica.
Synchronous Commit
The availability mode is a replica property that determines whether a given availability replica can run in synchronous-commit mode. For "Automatic Failover"
option used "Synchronous-commit" mode.
Readable Secondary Role
It determines the read availability of the secondary replica. For "Automatic Failover" option and "MSSQL Standard Edition" read access is not allowed.
Figure 6. Specify replicas
b. Endpoints Tab Use this tab to verify any existing database mirroring endpoints and also, if this endpoint is lacking on a server instance whose service accounts use Windows
Authentication, to create the endpoint automatically. If you using a non-domain account then use Transact-SQL or PowerShell to manually create a database mirroring endpoint that uses a
certificate. For more information, see CREATE ENDPOINT (Transact-SQL) and CREATE ENDPOINT (PowerShell).
Option
Description
Server Name
Displays the name of a server instance that will host an availability replica.
Endpoint URL
Displays the actual or proposed URL of the database mirroring endpoint.
Port Number
Displays the actual or proposed port number of the endpoint.
Encrypt Data
Indicates whether data sent over this endpoint is encrypted.
SQL Server Service Account
Username of the SQL Server service account.
Figure 7. Specify endpoints
c) Backup Preferences Tab
Use this tab to specify your backup preference for the availability group as a whole and your backup priorities for the individual availability replicas. For MSSQL Server Standard
Edition used only Primary replica. You need to set up backup jobs if you want to make backup database.
Option name
Description
Prefer Secondary
Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online.
Secondary only
Specifies that backups should never be performed on the primary replica.
Primary
Specifies that the backups should always occur on the primary replica.
Any Replica
Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups.
Figure 8. Specify backup rule
d) Listener Tab
Use this tab to create an availability group listener. The listener will provide a client connection point. IP Address of the listener is used in the connection string to connect
the database cluster.
On the Select Initial Data Synchronization page, choose how you want your new secondary data-bases to be created and joined to the availability group. Choose one of the following
options:
Automatic seeding: SQL Server automatically creates the secondary replicas for every database in the group. Automatic seeding requires that the data and log file paths are the
same on every SQL Server instance participating in the group. Available on SQL Server 2016 (13.x) and later. See Automatically initialize Always On Availability group.
Full database and log backup: Select this option if your environment meets the requirements for automatically starting initial data synchronization (for more information, see
Prerequisites, Restrictions, and Recommendations, earlier in this topic). If you select Full, after creating the availability group, the wizard will back up every primary
data-base and its transaction log to a network share and restore the backups on every server instance that hosts an secondary replica. The wizard will then join every secondary
database to the availability group.
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 use 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).
Figure 10. Select initial data synchronization
The Validation page verifies whether the values you specified in this Wizard meet the requirements of the New Availability Group Wizard. To make a change, click Previous to return
to an earlier wizard page to change one or more values. The click Next to return to the Validation page, and click Rerun Validation.
Figure 11. Validation page
On the Summary page, review your choices for the new availability group. To make a change, click Previous to return to the relevant page. After making the change, click Next to
return to the Summary page. If you are satisfied with your selections, optionally click Script to create a script of the steps the wizard will execute. Then, to create and
configure the new availability group, click Finish.
Figure 12. Summary page
The Progress page displays the progress of the steps for creating the availability group (configuring endpoints, creating the availability group, and joining the secondary replica
to the group). When these steps complete, the Results page displays the result of each step. If all these steps succeed, the new availability group is completely configured. If any
of the steps result in an error, you might need to manually complete the configuration or use a wizard for the failed step. For in-formation about the cause of a given error, click
the associated "Error" link in the Result column.
Figure 13. Results page
When the wizard completes, click Close to exit. For more information, see Creation and configuration of Always On availability groups. You can monitor the status of an availability group using an internal monitor included in SQL
Server Management Studio.
Figure 14. Availability Group Monitor
Use the listener address (IP address or DNS name) to connect to the cluster, see the Listener Tab for more information.
Note: The availability group has Windows
cluster resource properties, such as the Maximum Failures in the Specified Period property.
This property is used to avoid the indefinite movement of a clustered re-source when
multiple node failures occur.
Check the "Maximum Failures in the Specified Period property":
Start "Failover Cluster Manager".
In the navigation pane, click "Roles".
In the "Roles" pane, right-click the clustered resource, and then click "Properties".
Click the "Failover tab, and check the “Maximum Failures in the Specified Period value".
If you get the following error after configuring availability groups:
“Database Mirroring login attempt by user 'Login' failed with error: 'Connection handshake failed. The login 'Login' does not have CONNECT permission on the endpoint.”
follow these steps:
Check the existence of the given user. Create a login if it does not exist. CREATE LOGIN [login];
If the user exists, check and grant the rights to connect to the endpoint. GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [login];
MSSQL Server Availability Group includes work with two network adapters. To verify that the network adapters are included in the cluster
Open the Failover Cluster Manager.
Make sure each NIC has a use mode of Cluster Only or Cluster and Client
Cluster Only – used for communication only between cluster nodes
Cluster and Client – used for communication between cluster nodes and clients.
Figure 15. Failover Cluster Manager
If you want both network adapters to work with both clients and database instances, then you can:
Enable the use of the "Cluster and Client" mode for both network adapters.
Figure 16. Network adapter properties
Add a second network address for the listener:
During configuring an availability group (see Set up AlwaysOn Availability Group).
Adding a new address to an existing group listener:
Using Failover Cluster Manager
Figure 17. Listener properties as a failover cluster resource
Using SQL Server Management Studio
Figure 18. Availability Group Listener properties
In the backend connection string, you can specify the listener address in the form:
DNS name of the listener. In this case, the SQL Server driver will automatically manage the connection. (Example: “ListenerDNSName:port”).
List of IP addresses. In this case, the choice of adapter and connection will be controlled by the backend. (Example: “ip_first_adapter:port, ip_second_adapter:port”).
Note: If the cluster network is used in the "Cluster and Client" mode, then if the network disappears, then after restoring the network, it is necessary to check its mode
through the failover cluster manager, since it can switch to the "Only cluster" mode.
