June 11, 2008 at 2:22 am
I am a dba in a hospital. The situation we have at the moment is the following: almost all sql databases are on cluster servers; in particular, we have difficulty in projecting a new structure relative to an application used by many services. The application developers suggest an architecture consisting in one database that will contain the common data and configurations, and one separate database for each department, containing the specific parameters such as types of surgery intervention, names of medicines, ...
So the application used by a department will use both its dedicated db and the general one.
I think it would be better to have one only database, in which each department could own a schema. Every department schema would contain its specific data.
The question is: in terms of performance is it more expencive to have many more accesses to the same database (the application is a web application) or to have accesses split on several databases (beside accesses on the global database)? Considering that the accesses are a lot (at the moment the departments databases are about 20, with more than one access point each). All the databases involved are on the same server.
The cluster servers have the following configuration:
O.S. Windows Server 2003 x64 Enterprise Edition(service pack 1)
Database Sql Server 2005 x64 Enterprise Edition(service pack 2)
It is important for me to have the opinion of an expert, even regarding aspects that I have not mentioned, which have to be considered in such a situation.
Thanks for your replies! 😉
June 11, 2008 at 5:17 am
This is a Master Data Management question and the answer will depend on a lot of variables. Hopefully, we can help you ask the right questions.
The use of a single central database of master data is one approach. Will all departments be able to enter data that will end up in this master database, or will it be managed by one central group? Will you need to constantly be querying the data on the department servers and join to master data - if so, will the joins be handled in the application layer?
One approach I have used successfully is to replicate master data to individual sites so querying can be done locally without having to connect to multiple servers. This has always been a big performance help and is pretty reasonable to manage.
June 11, 2008 at 6:31 am
The databases involved would be on the same server, so there's no need to replicate data or to use linked servers. Joins could be made between databases in the same instance. The matter is, is it much more expencive for an application to retrieve data from 2 different databases, or having all users connected to the same database can be worst?
I think everyone will put data both in the general db and in the specific one.
I can be more precise tomorrow.
Thanks for your answer!
June 11, 2008 at 7:01 am
It is not much more expensive to have cross-database joins. It complicates security a bit. If you are to do this, I would suggest using synonyms to facilitate portability later.
June 11, 2008 at 7:25 am
I agree with Michael.
If you have all db's on the same instance [cluster in your case] then probably no more/less performance, but you lose the Declarative referential integrity (DRI) that a single db would support, and cross-db security is more tricky [recommended to NOT do db-chaining].
However putting all eggs in same basket implies more difficult to offline one department independently, although you could and should define different FileGroups to maximise partial backup/restores etc if you go for a single-db approach.
One could also consider multiple instances, virtualisation etc especially if different AD/OU's involved (for admin segmentation), but I would favour your existing cluster environment. You should be able to scale up+out with that to accommodate any future growth. I would strongly suggest you go for Active-Active cluster so that all servers work for their electrons, and that might suggest a split approach (eg dept1-3 on A, dept 4-6 on B). In case of failure dept1-3 get spun-up on B to join 4-6 and so on with various failbacks. Practice!
HTH
Dick
June 12, 2008 at 5:59 am
Thanks a lot, Michael and Dik.
in which case and why would I lose DRI? in the split case? why? The data managed in global and specialistic database would be different, so you mean there obviously could not be referencies between related data that are fisically separate? And this in unopportune...?
cross-db is disabled and won't be enabled, I agree with you, thanks for suggestion. All databases involved will have a common sqluser, which will be dbowner of each. This should avoid from db joins problems, correct?
Going for a single-db approach, the usage of schemas doesn't also allow a separate management such as e.g. oracle permits? Using different FileGroups helps only in event of partial restoring or even in case of single department updates?
We have 2 servers in cluster Active-Active. The databases would stay on the same server, and in case of failure moved to the other one, on which already is another instance with other databases.
Any other suggestion will be very appreciated! :w00t:
Silvia
June 12, 2008 at 12:38 pm
You've got 2005 Enterprise Edition, meaning you have the online restore, index rebuild features. Therefore, if you put it in one database, you have the option, in some cases, of taking a department off-line while others continue to function.
From a security perspective, simple tends to be better. Therefore, try to avoid the cross database situation. Security is harder, and, as mentioned, integrity is harder. Referential integrity has to be maintained via triggers, and if someone were to miss a trigger during an update of the application... you get the idea. Referential integrity tends to be more visible during development efforts.
As to load, you are basically in the same situation either way you go because you can use filegroups and files on different LUNs to get the same effect as multiple databases.
K. Brian Kelley
@kbriankelley
June 13, 2008 at 1:36 am
Hi Brian,
you say in some cases I have the possibility to take a department off-line while others continue to function. This is the matter! We were not sure it could be possible, and since it is quite frequent to make updates only to a department, we would like to avoid stopping all.
How does it work? Using schemas?
June 16, 2008 at 5:39 am
It is very important for me to know exactly if there would be the possibility to make updates to one department without stopping the others, and how it could be made.
I wait for answers... many thanks.
June 16, 2008 at 6:54 am
I would like to believe that most of the SQL wisdom is within Books OnLine (BOL) rather than just in the heads of the battle-scarred soldiers like Brian [the Duck] or me !
BOL is also online and searchable from MS website, so here is URL that you might like
http://msdn.microsoft.com/en-us/library/ms175199.aspx
for "Implementing Restore Scenarios for SQL Server Databases", and see its children links too
HTH
Dick
June 17, 2008 at 6:40 am
Some solutions dont'stand in any book, but are also known by experienced people, such as Brian and you probably. So thanks to the ones that share their wisdom!
The suggestions I have received are precious, even if experts may find them obvious! So for now thanks.
The thing I ment to do is to use schemas to keep all departments in the same database, with the possibility (but is there really this possibility) to work on each separately. Are sql server schemas reliable? Or are they getting performed in next versions? Are they usable such as oracle schemas?
Many thanks...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply