Hybrid Hyperscale using SQL Server 2016

Overview

One of the major trends in the last few years has been a huge explosion in amount of data being generated by OLTP systems often coupled with the need to retain ever longer durations of historical data that needs to be made available for querying on demand. In order to meet customer needs in terms of data retention and performance, hosted database implementations had to choose between the following less than optimal options:

  • expanding and managing local storage which led to increasing cost
  • archive less frequently queried data into offline backup systems like tapes which prevents on-demand querying
  • simply put in place more aggressive data retention policies that reduced the need for this data to be stored in the first place leading to data loss

SQL Server 2016 introduces a number of major capabilities which enable seamless scale-out of SQL Server for low cost storage which is available for anytime On-Demand querying using Azure. With Azure's almost limitless storage, hosters can offer low cost hyper-scale benefits to their SQL Server implementations for their customers. In this document, detailed technical guidance is provided in the context of a customer scenario on how to implement a hyper-scale database offering. For the implementation, the following SQL Server 2016 capabilities are leveraged

  • Stretched database and Azure Stretch database service
  • AlwaysOn Availability Groups with asynchronous replica in Azure
  • In-Memory ColumnStore
  • Temporal Database

The following sections explain these capabilities and also enumerate considerations that the Hosting Service Provider (HSP) database administrator has to keep in mind while implementing this offer.

DBA Considerations

In order to implement a Hyperscale database on SQL Server 2016 database administrators:

  • Have an in depth understanding of the customer's data retention, data growth and query access patterns including an understanding the relational structure of the database tables as explained below:
    • Data Retention: Understand key tables and the duration of data that the customer needs these to be made available
    • Data Growth: Understand the historical and projected growth of data in these tables
    • Query Patterns: Understand the query patterns associated with these tables for OLTP transactions and reporting/analysis if any.
    • Develop a projection of the of the storage and compute needed for the customer applications to support the query patterns with the expected data growth

SQL Server 2016 capabilities for a Hyperscale database implementation

Always On Availability Groups

AlwaysOn Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. AlwaysOn Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes. In SQL Server 2016, AlwaysOn availability groups can be setup with up to 3 synchronous replicas and up to 8 asynchronous replicas. The asynchronous replicas can be setup in Azure. Hosting Service Providers can provide Azure services to their customers through the Microsoft Cloud Solution Provider (CSP) Program.

For more information on availability groups, see AlwaysOn Availability Groups (SQL Server)

In Memory Column Store Indexes

The column store index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size. Beginning with SQL Server 2016, column store indexes enable operational analytics, the ability to run performant real-time analytics on a transactional workload.

For more information, see ColumnStore Indexes Guide.

Stretch Database

Stretch Database migrates historical data transparently and securely to the Microsoft Azure cloud. It provides cost-effective availability for cold data and doesn't require changes to queries or applications. It streamlines on-premises data maintenance and keeps your data secure even during migration. After you enable Stretch Database for a SQL Server instance, a database, and at least one table, it silently begins to migrate your historical data to Azure. The Stretch Database can be made available to the customer by the hosting service provider via the Microsoft Cloud Solution Provider (CSP) program.

For more information, see Stretch Database.

Temporal Tables

Temporal tables feature brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution, which is facilitated by temporal tables, such as in case of calculating trends over time, auditing data changes and performing data forensics, reconstructing data state at any time in the past and so on. For more details, check here.

In the rest of this document, we focus on implementing the hyperscale database using the above capabilities in the context of a customer scenario.

Example Scenario

An on-line retail shopping cart hosted application is developed by FabrikamOnline.com with millions of world-wide customers. Database tables are constantly increasing in size with new orders and existing order data is being updated as the orders are being processed. A reporting application must compute lifetime purchase amounts for each of the customers to allow the generation of discounts and promotions tailored for each customer. Customer Service Representatives must access both active orders as well as a customers' historical information in real-time as they are supporting customer issues.

