Lee Lup YuenPrincipal ConsultantChief Architect Office13 Apr 2010
Case Study: Using Azure for data crunching
• I have a huge database of (say) book titles
• I want to pre-generate a list of search suggestions for my search UI
• Needs plenty of CPU and RAM
• I want it fast and cheap
• Why use Azure?– On demand computing + storage– Ready-to-use .NET and SQL infra
The problem
04/10/23
The current database
• Database consists of titles, keywords and other metadata
• SQL Server 2008
• 2.9 GB of data
• 4.3 million records
04/10/23
Migrating the database to SQL Azure
• What’s different about SQL Azure?– SQL Server 2008 R2 can be used for remote mgmt
– Some GUI mgmt functions not supported, need to use T-SQL commands
– Need to open the Azure Firewall to allow remote SQL access
– All tables must have a clustered index
– CLR and Table Partitioning not supported– More details: http://msdn.microsoft.com/en-us/library/ff394102.aspx
• Copy data using SSIS Import/Export Data Wizard– Choose the “.NET Framework Data Provider for SQL Server”
04/10/23
SQL Azure
04/10/23
2.9 GB database,4.3 million records
uploaded to Azure in about 1 hour
SQL Azure Firewall
04/10/23
Add your firewall rule
Using SQL Server Mgmt Studio with Azure
04/10/23
Azure SQL Server
Local SQL Server
The current app
• Based on .NET 3.5; 700 lines of code
• Scans titles and keywords for substrings and counts the number of occurrences of each substring
• Brute force approach– 100% CPU utilisation– Can use more than 10 GB of runtime memory
04/10/23
Migrating the app to Windows Azure
• What’s different about .NET on Azure?– SQL works OK, need to use Azure connection string like
Server=tcp:xxx.database.windows.net;Database=xxx;User ID=xxx@xxx;Password=xxx;Trusted_Connection=False;Encrypt=True;
– Filesystem Support: Requires virtual drive configuration– Beware of unmanaged code and access to local resources
• Just follow the steps at http://msdn.microsoft.com/en-us/library/dd179367.aspx
– Download and install the Azure SDK– Create an Azure solution in Visual Studio– Copy your code into the Worker Role, Web Role or WCF
Role– Configure the Virtual Machine for ExtraLarge if necessary
04/10/23
Visual Studio with Azure SDK
04/10/23
ExtraLarge VM: 8 CPU cores
15 GB memory2,000 GB disk space
Azure Project
WCF App
Web App
Worker App
Deploying and running the Azure app
• Test the app locally using the Azure SDK
• Use Visual Studio to build and publish the app
• Go to the Azure website, create a hosted service
• Upload the *.cskpg and *.cscfg files
• Click “Run”
04/10/23
Windows Azure Hosted Service
04/10/23
Deploying a hosted service
04/10/23
Azure Performance
• Comparing Azure with my low-cost server– Core i7 3 GHz, 12 GB RAM, Win2008 R2 64-bit,
SQL2008 R2 Nov CTP 64-bit– S$3,000 from Sim Lim computer shop
04/10/23
Processing time for My Server Azure
500K records 0.5 hours 0.6 hours
1M records 1.5 hours 2 hours
2M records SQL Timeout 6.5 hours
4M records (To be provided) (To be provided)
100% RAM utilisation
Azure Pricing
Source: http://www.microsoft.com/windowsazure/faq/#pricing04/10/23
ComputeUS$0.12 / hour for the SMALL instance US$0.24 / hour for the MEDIUM instance US$0.48 / hour for the LARGE instanceUS$0.96 / hour for the EXTRA LARGE instance
StorageUS$0.15 / GB stored/monthUS$0.01 / 10K storage transactions
SQL AzureWeb Edition – Up to 1 GB relational database = US$9.99 Business Edition – Up to 10 GB relational database = US$99.99
Data Transfers US$0.10 in / US$0.15 out / GB for North America and EuropeUS$0.30 in / US$0.45 out / GB for Asia PacificInbound data transfers during off-peak times through June 30, 2010 are at no charge. Prices revert to our normal inbound data transfer rates after June 30, 2010
My Azure Bill
Total Bill: US$ 122.88
04/10/23
Sample Detailed Bill
04/10/23
Lessons learnt
• Test locally before deploying to cloud– Deployment may take a few minutes
• Migrating apps and data to Azure can be fast and easy– Mine took only 2 days
• Azure is cost-effective for my app– Costs less than a low-cost server
04/10/23