In this article, we are going to configure Microsoft SQL Server on a Windows machine to listen to a specific TCP port. We will enable TCP/IP and Named Pipe and configure firewall rules so that other network computers can communicate with SQL Server instances.

Pre-requisites
Microsoft SQL Server installed on the machine (please check my article to install SQL Server here).

Configure Named pipe and TCP/IP protocols

Let us first configure Named Pipe and TCP/IP for SQL Server. I have installed SQL Server 2019 on my local machine.

Step 1
Open SQL Server Configuration Manager from the Start menu.

Step 2
Let's first enable Named Pipe for the SQL Server Instance (in my case MSSQLSERVER). Go to SQL Server Network Configuration (in Console pane) and click on the Protocols for MSSQLSERVER.

Step 3
In the details pane (right panel), right-click on the Named Pipes protocol, and then click Enable to enable the named pipe for that particular SQL instance.
Configure Named Pipe and TCP/IP Settings of SQL Server

Step 4
Next, we will enable TCP/IP connection for the SQL Server Instance. So right-click on the TCP/IP protocol and click on the Enable option.

Step 5
Now again right-click on TCP/IP protocol, and then click the Properties option.

Step 6
Now you can see the TCP/IP Properties window, click on the Protocol tab, select Yes in Listen All property.

Step 7
Now click on the IP Addresses tab, scroll down to the IPALL section. Now change the value of the TCP Dynamic Ports property to blank. If its value is zero, the Database Engine will listen on dynamic ports. Next, in the TCP Port property, enter the port number you want this IP address to listen on (in my case 1717), and then click the OK button. You can also specify multiple ports by separating them with a comma.

Note
If you want to use individual IP addresses (IPn sections), please set Listen to All property to No in the Protocol tab. Here, I want to use IPAll section, for that reason, I have set Listen All property to Yes in the Protocol tab.

Step 7
The above configurations will only take effect after we restart SQL Server Instance services. So now select the SQL Server Services option from the Console Pane.

Step 8
Now, right-click on the SQL Server (MSSQLSERVER) from the Details Pane and then click the Restart button to restart the SQL Server service.

We have successfully configured an instance of the SQL Server Database Engine to listen on a specific fixed port by using the SQL Server Configuration Manager. Next, we need to configure a firewall so that our specified port (in my case 1717) can be opened in the firewall.
Firewall configurations to allow SQL port

Now that we have configured SQL Server to listen on a specific port, we need to open that particular port in the firewall. Let us see how to configure the firewall.

Step 1
Open Windows Defender Firewall from the Start menu.

Step 2
You will see the Windows Defender Firewall window from Control Panel. Click on the Advanced Settings link from the left panel.

Step 3
Now you will see Windows Defender Firewall with Advanced Security window. Click on the Inbound Rules from the left panel and then click on the New Rule button from the right panel.

Step 4
Now you will see the New Inbound Rule Wizard window. In the Rule Type step, select Port because we want to allow SQL port 1717 from the firewall and click on the Next button.

Step 5
In Protocol and Ports step, select Specific local ports and enter our SQL port 1717 in the textbox and click the Next button.

Step 6
In the Action step, select Allow the connection and click on the Next button.

Step 7
In the Profile step, check all the checkboxes and click on the Next button.

Step 8
In the Name step, specify a meaningful name for the rule and provide a description and click on the Finish button.

Step 9
Now you can see our new rule is listed in the Inbound Rules list.

Excellent! We have successfully configured a firewall to open the SQL port for the database instance connection from outside.

Expected errors/issues
Sometimes you can face some issues/errors in connectivity with SQL Server instances from outside even after the above configurations. Please make sure:

    In SQL Server Configuration Manager, in the console pane, click on the SQL Server Services, make sure SQL Server Browser service is running.
    If you have assigned an SQL port other than 1433, you have to specify the port number in the server name at the time of the SQL connection. For example, cloude-desire-01, 1717 in my case.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we are going to install Microsoft SQL Server on windows machines step by step. In this tutorial, we are going to use SQL Server 2019 Developer edition and Windows Server 2019.

Pre-requisites
    Windows machine having 2 core and 4 GB RAM configuration

Download SQL Server 2019 Developer Edition
First, let’s download SQL Server installation media from the official website.

Step 1
Download installation media from this link.

Step 2
Run the downloaded file and you will see the below screen. Now select the third option – Download Media.

Step 3
Now you will see the below screen. Please select the language you prefer and select the ISO radio button to download the ISO file. In addition, select the download location of your choice. I will go with the default location. Now press the Download button.

Step 4
Now it will start downloading SQL Server installation media. It will take some time based on your internet connection speed.

Step 5
After successful download of installation media, you will see the below screen. Click the Close button.

Install SQL Server 2019 Developer Edition
Now that we have installation media, we can start the installation of the SQL Server. Let’s see how to install SQL Server step by step.

Step 1
Run install media file (ISO file) downloaded in above section by double-clicking on it. It will extract/mount all the contents in a new temporary drive.

Step 2
Once extraction is completed, double click on the setup.exe file and you will see the below screen. Click on the Installation option in the left panel and then click on New SQL Server stand-alone installation or add features to an existing installation option from the right panel.

Step 3
Now you will see the Product Key window. Select the Developer option from the dropdown and click on the Next button.

