Modern data engineering with MS SQL Server?

Microsoft SQL Server is not the first tool that comes to mind when discussing data processing as you do via platforms like Hadoop, Databricks, or Snowflake.

However, as someone who has architected a couple of data-intensive systems, I'd like to make a case for SQL Server, which offers surprising benefits.

---
TLDR

MS SQL Server offers:
- Integration via PolyBase to query external systems like Hadoop or MongoDB
- In-memory processing and column store indexes for high-performance analytics.
- Support for real-time data through Change Data Capture (CDC) and temporal tables.

---

SQL Server supports both transactional and analytical queries. It is a unified environment that can handle different use cases without requiring multiple tools.

One of my favorite features is PolyBase. It allows SQL Server to query external data sources. For instance, you can query large datasets stored in Hadoop without moving the data.

Similarly, the integration with Azure Blob Storage opens up opportunities to tap into modern cloud storage solutions.

Other factors include the introduction of in-memory OLTP and column store indexes. We can perform analytical queries with reduced latency and do so with large datasets.

Does it mean you can forget about schema and data product optimization? Not really, but you get that near-instant feedback even for sub-optimal datasets.

CDC is my go-to for tracking changes in source systems, and temporal tables enable time-travel queries. With it, we see how data has changed over time without resorting to complex workflows.

Always Encrypted and row-level security ensures that sensitive data remains protected. Pairing these with audit logs and GDPR-friendly features like data classification gives us compliance with regulatory requirements.

SQL Server Integration Services (SSIS) is also available. It provides a platform for building data workflows that can handle complex transformations, data cleaning, and integration tasks.

I’ve used SSIS to orchestrate batch processing pipelines that moved and transformed large volumes of data across systems. Its visual interface is drag-and-drop, and for more advanced scenarios, you can always resort to scripting.

While big data platforms excel in handling massive, distributed datasets, they often lack the elegance of SQL Server’s tools for managing relational data.

Finally, SQL Server's ecosystem is one of its strongest selling points. The tools for automated backups and performance monitoring are nice.

In summary, while it’s easy to overlook SQL Server in this age of distributed computing and specialized big data platforms, I’ve found it to be a nice general-purpose database that can handle more than usual.