August 1, 2016 at 2:45 am
Hi all
I'm trying to set up replication on a couple of databases and I've run across some issues.
Firstly the background.......
We had a new VM server spun up and it was named slightly differently to the original one (one ended 01 and the new ended 02).
SQL was installed on the new server with the default instance name.
The feeds from a 3rd-party app were stopped, the 01 server was renamed and the 02 server was renamed to end 01 (to stop a lot of code breaking)
The feeds from the 3rd-party app were restarted without any issues.
Mirroring was set up to a secondary server without any issues (as far as I know).
Now, I'm trying to set up replication (to try and get feeds to multiple servers) and hitting an error.
The error message is:-
"SQL Server is unable to connect to server XXXXX-XX01
SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name,'XXXXX-XX02. (Replication.Utilities)"
Clicking OK on this brings up the Connect to Server dialogue box.
I can't connect to XXXXX-XX02 because that server doesn't exist any more (it's been renamed XXXXX-XX01).
Running "SELECT @@ServerName" on the new XXXXX-XX01 server gives an output of XXXXX-XX02.
I know I need to do a drop-server followed by an add-server but I don't know if I can do this while the feeds are still live.
Any advice on this would be greatly appreciated.
August 1, 2016 at 3:20 am
Quick thought......
If I stop the SQLServer service, do the dropserver followed by addserver and then restart the service would that work?
I get the feeling I'm going to have to stop all the feeds and at least pause mirroring but wondered if stopping the service would work as everything appears to be using the hostname not the SQL name?
August 1, 2016 at 3:25 am
If you run sp_helpserver the one with id 0 is it still having the old name?
If yes, try renaming this way
https://msdn.microsoft.com/en-us/library/ms143799.aspx
edit: added link
August 1, 2016 at 3:33 am
The server with ID 0 is still listed with the 02 ending.
August 1, 2016 at 3:47 am
I have faced this same issue long back with replication and solved with rename.
As per the link I shared you you need to reestablish the mirroring with new name.
August 1, 2016 at 4:04 am
I did think we'd have issues when they first proposed doing it like this.
Looks like someone (probably me) has a lot of work to do shortly to try and sort it out.
August 2, 2016 at 8:28 am
Run the drop server then add server they should run instantaneously
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 2, 2016 at 8:46 am
Thanks Perry
But can we run them when the databases are always being written to (unless we turn off the feeds)?
August 2, 2016 at 8:52 am
I have done in the past, if you're really uncomfortable get a maintenance window arranged
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 2, 2016 at 8:59 am
Thanks Perry
Our next maintenance window is in around 4 weeks so it might have to wait for that.
At worst case scenario, what would happen if we did this without turning the feeds off?
As far as I know, the applications that feed the data ion are using the host-name which is correct.
August 2, 2016 at 9:59 am
This is mainly used for internal operations and things like linked servers , etc.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 3, 2016 at 1:52 am
It sounds like it would affect mirroring as well then.
Looks like we will have to wait for the maintenance window if that's the case.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply