Rajendra Gupta
New availability group

Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups

July 15, 2020 by

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.

2,952 Views