Azure SQL Database is a Database as a Platform service designed for applications that will use database as self-contained service. Databases can be grouped together to simplify management options or share the resources. There are different options that can be used to bound databases in the groups, which will be explained in this post.
Databases in Logical server
Logical server is a default container for Azure SQL Database. Logical servers enables you to perform administrative tasks across multiple databases – including specifying regions, login information, firewall rules, auditing, threat detection, and failover groups.
All databases within the server are self-contained with independent service tiers that can be specified per each database. Each database can be independently scaled up or down by changing performance tier on the database, which will not affect other databases.
Databases cannot share resources and each database has guaranteed and predicable performance defined by its own service tier. Some server-level specific features such as cross-database querying, linked servers, SQL Agent, Service Broker, or CLR are not supported in Azure SQL databases placed in logical server.
Databases in Elastic pool
Databases that need to share resources (CPU, IO, memory) can be stored in elastic pools instead of the logical server. All databases within elastic pool share the same resources associated to the elastic pool level.
Currently there are three service tiers in elastic pools – Basic, Standard, and Premium. Databases within the elastic pools cannot have different service tiers because they share resources that are assigned to entire pool. Resource usage in one database might affect others; however, you can specify reserved performance for database in the pool that will guarantee minimal amount of resources that database can have.
This model is a good choice for databases that have performance peaks or heavy usage in different time periods because the amount of resources associated to the pool can be assigned to the databases that need them while the others are inactive.
Elastic pool model is designed for resource sharing and it still does not support server-level features such as SQL Agents, Service broker, etc. There are other mechanisms that can be used as a replacement of these features such as Elastic jobs and Elastic queries instead of some server level features.
In May 2017, the concept of a managed instance was announced. A managed instance would provide near 100% SQL Server compatibility with the benefits of platform as a service.
With a managed instance, features like SQL CLR, SQL Server Agent, and cross-database querying will be fully supported. Furthermore, a managed instance will have of the current capabilities of managed databases, including automatic backups, built-in high-availability, and continuous improvement and release of features in the Microsoft cloud-first development model. These capabilities are introduced in the remainder of this article.
Databases in Managed instances are designed for easy migration to cloud of databases that have dependency on server-level features or require nearly full compatibility with the latest SQL Server 2017.
Managed instances are currently in private preview.