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:
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
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.
In order to implement a Hyperscale database on SQL Server 2016 database administrators:
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)
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 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 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.
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 following sections explain the relevant tables and their schema in details.
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 sample queries for these are given later in the document.
HSP DBA arrives at an optimal design to address all the pain points to implement the hyperscale database as follows:
The following diagram shows the reference architecture for this implementation
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
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
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
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
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
Continue adding Azure Replicas using the steps in this section
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
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
This section details how to validate the scale optimization settings that have been applied on the data base.
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
-- 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
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
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
Stretch Database | |
Upgrade Advisor | |
In Memory Column Store | |
Enhanced Always On | |
Temporal Tables |