Install Management Server DB and DW DB on same SQL Server

Question

Why it's not recommended to install SM DB and DW DB on the same SLQ server? The common tutorials used to install two SQL Servers. Is it possible to do so for a test environment?

Answer 1

Hi @Artimes ,

Let me start first by saying that this recommendation is important indeed and the main reason for this is performance.
There is another limitation, which concerns the management and datawarehouse management servers, but this is not what you are aksing.

Deployment scenarios for System Center - Service Manager

https://learn.microsoft.com/en-us/system-center/scsm/deploy-scenarios?view=sc-sm-2019

System Center - Service Manager provides for many deployment scenarios. However, remember that you cannot deploy a Service Manager management server and a data warehouse management server on the same computer. In fact, Setup prevents you from installing both on a single server. The reason has to do with Service Manager architecture of the data warehouse, overall performance, and usage of the Operations Manager health service. The data warehouse was designed for quick data retrieval and hosting both the Service Manager management server and the data warehouse management server on a single server will negatively impact performance for both. Additionally, a single server doesn't scale out as Service Manager usage and data storage grow.

To get back on the databases. You can install both DBs on the same SQL server. The most common constellation though is to have the databases configured in SQL AlwaysON AGs and place each primary replica on different SQL node. So that you have two nodes SQL1 and SQL2, one is primary for the SMDB and the other for the SM DW DB.

Even in a test environment you need at least two servers for SCSM (the case with one server setup actually refers to a VM, installed on this single server), so event in a test environement you have the possibility to separate the DBs and you shoul do that.

The challenge of having both DBs on the same servers is that the all the DW jobs, which are constantly runing will most probably also impact your SMDB performance. I had personally a couple of cases, where jobs were failing, because the DW DB server did have enough ressources. Still if you decide to use a common backend please consider a few important things:

• Make sure you size your management group properly:

Plan for System Center - Service Manager performance and scalability

https://learn.microsoft.com/en-us/system-center/scsm/plan-perf-scale?view=sc-sm-2019

• Make sure you size your servers as recommended:

Service Manager database performance

https://learn.microsoft.com/en-us/system-center/scsm/sm-perf?view=sc-sm-2019

• Make sure you size your DW as recommended:

Service Manager data warehouse database performance

https://learn.microsoft.com/en-us/system-center/scsm/sm-perf?view=sc-sm-2019#service-manager-database-performance

• Use separate instances for your SM DB and the DW DB if you host those on the same SQL server. This way they won't have to share the temdb.
• Use afast disk subsystem for the temdbs if you can.
• Use 64k NTFS block size on all the volumes, used for the DBs.
• Separate the temdbs, log files and data files on different volumes.
• Size your hardware properly: Plan for Service Manager hardware performance
  https://learn.microsoft.com/en-us/system-center/scsm/plan-hardware-perf?view=sc-sm-2019
• Please take a look at the factors, which influence the SCSM performance:

System Center - Service Manager performance

https://learn.microsoft.com/en-us/system-center/scsm/sm-perf?view=sc-sm-2019

I think this is it. I hope I could clarify this for you!

---

(If the reply was helpful please don't forget to upvote or accept as answer, thank you)
Best regards,
Stoyan