Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: Two Design Decisions

    So the company that recently hired me (3 months ago) has what I consider clutter and bad architecture. I have tried arguing to (no avail) about the bad architecture but was told, this way behaves more like the cloud and we are not going to change it. I need more ammunition, any advice on how to pitch it?

    1) The bad architecture IMO is putting SQL and Web on the same machine, we have about 75 systems set up this way all with replication setup. They seem to be working fine, but I have a few concerns. I am worried about security. I think we could achieve the same or better performance with maybe 10 dedicated SQL and 60 dedicated web. Half of the servers are for tracking web activity (ads), the other half are ad servers that tell sites which ads to serve up. They claim they need it setup this way since the DB servers cannot handle the load from the web servers, I contend there is a bad design at play and that is why the DB servers cannot keep up.

    2) Regarding the clutter, we have about 29 databases on each of these servers. I'd like to simplify things and see at most 3 or 4. Do cross database joins perform poorly? Because the sql code is riddled with cross db joins.

    A more simpler question to 2 is what is your design strategy when creating a new DB, what criteria is met that warrants creating a new db?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Hey Gagnon,
    1. The number of servers does sound excessive and was probably done to keep up with demand on a poorly designed application and database.
    2. My feelings on a database are per application, maybe, a subset of the app, but you should keep the objects that being accessed by an app together when possible.
    Dave

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SQL and IIS were always considered as bad neighbors. So there I am with you, - yank SQL outathere! But it's hard to judge someone's application architecture by shere number of servers that got deployed. I hope you had a more substantiated argument than that. If I were to defend this, I'd say "load-balancing". But then I would have hard time finding a DBA who will be willing to do nothing but troubleshoot my transactional/merge replication (that's what they are running, right?), just because I thought it would be cool to have a SQL for each IIS

    When it comes to the number of databases, - again, the number by itself tells me nothing. 29 is 29, so? Why would you like to see 3 or 4 instead? Is it justified by the details that we don't know yet, and you're gonna tell us soon what they are, so that we can say "Oh yeah, you got it!"?

    It would be nice to have a summary of what each group of databases is used for.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    RE: (1)
    Well my argument was that there was a bad design at the foundation. Given enough time I will investigate. Their solution was to just throw more machines at the problem and given that the SQL DB Servers could not keep up with the App servers there were about an equal number of both - which is fine given no time to investigate. However, rather than figure out what was wrong I think they might have made matters worse by deciding to put both on the same machine. Without a performance baseline of the before and after it is hard to tell if going from 11 web and 8 db (19 total) to 69 consolidated (web+db) over the span of 3 months was justified.

    RE: (2)
    I agree a summary would be great, to try to quantify these db's here's a summary of how many tables are on each db:
    Out of 45 databases on one of the servers for example:
    6 have 0 tables
    11 have 1-5
    6 have 6-10
    17 have 11-50
    5 have > 50 tables

    Sizes:
    30 DB's are < 10 MB
    7 DB's are < 100 MB
    2 DB's are < 500 MB
    1 DB is betw 500 MB and 1GB
    4 DB's are betwee 1GB and 10 GB
    1 DB is > 10 GB

    There is no special security setup for these DB's if I had to guess they were created to organize data, for example there is a DB just for Messaging, it has 5 tables in it - not even sure what kind of messaging it is used for. I agree I need an audit of these DB's so we know how they are used.

    Managing replication for all of these DB's adds unnecessary overhead. I am positive we have referential integrity issues since (as far as I know) you cannot have an FK across DB's. As we bring on new hires it is overwhelming to deal with so many DB's when maybe 4 or 5 could have sufficed. Would there be more tables in these databases? Sure, but creating a new DB just because we have more than 10 tables (we have 23 databases with fewer than 10 tables!) is a little ridiculous. Managing backups (which we currently do not do), Managing diskspace...I could go on as to the cons.

    My main post asked, when do you create a new DB?, what are good reasons to?, then I can create a matrix to see if any of these criteria are met and hopefully consolidate some of the non-essential entities into the collective.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, targeting the main question, - when to create a new db... From application architecture perspective, only when the data that I intend to store in that new database has ***NOTHING*** to do with the data that my app is already using.

    From infrastructure stand point, - to establish a physical separation from existing data store, including memory allocation (CPU's may still be shared if it's in the same instance, or on a different instance of the same box).

    The main thing, I guess, is that I would have hard time convincing myself to create another database within the same instance to begin with. The reason is, because of the reason to create a new database - which means, that I identified a different pattern of usage of the data, which warrants a different database to be created. If this is the case, then I usually set up a box, if one doesn't exist, for such database. I am always more inclined to have a logical separation by using a different schema name for the new set of objects.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    nice response, thanks dude.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •