As the question says, I want to know if is good or bad idea to use a remote database, and what pros and cons does this practice have.
It's not necessarily a bad idea, assuming DB server/node/gear/droplet/etc is in the same "cloud" and stays on internal bogon routes. A couple cons that could come into play though, depending on the remote-ness are:
- Speed, as in round trip time to contact DB and get a reply back to the app.
- Caching, as in how and where will the cache be maintained
- Complexity, as in you are maintaining another OS/container/virt
- Reliability, as in there is more that could go wrong between here and there
- Security, as in there is another point of MITM attack if it's over the public wire and/or not encrypted
- PCI compliance, as in they do not like to see DB ports open to any IP, and may fail you [constantly] for it
And some pros are:
- Stateful, as in a DB server has only 1 job and normally does it well
- Stable, as in pegged DB server won't affect availability of your web server
- Diverse, as in you can run a different spec such as higher memory instead of disk space
- Powerful, you can tune DB instance to allow full resources, use larger mem caches, and allow more CPU cycles.
The answer depends on the environment you are running. If only a single machine will be accessing the database, in other words you only have one web server then ideally you would run the database on that same server and use a named pipe instead of a socket connection to access the database from your website, this eliminates the network latency of your website querying the database and getting a response back which can at times add a few hundred milliseconds to your page generation time. If you have more than one server needing access to the database that is when the database should be located on its own server as you should not have web servers needing to connect into another web server on the same network to access resources, if a resource such as a database needs to be shared then it should be on a different server. In this instance you would need to use a socket connection rather than a named pipe but in turn you would be able to secure the database server using a network level firewall and configure the database server to only accept connections from local IP's.