Sql Azure - separate servers?

Question

Let me preface this question by saying I'm neither a database nor Azure expert.

It appears that MS charges by the size and number of Sql Azure dbases and not by the number of servers. Thus, cost-wise it seems feasible to put a single dbase on each server instance. My understanding is that sometimes it's more beneficial to put databases on separate, physical servers rather than on one machine in high use scenarios. Would/could the same be applied to Sql Azure virtual servers? Here's what I could find about Sql Azure server instances:

A SQL Azure server is a logical group of databases and acts as a central administrative point for multiple databases. Each SQL Azure server includes logins similar to those in instances of SQL Server on your premises." (SQL Azure Provisioning Model)

Let me also add that though all of the databases will be accessed and used by the same Web Roles, none of the databases will require data from each other, directly.

Answer 1

The physical location of your database and the number of servers have absolutely nothing to do which each other. Wether you have 10 servers and 10 databases or 1 server and 10 databases, it is the same. In the end you will have 10 databases, located physically on various parts of the cloud in a distribution that you absolutely have no control over. They may be all 10 on the same physical host (unlikely), or each one on a separate host (much more likely), you won't be able to control nor will you be able to actually find out.

Answer 2

Everybody is saying the azure database server is a just a logical concept that don't need to worry about it, but, there are some limitations, in the portal, the server list, it actually tells you the available quota on that server, which means you can not really put too many databases on 1 server, so, we can not really think it of just as a logical concept, otherwise , why there is a limit there. for example, right now you can not put 3 P3 tier database on one server, because the maximum DTUs of one server is only 1600 DTU, and 1 p3 tier database will take 800 DTUs.

So, i can not just think of the azure database server as just a logical concept server to manage the credentials. If there is a limitation there, I guess there will be something somewhere affect the performance in general.

Unless someone can help explain where does that limitation come from.

Actually, with the retired web/business tire, a bad neighborhood could affect your database performance, this made more wondering the server is really just a logical concept?

Answer 3

Unless you're expecting to be under a really high load (around about 500 transactions per second I think, but I can't find the a reference to this anywhere) I think it's unlikely that you'll gain any benefit from having the databases on different servers.

SQL Azure does all sorts of magic under the hood to spread out your data. Even though two databases are on the same server, they're quite likely running on different machines (and each database is probably being load balanced across multiple machines)

Having databases spread across different servers does create a slightly higher management overhead, multiple sets of users, firewall rules etc.

Answer 4

Servers are a virtual/logical concept. Being in the same server means that you're in the same datacenter, but does not mean you're on the same physical server. Every database you have will end up redundantly spread across several different physical servers (so that you will be unaffected in the event of a hardware failure) and it's very unlikely that they will end up being the same physical server as our datacenters house a large number of servers.

You should use a different server only when you want to be in a different datacenter, or if you want a logical separation. I'm not aware of any performance- or load-related reason to have a different server.

(There are currently performance/load related reasons to have separate databases in some cases.)

Answer 5

The SQL azure server is a logical entity; So different servers doesn't necessarily means different physical server, they may very well be separete VMs on the same physical serever.

Answer 6

There are some limitations to putting all of your databases under one server. Each database server can support up to 150 databases (including the master database).

Another thought to consider is if your server or the server's VM begins to have technical or performance issues and/or is undergoing updates. If you place your databases on different servers it might help mitigate some risk as if one of your database servers is down, not all of your databases are offline.

Reference: http://msdn.microsoft.com/en-us/library/ee336245.aspx#dcasl

Answer 7

From my personal experience, there is a little issue with respect to storage, i have a server S1 and I created two databases under that server both as basic.

Now, storage wise it divides/shares the space between two. If there are three, it divides between three.

So I would recommend creating separate servers instead of creating databases under one server.

Answer 8

If one puts one's databases on one SQL Server then one can save money by sharing resources between them using elastic pools

SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.

This question predates the release of Azure elastic pools by five years (they were generally available in 2016!)

Another answer relates to how you manage your security set-up. Azure has both a server-level firewall and a database-level firewall. See Azure SQL Database and Azure SQL Data Warehouse IP firewall rules This might influence you as to where you would want to set up your databases.