[en] Installing NiceLabel in Microsoft SQL failover cluster

[en] Connect to the first node.

[en] Open Server Manager and navigate to Tools > iSCSI Initiator.

[en] A dialog box pops up (... service is not running). Click Yes.

[en] On the Targets tab, enter NAS IP address and click Quick Connect...

[en] Quick connect finds all available targets to your server. Click Done, not Connect. This action lists the targets, and you can connect to each target separately.

[en] When attempting to connect, click the target and continue with Connect.

[en] Click Advanced... and enter your credentials in Enable CHAP log on section. Click OK, Enable multi-path, then click OK to establish a connection. The connection in iSCSI Initiator must be set up and running on both nodes at the same time – if not, then you probably don't have multiple connections allowed.

[en] Once you connect all disks go to Disk Management on one node to bring them online and format them to NTFS.

[en] Set the disks to Offline mode in Disk Management and iSCSI Initiator, and set the disks to online mode on the second node to test their connection and availability.

[en] In the following steps, create the cluster. Select the node and set the disks to online mode only on that node.

[en] When you select the node, go to Server Manager > Manage tab > Add Roles and Features.

[en] On Installation Type keep Role-based or feature-based installation and continue with server (node) selection.

[en] In the Features section, skip Server Roles and select Failover Clustering.

[en] Install Failover Clustering and open it by navigating to Tools > Failover Cluster Manager. Install this option on the second node, too. If you don't, the node is not found when performing validation for the cluster.

[en] Before creating the cluster, always run Validate Configuration.

[en] Include both nodes at validating configuration. Validating configuration shows you existing errors or warnings.

[en] Run all tests.

[en] If the tests pass successfully you can click the checkbox "Create the cluster now using the validated nodes..." and proceed with creating the cluster.

[en] Once the cluster is formed, you can check it by navigating to Server Manager > Tools and select Failover Cluster Manager.

[en] If the validation passed successfully, you should see the cluster formed with a Witness disk.

[en] Other available disks are listed under Storage → Disks.

[en] When you install the Always On Availability Groups you must meet the prerequisites:

[en] - A cluster node can host one replica for an availability group. The same node can't host two replicas from the same availability group. The cluster node can participate in multiple availability groups, with one replica from each group.

[en] - Each availability group requires a set of failover partners, known as availability replicas, which are hosted by instances of Microsoft SQL Server. A given server instance can be a stand-alone instance or a Microsoft SQL Server failover cluster instance (FCI).

[en] - If you use a Microsoft SQL Server failover cluster instance (FCI) to host an availability replica, you must understand the FCI restrictions and meet the FCI requirements. The Microsoft SQL Server instance should reside on a WSFC node.

[en] This article describes two nodes with two stand-alone SQL instances (not FCI).

[en] Install Microsoft SQL Server on both nodes. Go to the Installation tab in Installation Center and select New SQL Server stand-alone installation or add features to an existing installation.

[en] Proceed installation with recommended steps. In the Feature Selection dialog box, select the following components Database Engine Services.

[en] In the Instance Configuration dialog box leave Default instance if this is your only Microsoft SQL Server instance (by default it is MSSQLSERVER) or write your own Named instance and Instance ID. Use Instance ID to identify your server instance in the future.

[en] Continue with the recommended steps.

[en] In the Server Configuration dialog box, provide the credentials for the SQL Server service accounts in the Service Accounts tab. Select the checkbox Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service. It enables Instant File Initialization for SQL Server.

[en] In the Database Engine Configuration > Server Configuration tab:

[en] a. Select Mixed Mode (SQL Server authentication and Windows authentication) in the Authentication Mode section. Provide a password for the sa account.

[en] b. Click the Add Current User button in the Specify SQL Server Administrators section to add the currently logged on user to be a part of the SQL Server administrators group.

[en] Proceed with installation. Don't change Data Directories, TempDB , and FILESTREAM.

[en] When the installation is complete, close the dialog box.

[en] Repeat the installation steps 1-10 on the second node.

[en] When you install both instances, go to Microsoft SQL Server Configuration Manager and right-click on SQL Server instance. Select Properties.

[en] Enable Always On Availability Groups.

[en] Open SQL Server Network Configuration, click Protocols for [yourserverinstance] enable TCP/IP protocol, then restart the SQL Server.

[en] Repeat steps 11-14 on both nodes.

[en] Verify the connection to the other node with SSMS (Microsoft SQL Server Management Studio). If the connection fails, check the solution:

[en] - If you receive such an error, then you probably have an outdated or misconfigured SPN (Service Principal Names) entry.

[en] - Run Command Prompt as Administrator.

[en] - If you are using Windows Authentication/Domain credentials, then run the command:

[en] sqlcmd -S [servername] (example: sqlcmd -S server_name)

[en] - If you receive 1> in the next line, the connection was established successfully, and you can proceed by resolving this issue. If not, try to troubleshoot errors further.

