It is not always easy to divide Low-level Design and High-Level Design. How to differentiate between high-level and low-level design depends on the entity that we refer to. When we need to design our cloud then high-level decision could be for example choice of the technology that makes our SQL Servers highly-available. But when we talk about our business or company (the entity) then SQL Servers are in Low-level Design category. This means that following division to High-level Design and Low-level Design are only within our entity.
High-level Design
I cannot guide you how to do the high-level design of your SQL but I can pick a several questions that architect should ask.
Architectural design questions you need to ask
Standard vs. Highly-Available (Fault-tolerant) deployment
- Everything begins and ends with SQL Servers so even when you will not install for example clustered VMM you should consider to use clustered SQL.
AlwaysOn Availability Groups
- It is a good idea to use AlwaysOn Availability Groups.
- AlwaysOn Availability Groups (AG) are not difficult to deploy and for a small cost makes you SQL Server environment fault-tolerant and reliable.
- You can have multiple copies of your DBs on a different geographic locations and you do not have to use the storage replication that is requirement for the standard SQL cluster (SQL Server AlwaysOn Failover Cluster Instances) with nodes on a different geographic locations.
- Not all of the System Center 2012 R2 products supports SQL Server AlwaysOn Availability Groups.
Replicas
- Advice to the Architects: Discuss with your DB Architects how many Replicas you need. Also evaluate configuration of your Replicas.
Replicas on the Microsoft Azure
- When deploying replicas to Azure, you should use asynchronous commit instead of synchronous commit for the synchronization mode. When deploying database mirroring servers both on-premises and in Azure, use the high performance mode instead of the high safety mode.
AlwaysOn Availability Groups support
Full support
- System Center App Controller
- System Center Operations Manager
- System Center Orchestrator
- System Center Virtual Machine Manager
Limited support
- System Center Service Manager
- SCSM support AlwaysOn Available Groups but the Available Groups have to be created on the default instance. It is not possible to install SCSM on Available Groups created on the named instance.
No support
- System Center Data Protection Manager
- System Center Configuration Manager
How many SQL Server deployments do you need?
By deployments I mean for example whole clusters or whole VMs with standalone installation of the SQL.
Input / Output Operations per Second (IOPS)
- Consider the amount of IOPS that the System Center products will cause by querying the DB server. Most active are usually Service Manager, Operations Manager and Configuration Manager. but of course that depends on the amount of the clients, configuration and your environment.
My recommendation
- I saw a large deployments of the System Center that was using a single SQL Server deployment. And that was enough.
- Try to install guest cluster (virtual cluster) for your SQL Servers and test it in the Proof of Concept (PoC) environment.
- Unless you are building a very large cloud you should be fine with the single deployment of the SQL Server on the several virtual machines because in the future you can easily scale those machine and for example leave only a single VM on the whole Hyper-V host (for example on the whole blade server with 16 CPU cores and 4 x 2 x 16 GB = 128 GB of memory). Virtualization helping us with this scalability so there is no reason not to use it.
How many instances do you need?
In the testing environment it is not a big problem to run all databases on a single instance but that is not something that you want to see in the production environment.
Reporting Services
- Reporting Services are not shared. You need three separate SQL Server Reporting Services instances for following System Center products:
- Operations Manager
- Service Manager
- Configuration Manager
Single instance for every database
- That is not a mistake. Some of the System Center products are using two databases to divide the latest data and the older data that you need to keep.
- You consider to separate Data Warehouse database of the Operations Manager and Data Warehouse of the Service Manager.
Single instance for every System Center products
- Consider this possibility. It will simplify your management and maintenance.
My recommendation
- Install separate instances for the Operations Manager, Service Manager and Configuration Manager and one instance for the rest of the System Center products and test it in the PoC (Proof of Concept) environment.
Low-level Design
Questions you need to ask
To get more information please continue to the SQL Server AlwaysOn Availability Groups series.
FILESTREAM
- System Center Data Protection Manager 2012 R2 supports SQL Server FILESTREAM.
