Professional Web Applications Themes

Multiple Instances of SQL Server 2000 - Microsoft SQL / MS SQL Server

We have just created 6 new instances of SQL Server 2000, each one to handle specific functions. The problem that I have got at the moment is this: We run a database which handles approximately 5000 records (as well as umpteen related-records) each day. In order to stop that database from becoming too big, we've also created a historic database, which the "live" system dumps into each day, just after midnight. That "dump" is handled via a stored procedure which is called by the live system. The historic system has been placed into one of the new instances, which isn't ...

  1. #1

    Default Multiple Instances of SQL Server 2000

    We have just created 6 new instances of SQL Server 2000, each one to handle
    specific functions.

    The problem that I have got at the moment is this:

    We run a database which handles approximately 5000 records (as well as
    umpteen related-records) each day. In order to stop that database from
    becoming too big, we've also created a historic database, which the "live"
    system dumps into each day, just after midnight. That "dump" is handled via
    a stored procedure which is called by the live system.

    The historic system has been placed into one of the new instances, which
    isn't a problem. What is a problem is the fact that now, I can't seem to
    access the historic database from SQL Query yser on the default
    instance, which presumably means that when the stored procedure is executed,
    it'll fail because it can't see the historic database.

    Is there something I can set up which reduces the amount of recoding
    required? I really hope so.

    TIA


    Ian Henderson
    Database Developer
    The Essentiagroup (ihendersonessentiagroup.com)



    Ian Henderson Guest

  2. #2

    Default Re: Multiple Instances of SQL Server 2000

    Two thoughts...

    1. I'm not sure what is actually happening...
    <<
    I can't seem to
    > access the historic database from SQL Query yser on the default
    > instance,
    >>
    all of the instances are on the same physical machine, right? What do you
    mean when you say that you can't access the historic database from the
    default instance? You don't run QA from an instance... you run it from a
    physical Windows server (or PC...).

    I'm not 100% sure what the problem is from that message...

    2.

    In general...

    if you're talking about DB's that have just a few thousand records... you
    absolutely don't need to worry about archiving your data for performance
    purposes. Just creates a lot of admin/dev overhead you don't need to worry
    about.

    Also.. in general... you'll probably get much better performance with one
    instance that has multiple DB's rather than 6 instances. That will waste a
    lot of memory and disk space....



    --

    Brian Moran
    Principal Mentor
    Solid Quality Learning
    SQL Server MVP
    [url]http://www.solidqualitylearning.com[/url]


    "Ian Henderson" <ihendersonessentiagroup.com> wrote in message
    news:bejoqa$8dc$1$8300dec7news.demon.co.uk...
    > We have just created 6 new instances of SQL Server 2000, each one to
    handle
    > specific functions.
    >
    > The problem that I have got at the moment is this:
    >
    > We run a database which handles approximately 5000 records (as well as
    > umpteen related-records) each day. In order to stop that database from
    > becoming too big, we've also created a historic database, which the "live"
    > system dumps into each day, just after midnight. That "dump" is handled
    via
    > a stored procedure which is called by the live system.
    >
    > The historic system has been placed into one of the new instances, which
    > isn't a problem. What is a problem is the fact that now, I can't seem to
    > access the historic database from SQL Query yser on the default
    > instance, which presumably means that when the stored procedure is
    executed,
    > it'll fail because it can't see the historic database.
    >
    > Is there something I can set up which reduces the amount of recoding
    > required? I really hope so.
    >
    > TIA
    >
    >
    > Ian Henderson
    > Database Developer
    > The Essentiagroup (ihendersonessentiagroup.com)
    >
    >
    >

    Brian Moran Guest

  3. #3

    Default Re: Multiple Instances of SQL Server 2000

    We've got about 15 databases (not including the archive copies, and training
    databases).

    The eventual intention is to have an archive database for every live system.
    At the present moment, we only have the one archive database - the reason it
    was created is because the main user table contains approximately 4.5
    MILLION records, to say nothing of all of the related records in other
    tables.

    Either way, I need to be able to create a stored procedure which will run
    from the live database, and which will transfer data from the live database
    to the archive one, which is going to be in a different instance.

    Ian



    Ian Henderson Guest

  4. #4

    Default Re: Multiple Instances of SQL Server 2000

    I think Brian's point is that running multiple instances of SQL Server on
    the same physical machine is going to be problematic. You may need to
    rethink this part of it before you proceed. The effect of this on the final
    solution will not be trivial.

    Bob Castleman
    SuccessWare Software

    "Ian Henderson" <ihendersonessentiagroup.com> wrote in message
    news:bejsue$e3u$1$8300dec7news.demon.co.uk...
    > We've got about 15 databases (not including the archive copies, and
    training
    > databases).
    >
    > The eventual intention is to have an archive database for every live
    system.
    > At the present moment, we only have the one archive database - the reason
    it
    > was created is because the main user table contains approximately 4.5
    > MILLION records, to say nothing of all of the related records in other
    > tables.
    >
    > Either way, I need to be able to create a stored procedure which will run
    > from the live database, and which will transfer data from the live
    database
    > to the archive one, which is going to be in a different instance.
    >
    > Ian
    >
    >
    >

    Bob Castleman Guest

  5. #5

    Default Re: Multiple Instances of SQL Server 2000

    Now that we've gotten around the possibility that multiple instances of SQL
    on the same machine might cause problems (thanks for that incidentally), can
    someone address the issue of how one would write a stored procedure that can
    reference a database on one instance from inside a database on another?

    TIA



    Ian Henderson

    "Ian Henderson" <ihendersonessentiagroup.com> wrote in message
    news:bejsue$e3u$1$8300dec7news.demon.co.uk...
    > We've got about 15 databases (not including the archive copies, and
    training
    > databases).
    >
    > The eventual intention is to have an archive database for every live
    system.
    > At the present moment, we only have the one archive database - the reason
    it
    > was created is because the main user table contains approximately 4.5
    > MILLION records, to say nothing of all of the related records in other
    > tables.
    >
    > Either way, I need to be able to create a stored procedure which will run
    > from the live database, and which will transfer data from the live
    database
    > to the archive one, which is going to be in a different instance.
    >
    > Ian
    >
    >
    >

    Ian Henderson Guest

Similar Threads

  1. Blazeds server push and multiple flex instances
    By Solerous in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: April 8th, 02:58 PM
  2. Different JVM Heap Size on Multiple Server Instances?
    By spikehenning in forum Coldfusion Server Administration
    Replies: 2
    Last Post: December 13th, 12:43 PM
  3. Handling multiple database server instances - failover
    By Amja in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 13th, 07:44 AM
  4. Draging multiple instances of multiple objects
    By equinox007 webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 2
    Last Post: January 23rd, 01:31 AM
  5. Replies: 0
    Last Post: September 5th, 01:50 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139