Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unhappy Unanswered: Combining transactional and snapshot replication


    I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)

    We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)

    When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.

    If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.

    Can I combine the two replication schemes to deliver updates to the same target database:
    - transactional replication delivering realtime updates to the tables with PKs during the day and
    - snapshot replication updating all the tables once per 24hrs at night?

    Or is there a better way of doing this?

    I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...


  2. #2
    Join Date
    Sep 2003
    Dallas, Texas

    Re: Combining transactional and snapshot replication

    Certainly, you can setup two publication - one for those tables with no PK and one for those tables with PK. They will have the same subscriber and destination but the one will be snapshot and one will be transactional. The other option you have is to create a surrogate key for each table without PK. I don't know how feasible it is since your database schema is controlled by the person other than you. You can also consider Log Shipping if the function of the subscriber is read-only.

  3. #3
    Join Date
    Feb 2004

    Re: Combining transactional and snapshot replication

    Thank you for your quick response. I'll set up two publications as you suggest. For some reason I was hung up on including the PK tables in both publications and I was wondering why I was getting errors... <doh!>

    Regarding log shipping, I've had a quick look around for info. It seems that it is not as easy as "standard" replication to set up and keep tabs on. Also, in my environment, although the replicated database will be read only on the target, one of the target SQL boxes will have a database which will capture input from web-connected users. Will this make a difference? Do you think that log shipping will be the better solution for me?

    I appreciate your help

Posting Permissions

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