03 - 搭建SQL Server 2019 Aways-On高可用集群

In this article, we will proceed with configuring a SQL Server Always On Availability Groups and perform failover validations.

You should go through the following articles and build the infrastructure before proceeding with this article.

  1. A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
    • Configuration of virtual machines on Oracle VirtualBox with Windows Server 2016
  2. Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
    • It configures the domain controller and active directory features.
    • Add the virtual machines to the domain created above.
  3. Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
    • Windows failover cluster configurations
    • iSCSI target and initiator configurations
    • File share witness confirmation as Cluster quorum

Install SQL Server 2019 on Windows Server 2016 for SQL Server Always On availability groups

SQL Server 2019 is the latest version available for SQL Server. It has several new features along with the enhancements to existing ones.

You can check out SQL Server 2019 articles on SQL Shack to learn more about SQL Server 2019 features.

SQL Server 2019 provides developer edition to use for development and test purpose without any license cost.

Open the Microsoft URL and click on download now for developer edition.

Download SQL Server 2019 developer edition

It downloads a setup file. Launch it, and you get the below options.

  • Basic: It installs SQL Server 2019 with the default configuration of the SQL Server database engine
  • Custom: In this option, we can select the features, configure them in the installation wizard
  • Download media: It downloads the ISO or CAB file for the SQL Server set up

Select an installation type

It is a better idea to download the media first and then perform the installation. You can share the media across different servers as well as it avoids issues due to network fluctuations.

Next, specify the file type, directory and language of the setup.

Specify SQL Server installer download

Click on Downloads, and it gives you ISO file, but it might take time depending upon your network bandwidth.

Downloading media

It downloads the SQL Server 2019, as shown below.

Download successful

Install SQL server 2019 on SQLNode1

Click on setup.exe, and it opens the SQL Server Installation Center.

Install SQL server 2019 on SQLNode1

Navigate to Installation -> New SQL Server standalone installation.

In a SQL Server Always On, we install the SQL as a standalone component on all the nodes. It does not require a SQL Server in a failover cluster mode.

New SQL Server standalone installation.

Select the edition of SQL Server 2019. We choose the Developer edition in this demo.

Select the edition

Accept the Microsoft software license terms and privacy agreement.

Microsoft software license terms

The next step of Microsoft update is optional. We can ask Microsoft update to check updates for Windows, Microsoft software along with SQL Server 2019. It requires an active internet connection on the server.

Microsoft update

It installs the required setup files.

Install setup files

In the next screen, we configure the following options.

  • Feature selection: Here, we select the features we want to install in the virtual machine. Select the database engine services from the instance features
  • Instance root directory: Specify a root directory for your SQL Server installation. As per best practice, you should not install it in the root directory where the Windows OS file exists. Similarly, configure the shared feature directory

Feature selection

Specify a SQL instance name as a named instance. You can give the SQL instance name to reflect your environment and application.

It creates the instance-specific directory in the specified instance root path.

Specify a SQL instance

On the next page, we configure the service account for running the SQL Services. Ideally, you should run the SQL Services with a separate service account in the active directory.

Let’s hold SQL Server installation at this moment (do not cancel) and connect to the active directory server.

Create a service account in the active directory

Type dsa.msc in the Windows start -> run and launch the active directory users and computers.

Create a service account in the active directory

Right-click on Users folder and create a new user with a unique service account information.

Service account

As per best practice, you should use the following things in the SQL Server service account.

  • A complex password (alphanumeric password)
  • Its password should not expire
  • User cannot change the password after the first logon

Service account details

Once the user is created, add the user to the administrator group on the respective node.

SQL Server 2019 installation (Continued)

Switch back to SQL Server installation on Node 1 and specify the service account for SQL Services. You can also change the default startup mode (automatic or manual) for SQL Services.

Server Configuration

Now, here comes various default database engine configurations.

  • Server Configuration: It gives you the option to choose the authentication mode (Windows or mixed mode). If you select the mixed mode, it enables SQL authentication as well. In this case, specify the password for SA. We can also add AD users as SQL Server administrators. Here, I added the MyDemoSQL\adadmin Windows user as SQL admin. It provides sysadmin permissions to all SQL Server administrators added here

    Database engine configurations

  • Data Directories: Specify the data root directory, user database, user log and backup directories. You should consider your storage requirements, database growth, IOPS for considering the data directories. For this article, we can go with the default directories

    Data Directories

  • TempDB: TempDB is a critical database in SQL Server and responsible for all data sorting, internal objects, row versions, indexes, DBCC checks, temporary objects, order by, group by functions. We should place it on the fastest storage drives such as flash disks

    According to the Microsoft recommendation, the number of tempdb data files depends upon the number of logical processors

    Logical processors <= 8: Number of TempDB files should match the logical processors count

    Logical processors > 8: Use 8 data files and the increase in multiple of 4 in case of contention

    SQL Server 2019 set up detects your system configuration, configures the tempdb files accordingly

    TempDB configuration

  • MAXDOP: It is the maximum degree of Parallelism setting for SQL Server. It controls the number of cores for parallel query execution. This configuration during SQL Server installation is available from SQL Server 2019

    MAXDOP

  • Memory: SQL Server is a memory-intensive application. It consumes the whole memory, whatever is assigned to it. By default, SQL Server is designed to use all OS memory. It might cause limited memory for the operating system that eventually leads to high resource utilization and performance issues

In SQL Server 2019, we get the option to set maximum default memory or the recommended maximum memory. We should assign approx. 70-80% of total server memory to SQL Server in case it does not hold any other application.

For the demo part, we can go with the default configuration.

Memory

Review your SQL Server 2019 installation configuration and click on Install.

Review your SQL Server 2019 configuration

It installs the SQL Server database engine services successfully, as shown below.

Setup complete

Install SQL server 2019 on SQLNode2

You can follow the steps mention in the Install SQL server 2019 on the SQLNode1 step and configure SQL Server 2019 on SQLNode as well.

Install SQL Server Management Studio on SQLNode1 and SQLNode2

SQL Server Management Studio (SSMS) is not an integral part of the SQL Server set up. It is a separate client application, and Microsoft provides periodic updates (monthly) with bug-fixes and new enhancements.

You can download SSMS latest version from Microsoft Docs and complete the installation wizard on both nodes.

Install SQL Server Management Studio

Launch SSMS and verify the SQL Server 2019 on both the instances.

Connect to both SQL nodes

Enable feature SQL Server Always On availability groups on SQLNode1 and SQLNode2

To use the SQL Server Always On, we need to enable the feature on both nodes. Connect to SQLNode1 and open SQL Server 2019 configuration Manager.

In the configuration manager, navigate to the Always On Availability Groups tab. Here, you can see the Windows failover cluster name. Put a check on the Enable Always On Availability Groups.

Enable feature SQL Server always on availability groups on SQLNode1 and SQLNode2

You need to restart the SQL Services. Click OK and restart SQL Services on SQLNode1.

Restart SQL

Similarly, enable the SQL Server Always On availability groups on SQLNode2 and restarts its services.

Create a SQL database to add in the availability group

Connect to SQL Server 2019 on SQLNode1 using SSMS. Expand Always On Availability Groups, and it does not show any groups as of now.

Create a SQL database

It does not hold any user databases as of now. We need a user database to add in the SQL Server Always On. In the new database configuration window, specify a database name and create it with default configurations.

Database name

As a prerequisite, a full backup should exist for a database to add in the Always On. In the query window of SSMS and run the following command to take full backup in the default directory.

Configure SQL Server Always On availability group

To create a new availability group, right-click on Always On Availability groups and new availability group wizard.

It launches the new availability group with a brief description of the steps.

New availability group

Specify a name for the availability group. Here, I specify the availability group name as SQLAG2019. In the cluster type, it automatically selects the Windows Server Failover Cluster.

We can skip the options – database level health detection and Per database DTC support for this article.

Availability group name

In the next step, it shows you the user database and its eligibility to add to the availability group. We have already performed a full database backup, so it shows the status as meets prerequisites. If the database is encrypted, such as SSISDB, we need to specify the password to decrypt the database and configure the AG.

Database eligibility for AG

