The distribution server for my publisher is a live server, and the publisher is a test server that I wish to make it's own distributor. Whenever I try to select it as it's own distributor, I get an error that "distributor_admin" is not a local user. How
do I go about fixing this?
Thanks for your help,
Stephen
Where is your Distributor? Is it on the Publisher?
Is the Subscriber in the same domain as the Publisher?
How did you configure your Subscriber? Can you go to
Tools-Replication-Configure Publishers, Subscribers, Distributors, click on
the Subscribers tab, and click on the browse button (the three ellipses) to
the right of your server name?
Make sure you are using Impersonate the SQL Server Agent account on the
Publisher if you are in the same domain.
"Stephen A." <stephen.accetta@.honeywell.com> wrote in message
news:B6D49A36-D4D7-4D51-85B5-0BDFEA948056@.microsoft.com...
> The distribution server for my publisher is a live server, and the
publisher is a test server that I wish to make it's own distributor.
Whenever I try to select it as it's own distributor, I get an error that
"distributor_admin" is not a local user. How do I go about fixing this?
> Thanks for your help,
> Stephen
|||>>Where is your Distributor? Is it on the Publisher?
No. The distributor is machine A, the publisher is machine B, I wish to
make machine B it's own distributor
Same segment, but the subscriber (Machine C) is not on any domain, I
registered it by IP address. I'm not having any problems with the
subscriber, merely with making the publisher it's own distributor.
click on
ellipses) to
I'll check, but I think so.
the
I already set up a replication account, I merely wish to make the
publisher it's own distributor.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The solution is easy!!! Register servers to SQL by using their names
or aliasses. Do not use ip numbers.
Showing posts with label distributor. Show all posts
Showing posts with label distributor. Show all posts
Wednesday, March 7, 2012
distributor_admin Issue....But different than normal
I am having the common "distributor_admin does not exist" error when
trying to set up a machine as its own distributor. (First time after
setting up about 200 distributors). I get this whether I'm doing it
remotely, or on the machine itself.
The wrinkle is that the login "distributor_admin" does not exist at all
on the potential publisher (who, again, is going to be its own
distributor).
Do I need to reinstall MS-SQL to get that login created? Or can I do
it by hand somehow?
Or am I barking up the wrong tree?
Thank you!
-Joseph
What happens when you do a select @.@.servername?
Does it match what you get when you issue a xp_cmdshell 'hostname'?
if not you need to do the following
sp_dropserver @.server='oldServername'
sp_addserver @.server='NewServerName', @.local='LOCAL'
Then restart SQL Server.
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
"Joseph" <josephsheppard@.gmail.com> wrote in message
news:1168894392.883101.309200@.v45g2000cwv.googlegr oups.com...
>I am having the common "distributor_admin does not exist" error when
> trying to set up a machine as its own distributor. (First time after
> setting up about 200 distributors). I get this whether I'm doing it
> remotely, or on the machine itself.
> The wrinkle is that the login "distributor_admin" does not exist at all
> on the potential publisher (who, again, is going to be its own
> distributor).
> Do I need to reinstall MS-SQL to get that login created? Or can I do
> it by hand somehow?
> Or am I barking up the wrong tree?
> Thank you!
> -Joseph
>
|||Thanks, Hilary...There certainly was a difference...
I followed your instructions, and I'm going to take another stab at
setting distribution up.
You are the man! I'm going to have my boss order your book...
-Joseph
Hilary Cotter wrote:[vbcol=seagreen]
> What happens when you do a select @.@.servername?
> Does it match what you get when you issue a xp_cmdshell 'hostname'?
> if not you need to do the following
> sp_dropserver @.server='oldServername'
> sp_addserver @.server='NewServerName', @.local='LOCAL'
> Then restart SQL Server.
> --
> 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
>
> "Joseph" <josephsheppard@.gmail.com> wrote in message
> news:1168894392.883101.309200@.v45g2000cwv.googlegr oups.com...
trying to set up a machine as its own distributor. (First time after
setting up about 200 distributors). I get this whether I'm doing it
remotely, or on the machine itself.
The wrinkle is that the login "distributor_admin" does not exist at all
on the potential publisher (who, again, is going to be its own
distributor).
Do I need to reinstall MS-SQL to get that login created? Or can I do
it by hand somehow?
Or am I barking up the wrong tree?
Thank you!
-Joseph
What happens when you do a select @.@.servername?
Does it match what you get when you issue a xp_cmdshell 'hostname'?
if not you need to do the following
sp_dropserver @.server='oldServername'
sp_addserver @.server='NewServerName', @.local='LOCAL'
Then restart SQL Server.
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
"Joseph" <josephsheppard@.gmail.com> wrote in message
news:1168894392.883101.309200@.v45g2000cwv.googlegr oups.com...
>I am having the common "distributor_admin does not exist" error when
> trying to set up a machine as its own distributor. (First time after
> setting up about 200 distributors). I get this whether I'm doing it
> remotely, or on the machine itself.
> The wrinkle is that the login "distributor_admin" does not exist at all
> on the potential publisher (who, again, is going to be its own
> distributor).
> Do I need to reinstall MS-SQL to get that login created? Or can I do
> it by hand somehow?
> Or am I barking up the wrong tree?
> Thank you!
> -Joseph
>
|||Thanks, Hilary...There certainly was a difference...
I followed your instructions, and I'm going to take another stab at
setting distribution up.
You are the man! I'm going to have my boss order your book...
-Joseph
Hilary Cotter wrote:[vbcol=seagreen]
> What happens when you do a select @.@.servername?
> Does it match what you get when you issue a xp_cmdshell 'hostname'?
> if not you need to do the following
> sp_dropserver @.server='oldServername'
> sp_addserver @.server='NewServerName', @.local='LOCAL'
> Then restart SQL Server.
> --
> 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
>
> "Joseph" <josephsheppard@.gmail.com> wrote in message
> news:1168894392.883101.309200@.v45g2000cwv.googlegr oups.com...
'distributor_admin' is not defined as a remote login at the server
We have set up one of the clustered instance as a remote distributor and
replication has been working properly. However, on the event log of the
active node, I saw the following error appeared every few minutes:
Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
'distributor_admin' is not defined as a remote login at the server. Verify
that you have specified the correct login name. [CLIENT: 10.1.1.28].
REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
as a remote distributor and it is running on active node; the client
(10.1.1.28) is the passive node of the cluster. our environment is windows
2003 and sql server 2005 with sp2.
I wonder if anyone has any idea how to get rid of this error from my server.
Thanks in advance.
Zack.
I wonder if it is a managed news group by Microsoft, if not, someone knows
the managed news group since I was told that it is a managed group and
Microsoft will reply every post?
thanks for your kind reply because I really need to fix this error and don't
want to pay MS for this kind of error. We have paid too much to MS already
for support.
Zack.
m
"dp" wrote:
> We have set up one of the clustered instance as a remote distributor and
> replication has been working properly. However, on the event log of the
> active node, I saw the following error appeared every few minutes:
> Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
> 'distributor_admin' is not defined as a remote login at the server. Verify
> that you have specified the correct login name. [CLIENT: 10.1.1.28].
> REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
> as a remote distributor and it is running on active node; the client
> (10.1.1.28) is the passive node of the cluster. our environment is windows
> 2003 and sql server 2005 with sp2.
> I wonder if anyone has any idea how to get rid of this error from my server.
> Thanks in advance.
> Zack.
>
replication has been working properly. However, on the event log of the
active node, I saw the following error appeared every few minutes:
Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
'distributor_admin' is not defined as a remote login at the server. Verify
that you have specified the correct login name. [CLIENT: 10.1.1.28].
REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
as a remote distributor and it is running on active node; the client
(10.1.1.28) is the passive node of the cluster. our environment is windows
2003 and sql server 2005 with sp2.
I wonder if anyone has any idea how to get rid of this error from my server.
Thanks in advance.
Zack.
I wonder if it is a managed news group by Microsoft, if not, someone knows
the managed news group since I was told that it is a managed group and
Microsoft will reply every post?
thanks for your kind reply because I really need to fix this error and don't
want to pay MS for this kind of error. We have paid too much to MS already
for support.
Zack.
m
"dp" wrote:
> We have set up one of the clustered instance as a remote distributor and
> replication has been working properly. However, on the event log of the
> active node, I saw the following error appeared every few minutes:
> Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
> 'distributor_admin' is not defined as a remote login at the server. Verify
> that you have specified the correct login name. [CLIENT: 10.1.1.28].
> REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
> as a remote distributor and it is running on active node; the client
> (10.1.1.28) is the passive node of the cluster. our environment is windows
> 2003 and sql server 2005 with sp2.
> I wonder if anyone has any idea how to get rid of this error from my server.
> Thanks in advance.
> Zack.
>
distributor_admin error
Hi,
When i try to configure publisher, distributor, subscriber on the same remote server, i am getting the following error:
SQL Server Enterprise Manager could not configure '90.168.XX.XXX' as the distributor for '90.168.xx.xxx'
Error: 18483: could not connect to server 'different name' beacuse 'distributor_admin' isn ot defined as a remote login at the server
I changed the MSSQLSERVER and the SQL Server Agent and a dependant file to run froma domain account, i gave that domain account all the privileges on the database and the master database aswell. but i keep getting the error.
Also why is the error showing diffetent server name than the '90.168...' name?
Will i have problems later if i donot set up alias names rather than ip address. If so, how can i set up alias names for SQL Server's
I am quite new to all this, any detailed answer could really help.
thanks
stummalaGot that fixed by re registering the server name to the result of @.@.SERVERNAME
When i try to configure publisher, distributor, subscriber on the same remote server, i am getting the following error:
SQL Server Enterprise Manager could not configure '90.168.XX.XXX' as the distributor for '90.168.xx.xxx'
Error: 18483: could not connect to server 'different name' beacuse 'distributor_admin' isn ot defined as a remote login at the server
I changed the MSSQLSERVER and the SQL Server Agent and a dependant file to run froma domain account, i gave that domain account all the privileges on the database and the master database aswell. but i keep getting the error.
Also why is the error showing diffetent server name than the '90.168...' name?
Will i have problems later if i donot set up alias names rather than ip address. If so, how can i set up alias names for SQL Server's
I am quite new to all this, any detailed answer could really help.
thanks
stummalaGot that fixed by re registering the server name to the result of @.@.SERVERNAME
Distributor to Subscriber History
I am using publisher as 2005 and subscriber as 2000. I am getting
Uninitialized Subscription. When I double click I don't see any message on
Distributor to Subscriber Histor. Where as I can see message for one of the
subscriber which is 2005.
Is that is do with 2000?
Nita
What type of repl? Sanpshot/Trans/Merge?
did you run your snapshot agent after adding subscription to 2000 svr?
Did your distribution agent push snapshot?
Try running Profiler on subscriber to pick up activity from distributor to
help troubleshoot.
ChrisB MCDBA
MSSQLConsulting.com
"NitaDBA" wrote:
> I am using publisher as 2005 and subscriber as 2000. I am getting
> Uninitialized Subscription. When I double click I don't see any message on
> Distributor to Subscriber Histor. Where as I can see message for one of the
> subscriber which is 2005.
> Is that is do with 2000?
> --
> Nita
Uninitialized Subscription. When I double click I don't see any message on
Distributor to Subscriber Histor. Where as I can see message for one of the
subscriber which is 2005.
Is that is do with 2000?
Nita
What type of repl? Sanpshot/Trans/Merge?
did you run your snapshot agent after adding subscription to 2000 svr?
Did your distribution agent push snapshot?
Try running Profiler on subscriber to pick up activity from distributor to
help troubleshoot.
ChrisB MCDBA
MSSQLConsulting.com
"NitaDBA" wrote:
> I am using publisher as 2005 and subscriber as 2000. I am getting
> Uninitialized Subscription. When I double click I don't see any message on
> Distributor to Subscriber Histor. Where as I can see message for one of the
> subscriber which is 2005.
> Is that is do with 2000?
> --
> Nita
Distributor SQLSERVERAgent Account
What account should the SqlServerAgent run under at the distributor
(dist. and publisher are same machine)?
Mine is running under .\sqladmin but I keep getting
Error: The schema script
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
could not be propagated to the subscriber.
At subscriber, SqlServerAgent is running under LocalSystem.
\\WSCLAN08\ReplData\ is public share and user has permissions (incl.
sqladmin).
Also, user is in PAL of publication (incl. sqladmin).
Thanks.
Is this pull? If so, your subscriber's sql server agent should be running
under the same account as the publisher's SQL Server agent, or an account
that is part of the local admin group on the publisher.
If this is not possible the SQL Server agent account on the subscriber
should have rigths to read the snapshot share and underlying files and
folders. You will probably have to craft another snapshot share on the
publisher for this.
For push subscriptions you should not have to worry about this unless you
have a remote distributor.
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
<richerwin@.ubs-europe.org> wrote in message
news:1107929613.327118.195320@.l41g2000cwc.googlegr oups.com...
> What account should the SqlServerAgent run under at the distributor
> (dist. and publisher are same machine)?
> Mine is running under .\sqladmin but I keep getting
> Error: The schema script
> '\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
> could not be propagated to the subscriber.
> At subscriber, SqlServerAgent is running under LocalSystem.
> \\WSCLAN08\ReplData\ is public share and user has permissions (incl.
> sqladmin).
> Also, user is in PAL of publication (incl. sqladmin).
> Thanks.
>
|||Hilary Cotter wrote:
> Is this pull? If so, your subscriber's sql server agent should be
running
> under the same account as the publisher's SQL Server agent, or an
account
> that is part of the local admin group on the publisher.
Yes, this is annonymous pull over VPN.
BTW, if default installations are used, what is the difference between
running SQL Server Agent as LocalSystem and sa? What is LocalSystem? If
SQL Server Agent is to run under sqladmin, for example, does sqladmin
have to be created as a domain administrator on the subscribers'
computer?
> If this is not possible the SQL Server agent account on the
subscriber
> should have rigths to read the snapshot share and underlying files
and
> folders. You will probably have to craft another snapshot share on
the
> publisher for this.
I've got everbody and his uncle given full rights to the share, but
since my subscriber SQL Server Agent is running under LocalSystem, I
guess all bets are off as to why he can't access the share.
Sorry for all these basic questions, but my accts are all screwed up
and my brain is mush.
Thanks
|||There is some confusion here between service startup
accounts and SQL Server logins.
Service startup accounts can be found in control panel,
services and we are really interested in the sql server
agent one, which runs the replication jobs. This must be
a domain user account if you are to replicate from one
machine to another. LocalSystem can be used if you're
doing it all on one box.
If you're on a trusted environment, then the domain user
account on the subscriber for pull subscriptions must
have rights to the snapshot share. If it's non-trusted,
you'll need FTP.
'sa' is a SQL Server login. If your agents are set to use
impersonation, then the whole thing will be using windows
security and you can forget sql logins. If you're using a
non-trusted environment, you'll be obliged to use sql
logins.
This is explained in replication, security in BOL but
admittedly is not at all a straightforward topic
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
>--Original Message--
>Hilary Cotter wrote:
agent should be[vbcol=seagreen]
>running
agent, or an
>account
>Yes, this is annonymous pull over VPN.
>BTW, if default installations are used, what is the
difference between
>running SQL Server Agent as LocalSystem and sa? What is
LocalSystem? If
>SQL Server Agent is to run under sqladmin, for example,
does sqladmin
>have to be created as a domain administrator on the
subscribers'[vbcol=seagreen]
>computer?
on the[vbcol=seagreen]
>subscriber
underlying files[vbcol=seagreen]
>and
snapshot share on
>the
>I've got everbody and his uncle given full rights to the
share, but
>since my subscriber SQL Server Agent is running under
LocalSystem, I
>guess all bets are off as to why he can't access the
share.
>Sorry for all these basic questions, but my accts are
all screwed up
>and my brain is mush.
>Thanks
>.
>
(dist. and publisher are same machine)?
Mine is running under .\sqladmin but I keep getting
Error: The schema script
'\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
could not be propagated to the subscriber.
At subscriber, SqlServerAgent is running under LocalSystem.
\\WSCLAN08\ReplData\ is public share and user has permissions (incl.
sqladmin).
Also, user is in PAL of publication (incl. sqladmin).
Thanks.
Is this pull? If so, your subscriber's sql server agent should be running
under the same account as the publisher's SQL Server agent, or an account
that is part of the local admin group on the publisher.
If this is not possible the SQL Server agent account on the subscriber
should have rigths to read the snapshot share and underlying files and
folders. You will probably have to craft another snapshot share on the
publisher for this.
For push subscriptions you should not have to worry about this unless you
have a remote distributor.
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
<richerwin@.ubs-europe.org> wrote in message
news:1107929613.327118.195320@.l41g2000cwc.googlegr oups.com...
> What account should the SqlServerAgent run under at the distributor
> (dist. and publisher are same machine)?
> Mine is running under .\sqladmin but I keep getting
> Error: The schema script
> '\\WSCLAN08\ReplData\unc\WSCLAN08_TMS_TMS\20050209 054316\Ltab_Years_1.sch'
> could not be propagated to the subscriber.
> At subscriber, SqlServerAgent is running under LocalSystem.
> \\WSCLAN08\ReplData\ is public share and user has permissions (incl.
> sqladmin).
> Also, user is in PAL of publication (incl. sqladmin).
> Thanks.
>
|||Hilary Cotter wrote:
> Is this pull? If so, your subscriber's sql server agent should be
running
> under the same account as the publisher's SQL Server agent, or an
account
> that is part of the local admin group on the publisher.
Yes, this is annonymous pull over VPN.
BTW, if default installations are used, what is the difference between
running SQL Server Agent as LocalSystem and sa? What is LocalSystem? If
SQL Server Agent is to run under sqladmin, for example, does sqladmin
have to be created as a domain administrator on the subscribers'
computer?
> If this is not possible the SQL Server agent account on the
subscriber
> should have rigths to read the snapshot share and underlying files
and
> folders. You will probably have to craft another snapshot share on
the
> publisher for this.
I've got everbody and his uncle given full rights to the share, but
since my subscriber SQL Server Agent is running under LocalSystem, I
guess all bets are off as to why he can't access the share.
Sorry for all these basic questions, but my accts are all screwed up
and my brain is mush.
Thanks
|||There is some confusion here between service startup
accounts and SQL Server logins.
Service startup accounts can be found in control panel,
services and we are really interested in the sql server
agent one, which runs the replication jobs. This must be
a domain user account if you are to replicate from one
machine to another. LocalSystem can be used if you're
doing it all on one box.
If you're on a trusted environment, then the domain user
account on the subscriber for pull subscriptions must
have rights to the snapshot share. If it's non-trusted,
you'll need FTP.
'sa' is a SQL Server login. If your agents are set to use
impersonation, then the whole thing will be using windows
security and you can forget sql logins. If you're using a
non-trusted environment, you'll be obliged to use sql
logins.
This is explained in replication, security in BOL but
admittedly is not at all a straightforward topic
Rgds,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
>--Original Message--
>Hilary Cotter wrote:
agent should be[vbcol=seagreen]
>running
agent, or an
>account
>Yes, this is annonymous pull over VPN.
>BTW, if default installations are used, what is the
difference between
>running SQL Server Agent as LocalSystem and sa? What is
LocalSystem? If
>SQL Server Agent is to run under sqladmin, for example,
does sqladmin
>have to be created as a domain administrator on the
subscribers'[vbcol=seagreen]
>computer?
on the[vbcol=seagreen]
>subscriber
underlying files[vbcol=seagreen]
>and
snapshot share on
>the
>I've got everbody and his uncle given full rights to the
share, but
>since my subscriber SQL Server Agent is running under
LocalSystem, I
>guess all bets are off as to why he can't access the
share.
>Sorry for all these basic questions, but my accts are
all screwed up
>and my brain is mush.
>Thanks
>.
>
Distributor server
I have a merge replication environment with 1 publisher/distributor in the
same machine and 3 subscribers with a lot of data to merge. The link between
them is slow.
I'm with performance problems with my applications I think that job
replications could be punish this performance.
Setup another machine to be a Distributor Server is a good idea ?
thank you for assistance.
Tony
Absolutely not. The location of the distribution server has little impact
with merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"toryi" <toryi@.ig.com.br> wrote in message
news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication environment with 1 publisher/distributor in the
> same machine and 3 subscribers with a lot of data to merge. The link
between
> them is slow.
> I'm with performance problems with my applications I think that job
> replications could be punish this performance.
> Setup another machine to be a Distributor Server is a good idea ?
> thank you for assistance.
> Tony
>
|||What advantage I'll have in setup another machine to be a Distributor server
?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iuKZW7AFHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Absolutely not. The location of the distribution server has little impact
> with merge replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "toryi" <toryi@.ig.com.br> wrote in message
> news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
the
> between
>
same machine and 3 subscribers with a lot of data to merge. The link between
them is slow.
I'm with performance problems with my applications I think that job
replications could be punish this performance.
Setup another machine to be a Distributor Server is a good idea ?
thank you for assistance.
Tony
Absolutely not. The location of the distribution server has little impact
with merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"toryi" <toryi@.ig.com.br> wrote in message
news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication environment with 1 publisher/distributor in the
> same machine and 3 subscribers with a lot of data to merge. The link
between
> them is slow.
> I'm with performance problems with my applications I think that job
> replications could be punish this performance.
> Setup another machine to be a Distributor Server is a good idea ?
> thank you for assistance.
> Tony
>
|||What advantage I'll have in setup another machine to be a Distributor server
?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23iuKZW7AFHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Absolutely not. The location of the distribution server has little impact
> with merge replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "toryi" <toryi@.ig.com.br> wrote in message
> news:%23oqjrx6AFHA.3504@.TK2MSFTNGP12.phx.gbl...
the
> between
>
Distributor password in distributor Properties
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)
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)
Distributor on another server?
Can the distributor be on another server that does not have SQL Server on
it. Using SQL 2k merge replication. Thanks.
David
No, it must have a version of SQL Server on it.
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
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:eofDI%232IGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Can the distributor be on another server that does not have SQL Server on
> it. Using SQL 2k merge replication. Thanks.
> David
>
it. Using SQL 2k merge replication. Thanks.
David
No, it must have a version of SQL Server on it.
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
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:eofDI%232IGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Can the distributor be on another server that does not have SQL Server on
> it. Using SQL 2k merge replication. Thanks.
> David
>
Distributor Lock Timeouts
I have transactional replication set up and running with a remote
distributor. Replication latency continues to be good (3 - 10 seconds) but
after we added several indexed views to the subscriber, we are seeing lock
timeouts on the distribution database. I haven't been able to tie the
timeouts to any specific activity or activity levels. When I do see the
timeouts I also see a spike in lock requests, but not lock escallations.
Through it all, replication latency seems to stay about the same.
Finally, whenever I fire up Replication Monitor the lock timeouts increase
quite a bit, but they still occur when replication monitor isn't running.
Any thoughts or ideas?
Replication applies the sync commands in a batch and holds a lock while
applying that batch. The locking you are seeing is probably a result of
this. If you are using SQL Server 2005, try using the snapshot isolation
model.
http://www.zetainteractive.com - Shift Happens!
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
"DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
news:O76HbG3OIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I have transactional replication set up and running with a remote
>distributor. Replication latency continues to be good (3 - 10 seconds) but
>after we added several indexed views to the subscriber, we are seeing lock
>timeouts on the distribution database. I haven't been able to tie the
>timeouts to any specific activity or activity levels. When I do see the
>timeouts I also see a spike in lock requests, but not lock escallations.
>Through it all, replication latency seems to stay about the same.
> Finally, whenever I fire up Replication Monitor the lock timeouts increase
> quite a bit, but they still occur when replication monitor isn't running.
> Any thoughts or ideas?
>
distributor. Replication latency continues to be good (3 - 10 seconds) but
after we added several indexed views to the subscriber, we are seeing lock
timeouts on the distribution database. I haven't been able to tie the
timeouts to any specific activity or activity levels. When I do see the
timeouts I also see a spike in lock requests, but not lock escallations.
Through it all, replication latency seems to stay about the same.
Finally, whenever I fire up Replication Monitor the lock timeouts increase
quite a bit, but they still occur when replication monitor isn't running.
Any thoughts or ideas?
Replication applies the sync commands in a batch and holds a lock while
applying that batch. The locking you are seeing is probably a result of
this. If you are using SQL Server 2005, try using the snapshot isolation
model.
http://www.zetainteractive.com - Shift Happens!
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
"DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
news:O76HbG3OIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I have transactional replication set up and running with a remote
>distributor. Replication latency continues to be good (3 - 10 seconds) but
>after we added several indexed views to the subscriber, we are seeing lock
>timeouts on the distribution database. I haven't been able to tie the
>timeouts to any specific activity or activity levels. When I do see the
>timeouts I also see a spike in lock requests, but not lock escallations.
>Through it all, replication latency seems to stay about the same.
> Finally, whenever I fire up Replication Monitor the lock timeouts increase
> quite a bit, but they still occur when replication monitor isn't running.
> Any thoughts or ideas?
>
Distributor cannot connect to subscriber
I am setting up my 2005 Replication system...
publisher = 2005 sp1
Subscriber = 2005 sp1
I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away.
Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections.
I am not sure where to look at next... I never had this issue in 2000.
Did a little more testing. My distributor/publisher also has SQL 2000 on it. I think this might have something to do with it.
I created a linked server on my subscriber to my publisher/dist and it has no problem connecting what so ever.
Could it be that my pub/dist is using the wrong client files?
|||Hi William,
Are you using merge or transactional replication?
Is the subscription that you set up a push or pull? This will determine where the distribution or merge agent is running.
In you second note, you indicate that the distributor/publisher has SQL 2000. Is this in addition to SQL 2005, per the first note?
Assuming that you have SQL 2000 and SQL 2005 on the boxes, then you are using named instances for the SQL 2005 installations. If you have named instances, not only do you need to enable remote connections, but you need to insure the SQL Browser service is running for connectivity to work properly.
This link has some more information about SQL Browser -- http://msdn2.microsoft.com/en-us/library/ms165724.aspx
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hey Tom,
I am using transactional replication. The pub/dist has both 2000 (default) and 2005 (named) instances.
The subscription is a push subscription. I will take a look at the link you sent.
=== Edited by William Lowers @. 24 Jan 2007 4:11 PM UTC===
After reading the article I check my pub/dist. It is set to listen on TCP port 1434 which I know works cause I had to have the Firewall openned to get to the server. The SQL Browser server appears to be running.
Is there anything I can do to ensure it is working?
|||update
Since my subscriber is a dev server and if I mess it up it does not matter I did the following...
I created a publication on this server with itself being the distributer also.. that way it is setup just like the server with 2000 and 2005 on it. I then created a subscription from this server (2005 only) to the 2005 database on the 2000 and 2005 server.
No problem what so ever. So I am leaning towards the fact that it has to do with having both versions on one server...
Any idea? or is this a Bug?
|||Can you try the following connectivity test from command prompt?
at your dev machine, try to connect to the other box
osql -S<publisher_server> -U<user id> -P<password>
and try the connect from publisher machine to your dev machine as well. If both work, you can rule out the protocal enabling issue. If it works only from your dev box to publisher machine, but not the other way around, it might be as simple as enabling TCP and name/pipe from configuration manager.
Gary
|||Ok... I did as suggested and the publisher has no problem connecting to the subscriber...
So what does that mean?
I can connect via command line,SMS but not replication.
|||Ok... So the above statment is only half true. After doing the above I thought about looking at my path cause it executed the osql from the default directory that I was openned to....
So when I execute from 80/tools/binn I can connect without issue.
From 90/tools/binn I get this error
[SQL Native Client]TCP Provider: No connection could be made because the
target machine actively refused it.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections.
Can you check the protocal interface are enabled for TCP and name pipe?
You can find the instruction on the following posting from Mahesh,
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1059301&SiteID=1
Gary
|||I double checked and I already have that set. I cycled the server anyway... Still no luck.
What makes no sense to me is that I can connect with 2000 osql but not 2005 osql...
Is there anyway to check what libraries each client is using?
|||What was your OS? If windows 2000 server, would install MDAC 2.7/2.8 help?
Thanks.
This posting is provided AS IS with no warranties, and confers no rights.
|||OS is Windows 2003 SP1...
I am thinking what happened was the re-install of 2000 client tools that was done when we could not work on DTS...
I
|||I will guess that the re-applying of SQL 2000 bits after SQL 2005 was installed is what's causing the problem. Multiple versions of SQL Server installed on the same box is only supported provide you install the earlier version first, then the later version second. Re-installing the earlier version after the later version is already installed can mess things up. I suggest reinstalling/repairing your SQL 2005 installation.|||That is what I am thinking but since the server is a major player in our production environment, I don't think I will be allowed to...
So I am asking my boss for other options...
Thanks for all the help.
Distributor and publisher
Hi All:
i have two publications in my local computer, then i go to
distributor tab in publisher and distributor properties ,which shows two
distributor database there. that make sense coz i have two publication one
for each. is that right?
my question is why in the publisher tab of publisher and distributor
properties only has one distributorDB ?
Cheers
nick
I don't think so. Each publisher can use a single distribution database.
However you can have one or more distribution database per server.
So consider three publishers a, b, and c. Publisher a and publisher b use
the distribution database d1 on publisher c. Publisher c used a local
distribution database on publisher c called distribution. You could also
configure publisher a to use d2 (another distribution database) on publisher
c and publisher b could still use d1 on publisher c.
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
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
|||Nick,
Hilary is correct, but what you are thinking of is
something I also assumed existed when I first saw the
option of several distribution databases. I still think
this would be a good idea as disaster recovery is far
more versatile if the msrepl_commands table could be
specific to a publication rather than a publisher ie if
one publisher has 2 transactional publications, they'd go
into separate distribution databases. Unfortunately this
would require a huge amount of coding to amalgamate
different notification data so I guess it's not going to
happen soon.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
>--Original Message--
>I don't think so. Each publisher can use a single
distribution database.
>However you can have one or more distribution database
per server.
>So consider three publishers a, b, and c. Publisher a
and publisher b use
>the distribution database d1 on publisher c. Publisher c
used a local
>distribution database on publisher c called
distribution. You could also
>configure publisher a to use d2 (another distribution
database) on publisher
>c and publisher b could still use d1 on publisher c.
>--
>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/offer-
listing/0974973602/ref=dp_more-buying-choices_2//102-
1802128-2428137?condition=all[vbcol=seagreen]
>Looking for a FAQ on Indexing Services/SQL FTS
>http://www.indexserverfaq.com
>"Nick" <fsheng@.ebreathe.co.nz> wrote in message
>news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
computer, then i go to[vbcol=seagreen]
properties ,which shows two[vbcol=seagreen]
two publication one[vbcol=seagreen]
publisher and
>distributor
>
>.
>
|||Thanks Hilary and Paul
so in this case, two publication are sharing one distribution DB.
Cheers
nick
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
i have two publications in my local computer, then i go to
distributor tab in publisher and distributor properties ,which shows two
distributor database there. that make sense coz i have two publication one
for each. is that right?
my question is why in the publisher tab of publisher and distributor
properties only has one distributorDB ?
Cheers
nick
I don't think so. Each publisher can use a single distribution database.
However you can have one or more distribution database per server.
So consider three publishers a, b, and c. Publisher a and publisher b use
the distribution database d1 on publisher c. Publisher c used a local
distribution database on publisher c called distribution. You could also
configure publisher a to use d2 (another distribution database) on publisher
c and publisher b could still use d1 on publisher c.
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
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
|||Nick,
Hilary is correct, but what you are thinking of is
something I also assumed existed when I first saw the
option of several distribution databases. I still think
this would be a good idea as disaster recovery is far
more versatile if the msrepl_commands table could be
specific to a publication rather than a publisher ie if
one publisher has 2 transactional publications, they'd go
into separate distribution databases. Unfortunately this
would require a huge amount of coding to amalgamate
different notification data so I guess it's not going to
happen soon.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
>--Original Message--
>I don't think so. Each publisher can use a single
distribution database.
>However you can have one or more distribution database
per server.
>So consider three publishers a, b, and c. Publisher a
and publisher b use
>the distribution database d1 on publisher c. Publisher c
used a local
>distribution database on publisher c called
distribution. You could also
>configure publisher a to use d2 (another distribution
database) on publisher
>c and publisher b could still use d1 on publisher c.
>--
>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/offer-
listing/0974973602/ref=dp_more-buying-choices_2//102-
1802128-2428137?condition=all[vbcol=seagreen]
>Looking for a FAQ on Indexing Services/SQL FTS
>http://www.indexserverfaq.com
>"Nick" <fsheng@.ebreathe.co.nz> wrote in message
>news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
computer, then i go to[vbcol=seagreen]
properties ,which shows two[vbcol=seagreen]
two publication one[vbcol=seagreen]
publisher and
>distributor
>
>.
>
|||Thanks Hilary and Paul
so in this case, two publication are sharing one distribution DB.
Cheers
nick
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
distributor agent
Win2003srv
mssql2000
mssql2005
Oracle
I have a mssql2000 database that I Replicate to oracle,
some tables have row filtering.
This was working fine until I create an another publication and subscription
(transaction)
on the same database, for mssql2005 This subscription has the
loopback_detection = N'True',
After this, the Oracle subscription stop to work correctly
Tables with row filter are not replicated. In Repl Monitor everything looks
fine
I try to delete all publication and then re-crate it and start snap-shot for
oracle
The bulk copying are doing the right thing regarding filter-rows.
But the distributor agent will not work properly
I can see the log-reader pushing transaction to the distibutor , but nothing
happens
What is wrong her?
-roger
What do you see if you run a sp_browsereplcmds in the distribution database?
The oracle DML should be showing up here.
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
"Roger Nygrd" <roger@.askit.no> wrote in message
news:11uemak27qnmp5d@.corp.supernews.com...
> Win2003srv
> mssql2000
> mssql2005
> Oracle
> I have a mssql2000 database that I Replicate to oracle,
> some tables have row filtering.
> This was working fine until I create an another publication and
> subscription (transaction)
> on the same database, for mssql2005 This subscription has the
> loopback_detection = N'True',
> After this, the Oracle subscription stop to work correctly
> Tables with row filter are not replicated. In Repl Monitor everything
> looks fine
> I try to delete all publication and then re-crate it and start snap-shot
> for oracle
> The bulk copying are doing the right thing regarding filter-rows.
> But the distributor agent will not work properly
> I can see the log-reader pushing transaction to the distibutor , but
> nothing happens
> What is wrong her?
>
> -roger
>
|||It is empty
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23w5ntqyKGHA.2036@.TK2MSFTNGP14.phx.gbl...
> What do you see if you run a sp_browsereplcmds in the distribution
> database? The oracle DML should be showing up here.
> --
> 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
> "Roger Nygrd" <roger@.askit.no> wrote in message
> news:11uemak27qnmp5d@.corp.supernews.com...
>
mssql2000
mssql2005
Oracle
I have a mssql2000 database that I Replicate to oracle,
some tables have row filtering.
This was working fine until I create an another publication and subscription
(transaction)
on the same database, for mssql2005 This subscription has the
loopback_detection = N'True',
After this, the Oracle subscription stop to work correctly
Tables with row filter are not replicated. In Repl Monitor everything looks
fine
I try to delete all publication and then re-crate it and start snap-shot for
oracle
The bulk copying are doing the right thing regarding filter-rows.
But the distributor agent will not work properly
I can see the log-reader pushing transaction to the distibutor , but nothing
happens
What is wrong her?
-roger
What do you see if you run a sp_browsereplcmds in the distribution database?
The oracle DML should be showing up here.
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
"Roger Nygrd" <roger@.askit.no> wrote in message
news:11uemak27qnmp5d@.corp.supernews.com...
> Win2003srv
> mssql2000
> mssql2005
> Oracle
> I have a mssql2000 database that I Replicate to oracle,
> some tables have row filtering.
> This was working fine until I create an another publication and
> subscription (transaction)
> on the same database, for mssql2005 This subscription has the
> loopback_detection = N'True',
> After this, the Oracle subscription stop to work correctly
> Tables with row filter are not replicated. In Repl Monitor everything
> looks fine
> I try to delete all publication and then re-crate it and start snap-shot
> for oracle
> The bulk copying are doing the right thing regarding filter-rows.
> But the distributor agent will not work properly
> I can see the log-reader pushing transaction to the distibutor , but
> nothing happens
> What is wrong her?
>
> -roger
>
|||It is empty
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23w5ntqyKGHA.2036@.TK2MSFTNGP14.phx.gbl...
> What do you see if you run a sp_browsereplcmds in the distribution
> database? The oracle DML should be showing up here.
> --
> 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
> "Roger Nygrd" <roger@.askit.no> wrote in message
> news:11uemak27qnmp5d@.corp.supernews.com...
>
Distributor (Server) Name Changed... No Replication Monitor what to do ?
Hi there,
We have changed the name of our SQL Server. Now the Replication Monitor is not appearing on the Enterprise Manager.
I tried to change the name of Distributor but there is no option. We have plenty of publications which are still showing old SQL Server instance name.
How we can change the name of Publisher without deleting all the publications ?
Any Help will be appreciated.
Abid Malik
the simplest way to fix this is to remove replication using the Tools -
Replication - Disable Publishing menu options and then to reinstall it.
"Abid Malik" <abidmunirmalik@.hotmail.com> wrote in message
news:69D97665-3983-4400-BE1A-028D9A1D5EAF@.microsoft.com...
> Hi there,
> We have changed the name of our SQL Server. Now the Replication Monitor is
not appearing on the Enterprise Manager.
> I tried to change the name of Distributor but there is no option. We have
plenty of publications which are still showing old SQL Server instance name.
> How we can change the name of Publisher without deleting all the
publications ?
> Any Help will be appreciated.
> Abid Malik
We have changed the name of our SQL Server. Now the Replication Monitor is not appearing on the Enterprise Manager.
I tried to change the name of Distributor but there is no option. We have plenty of publications which are still showing old SQL Server instance name.
How we can change the name of Publisher without deleting all the publications ?
Any Help will be appreciated.
Abid Malik
the simplest way to fix this is to remove replication using the Tools -
Replication - Disable Publishing menu options and then to reinstall it.
"Abid Malik" <abidmunirmalik@.hotmail.com> wrote in message
news:69D97665-3983-4400-BE1A-028D9A1D5EAF@.microsoft.com...
> Hi there,
> We have changed the name of our SQL Server. Now the Replication Monitor is
not appearing on the Enterprise Manager.
> I tried to change the name of Distributor but there is no option. We have
plenty of publications which are still showing old SQL Server instance name.
> How we can change the name of Publisher without deleting all the
publications ?
> Any Help will be appreciated.
> Abid Malik
Distributor
Hi,
I have set a transactional replication by Interprise
Manager, but when I create a subscriber using "Pull
Subscription to ..." it creates it succesfuly and even
gives me this message that "you have created the
subscription successfully", but it doesn't start
replication and gives this message in "Last Action" column:
" 'XServer' is not Configured as a Distributor. The step
failed".
Thank you very much.
Mathew,
this could be a naming issue.
Please try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'XServer'
GO
Use Master
go
Sp_Addserver 'XServer', 'local'
GO
Stop and Start SQL Services
HTH,
Paul Ibison
|||can the subscriber ping Xserver? Is XServer a Publisher/Distributor or
Distributor?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mathew" <anonymous@.discussions.microsoft.com> wrote in message
news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
> Hi,
> I have set a transactional replication by Interprise
> Manager, but when I create a subscriber using "Pull
> Subscription to ..." it creates it succesfuly and even
> gives me this message that "you have created the
> subscription successfully", but it doesn't start
> replication and gives this message in "Last Action" column:
> " 'XServer' is not Configured as a Distributor. The step
> failed".
> Thank you very much.
>
|||Paul,
Thank you so much. I have checked it, and it's correct.
Actually we have 2 servers in 2 different locations, and
both of them are called 'HAKIM-SERVER', so when I
run "Select @.@.ServerName" they answer 'HAKIM-SERVER'. I
have registered both of them in my "Enterprise
Manager",one by it's own name which is 'HAKIM-SERVER', and
the other one by it's IP address, as we have a VPN. It was
working until 2 weeks ago that our modem and router in the
other building were burned and we had to change them with
a brand new one. Our VPN is fine and there is nothing odd,
but when I go to to "Pull subscription
to 'xxx.xxx.xxx.xxx'... " on the server, which is in our
other location and I registered it with the IP address, to
create the subscribor, it gives me that error message
>--Original Message--
>Mathew,
>this could be a naming issue.
>Please try:
>Use Master
>go
>Select @.@.Servername
>This should return your current server name but if it
>returns NULL then try:
>Use Master
>go
>Sp_DropServer 'XServer'
>GO
>Use Master
>go
>Sp_Addserver 'XServer', 'local'
>GO
>Stop and Start SQL Services
>HTH,
>Paul Ibison
>
>.
>
|||Hi,
It's a Publisher/Distributor. I wrote a complete
explanation for the first respond from Paul. Whould you
please take a look at it?
Thnks again
>--Original Message--
>can the subscriber ping Xserver? Is XServer a
Publisher/Distributor or
>Distributor?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Mathew" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
column:
>
>.
>
|||Mathew,
can you try using an alias rather than an IP address.
Regards,
Paul Ibison
|||Dear Paul,
How can I assign an alias to a remote server when it has
the same name as the local server.
Thanks,
>--Original Message--
>Mathew,
>can you try using an alias rather than an IP address.
>Regards,
>Paul Ibison
>
>.
>
|||Matthew,
in the client network utility, you can add an alias with the TCP/IP network
library. The server alias is any name you choose, and the server name can be
the IP address you have been using.
HTH,
Paul Ibison
I have set a transactional replication by Interprise
Manager, but when I create a subscriber using "Pull
Subscription to ..." it creates it succesfuly and even
gives me this message that "you have created the
subscription successfully", but it doesn't start
replication and gives this message in "Last Action" column:
" 'XServer' is not Configured as a Distributor. The step
failed".
Thank you very much.
Mathew,
this could be a naming issue.
Please try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'XServer'
GO
Use Master
go
Sp_Addserver 'XServer', 'local'
GO
Stop and Start SQL Services
HTH,
Paul Ibison
|||can the subscriber ping Xserver? Is XServer a Publisher/Distributor or
Distributor?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mathew" <anonymous@.discussions.microsoft.com> wrote in message
news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
> Hi,
> I have set a transactional replication by Interprise
> Manager, but when I create a subscriber using "Pull
> Subscription to ..." it creates it succesfuly and even
> gives me this message that "you have created the
> subscription successfully", but it doesn't start
> replication and gives this message in "Last Action" column:
> " 'XServer' is not Configured as a Distributor. The step
> failed".
> Thank you very much.
>
|||Paul,
Thank you so much. I have checked it, and it's correct.
Actually we have 2 servers in 2 different locations, and
both of them are called 'HAKIM-SERVER', so when I
run "Select @.@.ServerName" they answer 'HAKIM-SERVER'. I
have registered both of them in my "Enterprise
Manager",one by it's own name which is 'HAKIM-SERVER', and
the other one by it's IP address, as we have a VPN. It was
working until 2 weeks ago that our modem and router in the
other building were burned and we had to change them with
a brand new one. Our VPN is fine and there is nothing odd,
but when I go to to "Pull subscription
to 'xxx.xxx.xxx.xxx'... " on the server, which is in our
other location and I registered it with the IP address, to
create the subscribor, it gives me that error message
>--Original Message--
>Mathew,
>this could be a naming issue.
>Please try:
>Use Master
>go
>Select @.@.Servername
>This should return your current server name but if it
>returns NULL then try:
>Use Master
>go
>Sp_DropServer 'XServer'
>GO
>Use Master
>go
>Sp_Addserver 'XServer', 'local'
>GO
>Stop and Start SQL Services
>HTH,
>Paul Ibison
>
>.
>
|||Hi,
It's a Publisher/Distributor. I wrote a complete
explanation for the first respond from Paul. Whould you
please take a look at it?
Thnks again
>--Original Message--
>can the subscriber ping Xserver? Is XServer a
Publisher/Distributor or
>Distributor?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Mathew" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
column:
>
>.
>
|||Mathew,
can you try using an alias rather than an IP address.
Regards,
Paul Ibison
|||Dear Paul,
How can I assign an alias to a remote server when it has
the same name as the local server.
Thanks,
>--Original Message--
>Mathew,
>can you try using an alias rather than an IP address.
>Regards,
>Paul Ibison
>
>.
>
|||Matthew,
in the client network utility, you can add an alias with the TCP/IP network
library. The server alias is any name you choose, and the server name can be
the IP address you have been using.
HTH,
Paul Ibison
Distributor
Hi
How do you clear out the distributor? I am able to clear down the log
using sp_repldone, sp_replflush if there are large errors. However,
how do I clear out the distributor assuming that it's trying to
execute a sproc that might not have been replicated?
Thank you
Simon
Drop the subscriber and recreate it using a no-sync subscription.
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
<simon@.otr-tech.com> wrote in message
news:1182337698.395239.178640@.c77g2000hse.googlegr oups.com...
> Hi
> How do you clear out the distributor? I am able to clear down the log
> using sp_repldone, sp_replflush if there are large errors. However,
> how do I clear out the distributor assuming that it's trying to
> execute a sproc that might not have been replicated?
> Thank you
> Simon
>
How do you clear out the distributor? I am able to clear down the log
using sp_repldone, sp_replflush if there are large errors. However,
how do I clear out the distributor assuming that it's trying to
execute a sproc that might not have been replicated?
Thank you
Simon
Drop the subscriber and recreate it using a no-sync subscription.
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
<simon@.otr-tech.com> wrote in message
news:1182337698.395239.178640@.c77g2000hse.googlegr oups.com...
> Hi
> How do you clear out the distributor? I am able to clear down the log
> using sp_repldone, sp_replflush if there are large errors. However,
> how do I clear out the distributor assuming that it's trying to
> execute a sproc that might not have been replicated?
> Thank you
> Simon
>
Saturday, February 25, 2012
Distribution Server - Replication
What happens when Distributor Server goes down in Transactional
Replication, no corruption (power , hardware failure)??
Thanks,
JohnCan you bring the distributor back up or the distribution database is lost? Before you bring the distributor back up, you need to shut down all subscriptions. If you can restart the distributor, you need to stop the logreader right after you have started the SQL Agent on the distributor. Open the subscription first to see if they can be synced. If the subscription doesn't errored out, open the logreader. Good luck!
Replication, no corruption (power , hardware failure)??
Thanks,
JohnCan you bring the distributor back up or the distribution database is lost? Before you bring the distributor back up, you need to shut down all subscriptions. If you can restart the distributor, you need to stop the logreader right after you have started the SQL Agent on the distributor. Open the subscription first to see if they can be synced. If the subscription doesn't errored out, open the logreader. Good luck!
Distribution Question
Hello
I'm just starting to look at replication for a job I have and I have a
question about setting up a distributor.
1. Can i have multiple distributors?
2. Is it possible to define which distributor is used for a certain
publication?
3. Can i set up the distributor to also be on the subscriber? (external to
the publisher)
Thanks for any and all help!
One distributor per publisher is what we can use.
The distributor can indeed be on the subscriber or the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I'm just starting to look at replication for a job I have and I have a
question about setting up a distributor.
1. Can i have multiple distributors?
2. Is it possible to define which distributor is used for a certain
publication?
3. Can i set up the distributor to also be on the subscriber? (external to
the publisher)
Thanks for any and all help!
One distributor per publisher is what we can use.
The distributor can indeed be on the subscriber or the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Distribution Database Log File Growth
SQL Server 2000 | Transactional Replication
Suspected Problem: Distribution Database Transaction Log Not Checkpointing
I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.
I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.
SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.
Any thoughts?
Jeff
I solved my own problem. The log file growth had nothing to do with it being the Distribution database. I stumbled upon a trace flag entry in the SQL Startup Parameters "-T3608" which is required to move certain system databases like Model (See article: http://support.microsoft.com/kb/224071/). The flag has been there for several months and was probably added the last time the server was rebuilt or storage was added. I removed the trace flag and checkpoints started occuring normally.
Distribution Database Log File Growth
SQL Server 2000 | Transactional Replication
Suspected Problem: Distribution Database Transaction Log Not Checkpointing
I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.
I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.
SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.
Any thoughts?
Jeff
I solved my own problem. The log file growth had nothing to do with it being the Distribution database. I stumbled upon a trace flag entry in the SQL Startup Parameters "-T3608" which is required to move certain system databases like Model (See article: http://support.microsoft.com/kb/224071/). The flag has been there for several months and was probably added the last time the server was rebuilt or storage was added. I removed the trace flag and checkpoints started occuring normally.
Subscribe to:
Posts (Atom)