If i add NEW COLUMNS to 2-3 existing Articles\Tables using
"sp_repladdcolumn" do i have to re-initialize replication & apply
SNAPSHOT or LOG READER will apply all data in that new columns for
subscriber. i am trying to understand when i add new columns how
columns are getting replicated to SUBSCRIBER & how data will be copied
to new columns.
Any help is appreciated to make me understand & thanking experts in
advance.
Provided you're not intending to modify the PK in trans replication (in
which case it won't work anyway) there's no need to reinitialize. The
columns will be nullable initially and there will be no data in them.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||If DDL replication is turned on do you need to use sp_repladdcolumn?
On 4 Feb 2007 21:34:05 -0800, myrights99@.gmail.com wrote:
>If i add NEW COLUMNS to 2-3 existing Articles\Tables using
>"sp_repladdcolumn" do i have to re-initialize replication & apply
>SNAPSHOT or LOG READER will apply all data in that new columns for
>subscriber. i am trying to understand when i add new columns how
>columns are getting replicated to SUBSCRIBER & how data will be copied
>to new columns.
>Any help is appreciated to make me understand & thanking experts in
>advance.
|||In most cases a mini-snapshot of only the modified article will be generated
and sent to all named subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<myrights99@.gmail.com> wrote in message
news:1170653645.410658.238120@.m58g2000cwm.googlegr oups.com...
> If i add NEW COLUMNS to 2-3 existing Articles\Tables using
> "sp_repladdcolumn" do i have to re-initialize replication & apply
> SNAPSHOT or LOG READER will apply all data in that new columns for
> subscriber. i am trying to understand when i add new columns how
> columns are getting replicated to SUBSCRIBER & how data will be copied
> to new columns.
> Any help is appreciated to make me understand & thanking experts in
> advance.
>
Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
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
ThanksMy 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|||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.|||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.|||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|||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|||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.|||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|||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?|||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.|||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).|||Will try it tonight probably and will update you how it went.
Thanks for Help to you and all|||Thanks it worked out all well . Table has grown 1.3 million rows in 2 days
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
ThanksMy 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|||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.|||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.|||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|||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|||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.|||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|||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?|||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.|||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).|||Will try it tonight probably and will update you how it went.
Thanks for Help to you and all|||Thanks it worked out all well . Table has grown 1.3 million rows in 2 days
Replication Question
Dear All,
After marked re-initialize an existing publication, a new snapshot has been
generated and applied to the subsciber, after that, there is an error on
Distributor agent saying that can't apply command as duplicate key error. I
have checked those insert commands and those records are already exists on
subsciber as new snapshot has been applied.
What I can do to resolve the problem? Do Distributor agent is smart enought
to ignore those old not yet replicated transactions which is older than my
latest snapshot being applied? What is the correct action sequence for
re-initialization?
Yours sincerely,
Henry
Henry,
see if this applies:
http://support.microsoft.com/default...b;en-us;813494
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Henry,
also, have a look at the post 'Subscription Choking' from yesterday, as
there might need to be an additional step if the Hotfix is applied:
http://groups-beta.google.com/group/...8?dmode=source
Rgds,
Paul Ibison
sql
After marked re-initialize an existing publication, a new snapshot has been
generated and applied to the subsciber, after that, there is an error on
Distributor agent saying that can't apply command as duplicate key error. I
have checked those insert commands and those records are already exists on
subsciber as new snapshot has been applied.
What I can do to resolve the problem? Do Distributor agent is smart enought
to ignore those old not yet replicated transactions which is older than my
latest snapshot being applied? What is the correct action sequence for
re-initialization?
Yours sincerely,
Henry
Henry,
see if this applies:
http://support.microsoft.com/default...b;en-us;813494
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Henry,
also, have a look at the post 'Subscription Choking' from yesterday, as
there might need to be an additional step if the Hotfix is applied:
http://groups-beta.google.com/group/...8?dmode=source
Rgds,
Paul Ibison
sql
Subscribe to:
Posts (Atom)