Downloaded 19 times
Uploaded byJoseph D'Antoni
Deploying your Application to SQLRally
This document provides an overview of migrating SQL Server applications to SQL Azure cloud databases. It discusses the business benefits of cloud computing, an overview of SQL Azure and its features and limitations. It demonstrates how to build and deploy a SQL Azure database using Data Tier Applications in Visual Studio. The document also covers database migration strategies from on-premise SQL Server to SQL Azure and synchronization of data between SQL Azure and SQL Server databases using SQL Azure Data Sync.
Deploying your Application to SQLRally
- 1. Migrate SQL ServerApps to SQL Azure Cloud DBJoe D’Antoni, Synthes12-May-2011
- 2. About MeDBA with10+ years of experience
- 3.
- 4. Vice President ofthe Philadelphia SQL Server User Group
- 5. I like totalk about and use new technologies
- 6.
- 7. Blog—joedantoni.wordpress.comSession Objectives andTakeawaysObjectives:Understand Cloud ComputingBusiness cases for using SQL Azure/Cloud ComputingBuilding your SQL Azure DatabaseInteracting with and deploying code to you SQL Azure databaseReporting Services (coming soon, but demo’ed today)
- 8.
- 9. Cloud in thenews…May 6, 2011"Most of the work going forward will be integration, and architectural in nature. There will be a need for people in all levels who are thinking about a composite world. You have to think about how parts fit, which is an architectural mindset. It's not implementation of a feature, it's architectural in nature”Bob Kelly, MS VP Server and Platform Marketing
- 10.
- 11. Why your CIOLikes Cloud ComputingIncreased flexibility on new projectCapital spending can be lowerReduce costs of offsite backupPotentially reduced employee costs
- 12. Legal? “In theory, youhave the same legal protection in the cloud, that you do in your own data center, but….Amazon doesn’t care as much about your data as getting the AG off of their back”--General Counsel of medium size healthcare company
- 13. SQL Azure The onlypurely database offering currently availableMicrosoft’s SQL Specific Platform Offering
- 14. Limited Selection ofSQL Server Features
- 15. 50 Gb sizelimit for an individual database
- 16. No native backupcommand
- 17. MS guarantees 99.95%availabilitySQL Azure DatabaseThe first and only true relational database as a serviceSubtitle colorElastic ScaleSelf-managedDeveloper AgilityBuild cloud-based database solutions on consistent relational model
- 18. Leverage existing skillsthrough existing ecosystem of developer and management tools
- 19. Explore new dataapplication patterns
- 20. Database utility; payas you grow
- 21.
- 22.
- 23.
- 24.
- 25. Easy provisioning anddeployment
- 26. Auto high-availability andfault tolerance
- 27.
- 28. No need forserver or VM administrationWhy your CIO thinks the cloud is cool…Lower upfront capital costsReduces costs of offsite backupPotentially reduce employee costsQuicker spin up of new environmentsIdeal for businesses who need temporary capacity
- 29. Why your LegalDepartment thinks it’s not…A good friend of mine, who shall remain nameless…“In theory, you have the same legal protection in the cloud, that you do in your own data center, but….Amazon doesn’t care as much about your data as getting the AG off of their back”--General Counsel of medium size healthcare company
- 30. SQL Azure CostsSQLAzureWeb Edition$9.99 per database up to 1GB per month$49.95 per database up to 5GB per monthBusiness Edition$99.99 per database up to 10GB per month$199.98 per database up to 20GB per month$299.97 per database up to 30GB per month$399.96 per database up to 40GB per month$499.95 per database up to 50GB per monthData TransfersNorth America and Europe regions$0.10 per GB in$0.15 per GB out
- 31. On-Premise SQL ServerCosts Server - $15kSQL Server Standard Edition (2 CPUs) - $14,200DBA - $85k yearTape backups - $20kUpfront Costs - ~$50kAnnual Costs - $105k
- 32. SQL Azure LimitationsConnectionConstraintsSQL Azure Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all SQL Azure Database customers, your connection to the service may be closed due to the following conditions:Excessive resource usageConnections that have been idle for 30 minutes or longerFailover because of server failures
- 33. Limitations, Cont’dMust haveclustered index on all of your tablesNo Backup CommandSQL Authentication onlyNo SQL Agent*Both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options are set to ON in SQL Azure Database50 GB Limit – Database can be sharded, but it’s not easy
- 34. SQL Azure DatabaseSingleLogicalDatabaseMultiple PhysicalReplicasReplica 1Single PrimaryBased on SQL Server 2008 R2 engineUse same tools and data access frameworksSix global datacentersHigh Availability & RedundancyReads are completed at the primaryWrites are replicated to a quorum of secondariesReplica 2DBReplica 3MultipleSecondaries
- 35. Application TopologiesSQL AzureAccess from within and outside MS Datacenter(On-premises & Azure Compute)SQL Azure Access from outside MS Datacenter(On-premises)SQL Azure access fromwithin MS Datacenter (Azure compute)SQL ServerApp code/ ToolsApp code/ ToolsApplication/ BrowserSOAP/RESTHTTP/SAstoria/REST - EDMHTTP/SWindowsAzureSQL Azure Data SyncApp Code(ASP.NET)App Code(ASP.NET)T-SQL (TDS)App Code(ASP.NET)App Code(ASP.NET)T-SQL (TDS)T-SQL (TDS)WindowsAzureSQL Azure SQL Azure SQL Azure Code NearCode FarHybrid
- 36. SQL Azure DatabaseManagementdemo
- 37. Database Migration fromSQL Server to SQL Azuredemo
- 38. Introducing SQL AzureData Sync”Synchronization of SQL Server and SQL Azure Databases”CTP1 (Now)SQL Azure DatabaseSyncSyncSQL Azure Data SyncRemote OfficesRetail StoresSyncSyncSyncSyncSyncCTP2On-Premises (Headquarters)
- 39. On-Premises to CloudSymmetry SQL Server to SQL Azure SyncSQL AzureBenefitsMakes cloud extension rather than replacement
- 40. Enables moving workloadto cloud in stages preserving investment in existing infrastructure
- 41. New scenarios spanningenterprise, cloudSync
- 42. SQL Azure toSQL Azure SynchronizationBenefitsGeo-synchronization of data across data centers
- 43. Scale-out read orread/writeExtending Data to Retail and Remote OfficesSQL Azure to SQL Server SyncBenefitsShare data with remote and retail offices, bringing data closer to the end-users
- 44. Lower latency –data access doesn’t require round trips
- 45. Higher availability –app still runs if server is unreachable
- 46. Reduced network utilization– most data access is localSQL Azure
- 47. SQL Azure DataSync CTP1(Available now from http://azure.com)Sync End to End ScenariosOffline ApplicationsSyncSyncSyncSyncOn-Premises ApplicationsSyncSyncSyncSQL Azure DatabaseSyncSyncSyncMicrosoft Sync Framework 4.0 CTP available now! Retail & Remote OfficesSQL Azure Data Synchttp://azure.comSQL Azure Data Sync CTP2(Coming in near future)
- 48.
- 49. Reporting Services inSQL Azure
- 50.
- 51. Data Tier ApplicationsData-TierApplication Project(Departmental apps in V1)New feature in SQL Server 2008 R2
- 52. Allows developers toown the database schema and definitions
- 53. Still needs work(more to come)
- 54. Microsoft seems tohave invested deeply to build this, so it will get better (hopefully)
- 55. Interacts with SQLAzure—this is one method of deploying application code thereVisual StudioBuildDeploy.dacpacSQL Azure
- 56.
- 57. What’s in aDACPAC?
- 58. Application Deployment toSQL Azure using Data Tier Applicationsdemo
- 59. SummaryThe Cloud iscoming and we will have to live with it
- 60. SQL Azure isstill developing, but is not a full fledged enterprise RDBMS, YET.
- 61. This fits bestwith either a smaller quickly growing company, or one that needs short term capacity.
- 62. Microsoft seems tohave invested deeply to build this, so it will get better Contact Info@jdanton – TwitterJoedantoni.wordpress.com – Blogjdanton1@yahoo.com -- email
Editor's Notes
- #3 Hello, and welcome to SQL Saturday #59 in New York NY. My name is Joe D’Antoni, and I’m going to talk about Data Tier Applications today. Before I get started on the presentation, let me tell you a bit about myself—I’m currently a DBA at Synthes—we’re a medical device company in West Chester PA. Our specialty areas are trauma and spine, if you break it we have the hardware to fix it. My background is largely in healthcare, I’ve worked in the pharmaceutical and medical device fields, so I’m used to a lot of paperwork and change control processes. I’m currently the Vice President of the Philadelphia SQL Server users group, and we are planning our SQL Saturday for March of 2011. When I’m not working or working with group, I like to spend time cycling, cooking and eating. I love being here in New York. I also like to work with new tech and talk about—I’ll be presenting this topic at SQL Saturday DC, next month. Lastly, if you’re on twitter look me up, I’m @jdanton—we have a great community of SQL folks on there, and it’s an easy way to get questions answered.
- #4 This is what I’m going to try cover today—we’ll go over the basics around cloud computing. I’m going to discuss a couple of business scenarios where SQL Azure might make sense for you or your firm. Then a couple of methods to build and deploy data and code to your SQL Azure Database. Finally, we’ll have a very brief demo of the Reporting Services feature in the latest CTP for SQL Azure. It’s not publically available yet, but it will be shortly.
- #5 Since it is a buzzword—cloud computing has been used in every new product offering I’ve seen recently. I’ve heard private cloud which basically applies to a closed box solution like PDW. I’ll try to break this down into a few isolated categories. I’ve been using cloud computing since the mid 90s—Yahoo Mail.Basically, there are four basic types of cloud offerings. Software as a service—which like my aforementioned Yahoo Mail, Gmail, Oracle Cloud Office, Microsoft Office in the cloud, and an offering like Salesforce.com Platform as a service, is a specific product offering. Specifically the two platforms I’m going to talk about today, which are database specific-Microsoft’s SQL Azure, and Amazon EC2. Infrastructure as a service—this could be a couple of things, a hosted server that you manage, or a hosted Oracle environment that is managed by someone else. We’ll discuss a couple of providers that provide Oracle services. Lastly, I wanted to discuss private clouds—I think this is a buzzword gone awry, but it’s basically a black box that is setup by the vendor. Exadata and Exalogic are good examples of this.
- #7 This just happened a couple of weeks ago—Amazon had a pretty major outage at one of it’s data centers. This took down a lot of websites—four square included. Amazon’s cloud setup does allow for a real DR scenario within their data centres—but most of these firms hadn’t done it that way.
- #8 So why would you use a cloud provider. Reduce backup and storage costs. Potentially reducing employee costs. But another use is to be able quickly add capacity to your environment. Zygna the people who bring you those annoying Facebook games like Farmville, keeps about 40-50% of their capacity at any time at Amazon. They can quickly deploy new capacity, while adding to their own data center.
- #9 The legal concerns about cloud computing are not well defined. But a friend of mine who is the general counsel of a healthcare company said the following. Quote. Just another thing to think about in terms of what data to put out there. Another concern is encrypting any personally identifiable data, as its going across the internet.
- #10 SQL Azure is Microsoft’s cloud offering—it’s basically just a database. There is no server that you have any association with. It has a limited subset of SQL features, but they are getting added rapidly. Much like Amazon MS has multiple data centers allowing users to replicate data for redundancy or speed. There is no licensing involved, just a monthly fee.
- #12 So why would you use a cloud provider. Reduce backup and storage costs. Potentially reducing employee costs. But another use is to be able quickly add capacity to your environment. Zygna the people who bring you those annoying Facebook games like Farmville, keeps about 40-50% of their capacity at any time at Amazon. They can quickly deploy new capacity, while adding to their own data center.These are some of the reasons why your CIO thinks this is a good idea. Especially in a startup type environment, I can wave my MBA hat and agree with him or her. Another good example I heard mentioned recently is Domino’s Pizza—they have something like 3000% more orders on Super Bowl Sunday—so they have scaled up with Windows Azure, just for that event.
- #13 The legal concerns about cloud computing are not well defined. But a friend of mine who is the general counsel of a healthcare company said the following. Quote. Just another thing to think about in terms of what data to put out there. Another concern is encrypting any personally identifiable data, as its going across the internet.
- #17 A lot of these requirements are due to the fact that you are operating in a multi tenant environment, and MS is trying to limit massive IOPs. Microsoft does replicate your data to three data bases, so in theory you have that redundancy. However, after last months Amazon outage—I’d like to have the DR plan of running in a second MS data center (and paying for) or Bulk Copying on regular basis, but I’m paranoid about data loss that way. There is no SQL Agent, however you can run SQL Agent jobs from an on-premise SQL Server, which connect to your SQL Azure database.We can only grow a database up to 50 GB—that number has increased—it was initially 5 or 10. I forget. The term sharding comes to us from the NOSQL community—it’s a way of spanning databases across multiple servers using horizontal partitioning. There’s a white paper on TechNet (linked in this deck) on how to do it, but it’s pretty complex, and something you’ll need to think about early in your application development process.
- #18 Slide ObjectiveUse this slide to transition into an explanation of SQL Azure Database (Reporting and Data Sync will be covered later)Explain at a high level how SQL Azure worksSpeaker NotesDesign Principle of SQL Azure: Focus on combining the best features of SQL Server running at scale with low frictionSQL Azure is a high availability databaseAlways three transaction consistent replicas of the databaseOne primary replica; two slave replicasFailure of a replica will result in another replica being spun up immediately by the fabricFailure of the primary replica means a slave replica will become the primary and a new slave will spin upMinimal down timeTypically just a few dropped connectionsEasy to code for the failover scenario- if you are ding god connection management and error handling will be fineClustered index required on all tables to allow replicationNotesUseful article from SQL Azure teamhttp://msdn.microsoft.com/en-us/magazine/ee321567.aspx
- #19 These are three sample application topologies that demonstrate different ways of using Azure
- #20 Managing Azure from both the Web and SSMS—mention that this requires SSMS 2008R2
- #21 Use Code Plex Tool here—the path going forward will be to use Data Sync (discussed in coming slides)
- #27 http://netflixpivot.cloudapp.net/
- #28 Top FeaturesThe Business Intelligence Design Studio (BIDS) offers a consistent report authoring experience that make your reports rich with visualizations – maps, charts, gauges, sparklines and more.Export to various popular file formats, including Excel, Word, HTML, PDF, XML, CSV and ATOM feeds.Scale and flex to meet elastic demand. Our cloud services platform automatically scales up and down to meet demand and has built-in high availability and fault tolerance. Microsoft SQL Azure Reporting lets you use the familiar on-premises tools you’re comfortable with to develop and deploy operational reports to the cloud. There’s no need to manage or maintain a separate reporting infrastructure, which leads to the added benefit of lower costs (and less complexity). Your customers can easily access the reports from the Management Portal, through a web browser, or directly from your applications.While SQL Azure Reporting is not yet commercially available, you can register to be invited to the community technology preview (CTP).Use SQL Azure Reporting to:Deliver rich insights to your customers without building and managing a reporting platform in house. Create robust, easy-to-read reports available directly within your applications, on the SQL Azure portal or online via a browser. Keep your data secure while offering access to even more users. The rich authentication/authorization model gives reliable, secure access to reports and underlying data
- #31 So what are the requirements for this? It is part of Visual Studio 2010 functionality. Additionally, to deploy data-tier applications, you need to running SQL 2008 R2 (enterprise
- #32 This is a view of an expanded DACPAC—as you can see it consists of 3 SQL scripts—the main schema script and any pre and post deployment scripts. There are a couple of XML files that define the physical and logical models around the data. As vendors begin to use this technology as method to roll out databases for their technology, as DBAs this will be the place where we can preview their schema and identify any potential issues. Also, not that you would do this, but always review code in a dacpac you downloaded from an unknown source, to ensure that there isn’t any malicious code. In Visual Studio 2010 Premium and higher, you can compare two DACPAC schema files, or just use a file comparison tool.