When people say “cloud”, they’re simplifying a lot of different solutions into a single catchphrase. Let’s break out the different options and compare them.
1. SQL Server in Amazon EC2 and Azure VMs
Amazon EC2 is a virtualization platform. Amazon buys servers, installs their secret sauce software, and rents you Windows virtual machines by the hour. Microsoft offers a similar product, Windows Azure Virtual Machines, that just went officially live.
You can rent a blank Windows VM without SQL Server installed, and then install SQL Server yourself just like you would on-premise. That’s a licensing mess, though – you have to use your existing SQL Server licenses or buy new ones for your VMs. That doesn’t make much financial sense. Instead, Amazon and Microsoft will rent you a Windows VM with SQL Server already configured, and your hourly fee includes the SQL Server licensing.
SQL Server runs just as it would in your own datacenter, which means you can use this as a disaster recovery option for your on-premise SQL Servers. You can do log shipping or database mirroring up to SQL in the cloud, running in Amazon EC2 or Microsoft Azure. When disaster strikes, fail over to your EC2/MS infrastructure, and you’re off and running.
The term “cloud” conjures up images of easy scalability and redundancy, but that’s not really the case here. We’re talking about a single virtual machine. This isn’t much different from running SQL Server in a VMware or Hyper-V guest in your own datacenter or in a colo datacenter. You can use all your traditional tools and techniques to manage SQL Server, which is both a pro and a con. If you need to patch it or scale out to multiple servers, there’s no tools included here. I still consider this the cloud, though, because the infrastructure and licensing are managed by somebody else. It’s easy to get started with one – or a hundred – virtual SQL Servers with no initial investment.
This method is the most conventional, and as I explain other options, I’m going to move from conventional to really-far-out-there. SQL in EC2 or MS VMs just works – it’s easy to understand and leverage without changing your code or your techniques – but it doesn’t bring a lot of the cloud’s benefits.
2. Amazon RDS for SQL Server
Instead of running SQL Server in an EC2 virtual machine, let’s start giving up a little bit of control in order to get more of cloud benefits. The next layer of clouds is Amazon Relational Database Service (RDS). Here, Amazon builds a Windows VM, installs SQL Server, configures it, and manages both Windows and the SQL Server service for you.
This is still the real, true blue SQL Server you know and love – all of your commands still work exactly the same as you’re used to, as long as you don’t try to access the server’s local drives directly. (Example: you can’t upload a flat file to the server’s C drive and then try to BCP data from that file into SQL Server.)
Amazon RDS is kinda like they’re the DBA, and you’re a very, very powerful developer. You can create and drop databases and users, but you can’t Remote Desktop into the SQL Server, nor can you access the drives.
Amazon RDS takes a few job duties away from you:
- Storage management – want faster storage? Just pick (and pay for) more IOPs. There’s no arguing with the SAN guy.
- Monitoring – Amazon CloudWatch tracks performance metrics and sends you emails when they’re outside of your thresholds.
- Patching – You pick the major/minor versions you want and when you want patches applied.
- Backups – You pick a time window for the full backups, and Amazon manages it using storage snapshots. You can restore from snapshots just by pointing and clicking in the management console.
But herein lies the first real compromise: you can’t restore from anything except snapshots. You can’t upload your own database backup file and restore it. To get data into Amazon RDS, you’ll want to export it to a file, upload that file to Amazon S3 (cloud-based file storage), and then import it. This also means you can’t use Amazon RDS as a participant in log shipping or database mirroring.
Microsoft doesn’t have a competitor to Amazon RDS for SQL Server today. Well, I say that, but some shops already manage their SQL Servers this way – they have an internal admin team that manages Windows and SQL. Departments get access to create & drop databases, change code, etc, but they don’t get access to the server’s desktop or backups. This doesn’t really compete with Amazon RDS, though – RDS is for companies who are too small to have this kind of internal engineering infrastructure. (Or for companies that want to get rid of this large engineering burden, I suppose.)
3. VMware vFabric Data Director
vFabric Data Director is a lot like running Amazon RDS in your own datacenter, but you can control the base Windows virtual machines. You build a Windows template to be used by default whenever a new SQL Server is created. VMware vFabric manages the implementation details for backups and high availability.
vFabric also supports Oracle, PostgreSQL, and Hadoop for a single pane of glass to create and manage your database servers. When someone in your company wants a new database instance, the sysadmins open up vFabric, configure it, and within a few minutes, it’s up and running.
vFabric makes sense for 100% virtualization shops who aren’t interested in moving their databases up to the cloud, but they want easier database management integrated into their virtualization tools.
Microsoft doesn’t have a competitor to VMware vFabric Data Director today. In theory, you could build your own alternative using System Center and a whole lotta scripting. That is left as an exercise for the reader.
4. Microsoft Windows Azure SQL Database (WASD)
The artist formerly known as SQL Azure takes the least conventional approach of all. While it’s technically built on Microsoft SQL Server, that’s like saying your pants are built on your underpants. They both cover your back end, but you can’t use them interchangeably.
Microsoft essentially built a new product designed for common database storage requirements. Like your underpants, you only get a minimum of feature and command coverage here. The new features and commands Microsoft has been adding to the boxed product for the last couple of versions just aren’t available in WASD including partitioning, Resource Governor, Service Broker, and CLR. But hey – are you really using those anyway? Most folks aren’t.
Rather than building large databases, WASD encourages developers to shard out their data across multiple smaller databases. While on-premise SQL Servers have had techniques to do this in the past, Microsoft started over and developed a new technique that makes more sense for cloud implementations. Again, though – we’re talking about a difference from the boxed product, something else that developers have to learn differently. As we’ve gone up this ladder into the clouds, we’ve been handling our problems differently. WASD’s partitioning technique is a good reminder that once you’ve gone this far up into the cloud, you’re dealing with something very different from SQL Server. You’re not going to take large volumes of code written for SQL Server and simply point them at WASD – you’re going to be doing a lot of testing and code changes.
Like Amazon RDS, there’s no backup/restore functionality here to get your existing data into the cloud. You’ll be exporting your data to a file, loading it into the cloud, and then…staying there. There’s no database mirroring or log shipping to/from Azure SQL Databases to on-premise SQL Servers.
Which One is Right for You?
If you’ve got an existing app, and you just want to cut costs without changing code, you can get started today with any of the first three options. They support the same T-SQL commands and datatypes you already know and love.
If you’ve got an existing app, and you’re willing to make code changes & do testing, you might be able to save even more plus gain new flexibility by going to Windows Azure SQL Database. In most cases, though, the cost savings won’t come anywhere near the costs required for the code changes and code review. We haven’t seen a case yet where the tradeoff made sense for our clients. I’m sure it’s out there – the perfect example would be a small amount of code that the developers know very well, can easily modify, and produces large server loads in short bursts.
If you’re building a new app from scratch, then let’s take a step back and survey the database industry as a whole. This is an incredible time to be in databases and there’s a bunch of really good options we didn’t even cover here.
Learn more about our SQL Server in Amazon cloud resources page.
32 Comments. Leave new
Very nice breakdown of the different options – I hadn’t heard of the vFabric before, but it sounds pretty interesting.
Thanks, sir!
You might mention which editions of SQL Server Amazon EC2 supports. I don’t believe they support Enterprise Edition, only Web, Express and Standard editions.
Hi, Larry. You can run Enterprise, but you have to bring your own licensing and install it yourself. (That’s the first option.)
SQL Server RDS also lets you bring your own EE license.
Very good summary of options. I wrote several articles on Windows Azure SQL Database.
The cloud management interface needs some work. For instance, no option to manage functions or user permissions.
Another thing, both data and index pages are rolled into one. How do you know if a table is over indexed? I usually like looking at the storage indicators.
I’ve often had troubles with using different VM solutions. So I’m considering using one of the above for an easy on-demand clean demo environment.
What do you think about that idea? The only thing I can think of is that it creates a new dependency for my demo. My demo now depends on a reliable internet connection where it didn’t before.
Any others?
That’s also the one thing I’d never rely on for demos – the Internet. I’ve seen so many cloud demos fail horribly because of that.
I cancelled my Amazon subscription after they charged me 0.43 for an RDS with two tables. Both tables were small and I only ran a couple queries a week. I was afraid of the cost if I had actually started to move objects.
Sticking with Azure for now 🙂
Michael
Wow – most folks I know would be excited to have a dedicated SQL Server at their disposal for $.43 per month. But hey, whatever floats your boat. 😉
Hi, Brent, nice article. Do any of these cover performance issues? for example, one day, some user query runs 10 times slower than a regular day. Who is taking care of it? or Who is calling who?
Thanks! With any of those options, performance issues are still up to you. You have to figure out where the bottleneck is and contact the appropriate staff or vendor.
Thanks for the reply and the article.
Then, can I run powershell and enable MDW? It seems you are saying some access will be restricted.
Jason – the use of MDW will depend on which solution you choose. They each have their own capabilities. Feel free to dig into the one that interests you and start checking it out. Thanks!
The question is would providers alow such activities? not when or whether I should do it.
Jason – that’s a question you would need to ask each provider.
It is likely to be a ball kicked around and around. Old timer have seen that in private hosting. It is just the new name called cloud.
I am using Amazon RDS on the free tier (check it out), I by mistake created an instance on non-free ties (mini instead of micro) and they charged $11, I called them and explained that I install the instance by mistake and they took off the charges. They also explained to me how to put on alert that will trigger any time I am being charged.
Brent, thoughts on federations know that Microsoft has somewhat retired it? Just saw a presenter mention that they were going away, and the MS page you linked says it’s going away on certain tiers. “Consider deploying custom sharding ” does not fill me with confidence.
Mbourgon – actually, the mumbling about federations had been going on for quite a while. In Conor Cunningham’s presentation about it at PASS last year, he mentioned that you should avoid using them.
I’m actually a big fan of custom sharding – built-in sharding features are rarely right for everybody’s applications. Sharding is more a feature of a development app rather than the database.
Hii All, can you guide me how to implement the FileTable and its related directory in Amazon S3.
Thanks in avance.
Manoj – sorry, no, SQL Server’s FileTable functionality is built into the SQL Server engine, not an external service like S3.
Brent,
What about using the cloud for Dev and Test environments?
Joe – in most shops, developers want their environment refreshed from production data regularly. The challenge becomes uploading that production data into the cloud on a regular basis. In a perfect world, we’d use cloud-based backups as our offsite DR plan, and then use them for refreshing dev/QA environments. In practice, outgoing bandwidth is often a challenge for that.
The term “cloud” conjures up images of easy scalability and redundancy, but that’s not really the case here ~ Brent Ozar
Not only has Brent nailed it %110 with this description but he’s quickly zeroed in on the fact that “The Cloud” is a marketing scheme designed to convince as many as possible to hand over their data to someone else; a cloud service provider. Remote computing is not a new idea but the “Cloud” is a new marketing approach on the technology and its one that’s designed to convince as many as possible that if they just embrace the could then all their troubles will go away and at a fraction of what it costs to do this yourself.
Prior to “The Cloud” if law enforcement or some agency of the government wanted to take a look at your data they would have to get a warrant and even then it would be difficult if at all possible to do this without your knowledge or at least without someone in your company knowing.
With the advent of the cloud these agencies can now easily access your data and not only are the cloud providers prohibited from informing you of this, they are encouraged to lie to you under the threat of legal recourse by the government. When the feds came knocking at the doors of Lavabit and demanded access to read the email of its users the founder showed a level of integrity not typically see today when he choose to shut down the service instead of complying with the feds unconstitutional demands.
For those who believe in the false ideal ology that “if you have nothing to hide” you need to take a step back and open your eyes. The recent IRS scandal has proven that abuse by federal agencies can and will occur and there will be little if any accountability let alone any justice served.
There is an excellent article on TechRepublic about the dangers of unintended legal issues with the cloud that you can bet no Cloud Service provider is ever going to tell you about and that’s assuming they are even aware of it.
If nothing else remember this, no one, not any Cloud Service Provider will ever protect your data the way you would and if law enforcement or some government agency ever takes an interest in you and decides to take a look at “your Cloud”
Just remember that they may be required by law to deny this has happened to you and may even be required to lie to you about it. The cloud is and always has been nothing more than an option for storing some or all of your data off sight.
Hi Brent
I liked the idea of VMware vFabric Data Director so I can host data in own cloud / data centre. It seems this product is now end of life.
Do you know of any similar products still available in the market? I was looking for ways of reducing our licencing overhead with the costs of SQL server increasing, I also like the concept of database as a service taking away DBA bottlenecks in projects.
Adam – not on-premises, but folks I see going in this direction tend to go with Amazon RDS or Microsoft Azure SQL DB. If you’re interested in reducing DBA costs, you’re also usually interested in reducing sysadmin and hardware costs, too.
If you’re already using VMware, then I’d suggest you talk with David Klee (@kleegeek on Twitter). Not pushing him as a sales call, more a guy who knows what’s what with VMware.
No relation or shilling for him, just have spoken with him at multiple SQL Saturdays and always come out impressed.
Brent, could you update this article as I believe both Amazon RDS and Azure SQL have changed and have added new features.
Arnold – unfortunately, I can’t keep all my past posts up to date, but you’re welcome to subscribe to our blog and get news as it comes out. Thanks!
Very well written. And helpful. Thank you!
Thanks for this wonderful information. Kindly provide more information like this.