SQL Server

This section explains the steps to install the SQL Server 2019 Evaluation Edition on a Microsoft Windows Server 2019. To continue using SQL Server post-trial expiry, you must upgrade to the SQL Server Enterprise or Standard Edition. Read SQL Server 2019: Hardware and software requirements for the hardware and software you need to install and set up SQL Server 2019.

Note: You must have administrative rights on the Windows server to set up the SQL Server instance.

1. Download SQL Server on Windows

1. Go to Microsoft Evaluation Center.

2. In the navigation bar, click SQL Server, and select SQL Server 2019 from the drop-down.

   

3. In the SQL Server 2019 page, click the Get Started for free tab, and then click Download the EXE.

   

4. In the Evaluate SQL Server 2019 page, specify the following and click Download now:

   

   • Provide your details such as First name, Last name, and Job role.

   • Provide your company details such as the name, its size, and country/region.

   • Provide your email address that you want to register for the free trial.

5. In the Please select your SQL Server 2019 download page, under EXE download, select 64-bit edition and save the server setup file.

   

6. Proceed to install the SQL Server.

2. Install the SQL Server on Windows

1. Go to the location where you saved the SQL Server setup file. For example, C:\MSSQL.

2. Launch the SQL Server setup file, SQL2019-SSEI-Eval.exe, and in the Select an installation type window, select Basic.

   

   The Basic installation installs the SQL Server with the default configuration and starts the instance. Select the Custom installation type if you want to manually pick the components to install. You can select Download Media to download the installation file, to install the SQL Server later. Read the SQL Server installation guide for further guidance.

3. In the Microsoft SQL Server License Terms window, review and accept the license terms.

   

4. In the Specify SQL Server install location window, specify the INSTALL LOCATION, and click Install to start the installation process.

   

5. Click Connect Now if you want to connect to the installed SQL Server. This opens a command line window for testing the connection.

   

6. Click Install SSMS to install the SQL Server Management Studio (SSMS). You can use SSMS to connect to and manage your SQL Server instances and perform operations in it, such as changing the authentication method and creating login users and databases.

You have successfully installed the SQL Server and started the instance.

To set up access and allow connections to your SQL Server instance from external machines such as Hevo, you must:

1. Enable the TCP/IP protocol for the SQL Server

2. Create firewall rules

3. Create a login for the SQL Server

1. Enable the TCP/IP protocol for the SQL Server

1. Open the SQL Server Configuration Manager.

2. In the left navigation pane, click SQL Server Network Configuration, and select Protocols for <SQL Server Instance Name> from the drop-down. Default name: MSSQLSERVER.

   

3. In the right pane, if the TCP/IP Protocol is Disabled, then perform this step. Else, skip to Step 4.

   1. Right-click on TCP/IP, and select Enable.

      

   2. Click OK to acknowledge the warning.

4. Verify the TCP/IP Port:

   1. In the right pane, double-click the TCP/IP protocol name.

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

   3. Scroll to the IPAII section, and ensure the TCP Port value is 1433, which is the default port for SQL Server. Read Configure a Server to Listen on a Specific TCP Port if you want to change this value.

      

   4. Click OK, to exit the TCP/IP Properties dialog.

5. Restart the SQL Server instance if you enabled the TCP/IP Protocol in Step 3, else skip this step. To restart:

   1. In the left navigation pane, click SQL Server Services.

   2. In the right pane, right-click on SQL Server (<your_instance_name>), and select Restart.

      

You have successfully enabled TCP/IP for your SQL Server instance.

2. Create firewall rules

The Windows Firewall filters the incoming and outgoing traffic to and from your Windows server. You must create Inbound Rules for allowing Hevo to access your SQL Server database. In addition, you must open the MS SQL port if your SQL Server is installed on Windows Virtual Machine. Contact your Windows system administrator for this.

To configure the firewall, you can use the Microsoft Management Console (MMC), netsh, or PowerShell. Read Programs to configure the firewall for the steps to do this.

Here, we are using Windows PowerShell.

Run the following command in Windows PowerShell to create an inbound rule named SQLServer Default Instance:

  New-NetFirewallRule -DisplayName "SQLServer Default Instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow

The output from this command is:

3. Create a login for the SQL Server

To enable non-Windows users, such as Hevo, to connect to your SQL Server, you need to:

1. Enable SQL authentication

2. Create an SQL login with sysadmin privileges

1. Enable SQL authentication

1. Open the SQL Server Management Studio (SSMS).

2. Connect to your SQL Server using Windows Authentication. This is the default authentication method.

   

3. In the Object Explorer, right-click on your SQL server name, and select Properties.

   

4. In the Server Properties dialog, click on Security in the left pane.

5. In the Security page, under Server authentication, select SQL Server and Windows Authentication mode, and click OK.

   

6. In the Microsoft SQL Server Management Studio dialog, click OK to acknowledge the warning to restart the SQL Server.

   

7. In the Object Explorer, right-click on your SQL server name, and select Restart.

   

   The SQL Server and the SQL Server Agent are restarted.

2. Create an SQL login with sysadmin privileges

1. In the Object Explorer, right-click on your SQL server name, and select New Query.

   

2. Enter the following commands in the Query Editor pane:

   CREATE LOGIN [<master_user>] WITH PASSWORD=N'<strong password>', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
   GO
   ALTER LOGIN [<master_user>] ENABLE
   GO
   ALTER SERVER ROLE [sysadmin] ADD MEMBER [<master_user>]
   GO
   

   Replace all placeholder values in the commands above with your own. For example, <master_user> with hevouser.

3. In the Quick Access Panel, click Execute to run the commands.

   

Note: Replace the placeholder values in all the commands with your own. For example, <database_name> with MSSQLDestination.

1. Create a database in your SQL Server

1. Open the SQL Server Management Studio (SSMS).

2. Connect to your SQL Server with the SQL login created in Step 2 above.

   

3. In the Object Explorer, under your SQL server name, right-click on Databases, and select New Database.

   

4. In the New Database dialog, specify a Database name, and click OK, to create the database.

   

You can provide this database name while configuring your SQL Server Destination.

2. Create a schema in your database (Optional)

1. Log in to your SQL Server instance as a master user using an SQL client tool, such as sqlcmd. For example, sqlcmd -U hevouser.

2. Enter the following commands to access your database and create a schema in it:

   USE [<your_database_name>]
   GO
   CREATE SCHEMA [<schema_name>]
   GO
   

You can provide this schema name while configuring your SQL Server Destination.