• Article

Install and Configuring a Remote SQL Server

 

Updated: May 13, 2016

DPM 2012 R2 requires a preinstalled SQL Server 2008 R2 SP2 or SQL Server 2012 SP1 instance to be specified during setup. The SQL instance can be installed on the local computer, on a remote standalone computer or on a clustered SQL server. The steps below provide an example of the steps necessary to install and configure a remote SQL instance using SQL Server 2008 R2 Service Pack 2.

Note for using a remote instance of SQL Server for DPM

  • The remote instance of SQL Server must be running SQL Server 2008 R2 with Service Pack 2 or later, or SQL Server 2012 SP1, Standard or Enterprise Edition.

  • The remote instance of SQL Server cannot be installed on a domain controller.

  • The computer running SQL Server must be located in the same domain as the DPM server.

  • For the DPM server to access a remote instance of SQL Server through Windows Firewall, on the remote computer you must configure an incoming exception for SQLservr.exe for the specific instance used so that the necessary TCP protocol ports are not blocked. This is covered in detail in the section below titled Setting SQL Firewall rules on a Remote SQL Server.

Installing the remote SQL server

  1. On the product media or network share for the SQL Server 2008 installation media, double-click setup.exe and then in the SQL Server Installation Center, click Installation in the navigation pane.

  2. On the Installation page, click New SQL Server stand-alone installation or add features to an existing installation.

  3. On the Product Key page, enter a valid license key, and then Next.

  4. On the License terms, accept the terms, and then Next.

  5. On the Setup Support Files page, click Install.

  6. On the Setup Role page, select SQL Server Feature Installation and then click Next.

    • Database Engine Services and both sub features, SQL Server Replication and Full-Text Search.

    • Reporting Services

    • Management Tools – Basic (also enables the Management Tools- Complete feature)

    • SQL Client Connectivity SDK

  7. Review the Installation Rules and then click Next.

  8. On the Instance Configuration page, click Named instance, enter a name for the instance of SQL Server and then click Next.

    NOTE Avoid instance names used by previous DPM installation instances like MSDPM2010, MSDPM2012 etc. Make the instance name unique but intuitive like DPMSQLDB.

  9. Review Disk Space Requirements page, then click Next.

  10. On the Server Configuration page, use the following table to configure the SQL Server services.

    SQL Server service Configuration
    SQL Server Agent Enter the name and password of a domain account that you want this service to run under, and then set the Startup type to Automatic.
    SQL Server Database Engine Enter the name and password of a domain account that you want this service to run under. Keep default Startup type of Automatic.
    SQL Server Reporting Services Enter the name and password of a domain account that you want this service to run under. Keep default Startup type of Automatic.
    SQL Full-text Filter Daemon Launcher Do not change the default setting of Manual
    SQL Server Browser Change the default Startup type to Automatic.

    NOTE It is recommended to use a single, dedicated domain user account to run SQL Server Agent, SQL Server Database Engine, and SQL Server Reporting Services.

  11. On the Database Engine Configuration page, accept Windows authentication mode as the authentication mode, and then in the Specify SQL Server administrators area, add the SQL Server administrators group the user account that you will use to connect to the remote instance of SQL Server when you install DPM. To add your own user account, click Add Current User. You can add additional user accounts if needed.

    NOTE Ensure that the user account you use is added to the SQL Server administrators group and to the Local Administrators group on the SQL Server.

  12. Retain the default settings on all subsequent pages, and then on the Ready to Install page click Install.

Installing the DPM support files on the remote SQL Server

Install the DPM support files (SQLprep) on the remote SQL Server computer by performing the following steps. If the remote SQL Server is a cluster, install the DPM Support tools on all nodes of the cluster.

  1. On the remote SQL Server computer, insert the DPM product DVD and start setup.exe. On the DPM Installation menu, click on the DPM remote SQL Prep option.

  2. Follow the steps in the wizard to install the Microsoft Visual C++ 2010 Redistributable package.

  3. The DPM support files will be installed automatically.

