May 28, 2015 at 2:14 am
Hi,
1. Are you hosting the database? If no, then customisable databases, but do it this way default schema for base application client schema for client based customisations. . If you are hosting then the same schema setup applies.
2. DR - If you are hosting there are a multitude (2) of DR options available to you:
mirroring
log shipping
3. Recovering to point in time for a single client on a shared database-
Daily database backups and hourly transaction log backups
Restore the database to a new database and location.
Backup production DB.
Copy data from new DB to Prod for client only.
Hope this helps.
Cheers
Jamie
May 28, 2015 at 7:54 am
Jamie you raise some interesting questions. At this time I don't have the answers because I was pulled onto another project for the next few months. I will consider what you posted as soon as I get back to the project.
Thanks
May 28, 2015 at 9:34 am
I just want to throw this out there regarding the data and your application, and realize that this is my own opinion, and the following quote from an earlier post:
eichnerm (12/17/2014)
Jason, thanks for the input. You bring up some good points. The ownership of the data is something I am going to have to consider when I make my decision. With that being said, even if I use one database I can provide each customer his/her own data.
The application may be yours, but the data is owned by the customer. You are (will be) the steward of this data and responsible for keeping it safe and secure.
At some point in the future a customer may decide that your application no longer meets their business needs and moves to another provider. They need to be able to move that data without having to purchase it from you. Yes, you could charge a fee for the effort needed to provide the customer their data, but you shouldn't be charging them for their data.
May 28, 2015 at 10:37 am
Lynn, all good points. My brother in-law has an app that is actually going through this same process. He wants the customers to own the data, but some want him for some reason to own it.
As I said I won't be getting back to my project for some months now. I have seen both methodologies - One database with a customer number to separate customer and and version where each customer has his/her own database. I am not sure for my project which is preferable. All the issues that were raised will hopefully help me make a decision.
The one thing I have gleaned from my posting is that there is no one clear cut answer.
Thanks
May 29, 2015 at 5:18 am
I would go for a separate database for each customer every time for security reasons.
Most customers do not want anyone else (and especially competitors) seeing their data and even the thought that it shares a database could well be enough to put them off.
With separate databases you can ensure they only see their own data very easily.
May 29, 2015 at 5:52 am
Hello,
Apart from all the technical issues, you should also consider legal requirements. Student data has privacy protections based on federal laws commonly referred to as "FERPA" (Google it).
Commingling data about students from different customers might not be palatable to your customers. Segregating each customer's data in a separate database, even on the same server, might be more acceptable from a student privacy standpoint.
I am not a lawyer. Please consult a real lawyer for help with this issue.
Good luck
May 29, 2015 at 2:10 pm
mfranksubs (5/29/2015)
Apart from all the technical issues, you should also consider legal requirements. Student data has privacy protections based on federal laws commonly referred to as "FERPA" (Google it).Commingling data about students from different customers might not be palatable to your customers. Segregating each customer's data in a separate database, even on the same server, might be more acceptable from a student privacy standpoint.
I am not a lawyer. Please consult a real lawyer for help with this issue.[/i]
Good luck
You beat me to it! You can think of FERPA as PCI or HIPAA only applied to students. Obviously it's radically different, but in broad strokes, it serves as an example. I'm currently developing a student information system; we're all in one state, so I'm using row-level security so District A cannot see District B's students (with some controlled exceptions).
I would definitely go with the prevailing wind of an individual database for each client. It's the only way to be safe if someone screws up a WHERE condition that could accidentally expose a lot of student info. Also log and audit as much as you can: if you suffer a data leak, you might be able to identify who was responsible.
No one has tried to answer how to restore Client 42 when everyone is in the same DB?! Well, it wouldn't be easy. I'd restore the full appropriate back up to another DB, query out the data in to staging tables, delete the info from live, then reload it from the staging tables. Obviously RI, triggers, etc. have to be taken in to account.
Alternatively, in addition to the normal database backup, you could write a backup system where every client gets their data extracted every night via SSIS in to its own data package, which is then backed up. You'd then already have every client's individual data available at hand. Obviously this ridiculously overcomplicates things, but that's just one of my many specialities. 😀
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply