How to install and configure Microsoft SQL Server 2016 on Windows Server 2016

Views: 10.772
Reading Time: 11 minutes

Hello people,
Good Morning!

In this post, I will demonstrate to you how to install SQL Server on Windows Server 2016. This post is an update from my other post. Installing and Configuring SQL Server 2014 on Windows Server 2012 R2 and if you are a SQL Server DBA and you have not installed SQL Server 2016 yet, you will face this scenario very soon.

For this demonstration, I will use SQL Server 2016 Developer Edition (en_sql_server_2016_developer_x64_dvd_8777069) and Windows Server 2016 Datacenter Technical Preview 5 (The final release has not yet been released), recently installed on a virtual machine. I have not made any adjustments or settings in the environment so that the installation is as faithful as possible to a newly created server.

One difference you will notice when installing SQL Server 2016 is that Management Studio now does not appear in installation options when you are creating a new instance or adding a node to a cluster because it is no longer part of SQL installation. Server 2016.

However, on the installation home screen itself, there is an option for you to click and be directed to a link where you can download the Management Studio installer.

How to install and configure Microsoft SQL Server 2016

How to install and configure Microsoft SQL Server 2016

  • 1) Insert the SQL Server 2016 installation disc into your DVD / USB drive to begin the installation.
  • 2) In this example, we will install a new stand-alone instance (which is not part of a cluster). To do so, click on the option “New SQL Server stand alone installation or add features to an existing installation”

    microsoft-sql-server-2016-installation-1

  • 3) From this screen you will be able to choose which edition of SQL Server you will install and enter the registration key for your license. In this example, I will use the Developer Edition edition, which is the full edition of SQL Server and free for developers, students, and any type of IT professional, provided no business data from production environments

    microsoft-sql-server-2016-installation-2

  • 4) On this screen you read and accept the SQL Server 2016 License Agreement.

    microsoft-sql-server-2016-installation-3

  • 5) On the next screen you can enable the option to use Microsoft Update to enable automatic updates of SQL Server 2016 and other Microsoft software.

    microsoft-sql-server-2016-installation-4

  • 6) This screen shows critical SQL Server 2016 product-specific patches and updates. We highly recommend downloading the updates presented in this step.

    microsoft-sql-server-2016-installation-5

  • 7) Once the patches have been downloaded and installed, you will be directed to this screen, containing a checklist of the prerequisites for installing SQL Server 2016.

    microsoft-sql-server-2016-installation-6

    Note that there is an alert on the Firewall part that states that Windows Firewall is enabled, so SQL Server ports must be released for remote connections to be able to access the new instance (http://go.microsoft.com/fwlink/?linkid=94001)

    microsoft-sql-server-2016-installation-7

    At the end of the post I will demonstrate how to properly configure Windows Firewall so that the instance is accessible to remote connections.

  • 8) At this stage of the installation, we can define which SQL Server features we will install on our instance. Look at the amount of new SQL Server 2016 features. This is the most improved version and new feature in SQL Server history. For this example, I just ticked the features “Database Engine”, “Analysis Services” and “Reporting Services - Native”

    microsoft-sql-server-2016-installation-8

  • 9) After selecting the features to install, let's define the name of the instance we will be installing. By setting the default instance to “Default instance”, you do not need to enter the instance name to connect to the SQL Server server. Otherwise, to connect to the database, you have to enter the server name and instance. Ex: home_server \ instanceTest.

    An instance, either standard or named, has its own set of program and data files, as well as a set of common files shared between all instances of SQL Server on the computer. For this reason, even if you install SQL Server as the default instance, you MUST enter the instance ID.

    microsoft-sql-server-2016-installation-9

  • 10) In this screen we can define how services are started and which accounts will be used for each service. Usually I do not make changes to this screen, I just change the startup of SQL Server Agent from Manual to Automatic.

    microsoft-sql-server-2016-installation-10

    In the “Collation” tab, we can set the database and Analysis Services character language. This is where we define if the bank will have accent support, if it will be case-sensitive (lowercase letters are different from uppercase letters. This is the default setting) or case-insensitive (uppercase and lowercase letters are interpreted by the bank as the same thing) .

    Note: Collation of tables may differ from the collation of a specific column, which may also differ from the collation of the database. Database Collation is used to set the default collation of tables and columns when not specified.

    microsoft-sql-server-2016-installation-11

    By clicking on the “Customize” button (optional), we can customize the collation of the database as needed. Particularly, I don't like to use Case-Sensitive bank very much, although it's good practice. I generally use the Latin1_General_CI_AI collation, but it's up to each DBA and / or environment in your company.

    microsoft-sql-server-2016-installation-12

  • 11) In this step, we can configure the form of user authentication: Windows Authentication (Active Directory) or Mixed (Active Directory and SQL Server Authentication). In addition, we can configure users who will be instance administrators.

    To add the logged in user who is installing SQL Server, simply click the "Add Current User" button.

    SQL Server authentication modes are:
    - Windows authentication: Uses Active Directory to authenticate users and only allows users who are registered with AD. No user and password entry required.

    - Mixed Mode: Allows both Active Directory authentication and SQL Server authentication, where the login is created in the database and is accessed using username and password. This is the most common option because most of the time, applications and systems do not use AD users, and configuration within the application to use SQL Server users is much simpler.

    microsoft-sql-server-2016-installation-13

    In the “Data Directories” tab, you can choose the data root directories, backup directory, log backup directory and data backup.

    microsoft-sql-server-2016-installation-14

    The "TempDB" tab is a new tab much requested by DBA's in previous versions, who installed the database and then had to create the other TempDB files, generating unnecessary additional work. Creating multiple TempDB files is Microsoft's own suggestion to avoid containing TempDB in the instance and providing an interesting performance gain, especially if TempDB files were stored on another disk / LUM of data / log from other databases to avoid I / O competition.

    microsoft-sql-server-2016-installation-15

    Finally, in the tab “FILESTREAM” it is possible to define if this feature will be enabled in the database. FILESTREAM integrates SQL SERVER with the NTFS file system by storing type objects (BLOB) with the varbinary (max) type using the NTFS file system for this storage. It is possible through T-SQL to insert, delete, update and query and backup this data natively.

    microsoft-sql-server-2016-installation-16

  • 12) In this screen, we can perform the Analysis Services configuration (Cubes), where you can define the data modeling type (Multidimensional - Default Option) and the “new” Tabular mode, present from SQL Server 2012 (See the difference between the two here) and specify Analysis Services Administrator users.

    microsoft-sql-server-2016-installation-17

    Once again, SQL Server Setup allows us to define directories where the Analysis Services Cubes data will be physically written to disk, such as data files, log files, temporary directory, and default backup directory.

    microsoft-sql-server-2016-installation-18

  • 13) Here we can install and configure Reporting Services or just perform the installation and leave the configuration later. I checked the second option and I will demonstrate how to install and configure Microsoft Reporting Services 2016 in another specific post for this.

    microsoft-sql-server-2016-installation-19

  • 14) In this step, the installation shows us all the features and configurations we have set for review before beginning the installation.

    microsoft-sql-server-2016-installation-20

  • 15) At this time, SQL Server 2016 is being installed on your machine / server.

    microsoft-sql-server-2016-installation-21

  • 16) If everything went as expected, you should see this screen at the end of the installation process. Microsoft SQL Server 2016 INSTALLED.

    microsoft-sql-server-2016-installation-22

