March 21, 2011 at 9:07 am
Everyone,
This is going to be potentially a confusing question, but it's a somewhat confusing situation. My employer is responsible for supporting an EMR system which we did not write. Instead, we resell this product, and do not have access to the code. The people directly responsible for working with clients to set up this system have asked me if we could keep in-house several copies of the "base" database, which they could go in and modify for various specialties (chiropractic, medical, podiatry, etc)
These modified base DBs would be used to setup new clients.
As I see it, I have a couple options:
1. Change the filename and DB name, having multiple attached copies. The in-office staff would then need to change the connection for the application.
2. Set up a batch file which the staff could run, choosing the specialty type they need to work on. The batch file would detach the current DB (using sp_detach_db) then attach (using create database ... for attach) the DB of the specialty they need.
In the second case, the DBs would all be stored in c:\data\{specialty}\
Now, the application in question also uses Filestream, but in doing some research, as long as I don't move the locations between attaches, this shouldn't be a problem.
I want to make this as "hands off" as possible for both the staff, and myself.
Clients with the application would NEVER go through this procedure, as they would always only have one DB.
Thanks,
Jason A.
March 21, 2011 at 2:22 pm
I would probably go with a third option:
Create a separate instance of SQL Server for each one. This would keep each database in it's own instance, with the same name in all instances. Each instance would have it's own LUN (or folder).
I would then make this multi-instance database server dedicated to only the database system and create multiple virtual servers for each application.
Then, when someone needs to work on a particular specialty - they access the application on that dedicated application server (hopefully, this is a web based application - which would make it much easier).
Downside is that you have to manage multiple instances of the application and databases and upgrades have to be managed across all versions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 21, 2011 at 3:20 pm
Thanks for the idea!
I hadn't thought about setting up multiple instances (there's a reason I consider myself a newbie DBA;-) ) but that may be more "elegant" than my hack job solutions. Considering the current server hosting this app is already a virtual machine (Hyper-V Server 2008 R2) it wouldn't be too hard to set up an application server.
Of course, your point about having to manage the multiple instances, and the multiple application servers is a point against, seeing as I am the office SQL / Hyper-V person. Further, though, I should find out from the vendor if the application and SQL have to be on the same physical server (yes, it's possible that's the only way it may work, which I agree would be crappy programming...)
Thanks again!
Jason A.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply