David Postlethwaite Shard Attack Horizontal Scaling in Azure

David Postlethwaite Shard Attack Horizontal Scaling in Azure SQL Database

BIG Thanks to SQL Sat Denmark sponsors GOLD SILVER BRONZE 2 06/10/2018 David Postlethwaite

Raffle and goodbye Beer Remember to visit the sponsors, stay for the raffle and goodbye beers Join our sponsors for a lunch break session in : cust 0. 01 and cust 1. 06 We hope you’ll all have a great Saturday. Regis, Kenneth 3 06/10/2018 David Postlethwaite

David Postlethwaite Liverpool Victoria LV= • • • SQL and Oracle DBA MCSE 2014 Data Platform MCITP 2008, 2005 Oracle OCA 25 years IT Experience 10 years as DBA Blog: gethynellis. com david@clunyweb. co. uk @postledm https: //www. linkedin. com/in/davidpostlethwaite

The problem Your database is growing. You need more disk space, more compute CPU and more memory What can you do? 5 06/10/2018 David Postlethwaite

The Solutions Vertical Scaling (Scale-up): Adding more processors, RAM or disk space to our server Pros Generally less challenging to implement No New licence costs Small increase in power consumption Less network hardware Cons Cost for large systems Limit to how big a server can grow Risk of hardware failure causing major outage Doesn’t Support Geo Location 6 06/10/2018 David Postlethwaite

The Solutions Horizontal Scaling (Scale-out): Adding more servers with similar CPU and RAM to compliment the original server. Pros Usually cheaper than scaling vertically In theory can scale infinitely Easier to run fault-tolerance Easy to upgrade Cons More licence costs Bigger footprint in your Data Center More Power Consumption More Infrastructure Technically more complex 7 06/10/2018 David Postlethwaite

The Problem in Azure In Azure SQL Database Your database is growing. You need more disk space and more compute BUT Maximum Database Size is 1 - 4 TB Premium Tier is very, very expensive Outgrowing one Azure SQL database Need physical separation for security or compliance or geopolitical reasons Keep as one logical database Scaling out is a solution Also know as Sharding 8 06/10/2018 David Postlethwaite

Terminology Sharding - Splitting identically structured data across several independent databases Shard Map Manager 1 Shard Map Manager DB Name Country_ID DB 1 2 DB 2 3 DB 3 4 2 Client App built with Azure Elastic Database Client Library (. NET or Java) Shard Map 3 Shardlet 4 Sharded Table Address 5 Tenant 9 Shard key 06/10/2018 David Postlethwaite Shard Set Shards ID Reference Table Country ID Address 1 2 Denmark 2 Address 2 2 Poland 3 Address 3 3 Austria 4 Sharded Tables Elastic Database Query Reference Tables Elastic Database Jobs (Elastic Job Agent)

Single and Multi Tenant: A person who occupies land or property rented from a landlord. 12 06/10/2018 David Postlethwaite

Multi Tenant. List and Range Mapping List Mapping Specific IDs in each database 13 06/10/2018 David Postlethwaite Range Mapping Range of IDs to each database

Growing the Shards Use Horizontal scaling out to increase capacity of a shard. Use Vertical scaling out to increase the performance of a shard. 14 06/10/2018 David Postlethwaite

Data Dependent Routing Querying a particular shard using a specific sharding key. Shard Map Manager Shard Key Elastic Client Library DB 1 cache GSM Data DB 1 LSM 15 06/10/2018 David Postlethwaite GSM Global Shard Map LSM Local Shard Map

Multi Shard Query multiple shards Results sets are returned using UNION ALL Shard Map Manager Elastic Client Library SELECT count(*) from addresses UNION ALL result set DB 1 16 06/10/2018 David Postlethwaite DB 2 DB 3 DB 4 DB 5

Splitting and Merging Shards Split: Move shardlets from one shard to another (typically new) shard. Merge: Move shardlets from two shards to one shard Shardlet Move: The act of moving a single shardlet to a different shard. The Shard Map Manager must be updated with the change Manually moving rows could lead to lost data Split/Merge Service Azure Web Service to move data between shards Requires its own database Can use web page to specify the shard to move Or Write c# or Power. Shell code into your application https: //docs. microsoft. com/en-us/azure/sql-database-elastic-scale-configure-deploy-split-and-merge 17 06/10/2018 David Postlethwaite

Recovery Manager What if the GSM and LSM become out of sync? Orphaned Sharp Maps Shard is deleted Shard is renamed A geo-failover occurs. A shard or the Shard. Map. Manager is restored to an earlier point-in time Part of the Elastic Client Library Available Methods Detach. Shard() Detect. Mapping. Differences() Resolve. Mapping. Differences() Attach. Shard() 18 06/10/2018 David Postlethwaite

The Demo Address 27 Mier Rue de l’Etuve Trangravsvej 14 Sankt Augustin Friesenstraße 30 Oudegracht 196 Aleja Korfantego Prečna ulica 6 Europe Addresses City Country_ID Antwerp Belgium 2 Brussels Belgium 2 Copenhagen Denmark 4 Bonn Germany 8 Koln Germany 8 Utrecht Netherlands 14 Katowice Poland 15 Ljubliana Slovenia 19 19 06/10/2018 David Postlethwaite Shards Europe Addresses City Country_ID Antwerp 2 Brussels 2 Copenhagen 4 Reference Table Countries Country_ID Austria 1 Belgium 2 Denmark 4 Germany 8 Netherland 14 Poland 15 Slovenia 19 Shard key Azure External Tables Elastic Database Query Shard 1 Range: ID 1 -7 Europe Addresses City Country_ID Bonn 8 Utrecht 14 Shard 2 Range: ID 8 -14 Shard Set Europe Addresses City Country_ID Plovdiv 15 Ljubljana 19 Shard 3 Range: ID 15 -21

DEMO Let’s see if we can get it all to work We have a large databases of addresses We want to split it into several new databases / shards And query it using a. NET application 20 06/10/2018 David Postlethwaite

Disadvantages of Sharding More Complex Code Can circumvent the sharding and write straight to the table Row Level Security Cross Shard ACID transaction not supported Reverential Integrity Performance Failover Corruption – Recovery Manager 31 06/10/2018 David Postlethwaite

Conclusion Hopefully you now have a better understanding of Horizontal Partitioning Links https: //docs. microsoft. com/en-us/azure/sql-database-elastic-database-client-library https: //docs. microsoft. com/en-us/azure/sql-database-elastic-scale-configure-deploy-split-and-merge https: //docs. microsoft. com/en-us/azure/sql-database-elastic-database-recovery-manager My Source Code https: //www. gethynellis. com/2018/10/horizontal-scaling-in-azure-SQL-database. html david@clunyweb. co. uk @postledm https: //www. linkedin. com/in/davidpostlethwaite www. youtube. com/c/David. Postlethwaite. SQL 32 06/10/2018 David Postlethwaite

BIG Thanks to SQL Sat Denmark sponsors GOLD SILVER BRONZE 33 06/10/2018 David Postlethwaite

Raffle and goodbye Beer Remember to visit the sponsors, stay for the raffle and goodbye beers Join our sponsors for a lunch break session in : cust 0. 01 and cust 1. 06 We hope you’ll all have a great Saturday. Regis, Kenneth 34 06/10/2018 David Postlethwaite