Microsoft calls the Azure SQL Database a managed cloud database; however, it does come attached to a server. In this case, one that’s called a “logical server”.
Many of us database experts have worked extensively with servers, as that’s where our databases usually exist. But if we’re promised only a database, why does it come with a server? And what’s the difference between a server and a logical server, then?
Traditional database servers
If you’re reading this article, there’s a good chance that you know what database servers are. To make some differences more clear as we move further, let’s do a quick recap.
When thinking about servers, there are three main components we typically work with. These are: server, instance and the database. How these relate to each other:
Server
Server is the physical or virtual machine that is responsible for running services and applications (like SQL Server and the OS) and managing resources like network, memory and storage.
Instance
SQL Server instance is just what it sounds like. A unique installation of SQL Server inside the server. There can be multiple instances running on the server, each operating independently of other instances. Many of the SQL Server configurations are set at the level of instance. This includes security (logins) and resources (CPU, memory). There are also many objects that reside in the instance level, such as Agent Jobs, Linked Server definitions and other objects.
Database
Databases are hosted in the instance, and there can be numerous databases in each instance. The newer versions of SQL Server also allow us to set some configurations at the database level, which were previously only available to be configured at the level of the instance.
While servers can host multiple instances, and each instance can contain multiple databases, there are some limits to these numbers. You can find this information from the Microsoft’s Maximum capacity specifications document.
While traditional servers provide the most flexibility and features for us to use, they also require extensive administration, with responsibilities often crossing over to multiple teams (AD, Storage, Windows).
Logical servers
Logical servers, despite their name, aren’t servers in the same way as we traditionally consider servers to be. Rather, it is an abstraction that’s built on top of the physical implementation. This approach hides many of the complexities of managing the servers and the instances.
While the picture is just about the same, except for the missing instance, there are some considerable differences between the two remaining components.
Logical Server
Logical servers can be considered to be a container for SQL databases, but they also act as a management layer. They differ from traditional servers in a sense, that you don’t need to patch them, or perform typical admin tasks on it. Moreover, you don’t assign resources (such as compute, memory or I/O) to logical servers. There are however many things you can configure at the logical server, that will be applied to individual databases as well. To me, the whole point of having the logical server is, is to be able to apply uniform configurations to databases that share similar requirements.
Database
The databases in Azure SQL Database also differ quite a bit from the regular SQL Server databases. Many of the things you’d normally set up at the level of instance have been shifted to the database level, such as compute and memory settings, backups, some security settings, firewall, etc. The databases also leverage Resource Governor, in some form or another. While many of the instance level features are available at the database level, it doesn’t include all of them. A good example of this is SQL Agent jobs. Azure SQL DB doesn’t have the SQL Agent, so you need to use other cloud provided technologies to find a replacement for them.
While some configurations from the Logical Server are applied to the databases for easier and more scalable management, resources, such as compute and storage are defined per database or per elastic pool.
What can you do in the logical server?
Azure SQL Database comes with many configurations knobs to turn. While in most cases you can also configure these for individual databases, logical server provides an alternative. As mentioned earlier, logical server does provide a management layer. If you have databases that share similar requirements, configuring these on the server level makes more sense. Here are just a few examples of what these configurations are.
Firewall Rules
While it’s possible to configure firewall rules at the database level, I feel that it’s a surefire way to create an administrative nightmare. For firewall rules, I’d definitely recommend finding databases that have similar requirements for access, and grouping them into a single logical server and configure it there.
Furthermore, firewall rules at the database level are managed by using T-SQL. This makes the database firewall rules complex to manage and maintain.
Auditing
Auditing is another perfect example of where using logical servers makes sense. Grouping together databases with similar auditing requirements shouldn’t be too difficult. Especially, if you’re managing a larger database estate.
Microsoft Defender for SQL
I don’t know what the good scenario for configuring Defender only for a single, or a set of databases, would be. At least when comparing it to just enabling the thing on the all databases hosted in the logical server.
Wrapping it up
Hopefully, this post helps to clarify what is the logical server, and how it makes managing large-scale database estates easier.
Leave a Reply