/
MS SQL Server

MS SQL Server

Overview

Microsoft SQL server is a database management system, with the primary function of storing and retrieving data as requested by other software applications.

Installing MS SQL

Windows

  1. Go to the following website: https://www.microsoft.com/en-us/sql-server/sql-server-downloads?rtc=1

  2. Locate which edition you would like to download and click Download Now.
    In this example, download SQL Server 2019 Developer, a free full-featured edition to use as a development and test database in a non-production environment.

    1. If you encounter the following error when downloading, A required file could not be downloaded. This could mean the version of the installer is no longer supported, please do the following:

      1. Open Powershell as administrator

      2. Run:

        Set-ItemProperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type Dword Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type Dword

      3. Retry to download and run the installer

  3. Double-click and open the downloaded installation file.

  4. Choose an installation type, specify the folder and click Install.

    Note: Choosing Custom allows for more customization for instance and user configuration.

    1. If you selected Custom, the following window appears after the installation completes. In the left-menu click Installation. Then click New SQL Server stand-alone installation or add features to an existing installation.

    2. Again, specify which SQL edition you wish to install.

    3. Click Next to continue. You’ll pass a page named Install Rules and it will test and execute rules.

    4. In the Feature Selection window, select Database Engine Services and SQL Client Connectivity SDK. Then click Next.

    5. Specify the name and instance ID for the SQL server and click Next.

    6. Change to default service configuration so that SQL Server Agent and SQL Server Browser have startup type set to Automatic. Click Next.

    7. In the Database Engine Configuration, choose the Mixed Mode authentication. Enter the password for the administrator (sa) account and click Add current user to add the host machine as an admin.

    8. After clicking Next, click Install to start the installation process. Once complete, you should see something similar to the following:

  5. You have now installed MS SQL Server.
    See the following section below to install SSMS to interact with the server: <once published, insert link to SSMS section below>

Linux

MS SQL servers only supports the following platforms: RHEL, SLES, Ubuntu, and Docker.

Ubuntu 16.04 or 18.04

  1. Import the public repository GPG keys.

    wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

    Install SQL Server 2017 or 2019.

    sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"

    If using Ubuntu 16.04, in line 2 replace .../ubuntu/18.04/... with .../ubuntu/16.04/....

  2. Update or install SQL Server.

    sudo apt-get update sudo apt-get install -y mssql-server

  3. Run the following to choose which edition to install.

    sudo /opt/mssql/bin/mssql-conf setup

    You’ll be prompted choose which edition by typing the corresponding value from [1-8].
    Then, you’ll also be asked to input a password for the SQL Server system administrator.

  4. To check the server is active, run the following command:

    sudo systemctl status mssql-server.service

  5. Install SQL Server command-line tools.

    sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - sudo curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list sudo curl -o /etc/apt/sources.list.d/mssql-server.list https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list sudo apt-get update sudo apt-get install mssql-tools unixodbc-dev

    If using Ubuntu 16.04, in line 2 replace .../ubuntu/18.04/... with .../ubuntu/16.04/....

  6. Verify installation of mssql-tools and enable to your user profile.

    sudo ls /opt/mssql-tools/bin/sqlcmd* sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd

  7. Connect by running the following command, where servername is the SQL server name (or localhost for host connection) and sa is the username.
    Notes: sa is the default admin username. For the default instance, the server name is the computer name or IP address.

    sqlcmd -S servername -U sa

Install MS SQL Service Management Studio

To interact with SQL servers, install SQL Server Management Studio (SSMS). It is a software for querying, designing and managing the server on your local computer or cloud.

SSMS supports Windows operating systems.

  1. Under the section Download SSMS, click Download SQL Server Management Studio.

  2. Once the installation file is done downloading. double-click and open it.

  3. Click Install in the Installer.

  4. Once it’s done, click Close.

  5. In the Windows search bar, type ssms and click Open.

  6. In SSMS, type the servername of the SQL server, select the authentication method, and type the username and password.
    In the example below, the server name is localhost (since the machine is the host) and authentication method is Windows, so no further input was needed.

  7. Click Connect.

AutoStart SQL Server Agent on System Startup

Windows

  1. On the host computer, in the Windows search bar, type sql server 2019 configuration manager and double-click to open.

  2. Click on SQL Server Services.

  3. Right-click on SQL Server Agent and click Properties.

  4. In the Properties window, click the Service Tab and change Start Mode to Automatic then click Ok.

Linux (Ubuntu 16.04 or 18.04)

  1. On the host computer, run the following command to check status and enable autostart:

sudo systemctl status mssql-server.service sudo systemctl enable mssql-server.service

Allow Remote Connections (Windows)

  1. Enable protocols.

    1. In the Windows search bar, type sql server 2019 configuration manager and double-click to open.

    2. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click Protocols for Instance Name.

      1. The default instance (an unnamed instance) is listed as MSSQLSERVER. If you installed a named instance, the name you provided is listed.

    3. Right-click TCP/IP, and then click Enable.

  2. Configure a fixed port.

    1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click Protocols for Instance Name.

    2. Double-click TCP/IP.

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

    4. In the TCP Port box of the IPAll section, type 1433 for a default instance or 49172 for a named instance.

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

    6. In the left pane, click SQL Server Services.

    7. Right-click SQL Server (Instance name), and then click Restart.

  3. Open ports in the firewall.

    1. In the Windows Start menu, search and click Run, type WF.msc, and then click OK.

    2. In Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.

    3. In the Rule Type dialog box, select Port, and then click Next.

    4. 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. Type 1433 for the default instance. Type 49172 if you are configuring a named instance. Click Next.

    5. In the Action dialog box, select Allow the connection, and then click Next.

    6. 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 click Next.

    7. In the Name dialog box, type a name and description for this rule, and then click Finish.

  4. Connecting from another computer.

    1. On a second computer that contains the SQL Server client tools, log in with an account authorized to connect to SQL Server, and open Management Studio.

    2. In the Connect to Server dialog box, confirm Database Engine in the Server type box.

    3. In the Server name box, type tcp:<computer name>,<port number>.
      To connect to the default instance, the port 1433 is implied; therefore, type tcp:<computer_name>.
      For a named instance, type tcp:<computer_name>,49172.

      Note: If you omit tcp: from the Server name box, then the client will attempt all protocols that are enabled, in the order specified in the client configuration.

    4. Select the authentication method and input user name and password if prompted. Then click Connect.