Where to place my database

Question

At the moment i'm busy to implement a website, database and software which will fill this database. The website is placed at an external hosting company, the software is running at out local netwerk. The website needs to read the information in the database, the local software will put its results (these data) in the database.

Question: where will i put the database? What's the best place considering security and performance. And also what are the possibilities: if i put the database together with my website at the webserver, can i access it form my local software for uploading? If i put the database local, can i access it from my website?

I'm a programmer and know about programming software and implementing databases but have little knowledge about infrastructure, so can please someone give me some advice.

Thanks in advance, Jethro

Answer 1

Both places? By setting up a unidirectional replication scheme you can guarantee you always have a local copy of the data, but won't hamper the performance of the remote site.

Replication can be managed either by your database, or by your application.

Answer 2

For stability and performance, you should deploy the database closest to the production system, which in this case is your web site. The backend software should be able to hit the hosted server over TCP assuming you have decent security and bandwidth between. This means it's preferred to have what's called point-to-point connection. The alternative is to set up a vpn over the Internet, which could be slow and unreliable.

Edit: By "production," I mean customer-facing and mission-critical, which may not be true for your case. Who are the audience of two systems? Which one is more important to be stable?

By using point-to-point or vpn, you can pretend as if you are hitting a computer over LAN. The speed however would depend on how much you spend per month.

Answer 3

You ask about stability and performance, but as always this is a question of trade offs, so you have to decide which is more important in these terms - the web site or the software.

I assume the web site is (thinking this is why you upload information into the database anyway) and from that point of view putting the database next to you web site (with the hosting company, most of which provide this service).

This would allow your web site to access the data more quickly and will therefore increase the stability of your system (less likelihood of timeouts)

The price you will pay, of course, is with the software, which now has to connect to the database remotely.

Another consideration is data volumes - if, for example, you expect the software to use the database heavily and the web site only sparingly, you might consider changing the approach.

Last - regarding accessing the database remotely - you can of course consider simply connecting to the database 'normally' but I would have probably considered wrapping it in a web service if possible, which would allow you

1. To have more flexibility with the location and implementation of the database
2. Using widely spread protocols such as WS-* you could achieve higher security without the need for infrastructure such as VPN

Again - depending on which part access the database remotely you would choose which side to expose as a service. this is of course potentially less suitable for large uploads of data.

Answer 4

Thanks all for your advice, i'll further consider what's the best solution.

Just one question: can you give me some more information (e.g. links) how to connect to a database outside the local network of the opposite, connect to a databse on a webserver from local software

Cheers, Jethro

Answer 5

This is a good question.

From a performance perspective I would optimize for reads done by the webserver. Proportionally there are going to be many more queries sent by the web server and if the database is slow, page will load slowly.

From a secuirty perspective connecting to remote databases can be hazardous. However, if you take the proper precautions, such as using an SSL key-pair for authentication. Also make sure that all user accounts on the database can only be used from IP address you trust.

There is another secuirty precaution with database placement. There are a number of attacks that hackers can use when your web server and database are on the same machine. The best example is the into outfile attack described in Hackproofing Mysql. If the database cannot be put on its own machine then a chroot or virtual machine can be used.