Step 4
Now you will see the License Terms window. Just select the checkbox and click on the Next button.

Step 5
Now you will see the Microsoft Update window. It is not compulsory to check for the latest updates but it is recommended. So, select the checkbox and click the Next button.

Step 6
Now it will check for updates and install them if any.

Step 7
After that, it will check some rules or prerequisites for the installation of SQL Server. Once all the rules passed, click on the Next button. Sometimes you may face an error at this stage. You can find some known errors at the end of this article.

Step 8
On the Feature Selection window, select features as shown in the below screenshot. You can also change the location for SQL Server instance installation but I will go with the default location. After feature selection please click the Next button.

        Image 1 – Features selection Image 2 - Features selection

Step 9
It will check some feature rules/prerequisites and then you will see the Instance Configuration screen. Here you can choose between Default Instance and Named Instance. Here I will go with Named Instance.

Note
Default Instance
When SQL Server is installed in the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name. For example, HARDIK-PC.

Named Instance
A named instance is identified by the network name of the computer plus the instance you specify during the installation. The client must specify both the server name and the instance name when connecting. For example, HARDIK-PC/MSSQLSERVER.

Step 10
Next, you will see the Server Configuration window. In Service Accounts tab, select Automatic in Startup Type for SQL Server Agent, SQL Server Database Engine, and SQL Server Browser services.

In the Collation tab, select collation as per your preference.
“Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.” – Microsoft.

Step 11
Next, you will see the Database Engine Configuration window. In the Server Configuration tab, choose Mixed Mode in the authentication mode section and enter a strong password. In Specify SQL Server administrators section, your current windows user should already be added automatically. If not, click on Add Current User button.

In the Data Directories tab, specify locations for database files and backup files. By default, it saves all the files on a C drive but it is not recommended to store database files on an OS drive because if any OS-related issue occurs then we may lose our data. Therefore, I choose D drive on my local machine.

In the TempDB tab, there are configurations for the temporary database file(s). There are some best practices on how to configure temporary database files locations, the number of files, and their file sizes. Ideally, the number of the TempDB data files should match the number of logical processors. So I have a number of files to 2. If you are interested in deep dive into TempDB best practices, here is a very good article on it.

Next, in the MaxDOP tab, the maximum degree of parallelism (MAXDOP) is a server configuration option for running SQL Server on multiple CPUs. It controls the number of processors used to run a single statement in parallel plan execution. By default, the setup will suggest value based on the system configuration.

Next, in the Memory tab, we can configure how much memory SQL Server instance can consume. By default, the installation process will recommend you min and max memory allocation based on the system configuration on which it is going install. However, you can change it. Here you can find best practices for SQL Server memory configurations.

In the FILESTREAM tab, leave the checkbox unchecked because we are not going to enable this feature. FILESTREAM, in SQL Server, allows storing these large documents, images, or files onto the file system itself.

Click on the Next button.

Step 12
Next, the setup will check some feature configuration rules, and then the Ready to Install window will appear. This window shows the summary of all the features and configurations which we have done in the above steps. Once review the summary and click on the Install button.

Step 13
Now, the installation will start and it may take some time based on our configurations.

Step 14
After installation, it will show you the list of features and their installation status. If any error occurred, it will show here.

Congratulations! We have successfully installed SQL Server 2019 Developer edition on Windows machines. Next, you can install SQL Server Management Studio to connect SQL Server and query SQL databases. Please check my blog to – Step by step installation of SQL Server Management Studio.

HostForLIFEASP.NET SQL Server 2019 Hosting

There are a number of knobs and switches that are available to database administrators that can be used to enable better performance. There are three options in particular that this blog will be discussing, trace flag 4199, the database scoped configuration QUERY_OPIMIZER_HOTFIXES and the qeury hint.
 
ENABLE_QUERY_OPTIMIZER_HOTFIXES. Understanding how these options function will give you a hand up on ensuring the query optimizer is running as optimally as possible.
 
Let’s take a look at the three options.
 
Trace Flag4199
In dealing with any trace flag, it is vital that you understand as to what the trace flag will enable or disable and how it pertains to your individual workloads. Some trace flags are benign, such as TF3226 which removes successful backup messages from the log, others can be quite intrusive and cause havoc. Make sure you review any documentation on the respective trace flag prior to implementation.
 
The official verbiage from Microsoft on trace flag 4199 is,
 
“Enables Query Optimizer (QO) fixes released in SQL Server Cumulative Updates and Service Packs.
 
QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 being enabled.”
 
What this really implies is that if your database is at the most current compatibility level for your version of SQL server, then the query optimizer will utilize all of the QO hotfixes up to the release to market (RTM) version but nothing beyond the RTM version.
 
For example, if you are running SQL Server 2017 CU20 the database is set at the appropriate compatibility level (140), and TF4199 is disabled, the query optimizer will only utilize any hotfixes for it up to 2017 RTM. Any query optimizer hotfix that was delivered in subsequent cumulative updates will not be utilized.
 
If you enable trace flag 4199, this instructs SQL Server to implement any query optimizer hotfix that was delivered after the RTM version. Note that enabling this trace flag will be applied across all databases. It’s an all or nothing trace flag as most trace flags are.