SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Unable to Create a Publication
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jbates99
Constraint Violating Yak Guru

393 Posts

Posted - 07/28/2014 :  14:29:20  Show Profile  Reply with Quote
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

USA
37466 Posts

Posted - 07/28/2014 :  15:39:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

393 Posts

Posted - 07/28/2014 :  16:50:57  Show Profile  Reply with Quote
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

USA
37466 Posts

Posted - 07/28/2014 :  17:13:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

393 Posts

Posted - 07/28/2014 :  18:42:24  Show Profile  Reply with Quote
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

USA
37466 Posts

Posted - 07/28/2014 :  18:54:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

393 Posts

Posted - 07/28/2014 :  19:49:25  Show Profile  Reply with Quote
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

393 Posts

Posted - 07/28/2014 :  19:59:15  Show Profile  Reply with Quote
.. 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

USA
37466 Posts

Posted - 07/28/2014 :  20:28:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

393 Posts

Posted - 08/12/2014 :  21:42:22  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000