FabrikamOnline.com is faced with two key challenges:

  • The lifetime purchase amount computations are making the database very slow
  • The order data is growing beyond the storage and performance capabilities of the existing system

Database Design

The following sections explain the relevant tables and their schema in details.

FabrikamOnline.com Transaction Table Relationships

There are three main tables that need to be considered. The relationship between the tables is as follows:

Customer Table

Customer Order Table

Order Line Item Table

While the Customer table is growing, it is the explosive growth in the Customer Order and Order Line item tables that is driving the need for additional storage. The query patterns on these tables are as follows:

  • The OLTP system inserts new purchase transactions into the tables 24x7
  • Customer Service Representatives (CSR) access these tables via their support application which needs to query these tables for customer specific queries like status of a particular order or all orders that have been completed and so on.
  • The analytics engine responsible for computing the lifetime value of the customer's purchases queries the entire table for the entire history

The sample queries for these are given later in the document.

Solution Design

HSP DBA arrives at an optimal design to address all the pain points to implement the hyperscale database as follows:

  • The Customer, CustomerOrders and OrderLineItem tables will be tracked for all historical changes using the Temporal Database capability
  • The history tables will all be stretched into Azure for potentially infinite online storage
  • Add an asynchronous replica in Azure to the Availability Group
  • Customer, CustomerOrder and OrderLineItem will be implemented in In-Memory will include InMemory ColumnStore indexes on all columns to allow for very efficient real time database queries from the Read Only Azure replica database.
  • Completed orders will be deleted from the in memory tables once the order is complete and will only hold active orders

The following diagram shows the reference architecture for this implementation

Implementation Steps

Creating the Sample Memory Optimized Customer Database

Once the updated SQL environment has been provisioned both on-premises and in the Azure subscription, the database and resources can be created.

USE [master]

GO

-- Create the Shop Online Database

CREATE DATABASE [FabrikamOnlineDb]
CONTAINMENT = NONE
ON PRIMARY
(
  NAME = N'
FabrikamOnlineDb',
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\
FabrikamOnlineDb.mdf',
  SIZE = 8192KB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 65536KB
)

LOG ON
(
  NAME = N'FabrikamOnlineDb_log
',
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\
FabrikamOnlineDb_log.ldf',
  SIZE = 8192KB,
  MAXSIZE = 2048GB,
  FILEGROWTH = 65536KB
)

GO

-- Create the memory optimized Filegroup and Container

ALTER DATABASE FabrikamOnlineDb
ADD
FILEGROUP
FabrikamOnlineDb_mod CONTAINS MEMORY_OPTIMIZED_DATA

GO

ALTER DATABASE FabrikamOnlineDb
ADD
FILE (
  name='
FabrikamOnlineDb_mod1',
  filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\
FabrikamOnlineDb_mod1'
)
TO FILEGROUP
FabrikamOnlineDb_modFabrikamOnlineDb_mod

GO

Creating the Customer, CustomerOrder and OrderLineItem Tables and associated History tables

Create required tables as follows.

USE [FabrikamOnlineDb]

GO

-- Customer Table(s)

CREATE TABLE Customer

(

ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000) DEFAULT (NEWID()),
  CustomerID varchar(10) NOT NULL,
  FirstName varchar(20) NOT NULL,
  LastName varchar(20) NOT NULL,
  Email varchar(50) NOT NULL,
  Address varchar(50) NOT NULL,
  City varchar(20) NOT NULL,
  State varchar(2) NOT NULL,
  Zip varchar(10) NOT NULL,
  Phone varchar(20) NOT NULL,
  CreditCard varchar(20) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

GO

CREATE SCHEMA CustomerHistory;

GO

ALTER TABLE Customer
ADD
  SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStartCustomer DEFAULT CONVERT(datetime2 (0), '2000-1-1 23:59:59'),
  SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_SysEndCustomer DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

GO

ALTER TABLE Customer
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = CustomerHistory.Customer));

GO

-- Customer Order Table(s)

CREATE TABLE CustomerOrder
(
  ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000) DEFAULT (NEWID()),
 
CustomerID varchar(10) NOT NULL, OrderNumber int NOT NULL,
  OrderStatus varchar(10) NOT NULL,
  OrderAmount money NOT NULL,
 
OrderDate datetime NOT NULL
)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

GO

CREATE SCHEMA CustomerOrderHistory;

GO

ALTER TABLE CustomerOrder
ADD
 
SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStartCustomerOrder DEFAULT CONVERT(datetime2 (0), '2000-1-1 23:59:59'),
 
SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_SysEndCustomerOrder DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

GO

ALTER TABLE CustomerOrder


SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = CustomerOrderHistory.CustomerOrder));

GO

-- Order Line Item Table(s)

CREATE TABLE OrderLineItem
(
  ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000) DEFAULT (NEWID()),
 
CustomerID varchar(10) NOT NULL,
 
OrderNumber int NOT NULL,
  ItemNumber int NOT NULL,
  ItemDescription varchar(50) NOT NULL,
  LineItemStatus varchar(10) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

GO

CREATE SCHEMA OrderLineItemHistory;

GO

ALTER TABLE OrderLineItem
ADD
  SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStartOrderLineItem DEFAULT CONVERT(datetime2 (0), '2000-1-1 23:59:59'),
  SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_SysEndOrderLineItem DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

GO

ALTER TABLE OrderLineItem
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = OrderLineItemHistory.OrderLineItem));

GO

Populating the Customer and Order Tables

Use the following query to insert new order records including processing table updates.

USE [FabrikamOnlineDb]

GO

-- Create Customers

INSERT INTO [dbo].[Customer]
([CustomerID],[FirstName],[LastName],[Email],[Address],[City],[State],[Zip],[Phone],[CreditCard])
VALUES
('0000000001',
'John',
'Doe',
'johndoe@hotmail.com',
'111 Newport Way',
'Nowhere',
'WA',
'99999',
'555-555-5555',
'1111-0000-0000-0000')

GO

INSERT
INTO [dbo].[Customer]


([CustomerID],[FirstName],[LastName],[Email],[Address],[City],[State],[Zip],[Phone],[CreditCard])


VALUES


('0000000002',
'Jane',
'Doe',
'janedoe@hotmail.com',
'2751 North 1250 East',
'Nowhere',
'AZ',
'99998',
'555-555-5554',
'2222-0000-0000-0001')

GO

-- Create Customer Orders

-- *** Order 1 ***

INSERT INTO [dbo].[CustomerOrder]
([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])
VALUES
('0000000001', 1,
'Processing',
'53.00',
'1-1-2016')

GO

INSERT INTO [dbo].[OrderLineItem]
([CustomerID],[OrderNumber],[ItemNumber],[ItemDescription],[LineItemStatus])
VALUES
('0000000001',1,100,'Microwave Oven','Processing')

GO

-- Process the order and set it to complete

UPDATE [dbo].[CustomerOrder]

SET OrderStatus = 'Complete'
WHERE OrderNumber = 1

GO

UPDATE [dbo].[OrderLineItem]

SET LineItemStatus = 'Shipped'
WHERE OrderNumber = 1 AND ItemNumber = 100

GO

-- Delete the order from the Customer database

UPDATE [dbo].[CustomerOrder]

SET OrderStatus = 'Moved'
WHERE OrderNumber = 1

GO

DELETE FROM [dbo].[CustomerOrder]
WHERE OrderNumber = 1

GO

DELETE FROM [dbo].[OrderLineItem]
WHERE OrderNumber = 1

GO

-- *** Order 2 ***

INSERT INTO [dbo].[CustomerOrder]
([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])
VALUES
('0000000001', 2,
'Processing',
'29.00',
'2-8-2016')

