Hybrid Hyperscale Using SQL Server 2016

SQL Server 2016 Hybrid Hyperscale

Hybrid Hyperscale using SQL Server 2016

Version 1.0

June 2016

Prepared by

Microsoft Services

MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, our provision of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

The descriptions of other companies’ products in this document, if any, are provided only as a convenience to you. Any such references should not be considered an endorsement or support by Microsoft. Microsoft cannot guarantee their accuracy, and the products may change over time. Also, the descriptions are intended as brief highlights to aid understanding, rather than as thorough coverage. For authoritative descriptions of these products, please consult their respective manufacturers.

© 2016 Microsoft Corporation. All rights reserved. Any use or distribution of these materials without express authorization of Microsoft Corp. is strictly prohibited.

Microsoft and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

1

Hybrid Hyperscale
Version 1.0 last modified on 5 Jun. 16

SQL Server 2016 Hybrid Hyperscale

Table of Contents

1Overview

1.1DBA Considerations

1.2SQL Server 2016 capabilities for a Hyperscale database implementation

1.2.1Always On Availability Groups

1.2.2In Memory Column Store Indexes

1.2.3Stretch Database

1.2.4Temporal Tables

2Example Scenario

2.1Database Design

2.1.1FabrikamOnline.com Transaction Table Relationships

2.2Solution Design

2.3Implementation Steps

2.3.1Creating the Sample Memory Optimized Customer Database

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

2.3.3Populating the Customer and Order Tables

2.3.4Create Availability Group and Adding the Secondary On Premise SQL Server

2.3.5Extending the Always On Availability Group to an asynchronous Azure Replica

2.3.6Enabling Stretch on the History tables

2.4Adding additional updates to the tables

2.5Testing the Deployment

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

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

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

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

3References

71

Hybrid Hyperscale
Version 1.0 last modified on 5 Jun. 16

SQL Server 2016 Hybrid Hyperscale

1Overview

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.

1.1DBA 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 anunderstanding 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

1.2SQL Server 2016 capabilities for a Hyperscale database implementation

1.2.1Always 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 groupscan 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, seeAlwaysOn Availability Groups (SQL Server)

1.2.2In Memory Column Store Indexes

The columnstore 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, columnstore indexes enable operational analytics, the ability to run performant real-time analytics on a transactional workload.

For more information, see ColumnStore Indexes Guide.

1.2.3Stretch 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.

1.2.4Temporal 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 capabilitiesin the context of a customer scenario.

2Example Scenario

An on-line retail shopping cart hosted application is developed byFabrikamOnline.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

2.1Database Design

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

2.1.1FabrikamOnline.com Transaction Table Relationships

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

2.1.1.1Customer Table

2.1.1.2Customer Order Table

2.1.1.3Order 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.

2.2Solution 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

2.3Implementation Steps

2.3.1Creating 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

CREATEDATABASE [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 )

LOGON

