Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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?

asked 06 Aug '12, 13:01

grant's gravatar image

accept rate: 100%

edited 15 Mar '13, 18:29

Mark%20Culp's gravatar image

Mark Culp

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.

(06 Aug '12, 16:49) grant

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...

permanent link

answered 06 Aug '12, 16:14

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

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.

permanent link

answered 06 Aug '12, 13:46

Justin%20Willey's gravatar image

Justin Willey
accept rate: 20%

edited 07 Aug '12, 10:54

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!

permanent link

answered 07 Aug '12, 10:55

Zote's gravatar image

accept rate: 43%

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.

permanent link

answered 07 Aug '12, 10:50

Dan%20Cummins's gravatar image

Dan Cummins
accept rate: 41%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 06 Aug '12, 13:01

question was seen: 6,370 times

last updated: 15 Mar '13, 18:29