Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Replication and Log Shipping

    I just had a general question...

    We currently have a production box that log ships several databases to multiple servers. One of these servers acts as our "reporting" server. One of the things that I'm not satisfied with is the fact that if we need to make a change to any underlying stored procedure that is used for reporting, we need to deploy that stored procedure to our production database and then wait for it to be log shipped to our reporting server.

    Because of this, we have a ton of stored procedures in production that are never actually used for production.

    I'd like to implement replication to get data from production to our reporting box. This way we can update stored procedures as necessary.

    Is anyone aware of any gotch's / caveats / issues with having replication and log shipping configured on the same "publisher"? Also, if I recall correctly, now with SQL2K5, any DDL is automatically replicated to any and all subscribers...

    Thank You!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    I know this is a weird work around, but you can always create a second db (in the same instance as the reporting db) and store all of your sp in it. All you would have to change is fully qualify your queries in your sps to include the reporting db name. Performance might be a little bit slower, but shouldn't be too bad.

    Good luck

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    I'm trying to get away from having objects scattered around in different databases. Good idea, but it doesn't address the issue if we need to make changes to objects in the Reporting database and not production.

    For example, I want to create an index(es) on a table in the reporting database and not the production database. Can't accomplish this while log shipping is in place...

    Also, really don't want to do anything that is going to impact perfomance versus improve...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Thats true. It sounds like you are looking for some sort of SSIS package which will compare and import data into the reporting db from production. This might be an option depending on DB size and business requirements (24hour data delay if you are only updating at night).

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Creating an SSIS package to compare and import data from one database to another is not an option.

    If I were to do that, OI'd essentially be writing my own replication...

    Replication is the answer - I just wanted to know if anyone has run into any issues with log shipping and replication running simultaneously on the same database
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

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