locked
NEED INPUT: Database Setup Strategy RRS feed

  • Question

  • I have a single web application that accessing data from lots of different locations.  Three of these locations are imported daily each morning and one is uploaded by users monthly.  Each of these data sets is rather large (3GB+) and contains its own unique processing logic, views, reports, etc.  What I designed is a multiple application/database instance on the same server.  So for example, server A has one installation of SQL Server 2005 with databases X, Y, and Z.  Each import and upload has a database instance because it has loads of data, stored procedures and views which make it unique.  The dynamic web interface has a database instance because it contains user information, menus, history, and anything else front-end related.  There is also a shared database instance which contains data used by all the other database, such as a list of countries and time zones.  It is easy to query other instances by simply referencing the tables X.dbo.TABLE.  Each instance run separate from each other and different queries can be running without locking a single instance.  The system seems to be designed well and functional however our DBA is now telling me that I need to merge them all into one instance and make multiple tables.  The multiple instance methods was missing a form of referential integrity due to SQL Server not allowing foreign keys across instances which would be easy to due if all tables were in the same instance.  The downside is that all the naming, massive data size, and cost if there is a database lock seems much greater on the single instance method.  I am unable to find good pros and cons towards this idea.  Most people only discuss multiple database servers and multiple SQL Server instances.  Can someone please provide some pros or cons on my method vs the single instance method?  Thanks
    Friday, September 10, 2010 1:51 PM

Answers

  • It is true, but do  you really need  to set up FKs from one database to another...Do you want to pervent the unneeded deletion? I think  your design is OK, you know , it is hard to suggest on design because only you know everything about your business, so instead of FKs you can write a trigger that checks the data.....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 12, 2010 6:49 AM
  • http://sqlserverpedia.com/wiki/Multiple_Database_Instances

    please check this

    thanks


    - MS (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Monday, September 13, 2010 10:11 AM

All replies

  • It is true, but do  you really need  to set up FKs from one database to another...Do you want to pervent the unneeded deletion? I think  your design is OK, you know , it is hard to suggest on design because only you know everything about your business, so instead of FKs you can write a trigger that checks the data.....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 12, 2010 6:49 AM
  • http://sqlserverpedia.com/wiki/Multiple_Database_Instances

    please check this

    thanks


    - MS (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Monday, September 13, 2010 10:11 AM