Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Unable to Create a Publication

Author  Topic 

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-07-28 : 14:29:20
hi all,

I need to create a publication but a former replcation configuration is preventing that.
Using the Wizard, it gets to "Creating Publication", then fails with error "A log reader agent can only be installed for a database enabled for transactional/snapshot replication. (Microsoft.SqlServer.Rmo)"

The former configuration includes a distributor that is on a DIFFERENT server, not the publisher server.

At this point, I believe its best to just Disable publishing and Distribution but that fails as well.

error "Could not connect to server AAA because is not defined as a remote server" error 18482

Please offer some advice.
Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 15:39:10
Have you tried sp_removedbreplication on the publisher database? I always start there when removing replication errors.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-07-28 : 16:50:57
Yes I ran sp_removedbreplication on the publisher db, Tara, It completed successfully but I don't think it did anything.
We're dealing with a botched repl configuration done by my predecessor. Somehow, Server A (publisher) is co-mingled with Server B (where the distributor database is)
Now, I need to configure transactional replication on Server A but have hit a wall after trying many things.
2 points:
- Server A and Server B are production servers. I can take them down but only briefly
- I DO have the option of uninstalling the replication feature on Server B because the server will soon be retired. I can also uninstall the replication feature on Server A but will have to reinstall because I need to have trans. repl for 2 batabases over to new Server C.

But I'm not sure that uninstalling repl will clean up the fragments in the system databases. These are production servers and I don't want to jack it up to the point of having to reinstall SQL Server :--)

Any thoughts or tips? Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 17:13:26
Don't uninstall replication if you need it. Though there are ways to reinstall it, you might not be able to get it to work even after working with Microsoft on it. Trust me, been there done that.

Try adding the AAA server as a remote server or linked server on the distributor to get around that error.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-07-28 : 18:42:24
Both servers have a linked server for the other server. However, I discovered that linked server repl_distributor exists on both servers. They use the distributo_admin login. When I run 'Test Connestion' for each repl_distributor linked servers,
the test fails with error "Server 'repl_distributor' is not configured for DATA ACCESS". What the heck does that mean, do you know?
Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 18:54:29
Modify the linked server. It's on the Server Options page in the linked server properties. It's the Data Access field. Switch it to True.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-07-28 : 19:49:25
I had already changed Data Access to True on both servers, Tara. Sorry should have mentioned that. It's looking pretty bleak at this point.
Go to Top of Page

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-07-28 : 19:59:15
.. reply continued. I assume the distribution_admin, which is associated with the linked server repl_distributor, is created by SQL Server when you configure snapshot/transactional replication? I would like to test that login but don't know the password. I don't see a related login failure in the logs though.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 20:28:40
Yes they are setup by replication. The password is the one that was used when the distributor was setup.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2014-08-12 : 21:42:22
Just to close out this discussion. We gave up on trying to get transactional replication to work on this 2008 R2 instance after someone else botched-up an attempt to do ... something 2 years ago?
I'll revisit this later, may open an incident with Microsoft. Thanks to the SQL Goddess for her ideas.
Go to Top of Page
   

- Advertisement -