July 19, 2006 at 12:11 am
Hello All,
We're currently in the process of redesigning a platform which is proving to become very restrictive in terms of expansion. I would like to get people opinions on the following and/or suggestions for different work-arounds, any other comments are most welcome.
Our current platform is based on a service being separated into a separate database for our business model. This unfortunately does not appear to be enough. What we are currently tabling is the following:
Each user is assigned a 'container' database. There would be serveral databases of this nature which contain identical tables to one another. Whenever this user requires an action to be done, the request is executed from a 'master' database which executes a local sp in the 'container' database to perform the action. The results of this action is then sent back to the 'master' and subsequentally to the user.
This style allows us to easly expand the database hardware and space as the user volume increases. However, in this design I have the following reservations:
(1) Consistency, making sure that if data is updated in a 'container' it is accurate. This should be ok however using essentially nested procedures and distributed procedures.
(2) Interaction. If one user needs to interact with another user the process would require passing back and forth between 'container' > 'master' > 'other container' and back again, again, rasing the issue of consistency and also performance.
(3) Upgradability, if identical coding is in each container database, then should the sp's need to be updated in each one, this would add some difficulty in management.
(4) Presently, we do not have the option to upgrade to SQL2005, but ideally we would like to design in the best possible way to make best use of 2005 when upgrades are performed.
I think these form the main points for the setup and again would welcome any further comments.
Thanks
Neil.
July 19, 2006 at 5:54 am
I find that usually such physical breakout into "separate but equal" databases are more difficult than any benefits it provides. You can provide the best by having one database (or designing separate databases as needed for the right reasons) and properly identify the user's records within the primary key. Let your database design to accomodate proper functionality of the applications and system, and let the physical structure of the database be a concern for the db/server administration.
Doing cross-database queries are always going to be slower than queries contained within one database.
Having all the queries and data in same database will allow SQL Server to tune itself to properly optimize queries based on all users. By having the data in separate databases, SQL Server will have to gather statistics and tune queries separately for each database, and consequently separate for each user.
If space constraints or performance tuning requires it, you can physically partition the tables into separate files/drives. In this way, your applications or queries do not need to know the physical location of files, and all queries continue to work the same.
Hope this helps
Mark
July 21, 2006 at 1:38 pm
keeping all the data in sync is also a huge amount of overhead.
Without knowing exactly what your program does and what expantion limitations you have hit on now its hard to make a solid recommendation.
I have found that 90% of all scalling issues are poor schema design, index placement, and bad code that is rarely resolved by having smaller duplicates of the same all tied together via views or linked servers.
Wes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply