December 2, 2002 at 11:57 pm
I have a very unique problem. Our company provides services as Application Service provider (ASP) on our own product. Application uses SQL Server 2000 as DB engine. Our application stores data of all our client companies in single database say ABC_DB. Application access SQL Server thru ODBC (by using DSN) say ABC_DSN for our application.
As a normal practice we take schedule backup of ABC_DB. So it backs up data of all our clients. But issue arises when a client makes such a mistake that we need to restore only a particular client's data, we cannot do that. If we restore ABC_DB from the latest backup it restores other clients data and other clients work duing this period will lost.
To resolve this issue we have following 2 solutions:
1. We make our own backup machenism by which individual client will be able to backup his/her data. This machenism will pick up the data row/row and table/table keeping in mind the FK constraint sequence and store into a new DB say Cleint1_DB. A restore machenism will restore this data back to ABC_DB row/row and table/table when needed. BUT we still have many concerns how we will manage identity columns which are being used as PK's and FK's and unique identifiers in the applications??.
2. We should split ABC_DB and make as many DB's as clients we have. Say we have 2 clients then we will have 2 DB's Client1_DB and Client2_DB instead of ABC_DB. We will also create mutltiple DSN's say Client1_DSN and Client2_DSN. Application will be modified such that it will access multiple DB's thru multiple DSN's depending upon Client ID. At the end we can schedule backup of all the individual clients DB's by SQL server and can restore for each individual client. But issue with this solution is that we have to mainain as many DB as clients we have e.g if we have 100 clients on one Server we need to have 100 DB's on same server with 100 DSN's. And if new client is added we will have to create new DB every time.
Does anyone has experince of this type of situation?? and guide which option would be better in the long run?? OR is there any other solution available??
This would be a great help for me.
December 3, 2002 at 4:36 am
I would go with option 2. Overall there is les chance that other transactions will interfere with other customers and you can spread the data across more drives as you need much easier. Also, are your customers aware there data is mixed with other customers, they may not be thrilled if they don't already know and eve find out.
Another point is you can see then what customer is utilizing what resources much better.
As for DSN you do not have to have more thn one. They can all attach to the same DB, you will then just need to reference the three part name to get at their data (dbname.objectowner.object). But I would suggest using ADO OLE connections instead of ODBC as they are far more flexible and you can set defaults databases based on the user login much easier.
Option one has more opportunities for failure and still having the same problem as before unless you mechanism is realtime.
December 3, 2002 at 9:47 am
Having been in a similar boat, and stuck with option 1. I'd go with option 2. More flexibility and gives you the ability to easily scale out if one client gets big or you overload the server.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 4, 2002 at 3:36 am
Antares/Steve: Thanks
Options 2 is 100% secure as well.
Antares, can you please further explain how can we access multiple DB's using single DSN?? It will make our lives much easier with option 2.
December 4, 2002 at 5:26 am
The way we do it is setup a dummy DB that has nothing but a control table to know what the logged in user has access to. All users when they login are given access then set to this database thru our DSN. The app thou when built makes reference to objects in the users database via the three part name.
Like so database.dbo.object
This allows us to set a single connection value and properly use pooling on our web server which runs multiple apps. With this we have a lot of control and significant enough security. If you app is the same interface for all users then you might add a dbname column to your table or query the default db value from the user account and set during login a session variable to alter text values as needed to the proper three part name.
December 4, 2002 at 7:17 am
I vote for #2 also. We have about 250 dbs, works well, though you trade off spending more time managing schema changes, etc, than you do on one db. Not srue why you need DSN at all, just as easy to build the concect string in the app dynamcially. Because our ap secures the data via the interface, we use the same login in all 250 dbs.
Andy
December 7, 2002 at 7:02 pm
Sorry, braindead some days. We have moved on to the same practice under ADO without a DSN. But we still only attach to one DB and then allow the app to properly reference. The more generic the connection string itself the fewer overall individual connections may be open at any given time (properly pooled connections will remain open for a period of time after user has logged off). Then as connections pool they allow faster logon access times. However, we also have servers with multiple apps where each app makes it's own connection to it's related database and they supply an app name in the connection. The big difference is 1 will limit somewhat connections which mattered on our 7 servers at one point (company now has unlimited CALS due to size), the other allows for easier identification of trouble spots in code. I guess thinking about it the later works great for me too and I do prefer being able to id my trouble spots. But either way segregate client data for security and recoverability without interfering wioth each other.
December 13, 2002 at 7:24 am
By going with option 2, we can have following additional benefits:
a. Each customer’s data is being physically stored on separate DB and privacy and secrecy of customer’s data can be maintained as per HIPPA’s requirement from the backend along with front end. Even if an offline client has 2 or more location they can protect their data from each other if required.
b. Better performance: if there are 3 clients accessing same DB (current approach) and 1 clients stores data say 80% , client 2 and 3 10% each, then client 2& 3 will hurt with slower DB response on the cost of client 1. Where as with new approach each individual client will access his own db/data and will not be effected with any others excess workload.
c. Any client’s application can be easily setup offline in their own server any time as clients DB would be ready to move.
d. Scalability: Splitting DB into multiple DB’s means we would be able to spread our application/DB on multiple servers by deploying each clients DB on separate DB server, if needed on the long run.
December 19, 2002 at 8:36 am
I vote for option 2. Beside other person's comment, if your application gets more clients as well as process than one DB box can handle, you may have more than one DB box to house these client DBs and write a application to point your client to the correct DB box.
jiema
Jie Ma
December 27, 2002 at 10:25 am
Agree with all of you. option 2 is the most practical way to do it. You can set DTS jobs to move the data to those individula dbs, which will update the data on a daily basis and then run backups as you need. Differential, complete, logs, etc. Then you can manipulate the data as needed.
Good luck!
December 28, 2002 at 3:18 am
CafeMar1.. Your idea could be another choice as to Keep single online DB for all clients and indvidual offline DB's for each client, DTS package would move the data to offline individual clients DBs on specified time interval and we can take backup of these DB.
BUT WHAT YOU SUGGEST IN CASE OF RESTORE INDIVIDUAL CLIENT DATA BACK TO ONLINE DB??
January 2, 2003 at 8:17 am
This brings up another question (mainly to Andy). If you have a single front end app and a set of different tables for user's based on their location, what's the simplest way to filter their data so they only get the company or client they have permission to?
In other words,an HR app has employees from PA, MD and VA. An HR rep from PA opens the app and only needs to see PA. Linked tables are being used now from SQL but using a DSN would be better? I guess if it's a multi-user app and the filter is set when the form opens, that might work too?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply