I believe I have read this before, and apologize for being redundant...
If you have a new server that will be dedicated to reporting, it is more efficient & faster to make this server the Distributor & do a PULL from the production database?
I am pretty sure I have a pretty good understanding of changes being replicated & pushed, but can you help me understand why Pull would be the better option.
Sorry for being thick, I have been reading & reading, and it just isn't sticking...
Thanx!
JLS,
The Distribution Agent can consume significant amounts of memory and CPU cycles, so using pull (or anonymous) subscriptions can increase performance by moving agent processing from the distributor to the subscriber.
The Log Reader Agent writes commands to the distribution database and if you have a remote distributor you also gain by reducing disk I/O on hte production server. Also, the cleanup tasks that are run as a maintenance activity on the distribution database can become expensive and involve significant disk activity.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanx Paul.
I am having an issue of a stored procedure inside a Crystal Report running seamlessly inside my ERP system, faster than my replication records are present in my reporting database.
I am hoping if I change to a PULL subscription with the Distributor being the box where the Reporting database resides, that this problem will be resolved.
I don't want to put a delay into the sp, that would be like shooting myself in the foot. The whole reason for the reporting database is to speed things up on both sides, logically the ERP will run faster once the reports execute on a different box, and this should also be true for the reports, since they will have their own servers.
Any other suggestions for me? I have read everything I can get my hands on.
I'm down to thinking about having 2 separate ODBC drivers, one for the seamless shipping reports that are executing so fast to continue to process on the production db, and all other reports execute on the reporting server & db. But I would really rather have all reports happening on the reporting server & db if at all possible....
"(The ONLY sql server 2000 replication book:" but it's still not available......
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:%23tJzWqdlEHA.3392@.TK2MSFTNGP14.phx.gbl...
JLS,
The Distribution Agent can consume significant amounts of memory and CPU cycles, so using pull (or anonymous) subscriptions can increase performance by moving agent processing from the distributor to the subscriber.
The Log Reader Agent writes commands to the distribution database and if you have a remote distributor you also gain by reducing disk I/O on hte production server. Also, the cleanup tasks that are run as a maintenance activity on the distribution database can become expensive and involve significant disk activity.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||JLS,
I'd definitely recommend this article for performance optimization hints: http://www.microsoft.com/technet/pro.../tranrepl.mspx
In particular, have a look at the sections on the Log Reader Agent Properties and the Distribution Agent Properties.
The most important one in most cases is POLLINGINTERVAL (both agents) in order to reduce the latency to a minimum.
HTH,
Paul Ibison
|||That helps a great deal, Thanx!
One more quick question....
Which table holds the commands to be replicated?
For example, yesterday I had an issue with a duplicate record, and I wanted to clear the command "Call MS_Repl_delete....."
I know I have done this before, but I can't recall what table I cleared it from the last time.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:ObEfrdelEHA.2884@.TK2MSFTNGP09.phx.gbl...
JLS,
I'd definitely recommend this article for performance optimization hints: http://www.microsoft.com/technet/pro.../tranrepl.mspx
In particular, have a look at the sections on the Log Reader Agent Properties and the Distribution Agent Properties.
The most important one in most cases is POLLINGINTERVAL (both agents) in order to reduce the latency to a minimum.
HTH,
Paul Ibison
|||JLS,
select * from distribution.dbo.MSrepl_commands
or
sp_browsereplcmds
Cheers,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label believe. Show all posts
Showing posts with label believe. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Pull Merge Replication - Expand Column Size
I need to increase the size of a column in a table which is part of merge
replication.
Here's the approach I believe I need to take:
1) Use sp_repladdcolumn to create a new temporary column and use the
schema_script to populate my new column
with the values from my old column.
2) Use sp_repldropcolumn to drop the old column
3) Use sp_repladdcolumn to create a new column using the "old column" name
and use the
schema_script to populate the new column with the values.
4) Use sp_repldropcolumn to drop the temporary column created in step 1.
Questions:
1) Do I need do these steps on both the publisher and subscriber?
2) Million Dollar Question - Will this work without having to reinitialize
all my subscribers?
Thanks for all your help!
Tina
1) just publisher
2) yes
Rgds,
Paul Ibison
[vbcol=seagreen]
|||The sp_repladdcolumn system stored procedure adds the dummy column but it
doesn't run the script I set for the schema_change_script parameter.
Is there something I'm missing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e2SwX4IsEHA.4008@.TK2MSFTNGP14.phx.gbl...
> 1) just publisher
> 2) yes
> Rgds,
> Paul Ibison
>
>
|||I have found in testing that this approach will NOT work when using a Push
ExchangeType ( 1 ). Replication makes the schema change on the subscriber
but the values in the column are lost.
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||use sp_repladdcolumn to add a dummy column.
Then update the the value of the dummy column with the value of the column
whose data type you want to change. Do this on the publisher.
Then use sp_repldropcolumn to drop the column which you wish to modify.
Then use sp_repladdcolumn to add the column back in with the correct
datatype.
Then update the value of the new column with the value of the dummy column.
Do this on the publisher.
Then drop the dummy column using sp_repldropcolumn.
Do all the work on the publisher, you shouldn't have to do anything on the
subscriber. You do not have to reinitialize all of your subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||Tina,
what is in the @.schema_change_script that you are
sending? This parameter shouldn't be nesessary for merge.
If it is a script to populate the column, try a simple
update statement instead (on the publisher).[vbcol=seagreen]
|||It's a script to populate the column with the values from the old column.
update CustomerContact
Set dummy = State
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:037e01c4b106$83c0b060$a401280a@.phx.gbl...
> Tina,
> what is in the @.schema_change_script that you are
> sending? This parameter shouldn't be nesessary for merge.
> If it is a script to populate the column, try a simple
> update statement instead (on the publisher).
>
|||I went with the following steps since I couldn't get the schema_script
parameter to work.
All went well on the Publisher, I now have a varchar(3) state column with
the data. I then ran the agent on the subscriber and ended up with a
varchar(3) state column BUT with no data. My subscriber is a PUSH (
exchangetype (1 ) ) for this publication so I don't see how it will work.
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'Dummy'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set dummy = state
GO
sp_repldropcolumn @.source_object = 'CustomerContact'
, @.column = 'state'
GO
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'State'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set state = dummy
GO
sp_repldropcolumn @.source_object = 'CustomerContact' , @.column = 'dummy'
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eko7LbMsEHA.1032@.TK2MSFTNGP10.phx.gbl...
> use sp_repladdcolumn to add a dummy column.
> Then update the the value of the dummy column with the value of the column
> whose data type you want to change. Do this on the publisher.
> Then use sp_repldropcolumn to drop the column which you wish to modify.
> Then use sp_repladdcolumn to add the column back in with the correct
> datatype.
> Then update the value of the new column with the value of the dummy
column.[vbcol=seagreen]
> Do this on the publisher.
> Then drop the dummy column using sp_repldropcolumn.
> Do all the work on the publisher, you shouldn't have to do anything on the
> subscriber. You do not have to reinitialize all of your subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
merge[vbcol=seagreen]
name[vbcol=seagreen]
reinitialize
>
|||Pls try a simple update statement (on the publisher)
after the sp_repladdcolumn statement and miss out the
@.schema_change_script parameter.
TIA,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry Tina,
ignore my last message as in your more recent post you
mentioned the -EXCHANGETYPE forcing uploading. Please try
putting the update statement in sp_addscriptexec as a
step after adding the column.
Rgds,
Paul Ibison
replication.
Here's the approach I believe I need to take:
1) Use sp_repladdcolumn to create a new temporary column and use the
schema_script to populate my new column
with the values from my old column.
2) Use sp_repldropcolumn to drop the old column
3) Use sp_repladdcolumn to create a new column using the "old column" name
and use the
schema_script to populate the new column with the values.
4) Use sp_repldropcolumn to drop the temporary column created in step 1.
Questions:
1) Do I need do these steps on both the publisher and subscriber?
2) Million Dollar Question - Will this work without having to reinitialize
all my subscribers?
Thanks for all your help!
Tina
1) just publisher
2) yes
Rgds,
Paul Ibison
[vbcol=seagreen]
|||The sp_repladdcolumn system stored procedure adds the dummy column but it
doesn't run the script I set for the schema_change_script parameter.
Is there something I'm missing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e2SwX4IsEHA.4008@.TK2MSFTNGP14.phx.gbl...
> 1) just publisher
> 2) yes
> Rgds,
> Paul Ibison
>
>
|||I have found in testing that this approach will NOT work when using a Push
ExchangeType ( 1 ). Replication makes the schema change on the subscriber
but the values in the column are lost.
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||use sp_repladdcolumn to add a dummy column.
Then update the the value of the dummy column with the value of the column
whose data type you want to change. Do this on the publisher.
Then use sp_repldropcolumn to drop the column which you wish to modify.
Then use sp_repladdcolumn to add the column back in with the correct
datatype.
Then update the value of the new column with the value of the dummy column.
Do this on the publisher.
Then drop the dummy column using sp_repldropcolumn.
Do all the work on the publisher, you shouldn't have to do anything on the
subscriber. You do not have to reinitialize all of your subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||Tina,
what is in the @.schema_change_script that you are
sending? This parameter shouldn't be nesessary for merge.
If it is a script to populate the column, try a simple
update statement instead (on the publisher).[vbcol=seagreen]
|||It's a script to populate the column with the values from the old column.
update CustomerContact
Set dummy = State
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:037e01c4b106$83c0b060$a401280a@.phx.gbl...
> Tina,
> what is in the @.schema_change_script that you are
> sending? This parameter shouldn't be nesessary for merge.
> If it is a script to populate the column, try a simple
> update statement instead (on the publisher).
>
|||I went with the following steps since I couldn't get the schema_script
parameter to work.
All went well on the Publisher, I now have a varchar(3) state column with
the data. I then ran the agent on the subscriber and ended up with a
varchar(3) state column BUT with no data. My subscriber is a PUSH (
exchangetype (1 ) ) for this publication so I don't see how it will work.
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'Dummy'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set dummy = state
GO
sp_repldropcolumn @.source_object = 'CustomerContact'
, @.column = 'state'
GO
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'State'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set state = dummy
GO
sp_repldropcolumn @.source_object = 'CustomerContact' , @.column = 'dummy'
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eko7LbMsEHA.1032@.TK2MSFTNGP10.phx.gbl...
> use sp_repladdcolumn to add a dummy column.
> Then update the the value of the dummy column with the value of the column
> whose data type you want to change. Do this on the publisher.
> Then use sp_repldropcolumn to drop the column which you wish to modify.
> Then use sp_repladdcolumn to add the column back in with the correct
> datatype.
> Then update the value of the new column with the value of the dummy
column.[vbcol=seagreen]
> Do this on the publisher.
> Then drop the dummy column using sp_repldropcolumn.
> Do all the work on the publisher, you shouldn't have to do anything on the
> subscriber. You do not have to reinitialize all of your subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
merge[vbcol=seagreen]
name[vbcol=seagreen]
reinitialize
>
|||Pls try a simple update statement (on the publisher)
after the sp_repladdcolumn statement and miss out the
@.schema_change_script parameter.
TIA,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry Tina,
ignore my last message as in your more recent post you
mentioned the -EXCHANGETYPE forcing uploading. Please try
putting the update statement in sp_addscriptexec as a
step after adding the column.
Rgds,
Paul Ibison
Subscribe to:
Posts (Atom)