Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Unanswered: replication question

    I am a replication newbie so bear with me .

    1. I want to add another table to an existing replication . Table has been created on Pub and Sub . How do I add it to the publication of 10 existing published table ?
    - Do I disable the Distribution agent for those 10 tables , add this 11th table and re enable the agent ?

    Please help

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My solution has always been to just create a new publication until I can declare down time to consolidate the publications. That way I don't need to declare downtime just to add a table.

    When you are going to declare downtime anyway:

    1) Stop all end user access!
    2) Drop the old publications
    3) Create a new publication that combines the old publications
    4) Allow user access again

    When you create the new publication, you can do it without forcing the initial snapshot. On small databases (under 10 Gb) it doesn't make much difference, but on larger databases that snapshot can take quite a while.

    -PatP

  3. #3
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    So

    So Only way to do it is Either create a new publication or RECreate the whole publication (which is impossible almost !..) The Publishing datbase is 6 Gb but i subscribing to an Archive which is 280Gb . Informastion is very Critical and too risky to drop existing Pub and recreate it.

  4. #4
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    We have a very similar environment as Pat has described.

    We don't do snapshot either. We announce the downtime first, pause the publisher server on time, make sure no transactions can occur on all subscribers. Then drop subscribers, drop the article(s) that need schema change, or simply add a new article, and lastly, push to all subscribers, continue servers.

    Your scenario is kind of straight forward. Use sp_add_article to add the new table in the publication. No need to drop the whole pub. If you 're not sure about all those parameters, script out from the publication and use the command in it as a template.

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

    Re: So

    Originally posted by aashu
    So Only way to do it is Either create a new publication or RECreate the whole publication (which is impossible almost !..) The Publishing datbase is 6 Gb but i subscribing to an Archive which is 280Gb . Informastion is very Critical and too risky to drop existing Pub and recreate it.
    You can create a new publication for the table(s) you want to add, and let them snapshot normally. This can be done at any time, although the users won't be able to use the newly added tables until they finish replicating (and you do any housekeeping like granting permissions, etc).

    Once everything is running nicely:

    1) Announce downtime a few days in advance.
    2) Lock the users out, 100% for certain.
    3) Drop the new publication (and all its subscriptions)
    4) Drop only the subscriptions to the main publication
    5) Add the tables from the new publication you just dropped to the main publication.
    6) Create new subscriptions, with the "existing data" option.
    7) Re-enable user access to the publisher and the subscribers

    This gives you a downtime window that is only minutes wide if everything goes as planned (and it almost always does).

    -PatP

  6. #6
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Thanks for reply

    Thanks for all the replies. Let me make it more simple for myself.

    Table is already on Prod Server and in use . Has got about 280,000 records in 2 days. Table exists in Subscriber server(No data)

    Can I just create a separate Publication and new scbscription ? Without affecting users

    Thanks

  7. #7
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    the answer is NO. You can create publication for this table at any time without affecting users. However, if you snapshot during subsribing, the table will be exclusively locked until all records have been bcped to the subsriber, and the log reader starts to monitor updates, deletes and inserts. How many rows are there in the table?

    One workaround we often perform in this case, is to restore the publisher db on the subscriber, and apply all following transaction dumps all the way up to the downtime, or to a short periond we can ensure there is no transactions. Then backup the publisher db and restore the tlog backup to the subscriber in recovery mode. At this moment, push to the subsriber using no snapshot option. This could finish in a few minutes or even sooner. If the publisher table is continuously modified, there is no easy way to get it subsribed without affecting users.

  8. #8
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Well

    That is the catch here .. Table grows crazy (suppporting Online users on web) it grows like a Million rows a week and is never "Not in Use"

    Is there any option where I can specify do not lock table while doing snapshot ?

    Other options are really out of question

    Thanks

  9. #9
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    There isn't such an option. If there is, you'll get in big trouble. You will have replication errors complaining about PK violations on duplicate rows, non-existing rows, and so on. Is the web server accessed 24x7?

  10. #10
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Yes it is

    Yes it is . While the databse doing a snapshot for the first time , I assume it blocks the table to maintain data integrity till subscriber is up to date . Any Idea , how long it takes to snapshot about 600000 rows ?

    Row size is not too big . Its about 100 bytes.. Also is it necessary to use those stored procs sp_MInsTbaname, sp_InsDel.. sp_Upd...

    I just unchecked them while creating the Publication.

  11. #11
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    How long it takes to get 600k rows really depends on a lot of factors, the server processors, memory, network speed, etc. My benchmark in a 8xPIII 550 Mhz cpu, 4 GB RAM, 100 mbps LAN, 600,000 rows with 100 bytes in size should finish in 5 minutes. But the result could be drastically different.

    You do need the sp_MSins, del, upd sprocs to do the actual work. snapshot agent will create them if you check the option. you can leave them unchecked if you prefer creating them manually (I do this most of the time 'cause snapshot is not an option).

  12. #12
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Thanks

    Will try it tonight probably and will update you how it went.

    Thanks for Help to you and all

  13. #13
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Thanks

    Thanks it worked out all well . Table has grown 1.3 million rows in 2 days

Posting Permissions

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