How to install and configure Microsoft SQL Server 2016 on Windows 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 this, click on the option “New SQL Server stand-alone installation or add features to an existing installation”

  

• 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

  

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

  

• 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.

  

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

  

• 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.

  

  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)

  

  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 features of SQL Server we will install in our instance. Note the number of new features in SQL Server 2016. This is the version with the largest number of improvements and new features in the history of SQL Server. For this example, I checked only the features “Database Engine”, “Analysis Services” and “Reporting Services - Native”

  

• 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.

  

• 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.

  

  In the “Collation” tab, we can define the character language of the database and Analysis Services. This is where we define whether the bank will support accentuation, whether 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.

  

  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.

  

• 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.

  

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

  

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

  

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

  

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

  

  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.

  

• 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.

  

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

  

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

  

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

  

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 option “Install SQL Server Management Tools”, 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.

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

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

Installation completed. SQL Server 2016 Management Studio installed.

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 option “SQL Server Network Configuration”> “Protocols for SQL2016 (Name of your instance here)”.

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

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

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

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 on the option “SQL Server Services”, select the service “SQL Server (SQL2016)”, right click and select the option “Restart”.

Now only the additional Windows Firewall rules are missing.

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:

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 VM's. See more by accessing the post How to configure your VM network in Bridge mode in VMware Player.