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.
Note
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:
- Insert the SQL Server 2016 media and start the SQL server's setup. In the
S
QL Server Installation Center
, click onÂInstallation
 and click onNew SQL Server stand-alone installation…
- 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 onOK
to continue. - In the
Product Key
step, enter the product key that ships with SQL server license and click onNext
 to continue. - The next step is the
License Terms
step, which is where you check theI accept the license terms
checkbox if you agree with the license terms. Click onNext
 to continue. - 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 onNext
 to continue. - Select the
Include SQL Server product updates
checkbox and click onNext
 to continue:
- Next is the
Install Setup Files
step, which initializes the actual installation. When these tasks have finished, click onInstall
to continue. - 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 theRe-run
button to run the verification again. If all of the rules have passed, click onNext
 to continue. - 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
- Click on
Next
to continue:
- Verify the
Installation Rules
step, resolve any errors, and click onNext
 to continue. - In the
Instance Configuration
step, selectNamed instance
and type in a suitable name for your SQL Server instance. Click onNext
 to continue:
- In the
Server Configuration
step, type in the credentials for the dedicated service account you would like to use for this SQL Server. Switch theStartup Type
toAutomatic
for theÂSQL Server Agent
. When all of the credentials have been filled in, click on theCollation
tab:
- In the
Collation
tab, make sure thatÂSQL_Latin1_General_CP1_CI_AS
 is listed in theDatabase Engine Configuration
. If not, then click on theCustomize…
button to choose the correct collation and then click onÂNext
to continue. - The next step is the
Database Engine Configuration
step. Enter the authentication security mode, administrators, and directories. In theAuthentication Mode
 section, chooseWindows Authentication mode
. In theSpecify SQL Server administrators
section, click theÂAdd...
button and add theDPM Admins
 group into Active Directory:
- 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 onNext
 to continue. - In the
Reporting Services Configuration
step, under theReporting Services Native Mode
, chooseInstall and configure
 and click onNext
to continue. - Verify the configuration in the
Ready to Install
step and click onInstall
to start the installation. - 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 theComplete
step. That is the final step page of the SQL Server Server 2016 installation wizard. - Click on the
Close
button to endSQL Server 2016 Setup
.
Note
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:
- Insert the DPM media on the SQL Server and run the setup. In the setup screen, click on the
DPM Remote SQL Prep
link:
- The next step is to go through theÂ
Microsoft Software License Terms
, where you must check theI accept the license terms and conditions
checkbox if you agree with the license terms. Click onOK
to continue. - The installation wizard will start and install the
DPM Support Files
; this is a very quick installation. - 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:
Note
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.