GO

INSERT INTO [dbo].[OrderLineItem]
([CustomerID],[OrderNumber],[ItemNumber],[ItemDescription],[LineItemStatus])
VALUES ('0000000001',2,101,'Coffee Maker','Processing')

GO

-- Process the order and set it to complete

UPDATE [dbo].[CustomerOrder]
SET OrderStatus = 'Complete'
WHERE OrderNumber = 2

GO

UPDATE [dbo].[OrderLineItem]
SET LineItemStatus = 'Shipped'
WHERE OrderNumber = 2 AND ItemNumber = 101

GO

-- Delete the order from the Customer database

UPDATE [dbo].[CustomerOrder]
SET OrderStatus = 'History'
WHERE OrderNumber = 2

GO

DELETE FROM [dbo].[CustomerOrder]
WHERE OrderNumber = 2

GO

DELETE FROM [dbo].[OrderLineItem]
WHERE OrderNumber = 2

GO

-- *** Order 3 ***

INSERT INTO [dbo].[CustomerOrder]
([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])
VALUES
('0000000001', 3,
'Processing',
'45.00',
'3-9-2016')

GO

INSERT INTO [dbo].[OrderLineItem]
([CustomerID],[OrderNumber],[ItemNumber],[ItemDescription],[LineItemStatus])
VALUES ('0000000001',3,102,'Delux Blender','Processing')

GO

-- Process the order and set it to complete

UPDATE [dbo].[CustomerOrder]
SET OrderStatus = 'Complete'
WHERE OrderNumber = 3

GO

UPDATE [dbo].[OrderLineItem]
SET LineItemStatus = 'Shipped'
WHERE OrderNumber = 3 AND ItemNumber = 102

GO

-- Delete the order from the Customer database

UPDATE [dbo].[CustomerOrder]
SET OrderStatus = 'MovedToHistory'
WHERE OrderNumber = 3

GO

DELETE FROM [dbo].[CustomerOrder]
WHERE OrderNumber = 3

GO

DELETE FROM [dbo].[OrderLineItem]
WHERE OrderNumber = 3

GO

-- *** Order 4 ***

INSERT INTO [dbo].[CustomerOrder]
([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])
VALUES
('0000000002', 4,
'Processing',
'66.00',
'3-12-2016')

GO

INSERT INTO [dbo].[OrderLineItem]
([CustomerID],[OrderNumber],[ItemNumber],[ItemDescription],[LineItemStatus])
VALUES ('0000000002',4,103,'Rice Cooker','Processing')

GO

-- Cancel the order and set it to complete

UPDATE [dbo].[CustomerOrder]
SET OrderStatus = 'Cancelled'
WHERE OrderNumber = 4

GO

UPDATE [dbo].[OrderLineItem]
SET LineItemStatus = 'Cancelled'
WHERE OrderNumber = 4 AND ItemNumber = 103

GO

-- Delete the order from the Customer database

UPDATE [dbo].[CustomerOrder]
SET OrderStatus = 'MovedToHistory'
WHERE OrderNumber = 4

GO

DELETE FROM [dbo].[CustomerOrder]
WHERE OrderNumber = 4

GO

DELETE FROM [dbo].[OrderLineItem]
WHERE OrderNumber = 4

GO

-- *** Order 5 ***

INSERT INTO [dbo].[CustomerOrder]
([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])
VALUES
('0000000002', 5,
'Processing',
'74.00',
'3-18-2016')

GO

INSERT INTO [dbo].[OrderLineItem]
([CustomerID],[OrderNumber],[ItemNumber],[ItemDescription],[LineItemStatus])
VALUES
('0000000002',5,104,'Kitchen Knife Set','Processing')

GO

-- Process the order and set it to complete

UPDATE [dbo].[CustomerOrder]
SET OrderStatus = 'Complete'
WHERE OrderNumber = 5

GO

