In this article by Robert Hedblom, author of the book Microsoft System Center Data Protection Manager Cookbook, we will cover the installation and upgrade for SQL Server on DPM server. We will also understand the prerequisites to start your upgrade process.
You will learn how to:
- Install a SQL Server locally on the DPM server
- Prepare a remote SQL Server for DPMDB
(For more resources related to this topic, see here.)
The final result of an installation will never be better than the dependent application design and implementation. A common mistake discovered frequently is the misconfiguration of the SQL configurations that the System Center applications depend on. If you provide System Center a poorly configured SQL Server or insufficient resources, you will end up with quite a bad installation of the application that could be part of the services you would like to provision within your modern data center. In the end, a System Center application can never work faster than what the underlying dependent architecture or technology allows.
By proper planning and decent design, you can also provide a scalable scenario for your installation that will make your System Center application applicable for future scenarios.
One important note regarding the upgrade scenario for the System Center Data Protection Manager software is the fact that there is no rollback feature built in. If your upgrade fails, you will not be able to provide an easy approach for restoring your DPM server to its former running state. Always remember to provide supported scenarios for your solution. Never take any shortcuts because there aren’t any.
Installing a SQL Server locally on the DPM server
This recipe will cover the installation process of a local SQL Server that is collocated with the DPM server on the same operating system.
Getting ready
SQL Server is a core component for System Center Data Protection Manager. 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.
How to do it…
Make sure that your operating system is fully patched and rebooted before you start the installation of SQL Server 2012 and that the DPM Admins group is a member of the local administrators group. Now take the following steps:
Insert the SQL server media and start the SQL server setup. In the SQL Server Installation Center, click on New SQL Server stand-alone installation…
The Setup Support Rules will start and will identify any problems that might occur during the SQL server installation. When the Operation is complete, click on OK to continue.
In the Product Key step, Enter the product key and click on Next > to continue.
The next is the License Terms step where you check the I accept the license terms checkbox if you agree with the license terms. Click on Next > to continue.
The SQL server installation will verify if there are any product updates available from the Microsoft update service. Check the Include SQL Server product updates checkbox and click on Next > to continue.
Next is the Install Setup Files step that initializes the actual installation. When the tasks have finished, click on Install to continue.
Verify that all the rules have passed in the Setup Support Rule 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 the rules have passed, click on Next > to continue.
In the Setup Role step, select SQL Server Feature Installation and click on Next >.
In the Feature Selection, choose the SQL server features that you would like to install. System Center Data Protection Manager requires:
- Database Engine Service
- Full-Text and Semantic Extractions for Search
- Reporting Services – Native
As an option, you can also install the SQL Server Management Studio on the same operating system as the DPM sever. Those components are found under Management Tools, check both Basic and Complete. Click on Next > to continue.
Verify the Installation Rules step, resolve any errors, and click on Next > to continue.
In the Instance Configuration step, select Named instance and type in a suitable name for your SQL server instance. Click on the button next to the Instance root directory and select the volume that should host the DPMDB.
Click on Next > to continue
Verify that there are no problems in the Disk Space Requirement step, resolve any issues, and click on Next > 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 the Startup Type to Automatic for the SQL Server Agent. When all the credentials are filled in, click on the Collation tab.
In the Collation tab, must enter the collation for the database engine. System Center Data Protection Manager must have the SQL_Latin1_General_CP1_CI_AS collation. Click on the Customize… button to choose the correct collation and then Next > to continue.
The next step is the Database Engine Configuration step and here you enter the authentication security mode, administrators, and directories. In the Authentication Mode section, choose Windows Authentication mode. In the Specify SQL Server administrators section, add the DPM Admins group and click on the Data Directories tab to verify that all your SQL server configurations point to the dedicated disk. Click on Next > to continue.
In the Reporting Services Configuration step, configure SSRS or SQL Server Reporting Services. For the Reporting Services Native Mode choose Install and configure and click on Next > to continue.
The next step is Error Reporting. Choose the defaults and click on Next > to continue.
In the Installation and Configuration Rules step, verify that all operations pass the rules. Resolve any warnings or errors and click the Re-run button for another verification. When all operations have passed, click on Next > to continue.
Verify the configuration in the Ready to Install step and click on Install to start the installation.
The Installation Progress step will show the current status of the installation process. When the installation is done, the SQL Server 2012 Setup will show you a summary of the Complete step. That is the final step page of the SQL Server Server 2012 installation wizard.
Click on the Close button to end the SQL Server 2012 Setup.
How it works…
SQL server is a very important component for the System Center family. If the SQL server is undersized or misconfigured in any way, it will reflect negatively in many ways on the performance of the System Center.
It is crucial to plan, design, and measure the performance of the SQL server so that you know it will fit the scale you are planning for, and the workloads that it should host.
Preparing a remote SQL server for DPMDB
This recipe will cover the procedure to prepare a remote SQL server for hosting the DPMDB.
Getting ready
In the scenario where you build a large hosted DPM service solution delivering BaaS (Backup as a Service), RaaS (Restore as a Service), or DRaaS (Disaster Recovery as a Service) within your modern data center, you may want to use a dedicated backend SQL server that is either a standalone SQL server or a clustered one, for high availability.
It is not advisable to use SQL Server Always-On to host the DPMDB.
Regardless of whether you put the DPMDB on a cluster or a backend standalone SQL server, you still need to perform some initial configurations prior to the actual DPM server installation.
How to do it…
After installing your backend SQL server solution you must prepare it for hosting the DPMDB.
Insert the DPM2012R2 media and run the setup. In the setup screen, click on the DPM Remote SQL Prep link.
The installation wizard will start and install the DPM 2012 R2 Support Files; this is a very quick installation.
When the installation has finished, a message box prompts that the installation has finished and that the System Center 2012 R2 DPM Support Files have been successfully installed.
How it works…
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.
There’s more…
For the DPM server installation to be successful, when you place the DPMDB on a backend SQL server solution, you need to install the SQL 2012 SERVICE PACK 1 Tools that are located in the catalogueSCDPMSQLSRV2012SP1 directory on the DPM media.
Summary
In this article we learned how to install a SQL Server on a local DPM server, and prepared the remote SQL Server for hosting the DPMDB. We got to know the prerequisites to start the upgrade process for System Center Data Protection Manager.
Resources for Article:
Further resources on this subject:
- Mobility [article]
- Planning a Compliance Program in Microsoft System Center 2012 [article]
- Wireless and Mobile Hacks [article]
