SQL considerations for DPM

The heart of a System Center product has always been and will always be SQL Server. This is why its so important to setup SQL Server optimal so your DPM server will deliver and optimal experience and make backups, but most importantly restore that works.

There are some key findings I always do when performing health checks on DPM (or other System Center products) and to help the DPM community I would like to share my recommendations with you.

First of is “Supported Scenarios”. This is actually a super simple thing but often missed out which leads to the consequences of you not being able to get support from Microsoft if you need it…ouch. Setting up SQL there is only one supported SQL collation, that is SQL_Latin1_General_CP1_CI_AS never deviate from this because then you deviate from Microsoft support.

Second, clustered SQL servers. Microsoft supports your DPM databases to run on remote SQL server that are clustered. However, a clustered SQL server is NOT the same as a SQL Always On setup. The latter (typing it out just in case…SQL Always On) is not supported. Now…even if its supported to run DPM on a remote SQL server this is probably the worst idea ever, don’t do it. The reason is simple, there are more unnecessary components to your DPM solution that only adds complexity…why and how is that a good idea? Guess what, it isn’t. I have had clients wanting to put the DPMDB on their production SQL hotel since this is the company standard. Don’t get me wrong, its great to standardize but you must consider the impact. A backup solution should always be “looking into” your production environment, never be part of it. Simple question, how should you restore your data when your SQL Hotel is down…guess what, you don’t.

Third, use Service Accounts (gMSA). Throughout the years people have setup their SVC accounts which is great but they all fall on the same step, changing password for all SVC accounts. “Well that is a lot of hard work so we just setup an account and make a long and complex password”, that’s the approach for to many companies. How could that possibly be a good idea? The correct setup is to leverage and use gMSA accounts that your define using PowerShell and associates with your SQL Server that hosts the DPM database.

Forth, maintenance. Always (well I have had one client that made it right) I find SQL Server not having any maintenance plans for the DPM database. Now, its important to know that from a SQL Server perspective your DPMDB is fine and consistent (DBCC check) and also your index is not to high (Re.Org Index). Those are the two most basic jobs that you must setup if you want a healthy DPMDB and a continues nice experience.

Fifth, Disk setup. Use a dedicated disk for the DPMDB that has 64K sector size. Don’t mix all the SQL Server components on the same drive as the %SystemDrive%, at least make a dedicated drive for the DB that also contains LOG, backup and TempDB.

There are more to it but this is a great starting point for you.