Setting SQL Firewall rules on a Remote SQL Server

DPM needs to be able to communicate to the remote SQL Server using a few TCP protocol ports. By default, the report server listens for HTTP requests on TCP port 80. The default instance (MSSQLSERVER) of the Database Engine always uses TCP port 1433, however that can be changed.

The SQL Server Browser service allows users to connect to instances of the Database Engine that are not listening on port 1433 without knowing the port number. To use SQL Server Browser, you must open UDP port 1434. On the other hand, a named instance of SQL Server (IE: DPMSQLDB) uses Dynamic ports by default. This can also be changed if desired.

In any case, the current port number used by the Database Engine is listed in the SQL Server error log. You can view the error log using SQL Server Management Studio and connecting to the named instance, then viewing the current log under Management -> SQL Server Logs. Look for "Server is listening on ['any' <ipv4> XXXXX]. Where XXXXX is the port number.

If you want to change the TCP port that the SQL Database Engine is listening on, you can do that using the SQL Server Configuration Manager tool:

SQL Server Configuration Manager

Assigning a TCP/IP port number to the SQL Server Database Engine

  1. Within the console pane in SQL Server Configuration Manager, expand SQL Server Network Configuration, then expand Protocols for <instance name> and double-click TCP/IP.

  2. In the TCP/IP Properties dialog box on the IP Addresses tab, several IP addresses will appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses will appear for each IP address on the computer. Right-click each address and then click Properties to identify the IP address that you want to configure.

  3. If the TCP Dynamic Ports dialog box contains a 0 value, indicating the Database Engine is listening on dynamic ports, delete the value.

  4. In the IPn Properties area box in the TCP Port box, type the port number you want that IP address to listen on and then click OK.

  5. In the console pane, click SQL Server Services.

  6. In the details pane, right-click SQL Server (<instance name>) and then click Restart to stop and restart SQL Server.

The screen shot below shows Dynamic port 49298 being used. If you wanted to change it to be a static port, you would simply remove the Dynamic port number and enter a static port number under the TCP port.

Now that you know the ports used, you can create the necessary firewall rules to allow communication using those ports.

Opening a static port in Windows Firewall for TCP access

To open the necessary static ports on the remote SQL Server computer, complete the following:

  1. On the Start menu, click Run, type WF.msc, and then click OK.

  2. In Windows Firewall with Advanced Security, on 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, such as 1433 for the default 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.

Opening dynamic ports in Windows Firewall for TCP access

If using dynamic ports on the remote SQL Server computer, complete the following:

  1. Open Services.cpl and locate the SQL Server (Instance_name), then double-click to get the Path to executable.

  2. Copy the path (e.g. C:\program files\Microsoft SQL Server\MSSQL11.Instance_name\MSSQL\binn\SQLservr.exe)

  3. On the Start menu, click Run, type WF.msc, and then click OK.

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

  5. In the Rule Type dialog box, select Program, and then click Next.

  6. In the Program dialog box, select This program path. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall and select the SQLServr.exe and click Next. By default, SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQL11.Instance_name\MSSQL\Binn\SQLservr.exe.

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

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

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

NOTE When installing DPM and using a SQL cluster, the remote SQL Reporting Server must have port 80 opened so that the DPM server can connect to SQL reporting during and after setup.

Opening ports in Windows Firewall using the command line

As an alternate method, you can use the command line below to open the required ports. This will work with both static and dynamic ports. Be aware that certain parameters in the example below may need to be modified for your particular environment.

Netsh advfirewall firewall add rule name=DPM_SQLServr.exe dir=in action=allow program=\"%PROGRAMFILES%\Microsoft SQL Server\MSSQL11.INSTANCE_NAME\MSSQL\binn\SQLservr.exe\" profile=Domain

Netsh advfirewall firewall add rule name=DPM_UDP_Port_1434 dir=in action=allow protocol=UDP localport=1434 profile=Domain

Netsh advfirewall firewall add rule name=DPM_TCP_Port_80 dir=in action=allow protocol=TCP localport=80 profile=Domain