SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged

When we released Always On Availability Groups in SQL Server 2012 as a new and powerful way to achieve high availability, hardware environments included NUMA machines with low-end multi-core processors and SATA and SAN drives for storage (some SSDs). Performance issues surrounding Availability Groups typically were related to disk I/O or network speeds. As we… Read more

Implementing Product Catalogs in SQL Server and Azure SQL database

Product catalog is one of the key scenarios in NoSQL systems. In product catalog scenario, you need to store different types of products with different properties (e.g. phones have memory and CPU power; cars have number of doors and max speed, etc.) If you try to model this in relational database you will end-up either with… Read more

JSON is Generally available in Azure Sql Database!

JSON functionalities are now generally available in Azure Sql Database! All functions that are available in SQL Server 2016 are also available in Azure Sql Database. Azure Sql Database enables you to get values from JSON documents using JSON_VALUE function, modify values in JSON text using JSON_MODIFY function, transform JSON to table using OPENJSON function… Read more

Columnstore Index: Parallel load into clustered columnstore index from staging table

SQL Server has supported parallel data load into a table using BCP, Bulk Insert and SSIS. The picture below shows a typical configuration of a Data Warehouse where data is loaded from external files either using BCP or SSIS. SQL Server supports parallel data load. Another common scenario of loading data is via a staging… Read more

Columnstore Index: Differences between Clustered/Nonclustered Columnstore Index

SQL Server 2016 provides two flavors of columnstore index; clustered (CCI) and nonclustered (NCCI) columnstore index. As shown in the simplified picture below, both indexes are organized as columns but NCCI is created on an existing rowstore table as shown on the right side in the picture below while a table with CCI does not… Read more

Columnstore Index: Differences between Columnstore Index vs BTree index

In earlier blog why columnstore index, we had discussed what is a columnstore index and why do we need it. The columnstore storage model in SQL Server 2016 comes in two flavors; Clustered Columnstore Index (CCI) and Nonclustered Columnstore Index (NCCI) but these indexes are actually quite different than the traditional btree indexes. Here are… Read more

Increased Memory Size for In-Memory OLTP in SQL Server 2016

We are happy to announce that SQL Server 2016 removes the size limitation on user data in memory-optimized tables. You can grow your memory-optimized tables as large as you like as long as you have enough available memory. This means that with Windows Server 2016 you can leverage all 12TB (Terabytes) of available memory in… Read more

IoT Smart Grid code sample

This code sample simulates an IoT Smart Grid scenario where multiple IoT power meters are constantly sending measurements to a SQL Server 2016 in-memory database. The sample is leveraging the following features: Memory Optimized Tables, Table valued Parameters (TVPs), Natively Compiled Stored Procedures, System-Versioned Temporal Tables (for building version history), Clustered Columnstore Index, Power BI… Read more

Announcing availability of SQL Server 2014 Express Docker image

We are excited to announce the public availability of the sql server 2014 express Docker image for Windows Server Core based Containers! The public repo is hosted on Docker Hub and contains the latest docker image as well as pointers to the Dockerfile and the start PS script (hosted on Github). We hope you will find this… Read more

IoT code sample – loading messages from Event Hub into Azure SQL Database

Paolo Salvatori created an example that simulates an Internet of Things (IoT) scenario where thousands of devices send events (e.g. sensor readings) to a backend system via a message broker. The backend system retrieves events from the messaging infrastructure and store them to a persistent repository in a scalable manner. Solution has the following components: Event… Read more