Share via


Set up the SQL Server database for DPM

 

Updated: May 13, 2016

DPM can use a local (installed on the DPM server) or remote instance of SQL Server for its database:

  • Set up local SQL Server —On DPM 2012 R2 you’ll need to install SQL Server on the DPM server before you run DPM setup. On DPM 2012 or DPM 2012 with SP1 you have the option of installing SQL Server on the DPM server before you run DPM setup, or you can use the instance of SQL Server 2008 R2 that comes as part of the DPM installation files. If you want to use this option you don’t need to set anything up before you start DPM setup.

  • Set up remote SQL Server —If you want to use a remote instance of SQL Server you’ll need to set it up before you start DPM setup.

Set up local SQL Server

Set up a local instance of SQL Server 2012 on the DPM server as follows:

  1. Run SQL Server setup on the remote computer. On the Setup Support Files tab, click Install.

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

  3. On the Product Key tab enter a valid license key and accept the terms.

  4. On the Setup Support Rules tab, correct any failures before proceeding.

  5. On the Setup Role tab select SQL Server Feature Installation.

  6. On the Feature Selection tab select Database Engine Services. In Instance Features, select Reporting Service - Native.

  7. On the Installation Rules tab review the rules.

  8. On the Instance Configuration tab specify the name of the DPM instance. Don’t use an underscore or localized characters in the name

  9. On the Disk Space Requirements tab review the information.

  10. On the Server Configuration tab, in Service Accounts specify domain accounts that the SQL Server services should run under:

    • We recommend that you use a single, dedicated domain user account to run the SQL Server services SQL Server Agent, SQL Server Database Engine, and SQL Server Reporting Services.

    • When you create a domain user account give it the lowest possible privileges, assign it a strong password that does not expire, and give it a name that’s easily identifiable. You’ll add this account to the local Administrators group and to the SQL Server Sysadmin fixed server role later in the wizard.

    • All services except the SQL Full-text Filter Daemon Launcher should be set to Automatic.

  11. On the Database Engine Configuration tab, accept the Windows authentication mode setting. In Specify SQL Server administrators, add the user account you’ll use to connect to the remote instance when you install DPM. To add your own user account, click Add Current User. You can add additional accounts if you need to.

  12. Complete the rest of the wizard with the default settings, and on the Ready to Install tab, click Install.

Set up remote SQL Server

Set up a remote instance of SQL Server 2012 as follows:

  1. Set up remote SQL Server with the required features

  2. Install DPM support files on the SQL Server

  3. Setup firewall rules so that DPM can communicate with the SQL Server

Set up SQL Server

  1. Run SQL Server setup on the remote computer. On the Setup Support Files tab, click Install.

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

  3. On the Product Key tab enter a valid license key and accept the terms.

  4. On the Setup Support Rules tab, correct any failures before proceeding.

  5. On the Setup Role tab select SQL Server Feature Installation.

  6. On the Feature Selection tab select Database Engine Services. In Instance Features, select Reporting Service - Native.

  7. On the Installation Rules tab review the rules.

  8. On the Instance Configuration tab specify the name of the DPM instance. Don’t use an underscore or localized characters in the name

  9. On the Disk Space Requirements tab review the information.

  10. On the Server Configuration tab, in Service Accounts specify domain accounts that the SQL Server services should run under:

    • We recommend that you use a single, dedicated domain user account to run the SQL Server services SQL Server Agent, SQL Server Database Engine, and SQL Server Reporting Services.

    • When you create a domain user account give it the lowest possible privileges, assign it a strong password that does not expire, and give it a name that’s easily identifiable. You’ll add this account to the local Administrators group and to the SQL Server Sysadmin fixed server role later in the wizard.

    • All services except the SQL Full-text Filter Daemon Launcher should be set to Automatic.

  11. On the Database Engine Configuration tab, accept the Windows authentication mode setting. In Specify SQL Server administrators, add the user account you’ll use to connect to the remote instance when you install DPM. To add your own user account, click Add Current User. You can add additional accounts if you need to.

  12. Complete the rest of the wizard with the default settings, and on the Ready to Install tab, click Install.

Install DPM support files

On the remote SQL Server you’ll need to install the DPM support files (SQLprep).

  1. On the SQL Server computer insert the DPM DVD and start setup.exe

  2. Follow the wizard to install Microsoft Visual C++ 2012 Redistributable. The DPM support files will be installed automatically.

Setup firewall rules

DPM communicates with the remote SQL Server using TCP ports, and you’ll need to open these ports in the Windows Firewall on the remote SQL Server, as follows:

  • After SQL Server installation is complete make sure the TCP/IP protocol is enabled for the DPM instance of SQL Server with the following settings: default failure audit, and enable password policy checking

  • Configure an incoming exception for sqservr.exe for the DPM instance of SQL Server, to allow TCP on port 80.

  • The report server listens for HTTP requests on port 80 for HTTP requests.

  • Enable RPC on the remote SQL Server.

  • The default instance of the database engine listens on TCP port 1443. This setting can be modified.

  • To use the SQL Server Browser service to connect to instances that don’t listen on the default 1433 port, you’ll need UDP port 1434.

  • A named instance of SQL Server uses Dynamic ports by default. This setting can be modified.

You can see the current port number used by the database engine in the SQL Server error log. You can view the error logs by using SQL Server Management Studio and connecting to the named instance. You can view the current log under the Management – SQL Server Logs in the entry Server is listening on [‘any’ <ipv4> port_number]. For information about modifying default ports, see Configure a Widows Firewall for Database Engine Access in the SQL Server TechNet library.