Overview

I created this post to use as a reference for how my current working environment is configured, so I don’t have to repeat details in my other posts; I can just reference this post.

What we have

Our shop is predominantly Microsoft, so .NET and MSSQL, with a few others thrown in (MySQL and PHP, really, but I have almost nothing to do with them (yay)), and we’re also in the process of integrating Azure into the mix. Other than our production environment we have 2-3 non-prod tiers for almost all of our ecosystems: DEV and QA for almost all, and PLT (performance load testing) for a couple. We are a VMWare shop and aside from a few older systems almost 100% of our environment is virtualized (of the machines which can be virtualized, of course). Because of this, we have dedicated ESX hosts for all of our SQL Servers and license the hosts; this allows us to have as many VMs as we need (as an enterprise client we have Software Assurance) as well as use Enterprise Edition, which also allows us to create dedicated SQL Servers for individual ecosystems and not have to worry about the issues with sharing. We currently have around 90 SQL Server instances in production.

We also have a fairly even split between vendor- and in-house supported applications/databases; our two main systems (which I’ll reference as CV and CRM) are vendor-supported, with extensions and integrations from us. These are also our two largest systems – with BI coming in a close 3rd – with at 36 processor cores and 1TB RAM each. Because of the virtualized nature of our environment we can also do some fun things with our SAN (one of which I’ll mention in its own post).

We have a neat automated server build process, which includes standing up a VM, configuring the system to our baseline (like Page File settings), installing SQL Server, and running post-install SQL scripts (straight from TFS). One of those scripts actually creates a “phone-home” feature to automatically add the server to our inventory.

We have a mix of versions – a few SQL 2008R2 holdouts (which should hopefully be gone by end of 2017), mostly SQL 2012, a few SQL 2014, and have been starting all new installs with SQL 2016 (including a recent upgrade to our CRM!). For SQL Server HA we have some FCIs (Failover Cluster Instances), some standalone servers (using VMWare HA), and some AGs (Availability Groups). We have:

  • SQL Servers
  • Some dedicated SSIS servers (2008R2, 2012, and 2016), plus the SSIS in BI, using a mix of file-based packages as well as the SSIS catalog
  • A couple of SSAS servers (both cube and tabular models); and
  • A couple of SSRS farms, including a SSRS 2016 farm for mobile reports and KPIs

Monitoring

We use Idera’s SQL Diagnostic Manager to monitor our production and some non-prod environments, for connectivity, disk space, and SQL performance.  Our enterprise uses SCOM for most monitoring and we leverage that as a redundant monitor for connectivity (because, you know, we’re paranoid).

Backups

We use mostly Idera’s SQL Safe, but for our AGs we use Ola Hallengren’s backup process since it has proven to be much more effective at keeping the backup files centralized, regardless of what server they originate from.

Maintenance

We use  Ola Hallengren’s maintenance scripts for index maintenance, with the addition of a custom process to manage statistics (this will be shared in another post).

Specific applications

Extension databases

For a couple of our vendor applications, we need to have integration points and custom data, so we have created “extension” databases to store our custom code and data.

CRM

Our CRM environment consists of 3 servers, with 5 vendor databases (plus 2 extension databases), and SQL Server transactional replication managed by the actual CRM application.  There are also applications, services, and DLLs on the SQL Server machines, making installs and migrations more complicated than would normally be expected.

Other

There will be more to this post later, but for now this will cover for the posts I want to create :).