[en] - You can resolve the original issue by removing the SPN entry in the attributes of the computer account in AD for the server.

[en] - Find the machine account in AD Users and Computers (using Advanced Features)

[en] - Double click the computer to which you want to establish the connection

[en] - Go to Attribute Editor and find servicePrincipalName. Click Edit.

[en] - If there are any MSSQLSvc entries you can delete them and add a new one. For example, MSSQLSvc/sqlserver05.local.

[en] Note: This is just one of the many possible solutions.

[en] Create two SQL users on both nodes: EPM_USER and EPM_DBADMIN.

[en] Go to SSMS on each node, navigate to Security, and right-click the Logins folder. Select New Login.

[en] Enter the name of the user, select SQL Server authentication, and provide the password. Disable User must change password at next login and Enforce password expiration.

[en] Write down the passwords, you will need them later.

[en] In the Server Roles tab, select sysadmin Server role.

[en] Click OK and repeat steps 18-20 for EPM_DBADMIN.

[en] Repeat the process on the second node.

[en] To create Always On Availability Group, create NiceAN database from scratch on the primary replica (node):

[en] a. Create a database on a primary node by using DBTool provided with NiceLabel installation.

[en] b. Run DBToolSFX and go to the DBTool folder.

[en] c. Run DBToolGui.exe.

[en] d. Select your local server with Windows Authentication and click Connect.

[en] e. Enter Database information. Set Users creation to Assign required roles to the existing users. Set Authentication to SQL Server Authentication.

[en] f. Enter passwords for the EPM_DBADMIN and EPM_USER that you created in SSMS and click Create database.

[en] g. Save the configuration file in the same folder as the Control Center installation file. If you won't run Control Center on this node, you don't have to save the configuration file.

[en] Open SSMS and go to Always ON High Availability section, then select New Availability Group Wizard...

[en] Skip the Introduction page and select Availability group name. Click Next.

[en] In the Select Databases section, first backup your database (right-click the database > Tasks > Back Up...). When the backup process is completed, click Next.

[en] In Specify Replicas section, add your replica and then select:

[en] - Automatic Failover (Up to 2): Checked or unchecked (based on your preferences)

[en] - Synchronous Commit (Up to 3): Checked

[en] - Readable Secondary: Yes

[en] - In asynchronous-commit mode, the secondary replica never synchronizes with the primary replica. Asynchronous-commit mode can be useful in a disaster-recovery scenario in which the primary replica and the secondary replica are separated, and when you do not want small errors to impact the primary replica or when performance is more important than synchronized data protection. The primary replica doesn't wait for acknowledgments from the secondary replica so problems on the secondary replica never impact the primary replica.

[en] - In synchronous-commit availability mode (synchronous-commit mode), a secondary database catches up to the primary database and goes to the SYNCHRONIZED state. The secondary database remains SYNCHRONIZED as long as data synchronization continues. This enables every committed transaction on a primary database is also committed on the secondary database. When every secondary database on a secondary replica is synchronized, the synchronization-health state of the secondary replica is HEALTHY.

[en] - The Always On availability groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). A readable secondary replica can be in synchronous-commit availability mode or asynchronous-commit availability mode. A secondary replica allows read-only access to all its secondary databases.

[en] Configure Listener on Specify Replicas. Select the following options:

[en] - Enable Create an availability group listener.

[en] - Enter Listener DNS Name.

[en] - Port: 1433.

[en] - Select preferred Network Mode (for example, DHCP).

[en] In the Select Data Synchronization section don't change the Automatic seeding setting.

[en] Wait for validation to complete and create Availability Group.

[en] Note: Avoid using two databases with the same names on the nodes.

[en] To validate this availability group, you can open a connection in SSMS to the other node and confirm that databases exist on both nodes and are synchronized.

[en] Establish a connection to the listener that you created (by hostname or IP address). You can find the IP address in Failover Cluster Manager in the Always On availability group role in the resource section, or select the IP that you provided (if you didn't choose DHCP).

[en] If the connection is successful, you can see your listener in the SSMS list with the primary database/databases.

[en] To test if the availability group is working properly, go to the primary node and right-click on the availability group. Select Failover... and perform failover as instructed on the Failover Wizard.

[en] Make sure you are connected to the selected instance.

[en] If failover is completed successfully you can see the change in Secondary/Primary node classification in the SSMS:

[en] Start Control Center installation.

[en] Follow the wizard and enable Security Settings if you have an active SSL certificate. Leave other settings at their defaults.

[en] At the Microsoft SQL Server Instances step, type in your listener IP address and keep NiceAN for the database name. Click Next.

[en] Enter Optional User Settings Changes for your selected database. Keep Windows Authentication in the Database Creation section and enter SQL users in the Users creation section.

[en] Click Next to begin the installation.

[en] When setup is finished you are redirected to the browser window where you can access the Control Center.

[en] To test, if the Always On Availability Group is working properly, perform manual failover in SSMS and refresh the browser (including clearing cache in the browser).