54. Microsoft seems to have invested deeply to build this, so it will get better (hopefully)
55.
56.
57.
58.
59.
60. SQL Azure is still developing, but is not a full fledged enterprise RDBMS, YET.
61. This fits best with either a smaller quickly growing company, or one that needs short term capacity.
62.
Editor's Notes
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
These are three sample application topologies that demonstrate different ways of using Azure
Managing Azure from both the Web and SSMS—mention that this requires SSMS 2008R2
Use Code Plex Tool here—the path going forward will be to use Data Sync (discussed in coming slides)
http://netflixpivot.cloudapp.net/
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
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
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.