Re: Change distribution server

This is more of a maintenance question regarding replication.
With all the SQL Agent jobs and replication pieces, where is the best place
to monitor if any of the replication pieces fail and send an email?
Simply monitoring the SQL Agent jobs doesn't sound like it would catch all
the error scenarios.
I would like the monitoring to occur on the SQL2005 machine.
Remember my scenario has the remote distributor on SQL2000 if that matters.
This is for my scenario
--
Steve P


"SteveP" wrote:

Hilary,
That was the final straw to make the brick.
A good way to start the morning.

Thank you very much.
--
Steve P


"Hilary Cotter" wrote:

Have your SQL Server agent on the publisher run under an admin account and
add this account to the sysadmin role on the SQL 2005 distributor.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



"SteveP" <SteveP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B77BE596-B75A-4C6C-9489-FBE462DBC49E@xxxxxxxxxxxxxxxx
I now have the distributor and subscriber setup on SQL2005.
The publisher is setup on SQL2000. Thank you very much.
The LogReader sql agent job on SQL2005 is failing with following login
error:

2006-05-01 20:33:40.528 Login failed for user 'MYDOMAIN\WEBRPT02$'.TCP
Provider: An existing connection was forcibly closed by the remote
host.Communication link failure

My challenge is finding what security context this user is coming from.
SQL2005 SQL Agent is started under Local System Account if that matters.
It's not a user account that I have entered anywhere and it does not exist
on
either the SQL2000 or SQL2005 servers.

Thanks for you persistent help with this.
Steve.
--
Steve P


"Hilary Cotter" wrote:

You should be able to connect to your SQL 2000 server in Management
Studio
and create your publication there.

To get the SQL 2000 server to use the SQL 2005 distributor, connect to
the
SQL 2005 server, right click on the replication folder and select
Distributor Properties, click on the publishers tab, click add, and
browse
to find your SQL 2000 server you wish to have publish to this SQL 2005
distributor. Enter the Administrative Link Password.

Then connect to the SQL 2000 server, again, in Management Studio, right
click on the replication folder and select Configure Distributor, then
click
next at the splash screen, and in the select distributor dialog, select
use
another server, click add, and select your SQL 2005 server. Enter the
administrative link password you created before. Click Next,, and Finish.

Then expand the replication folder on the SQL 2000 server, select Local
Publications and create your publication as you normally would. If you
feel
uncomfortable with Management Studio you can create it in Enterprise
Manager.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



"SteveP" <SteveP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FCA63F51-E6A1-4B67-90E8-55EF0334F7EC@xxxxxxxxxxxxxxxx
I "Disabled Publishing" on the SQL2000 machine which removed the
publishing
and all associated subscriptions. I scripted the other non-related
subscriptions before doing this because the warning message seems
pretty
destructive.
I now have the SQL2000 database published.
The distributor is installed on the SQL2005 server.
I am trying to create the push subscription where all the agents run on
the
distributor.
The challenge is there is no convenient "Configure Publishing,
Subscribers,
Distribution" interface in SQL2005 like there is in SQL2000 - that I
have
found. The Replication Monitor does not provide this same type of
status
and
configuration capability. Thanks again.



--
Steve P


"Hilary Cotter" wrote:

Drop the subscriptions if possible.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business
intelligence.

This posting is my own and doesn't necessarily represent
RelevantNoise's
positions, strategies or opinions.

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



"SteveP" <SteveP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6A705835-0114-488E-BB5A-83F5A58F8D20@xxxxxxxxxxxxxxxx
Thanks for the reply. I received the following message:
Server: Msg 21008, Level 16, State 1, Procedure sp_dropdistributor,
Line
170
Cannot uninstall the Distributor because there are Subscribers
defined.

Another "challenge" to this scenario is that another database does
have
a
subscription to another SQL2000 server, unrelated to the replication
I
mention below, but still on the same SQL2000 server.
--
Steve P


"Hilary Cotter" wrote:

try sp_dropdistributor @ignore_distributor=1

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business
intelligence.

This posting is my own and doesn't necessarily represent
RelevantNoise's
positions, strategies or opinions.

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



"SteveP" <SteveP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EADAF280-E650-4735-AD28-AA0A632C6CCE@xxxxxxxxxxxxxxxx
I setup replication with SQL2000 publisher going to SQL2005
subscriber.
Distributor is on SQL2005 server. After initially setting this
up
the
SQL2005 server was repurposed and now I have a new SQL2005 server
(different
servername) to use.
Now when I try to configure SQL2000 replication from "Configure
Publishing,
Subscriptions, and Distributor" it tries to connect to the old
SQL2005
server
- which no longer exists and I get the "Distributor is not
available"
error
message - rightfully so.
I need to "point" the SQL2000 distributor information from the
old
SQL2005
to the new SQL2005 server.
sp_get_distributor still has the old SQL2005 server listed as the
"distribution server".
I cannot execute sp_dropdistributiondb because the old SQL2005
server
no
longer exists.

How can I redirect this toward the new SQL2005?
--
Steve P












.

Relevant Pages

  • Re: Change distribution server
    ... I now have the distributor and subscriber setup on SQL2005. ... SQL2005 SQL Agent is started under Local System Account if that matters. ... To get the SQL 2000 server to use the SQL 2005 distributor, ... subscriptions before doing this because the warning message seems pretty ...
    (microsoft.public.sqlserver.replication)
  • Re: SQLEXPRESS Replication?
    ... The way the Technet SQL BOL is arranged it is difficult to know just how to setup the required permissions to do replication which is the major stumbling block. ... You can replication from SQL Server 2005 Workgroup edition and above ... to a SQL Server Express subscriber using transactional replication. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2000 and MSDE/Express/Compact edition..
    ... What is does is downloads .Net Framework and SQL Server Express if they are not installed and then configures SQL Server as a subscriber. ... string DotNetFramework2Location = ... LogMe("Creation of Replication Account failed."); ...
    (microsoft.public.sqlserver.replication)
  • Re: Advice Needed: Asynchronous Replication/Mirroring to central o
    ... Looking for a SQL Server replication book? ... geographically-dispersed remote locations. ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscriber Transaction Logs Too Large
    ... Its not so critical to shrink the db files or transaction logs in SQL 2005. ... Director of Text Mining and Database Strategy ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)