Azure SQL DB - A Database for Microservices
Sunday, October 2, 2016
One of the key principles of microservices is that each microservice encapsulates its own isolated data store. In reality, it can be costly to provision a separate, geo-redundant database for every microservice using a database-as-a-service offerings such as Amazon RDS. We can also do it ourselves using virtual machine or containers, but it's difficult to create something highly available.
This is where, I think, Azure SQL Database is very interesting. A single SQL Azure database starts at $5 a month, and provides a whole slew of features including active geo-replication.
In this article, I'll go over some features of SQL Azure DB and take a look at how it's the ideal relational database for microservices.
Are relational databases still relevant in a microservices world?
This is a great question, and one we should get out of the way. I think if microservices are designed properly, we end up applying Domain Driven Design and splitting our application into bounded contexts. Each bounded context contains one or more microservices. It's usually pretty natural for an aggregate in a bounded context to be represented as a document. This means a document database like Azure DocumentDB or MongoDB are good candidates for data persistence.
Check out my article on using DocumentDB with MongoDB drivers
That being said, there are still valid reasons for sticking with relational databases. And the line is blurring between relational and NoSQL databases with features such as JSON document support that are now in Postgres and SQL Server (and SQL DB).
What is Azure SQL Database?
Azure SQL DB is Microsoft's database-as-a-service offering. It's built from the ground-up to be a cloud-native database and its API is highly compatible with the SQL Server that we might install on-premises.
When we provision a database in a service like Amazon RDS, it is actually provisioning a VM with the database server binaries running on it. In contrast, when we ask Azure SQL DB to provision a database, it simply provisions a database with the desired throughput. No VMs.
In Azure SQL Database, we still have a "database server", but it's not a physical server or VM. It is a logical entity. It's there mainly to allow us to manage things like security across a group of databases.
For a while, Microsoft was pretty outspoken about SQL DB is built on Service Fabric. And that is why it is able to provision and scale databases extremely quickly.
Starts at $5/month
Yep. It's pretty incredible what they're able to offer for $5. Every feature we can get from their most expensive tier (except in-memory OLTP storage) is available in the $5/month Basic tier. So $5 gets us features like active geo-replication and point-in-time restore!
In my experience, the $5 Basic tier has plenty of throughput for a simple schema like ones we'd typically find in microservices. It is certainly enough for many pre-production environments! I have benchmarked one at about 15 queries per second for indexed lookups over millions of rows.
Scaling with virtually no downtime
Because Azure SQL DB is not bound to a virtual machine, we can scale a database up and down in minutes with almost no downtime. There's no need to know ahead of time how big of a database to provision because we can always change our minds later.
Scaling can be performed via Azure Resource Manager templates or the portal (pricing is shown in Canadian dollars)...
Active geo-replication
If you are running a database in production, chances are you'll want replication to another datacenter. With SQL DB, we get active geo-replication. This means not only is the data replicated to one or more secondaries another datacenter, we can read from these secondaries as well!
It's easy to set up geo-replication via the portal. Simply click on up to four locations to put your secondaries. Secondaries are billed as another database. So for a Basic database with one secondary, it's $10 (2x $5) a month. I think we can afford that!
Point-in-time restore
SQL Database handles backups automatically for us. We can restore our database to any point in time in the past 35 days (7 days for the $5 Basic tier).
The restore process is as simple as picking a date and time in the portal:
Automatic tuning
With Azure SQL DB, it's like having our own DBA. It can monitor our queries and suggest indexes to create or delete. It also recommends when queries can be parameterized and when it notices schema issues.
Here's an email that shows up when it has detected an improvement to recommend. Notice that it'll even offer to fix it at the click of a button:
Yep, it's recommending an index be added to a table used by Octopus Deploy.
And if clicking that button is too difficult, we can tell it to automatically apply any recommendations!
Automatic threat detection
Another cool feature of Azure SQL DB is automatic threat detection. It can detect potential security issues such as SQL-injection attacks or unauthorized logins. As with the automatic performance tuning stuff, this turns on with a few clicks as well.
Summary
Because a microservice architecture can mean a lot of separate data stores, databases need to be cheap but extremely performant and reliable. Azure SQL DB -- with its $5/month starting price, active geo-replication, point-in-time restore, elastic scaling, and much more -- is an ideal choice for this purpose.
For more information, check out the Azure SQL Database documentation.
One of the key principles of microservices is that each microservice encapsulates its own isolated data store. In reality, it can be costly to provision a separate, geo-redundant database for every microservice using a database-as-a-service offerings such as Amazon RDS. We can also do it ourselves using virtual machine or containers, but it's difficult to create something highly available.
This is where, I think, Azure SQL Database is very interesting. A single SQL Azure database starts at $5 a month, and provides a whole slew of features including active geo-replication.
In this article, I'll go over some features of SQL Azure DB and take a look at how it's the ideal relational database for microservices.
Are relational databases still relevant in a microservices world?
This is a great question, and one we should get out of the way. I think if microservices are designed properly, we end up applying Domain Driven Design and splitting our application into bounded contexts. Each bounded context contains one or more microservices. It's usually pretty natural for an aggregate in a bounded context to be represented as a document. This means a document database like Azure DocumentDB or MongoDB are good candidates for data persistence.
Check out my article on using DocumentDB with MongoDB drivers
That being said, there are still valid reasons for sticking with relational databases. And the line is blurring between relational and NoSQL databases with features such as JSON document support that are now in Postgres and SQL Server (and SQL DB).
What is Azure SQL Database?
Azure SQL DB is Microsoft's database-as-a-service offering. It's built from the ground-up to be a cloud-native database and its API is highly compatible with the SQL Server that we might install on-premises.
When we provision a database in a service like Amazon RDS, it is actually provisioning a VM with the database server binaries running on it. In contrast, when we ask Azure SQL DB to provision a database, it simply provisions a database with the desired throughput. No VMs.
In Azure SQL Database, we still have a "database server", but it's not a physical server or VM. It is a logical entity. It's there mainly to allow us to manage things like security across a group of databases.
For a while, Microsoft was pretty outspoken about SQL DB is built on Service Fabric. And that is why it is able to provision and scale databases extremely quickly.
Starts at $5/month
Yep. It's pretty incredible what they're able to offer for $5. Every feature we can get from their most expensive tier (except in-memory OLTP storage) is available in the $5/month Basic tier. So $5 gets us features like active geo-replication and point-in-time restore!
In my experience, the $5 Basic tier has plenty of throughput for a simple schema like ones we'd typically find in microservices. It is certainly enough for many pre-production environments! I have benchmarked one at about 15 queries per second for indexed lookups over millions of rows.
Scaling with virtually no downtime
Because Azure SQL DB is not bound to a virtual machine, we can scale a database up and down in minutes with almost no downtime. There's no need to know ahead of time how big of a database to provision because we can always change our minds later.
Scaling can be performed via Azure Resource Manager templates or the portal (pricing is shown in Canadian dollars)...
Active geo-replication
If you are running a database in production, chances are you'll want replication to another datacenter. With SQL DB, we get active geo-replication. This means not only is the data replicated to one or more secondaries another datacenter, we can read from these secondaries as well!
It's easy to set up geo-replication via the portal. Simply click on up to four locations to put your secondaries. Secondaries are billed as another database. So for a Basic database with one secondary, it's $10 (2x $5) a month. I think we can afford that!
Point-in-time restore
SQL Database handles backups automatically for us. We can restore our database to any point in time in the past 35 days (7 days for the $5 Basic tier).
The restore process is as simple as picking a date and time in the portal:
Automatic tuning
With Azure SQL DB, it's like having our own DBA. It can monitor our queries and suggest indexes to create or delete. It also recommends when queries can be parameterized and when it notices schema issues.
Here's an email that shows up when it has detected an improvement to recommend. Notice that it'll even offer to fix it at the click of a button:
Yep, it's recommending an index be added to a table used by Octopus Deploy.
And if clicking that button is too difficult, we can tell it to automatically apply any recommendations!
Automatic threat detection
Another cool feature of Azure SQL DB is automatic threat detection. It can detect potential security issues such as SQL-injection attacks or unauthorized logins. As with the automatic performance tuning stuff, this turns on with a few clicks as well.
Summary
Because a microservice architecture can mean a lot of separate data stores, databases need to be cheap but extremely performant and reliable. Azure SQL DB -- with its $5/month starting price, active geo-replication, point-in-time restore, elastic scaling, and much more -- is an ideal choice for this purpose.
For more information, check out the Azure SQL Database documentation.