extreme scaling with sql azure sql bits 7, york, october 2010 martin schmidt – miracle a/s denmark
TRANSCRIPT
![Page 1: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/1.jpg)
ExtremeScaling with SQL
AzureSQL Bits 7, York, October 2010Martin Schmidt – Miracle A/S
Denmark
![Page 2: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/2.jpg)
Subtitle:The Safetrack story
![Page 3: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/3.jpg)
AgendaO Speaker InfoO Safetrack facts & system infoO Safetrack architectureO Database designO Issues with current setupO The Azure platformO ShardingO Solution WalkthroughO Moving Exsisting Data to SQL Azure
![Page 4: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/4.jpg)
Speaker infoO Name: Martin SchmidtO Email: [email protected] Blog: www.performanceduo.comO Twitter: @ms1333
![Page 5: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/5.jpg)
AgendaO Speaker InfoO Safetrack facts & system infoO Safetrack architectureO Database designO Issues with current setupO The Azure platformO ShardingO Solution WalkthroughO Moving Exsisting Data to SQL Azure
![Page 6: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/6.jpg)
Safetrack factsO Danish Company – started 2005O GPS tracking of trucks and excavatorsO Started out as an insurance service
against theftO Today: A complete platform for fleet
managementO Tracks more than 25.000 unitsO Website: http://www.safetrack.dk
![Page 7: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/7.jpg)
Unit Types
![Page 8: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/8.jpg)
![Page 9: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/9.jpg)
![Page 10: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/10.jpg)
![Page 11: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/11.jpg)
How does this Work?
![Page 12: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/12.jpg)
How does this WorkO All units are equipped with a GPS
transmitterO Sends binary packages over GPRSO Built-in SIM cardO Sends a package every 2 min when
runningO Sends a package every 15 min when
parkedO Data is sent using UDP Protocol
![Page 13: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/13.jpg)
ME - 301
Length = 75 mm Width = 45 mmHeight = 18 mm
![Page 14: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/14.jpg)
Package dataO ReporttimeO LongitudeO LatitudeO SpeedO KM StatusO Inputs status information
O Is the lift up / downO Is the engine running
O Timecounter info on inputsO For how long has the engine been runningO How long has the lift been in use
![Page 15: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/15.jpg)
AgendaO Speaker InfoO Safetrack facts & system infoO Safetrack architectureO Database designO Issues with current setupO The Azure platformO ShardingO Solution WalkthroughO Moving Exsisting Data to SQL Azure
![Page 16: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/16.jpg)
System Archietecture
![Page 17: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/17.jpg)
AgendaO Speaker InfoO Safetrack facts & system infoO Safetrack architectureO Database designO Issues with current setupO The Azure platformO ShardingO Solution WalkthroughO Moving Exsisting Data to SQL Azure
![Page 18: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/18.jpg)
Database Design
![Page 19: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/19.jpg)
AgendaO Speaker InfoO Safetrack facts & system infoO Safetrack architectureO Database designO Issues with current setupO The Azure platformO ShardingO Solution WalkthroughO Moving Exsisting Data to SQL Azure
![Page 20: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/20.jpg)
Issues ?O Database and application on same
boxO Everything runs on the same single
serverO Single Point of Failure
O HardwareO DatabaseO Services / Website
O No HA solutionO Limited scaling options (Buy bigger
HW)
![Page 21: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/21.jpg)
Issues ?O All elements are independendO Easy to split thingsO No ”secret” data
O No personal or banking informationO Not breaking the Danish data law
![Page 22: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/22.jpg)
Issues ?O SQL Azure in the future?O Windows Azure in the future?
O Why Not
![Page 23: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/23.jpg)
AgendaO Speaker InfoO Safetrack facts & system infoO Safetrack architectureO Database designO Issues with current setupO The Azure platformO ShardingO Solution WalkthroughO Moving Exsisting Data to SQL Azure
![Page 24: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/24.jpg)
SQL AzureO High Availibity = Free
O 1 DB = 3 DB’s (automatic failover)O Size limitations
O 1 – 50 GBO Safetrack database > 250 GB
O Get the database off the current boxO Release more power to the Windows
service
![Page 25: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/25.jpg)
SQL AzureO Let Microsoft be your primary DBA!O SQL Azure = Black boksO Running your database with other
unknown applications
![Page 26: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/26.jpg)
Windows AzureO Moving the website to Windows
AzureO Easy peasy
O The Windows Service is not compatible with Azure at presentO No support for UDP – TCP Only
![Page 27: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/27.jpg)
SQL AzureO Lets move the database to the Cloud
O Latency from the local Windows service to SQL Azure is not a big issue.
O Size is a issue, we will have to Shard the data across multiple databases
O Sharding gives us scalability
![Page 28: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/28.jpg)
AgendaO Speaker InfoO Safetrack facts & system infoO Safetrack architectureO Database designO Issues with current setupO The Azure platformO ShardingO Solution WalkthroughO Moving Exsisting Data to SQL Azure
![Page 29: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/29.jpg)
Sharding factsO Partition data across multiple databasesO Each database holds a subset of the dataO A query needs only to look in one
databaseO Sharding is no silver bullet, you still need
to thinkO Important to choose the correct sharding
keyO Try to avoid fan-out queries, if possible
![Page 30: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/30.jpg)
AgendaO Speaker InfoO Safetrack facts & system infoO Safetrack architectureO Database designO Issues with current setupO The Azure platformO ShardingO Solution WalkthroughO Moving Exsisting Data to SQL Azure
![Page 31: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/31.jpg)
Solution walkthroughO Shard key candidates
O UnitIDO CustomerID
O The winner is:O CustomerID
O All reports on the web front end is based on customerID. This gives the customer the option to se the status of all his units.
![Page 32: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/32.jpg)
Data SyncronisationO MS Data sync Framework
O Requires SQL Agent to do scheduling
O SQL Azure Data Sync (Dev Preview)O The winner so farO We will have a look in a minuteO Pretty Slow
O SQL Server Integration ServicesO Still no scheduler
O Windows Azure worker roleO We need to test this
![Page 33: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/33.jpg)
Database Design
![Page 34: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/34.jpg)
DEMOO Enough Power Pointing
![Page 35: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/35.jpg)
AgendaO Speaker InfoO Safetrack facts & system infoO Safetrack architectureO Database designO Issues with current setupO The Azure platformO ShardingO Solution WalkthroughO Moving Exsisting Data to SQL Azure
![Page 36: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/36.jpg)
Loading data into Azure
O Task: Load existing data into SQL AzureO 265.000.000 Rows (ReportMessage)O 150 GB
O Possible solutionsO BCP UtilityO SSIS
![Page 37: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/37.jpg)
Loading data into Azure
![Page 38: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/38.jpg)
Loading data into Azure
O Slow, poor performanceO Still working on improving
performance
O Tested throughput (Laptop)O 1000 Rows / sec
![Page 39: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/39.jpg)
SQL Azure RessourcesO SQL Azure webfrontendO http://sql.azure.com
O SQL Azure LabsO http://www.sqlazurelabs.com
O SQL Azure team BlogO http://blogs.msdn.com/b/sqlazure/
![Page 40: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/40.jpg)
More RessourcesO PerformanceDUO
O http://www.performanceduo.com
O Miracle A/SO http://www.miracleas.dk
O MiracleDBO http://Miracledb.codeplex.com
O SafetrackO http://www.safetrack.dk
![Page 41: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/41.jpg)
Q & AO Questions?
![Page 42: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/42.jpg)
Thank You
![Page 43: Extreme Scaling with SQL Azure SQL Bits 7, York, October 2010 Martin Schmidt – Miracle A/S Denmark](https://reader038.vdocuments.us/reader038/viewer/2022110209/56649e2d5503460f94b1d7a5/html5/thumbnails/43.jpg)
Coming up…P/X001Understanding and Preventing SQL Injection AttacksKevin KlineP/L001SSIS FieldnotesDarren GreenP/L002The (Geospatial) Shapes of Things to ComeSimon MunroP/L005End to End Master Data Management with SQL Server Master Data ServicesJeremy KashelP/T007Understanding Microsoft Certification in SQL ServerChris Testa-O'Neill
#SQLBITS