On the next page, we can do the following configurations.

  • Replicas: By default, it shows you the instance you are connected to

    Replicas

    Click on Add replica, specify the SQL instance name for SQLNode2. As we can see here, SQLNode1 takes the primary role, while SQLNode2 takes a secondary role initially.

    We can have Synchronous commit and Asynchronous commit for the availability mode. In the synchronization mode, it waits for the acknowledgment from the secondary node before commit records on the primary instance.

    In an asynchronous mode, it does not wait for any acknowledgment and commit records directly on the primary instance.

    You can refer to this article, Data synchronization in SQL Server Always On Availability Groups for more details on availability modes.

    Specify replica and sync mode

    We can have automatic failover for the synchronous commit mode; therefore, I choose automatic failover in synchronous mode.

    Synchronous commit mode

  • Endpoints: It automatically creates the database mirroring endpoints on both the SQL instances on port 5022

    Endpoints

    Backup preferences

    As highlighted earlier, we can use the secondary AG replica for SQL Server backups as well. We will go with the default options in this article. You can refer to the following articles for the backups knowledge in AGs.

     

     

    Backup preferences

    Listener

    In SQL Server Always On, we use a listener to connect to the primary node. We might have a primary replica on any of the participating nodes. SQL listener helps to avoid any modification in the connections string, in case of a database failover. They can connect to SQL using listeners, and it always redirects the connection to the primary replica.

    We can choose to create an availability group listener name or not. If we wish to create a listener, specify a listener’s name and the port.

    Listener

    On the next page, select the method for the initial synchronization. You can choose the automatic seeding, full database and log backup, Join only and skip initial data synchronization.

    Data synchronization

    Next, it performs the validations, and you get success status once it does not report any issues.

    Validation

    View Summary

    Review your configuration and click Finish to configure a new availability group and synchronize it. Launch availability group dashboard and you can see the database is in synchronized mode on both SQLNode1 and SQLNode2.

    Check AG dashboard

    Previously, we do not have any role in the Windows failover clustering. Now, after the AG configuration, we see the role of the SQL listener. Currently, this role is owned by SQLNode1.

    View role in failover cluster

    A usual error while creating the SQL Listener for SQL Server Always On availability groups

    You might face the following error while creating the SQL listener.

    SQL Listener error

    This error comes if the computer account does not have sufficient permissions in the active directory to create the computer objects.

    To resolve this error, open the active directory and open the Computers Properties and go to Security.

    Computer properties

    Click on Add and search for the cluster name account in the Computers object type. For example, in my case Windows cluster name is SQLAGCLU.

    Enter the object name

    Click OK and open the advanced properties. In the advanced properties, click on Add permissions.

    Assign permissions

    Here, provide the Create Computer objects and Delete Computer objects permissions for the cluster computer account.

    Create Computer Objects

    After assigning the permissions, it creates the SQL listener successfully.

    SQL Listener

    We can connect to the primary SQL instance using SQL listener, as shown below.

    Connect primary replica using listener

    Failover testing for SQL Server Always On availability groups

    Once you have configured the AG replica, it is equally important to test whether the AG failover is working fine or not. You might not come with an accidental surprise later that failover is not working.

    Right-click on the AG group and click on failover.

    It shows you the current primary replica, replica status and its failover readiness. Its status should be No data loss to avoid any data loss scenario.

    Failover testing

    On the next page, connect to the current secondary replica.

    Specify replica

    View the summary for the current and new primary replica along with the affected database during failover.

    Click on Finish to begin failover

    Click Finish, and it performs a failover from SQLNode1 to SQLNode2 for my demonstration.

    Failover progress

    Refresh the AG console, and you can validate the new primary replica and synchronization status between both nodes.

    View updated dashboard

    In the Failover Cluster Manager also, you have the role owner as SQLNode2.

    Role owner in cluster

    Conclusion

    In this series of articles for SQL Server Always On availability groups, we explored end to end process for all configurations including VM, Active Directory, DNS, failover cluster, AG replica. You can follow these articles and prepare your SQL Server Always On availability group for the testing purpose.

posted @ 2021-02-27 10:48  周文洋  阅读(528)  评论(0编辑  收藏  举报