Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Unanswered: Create Reporting Instance from Prod

    Hi all,

    I've got two SQL Server 2000 (SP ??) instances (on two separate machines; Win Server 2003 Standard) that I've inherited. I want to use one of them as a reporting instance of production for a single ~4GB database, updated nightly.

    In other DBMS's I'd set up log shipping or a simple dump-and-load to keep the two in sync, but I'm not very familiar with SQL 2000 (I used to admin a SQL Server 7 back-in-the-day but have been on Sybase ASE, MySQL (blech) and 'Orable since).

    Any suggestions to do this easily and (fairly) painlessly?

    Would I want to set up replication between the two? If so, which flavor?
    -- To me, this seems a bit overkill. Plus I hate to muck with production unless I really need to

    Would I use DTS to do this?
    -- Seems straightforward but as I understand it, DTS under-the-covers is a bcp-type process, which can be fairly slow.

    Or a simple dump-and-load (with copy)?
    -- This seems the best option as we're already doing a nightly dump. However, the data will have to be shuffled off to the other server (or some sort of network share set up that it can access) and then a script fired off when the dump is complete. This seems the most "brittle" of the three options (if the dump hasn't finished yet, then the script copy and import will fail, etc.)

    Surely this has been done over and over again (searching the archives didn't tell me anything, but the site search tool isn't that great).

    Thanks!!
    Thanks,

    Matt

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You do have several options:
    - Backup/restore - that's the easiest to setup and the hardest to turn into a true reporting environment;
    - DTS (or BCP as you pointed out) - actually, this is the one that gives you most control, because you can load the data from prod into a staging, and then do a delta, so that you don't have to reload your reporting tables every time.

    Database copy I do not recommend, because that requires the source database to be set offline or detached before a device copy can take place.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Offlining the source DB is ok; this would take place after-hours (it's an internal use app) and so no one will be using it. I'm just looking for the simplest/easiest to mange solution.

    What do you mean with "hardest to turn into a true reporting environment"? I assume you mean that, in the future, it'll be difficult to increase the frequency of updates, which I would agree with.
    Thanks,

    Matt

Posting Permissions

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