Relational Databases in Cloud Environment

1. Relational Databasesin Cloud Environment Amazon RDS (MySQL and Oracle) andSQL Azure relational Emil Tabakov Telerik Software Academy academy.telerik.com

2. Who Am I? • Team lead at Telerik • Professional experience • Web applications development • How to contact me • Twitter: @anthares • Blog: www.etabakov.com • Email: emil.tabakov at telerik dot com • Disclaimer

3. Agenda • Architecture overview • Features and limitations • Pricing • Scaling • Development for cloud based database • Demo • Some other considerations

4. Why Should We Care? • To add another tool in your toolbox • To be prepared for the future

5. Relational Databasesin the Cloud: Solutions • Amazon RDS • SQL Server new • MySQL • Oracle • Microsoft • SQL Azure • Google Cloud SQL • MySQL

6. SQL Azure Architecture • Four layers of abstraction • Client Layer • TDS protocol • Located on various environments • Developed with different technologies • Services Layer • Provisioning • Billing and metering • Connection routing

7. SQL Azure Architecture • Four layers of abstraction • Platform Layer • The physical database servers • SQL Azure fabric • Automatic failover, load balancing and automatic replication between all the physical servers • Infrastructure Layer

8. Amazon RDS Architecture • Dedicated virtual machine • Built on top of MySQL / Oracle / SQL Server instances

9. SQL Azure – Key Features • SQL Azure is “native” cloud platform • Offers management tool as a Service • Convenient even for non-SQL developers • Disaster recovery solution • Out of the box at no cost • Roadmap and community • Lots of sessions, clear vision for improvement • Generally, cheaper

10. SQL Azure – Limitations • 150GB database size limitation • Only a subset of features compared toSQL Server, no support for: • Analysis services • Replication • Service Broker • Manipulating physical resources • Setting server options, trace flags, SQL server profiler or database advisor, no CLR

11. Amazon RDS – Key Features • Full MySQL / Oracle / SQL Server instance • Database Backup / Restore functionality • Database size – up to 1 TB • Available for developers all over the world today • Generally better performance than SQL Azure

12. Amazon RDS – Limitations • Up to 4 hours a week downtime for maintenance • Usually no downtime • Not really cloud solution • More like database hosting solution • Could cost a lot

13. Pricing in SQL Azure • Estimated cost for 50GB database a month: • ~ $125

14. Pricing in Amazon RDS • Multi Availability Zone doubles the cost • Additional cost for data transfer • Greater variety of hardware configurations • The billing depends on • Usage • Provisioned storage • I/O requests • Harder to be predicted

15. Scaling • Scale-up (vertically) • Limited by the hardware • High administration costs (exponential) • Scale-out (horizontally) • Cost effective • Commodity class hardware • Multiple approaches • Sharding, horizontal partitioning

16. Sharding vs. Horizontal Partitioning • Horizontal partitioning • Splitting database table in multiple tables within a single database instance • Going further with sharding • Splitting between multiple instances • Advantages of sharding: • Split the search load between multiple instances (not only multiple indexes) • Easier replication, worldwide distribution

17. Scaling the Azure Cloud • Limited Scale up opportunities • Great Scale out options through federations • Collection of database partitions defined by federation scheme • No joins supported across multiple database instances because of the physical separation

18. SQL Azure Demo Working with Federationsusing Entity Framework

19. Scaling with Amazon RDS • Good Scale-up opportunities • 5 database sizes available (small, large, extra large, double extra large, quadruple extra large) • Standard and High memory options • No out of the box scale-out solution • But there are various home grown solutions available

20. Development withCloud Databases • Your database server will fail sooner or later • Build stateless application • Databases in cloud are different from those on the ground • Have in mind the pricing model of your cloud provider • Have in mind that the location of your DB can be changed any time

21. Demo Migrate a simple Web Application’s data layer to various cloud environments

22. Legal Concerns • Tracking and auditing data • Privacy and data security • Physical and logical security requirements • EU Directive on data protection (95/46/EC) • Accessing the data by the vendor • Jurisdiction concerns – US Patriot Act • Limitations on Vendor Liability • No warranty, limited responsibility in case of accidents

23. Choosing the Cloud for You • New project or existing one • Define your project’s requirements and restrictions • Budget • High availability • High Scalability • Ease of use • Performance

24. Choosing the Cloud for You (2) • Have in mind your application hosting environment • Increased latency • Possible security flaws • Be prepared for a change • Free Azure subscription for BizSpark members

25. Possible Use Cases • Software as a service product • Hosted trials on Telerik

26. Questions?

27. Thank you! • Twitter: @anthares • Blog: www.etabakov.com • Email: emil.tabakov at telerik dot com Thanks for attending!

28. Homework • Create a relational DB account in some cloud • Use Amazon RDS or SQL Azure orXeround Cloud MySQL or other cloud relational DB • Define a table "Bookmark" with columns "URL" and "Description" and table "Clicks" to store the click count for each bookmark entry • Create a console or Web based application • Use C#, Java, PHP or other language • Your application should add few bookmarks, list all bookmarks and a click for the first bookmark