Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: Replication Best Practice Question: Split Publications

    I am working on a replication design and getting closer to implementation. One of my major concerns is maintaining and updating stored procs and/or user defined functions.

    The current design is a single publication, including tables, procs and functions (no views yet). All told there are about 686 articles in the publication. The tables are horizontally partitioned using dynamic filters based on the hostname of the subscriber. There are around 50 subscribers. Most will have small databases (< 250 MB). A few (2-3) will have much larger databases. I am mostly concerned that whenever I have to update a proc or function, I have to re-initialize the subscriptions and that pushes a TON of data out over the network and may interrupt service at the subscriber locations.

    I see three options for the procs and functions:
    1. Include them in the publication with the data tables
    2. Place them in a separate (snapshot only) publication
    3. Exclude them entirely from replication and maintain them manually

    I am starting to lean towards option #2; but I am a bit concerned about maintaining a duplicate set of replication agents for each subscriber.

    Any thoughts and/or comments?

    Regards,

    hmscott

    Am I overlooking something? Is there an option that I have not considered?
    Have you hugged your backup today?

  2. #2
    Join Date
    Nov 2004
    Posts
    128
    I maintain them manually. All replication does is replace procs, and I can do that without involving replication.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by cfr
    I maintain them manually. All replication does is replace procs, and I can do that without involving replication.
    How many subscribers?
    Have you hugged your backup today?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I don't really feel qualified on this subject and don't have that much related experience (I use replication to get disparate data sources into one single, read only point rather than the other way round) however you aren't getting much joy so I might as well wade in as not.

    I have read a couple of articles that advocate an approach very like point 2. One, as I recall, suggested one publication per article type which does seem a bit over the top. Anway - although this would require administration of more agents it sounds preferable to administering everything manually.

    Can I find my sources? Can I buggery - you will just have to take my word for it that I have read at least two articles that suggest this very set up. I will continue hunting through my favourites.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Pootle -

    Thanks for your comments. I'm going to experiment a bit with this approach in the next fiew days.

    Is it me, or does it seem that there's a dearth of information on the net regarding SQL replication? I have found a few sites (http://www.replicationanswers.com/Default.asp, http://www.dyessconsulting.com, and a few articles in http://www.sqlservercentral.com), but it just seems that there's not a whole lot of "hard" information (ie, mostly it's a lot of generic stuff with few practical examples).

    Thanks again, I'll try to remember to post back with an update as things progress.

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is my experience too. I believe Hilary Cotter is extremely knowledgeable and also active on some forum(s) if you really get no joy here.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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