Set Up On-premise SQL Server

ยท

4 min read

Install SQL Server 2019

Download the installer

Go to https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Choose Download now under SQL Server on-premises

This will download an executable named SQL2019-SSEI-Eval.

Install

Choose Basic for the installation type: Screen Shot 2022-09-18 at 1.45.03 PM.png

Choose Accept to accept the license terms: Screen Shot 2022-09-18 at 1.53.06 PM.png

Specify the installation location or leave the default. Choose Install: Screen Shot 2022-09-18 at 1.55.35 PM.png

After it finishes downloading the install package, it will begin to install. If a restart is required, preform a restart and re-run the executable.

After installing, you should receive a success message ๐ŸŽ‰: Screen Shot 2022-09-18 at 2.03.49 PM.png

Choose Customize.

This will open the setup dialog: Screen Shot 2022-09-18 at 2.13.50 PM.png

Enabling automatic updates is up to your preference. If desired, check the box.

Choose Next.

Once it says Operation completed, choose Next: Screen Shot 2022-09-18 at 2.18.33 PM.png

Ensure Perform a new installation of SQL Server 2019 is selected. Choose Next.

If you have a product key, enter it now. Otherwise, choose a free edition from the dropdown: Screen Shot 2022-09-18 at 2.24.00 PM.png

Choose Next.

Accept the license terms by checking the box and choose Next: Screen Shot 2022-09-18 at 2.28.09 PM.png

On the next page, we'll choose the features we want to install: Screen Shot 2022-09-18 at 2.39.54 PM.png

Check Database Engine Services and choose Next: Screen Shot 2022-09-18 at 2.42.48 PM.png

Specify the instance name and the Instance ID field will automatically populate: Screen Shot 2022-09-18 at 2.55.22 PM.png

Choose Next.

The Server Configuration page will show. Choose Next: Screen Shot 2022-09-18 at 2.58.43 PM.png

Select either Windows authentication mode or Mixed Mode. If you choose Mixed Mode, specify a password.

In the Specify SQL Server administrators section. Pick a Windows user(s) by choosing Add Current User to add the current user, and/or use the Addโ€ฆ button:

Screen Shot 2022-09-18 at 3.01.28 PM.png

Choose Next.

At this point, you're ready to install the instance ๐ŸŽ‰: Screen Shot 2022-09-18 at 3.12.24 PM.png

Choose Install.

After the installation completes, you should receive the below success page โœ…: Screen Shot 2022-09-18 at 3.22.44 PM.png

Choose Close and Close: Screen Shot 2022-09-18 at 3.26.14 PM.png

Choose Yes: Screen Shot 2022-09-18 at 3.27.17 PM.png

That's the end of the installation process! You can now access your SQL Server from itself.

Configure Your Server To Allow Connections From Another Computer

Enable TCP/IP Connections

Find and open SQL Server Configuration Manager: Screen Shot 2022-09-19 at 4.18.03 PM.png

In the left pane, find and expand SQL Server Network Configuration and choose Protocols for <InstanceName>.

Right-click TCP/IP, and choose Enable: Screen Shot 2022-09-19 at 4.23.58 PM.png

Choose OK: Screen Shot 2022-09-19 at 4.26.35 PM.png

As the message above states, we'll have to restart the service.

Choose SQL Server Services in the left pane, right-click SQL Server (InstanceName), and choose Restart: Screen Shot 2022-09-19 at 4.29.47 PM.png

Configure a Fixed Port

In the left pane, find and expand SQL Server Network Configuration and choose Protocols for <InstanceName>.

In the right pane, right-click TCP/IP, and choose Properties: Screen Shot 2022-09-19 at 4.35.09 PM.png

In the TCP/IP Properties dialog box, click the IP Addresses tab.

At the bottom of the TCP Port box, there is an IPAll section. Type an available port number. For this tutorial, we will use 1433: Screen Shot 2022-09-19 at 4.40.31 PM.png

Choose OK to close the dialog box, and click OK to the warning that the service must be restarted.

Restart the service again:

Choose SQL Server Services in the left pane, right-click SQL Server (InstanceName), and choose Restart.

Open Ports in the Firewall

Find and run Windows Defender Firewall with Advanced Security: Screen Shot 2022-09-19 at 4.49.26 PM.png

In the left pane, choose Inbound Rules, right-click Inbound Rules, and then choose New Rule... in the action pane: Screen Shot 2022-09-19 at 4.54.05 PM.png

In the Rule Type dialog box, select Port, and then choose Next: Screen Shot 2022-09-19 at 4.56.22 PM.png

In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine (in my case, 1433). Choose Next: Screen Shot 2022-09-19 at 4.58.47 PM.png

In the Action dialog box, select Allow the connection, and then choose Next: Screen Shot 2022-09-19 at 5.00.14 PM.png

In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then choose Next: Screen Shot 2022-09-19 at 5.01.35 PM.png

In the Name dialog box, type a name and description for this rule, and then choose Finish: Screen Shot 2022-09-19 at 5.02.47 PM.png

Now that you have configured the Database Engine to listen on a fixed port, and have opened that port in the firewall, you can connect to your SQL Server from another computer. ๐ŸŽ‰

When the SQL Server Browser service is running on the server computer, and when the firewall has opened UDP port 1434, the connection can be made by using the computer name and instance name.

Sources

ย