( 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

ALTERDATABASEFabrikamOnlineDbADDFILEGROUPFabrikamOnlineDb_mod CONTAINS MEMORY_OPTIMIZED_DATA

GO

ALTERDATABASEFabrikamOnlineDbADDFILE (name='FabrikamOnlineDb_mod1',filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FabrikamOnlineDb_mod1')TOFILEGROUPFabrikamOnlineDb_mod

GO

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

Create required tables as follows.

USE [FabrikamOnlineDb]

GO

-- Customer Table(s)

CREATETABLE Customer

(

ID UNIQUEIDENTIFIERNOTNULLPRIMARYKEYNONCLUSTEREDHASHWITH (BUCKET_COUNT=1000000)DEFAULT (NEWID()),

CustomerID varchar(10)NOTNULL,

FirstName varchar(20)NOTNULL,

LastName varchar(20)NOTNULL,

Email varchar(50)NOTNULL,

Address varchar(50)NOTNULL,

City varchar(20)NOTNULL,

State varchar(2)NOTNULL,

Zip varchar(10)NOTNULL,

Phone varchar(20)NOTNULL,

CreditCard varchar(20)NOTNULL

)

WITH (MEMORY_OPTIMIZED=ON,DURABILITY= SCHEMA_AND_DATA);

GO

CREATESCHEMA CustomerHistory;

GO

ALTERTABLE Customer

ADD

SysStartTime datetime2(0)GENERATEDALWAYSASROWSTARTHIDDEN

CONSTRAINT DF_SysStartCustomer DEFAULTCONVERT(datetime2 (0),'2000-1-1 23:59:59'),

SysEndTime datetime2(0)GENERATEDALWAYSASROWENDHIDDEN

CONSTRAINT DF_SysEndCustomer DEFAULTCONVERT(datetime2 (0),'9999-12-31 23:59:59'),

PERIODFORSYSTEM_TIME (SysStartTime, SysEndTime);

GO

ALTERTABLE Customer

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

GO

-- Customer Order Table(s)

CREATETABLE CustomerOrder

(

ID UNIQUEIDENTIFIERNOTNULLPRIMARYKEYNONCLUSTEREDHASHWITH (BUCKET_COUNT=1000000)DEFAULT (NEWID()),

CustomerID varchar(10)NOTNULL,

OrderNumber intNOTNULL,

OrderStatus varchar(10)NOTNULL,

OrderAmount moneyNOTNULL,

OrderDate datetimeNOTNULL

)

WITH (MEMORY_OPTIMIZED=ON,DURABILITY= SCHEMA_AND_DATA);

GO

CREATESCHEMA CustomerOrderHistory;

GO

ALTERTABLE CustomerOrder

ADD

SysStartTime datetime2(0)GENERATEDALWAYSASROWSTARTHIDDEN

CONSTRAINT DF_SysStartCustomerOrder DEFAULTCONVERT(datetime2 (0),'2000-1-1 23:59:59'),

SysEndTime datetime2(0)GENERATEDALWAYSASROWENDHIDDEN

CONSTRAINT DF_SysEndCustomerOrder DEFAULTCONVERT(datetime2 (0),'9999-12-31 23:59:59'),

PERIODFORSYSTEM_TIME (SysStartTime, SysEndTime);

GO

ALTERTABLE CustomerOrder

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

GO

-- Order Line Item Table(s)

CREATETABLE OrderLineItem

(

ID UNIQUEIDENTIFIERNOTNULLPRIMARYKEYNONCLUSTEREDHASHWITH (BUCKET_COUNT=1000000)DEFAULT (NEWID()),

CustomerID varchar(10)NOTNULL,

OrderNumber intNOTNULL,

ItemNumber intNOTNULL,

ItemDescription varchar(50)NOTNULL,

LineItemStatus varchar(10)NOTNULL

)

WITH (MEMORY_OPTIMIZED=ON,DURABILITY= SCHEMA_AND_DATA);

GO

CREATESCHEMA OrderLineItemHistory;

GO

ALTERTABLE OrderLineItem

ADD

SysStartTime datetime2(0)GENERATEDALWAYSASROWSTARTHIDDEN

CONSTRAINT DF_SysStartOrderLineItem DEFAULTCONVERT(datetime2 (0),'2000-1-1 23:59:59'),

SysEndTime datetime2(0)GENERATEDALWAYSASROWENDHIDDEN

CONSTRAINT DF_SysEndOrderLineItem DEFAULTCONVERT(datetime2 (0),'9999-12-31 23:59:59'),

PERIODFORSYSTEM_TIME (SysStartTime, SysEndTime);

GO

ALTERTABLE OrderLineItem

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

GO

2.3.3Populating the Customer and Order Tables

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

USE [FabrikamOnlineDb]

GO

-- Create Customers

INSERTINTO [dbo].[Customer]

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

VALUES

('0000000001','John','Doe','','111 Newport Way','Nowhere','WA','99999','555-555-5555','1111-0000-0000-0000')

GO

INSERTINTO [dbo].[Customer]

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

VALUES

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

GO

-- Create Customer Orders

-- *** Order 1 ***

INSERTINTO [dbo].[CustomerOrder]

([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])

VALUES

('0000000001', 1,'Processing','53.00','1-1-2016')

GO

INSERTINTO [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 ***

INSERTINTO [dbo].[CustomerOrder]

([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])

VALUES

('0000000001', 2,'Processing','29.00','2-8-2016')

GO

INSERTINTO [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 ***

INSERTINTO [dbo].[CustomerOrder]

([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])

VALUES

('0000000001', 3,'Processing','45.00','3-9-2016')

GO

INSERTINTO [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 ***

INSERTINTO [dbo].[CustomerOrder]

([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])

VALUES

('0000000002', 4,'Processing','66.00','3-12-2016')

GO

INSERTINTO [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 ***

INSERTINTO [dbo].[CustomerOrder]

([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])

VALUES

('0000000002', 5,'Processing','74.00','3-18-2016')

GO

INSERTINTO [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 ***

INSERTINTO [dbo].[CustomerOrder]

([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])

VALUES

('0000000002', 6,'Processing','77.00',getdate())

GO

INSERTINTO [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 ***

INSERTINTO [dbo].[CustomerOrder]

([CustomerID],[OrderNumber],[OrderStatus],[OrderAmount],[OrderDate])

VALUES

('0000000002', 7,'Processing','399.00',getdate())

GO

INSERTINTO [dbo].[OrderLineItem]

([CustomerID],[OrderNumber],[ItemNumber],[ItemDescription],[LineItemStatus])

VALUES

('0000000002',7,106,'10000 Watt Air Conditioner','Processing')

GO

2.3.4Create 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