Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Red face Unanswered: Transactional Replication Alternative?

    I know this is a mess, but they set up this environment before I got there. There is a very minute chance I may get another SQL server, which is why I ask question 1. But I have to move forward with question 2 until I can confirm another box is possible.

    1) Is transactional replication possible if the subscriber is in a failover
    cluster?
    1b) Am I correct in the idea that the publisher and the distributor can live
    on the same SQL Server.

    2) Transactional replication aside... do you know an alternative to it?

    Here' s my situation. I wish they would have consulted with me prior to
    setting this up but nonetheless...I have a database cluster with 2 servers
    in it. Failover setup between them. The staging database and production
    database live on the same SQL server. When data is written to, or updated on the staging database, I need to replicate it to the production database. I obviously can't setup transactional replication when I virtually only have one server to work with.

    >> Individual triggers are out because there are 38 tables and I would need a trigger for each action. SO that's approx 114 triggers. Not viable or safe and just plain wrong.

    >> I've considered kicking off a DTS package that will copy the table up but then I would need 38 DTS pkgs. And I don't want to copy the whole database every time for obvious performance issues.

    >> I've considered modifying the code in their software so that it reruns
    their DB update object after I temporarily change the data source... which is a great alternative, except then I have to worry about data integrity; making sure the primary keys match between staging and production.

    Thanx for any input or any ideas beyond what I've written.
    Egotistical slander not welcome.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Why not consider using Log shipping to provide hot standby support.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: Transactional Replication Alternative?

    Originally posted by msanchez13
    Egotistical slander not welcome.
    Aw geeze, you start by taking all of the fun out of things!

    Yes, you can replicate to and from a clustered server nicely. There are some minor technical issues that might be problems, but no big deals.

    On a more serious note, you could build snapshot replication easily. This induces the same problem of having to copy the whole tamale when you replicate, but that is a relatively low price to pay for sanity, at least in my opinion.

    I wouldn't get within a country mile of the idea of modifying their code to update two different databases. There is WAY too much chance of missing a minor point somewhere that could trash your data.

    -PatP

Posting Permissions

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