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?
 
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.
0
 
Alexander Eßer [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
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.
0
 
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.
0
 
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).
0
 
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.
0
 
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.
0
 
Andy BrownDeveloperAuthor Commented:
All great advice - very much appreciated.

Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.