The right back-end database

One of the apps that I am researching as a possible project, will be a database application that allows clients to access the data on a hosted platform.  We will initially be using a C# Desktop front-end, but I could really do with some additional advice as to the back-end.  We will be seeking professional guidance on this - but I would really like a little heads-up first.

The type of application means that each client could have multiple, individual databases of between 1k and 10k records (average), which although are initially based on a standard template are slightly altered for each project (there could be in excess of 100 projects (although not all will be used at the same time)).  Also, there could be up to 50 users (working on the various projects).  In addition to this, the aim will be to get multiple clients....who may need the same type of setup.

So my question is - would you initially have one instance of a server such as Microsoft SQL / PostgreSQL (other suggestions greatly appreciated) for all of the clients, or would you need to set-up a separate instance/server for each one?

As mentioned, we will be seeking help on this - I'd just also like some EE help first.

Thank you.
Andy BrownDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Hi. You could go with one instance, using Microsoft SQL Server 2012 contained databases to isolate each client from each other.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alex [***Alex140181***]Software DeveloperCommented:
each client could have multiple, individual databases of between 1k and 10k records (average), which although are initially based on a standard template are slightly altered for each project

Just an idea:

Did you check out the new Oracle 12c database?!
One of its new features is being able now to have 1 (or more) container database(s) with several "child" databases plugged into these. So you could store your template and generic stuff within that container/"host" db and override/alter the client DBs in order to fulfil the customers needs...

http://www.oracle.com/technetwork/database/multitenant/overview/index.html
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I have a similar set up.  But just one sql server with multiple databases.  If a client has multiple databases, I just name them similarly.   You could also use a master database for you that contains the detail for each database along with the scripting to create each database and name it.

I have been using a bank of shared and dedicated servers.  Going forward, I like the idea of using azzure http://www.windowsazure.com/en-us/pricing/details/sql-database/.  The reason is the biggest bottleneck is the db.  You can get by with a smaller web server or VPS/Cloud to host your files then use azzure for your db.
SD-WAN: Making It Work for You

As bandwidth requirements and Internet costs grow, businesses naturally want to manage budgets by reducing reliance on their most expensive connection types. Learn more about how to make SD-WAN work for your business in our on-demand webinar!

Daniel HelgenbergerCommented:
Hello,

depending on the objects / records you want to store - keep in mind NoSQL may be something for you.
This would be true if you have to store complex data and map them. Then, a one instance MongoDB scales up really well to your needs.

Later on, you have the option to migrate to AWS DynamoDB if you need superfast and reliable access.

Though it first it might be harder to implement because for many developers it is still uncommon to use NoSQL.

Have a look at this well written post for SQL vs. NoSQL.
ZberteocCommented:
How about MySQL? It is free and performs great.

I am confused by your statement here:

"The type of application means that each client could have multiple, individual databases of between 1k and 10k records (average)..."

I a database doesn't have "records", a table has. And a database can have multiple tables.

Anyway, any of the recommended platforms would work so you should look to free platforms.
Andy BrownDeveloperAuthor Commented:
MySQL is free up to a certain level - And yes you are correct a TABLE contains the records (you win the prize).
ZberteocCommented:
MySQL is free for your use, like development, but you will pay a extremely low fee if you use it in a production environment for the purpose to make money.
Daniel HelgenbergerCommented:
Is this post still open? There is no point in arguing about fees here. I could point out you can use the open source implementation of MySQL, called mariaDB with no costs at all if you want to. Most people and companies pay gladly the fees because of the support they have; otherwise MS Sql server would be a dead product.
Andy BrownDeveloperAuthor Commented:
All great advice - very much appreciated.

Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.