Multiple databases on 1 machine

[Former Member]

I have 5 independent SQL anywhere databases currently running on 3 servers. I want to consolidate them to one server. My question is should I run them under a single network service, or should I start them as different services on different ports.

The main DB is over 30 GB and is the largest and busiest of the 5. The rest are 250 MB and smaller. The server should be capable. It has 24 cores and 64 GB of RAM.

Does anyone here have experience with running multiple databases on a single server? Any suggestions?

[Former Member]

Thanks guys. I've installed 12.0.1 in my test environment. The 30GB DB is currently running dbsrv9 and averages 20-30 active users. The others are running dbsrv8 and average 1-3 active users. I've already migrated the main DB from 9 to 12 in our test environment. Application testing is underway, and things are looking great. I still need to migrate the other DBs from 8 to 12 and test the applications.

On the 30GB database, do you think I would benefit from increasing the page size from 4096 to 8192? I read through the SA12's performance improvements tips and thought it might be worth addressing.

BTW, Gotta love the smaller indexes after moving from 9 to 12. The DB file shrunk from 32GB to 22GB.

[Breck_Carter]

Let's assume you are using version 12.0.1 dbsrv12.exe...

From a performance point of view, with 24 cores and enough RAM to hold two copies of all the data in all your databases, the answer is "it doesn't really matter"... use one copy of dbsrv12.exe, or two copies, or five copies, you'll be hard pressed to tell the difference unless you really mess up the dbsrv12 option settings (where, in most situations, "messing up the option settings" means "picking something other than the defaults" 🙂

So.... if it's easiest for you to copy the separate setups from the three existing computers to the one new computer, with minimal changes, then why not start with that? If it works, you're done... and in the unlikely event you experience performance problems, have a look at the big database through Foxhound's eyes to see what's going on.

All glibness aside, there's always a lot that can go wrong, and a lot of it has nothing to do with one service or three or five; for example, if you're running dbsrv5.exe then life might get interesting fast 🙂

...but until you tell us more about your versions and your current setup and your number of active users and so on, the my guesswork will get worse than it already is...

[Former Member]

In my opinion you shuld run a server per database.

Here my numbers:

• 14 db/dbsrv12 instances
• Biggest: 131gb 100-200 active connections (web app)
• Total size: 175gb (without log)
• 8 cores
• 48gb ram

And everything works really fine!

[justin_willey]

There are pros and cons

• Pros - simple to manage, cache shared across multiple databases
• Cons - server asserts - all databases stop, if you want to restart a server for some reason, you have to stop all databases, you have to have the same server settings for all the databases

Extra Con - if you are using the in-built http server, all the databases on a single engine instance have to use the same http port. I'm sure others will have their own views, but in the situation you describe I might be tempted to have one service for the 30GB database and another for the other four.

[Dan_Cummins]

My experience was with multiple databases, that were hosting different applications, running under one server. Each of the databases hosted a different web application (SQL stored procedures served up responses for the browser). When I wanted to upgrade the servers and applications to v12, I had to plan to upgrade and test the applications together, so I could upgrade the entire system at once.

I found that this situation was not convenient for me; I wanted to be able to upgrade the applications independently. I ended up running the applications under different servers, so I could have more control over their upgrade path in the future. For example, I can move one to 12.0.1 GA, then when that is done move the next one, which may move to 12.0.1 EBF1.