UPDATE [dbo].[OrderLineItem]
SET LineItemStatus = 'Shipped'

GO

-- Delete the order from the Customer database

UPDATE [dbo].[CustomerOrder]
SET OrderStatus = 'MovedToHistory'
WHERE OrderNumber = 5

GO

DELETE FROM [dbo].[CustomerOrder]
WHERE OrderNumber = 5

GO

DELETE FROM [dbo].[OrderLineItem]
WHERE OrderNumber = 5

GO

-- *** Order 6 ***

INSERT INTO [dbo].[CustomerOrder]
([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])
VALUES
('0000000002', 6,
'Processing',
'77.00',
getdate())

GO

INSERT INTO [dbo].[OrderLineItem]
([CustomerID],[OrderNumber],[ItemNumber],[ItemDescription],[LineItemStatus])
VALUES
('0000000002',6,105,'Toaster Oven','Processing')

GO

-- Place the order on hold due while the line item is out of stock

UPDATE [dbo].[CustomerOrder]
SET OrderStatus = 'OnHold'
WHERE OrderNumber = 6

GO

UPDATE [dbo].[OrderLineItem]
SET LineItemStatus = 'OutOfStock'
WHERE OrderNumber = 6 AND ItemNumber = 105

GO

-- *** Order 7 ***

INSERT INTO [dbo].[CustomerOrder]
([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])
VALUES
('0000000002', 7,
'Processing',
'399.00',
getdate())

GO

INSERT INTO [dbo].[OrderLineItem]
([CustomerID],[OrderNumber],[ItemNumber],[ItemDescription],[LineItemStatus])
VALUES
('0000000002',7,106,'10000 Watt Air Conditioner','Processing')

GO

Create Availability Group and Adding the Secondary On Premise SQL Server

Begin by starting the Add Replica… dialog for the Availability Group

Fill in the Availability group name and click the Next> button

Verify the database Meets prerequisites and click the Next> button

Click the Add Replica… button

Select the SQL Server name and click connect

Fill in the properties for the new replica and click the Next> button

Select Full synchronization, populate the network share location and click the Next> button

Verify the validation was successful and click the Next> button

Confirm all information in the Summary page and click the Finish button

Verify the availability group is being created

Verify the results were successful and click the Close button

Click the OK button

Extending the Always On Availability Group to an asynchronous Azure Replica

Right-click the Availability Group and select Add Replica… from the dropdown

Click the Next button

Click the Connect… button and then the Next> button

Click the Add Azure Replica… button

Fill in the Azure Replica information

  • Connect to the Azure subscription using the Sign In… button
  • Select the appropriate Image
  • Select the VM Size
  • Fill in the VM Name
  • Fill in the VM User Name
  • Fill in the VM Administrator Password
  • Fill in the Confirm Password
  • Select the Virtual Network
  • Select the Virtual Network Subnet
  • Fill in the Domain
  • Fill in the Domain User Name
  • Fill in the Password
  • Click the OK button

Continue adding Azure Replicas using the steps in this section

Enabling Stretch on the History tables

Follow the steps outlined below to enable the stretch feature

Enable Stretch on the Database by launching the Stretch wizard

Click the Next button

Select tables that need to be stretched and click the Next> button

Sign-In to Microsoft Azure account that will contain the Stretched Database

Provide credentials on the sign-in screen

Select the appropriate subscription, region and provide login and password for the SQL Server administrator

Provide password to encrypt the master key

Configure Azure firewall to ensure communication from source database

Review Settings and Click Finish

Ensure successful deployment of the Stretch Database and Click Close

Verify the results were successful and click the Close button

NOTE:

Individual tables can also be stretched using a predicate function on the table.

As an example the customer order history table can be stretched such that only rows where the OrderDate column is before March 1, 2016 are migrated to the Azure stretch table as follows:

USE [FabrikamOnlineDb]

GO

-- Create the Stretch Predicate function for the Customer Order Table

