19

SQL Azure has a database size limit of 150 gb. I have read through their documentation several times and also searched online but I'm unclear about this: Does using federations allow a developer to grow beyond a 150 gb data base? For example can I have several 150GB federation members.

If not, how can I handle a database larger than 150 gb on Windows Azure?

basically, How do I scale out beyond 150 gb on Windows Azure

If theres no other way is RDS a good alternative(share any other alternatives)

astaykov
  • 30,768
  • 3
  • 70
  • 86
Student Loan
  • 193
  • 2
  • 7

5 Answers5

15

Currently it is not possible to have a single database larger than 150G.

The only approach is to either split the data into multiple databases, one account can have up to 149 user databases plus the master DB, or use SQL Azure Federations. Currently, if I am not mistaken, the total number of Federations supported is Int16.MaxValue - 1. Each federation is actually a separate database, transparent to the developer, which can be up to 150GB.

However, SQL Azure Federations has its own pros and cons, along with some data access layer re-factoring. If you are interested you may check out these cool videos on SQL Azure Federations:

UPDATE

I will not completely agree with @ryancrawcour. What he explains is just the peak of the iceberg lying bellow the water. The amount of required re-factoring really depends on how data is consumed from the application. I will just mention a few factors for considerations (which are not complete picture at all). Consider any of the following:

  • Data that is common for all federations (how you get this data)
  • Stored proc, that post-processes data - you have to iterate in each and ever federation member and execute that stored proc. There is no way to execute the Stored proc once and process data in all the federations.
  • Aggregate data, which is spread across more than 1 federation member
  • List data from more than one federation member.

These are just few operations that you will need to consider, and that does not require "just change in connection string and execute one use federation ..." before each query. Actually using SQL Azure Federations you don't need to change the connection string at all. It is all the same SQL Azure connection string. The "USE FEDERATION ..." statement is what you have execute before each query. But it is way not just the only thing. And how about if one is using EntityFramework (model first, or code first, or whatever). Things get even more complicated and need real understanding of SQL Azure Federations.

I would say that SQL Azure Federations is different way of thinking about data, about modelling and normalizing.

UPDATE 2 - new Database sizes announced by Microsoft

As of 03. April 2014 the maximum size for a single Database has been increased to 500GB. The only available information to date is here. Be aware that the management portal still doesn't show this option (as of Today and now: 4. Apri 2014, 15:00 GMT+0:00).

astaykov
  • 30,768
  • 3
  • 70
  • 86
  • 3
    SQL Database Federations provides horizontal scaleout of both database size AND performance. Since each SQL Database instance has the same performance characteristics, regardless of size, it is very likely that ten 15GB databases will perform significantly better than one 150GB database. – Neil Mackenzie Sep 13 '12 at 05:48
  • Thanks, Nail for that clarification – astaykov Sep 13 '12 at 06:11
  • Thanks astaykov and Neil for the responses. So I guess SQL Azure federations solves this problem and enables me to go beyond 150 gb. Thank you once again. – Student Loan Sep 13 '12 at 06:59
  • 1
    you do not have to re-architect your entire DAL! not at all. It requires a small change on the connection, and a new command sent with every CRUD command. but this does not require a complete rebuild of your DAL. it is easy to slightly refactor the DAL with base clases etc. – ryancrawcour Sep 14 '12 at 03:37
  • @ryancrawcour: This assumes there is a DAL in the first place... (OK sane companys have one, but there are in-sane companies out there) – Stefan Steiger Mar 12 '15 at 13:30
2

There is also the new Azure feature of persistent VMs (currently in preview) which will allow you to migrate your on-premises applications to cloud with minimal changes.

Further reading: Infrastructure as a Service Series: Running SQL Server in a Windows Azure Virtual Machine .This guide might be helpful as well.

Edit

Here is a comparison with Sql Azure

NoviceProgrammer
  • 3,347
  • 1
  • 22
  • 32
  • This is correct, however I question the performance of a Windows Azure VM with single SQL Server DB larger than 150GB vs Windows Azure SQL Database (aka SQL Azure) with Federations. Plus on the Windows Azure VM one will have single point of failure. While the SQL Azure comes out of the box with 3 independent copies of the data (a this counts towards federations too). – astaykov Sep 14 '12 at 07:07
  • The VHD for the VM itself will have multiple copies but I guess you will need to have mirroring in place for managing failover – NoviceProgrammer Sep 14 '12 at 20:00
  • Since VMs are out of Preview they now fall under the Service Guarantee for performance. There is a good article on MSDN detailing the different types of disks you can use on Azure and the performance implications of each, but a VHD will have very good performance! You will need to setup an enterprise level architecture taking this approach however, which can be costly... – emalamisura May 14 '13 at 20:37
2

I've been looking for these same answers a while ago. In addition to the answers Anton provided (which are very accurate), I found that you can make your WAVM with SQL Server installation redundant through load balancing and mirroring.

The advantage of WASD is that everything is automated. E.g. when your WAVM instance is taken out of the roulation of the load balancer, you'll need bring a new one up yourself. WASD takes care of all of this.

With WASD Federations you're able to scale to 75TB of data (if I remember correctly), while with WAVM with SQL Server you can scale to 16TB tops.

Also with WASD Federations you can more granularly divide the SQL Workloads.

Regards,

Patriek

Patriek van Dorp
  • 106
  • 3
1

While considering your scale options, be aware that, as of April 3 2014, Microsoft announced upcoming changes to SQL Premium, including ability to scale each SQL Database instance to 500GB (along with geo-replication, self-service restore, and higher uptime SLA). No date has been announced yet, but you can read about the announcement details here.

David Makogon
  • 69,407
  • 21
  • 141
  • 189
  • when this information will be posted publicly on Microsoft Site and Pricing updated. As of now (4.4.2014) the pricing still shows 150GB as max DB size. – astaykov Apr 04 '14 at 13:58
  • I suspect it's being rolled out "very soon." That said, it's not available in the sizing dropdown as of this morning. I'll edit my answer to state it was *announced*. But given it was officially stated, it's fair to say you can plan on the 500GB target. – David Makogon Apr 04 '14 at 14:15
  • 1
    @astaykov - answer updated with link to blog post describing details. The size increase, along with other features, are exclusive to Premium. – David Makogon Apr 08 '14 at 10:55
0

There is now a 1 Terrabyte tier available - see https://azure.microsoft.com/en-us/pricing/details/sql-database/ and look at the Premium level.

Joe Healy
  • 5,769
  • 3
  • 38
  • 56