October 19, 2011 at 10:59 am
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 7:18 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 21, 2011 at 6:26 am
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. 😀
October 25, 2011 at 4:22 am
I hope you do not get any other side-effects from restoring master from another system. IMHO this is a high-risk operation that I would do my best to avoid. It is far safer to script out everything you need to keep from the old system and script it into the new system.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 26, 2011 at 2:01 am
You dont need to restore master. I do this on a fairly regular basis.
you need to run sp_subscription_cleanup
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply