OM12 Installation Quick Trick 03: Dedicated SQL Server Instance Or Not?

---

Postings in the same series:
01: OM12 Database Sizes & When To Set Them
02: OM12 Database Owners & Why To  Change Them

---

Many times I bump into installed OM12 environments which run on shared SQL servers. Even though it doesn’t has to be an issue on itself, many times it becomes an issue in the nearby future. Why?

1. A wrong SQL Collation setting is being used for the OM12 databases.
   In scenario’s like these the related SQL Server doesn’t host an additional SQL Server instance for OM12. Instead an existing SQL Server instance is being used for hosting the OM12 databases. Many times in situations like these the SQL Collation settings are wrong.
   
   
2. High IO/IOPS
   The Data Warehouse database used by OM12 is a special database. It’s not simply a situation where data is written to the database and that’s it. No way. Many more steps are required here in order to get some decent reports. Therefore the data gets some special ‘treatments’ when it’s present in the Data Warehouse database. This is also why all the OM12 Reports are ‘almost’ real time. Many times the latest data these Reports show are already two hours old. Simply because the ‘raw’ data – already present in the Data Warehouse database - has to be aggregated before it’s ‘report-ready’.
   
   This makes the Data Warehouse database IO intensive and also IOPS intensive. When the SQL Server hosting the Data Warehouse also hosts many other databases and SQL Server instances as well, changes are something has to give: or the Data Warehouse database has to be capped for it’s IO/IOPS hunger, or the other databases will suffer when this isn’t done.
   
   However, when the Data Warehouse database is capped, OM12 will suffer from it. Not only Report rendering will go slow, data might even be dropped simply because the Data Warehouse database is constrained too much. I have seen situations like these and they aren’t nice.
   
   
3. Licenses
   SQL Server doesn’t come really cheap. With the predecessor of OM12 this was a valid argument. So many companies opted for a shared SQL Server approach. Also because in order to keep the SQL Server licenses within budget many times the companies consolidated all SQL Servers to a few ‘beasts’. But now with System Center 2012 and the license changes, this argument isn’t 100% valid anymore.
   
   When SQL Server is solely used for any System Center 2012 product, the Standard edition is FREE. And nowadays many companies run highly virtualized environments so many times the SQL Server required for OM12 is a VM as well. And when running any hyper-visor it’s best practice to install/use the datacenter edition of Windows on those hyper-visor hosts since the VMs running on it are automatically covered by a Windows Server license.
   
   Basically meaning SQL Server AND the Windows Server OS required for a dedicated SQL Server instance for OM12 are FREE now. Even better, when opting for dedicated High Available SQL Server clusters for OM12 one can still use those free SQL Server licenses for System Center 2012. Simply because SQL Server Standard edition allows cluster up to two active nodes. And the additional SQL Server standalone instance required for the OM12 Reporting component is also free when it’s only used for System Center 2012 workloads.
   
   The only thing which remains now are the resources required for the VM(s). But it would be the same when a shared SQL Server would be used since more resources are required for running the Data Warehouse database successfully without bogging down the other databases/applications or itself.
   
   
4. Simplicity
   Many times organizations tell me they don’t want an additional SQL Server (instance) for OM12. It keeps their work simpler. Because the more servers they have to manage, the more work it costs. On itself a valid argument which can used for my approach as well.
   
   Imagine you run a shared SQL Server for hosting the OM12 databases. You experience performance issues on some other databases. How long will it take to troubleshoot, to find the culprit? And to remedy it? Suppose certain databases require a certain SP for SQL. How do you know whether it’s supported by OM12 as well? Trial and error? Or when you have to bring down the server for maintenance. It will bring down the applications depending on that very same SQL Server as well. So all those servers running those depended applications have to be stopped or treated in some kind of way as well. Or when the shared SQL Server runs multiple dedicated SQL Server instances, every instance has to be configured for CPU and RAM load in order to get the most out of it. This takes some time as well. Not only to do it but also to document it and describe the why of it all.
   
   As you can see, simplicity isn’t that simple at all. Many times people think they save themselves a lot of time only to find out they need the double amount of saved time later on in order to keep everything OK.

In environments where originally was chosen for the shared SQL Server approach it turns out – rather sooner then later – that the OM12 databases (or SCOM R2 databases for that matter) have to be moved to a dedicated SQL server. This move can be done but isn’t to be taken lightly. Not only many preparations are required but also during the move (which will take at least 4 hours) no monitoring will take place. And much of the data collected during that timeframe will be dropped.

So when you’re in the phase of designing a brand new OM12 environment, take good care about choosing the correct SQL Server instance(s). When doing it right from the start it will save you a lot of time and hassle.