One of my banking clients has a large SQL Server farm which hosts support data. Another client uses SharePoint as ECM (Enterprise Content Management) system which relies on underneath SQL Server farm. Both are dependent on the infrastructure operating 24*7*365, both have strong needs to monitor the performance of SQL Server farm.
Let’s see how to build performance monitoring layers for a SQL Server farm. Though this might not be the best approach, it can be a great reference based on technical implementation feasibility under existing IT environment.
A picture is worth a thousand words. Let’s assume that we utilize NetApp as storage, VMware ESXi (vs. Hyper-V) as bare-metal OS. The guest OS is Windows Servers on which SQL Server instances run. As illustrated, scale-out (vs. scale-up) approach is chosen for consolidating SQL Servers on ESXi because it offers faster migration, better isolation, more efficient disaster recovery and easier change management.
On storage layer, NetApp OnCommand Balance (OCB) routinely collects configuration and performance statistics information from monitored data center facilities.
On Network layer, ipswitch’s WhatsUp Gold is used to monitor what’s up, what’s down instantly.
On ESXi level, we have vSphere Client, Web Client, Windows terminal services, Xterm to monitor performance.
On Guest OS layer, here it’s Windows, we have Windows Server Manager and Windows Performance Monitor.
On application layer, we have vCenter to provide statistic information of resource usage, and provision the ability to scale and adjust the compute, memory, storage and other resource management functions. Also for SQL Server performance monitoring, we have Idera SQL Diagnostic Manager and whole bunch of other tools to choose, such as SolarWinds DB Performance Analyzer and SentryOne SQL Sentry, etc.
So far we’ve got enough to play with, but there’s ‘one more thing’: Splunk. Splunk collects log information from every device or application, including application, host OS , virtual machine, network and storage devices, etc. There are a lot of add-on apps available for Splunk, or if you want, you may build customized apps to monitor SQL Server farm exclusively.
Lionsgate Software has rich experience building these solution designs. Should you have any needs, please do not hesitate to contact us.
