| Author |
Topic |
|
cnadler3
Starting Member
9 Posts |
 Posted - 2009-06-22 : 13:13:48
|
| We have a SQL server that was renamed. Replication was enabled when the server was renamed. When I right click on replication folder and choose publisher properties, the distributor name is the old server. The sql server instance and distributor does not match.There is also no distribution database.How can I change the name or remove replication from the server and start over?Thanks! |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-23 : 11:58:12
|
| I am confused.. did you rename your publisher or distributor? or both?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-23 : 11:58:32
|
| you can remove replication using sp_removedbreplication.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
cnadler3
Starting Member
9 Posts |
Posted - 2009-06-23 : 15:24:12
|
| The distributor is defined in the publisher properties. The distributor name is wrong and needs to change. Are the publisher and distributor different? |
|
|
|
cnadler3
Starting Member
9 Posts |
Posted - 2009-06-23 : 15:28:20
|
| Ok, I ran the command and it completed successfully, thanks. Now when I right click on the Replication folder and select configure distribution, I receive the error, " SQL server is unable to connect to server 'CORRECT NAME'. SQL Server replication requires the actual server name to make a connection to the server. connections through server alias, IP address, or any other alternate names are not support, specify the actual server name 'WRONG SERVER'. how can I fix this?Thanks! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-23 : 15:34:12
|
| When you run this, do you see the correct name?select * from sys.serverswhere server_id = 0Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
cnadler3
Starting Member
9 Posts |
Posted - 2009-06-23 : 18:31:37
|
| I actually have two servers that are having this problem. One one of the servers (the production) yes, the correct name appears. However, on the secondary server the incorrect name shows, it is the same name that the distributor had. How do I change that name on the secondary server? Also, any idea how to completely remove replication from both servers? I would like to set replication up from scratch. I appreciate your help,Thanks,Chris |
|
|
|
cnadler3
Starting Member
9 Posts |
Posted - 2009-06-23 : 18:43:19
|
| Actually, i think i see the problem, not sure how to fix though. I ran the query select *from sys.servers and server id 0 is the correct name and has the correct data source, but server id 1's name is repl_distributor, but the data source says the same name that the distributor does, the wrong name.How can I change that? |
|
|
|
cnadler3
Starting Member
9 Posts |
Posted - 2009-06-23 : 18:44:38
|
| And after running the sp_removedbreplication command on the secondary server, select * from sys.servers only returns one entry, which is the wrong server name. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-24 : 09:55:32
|
| the server name with server_id = 0 is the name of the local server. This sys table also lists any linked servers. so if you have distributor server name in it dont worry. I think you need to clean up your replication first. run the sp_removedbreplication on all the servers - publisher, subscriber and distributor. then query the sys.servers and check the names of your servers. Use sp_addserver and sp_dropserver to change your server names. If possible, restart the servers and run the queries again to verify if the server comes up with correct names.. you could also run SELECT @@Servername to get the name.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
cnadler3
Starting Member
9 Posts |
Posted - 2009-07-28 : 13:16:24
|
| I was able to remove replication from one sql server, but I am still having problems configuring replication on another server. When I right click on Replication within SQL studio manager and choose Configure Distribution, I get an hour glass for about a minute and then nothing. If I expand replication and right click on Local Publications and select new publication I get an hour glass for about a minute and then nothing. If I right click on Local Subscriptions and choose new subscription, the new subscription wizard appears.I have ran the sp_removedbreplcation and also restart the server. Any idea how I can fix this problem?Thanks! |
|
|
|
cnadler3
Starting Member
9 Posts |
Posted - 2009-07-28 : 23:27:24
|
| Also, both servers return the correct values for the name. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-29 : 00:17:03
|
| I hope you have the scripts, or at least know/have documented the articles for replication. shouldn't have dropped publications until you were fairly certain you could recreate on new distributor.When changing the distributor, first and foremost, you need to verify connectivity to the distributor BEFORE doing anything else, like dropping publications. Then you need to script out all of your publications.I'll make 2 copies of the scripts: 1 for rollback purposes and the other to modify the distributor (roll forward).1. Configure new server as distributor2. Make sure all replicated transactions are applied at the subscriber(s), when possible.3. Drop subscriptions4. Disable replication on publisher / drop publications5. Re-enable replication on publisher with new server as distributor6. Recreate publications (with new distributor)7. Recreate subscriptions (using no_sync if all transactions were quiesced before dropping publications)If you can't quiesce the data, just recreate and re-snaphot.This can all be scripted from existing publications (if you hadn't dropped 'em) pretty quickly, then all you need to do is change the distributor in scripts, drop subscriptions/publications and execute script(s) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-29 : 00:23:37
|
| ok, now...why can't you connect to new distributor?what account did you specify for distribution agent? does that account have permission on new server?can you connect to it via Management Studio using YOUR credentials?Problem is likely the account you ticked in the distributor agent security properties.if you can't connect, may be a server configuration issue, but more likely is a replication configuration issue.EDIT: Sorry, just realized it's a renamed server, not new server. Same steps apply as earlier post. May need to set SPN now too |
|
|
|
cnadler3
Starting Member
9 Posts |
Posted - 2009-07-29 : 12:56:35
|
| The server was renamed and the distributor kept the old sql instance name. When I would right click replication and choose configure distribution, the properties window would appear and I was able to see the incorrect name. It would not allow me to change it. It was just a grey box witih the incorrect name in it. Since then I have ran the command sp_removedbreplication and also disabled replication. Now when I right click on replication and choose configure distribution, I receive an hour glass and then nothing. I am connecting to SQL studio manager using SA creds. |
|
|
|
weiei
Starting Member
1 Post |
Posted - 2010-06-20 : 11:46:44
|
| I have same problem as yous, after changing the server name, the repl_distributor data source keeps the old server name, did you fix it? thanks |
|
|
|
|
Change Distributor