I am using SQL2k with sp4 using push transactional replication.
When I select 'configure publishing, subscribers and Distribution' from the
SQL EM's tool menu\replication, under the distributor tab, there is the
administrator link password, what do they use for password? When I set up
replication for the first time, it doesn't ask me for it so it is unknown to
me. If I change the password, will it affect anything? I also noticed that
during replication setup, it also creates a SQL login for Distribution_Admin
and I also don't know what password it use. Any ideas for these two
passwords? Just curious, why do they set up the password behind the scene?
wingman
Wing
If you didn't set it (remote distributor) this means you have a local
distributor, and it is randomly generated. If you want to change it, you can
use sp_changedistributor_password.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||No, I didn't set it. I assume local distributor means it locates in the
same server as the publisher server.
The reason I ask for the password is that I tried to use the 'generating SQL
script' feature in replication to script the setup. But the script purposely
leaves out the passwrod for security reason so in order for me to run the
script, I need to know the password. So should I run the
sp_changedistributor_password and put the new password in the script to make
it work?
Or is there another way to script a replication setup?
Wing
"Paul Ibison" wrote:
> If you didn't set it (remote distributor) this means you have a local
> distributor, and it is randomly generated. If you want to change it, you can
> use sp_changedistributor_password.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||The 'old' password doesn't really need to be retained, and sp_adddistributor
can be fed any value you want for the administrative link password.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label publishing. Show all posts
Showing posts with label publishing. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
Distribution agent action messages in session details
We are using SQL 2K std with SP4 and push subscription. We have situation
between our publishing SQL server and subcribing SQL server.
Here is what I see when I double click an action message (listed as Timeout
Expired) in the listing of Distribution Agent history.
3:24 pm Initializing
3:29 pm The process is running and is waiting for a response from one of
the backend connections.
3:29 pm Timeout expired
The sequence of action took exactly five minutes. And the timeout happened
again at 3:30 pm and last until 3:35 pm. Then at 3:36, it started working
and delivered the transactions.
Can someone tell me what the 'backend connection' the distribution agent is
waiting for?
Also, during this problem period, both publishing and subscribing SQL
servers all of a sudden became activity dead meaning there is no IO and no
SQL internal activity (my perf monitor showed almost a blank screen except
cpu time), the publishing SQL server's cpu time is very minimal but the
subscribing SQL server's cpu is 60% peak and no IO. I checked the proces of
the CPU in Em and it was for replication but it had long wait time. It
looked all a sudden nobody can connect to the SQL servers. In fact, we
confirmed that users can't connect to the web application that use SQL
servers. I don't see any networking error shown in the event log in both
servers. I am assuming there is a 'disconnect' or something is 'hanging', I
like to know if the 'disconnect or hanging' caused the distribution agent to
time out or the distrbitution timeout problem caused the SQL server system to
become 'disconnect or hang'?
wingman
The replication subsystem issues commands and waits for responses. If it
doesn't get a response in a predetermined amount of time it gives the
waiting for a response from one of backend connections.
I can't explain why no-one could connect - perhaps the database or tlog was
auto-growing at that time.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:76F91F8B-925F-4152-9C2D-ACB9EA89FCD5@.microsoft.com...
> We are using SQL 2K std with SP4 and push subscription. We have situation
> between our publishing SQL server and subcribing SQL server.
> Here is what I see when I double click an action message (listed as
> Timeout
> Expired) in the listing of Distribution Agent history.
> 3:24 pm Initializing
> 3:29 pm The process is running and is waiting for a response from one of
> the backend connections.
> 3:29 pm Timeout expired
> The sequence of action took exactly five minutes. And the timeout
> happened
> again at 3:30 pm and last until 3:35 pm. Then at 3:36, it started working
> and delivered the transactions.
> Can someone tell me what the 'backend connection' the distribution agent
> is
> waiting for?
> Also, during this problem period, both publishing and subscribing SQL
> servers all of a sudden became activity dead meaning there is no IO and no
> SQL internal activity (my perf monitor showed almost a blank screen except
> cpu time), the publishing SQL server's cpu time is very minimal but the
> subscribing SQL server's cpu is 60% peak and no IO. I checked the proces
> of
> the CPU in Em and it was for replication but it had long wait time. It
> looked all a sudden nobody can connect to the SQL servers. In fact, we
> confirmed that users can't connect to the web application that use SQL
> servers. I don't see any networking error shown in the event log in both
> servers. I am assuming there is a 'disconnect' or something is 'hanging',
> I
> like to know if the 'disconnect or hanging' caused the distribution agent
> to
> time out or the distrbitution timeout problem caused the SQL server system
> to
> become 'disconnect or hang'?
> wingman
>
>
|||Is it a good idea to increase the query timeout from 300 seconds (default) to
maybe 600 seconds in Distribution Agent profile to accomendate any potential
long running process?
Wingman
"Hilary Cotter" wrote:
> The replication subsystem issues commands and waits for responses. If it
> doesn't get a response in a predetermined amount of time it gives the
> waiting for a response from one of backend connections.
> I can't explain why no-one could connect - perhaps the database or tlog was
> auto-growing at that time.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:76F91F8B-925F-4152-9C2D-ACB9EA89FCD5@.microsoft.com...
>
>
|||QueryTimeout is more often used in merge replication than in transactional
replication. I normally set the inactivity level to something higher, but
this could mask other problems.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:649BE7BF-C83E-44DC-8B8A-BD5A498510CB@.microsoft.com...[vbcol=seagreen]
> Is it a good idea to increase the query timeout from 300 seconds (default)
> to
> maybe 600 seconds in Distribution Agent profile to accomendate any
> potential
> long running process?
> Wingman
>
> "Hilary Cotter" wrote:
between our publishing SQL server and subcribing SQL server.
Here is what I see when I double click an action message (listed as Timeout
Expired) in the listing of Distribution Agent history.
3:24 pm Initializing
3:29 pm The process is running and is waiting for a response from one of
the backend connections.
3:29 pm Timeout expired
The sequence of action took exactly five minutes. And the timeout happened
again at 3:30 pm and last until 3:35 pm. Then at 3:36, it started working
and delivered the transactions.
Can someone tell me what the 'backend connection' the distribution agent is
waiting for?
Also, during this problem period, both publishing and subscribing SQL
servers all of a sudden became activity dead meaning there is no IO and no
SQL internal activity (my perf monitor showed almost a blank screen except
cpu time), the publishing SQL server's cpu time is very minimal but the
subscribing SQL server's cpu is 60% peak and no IO. I checked the proces of
the CPU in Em and it was for replication but it had long wait time. It
looked all a sudden nobody can connect to the SQL servers. In fact, we
confirmed that users can't connect to the web application that use SQL
servers. I don't see any networking error shown in the event log in both
servers. I am assuming there is a 'disconnect' or something is 'hanging', I
like to know if the 'disconnect or hanging' caused the distribution agent to
time out or the distrbitution timeout problem caused the SQL server system to
become 'disconnect or hang'?
wingman
The replication subsystem issues commands and waits for responses. If it
doesn't get a response in a predetermined amount of time it gives the
waiting for a response from one of backend connections.
I can't explain why no-one could connect - perhaps the database or tlog was
auto-growing at that time.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:76F91F8B-925F-4152-9C2D-ACB9EA89FCD5@.microsoft.com...
> We are using SQL 2K std with SP4 and push subscription. We have situation
> between our publishing SQL server and subcribing SQL server.
> Here is what I see when I double click an action message (listed as
> Timeout
> Expired) in the listing of Distribution Agent history.
> 3:24 pm Initializing
> 3:29 pm The process is running and is waiting for a response from one of
> the backend connections.
> 3:29 pm Timeout expired
> The sequence of action took exactly five minutes. And the timeout
> happened
> again at 3:30 pm and last until 3:35 pm. Then at 3:36, it started working
> and delivered the transactions.
> Can someone tell me what the 'backend connection' the distribution agent
> is
> waiting for?
> Also, during this problem period, both publishing and subscribing SQL
> servers all of a sudden became activity dead meaning there is no IO and no
> SQL internal activity (my perf monitor showed almost a blank screen except
> cpu time), the publishing SQL server's cpu time is very minimal but the
> subscribing SQL server's cpu is 60% peak and no IO. I checked the proces
> of
> the CPU in Em and it was for replication but it had long wait time. It
> looked all a sudden nobody can connect to the SQL servers. In fact, we
> confirmed that users can't connect to the web application that use SQL
> servers. I don't see any networking error shown in the event log in both
> servers. I am assuming there is a 'disconnect' or something is 'hanging',
> I
> like to know if the 'disconnect or hanging' caused the distribution agent
> to
> time out or the distrbitution timeout problem caused the SQL server system
> to
> become 'disconnect or hang'?
> wingman
>
>
|||Is it a good idea to increase the query timeout from 300 seconds (default) to
maybe 600 seconds in Distribution Agent profile to accomendate any potential
long running process?
Wingman
"Hilary Cotter" wrote:
> The replication subsystem issues commands and waits for responses. If it
> doesn't get a response in a predetermined amount of time it gives the
> waiting for a response from one of backend connections.
> I can't explain why no-one could connect - perhaps the database or tlog was
> auto-growing at that time.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:76F91F8B-925F-4152-9C2D-ACB9EA89FCD5@.microsoft.com...
>
>
|||QueryTimeout is more often used in merge replication than in transactional
replication. I normally set the inactivity level to something higher, but
this could mask other problems.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:649BE7BF-C83E-44DC-8B8A-BD5A498510CB@.microsoft.com...[vbcol=seagreen]
> Is it a good idea to increase the query timeout from 300 seconds (default)
> to
> maybe 600 seconds in Distribution Agent profile to accomendate any
> potential
> long running process?
> Wingman
>
> "Hilary Cotter" wrote:
Subscribe to:
Posts (Atom)