Does anyone see any problems with do an "Add File" to a publisher or
subscriber database, while replication is running?
There is no impact per se. Depending on the size of the file there may be
some impact which the file is being allocated/initialized.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"frankm" <frankm@.nospam.postalias> wrote in message
news:uxbuu0KoEHA.2900@.TK2MSFTNGP09.phx.gbl...
> Does anyone see any problems with do an "Add File" to a publisher or
> subscriber database, while replication is running?
>
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Push vs. Pull Transaction Replication
Q: Push vs. Pull Transactional Replication
We are currently utilizing a push transactional
replication of a subset of the tables in a production
database involving 3 machines; the publisher which is our
production db machine with 4 hyperthreaded processors, 8
GB ram, a distribution db machine with 1 processor, 2 GB
ram, and a subscriber db machine with 2 hyperthreaded
processors, 6 GB ram to which this table subset is
replicated. All machines are SQL EE with SP3. The
subscriber is used for complex searches and has 6 indexed
views resident on it. All replication agents are set to
run continuously.
The Distribution Agent profiles have been left at the
defaults except for QueryTimeout, which has been set to
3600.
Because we cannot simulate this environment for testing,
I would appreciate hearing from anyone who has experience
with a push vs. pull subscription from a performance
standpoint. Thanks very much for your input.
pull offloads the distribution agent to the subscriber. Hence your
performance on your publisher is better.
The problem is that then you no longer have a central point of
administration - in other words you manage your publications and snapshot
agent on your publisher, but your distribution agent on your subscriber.
I can't quantify exactly what performance increase you will have on your
publisher as it is largely a function of load and hardware on your publisher
and subscriber.
You will have to test to see what performance increase you get. You can get
an idea of what it will be by stopping your log reader and distribution
agent on your publisher.
"fundster" <anonymous@.discussions.microsoft.com> wrote in message
news:98dc01c433b2$498477f0$a301280a@.phx.gbl...
> Q: Push vs. Pull Transactional Replication
> We are currently utilizing a push transactional
> replication of a subset of the tables in a production
> database involving 3 machines; the publisher which is our
> production db machine with 4 hyperthreaded processors, 8
> GB ram, a distribution db machine with 1 processor, 2 GB
> ram, and a subscriber db machine with 2 hyperthreaded
> processors, 6 GB ram to which this table subset is
> replicated. All machines are SQL EE with SP3. The
> subscriber is used for complex searches and has 6 indexed
> views resident on it. All replication agents are set to
> run continuously.
> The Distribution Agent profiles have been left at the
> defaults except for QueryTimeout, which has been set to
> 3600.
> Because we cannot simulate this environment for testing,
> I would appreciate hearing from anyone who has experience
> with a push vs. pull subscription from a performance
> standpoint. Thanks very much for your input.
>
|||I would think that you would not need to stop the log reader. Even with a
pull subscription the log reader is still running on the publisher.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||that's a very good point Rand, I missed that.
I was trying to get a baseline "without" replication, but to get a
representative baseline with pull subscriptions you really should have the
log reader agent running.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:nxrqWlENEHA.3780@.cpmsftngxa10.phx.gbl...
> I would think that you would not need to stop the log reader. Even with a
> pull subscription the log reader is still running on the publisher.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
sql
We are currently utilizing a push transactional
replication of a subset of the tables in a production
database involving 3 machines; the publisher which is our
production db machine with 4 hyperthreaded processors, 8
GB ram, a distribution db machine with 1 processor, 2 GB
ram, and a subscriber db machine with 2 hyperthreaded
processors, 6 GB ram to which this table subset is
replicated. All machines are SQL EE with SP3. The
subscriber is used for complex searches and has 6 indexed
views resident on it. All replication agents are set to
run continuously.
The Distribution Agent profiles have been left at the
defaults except for QueryTimeout, which has been set to
3600.
Because we cannot simulate this environment for testing,
I would appreciate hearing from anyone who has experience
with a push vs. pull subscription from a performance
standpoint. Thanks very much for your input.
pull offloads the distribution agent to the subscriber. Hence your
performance on your publisher is better.
The problem is that then you no longer have a central point of
administration - in other words you manage your publications and snapshot
agent on your publisher, but your distribution agent on your subscriber.
I can't quantify exactly what performance increase you will have on your
publisher as it is largely a function of load and hardware on your publisher
and subscriber.
You will have to test to see what performance increase you get. You can get
an idea of what it will be by stopping your log reader and distribution
agent on your publisher.
"fundster" <anonymous@.discussions.microsoft.com> wrote in message
news:98dc01c433b2$498477f0$a301280a@.phx.gbl...
> Q: Push vs. Pull Transactional Replication
> We are currently utilizing a push transactional
> replication of a subset of the tables in a production
> database involving 3 machines; the publisher which is our
> production db machine with 4 hyperthreaded processors, 8
> GB ram, a distribution db machine with 1 processor, 2 GB
> ram, and a subscriber db machine with 2 hyperthreaded
> processors, 6 GB ram to which this table subset is
> replicated. All machines are SQL EE with SP3. The
> subscriber is used for complex searches and has 6 indexed
> views resident on it. All replication agents are set to
> run continuously.
> The Distribution Agent profiles have been left at the
> defaults except for QueryTimeout, which has been set to
> 3600.
> Because we cannot simulate this environment for testing,
> I would appreciate hearing from anyone who has experience
> with a push vs. pull subscription from a performance
> standpoint. Thanks very much for your input.
>
|||I would think that you would not need to stop the log reader. Even with a
pull subscription the log reader is still running on the publisher.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||that's a very good point Rand, I missed that.
I was trying to get a baseline "without" replication, but to get a
representative baseline with pull subscriptions you really should have the
log reader agent running.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:nxrqWlENEHA.3780@.cpmsftngxa10.phx.gbl...
> I would think that you would not need to stop the log reader. Even with a
> pull subscription the log reader is still running on the publisher.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
sql
push to subscription in non-trusted domain (Sql 2000)
Thanks for your response.
I don't know how to do pass-through authentication across domain
(particularly using SQL2k replication), I thought that would require a
trusted account on both sides.
I have been trying using the sql account and although I can connect to the
subscriber using the account from query tools and em, I keep receiving an
error when my distribution agent tries to connect: "The process could not
connect to Subscriber 'subscriptionservername'"
I've tried it before and after manipulating the Replication Distributor
authentication arguments and the Subscriber is already setup on the
Distributor to use sql authentication (I've reset this login and pwd at
least once for each trial, I'm reasonable sure it is not mistyped). Same
errant response always.
I even once tried populating the data 1st (in case the snapshot folder was
the only issue) but I did not try to populate the MS* subscription tables
which I figured would blow up anyway, but it couldn't connect so I got the
same response.
Got any ideas? Am I missing something?
Please ... Help!
Sorry, this should have been a reply to a different post.
"William Millett" <william.millett@.altivity.com> wrote in message
news:OHhOC68aHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Thanks for your response.
> I don't know how to do pass-through authentication across domain
> (particularly using SQL2k replication), I thought that would require a
> trusted account on both sides.
> I have been trying using the sql account and although I can connect to the
> subscriber using the account from query tools and em, I keep receiving an
> error when my distribution agent tries to connect: "The process could not
> connect to Subscriber 'subscriptionservername'"
> I've tried it before and after manipulating the Replication Distributor
> authentication arguments and the Subscriber is already setup on the
> Distributor to use sql authentication (I've reset this login and pwd at
> least once for each trial, I'm reasonable sure it is not mistyped). Same
> errant response always.
> I even once tried populating the data 1st (in case the snapshot folder
> was the only issue) but I did not try to populate the MS* subscription
> tables which I figured would blow up anyway, but it couldn't connect so I
> got the same response.
> Got any ideas? Am I missing something?
> Please ... Help!
>
I don't know how to do pass-through authentication across domain
(particularly using SQL2k replication), I thought that would require a
trusted account on both sides.
I have been trying using the sql account and although I can connect to the
subscriber using the account from query tools and em, I keep receiving an
error when my distribution agent tries to connect: "The process could not
connect to Subscriber 'subscriptionservername'"
I've tried it before and after manipulating the Replication Distributor
authentication arguments and the Subscriber is already setup on the
Distributor to use sql authentication (I've reset this login and pwd at
least once for each trial, I'm reasonable sure it is not mistyped). Same
errant response always.
I even once tried populating the data 1st (in case the snapshot folder was
the only issue) but I did not try to populate the MS* subscription tables
which I figured would blow up anyway, but it couldn't connect so I got the
same response.
Got any ideas? Am I missing something?
Please ... Help!
Sorry, this should have been a reply to a different post.
"William Millett" <william.millett@.altivity.com> wrote in message
news:OHhOC68aHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Thanks for your response.
> I don't know how to do pass-through authentication across domain
> (particularly using SQL2k replication), I thought that would require a
> trusted account on both sides.
> I have been trying using the sql account and although I can connect to the
> subscriber using the account from query tools and em, I keep receiving an
> error when my distribution agent tries to connect: "The process could not
> connect to Subscriber 'subscriptionservername'"
> I've tried it before and after manipulating the Replication Distributor
> authentication arguments and the Subscriber is already setup on the
> Distributor to use sql authentication (I've reset this login and pwd at
> least once for each trial, I'm reasonable sure it is not mistyped). Same
> errant response always.
> I even once tried populating the data 1st (in case the snapshot folder
> was the only issue) but I did not try to populate the MS* subscription
> tables which I figured would blow up anyway, but it couldn't connect so I
> got the same response.
> Got any ideas? Am I missing something?
> Please ... Help!
>
Push Subscription - will it overwrite existing data first?
If I push a new subscription to an existing target database, and I choose or
check the box to include schema and data, will replication first delete or
empty tables at the target before pushing a new?
I did this, that is to push the subscription and checked the box to send
schema and data and killed the process mid-stream. At the target I found
certain tables to be empty. I am concluding that replication will first
empty or delete tables at the target.
Am I correct?
thanks,
bob
Bob,
on the article properties of a table, on the snapshot tab there is the
option to control this behaviour. By default if you look at the bcp files
per article, they will start with a drop table then a create table and after
that the rows will be inserted. It looks like you caught the process in
between these batches.
HTH,
Paul Ibison
check the box to include schema and data, will replication first delete or
empty tables at the target before pushing a new?
I did this, that is to push the subscription and checked the box to send
schema and data and killed the process mid-stream. At the target I found
certain tables to be empty. I am concluding that replication will first
empty or delete tables at the target.
Am I correct?
thanks,
bob
Bob,
on the article properties of a table, on the snapshot tab there is the
option to control this behaviour. By default if you look at the bcp files
per article, they will start with a drop table then a create table and after
that the rows will be inserted. It looks like you caught the process in
between these batches.
HTH,
Paul Ibison
push subscriber in merge replication
I am using merge replication with a push subscription type. I am wondering if the updates of the tables on the subscriber side are push to the publisher from the subscriber or pulled from the subscriber by the publisher when the syncronisation takes place. this makes a big diferrence for me and i can't find the answer to this question anywhere...
if anyone could answer it would be really appreciated
ThanksData Synchronization is performed by the Merge agent, wherever it runs from. In your case it's the Publisher.|||The Merge agent runs on the publisher but my question is : does the merge agent pull information from the subcriber or the subscriber push the information to the merge agent ??|||Well, it's a PULL if you look at it this way. The Agent runs on the Publisher, connects to Subscriber (see the agent's job definition in Scheduled Tasks), etc.sql
if anyone could answer it would be really appreciated
ThanksData Synchronization is performed by the Merge agent, wherever it runs from. In your case it's the Publisher.|||The Merge agent runs on the publisher but my question is : does the merge agent pull information from the subcriber or the subscriber push the information to the merge agent ??|||Well, it's a PULL if you look at it this way. The Agent runs on the Publisher, connects to Subscriber (see the agent's job definition in Scheduled Tasks), etc.sql
push snapshot
Hi I'm trying to setup a push replication between 2 servers on different
networks. both machines can ping eachother using there machine names. I setup
the publication and the distributor on the push server and generate a
snapshot. I then run the distributor but it fails to connect to the
subscriber.
thanks for the help.
Mark
You need to ensure the @.@.servername of the subscriber is how the
publisher/distributor refer to it and vice versa. If you have to use IP
address, you can setup client side alias on the distributor that maps the IP
address to the @.@.servername of the subscriber
“This posting is provided "AS IS" with no warranties, and confers no rights.”
"ACDMark" wrote:
> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark
|||hi ,
be sure that your sql agent services is
running on a domain account
that has permission to your snapshot folder
and the database
thanks,
joey
"ACDMark" wrote:
> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark
networks. both machines can ping eachother using there machine names. I setup
the publication and the distributor on the push server and generate a
snapshot. I then run the distributor but it fails to connect to the
subscriber.
thanks for the help.
Mark
You need to ensure the @.@.servername of the subscriber is how the
publisher/distributor refer to it and vice versa. If you have to use IP
address, you can setup client side alias on the distributor that maps the IP
address to the @.@.servername of the subscriber
“This posting is provided "AS IS" with no warranties, and confers no rights.”
"ACDMark" wrote:
> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark
|||hi ,
be sure that your sql agent services is
running on a domain account
that has permission to your snapshot folder
and the database
thanks,
joey
"ACDMark" wrote:
> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark
Push replication to multihomed client fails
Hi
We're having problems with push replication to multihomed clients. if the DNS contains multiple IP's and one of those are not reachable from the sql server. the replication fails. no matter order of IP adresses. i wrote a DNS proxy which re-ordered the IP's so that the "working" IP would come first in the list but no success until i filtered out so that the DNS proxy only replied with one, the working, IP.
the replication is part of a commercial application which we have no control over. are there any settings on the server side that can help here? I really want to fix the source of the problem and not patch it with a proxy.
any ideas are welcome. the server is sql 2005 standard and the clients are running 2005 express
Rgds
just an update, if anyone has the same problem
i found out how it works by reading this thread.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1526698&SiteID=1
this doesnt fix the problem. but it explains it very well. so we'll stick with the proxy.
Push replication to multihomed client fails
Hi
We're having problems with push replication to multihomed clients. if the DNS contains multiple IP's and one of those are not reachable from the sql server. the replication fails. no matter order of IP adresses. i wrote a DNS proxy which re-ordered the IP's so that the "working" IP would come first in the list but no success until i filtered out so that the DNS proxy only replied with one, the working, IP.
the replication is part of a commercial application which we have no control over. are there any settings on the server side that can help here? I really want to fix the source of the problem and not patch it with a proxy.
any ideas are welcome. the server is sql 2005 standard and the clients are running 2005 express
Rgds
just an update, if anyone has the same problem
i found out how it works by reading this thread.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1526698&SiteID=1
this doesnt fix the problem. but it explains it very well. so we'll stick with the proxy.
sqlPush replication between SQL2k and SQL2k
Hi,
I am wanting to set up replication between two SQLServer 2000 databases. DB1 has the data, and DB2 is where I would like to replicate to. I have set up the publisher and distributor on DB1, and have set up DB2 as the subscriber. The replication is set up as transactional.
When the agent runs, it fails with the following error details:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Where do I set the user that the agent should be using?
I think that it is also worth noting that the two computers are not on the same domain. There are no firewalls involved either.I Think it uses the SQL Server Agent Sevice Startup Account security
Right click on the Agent in EM & Make sure that the account has access to both Servers.
or
Go into the server Services and look at the SQL Agent Startup Account
There are other areas but try this till someone explains it better than I can|||Aha! I found the answer!
The agent at the distributor is trying to start the agent at the subscriber. The correct permissions must be given on the agent at the subscriber so that the agent at the distributor can execute the app. I am not exactly sure what it requires yet, but this is what I did:
1) Run the dcomcnfg application from the Run box.
2) Select the 'Microsoft SQL Server Replication Remote Dist Agent 8.0'
3) Open the properties of the agent.
4) Select the Security tab.
5) Use custom configurations for all settings in this tab. Add the NETWORK group to each configuration group and give the same level of access as the other accounts in the configuration.
6) Select the Identity tab.
7) Select 'This user' and provide a local machine user name that has permissions to run this app.
8) Click OK to save the changes.
The next problem is that if you are running 2000 with SP3, then the user provided by the agent will not be trusted and will give you another error. Error number 18452 or something similar.
To make the user trusted, you must execute the xp_sqlagent_proxy_account stored procedure on the subscriber server.
xp_sqlagent_proxy_account
{N'GET' | N'SET' | N'DEL ,
N'agent_domain_name', N'agent_username', N'agent_password'
}
You can find more documentation on this stored procedure in MSDN under SQL Server 2000 and Replication Security.
I am wanting to set up replication between two SQLServer 2000 databases. DB1 has the data, and DB2 is where I would like to replicate to. I have set up the publisher and distributor on DB1, and have set up DB2 as the subscriber. The replication is set up as transactional.
When the agent runs, it fails with the following error details:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Where do I set the user that the agent should be using?
I think that it is also worth noting that the two computers are not on the same domain. There are no firewalls involved either.I Think it uses the SQL Server Agent Sevice Startup Account security
Right click on the Agent in EM & Make sure that the account has access to both Servers.
or
Go into the server Services and look at the SQL Agent Startup Account
There are other areas but try this till someone explains it better than I can|||Aha! I found the answer!
The agent at the distributor is trying to start the agent at the subscriber. The correct permissions must be given on the agent at the subscriber so that the agent at the distributor can execute the app. I am not exactly sure what it requires yet, but this is what I did:
1) Run the dcomcnfg application from the Run box.
2) Select the 'Microsoft SQL Server Replication Remote Dist Agent 8.0'
3) Open the properties of the agent.
4) Select the Security tab.
5) Use custom configurations for all settings in this tab. Add the NETWORK group to each configuration group and give the same level of access as the other accounts in the configuration.
6) Select the Identity tab.
7) Select 'This user' and provide a local machine user name that has permissions to run this app.
8) Click OK to save the changes.
The next problem is that if you are running 2000 with SP3, then the user provided by the agent will not be trusted and will give you another error. Error number 18452 or something similar.
To make the user trusted, you must execute the xp_sqlagent_proxy_account stored procedure on the subscriber server.
xp_sqlagent_proxy_account
{N'GET' | N'SET' | N'DEL ,
N'agent_domain_name', N'agent_username', N'agent_password'
}
You can find more documentation on this stored procedure in MSDN under SQL Server 2000 and Replication Security.
Push Replication
I have a general question. I want to push data from MSDE to SQL Server
2000. The pushed data is to end up in a 'staging' database on SQL
Server. As part of this process I want to run a stored procedure
against the staged data to update the production database (on the same
SQL Server). I then would like to move the updated production data back
to the staging database (using a stored procedure) and finally back to
the remote client running MSDE. Using events, ActiveX and/or scripts,
can this entire process be encapsulated? The desire is for the user to
connect to headquarters, send his updates, get his updates and
disconnect. Thank you in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Sounds like republishing.
make the production database the publisher and the staging database a
subscriber. Then make another publication on your subscriber which the MSDE
database will subscribe to.
You can use Windows Synchronization Manager to control the pull, or use an
ActiveX control.
Merge replication would probably be the idea replication type.
"Alan Rubel" <alrubel@.hotmail.com> wrote in message
news:%2315ZV1htEHA.2196@.TK2MSFTNGP14.phx.gbl...
>I have a general question. I want to push data from MSDE to SQL Server
> 2000. The pushed data is to end up in a 'staging' database on SQL
> Server. As part of this process I want to run a stored procedure
> against the staged data to update the production database (on the same
> SQL Server). I then would like to move the updated production data back
> to the staging database (using a stored procedure) and finally back to
> the remote client running MSDE. Using events, ActiveX and/or scripts,
> can this entire process be encapsulated? The desire is for the user to
> connect to headquarters, send his updates, get his updates and
> disconnect. Thank you in advance.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thank you. I will try it.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
2000. The pushed data is to end up in a 'staging' database on SQL
Server. As part of this process I want to run a stored procedure
against the staged data to update the production database (on the same
SQL Server). I then would like to move the updated production data back
to the staging database (using a stored procedure) and finally back to
the remote client running MSDE. Using events, ActiveX and/or scripts,
can this entire process be encapsulated? The desire is for the user to
connect to headquarters, send his updates, get his updates and
disconnect. Thank you in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Sounds like republishing.
make the production database the publisher and the staging database a
subscriber. Then make another publication on your subscriber which the MSDE
database will subscribe to.
You can use Windows Synchronization Manager to control the pull, or use an
ActiveX control.
Merge replication would probably be the idea replication type.
"Alan Rubel" <alrubel@.hotmail.com> wrote in message
news:%2315ZV1htEHA.2196@.TK2MSFTNGP14.phx.gbl...
>I have a general question. I want to push data from MSDE to SQL Server
> 2000. The pushed data is to end up in a 'staging' database on SQL
> Server. As part of this process I want to run a stored procedure
> against the staged data to update the production database (on the same
> SQL Server). I then would like to move the updated production data back
> to the staging database (using a stored procedure) and finally back to
> the remote client running MSDE. Using events, ActiveX and/or scripts,
> can this entire process be encapsulated? The desire is for the user to
> connect to headquarters, send his updates, get his updates and
> disconnect. Thank you in advance.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thank you. I will try it.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Push replication
I need to pull the schema only with no data, then push the data back without
deletes on the publisher, then delete the client data. Next time these
deletes would not be pushed up. Only new data.
SQL Server 2005 June CTP and SQL Mobile 2005
"msmith" wrote:
> I need to pull the schema only with no data, then push the data back without
> deletes on the publisher, then delete the client data. Next time these
> deletes would not be pushed up. Only new data.
deletes on the publisher, then delete the client data. Next time these
deletes would not be pushed up. Only new data.
SQL Server 2005 June CTP and SQL Mobile 2005
"msmith" wrote:
> I need to pull the schema only with no data, then push the data back without
> deletes on the publisher, then delete the client data. Next time these
> deletes would not be pushed up. Only new data.
Push referenced records
Hi,
I am facing the following problem. Please help me to solve it.
My question is: How to force the replication engine to include not only
the updated records but the relevant records as well?
I am using merge replication (SQL2kSP3 with SQL2kCE) with row filtering.
I many cases i have to replicate tables with "many to many"
relationship, where i operate the connection table (BOOKPERSON in the
example bellow). All the rows are filtered by PERSON_ID through out the
database.
Eg.
PERSON records to send:
select * from person where person_id = HOST_NAME()
BOOK records to send:
select * from book inner join bookperson on book.book_id =
bookperson.book_id and bookperson.person_id = HOST_NAME()
BOOKPERSON records to send:
select * from bookperson where person_id = HOST_NAME()
My problem is the following. The newly initialized subsciption
replicates fine, but in case of inserting 1 row to the table BOOKPERSON
(which means associating a book with a person) causes the replication to
fail, because the it replicates only the inserted BOOKPERSON record, and
does not replicate the the relevant BOOK record that is referenced now
by the BOOKPERSON record.
So my question is: How to force the replication engine to include not
only the updated records but the relevant records as well?
Thanks in advance
Pierre
The relevant schema is as follows:
CREATE TABLE Book (
BOOK_ID int not null,
BOOK_TITLE varchar(30) not null,
constraint PK_BOOK primary key clustered (BOOK_ID)
)
CREATE TABLE Person (
PERSON_ID int not null,
PERSON_NAME varchar(30) not null,
constraint PK_PERSON primary key clustered (PERSON_ID)
)
CREATE TABLE BookPerson (
BOOK_ID int not null,
PERSON_ID int not null,
constraint PK_BOOKPERSON primary key clustered (BOOK_ID, PERSON_ID)
)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_BOOKPERSON foreign key (BOOK_ID)
references BOOK (BOOK_ID)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_PERSONBOOK foreign key (PERSON_ID)
references PERSON (PERSON_ID)
Pierre,
presumably the personid value for a bookperson and book are not really
referring to the same entity (a person can be related to an individual book
as eg an author and as a reader), otherwise the related book would already
be replicated? In that case I'd say that the filter clause on the Books
article is incorrect. The simplest way would be to drop this filter and
replicate all the books.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I think I forgot to tell that we have thousands of records in the "BOOK"
table, and our subscribers have very limited storage capacity (~1000
thousand book records).
The relation between the two tables (person, book) is not like "authors
of a book" but "books 'thouched' by person", so there is a real M:N
relationship.
Meanwhile found the solution to my problem. I think I have to create
triggers (for insert, delete and update) on the table BookPerson which
will use a stored procedure (sp_mergedummyupdate) to force the
replication engine to put the relevant book records into the publication.
Am I right?
regards
Pierre
|||Pierre,
this sounds correct. Actually I misread your original post and didn't notice
the inner join, so was thinking that the PersonID was a FK directly to the
books table as an author. You'll need to make sure that the FK relationship
is 'Not For Replication', as you can't guarantee the replication order (in
SQL 2005 the default is PK then FK records but not in SQL 2000).
Regards,
Paul Ibison
|||Thank you for the tip, i`ll try it.
pierre
|||Paul,
we have tried to script the references with the "NOT FOR REPLICATION" option
and it caused SQL Server CE to completly break down at replication. After a
quick search on MSDN we found a reported bug saying that SQL Server CE
doesn't support the option mentioned above. (to be specfic SQL Server CE
supports NOT FOR REPLICATION, but there is a bug so we can't use it.)
If I'm not mistaken we have to drop all our references in these
circustances. Am I right? Is there any way to control the order of bulk
inserts at replication. (after PDA downloaded the appropriate snapshot, it
starts to bulk insert & update the rows)
regards
Pierre
"Paul Ibison" wrote:
> Pierre,
> this sounds correct. Actually I misread your original post and didn't notice
> the inner join, so was thinking that the PersonID was a FK directly to the
> books table as an author. You'll need to make sure that the FK relationship
> is 'Not For Replication', as you can't guarantee the replication order (in
> SQL 2005 the default is PK then FK records but not in SQL 2000).
> Regards,
> Paul Ibison
>
>
|||Pierre,
can you post up your reference for this CE bug (I'll put it on my website).
I haven't seen this reference, but if that is the case, you could increase
the -UploadGenerationsPerBatch
and the -DownloadGenerationsPerBatch parameters (to the max of 2000) to
avoid splitting parent and child changes across generation batches.
See http://support.microsoft.com/default...b;EN-US;308266 for more
info.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||BUG: NOT FOR REPLICATION Clause Causes SQL Server CE Replication to Fail
http://support.microsoft.com/default...b;en-us;300597
...however SQL Server CE Books Online says that "NOT FOR REPLICATION" is not
supported in case of foreign key constriants...
anyway, thanks for the tip, i'll try that.
pierre
sql
I am facing the following problem. Please help me to solve it.
My question is: How to force the replication engine to include not only
the updated records but the relevant records as well?
I am using merge replication (SQL2kSP3 with SQL2kCE) with row filtering.
I many cases i have to replicate tables with "many to many"
relationship, where i operate the connection table (BOOKPERSON in the
example bellow). All the rows are filtered by PERSON_ID through out the
database.
Eg.
PERSON records to send:
select * from person where person_id = HOST_NAME()
BOOK records to send:
select * from book inner join bookperson on book.book_id =
bookperson.book_id and bookperson.person_id = HOST_NAME()
BOOKPERSON records to send:
select * from bookperson where person_id = HOST_NAME()
My problem is the following. The newly initialized subsciption
replicates fine, but in case of inserting 1 row to the table BOOKPERSON
(which means associating a book with a person) causes the replication to
fail, because the it replicates only the inserted BOOKPERSON record, and
does not replicate the the relevant BOOK record that is referenced now
by the BOOKPERSON record.
So my question is: How to force the replication engine to include not
only the updated records but the relevant records as well?
Thanks in advance
Pierre
The relevant schema is as follows:
CREATE TABLE Book (
BOOK_ID int not null,
BOOK_TITLE varchar(30) not null,
constraint PK_BOOK primary key clustered (BOOK_ID)
)
CREATE TABLE Person (
PERSON_ID int not null,
PERSON_NAME varchar(30) not null,
constraint PK_PERSON primary key clustered (PERSON_ID)
)
CREATE TABLE BookPerson (
BOOK_ID int not null,
PERSON_ID int not null,
constraint PK_BOOKPERSON primary key clustered (BOOK_ID, PERSON_ID)
)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_BOOKPERSON foreign key (BOOK_ID)
references BOOK (BOOK_ID)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_PERSONBOOK foreign key (PERSON_ID)
references PERSON (PERSON_ID)
Pierre,
presumably the personid value for a bookperson and book are not really
referring to the same entity (a person can be related to an individual book
as eg an author and as a reader), otherwise the related book would already
be replicated? In that case I'd say that the filter clause on the Books
article is incorrect. The simplest way would be to drop this filter and
replicate all the books.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I think I forgot to tell that we have thousands of records in the "BOOK"
table, and our subscribers have very limited storage capacity (~1000
thousand book records).
The relation between the two tables (person, book) is not like "authors
of a book" but "books 'thouched' by person", so there is a real M:N
relationship.
Meanwhile found the solution to my problem. I think I have to create
triggers (for insert, delete and update) on the table BookPerson which
will use a stored procedure (sp_mergedummyupdate) to force the
replication engine to put the relevant book records into the publication.
Am I right?
regards
Pierre
|||Pierre,
this sounds correct. Actually I misread your original post and didn't notice
the inner join, so was thinking that the PersonID was a FK directly to the
books table as an author. You'll need to make sure that the FK relationship
is 'Not For Replication', as you can't guarantee the replication order (in
SQL 2005 the default is PK then FK records but not in SQL 2000).
Regards,
Paul Ibison
|||Thank you for the tip, i`ll try it.
pierre
|||Paul,
we have tried to script the references with the "NOT FOR REPLICATION" option
and it caused SQL Server CE to completly break down at replication. After a
quick search on MSDN we found a reported bug saying that SQL Server CE
doesn't support the option mentioned above. (to be specfic SQL Server CE
supports NOT FOR REPLICATION, but there is a bug so we can't use it.)
If I'm not mistaken we have to drop all our references in these
circustances. Am I right? Is there any way to control the order of bulk
inserts at replication. (after PDA downloaded the appropriate snapshot, it
starts to bulk insert & update the rows)
regards
Pierre
"Paul Ibison" wrote:
> Pierre,
> this sounds correct. Actually I misread your original post and didn't notice
> the inner join, so was thinking that the PersonID was a FK directly to the
> books table as an author. You'll need to make sure that the FK relationship
> is 'Not For Replication', as you can't guarantee the replication order (in
> SQL 2005 the default is PK then FK records but not in SQL 2000).
> Regards,
> Paul Ibison
>
>
|||Pierre,
can you post up your reference for this CE bug (I'll put it on my website).
I haven't seen this reference, but if that is the case, you could increase
the -UploadGenerationsPerBatch
and the -DownloadGenerationsPerBatch parameters (to the max of 2000) to
avoid splitting parent and child changes across generation batches.
See http://support.microsoft.com/default...b;EN-US;308266 for more
info.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||BUG: NOT FOR REPLICATION Clause Causes SQL Server CE Replication to Fail
http://support.microsoft.com/default...b;en-us;300597
...however SQL Server CE Books Online says that "NOT FOR REPLICATION" is not
supported in case of foreign key constriants...
anyway, thanks for the tip, i'll try that.
pierre
sql
Push over the Internet?
Is it possible to have a push subscription for merge replication over the Internet?
If so, how?
If not, does anybody know why not?
Hi,
Have a look into the below article.
http://support.microsoft.com/?id=321822
FYI, I have never tried replication over internet.
Thanks
Hari
MCDBA
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:9B38B0BC-8700-46A9-A456-9432F24CFBAF@.microsoft.com...
> Is it possible to have a push subscription for merge replication over the
Internet?
> If so, how?
> If not, does anybody know why not?
|||Hari,
Thanks for the reply. Since writing my post I have become VERY familiar with that article. From what I understand, you can push over the Internet if the subscriber already has the schema. To get the initial schema, however, the subscriber must have a snap
shot agent that is configured to pull via FTP. While this is not my ideal solution, I may be able to make it work. Some of my subscribers will be using MSDE though so I don't know how well that will work.
Thanks,
Kyle
"Hari" wrote:
> Hi,
> Have a look into the below article.
> http://support.microsoft.com/?id=321822
> FYI, I have never tried replication over internet.
> --
> Thanks
> Hari
> MCDBA
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:9B38B0BC-8700-46A9-A456-9432F24CFBAF@.microsoft.com...
> Internet?
>
>
|||you can't push over the internet.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> Hari,
> Thanks for the reply. Since writing my post I have become VERY familiar
with that article. From what I understand, you can push over the Internet if
the subscriber already has the schema. To get the initial schema, however,
the subscriber must have a snapshot agent that is configured to pull via
FTP. While this is not my ideal solution, I may be able to make it work.
Some of my subscribers will be using MSDE though so I don't know how well
that will work.[vbcol=seagreen]
> Thanks,
> Kyle
>
> "Hari" wrote:
the[vbcol=seagreen]
|||Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are devoting an ENTIRE book on merge replication. THA
TS GREAT! because so far, I have not been able to find a book that deals with the subject in depth. Usually I just see a chapter on replication that just repeates the Microsoft BOL. Please let me know when it is released.
That said, I HAVE been able to push over the Internet. It's not easy, it's not obvious and it's not well documented but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push over the Interne
t with dynamic filtering:
1. The publication must be saved to an FTP site that is available to the subscribers.
2. You must make a server alias (with Client Network Utility) of the subscriber on the publisher server that MATCHES the actual NetBIOS computer name of the subscriber. It seems dumb that you have to do this but it works.
3. For a dynamic filter to work with a push subscription, you must filter on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent step of the subscription.
If I've got something wrong in my head or you have any other tips, I'd love to hear about it. Also, if I can be of any help with your upcoming book, please let me know.
Thanks,
Kyle
"Hilary Cotter" wrote:
> you can't push over the internet.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> with that article. From what I understand, you can push over the Internet if
> the subscriber already has the schema. To get the initial schema, however,
> the subscriber must have a snapshot agent that is configured to pull via
> FTP. While this is not my ideal solution, I may be able to make it work.
> Some of my subscribers will be using MSDE though so I don't know how well
> that will work.
> the
>
>
|||Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are currently working on a book devoted to merge repl
ication. Selfishly, I'm hoping you don't have good surf conditions for the next few months. Please let me know when it is released.
That said, I HAVE been able to push subscriptions over the Internet. It's not obvious or easy but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push a merge subscription over the internet
with dynamic filtering:
1. The publication must be put on an FTP site that is accessable to the subscribers.
2. The publishing server must have an alias to the subscriber that matches the actual NetBIOS computer name of the subscriber machine. This seems really dumb to me but it's the only way I could get it to work.
3. To enable dynamic filtering with a push subscription, you must filter on HOST_NAME() and you must put a -hostname [somename] parameter in the Run Agent step of the merge agent. This allows you to specify a particular subset of data to each subscriber i
ndependant of the subscriber's actual name or user (although, as I mentioned in point 2, you do have to know the subscriber's computer name).
If I've got something wrong or you have any other tips for me, please let me know. Also, if I can be of any help with your upcoming book, I'd be glad to contribute.
Thanks,
Kyle
"Hilary Cotter" wrote:
> you can't push over the internet.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> with that article. From what I understand, you can push over the Internet if
> the subscriber already has the schema. To get the initial schema, however,
> the subscriber must have a snapshot agent that is configured to pull via
> FTP. While this is not my ideal solution, I may be able to make it work.
> Some of my subscribers will be using MSDE though so I don't know how well
> that will work.
> the
>
>
|||But if FTP is in the mix you are using a pull.
You don't have to register you Netbios name of the Subscriber on the
publisher. It can be any name as long as that name matches the ip address of
your subscriber and this name is entered in your hosts file.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:39C08D3F-D3FC-47E2-BB32-1AB5C04C8919@.microsoft.com...
> Hilary,
> I clicked on your book link and was disappointed to see that you wrote a
book on transactional and snapshot replication but not merge. I then checked
out the nwsu site and was delighted to see that you are devoting an ENTIRE
book on merge replication. THATS GREAT! because so far, I have not been able
to find a book that deals with the subject in depth. Usually I just see a
chapter on replication that just repeates the Microsoft BOL. Please let me
know when it is released.
> That said, I HAVE been able to push over the Internet. It's not easy, it's
not obvious and it's not well documented but it IS possible. I'm still
working on my application but here's what I have learned so far which has
enabled me to push over the Internet with dynamic filtering:
> 1. The publication must be saved to an FTP site that is available to the
subscribers.
> 2. You must make a server alias (with Client Network Utility) of the
subscriber on the publisher server that MATCHES the actual NetBIOS computer
name of the subscriber. It seems dumb that you have to do this but it works.
> 3. For a dynamic filter to work with a push subscription, you must filter
on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent
step of the subscription.
> If I've got something wrong in my head or you have any other tips, I'd
love to hear about it. Also, if I can be of any help with your upcoming
book, please let me know.[vbcol=seagreen]
> Thanks,
> Kyle
> "Hilary Cotter" wrote:
familiar[vbcol=seagreen]
Internet if[vbcol=seagreen]
however,[vbcol=seagreen]
well[vbcol=seagreen]
over[vbcol=seagreen]
|||I see your point about the FTP but I don't think I'm creating a pull subscription. For the initial data... on the publisher I must tell the subscribers how to get to the FTP site (IP, login, password, folder). This is done via the Snapshot Location tab on
the publisher properties form, but this information does not show up anywhere (that I can find) on the subscriber. The subscriber shows that it has one subscription and that it is a push type. From the subscriber, I cannot modify or delete the subscripti
on - meaning that the publisher has complete control (as long as the connection remains in tact). Maybe internally there is an invisible pull subscription but from both sides all I see is push and the publisher maintains complete control (which is what I
want). Also, on the Subscription Options tab of the publication properties, I have un-checked the Allow pull subscriptions checkbox further convincing me that there are no pull subscriptions (even while moving the initial snapshot).
I tried using the lmhosts file instead of registering an alias with the Client Network Utility to map the subscriber IP to a name other than it's actual NetBIOS name. That allowed me to connect and it allowed me to push the initial snapshot. The merge age
nt failed, however, giving me the following error:
The subscription to publication 'TestPush1' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
-----
The remote server is not defined as a subscription server.
(Source: SACRAMENTO (Data source); Error number: 14010)
-----
SACRAMENTO is the name in the lmhost file that is mapped to the subscriber's IP address. It IS defined as a subscription server. The same publication 'TestPush1' works fine with the other subscription (where the alias = the actual NetBIOS name).
This is all pretty much consistent with the KB article 321822 except that the article implies that you can't move the initial schema and data without a pull subscription. It does say, however, that you need to use the actual NetBIOS name although I would
sure like to find a way around this requirement.
Kyle
"Hilary Cotter" wrote:
> But if FTP is in the mix you are using a pull.
> You don't have to register you Netbios name of the Subscriber on the
> publisher. It can be any name as long as that name matches the ip address of
> your subscriber and this name is entered in your hosts file.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:39C08D3F-D3FC-47E2-BB32-1AB5C04C8919@.microsoft.com...
> book on transactional and snapshot replication but not merge. I then checked
> out the nwsu site and was delighted to see that you are devoting an ENTIRE
> book on merge replication. THATS GREAT! because so far, I have not been able
> to find a book that deals with the subject in depth. Usually I just see a
> chapter on replication that just repeates the Microsoft BOL. Please let me
> know when it is released.
> not obvious and it's not well documented but it IS possible. I'm still
> working on my application but here's what I have learned so far which has
> enabled me to push over the Internet with dynamic filtering:
> subscribers.
> subscriber on the publisher server that MATCHES the actual NetBIOS computer
> name of the subscriber. It seems dumb that you have to do this but it works.
> on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent
> step of the subscription.
> love to hear about it. Also, if I can be of any help with your upcoming
> book, please let me know.
> familiar
> Internet if
> however,
> well
> over
>
>
|||Can you script out your publication so we can see exactly what you have
done. It is possible you did a no sync push which can be done over the
internet.
FTP information is stored on the publisher not the subscriber in SQL 2000,
on SQL 7 IIRC it was also stored on the subcriber. The subscriber connects
to the publisher (using pull) to get ftp information.
Don't modify the lmhosts file, but the hosts file. LMhosts is primarily used
during netbios name resolution and if you are using TCPIP in the client
network utiltity it will use cache-hosts-DNS resolution to get the IP
address. It may then do a broadcast and consult the LMHosts files (IIRC).
You have to enable Sacremento as an enabled subscriber which won't work over
the internet. So you should be using anonymous.
Please contact me offline so we can resolve this quickly and I can get a
handle on what you are doing.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:985B1BF9-2345-4DB0-A803-FF5F4EBA7D80@.microsoft.com...
> I see your point about the FTP but I don't think I'm creating a pull
subscription. For the initial data... on the publisher I must tell the
subscribers how to get to the FTP site (IP, login, password, folder). This
is done via the Snapshot Location tab on the publisher properties form, but
this information does not show up anywhere (that I can find) on the
subscriber. The subscriber shows that it has one subscription and that it is
a push type. From the subscriber, I cannot modify or delete the
subscription - meaning that the publisher has complete control (as long as
the connection remains in tact). Maybe internally there is an invisible pull
subscription but from both sides all I see is push and the publisher
maintains complete control (which is what I want). Also, on the Subscription
Options tab of the publication properties, I have un-checked the Allow pull
subscriptions checkbox further convincing me that there are no pull
subscriptions (even while moving the initial snapshot).
> I tried using the lmhosts file instead of registering an alias with the
Client Network Utility to map the subscriber IP to a name other than it's
actual NetBIOS name. That allowed me to connect and it allowed me to push
the initial snapshot. The merge agent failed, however, giving me the
following error:
> The subscription to publication 'TestPush1' is invalid.
> (Source: Merge Replication Provider (Agent); Error number: -2147201019)
> ----
--
> The remote server is not defined as a subscription server.
> (Source: SACRAMENTO (Data source); Error number: 14010)
> ----
--
> SACRAMENTO is the name in the lmhost file that is mapped to the
subscriber's IP address. It IS defined as a subscription server. The same
publication 'TestPush1' works fine with the other subscription (where the
alias = the actual NetBIOS name).
> This is all pretty much consistent with the KB article 321822 except that
the article implies that you can't move the initial schema and data without
a pull subscription. It does say, however, that you need to use the actual
NetBIOS name although I would sure like to find a way around this
requirement.[vbcol=seagreen]
> Kyle
> "Hilary Cotter" wrote:
address of[vbcol=seagreen]
a[vbcol=seagreen]
checked[vbcol=seagreen]
ENTIRE[vbcol=seagreen]
able[vbcol=seagreen]
a[vbcol=seagreen]
me[vbcol=seagreen]
it's[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
computer[vbcol=seagreen]
works.[vbcol=seagreen]
filter[vbcol=seagreen]
via[vbcol=seagreen]
work.[vbcol=seagreen]
replication[vbcol=seagreen]
|||Kyle, thanks for sending me your scripts off line.
Yes! You are successfully doing a push over the internet, and yes, it is
possible to do a push over the internet.
The reason it is working for you is that you are connecting over DSL (I did
a tracert to the IP address you provided me with and found out you are using
pacbell DSL). The reason it is working is that the you have the netbios
ports open. On most corporate internets this is locked down as it is a
security risk.
I assumed you were on a corporate network, or behind a firewall, and that
these ports will be blocked.
So my advise to you is to use a personal firewall, and some form of internet
security software, and migrate to a pull subscription.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%237wZnlSYEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Can you script out your publication so we can see exactly what you have
> done. It is possible you did a no sync push which can be done over the
> internet.
> FTP information is stored on the publisher not the subscriber in SQL 2000,
> on SQL 7 IIRC it was also stored on the subcriber. The subscriber connects
> to the publisher (using pull) to get ftp information.
> Don't modify the lmhosts file, but the hosts file. LMhosts is primarily
used
> during netbios name resolution and if you are using TCPIP in the client
> network utiltity it will use cache-hosts-DNS resolution to get the IP
> address. It may then do a broadcast and consult the LMHosts files (IIRC).
> You have to enable Sacremento as an enabled subscriber which won't work
over
> the internet. So you should be using anonymous.
> Please contact me offline so we can resolve this quickly and I can get a
> handle on what you are doing.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:985B1BF9-2345-4DB0-A803-FF5F4EBA7D80@.microsoft.com...
> subscription. For the initial data... on the publisher I must tell the
> subscribers how to get to the FTP site (IP, login, password, folder). This
> is done via the Snapshot Location tab on the publisher properties form,
but
> this information does not show up anywhere (that I can find) on the
> subscriber. The subscriber shows that it has one subscription and that it
is
> a push type. From the subscriber, I cannot modify or delete the
> subscription - meaning that the publisher has complete control (as long as
> the connection remains in tact). Maybe internally there is an invisible
pull
> subscription but from both sides all I see is push and the publisher
> maintains complete control (which is what I want). Also, on the
Subscription
> Options tab of the publication properties, I have un-checked the Allow
pull[vbcol=seagreen]
> subscriptions checkbox further convincing me that there are no pull
> subscriptions (even while moving the initial snapshot).
> Client Network Utility to map the subscriber IP to a name other than it's
> actual NetBIOS name. That allowed me to connect and it allowed me to push
> the initial snapshot. The merge agent failed, however, giving me the
> following error:
> ----
> --
> ----
> --
> subscriber's IP address. It IS defined as a subscription server. The same
> publication 'TestPush1' works fine with the other subscription (where the
> alias = the actual NetBIOS name).
that
> the article implies that you can't move the initial schema and data
without[vbcol=seagreen]
> a pull subscription. It does say, however, that you need to use the actual
> NetBIOS name although I would sure like to find a way around this
> requirement.
> address of
wrote[vbcol=seagreen]
> a
> checked
> ENTIRE
been[vbcol=seagreen]
> able
see[vbcol=seagreen]
> a
let[vbcol=seagreen]
> me
easy,[vbcol=seagreen]
> it's
> has
> the
> computer
> works.
> filter
Agent[vbcol=seagreen]
I'd[vbcol=seagreen]
upcoming[vbcol=seagreen]
pull[vbcol=seagreen]
> via
> work.
how
> replication
>
If so, how?
If not, does anybody know why not?
Hi,
Have a look into the below article.
http://support.microsoft.com/?id=321822
FYI, I have never tried replication over internet.
Thanks
Hari
MCDBA
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:9B38B0BC-8700-46A9-A456-9432F24CFBAF@.microsoft.com...
> Is it possible to have a push subscription for merge replication over the
Internet?
> If so, how?
> If not, does anybody know why not?
|||Hari,
Thanks for the reply. Since writing my post I have become VERY familiar with that article. From what I understand, you can push over the Internet if the subscriber already has the schema. To get the initial schema, however, the subscriber must have a snap
shot agent that is configured to pull via FTP. While this is not my ideal solution, I may be able to make it work. Some of my subscribers will be using MSDE though so I don't know how well that will work.
Thanks,
Kyle
"Hari" wrote:
> Hi,
> Have a look into the below article.
> http://support.microsoft.com/?id=321822
> FYI, I have never tried replication over internet.
> --
> Thanks
> Hari
> MCDBA
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:9B38B0BC-8700-46A9-A456-9432F24CFBAF@.microsoft.com...
> Internet?
>
>
|||you can't push over the internet.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> Hari,
> Thanks for the reply. Since writing my post I have become VERY familiar
with that article. From what I understand, you can push over the Internet if
the subscriber already has the schema. To get the initial schema, however,
the subscriber must have a snapshot agent that is configured to pull via
FTP. While this is not my ideal solution, I may be able to make it work.
Some of my subscribers will be using MSDE though so I don't know how well
that will work.[vbcol=seagreen]
> Thanks,
> Kyle
>
> "Hari" wrote:
the[vbcol=seagreen]
|||Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are devoting an ENTIRE book on merge replication. THA
TS GREAT! because so far, I have not been able to find a book that deals with the subject in depth. Usually I just see a chapter on replication that just repeates the Microsoft BOL. Please let me know when it is released.
That said, I HAVE been able to push over the Internet. It's not easy, it's not obvious and it's not well documented but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push over the Interne
t with dynamic filtering:
1. The publication must be saved to an FTP site that is available to the subscribers.
2. You must make a server alias (with Client Network Utility) of the subscriber on the publisher server that MATCHES the actual NetBIOS computer name of the subscriber. It seems dumb that you have to do this but it works.
3. For a dynamic filter to work with a push subscription, you must filter on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent step of the subscription.
If I've got something wrong in my head or you have any other tips, I'd love to hear about it. Also, if I can be of any help with your upcoming book, please let me know.
Thanks,
Kyle
"Hilary Cotter" wrote:
> you can't push over the internet.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> with that article. From what I understand, you can push over the Internet if
> the subscriber already has the schema. To get the initial schema, however,
> the subscriber must have a snapshot agent that is configured to pull via
> FTP. While this is not my ideal solution, I may be able to make it work.
> Some of my subscribers will be using MSDE though so I don't know how well
> that will work.
> the
>
>
|||Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are currently working on a book devoted to merge repl
ication. Selfishly, I'm hoping you don't have good surf conditions for the next few months. Please let me know when it is released.
That said, I HAVE been able to push subscriptions over the Internet. It's not obvious or easy but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push a merge subscription over the internet
with dynamic filtering:
1. The publication must be put on an FTP site that is accessable to the subscribers.
2. The publishing server must have an alias to the subscriber that matches the actual NetBIOS computer name of the subscriber machine. This seems really dumb to me but it's the only way I could get it to work.
3. To enable dynamic filtering with a push subscription, you must filter on HOST_NAME() and you must put a -hostname [somename] parameter in the Run Agent step of the merge agent. This allows you to specify a particular subset of data to each subscriber i
ndependant of the subscriber's actual name or user (although, as I mentioned in point 2, you do have to know the subscriber's computer name).
If I've got something wrong or you have any other tips for me, please let me know. Also, if I can be of any help with your upcoming book, I'd be glad to contribute.
Thanks,
Kyle
"Hilary Cotter" wrote:
> you can't push over the internet.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> with that article. From what I understand, you can push over the Internet if
> the subscriber already has the schema. To get the initial schema, however,
> the subscriber must have a snapshot agent that is configured to pull via
> FTP. While this is not my ideal solution, I may be able to make it work.
> Some of my subscribers will be using MSDE though so I don't know how well
> that will work.
> the
>
>
|||But if FTP is in the mix you are using a pull.
You don't have to register you Netbios name of the Subscriber on the
publisher. It can be any name as long as that name matches the ip address of
your subscriber and this name is entered in your hosts file.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:39C08D3F-D3FC-47E2-BB32-1AB5C04C8919@.microsoft.com...
> Hilary,
> I clicked on your book link and was disappointed to see that you wrote a
book on transactional and snapshot replication but not merge. I then checked
out the nwsu site and was delighted to see that you are devoting an ENTIRE
book on merge replication. THATS GREAT! because so far, I have not been able
to find a book that deals with the subject in depth. Usually I just see a
chapter on replication that just repeates the Microsoft BOL. Please let me
know when it is released.
> That said, I HAVE been able to push over the Internet. It's not easy, it's
not obvious and it's not well documented but it IS possible. I'm still
working on my application but here's what I have learned so far which has
enabled me to push over the Internet with dynamic filtering:
> 1. The publication must be saved to an FTP site that is available to the
subscribers.
> 2. You must make a server alias (with Client Network Utility) of the
subscriber on the publisher server that MATCHES the actual NetBIOS computer
name of the subscriber. It seems dumb that you have to do this but it works.
> 3. For a dynamic filter to work with a push subscription, you must filter
on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent
step of the subscription.
> If I've got something wrong in my head or you have any other tips, I'd
love to hear about it. Also, if I can be of any help with your upcoming
book, please let me know.[vbcol=seagreen]
> Thanks,
> Kyle
> "Hilary Cotter" wrote:
familiar[vbcol=seagreen]
Internet if[vbcol=seagreen]
however,[vbcol=seagreen]
well[vbcol=seagreen]
over[vbcol=seagreen]
|||I see your point about the FTP but I don't think I'm creating a pull subscription. For the initial data... on the publisher I must tell the subscribers how to get to the FTP site (IP, login, password, folder). This is done via the Snapshot Location tab on
the publisher properties form, but this information does not show up anywhere (that I can find) on the subscriber. The subscriber shows that it has one subscription and that it is a push type. From the subscriber, I cannot modify or delete the subscripti
on - meaning that the publisher has complete control (as long as the connection remains in tact). Maybe internally there is an invisible pull subscription but from both sides all I see is push and the publisher maintains complete control (which is what I
want). Also, on the Subscription Options tab of the publication properties, I have un-checked the Allow pull subscriptions checkbox further convincing me that there are no pull subscriptions (even while moving the initial snapshot).
I tried using the lmhosts file instead of registering an alias with the Client Network Utility to map the subscriber IP to a name other than it's actual NetBIOS name. That allowed me to connect and it allowed me to push the initial snapshot. The merge age
nt failed, however, giving me the following error:
The subscription to publication 'TestPush1' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
-----
The remote server is not defined as a subscription server.
(Source: SACRAMENTO (Data source); Error number: 14010)
-----
SACRAMENTO is the name in the lmhost file that is mapped to the subscriber's IP address. It IS defined as a subscription server. The same publication 'TestPush1' works fine with the other subscription (where the alias = the actual NetBIOS name).
This is all pretty much consistent with the KB article 321822 except that the article implies that you can't move the initial schema and data without a pull subscription. It does say, however, that you need to use the actual NetBIOS name although I would
sure like to find a way around this requirement.
Kyle
"Hilary Cotter" wrote:
> But if FTP is in the mix you are using a pull.
> You don't have to register you Netbios name of the Subscriber on the
> publisher. It can be any name as long as that name matches the ip address of
> your subscriber and this name is entered in your hosts file.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:39C08D3F-D3FC-47E2-BB32-1AB5C04C8919@.microsoft.com...
> book on transactional and snapshot replication but not merge. I then checked
> out the nwsu site and was delighted to see that you are devoting an ENTIRE
> book on merge replication. THATS GREAT! because so far, I have not been able
> to find a book that deals with the subject in depth. Usually I just see a
> chapter on replication that just repeates the Microsoft BOL. Please let me
> know when it is released.
> not obvious and it's not well documented but it IS possible. I'm still
> working on my application but here's what I have learned so far which has
> enabled me to push over the Internet with dynamic filtering:
> subscribers.
> subscriber on the publisher server that MATCHES the actual NetBIOS computer
> name of the subscriber. It seems dumb that you have to do this but it works.
> on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent
> step of the subscription.
> love to hear about it. Also, if I can be of any help with your upcoming
> book, please let me know.
> familiar
> Internet if
> however,
> well
> over
>
>
|||Can you script out your publication so we can see exactly what you have
done. It is possible you did a no sync push which can be done over the
internet.
FTP information is stored on the publisher not the subscriber in SQL 2000,
on SQL 7 IIRC it was also stored on the subcriber. The subscriber connects
to the publisher (using pull) to get ftp information.
Don't modify the lmhosts file, but the hosts file. LMhosts is primarily used
during netbios name resolution and if you are using TCPIP in the client
network utiltity it will use cache-hosts-DNS resolution to get the IP
address. It may then do a broadcast and consult the LMHosts files (IIRC).
You have to enable Sacremento as an enabled subscriber which won't work over
the internet. So you should be using anonymous.
Please contact me offline so we can resolve this quickly and I can get a
handle on what you are doing.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:985B1BF9-2345-4DB0-A803-FF5F4EBA7D80@.microsoft.com...
> I see your point about the FTP but I don't think I'm creating a pull
subscription. For the initial data... on the publisher I must tell the
subscribers how to get to the FTP site (IP, login, password, folder). This
is done via the Snapshot Location tab on the publisher properties form, but
this information does not show up anywhere (that I can find) on the
subscriber. The subscriber shows that it has one subscription and that it is
a push type. From the subscriber, I cannot modify or delete the
subscription - meaning that the publisher has complete control (as long as
the connection remains in tact). Maybe internally there is an invisible pull
subscription but from both sides all I see is push and the publisher
maintains complete control (which is what I want). Also, on the Subscription
Options tab of the publication properties, I have un-checked the Allow pull
subscriptions checkbox further convincing me that there are no pull
subscriptions (even while moving the initial snapshot).
> I tried using the lmhosts file instead of registering an alias with the
Client Network Utility to map the subscriber IP to a name other than it's
actual NetBIOS name. That allowed me to connect and it allowed me to push
the initial snapshot. The merge agent failed, however, giving me the
following error:
> The subscription to publication 'TestPush1' is invalid.
> (Source: Merge Replication Provider (Agent); Error number: -2147201019)
> ----
--
> The remote server is not defined as a subscription server.
> (Source: SACRAMENTO (Data source); Error number: 14010)
> ----
--
> SACRAMENTO is the name in the lmhost file that is mapped to the
subscriber's IP address. It IS defined as a subscription server. The same
publication 'TestPush1' works fine with the other subscription (where the
alias = the actual NetBIOS name).
> This is all pretty much consistent with the KB article 321822 except that
the article implies that you can't move the initial schema and data without
a pull subscription. It does say, however, that you need to use the actual
NetBIOS name although I would sure like to find a way around this
requirement.[vbcol=seagreen]
> Kyle
> "Hilary Cotter" wrote:
address of[vbcol=seagreen]
a[vbcol=seagreen]
checked[vbcol=seagreen]
ENTIRE[vbcol=seagreen]
able[vbcol=seagreen]
a[vbcol=seagreen]
me[vbcol=seagreen]
it's[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
computer[vbcol=seagreen]
works.[vbcol=seagreen]
filter[vbcol=seagreen]
via[vbcol=seagreen]
work.[vbcol=seagreen]
replication[vbcol=seagreen]
|||Kyle, thanks for sending me your scripts off line.
Yes! You are successfully doing a push over the internet, and yes, it is
possible to do a push over the internet.
The reason it is working for you is that you are connecting over DSL (I did
a tracert to the IP address you provided me with and found out you are using
pacbell DSL). The reason it is working is that the you have the netbios
ports open. On most corporate internets this is locked down as it is a
security risk.
I assumed you were on a corporate network, or behind a firewall, and that
these ports will be blocked.
So my advise to you is to use a personal firewall, and some form of internet
security software, and migrate to a pull subscription.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%237wZnlSYEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Can you script out your publication so we can see exactly what you have
> done. It is possible you did a no sync push which can be done over the
> internet.
> FTP information is stored on the publisher not the subscriber in SQL 2000,
> on SQL 7 IIRC it was also stored on the subcriber. The subscriber connects
> to the publisher (using pull) to get ftp information.
> Don't modify the lmhosts file, but the hosts file. LMhosts is primarily
used
> during netbios name resolution and if you are using TCPIP in the client
> network utiltity it will use cache-hosts-DNS resolution to get the IP
> address. It may then do a broadcast and consult the LMHosts files (IIRC).
> You have to enable Sacremento as an enabled subscriber which won't work
over
> the internet. So you should be using anonymous.
> Please contact me offline so we can resolve this quickly and I can get a
> handle on what you are doing.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:985B1BF9-2345-4DB0-A803-FF5F4EBA7D80@.microsoft.com...
> subscription. For the initial data... on the publisher I must tell the
> subscribers how to get to the FTP site (IP, login, password, folder). This
> is done via the Snapshot Location tab on the publisher properties form,
but
> this information does not show up anywhere (that I can find) on the
> subscriber. The subscriber shows that it has one subscription and that it
is
> a push type. From the subscriber, I cannot modify or delete the
> subscription - meaning that the publisher has complete control (as long as
> the connection remains in tact). Maybe internally there is an invisible
pull
> subscription but from both sides all I see is push and the publisher
> maintains complete control (which is what I want). Also, on the
Subscription
> Options tab of the publication properties, I have un-checked the Allow
pull[vbcol=seagreen]
> subscriptions checkbox further convincing me that there are no pull
> subscriptions (even while moving the initial snapshot).
> Client Network Utility to map the subscriber IP to a name other than it's
> actual NetBIOS name. That allowed me to connect and it allowed me to push
> the initial snapshot. The merge agent failed, however, giving me the
> following error:
> ----
> --
> ----
> --
> subscriber's IP address. It IS defined as a subscription server. The same
> publication 'TestPush1' works fine with the other subscription (where the
> alias = the actual NetBIOS name).
that
> the article implies that you can't move the initial schema and data
without[vbcol=seagreen]
> a pull subscription. It does say, however, that you need to use the actual
> NetBIOS name although I would sure like to find a way around this
> requirement.
> address of
wrote[vbcol=seagreen]
> a
> checked
> ENTIRE
been[vbcol=seagreen]
> able
see[vbcol=seagreen]
> a
let[vbcol=seagreen]
> me
easy,[vbcol=seagreen]
> it's
> has
> the
> computer
> works.
> filter
Agent[vbcol=seagreen]
I'd[vbcol=seagreen]
upcoming[vbcol=seagreen]
pull[vbcol=seagreen]
> via
> work.
how
> replication
>
push or pull, local or remote
sql2k sp3
Transactial, 1 way replication to 1 Subscriber. I want to offload as much
processing to the Subscriber as possible. I was thinking to use a Remote
Distributor (Subscriber) with Push Subscriptions would be good. (Push for
Security/ Firewall reasons.) Is this correct thinking?
TIA, ChrisR
I normally only migrate to a remote distributor when circumstances warrant.
For instance when my publisher is under load and I start to experience
locking especially in the distribution database.
There is an added point of failure if you move to a remote distributor when
you don't really need to.
Pull is best when you have large numbers of subscribers. You do lose the
central point of administration.
You might want to think about remote activation. This will offload your
agent processing to your subscriber. Its like a push subscription but with
less overhead on the publisher, and more on the subscriber.
For firewall issues you would use pull with FTP. Push doesn't work welll
over a firewall.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:uWARPlqoEHA.1992@.TK2MSFTNGP09.phx.gbl...
> sql2k sp3
> Transactial, 1 way replication to 1 Subscriber. I want to offload as much
> processing to the Subscriber as possible. I was thinking to use a Remote
> Distributor (Subscriber) with Push Subscriptions would be good. (Push for
> Security/ Firewall reasons.) Is this correct thinking?
> TIA, ChrisR
>
|||> You might want to think about remote activation. This will offload your
> agent processing to your subscriber. Its like a push subscription but with
> less overhead on the publisher, and more on the subscriber.
I had always thought this is what a Remote Distributor would do? Is this
incorrect? Also, Push/ Pull doesnt have anything to do with where the load
is placed, right?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23mFVPNvoEHA.516@.TK2MSFTNGP09.phx.gbl...
> I normally only migrate to a remote distributor when circumstances
warrant.
> For instance when my publisher is under load and I start to experience
> locking especially in the distribution database.
> There is an added point of failure if you move to a remote distributor
when[vbcol=seagreen]
> you don't really need to.
> Pull is best when you have large numbers of subscribers. You do lose the
> central point of administration.
> You might want to think about remote activation. This will offload your
> agent processing to your subscriber. Its like a push subscription but with
> less overhead on the publisher, and more on the subscriber.
> For firewall issues you would use pull with FTP. Push doesn't work welll
> over a firewall.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:uWARPlqoEHA.1992@.TK2MSFTNGP09.phx.gbl...
much[vbcol=seagreen]
for
>
|||No, a remote distributor contains all the commands to be replicated and the
history if you are using a push subscription.
If you are using a pull subscription your remote distributor only contains
all the commands to be replicated. This history is on the subscriber. If
your remote distributor is on your subscriber, and your pull subscription
agents are there as well then the commands and history are there.
With remote activiation your agent exe will run wherever you specify -
publisher, subcsriber or distributor. So you can run a pull subscription and
have the agent run on your publisher or a push subscription and have your
agent run on your publisher.
HTH
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:%234ZdwMxoEHA.3460@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
with[vbcol=seagreen]
>
> I had always thought this is what a Remote Distributor would do? Is this
> incorrect? Also, Push/ Pull doesnt have anything to do with where the load
> is placed, right?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23mFVPNvoEHA.516@.TK2MSFTNGP09.phx.gbl...
> warrant.
> when
with[vbcol=seagreen]
> much
Remote
> for
>
|||So then whats the benifit to having a remote distributor. I always thought
if processing got really heavy on the Publisher I could just use a Remote
Distributor and that would fix it. It sounds like my whole thought process
is off?
My goals are:
To offload all the processing to the Subscriber. Keep as secure as possible.
After reading all this it sounds like I should:
Use a Local Distributor. Use Remote Activation Agent. Use Pull Subscriptions
with FTP.
Is this how you would do it for my scenario?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uQ4G7axoEHA.2340@.TK2MSFTNGP11.phx.gbl...
> No, a remote distributor contains all the commands to be replicated and
the
> history if you are using a push subscription.
> If you are using a pull subscription your remote distributor only contains
> all the commands to be replicated. This history is on the subscriber. If
> your remote distributor is on your subscriber, and your pull subscription
> agents are there as well then the commands and history are there.
> With remote activiation your agent exe will run wherever you specify -
> publisher, subcsriber or distributor. So you can run a pull subscription
and[vbcol=seagreen]
> have the agent run on your publisher or a push subscription and have your
> agent run on your publisher.
> HTH
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:%234ZdwMxoEHA.3460@.tk2msftngp13.phx.gbl...
your[vbcol=seagreen]
> with
load[vbcol=seagreen]
the[vbcol=seagreen]
your[vbcol=seagreen]
> with
welll[vbcol=seagreen]
> Remote
(Push
>
|||You are completely correct. To offload some of the processing involved in
replication use a remote distributor. The only replication overhead on the
publisher will be the log reader agent which will still run on the
publisher.
However, you will then have another point of failure. Life is simpler with a
local distributor.
When you migrate to pull you get better performance as the distribution
agent runs on the subscriber. But then you have to administer your
distribution agent on your subscriber, and consequently lose your single
central point of administration.
So its a trade off, administrative simplicity vs. the performance boost.
Same with remote activation; it is slightly more complex, but there can be
performance benefits by running your push agent on your subscriber - note
that is push agent, not pull. If the server you are running your
distribution agent on (publisher, subscriber, distributor) is under load you
may get dcom timeouts when you agent starts up.
If you chose the local distributor, and pull subscriptions with FTP you are
probably getting better performance than with local and push. You would get
better performance with remote and pull, but then there is the added
administrative burden - albeit slight.
If you want to throw remote activation into the mix where is your agent
going to run? Publisher, subscriber, distributor? For performance reasons
it should be the distributor or subscriber. If its the subscriber you should
be using a simple pull.
"ChrisR" <chris@.noemail.com> wrote in message
news:OaO7L0yoEHA.1712@.tk2msftngp13.phx.gbl...
> So then whats the benifit to having a remote distributor. I always thought
> if processing got really heavy on the Publisher I could just use a Remote
> Distributor and that would fix it. It sounds like my whole thought process
> is off?
> My goals are:
> To offload all the processing to the Subscriber. Keep as secure as
> possible.
> After reading all this it sounds like I should:
> Use a Local Distributor. Use Remote Activation Agent. Use Pull
> Subscriptions
> with FTP.
> Is this how you would do it for my scenario?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uQ4G7axoEHA.2340@.TK2MSFTNGP11.phx.gbl...
> the
> and
> your
> load
> the
> your
> welll
> (Push
>
|||How would you do it? Publisher performance is more important to me than
centralized administration. It sounds like the ultimate performance boost
would be:
Remote Distributor. Pull Subscriptions. Remote Agent.
Would you agree?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OaeV0e1oEHA.3900@.TK2MSFTNGP10.phx.gbl...
> You are completely correct. To offload some of the processing involved in
> replication use a remote distributor. The only replication overhead on the
> publisher will be the log reader agent which will still run on the
> publisher.
> However, you will then have another point of failure. Life is simpler with
a
> local distributor.
> When you migrate to pull you get better performance as the distribution
> agent runs on the subscriber. But then you have to administer your
> distribution agent on your subscriber, and consequently lose your single
> central point of administration.
> So its a trade off, administrative simplicity vs. the performance boost.
> Same with remote activation; it is slightly more complex, but there can be
> performance benefits by running your push agent on your subscriber - note
> that is push agent, not pull. If the server you are running your
> distribution agent on (publisher, subscriber, distributor) is under load
you
> may get dcom timeouts when you agent starts up.
> If you chose the local distributor, and pull subscriptions with FTP you
are
> probably getting better performance than with local and push. You would
get
> better performance with remote and pull, but then there is the added
> administrative burden - albeit slight.
> If you want to throw remote activation into the mix where is your agent
> going to run? Publisher, subscriber, distributor? For performance reasons
> it should be the distributor or subscriber. If its the subscriber you
should[vbcol=seagreen]
> be using a simple pull.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OaO7L0yoEHA.1712@.tk2msftngp13.phx.gbl...
thought[vbcol=seagreen]
Remote[vbcol=seagreen]
process[vbcol=seagreen]
If[vbcol=seagreen]
subscription[vbcol=seagreen]
subscription[vbcol=seagreen]
your[vbcol=seagreen]
but[vbcol=seagreen]
lose[vbcol=seagreen]
but
>
|||I did some testing a couple of years ago with pulling from an FTP site.
Doesnt it have an issue with filling up the C: drive? Something like making
a copy of the snapshot in a temp folder?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OaeV0e1oEHA.3900@.TK2MSFTNGP10.phx.gbl...
> You are completely correct. To offload some of the processing involved in
> replication use a remote distributor. The only replication overhead on the
> publisher will be the log reader agent which will still run on the
> publisher.
> However, you will then have another point of failure. Life is simpler with
a
> local distributor.
> When you migrate to pull you get better performance as the distribution
> agent runs on the subscriber. But then you have to administer your
> distribution agent on your subscriber, and consequently lose your single
> central point of administration.
> So its a trade off, administrative simplicity vs. the performance boost.
> Same with remote activation; it is slightly more complex, but there can be
> performance benefits by running your push agent on your subscriber - note
> that is push agent, not pull. If the server you are running your
> distribution agent on (publisher, subscriber, distributor) is under load
you
> may get dcom timeouts when you agent starts up.
> If you chose the local distributor, and pull subscriptions with FTP you
are
> probably getting better performance than with local and push. You would
get
> better performance with remote and pull, but then there is the added
> administrative burden - albeit slight.
> If you want to throw remote activation into the mix where is your agent
> going to run? Publisher, subscriber, distributor? For performance reasons
> it should be the distributor or subscriber. If its the subscriber you
should[vbcol=seagreen]
> be using a simple pull.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OaO7L0yoEHA.1712@.tk2msftngp13.phx.gbl...
thought[vbcol=seagreen]
Remote[vbcol=seagreen]
process[vbcol=seagreen]
If[vbcol=seagreen]
subscription[vbcol=seagreen]
subscription[vbcol=seagreen]
your[vbcol=seagreen]
but[vbcol=seagreen]
lose[vbcol=seagreen]
but
>
Transactial, 1 way replication to 1 Subscriber. I want to offload as much
processing to the Subscriber as possible. I was thinking to use a Remote
Distributor (Subscriber) with Push Subscriptions would be good. (Push for
Security/ Firewall reasons.) Is this correct thinking?
TIA, ChrisR
I normally only migrate to a remote distributor when circumstances warrant.
For instance when my publisher is under load and I start to experience
locking especially in the distribution database.
There is an added point of failure if you move to a remote distributor when
you don't really need to.
Pull is best when you have large numbers of subscribers. You do lose the
central point of administration.
You might want to think about remote activation. This will offload your
agent processing to your subscriber. Its like a push subscription but with
less overhead on the publisher, and more on the subscriber.
For firewall issues you would use pull with FTP. Push doesn't work welll
over a firewall.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:uWARPlqoEHA.1992@.TK2MSFTNGP09.phx.gbl...
> sql2k sp3
> Transactial, 1 way replication to 1 Subscriber. I want to offload as much
> processing to the Subscriber as possible. I was thinking to use a Remote
> Distributor (Subscriber) with Push Subscriptions would be good. (Push for
> Security/ Firewall reasons.) Is this correct thinking?
> TIA, ChrisR
>
|||> You might want to think about remote activation. This will offload your
> agent processing to your subscriber. Its like a push subscription but with
> less overhead on the publisher, and more on the subscriber.
I had always thought this is what a Remote Distributor would do? Is this
incorrect? Also, Push/ Pull doesnt have anything to do with where the load
is placed, right?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23mFVPNvoEHA.516@.TK2MSFTNGP09.phx.gbl...
> I normally only migrate to a remote distributor when circumstances
warrant.
> For instance when my publisher is under load and I start to experience
> locking especially in the distribution database.
> There is an added point of failure if you move to a remote distributor
when[vbcol=seagreen]
> you don't really need to.
> Pull is best when you have large numbers of subscribers. You do lose the
> central point of administration.
> You might want to think about remote activation. This will offload your
> agent processing to your subscriber. Its like a push subscription but with
> less overhead on the publisher, and more on the subscriber.
> For firewall issues you would use pull with FTP. Push doesn't work welll
> over a firewall.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:uWARPlqoEHA.1992@.TK2MSFTNGP09.phx.gbl...
much[vbcol=seagreen]
for
>
|||No, a remote distributor contains all the commands to be replicated and the
history if you are using a push subscription.
If you are using a pull subscription your remote distributor only contains
all the commands to be replicated. This history is on the subscriber. If
your remote distributor is on your subscriber, and your pull subscription
agents are there as well then the commands and history are there.
With remote activiation your agent exe will run wherever you specify -
publisher, subcsriber or distributor. So you can run a pull subscription and
have the agent run on your publisher or a push subscription and have your
agent run on your publisher.
HTH
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:%234ZdwMxoEHA.3460@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
with[vbcol=seagreen]
>
> I had always thought this is what a Remote Distributor would do? Is this
> incorrect? Also, Push/ Pull doesnt have anything to do with where the load
> is placed, right?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23mFVPNvoEHA.516@.TK2MSFTNGP09.phx.gbl...
> warrant.
> when
with[vbcol=seagreen]
> much
Remote
> for
>
|||So then whats the benifit to having a remote distributor. I always thought
if processing got really heavy on the Publisher I could just use a Remote
Distributor and that would fix it. It sounds like my whole thought process
is off?
My goals are:
To offload all the processing to the Subscriber. Keep as secure as possible.
After reading all this it sounds like I should:
Use a Local Distributor. Use Remote Activation Agent. Use Pull Subscriptions
with FTP.
Is this how you would do it for my scenario?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uQ4G7axoEHA.2340@.TK2MSFTNGP11.phx.gbl...
> No, a remote distributor contains all the commands to be replicated and
the
> history if you are using a push subscription.
> If you are using a pull subscription your remote distributor only contains
> all the commands to be replicated. This history is on the subscriber. If
> your remote distributor is on your subscriber, and your pull subscription
> agents are there as well then the commands and history are there.
> With remote activiation your agent exe will run wherever you specify -
> publisher, subcsriber or distributor. So you can run a pull subscription
and[vbcol=seagreen]
> have the agent run on your publisher or a push subscription and have your
> agent run on your publisher.
> HTH
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:%234ZdwMxoEHA.3460@.tk2msftngp13.phx.gbl...
your[vbcol=seagreen]
> with
load[vbcol=seagreen]
the[vbcol=seagreen]
your[vbcol=seagreen]
> with
welll[vbcol=seagreen]
> Remote
(Push
>
|||You are completely correct. To offload some of the processing involved in
replication use a remote distributor. The only replication overhead on the
publisher will be the log reader agent which will still run on the
publisher.
However, you will then have another point of failure. Life is simpler with a
local distributor.
When you migrate to pull you get better performance as the distribution
agent runs on the subscriber. But then you have to administer your
distribution agent on your subscriber, and consequently lose your single
central point of administration.
So its a trade off, administrative simplicity vs. the performance boost.
Same with remote activation; it is slightly more complex, but there can be
performance benefits by running your push agent on your subscriber - note
that is push agent, not pull. If the server you are running your
distribution agent on (publisher, subscriber, distributor) is under load you
may get dcom timeouts when you agent starts up.
If you chose the local distributor, and pull subscriptions with FTP you are
probably getting better performance than with local and push. You would get
better performance with remote and pull, but then there is the added
administrative burden - albeit slight.
If you want to throw remote activation into the mix where is your agent
going to run? Publisher, subscriber, distributor? For performance reasons
it should be the distributor or subscriber. If its the subscriber you should
be using a simple pull.
"ChrisR" <chris@.noemail.com> wrote in message
news:OaO7L0yoEHA.1712@.tk2msftngp13.phx.gbl...
> So then whats the benifit to having a remote distributor. I always thought
> if processing got really heavy on the Publisher I could just use a Remote
> Distributor and that would fix it. It sounds like my whole thought process
> is off?
> My goals are:
> To offload all the processing to the Subscriber. Keep as secure as
> possible.
> After reading all this it sounds like I should:
> Use a Local Distributor. Use Remote Activation Agent. Use Pull
> Subscriptions
> with FTP.
> Is this how you would do it for my scenario?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uQ4G7axoEHA.2340@.TK2MSFTNGP11.phx.gbl...
> the
> and
> your
> load
> the
> your
> welll
> (Push
>
|||How would you do it? Publisher performance is more important to me than
centralized administration. It sounds like the ultimate performance boost
would be:
Remote Distributor. Pull Subscriptions. Remote Agent.
Would you agree?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OaeV0e1oEHA.3900@.TK2MSFTNGP10.phx.gbl...
> You are completely correct. To offload some of the processing involved in
> replication use a remote distributor. The only replication overhead on the
> publisher will be the log reader agent which will still run on the
> publisher.
> However, you will then have another point of failure. Life is simpler with
a
> local distributor.
> When you migrate to pull you get better performance as the distribution
> agent runs on the subscriber. But then you have to administer your
> distribution agent on your subscriber, and consequently lose your single
> central point of administration.
> So its a trade off, administrative simplicity vs. the performance boost.
> Same with remote activation; it is slightly more complex, but there can be
> performance benefits by running your push agent on your subscriber - note
> that is push agent, not pull. If the server you are running your
> distribution agent on (publisher, subscriber, distributor) is under load
you
> may get dcom timeouts when you agent starts up.
> If you chose the local distributor, and pull subscriptions with FTP you
are
> probably getting better performance than with local and push. You would
get
> better performance with remote and pull, but then there is the added
> administrative burden - albeit slight.
> If you want to throw remote activation into the mix where is your agent
> going to run? Publisher, subscriber, distributor? For performance reasons
> it should be the distributor or subscriber. If its the subscriber you
should[vbcol=seagreen]
> be using a simple pull.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OaO7L0yoEHA.1712@.tk2msftngp13.phx.gbl...
thought[vbcol=seagreen]
Remote[vbcol=seagreen]
process[vbcol=seagreen]
If[vbcol=seagreen]
subscription[vbcol=seagreen]
subscription[vbcol=seagreen]
your[vbcol=seagreen]
but[vbcol=seagreen]
lose[vbcol=seagreen]
but
>
|||I did some testing a couple of years ago with pulling from an FTP site.
Doesnt it have an issue with filling up the C: drive? Something like making
a copy of the snapshot in a temp folder?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OaeV0e1oEHA.3900@.TK2MSFTNGP10.phx.gbl...
> You are completely correct. To offload some of the processing involved in
> replication use a remote distributor. The only replication overhead on the
> publisher will be the log reader agent which will still run on the
> publisher.
> However, you will then have another point of failure. Life is simpler with
a
> local distributor.
> When you migrate to pull you get better performance as the distribution
> agent runs on the subscriber. But then you have to administer your
> distribution agent on your subscriber, and consequently lose your single
> central point of administration.
> So its a trade off, administrative simplicity vs. the performance boost.
> Same with remote activation; it is slightly more complex, but there can be
> performance benefits by running your push agent on your subscriber - note
> that is push agent, not pull. If the server you are running your
> distribution agent on (publisher, subscriber, distributor) is under load
you
> may get dcom timeouts when you agent starts up.
> If you chose the local distributor, and pull subscriptions with FTP you
are
> probably getting better performance than with local and push. You would
get
> better performance with remote and pull, but then there is the added
> administrative burden - albeit slight.
> If you want to throw remote activation into the mix where is your agent
> going to run? Publisher, subscriber, distributor? For performance reasons
> it should be the distributor or subscriber. If its the subscriber you
should[vbcol=seagreen]
> be using a simple pull.
>
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OaO7L0yoEHA.1712@.tk2msftngp13.phx.gbl...
thought[vbcol=seagreen]
Remote[vbcol=seagreen]
process[vbcol=seagreen]
If[vbcol=seagreen]
subscription[vbcol=seagreen]
subscription[vbcol=seagreen]
your[vbcol=seagreen]
but[vbcol=seagreen]
lose[vbcol=seagreen]
but
>
Push or Pull replication
What is better to use, Push or Pull Replication? What are the advantages and disadvantages?
It really depends on your environment.
Check out this article,
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
Here is some advantage using pull subscription:
Using Pull Subscriptions
The Distribution Agent runs on the Distributor for push subscriptions and on Subscribers for pull or anonymous subscriptions. Using pull or anonymous subscriptions can increase performance by moving Distribution Agent processing from the Distributor to Subscribers.
Anonymous subscriptions, which are especially useful for Internet applications, do not require information about the Subscriber to be stored in the distribution database at the Distributor for transactional replication. Not having to maintain information on Subscribers using anonymous subscriptions reduces the resource demands on the Publisher and Distributor.
Anonymous subscriptions are a special category of pull subscriptions. In regular pull subscriptions, the Distribution Agent runs at the Subscriber (thereby reducing the resource demands on the Distributor), but it still stores information at the Publisher.
However, since pull subscription is managed on the subscriber, so if you have many subscribers with different sync schedule, there maybe more administrative overhead.
Gary
sqlPush or Pull for Replication-Based failover
I am trying to devise a setup where I can use Replication to maintain a
failover server in case of a critical failure on the primary DB server. I
plan on using transactional replication and immeadiate updating for minimal
latency, but I have a couple of questions about the right setup:
1. Push or pull? Push would allow for 2 phase commit, which seems
desirable. On the other hand, when system 1 fails, don't you need to "break
the link" between the subscriber and publisher (i.e. run sp_dropsubscription)
in order to ensure proper operation?
2. We have some identity columns. I am planning on marking them as NOT FOR
REPLICATION and then in a failover scenario, run DBCC CHECKIDENT('my
table',RESEED,<looked up max from table>) to reseed the identity on the
secondary. Is this an advisable strategy?
I have been looking at other solutions in this area which involve modifying
some of the generated procs and I would like to avoid that so that we can use
as much out of the box as possible (trying not to pretend I can engineer
better than MS
).
Thanks,
Tom
Push or pull doesn't really relate to 2PC - the setting really controls who
initiates the distributor->subscriber traffic. If the subscriber fails, the
2PC won't matter because it is in the direction subscriber->distributor. The
other way round and you'll have to remove the subscription.
For the identities, the easiest solution is to set up immediateupdating with
queued updating failover and have a large seed set up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Could you clarify a bit? does subscriber->distributor imply pull whereas
distributor ->subscriber implies push? I am trying to understand when I
would have to remove the subscription.
Thanks,
Tom
"Paul Ibison" wrote:
> Push or pull doesn't really relate to 2PC - the setting really controls who
> initiates the distributor->subscriber traffic. If the subscriber fails, the
> 2PC won't matter because it is in the direction subscriber->distributor. The
> other way round and you'll have to remove the subscription.
> For the identities, the easiest solution is to set up immediateupdating with
> queued updating failover and have a large seed set up.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||The directions I indicated refer to data flow, eg subscriber->distributor in
your case is 2PC, whereas distributor ->subscriber is the PUSH/PULL running
of the distribution agent. I think you're mixing PUSH/PULL with the flow of
data, while the 2 are not really related in that way - it's more which
computer is going to do the synchronization work.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
failover server in case of a critical failure on the primary DB server. I
plan on using transactional replication and immeadiate updating for minimal
latency, but I have a couple of questions about the right setup:
1. Push or pull? Push would allow for 2 phase commit, which seems
desirable. On the other hand, when system 1 fails, don't you need to "break
the link" between the subscriber and publisher (i.e. run sp_dropsubscription)
in order to ensure proper operation?
2. We have some identity columns. I am planning on marking them as NOT FOR
REPLICATION and then in a failover scenario, run DBCC CHECKIDENT('my
table',RESEED,<looked up max from table>) to reseed the identity on the
secondary. Is this an advisable strategy?
I have been looking at other solutions in this area which involve modifying
some of the generated procs and I would like to avoid that so that we can use
as much out of the box as possible (trying not to pretend I can engineer
better than MS
).Thanks,
Tom
Push or pull doesn't really relate to 2PC - the setting really controls who
initiates the distributor->subscriber traffic. If the subscriber fails, the
2PC won't matter because it is in the direction subscriber->distributor. The
other way round and you'll have to remove the subscription.
For the identities, the easiest solution is to set up immediateupdating with
queued updating failover and have a large seed set up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Could you clarify a bit? does subscriber->distributor imply pull whereas
distributor ->subscriber implies push? I am trying to understand when I
would have to remove the subscription.
Thanks,
Tom
"Paul Ibison" wrote:
> Push or pull doesn't really relate to 2PC - the setting really controls who
> initiates the distributor->subscriber traffic. If the subscriber fails, the
> 2PC won't matter because it is in the direction subscriber->distributor. The
> other way round and you'll have to remove the subscription.
> For the identities, the easiest solution is to set up immediateupdating with
> queued updating failover and have a large seed set up.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||The directions I indicated refer to data flow, eg subscriber->distributor in
your case is 2PC, whereas distributor ->subscriber is the PUSH/PULL running
of the distribution agent. I think you're mixing PUSH/PULL with the flow of
data, while the 2 are not really related in that way - it's more which
computer is going to do the synchronization work.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Push Merge snapshot fails with Data is Null message.
Hi,
I'm converting a replication script from SQL 2000 to SQL 2005.
I am getting an error with push merge with no way to figure out what is wrong.
I've configured replication on a single XP server in SQL 2005 RTM version.
I have a push merge set up between A and B and between B and C. All 3 databases are 9.0 compatibility.
The snapshot and merge jobs for the A to B run fine with no errors, and merge replicates ok.
The snapshot for B to C fails with this message:
Message
2006-03-09 17:30:35.94
2006-03-09 17:30:35.94 -BcpBatchSize 100000
2006-03-09 17:30:35.94 -HistoryVerboseLevel 2
2006-03-09 17:30:35.94 -LoginTimeout 15
2006-03-09 17:30:35.94 -QueryTimeout 1800
2006-03-09 17:30:35.94
2006-03-09 17:30:35.95 Connecting to Publisher 'MyInstance'
2006-03-09 17:30:35.97 Publisher database compatibility level is set to 90.
2006-03-09 17:30:35.97 Retrieving publication and article information from the publisher database 'MyInstance.MyDB'
2006-03-09 17:30:36.22 [0%] The replication agent had encountered an exception.
2006-03-09 17:30:36.22 Source: Replication
2006-03-09 17:30:36.22 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-09 17:30:36.22 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-09 17:30:36.22 Message Code: 52006
2006-03-09 17:30:36.22
Love that exception message: "Data is Null" - very helpful to someone who is clairvoyant perhaps.
I checked the snapshot bcp files. The tables being merged all have data.
A sample add article command is:
exec sp_addmergearticle @.publication = N'MyMerge', @.article = N'Phone', @.processing_order = 4, @.source_owner = N'dbo', @.source_object = N'Phone', @.type = N'table', @.description = null, @.column_tracking = N'true', @.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option = 0x000000004C42CDDF, @.article_resolver = null, @.subset_filterclause = null, @.vertical_partition = N'false', @.destination_owner = N'dbo', @.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.identityrangemanagementoption = N'none' ,@.force_invalidate_snapshot = 1,@.force_reinit_subscription = 1
If you have any ideas on how to fix this, I'd be most grateful. As it is after 6pm I probably won't read this again until morning. Thanks for any suggestions.
Can you get the full stack trace from MSrepl_errors in the distribution database and post it here please? Thanks.
-Raymond
|||Here you go:
Message: Data is Null. This method or property cannot be called on Null values.
Command Text: sp_MSestimatemergesnapshotworkload
Parameters: @.publication = StandardMerge Stack:
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.InitializeProgressTracker()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run()
Source: System.Data Target Site: Int32 get_Int32()
Message: Data is Null. This method or property cannot be called on Null values. Stack:
at System.Data.SqlClient.SqlBuffer.get_Int32()
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at Microsoft.SqlServer.Replication.Snapshot.ProgressTracker..ctor(SqlDataReader dataReader, SnapshotGenerationAgent snapshotGenerationAgent)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.CreateProgressTracker(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
This looks like a bug, can you execute sp_MSestimatemergesnapshotworkload @.publication = 'StandardMerge' at the publisher database and post any rows with a null 'taskid' or 'taskload' value here. Thanks.
-Raymond
|||Here they are. I've renamed the tables from their real names.
By the way, for the same task id (4), for the A to B merge, these are the values (in case this is useful for comparison).
Can you try the following query at the publisher database and see if it returns any rows?
select rowcnt from sysindexes where id = object_id(N'Table1') and indid in (0,1)
If the above query doesn't return any rows then this is basically the cause of the problem and it would be great if you can retry the query without the "and indid in (0,1)" part and report back whether there are any rows returned in that case. And if the publisher connection of the snapshot agent is not db_owner\sysadmin, you may want to temporarily change that and see if the problem goes away.
-Raymond
|||The result from the original query is rowcnt = 195.
|||Also, this query and the prior queries were run using the login of the original script.|||
More info - in case it is useful
The parms passed for the merge publications:
For the AtoB merge (which works)
exec sp_addmergepublication @.publication = N'AtoB', @.description = N'Corporate Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false', @.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'AtoB',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.MyName
exec sp_addmergesubscription @.publication = N'AtoB', @.subscriber = @.MyInstance, @.subscriber_db = N'SiteMaster', @.subscription_type = N'push', @.subscriber_type = N'global', @.subscription_priority = 75.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.Myname
For the BtoC merge (which doesn't)
exec sp_addmergepublication @.publication = N'BtoC', @.description = N'Facility Master Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false',@.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'BtoC',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.Myname
exec sp_addmergesubscription @.publication = N'BtoC', @.subscriber = @.MyInstance, @.subscriber_db = @.MyFacility, @.subscription_type = N'push', @.subscriber_type = N'local', @.subscription_priority = 0.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.MyName
|||This looks really weird, can you run sp_helpmergearticle @.publication =N'BtoC' at B and report back whether the source_object and source_owner columns are NULLs? Thanks much.
-Raymond
|||Raymond,
I get nothing back from that at all. When I run it on the AtoB pub,run at A, the result looks normal. But nothing at all comes back for BtoC publication, run at B.
|||This is interesting:
When I run EXEC dbo.sp_helpmergepublication at A, I get priority = 100.
When I run the same thing at B, I also get priority = 100.
You can see in my sp_addmergesubscription for A, I had set priority to 75.0 and that for B I'd set it to 0.0 so SQL is ignoring those parms I guess.
|||Ok, looks like your publication\article metadata is very messed up at B. To confirm my suspicion, can you try the following query at B:
select name, pubid, objid, object_name(objid) from dbo.sysmergeextendedarticlesview
I am interested to know if any values in the last column are null or not. If there are any null values, then your article meta-data is seriously corrupted. You can, of course, recover from this particular problem by recreating the publication at B but given the kind of problems you have experienced in the past, I strongly suspect some other background operations at B is what is causing the problems in the first place. Here are a couple of possibilities that I can think of:
1) Someone tried to renamed the published table at B
2) Someone tried to drop and recreate the published table at B and use sp_MSunmarkreplino to allow the operation to occur.
3) The merge agent has a problem updating the object ids of published tables at B when a snapshot is delivered from A to B.
4) You have included sysmergearticles and\or sysmergepublications as part of the publication from A to B.
HTH
-Raymond
|||I ran it (ordered by 2,1) and all the articles for B publication have NULL in the last column.
As for the possibilities, nobody but me has done anything to this data.
1) haven't done
2) I ran that once a week ago when I was first putting this stuff together and trying to clean stuff up with another problem I have with transactional replication getting a 3724 error ("Cannot drop the table 'dbo.Mytablename' because it is being used for replication.) Would that still be messing me up after many builds since then?
3) At the moment, I believe everything is running under the SQL Agent Svc login.
4) Those are not in my 13 explicitly named sp_addmergearticle articles.
Also, per priority difference mentioned earlier. I ran sp_helpmergesubscription and the priorities were as I expected. So why are the sp_helpmergepublication priorities 100?
Push merge replication fails - problem connecting to subscriber
I am trying to replicate a SQL 2000 database from one branch office to
another. The replication monitor seems to stall at Connecting to
'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
have left it running for a day and a half to see if it would sort itself out
and it hasn't.
Replication seemed to work fine while I was making changes at the publisher
and replicating them down to subscriber. As soon as subscriber made changes,
this happened. How do I start to troubleshoot?
Thanks
Brandon
Pasting my output debugging file
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SERVER-ildm-ildm-CALGSERV-34
Percent Complete: 0
Connecting to Distributor 'SERVER'
Connecting to Distributor 'SERVER.'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.: {call sp_MSgetversion }
[2/7/2005 11:08:00 AM]SERVER.: {call sp_helpdistpublisher (N'SERVER') }
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'SERVER')
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'CALGSERV')
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_merge_agentid (0,
N'ildm', N'ildm', 2, N'ILDM')}
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_profile (34, 4,
N'')}
Percent Complete: 0
Connecting to Publisher 'SERVER.ildm'
Initializing
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.ildm: set nocount on declare @.dbname sysname
select @.dbname = db_name() declare @.collation nvarchar(255) select @.collation
= convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select
collationproperty(@.collation, N'CODEPAGE') as 'CodePage',
collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Percent Complete: 1
Connecting to Publisher 'SERVER.ildm'
Connecting to Publisher 'SERVER'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]SERVER.ildm: {call sp_MSgetversion }
[2/7/2005 11:08:01 AM]SERVER.distribution: {call sp_MShelp_subscriber_info
(N'SERVER', N'CALGSERV')}
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'CALGSERV'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'ildm'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
Uploading data changes to the Publisher
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]SERVER.ildm: {call sp_MSgetversion }
|||What profile are you running?
Can you right click on your merge agent, select agent properties, Steps, and
then Run Agent. Click on the Edit button. Click in the Commands box, and
press CTRL A. Then Press CTRL C
Then paste the commands here and post everything here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pUmkInhEd" <pUmkInhEd@.discussions.microsoft.com> wrote in message
news:388D911E-2BB3-4A02-AC22-F1129C02C2BC@.microsoft.com...
> I am trying to replicate a SQL 2000 database from one branch office to
> another. The replication monitor seems to stall at Connecting to
> 'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
> have left it running for a day and a half to see if it would sort itself
out
> and it hasn't.
> Replication seemed to work fine while I was making changes at the
publisher
> and replicating them down to subscriber. As soon as subscriber made
changes,
> this happened. How do I start to troubleshoot?
> Thanks
> Brandon
another. The replication monitor seems to stall at Connecting to
'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
have left it running for a day and a half to see if it would sort itself out
and it hasn't.
Replication seemed to work fine while I was making changes at the publisher
and replicating them down to subscriber. As soon as subscriber made changes,
this happened. How do I start to troubleshoot?
Thanks
Brandon
Pasting my output debugging file
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SERVER-ildm-ildm-CALGSERV-34
Percent Complete: 0
Connecting to Distributor 'SERVER'
Connecting to Distributor 'SERVER.'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.: {call sp_MSgetversion }
[2/7/2005 11:08:00 AM]SERVER.: {call sp_helpdistpublisher (N'SERVER') }
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'SERVER')
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'CALGSERV')
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_merge_agentid (0,
N'ildm', N'ildm', 2, N'ILDM')}
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_profile (34, 4,
N'')}
Percent Complete: 0
Connecting to Publisher 'SERVER.ildm'
Initializing
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.ildm: set nocount on declare @.dbname sysname
select @.dbname = db_name() declare @.collation nvarchar(255) select @.collation
= convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select
collationproperty(@.collation, N'CODEPAGE') as 'CodePage',
collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Percent Complete: 1
Connecting to Publisher 'SERVER.ildm'
Connecting to Publisher 'SERVER'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]SERVER.ildm: {call sp_MSgetversion }
[2/7/2005 11:08:01 AM]SERVER.distribution: {call sp_MShelp_subscriber_info
(N'SERVER', N'CALGSERV')}
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'CALGSERV'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'ildm'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
Uploading data changes to the Publisher
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]SERVER.ildm: {call sp_MSgetversion }
|||What profile are you running?
Can you right click on your merge agent, select agent properties, Steps, and
then Run Agent. Click on the Edit button. Click in the Commands box, and
press CTRL A. Then Press CTRL C
Then paste the commands here and post everything here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pUmkInhEd" <pUmkInhEd@.discussions.microsoft.com> wrote in message
news:388D911E-2BB3-4A02-AC22-F1129C02C2BC@.microsoft.com...
> I am trying to replicate a SQL 2000 database from one branch office to
> another. The replication monitor seems to stall at Connecting to
> 'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
> have left it running for a day and a half to see if it would sort itself
out
> and it hasn't.
> Replication seemed to work fine while I was making changes at the
publisher
> and replicating them down to subscriber. As soon as subscriber made
changes,
> this happened. How do I start to troubleshoot?
> Thanks
> Brandon
Subscribe to:
Posts (Atom)