The ever expanding SQL Server family

 Gavin Payne is a principal architect for Coeo, a SQL Server and Azure professional services company, and a Microsoft Certified Architect and Microsoft Certified Master.  His role is to guide and lead organisations through data platform transformation and cloud adoption programmes.  

SQL Server isn’t just the name of Microsoft’s relationship database engine, it’s also the name on the box that now contains over a dozen Microsoft data-centric products. Learning just how broad and deep this technology toolbox is should help stop us re-inventing the wheel - or worse, trying to make do with the wrong tool for the job.

One box, one licence, fifteen products

When SQL Server first shipped, it had a single service – the relationship database engine. Then, Microsoft began adding extra services into the SQL Server box. First came the analytical services that went on to become SSAS; next the reporting services that became SSRS; then SSIS and so on. Roll forward to SQL Server 2016 and we now find fifteen data-centric products in the SQL Server family:

  • SQL Server database engine
  • In-memory OLTP engine
  • In-memory analytics engine
  • PolyBase integration services
  • SQL Server Data Tools
  • SQL Server Integration Services
  • SQL Server Analysis Services - Multi-dimensional model
  • SQL Server Analysis Services - Tabular model
  • SQL Server Reporting Services
  • Power Pivot for SharePoint
  • Power View for SharePoint
  • Master Data Services
  • Data quality Services
  • StreamInsight
  • Datazen

How you define individual products within a big technology toolbox is always debatable. I’ve taken a marketplace led approach and identified SQL Server functionality that other vendors provide as individual products. While that creates a relatively long list, it’s a helpful reminder of how much functionality we get from a single vendor and a single SQL Server licence.

Using the right SQL Server tool for the job
With such a wide range of functionality available in the SQL Server family today, we should now always be able to architect and implement solutions using the right tool. That might explain why there’s so much business intelligence and data analytics functionality packed into SQL Server. The increase in BI business requirements in the last decade has without doubt justified the creation of a new generation of SQL Server functionality.

Broad functionality brings learning opportunities
Knowing about every member of today’s SQL Server family can easily overwhelm us. Their capabilities span from high performance transactional data processing, to data mining, to end-user graphical dashboards. In fact, being an expert in more than a small handful of product areas is now considered challenging and something few can claim to be.

However, not everyone needs a deep knowledge of every feature. For one organisation I’ve worked with, just learning how much functionality Microsoft provides with a single SQL Server licence helped them standardise on a Microsoft data platform.  For others, it’s stopped them re-creating functionality in one part of the product that was available in another (for example transactional replication vs. SQL Server Integration Services).

For help with your SQL Server 2016 learning, whatever your experience, I recommend the following Microsoft resources:

Comparing the features available in the different editions of SQL Server The index of the SQL Server 2016 edition of Books Online