We have a monolithic application which we are now converting to microservice architecture using containers.
Our microservices are stateful (i.e they need to insert/retrieve data from db). As per microservice architecture, each microservice should have its own data (i.e database in our case).
My question is that where the database of each microservice should be deployed, whether it should be in the same host in which the microservice is deployed, in the same container in which the microservice is deployed or it should be in the separate server like azure db or something?
What would be the pros & cons of each approach and what is the best approach according to microservice best practices? *
You are correct, each microservice should use its own data store that fits best to its needs. There might be a service that want's to store its data in a blob storage, another may store its data in a table storage or DocumentDb or SQL Database.
You probably want to use Database-as-a-Service thus not hosting your own db because you don't have to worry about availabilty, scaling, backups...
Martin's answer is good, but I want to add that because you are using a containerized application you should definitely deploy the database separately from your services containers. The reason being that your services can evolve (independently), and one of the biggest benefits of stateless service containers is that, if you have a cluster of them, you can update them using rolling updates without any impact on your application availability. Updates to the stateful database services are more difficult, but also expected to be less frequent (and new technologies like cockroachdb are on the horizon). Good read.
I doesn't matter where so much except that it cannot be within the same container as your application, as stated earlier in this thread.
The important part is that only one (1) microservice has the ownership of the data. If more than one microservice needs access to the data, they must access it through a API provided by the microservice that owns that data.
You could structure it likes this:
"Sql Microservice" - handles all traffic to and from SQL Server. All microservices that needs data from Sql talks to this guys. You will have a similar microservice for TableStorage.
If "microservice A" uses a datastore other from Sql/TableStorage and that datastore is local to Microservice A, I would create 2 microservice.
Microservice A1 would be where your code runs Microservice A2 has an API that exposes the database operation to A1. When A1 needs data he talks to A2.
In addition to, that this pattern allows you to scale your data layer independent of the application nodes, you also ensure that data is only owned by one (1) microservice and that is the key.