CREATE FUNCTION dbo.fn_customerorderstretchpredicate(@OrderDate datetime)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN   
SELECT 1 AS is_eligible
WHERE @OrderDate < CONVERT(datetime, '3/1/2016', 101)

GO

ALTER TABLE CustomerOrderHistory.CustomerOrder
SET ( REMOTE_DATA_ARCHIVE =
ON (
    FILTER_PREDICATE = dbo.fn_customerorderstretchpredicate(OrderDate),
    MIGRATION_STATE = OUTBOUND)
)

GO

Adding additional updates to the tables

USE [FabrikamOnlineDb]

GO

-- Change a customer's email address

UPDATE Customer

SET Email = 'janedoe2@hotmail.com'
WHERE CustomerId = 2

GO

-- Delete a customer

DELETE FROM Customer
WHERE CustomerID = 1

GO

DELETE FROM CustomerOrder
WHERE CustomerID = 1

GO

DELETE FROM OrderLineItem
WHERE CustomerID = 1

GO

Testing the Deployment

This section details how to validate the scale optimization settings that have been applied on the data base.

Example CSR application query for a customer order from the in memory column store tables

use [FabrikamOnlineDb]

GO

-- Query Jane Doe's open order (Order 7) from the Read Only replica database

SELECT
c.CustomerID, c.FirstName, c.LastName,
co.OrderNumber, co.OrderAmount, co.OrderDate,
ol.ItemNumber, ol.ItemDescription
FROM [Customer] c
JOIN [CustomerOrder] co ON c.CustomerID = co.CustomerID
JOIN [OrderLineItem] ol ON co.OrderNumber = ol.OrderNumber
WHERE c.FirstName = 'Jane'
AND c.LastName = 'Doe'
AND co.OrderNumber = 7

GO

Example of CSR application query for all of a customer's completed orders from the history tables

-- Query all John Doe's completed orders from the History Tables

SELECT
c.CustomerID, c.FirstName, c.LastName,
co.OrderNumber, co.OrderAmount, co.OrderDate,
ol.ItemNumber, ol.ItemDescription
FROM [CustomerHistory].[Customer] c
JOIN [CustomerOrderHistory].[CustomerOrder] co ON c.CustomerID = co.CustomerID
JOIN [OrderLineItemHistory].[OrderLineItem] ol ON co.OrderNumber = ol.OrderNumber
WHERE c.FirstName = 'John'
AND c.LastName = 'Doe'
AND co.OrderStatus = 'Complete'
AND ol.LineItemStatus = 'Shipped'

GO

Example analytics query for customer lifetime purchase amount from the Read Only replica database tables

Begin by connecting to one of the Azure Secondary SQL server in the cluster and run the following query which will prevent the background query from slowing down the real time executions.

USE [FabrikamOnlineDb]

GO

-- Query Customer Order orders in processing Totals from the readonly replica database tables

SELECT c.CustomerID, c.FirstName, c.LastName, SUM(coh.OrderAmount)
FROM [Customer] c
JOIN [CustomerOrder] coh ON c.CustomerID = coh.CustomerID
WHERE (coh.OrderStatus IN ('Processing', 'OnHold'))
GROUP BY c.CustomerID, c.FirstName, c.LastName

GO

Example analytics query for customer lifetime completed orders amount from the History Tables

USE [FabrikamOnlineDb]

GO

SELECT c.CustomerID, c.FirstName, c.LastName, SUM(coh.OrderAmount)
FROM [CustomerHistory].[Customer] c
JOIN [CustomerOrderHistory].[CustomerOrder] coh ON c.CustomerID = coh.CustomerID
WHERE (coh.OrderStatus IN ('Complete', 'OnHold'))
GROUP BY c.CustomerID, c.FirstName, c.LastName

GO

References

Stretch Database

Upgrade Advisor

In Memory Column Store

Enhanced Always On

Temporal Tables