How to install Microsoft SQL Server Management Studio 2016

How to install and configure Microsoft SQL Server Management Studio 2016

As I commented at the beginning of this post, installing SQL Server 2016 no longer comes with Management Studio. You will need to download it separately to your server to perform the installation.

To do this, simply run the installer again and select the "Install SQL Server Management Tools" option, which will open your web browser on the download page (https://go.microsoft.com/fwlink/?LinkId=531355).

At this step, due to Windows Server download restrictions, you will not be able to download the installer or any executable program. Therefore, I recommend downloading the file to a standard machine and then transferring the installer to your server, avoiding security policies having to be changed.

microsoft-sql-server-2016-installation-23

Once the executable is already on the server, simply start the installation.

microsoft-sql-server-2016-installation-25

If all goes well, you will see a table like this:

microsoft-sql-server-2016-installation-26

Installation completed. SQL Server 2016 Management Studio installed.

microsoft-sql-server-2016-installation-33

microsoft-sql-server-2016-installation-34

Enabling TCP Protocol and Setting the Default SQL Server Connection Port

Enabling TCP Protocol and Setting the Default SQL Server Connection Port

Now that we have set up our server, we need to enable connections outside of SQL Server, since in most cases access is made from machines other than the server, as in my case where the server is on the virtual machine and I access the database through Management Studio on the physical machine.

Before enabling Firewall rules, we need to define the connection port for our SQL Server instance.

To do this, run SQL Server 2016 Configuration Manager by going to the Start menu> All Programs> Microsoft SQL Server 2016> Configuration Tools.

For direct access, you can use the path "C: \ ProgramData \ Microsoft \ Windows \ Start Menu \ Programs \ Microsoft SQL Server 2016 \ Configuration Tools" or the direct path of the executable: "C: \ Windows \ SysWOW64 \ mmc.exe / 32 C: \ Windows \ SysWOW64 \ SQLServerManager13.msc ”

When the screen opens, select the "SQL Server Network Configuration"> "Protocols for SQL2016 (Your Instance Name Here)" option.

microsoft-sql-server-2016-installation-27

Right click and select the "Enable" option to enable this protocol.

microsoft-sql-server-2016-installation-28

This message will appear on the screen informing you that the changes will take effect only after restarting the service:

microsoft-sql-server-2016-installation-29

Double click on the “TCP / IP” option to open this screen below. It is important that the value of the “TCP Dynamic Ports” parameter is 0 (Off), as this option is to use random ports for SQL Server connection. For Firewall, this is not legal because they would have to have multiple ports open or even disable Firewall to allow remote connections. Another important point is that on this screen we can set the default instance connection port. I will use the standard SQL Server (1433), but it's up to you.

microsoft-sql-server-2016-installation-30

After committing the changes, you will again be shown a message that the changes have been applied but will not take effect until you restart the service, and we will do so now.

Still in SQL Server Configuration Manager, click the "SQL Server Services" option, select the "SQL Server (SQL2016)" service, right-click and select the "Restart" option.

microsoft-sql-server-2016-installation-31

microsoft-sql-server-2016-installation-32

Now only the additional Windows Firewall rules are missing.

Adding Rules in Windows Firewall

Adding Rules in Windows Firewall

Although we have already enabled external connections during installation, our server's firewall is probably blocking the ports required for SQL Server from another machine to access our server. To do this, simply run the following commands in Prompt-DOS, logged in as Administrator:

microsoft-sql-server-2016-installation-24

Remember that if you have changed the default port of any service, you will need to change this script to enter the default port that you set for the particular service.

If you are using a virtual machine (VM), you will need to use Bridge mode so that your server has a public IP on your network and is accessible by other machines or VMs. See more accessing the post How to configure your VM network in Bridge mode in VMware Player.

That's it folks!
Regards and see you next post.

SQL Server Step by Step Tutorial How to Install and Configure Microsoft SQL Server 2016 on Windows Server 2016 How to Install SQL Server 2016

SQL Server Step by Step Tutorial How to Install and Configure Microsoft SQL Server 2016 on Windows Server 2016 How to Install SQL Server 2016