October 19, 2011 at 1:00 pm
Upgrading Server Hardware and changing from Default Instance of SQL Server 2005 Standard 32bit on Windows 2003 32 bit to New Server Hardware with Named Instance of SQL Server 2005 Standard 64bit on Windows 2008 R2 .
I built the new server, created the named instance, restored the system DBs, and restored the user DBs.
My problem: Now I can't drop, rebuild, configure, or do anything with my transactional replication.
I receive the following error message:
An error occurred connecting to Publisher 'NewServer\Instance'
Additional Information: SQL Server requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternative name are not supported. Specify the actual server name, 'OldServerName'. (Replication.Utilities)
Where is this former server name stored? How do I wipe out everything in replication related to old server so I can start from scratch and use my modified replication scripts from the old server to rebuild replication? I would think there is a simple script that would clean up all this old information so you can rebuild replication on your new server.
I can't believe I'm the first one to encounter this problem but I've spent a few days looking around and haven't found a solution.
Thanks in advance to anyone that can help!
October 20, 2011 at 9:34 am
Additional info: I've already followed the steps outlined in the following article which did not solve my problem.
http://msdn.microsoft.com/en-us/library/ms147921(v=SQL.90).aspx
How to: Disable Publishing and Distribution (Replication Transact-SQL Programming)
sp_dropsubscriber 'servername', @Ignore_distributor =1
sp_removedbreplication
use [master]
exec sp_dropdistributor @no_checks = 1, @Ignore_distributor =1
All commands ran successfully but I still receive same error when I try to configure publishing
Thanks in advance to anyone that can help!
October 20, 2011 at 4:10 pm
Replication is one of those nasty things that it keeps having a number of hooks. What you may want to do is completely destroy replication on that box and rebuild it from scratch. I've done this a number of times, especially when confronted with a cluster failover or shared folders become unshared. It kind of freaks out.
So, the steps that I follow are:
For each publication,
- delete subscribers
- delete publisher
In Mgmt Studio, right click on Replication
- Disable Publishing and Distribution
- - Yes, disable publishing on this server
- - Go through the rest of the wizard (I save the scripts, too)
When rebuilding, sometimes it does want the actual server name, not the instance name. Talk to your windows guys about the actual name and use that.
October 21, 2011 at 6:15 am
Thanks for the reply andre.quitta.
However, that is what I was trying to do when I was recieving the error message.
In Mgmt Studio, whe I right clicked on Replication, and
selected Disable Publishing and Distribution
I immediately received the error message:
An error occurred connecting to Publisher 'NewServer\Instance'
Additional Information: SQL Server requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternative name are not supported. Specify the actual server name, 'OldServerName'. (Replication.Utilities)
I found the solution on another forum and it's one of those that you say 'Duh' when you see it. It's the same commands you use as if you were renaming the instance after renaming a server. In this case I had restored a master database from another server with a different server/instance name.
The solution was to correct the server/instance name in the restored master database.
sp_dropserver <old_instance_name>
GO
sp_addserver <new_servername\instancename>, local
GO
Now I can configure replication. 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply