|
I was wondering if anyone could comment on running multiple databases on one server. We use SQL Anywhere 12 and above and up until now we've pretty much dedicated a server to an entire DB instance (i.e one dbsrv executable running). Our database file size is about 60GB and the entire system is very responsive. Our client is going to be taking on new work that could potentially double the size of the database file and I was wondering if it would make sense to just start up another instance of the database vs plowing all that added data into the existing DB. So instead of 1 120GB database, we have 2 60GB databases running on different ports. The only reason I would do this is due the fact that 120GB DB is very large and not very portable for backups etc. Does it make sense to use split it up into 2 separate DBs or is it better to just keep it in one and let it grow? |
|
There is no "best practice", just a lot of pros and cons... If you ever want to run queries that select rows from both sets of data, then think twice before storing them in different physical databases. Cross-database queries are possible, but not nearly as convenient or efficient. The size of backup files is sometimes used as an argument to keep the size of each physical database file to a minumum... but disk performance and capacity continues to grow, making backup size less important over time. There is probably no better way to isolate different sets of data from each other, for security, safety and performance and tuning reasons, then storing them in separate physical databases. Two separate physical database files immediately doubles the administrative effort; e.g., backup, high availability setup, etc. If your future holds more sets of data (third, fourth, tenth, etc) then maybe now's the time to get some practice with two physical database files... and it's certainly easier to split the data before the second set arrives, than split one physical database in two (or three, or ten) later on. You may or may not be venturing into "multi-tenant database server" territory, discussed by Chris Kleisath in this series of blog posts. Thanks for the very insightful response Breck. We already do multi-tenant type setups so this would would almost be a multi multi setup. Kind of like a developer that own multiple apartment complexes with hundreds of people in each one. The points you make are all very valid and exactly what we are struggling with. Thanks!!!!
(31 Oct '17, 11:46)
Codecranker
2
Splitting data into two databases is usually way easier than merging two databases into one (think of primary key issues, data consolidation...), so I would not split "too early".
(31 Oct '17, 14:52)
Volker Barth
Replies hidden
1
I'd apply 200 votes to this comment if I could! We had a customer who against very strong advice split their database three ways. It took minutes to split and weeks if not months of work to put it back together some years later. (They had the very good grace to start off the request for the putting back together with "We know you told us not to do it BUT ....) If you might ever want to put them together in the future, the design needs to cope with that possibility from day 1. An advantage of separate services running separate databases is that you can allocate resources (esp RAM) to each database. If running on the same db service they can compete and you have no control.
(31 Oct '17, 17:20)
Justin Willey
|
Why would you run those databases on different ports? Do you also want to use two database server instances? If so, be aware that they compete for resources like cache...