Installing SQL Server for the DPM database

This recipe will cover the installation process in two scenarios:

• Local SQL Server instance
• Remote SQL Server instance

Getting ready

SQL Server is a core component and is required for the System Center Data Protection Manager database. It is of major importance that the installation and design of SQL Server is well-planned and implemented. If you have an undersized installation of SQL Server, it will provide you with a negative experience while operating the System Center Data Protection Manager.

Starting with DPM 2012 R2 and later, SQL Server is no longer a part of the installation media for DPM, which is a good thing. The majority of users need to understand SQL more and also understand that if you have a poorly set-up SQL Server, you will have a bad experience with the product hosting its database on that SQL Server. Remember to set up your SQL Server using domain service accounts, use a dedicated disk for the DPM database, and keep monitoring SQL's performance with a proactive monitoring approach.

The following requirements are recommended for a SQL Server database:

• RAM: 8 GB
• Disk: 3 GB
• Required features: Database Engine Services, Reporting Services
• Collations: SQL_Latin1_General_CP1_CI_AS
• AlwaysOn: Not Supported
• Clustered SQL Server: Supported 

How to do it...

The following steps will cover the installation process of a local SQL Server that has been collocated with the DPM server on the same operating system.

Option 1 – local SQL Server instance

Make sure that your operating system is fully patched and that is has been rebooted before you start the installation of SQL Server 2016. Now, follow these steps:

1. Insert the SQL Server 2016 media and start the SQL server's setup. In the SQL Server Installation Center, click on Installation and click on New SQL Server stand-alone installation…
2. The Setup Support Rules will start and will identify any problems that might occur during the SQL server's installation. When the operation is complete, click on OK to continue.
3. In the Product Key step, enter the product key that ships with SQL server license and click on Next to continue.
4. The next step is the License Terms step, which is where you check the I accept the license terms checkbox if you agree with the license terms. Click on Next to continue.
5. The SQL Server installation will verify whether there are any product updates available from the Microsoft Update service. Check the Use Microsoft Update to check for updates (recommended) checkbox and click on Next to continue.
6. Select the Include SQL Server product updates checkbox and click on Next to continue:

7. Next is the Install Setup Files step, which initializes the actual installation. When these tasks have finished, click on Install to continue.
8. Verify that all of the rules have passed in the Install Rules step of the SQL Server installation process. Resolve any warnings or errors and click the Re-run button to run the verification again. If all of the rules have passed, click on Next to continue.
9. In Feature Selection, choose the SQL Server features that you would like to install. System Center Data Protection Manager requires the following:
   • Database Engine Service
   • Full-Text and Semantic Extractions for Search
   • Reporting Services – Native

10. Click on Next to continue:

11. Verify the Installation Rules step, resolve any errors, and click on Next to continue.
12. In the Instance Configuration step, select Named instance and type in a suitable name for your SQL Server instance. Click on Next to continue:

13. In the Server Configuration step, type in the credentials for the dedicated service account you would like to use for this SQL Server. Switch the Startup Type to Automatic for the SQL Server Agent. When all of the credentials have been filled in, click on the Collation tab:

14. In the Collation tab, make sure that SQL_Latin1_General_CP1_CI_AS is listed in the Database Engine Configuration. If not, then click on the Customize… button to choose the correct collation and then click on Next to continue.
15. The next step is the Database Engine Configuration step. Enter the authentication security mode, administrators, and directories. In the Authentication Mode section, choose Windows Authentication mode. In the Specify SQL Server administrators section, click the Add... button and add the DPM Admins group into Active Directory:

16. Click on the Data Directories tab as well as the TempDB tab to verify that all of your SQL Server configurations point to a dedicated disk. Click on Next to continue.
17. In the Reporting Services Configuration step, under the Reporting Services Native Mode, choose Install and configure and click on Next to continue.
18. Verify the configuration in the Ready to Install step and click on Install to start the installation.
19. The Installation Progress step will show you the current status of the installation process. When the installation has completed, SQL Server 2016 Setup will show you a summary of the Complete step. That is the final step page of the SQL Server Server 2016 installation wizard.
20. Click on the Close button to end SQL Server 2016 Setup.

After installing SQL Server, please make sure that you install an important update for SQL Server 2016 RTM (KB3210111). Please note that, starting with SQL Server 2014 and later, SQL Server Management Studio (SSMS) is not part of the installation media and you need to download it separately. 

At the time of writing this book, SSMS version 17.8.1 is the latest generation of SQL Server Management Studio that supports SQL Server 2017. However, if you install SSMS version 17.X and later on the same OS, DPM installation will fail with the following error: An unexpected error occurred during the installation – ID: 4387. Alternatively, you can download SSMS version 16.5.3 from the following link, which will work side-by-side with DPM: http://go.microsoft.com/fwlink/?LinkID=840946.

Option 2 – remote SQL Server instance

When you need to build a large hosted DPM solution within your modern datacenter, you may want to use a dedicated backend SQL Server that is either a standalone SQL Server or a clustered one, for high availability. This step will cover the procedure to prepare a remote SQL server for hosting the DPM database.

After installing your backend SQL Server, you must prepare it for hosting the DPM database. Now look at the following steps:

1. Insert the DPM media on the SQL Server and run the setup. In the setup screen, click on the DPM Remote SQL Prep link:

2. The next step is to go through the Microsoft Software License Terms, where you must check the I accept the license terms and conditions checkbox if you agree with the license terms. Click on OK to continue.
3. The installation wizard will start and install the DPM Support Files; this is a very quick installation.
4. When the installation has finished, a message box appears that informs you that the installation has finished and that the System Center DPM Support Files have been successfully installed:

The support files for SQL Server will be installed on the backend SQL Server box and will be used when the DPM server connects and creates its database.

How it works...

SQL Server is a very important component for System Center Data Protection Manager. If the SQL Server is undersized or misconfigured in any way, it will reflect negatively on the performance of DPM.

It is crucial to plan, design, and measure the performance of SQL Server before you install the DPM server so that you know it will fit the scale you are planning for and the workloads that it should host.