# How unsafe is it to change the data source of repl_distributor on a publisher

Here's my situation.

• Reporting Server (SQL 2008) is distributor/subscriber cluster for a SQL 2000 Transactional Publication
• Reporting Server was migrated to new SAN
• Reporting Server fatally crashed upon completion of migration (model db was corrupted)
• I installed another instance of SQL on a node of the cluster, attached dbs, and changed report connections to use this new instance
• Stupidly, I attached the [distribution] database to this new instance
• select * from sysservers (rightly) returns that the distributor is still the first instance on the cluster.

How terribly would it break things if I ran the following on the publisher (or is this even possible)?

sp_configure ‘allow updates’, 1
GO
RECONFIGURE WITH OVERRIDE
GO

update sysservers set datasource = 'server\instance' where srvname = 'repl_distributor';
GO

sp_configure ‘allow updates’, 0
GO
RECONFIGURE WITH OVERRIDE
GO


Is this even the best way, or is there an alternative way to change the distributor?

-

## 1 Answer

Know this is an old question but we've just been working with this, Last month we had to move our distribution server to a brand new machine, (Setup is two machines in AG stand alone distributor and a reporting server)

The only real way we found to move distributor server is to completely tear down and rebuild replication, this can be done with right clicking replication and generate scripts

bare in mind you'll need to do this on the publishers and the new distributor.

We've just had an issue where our AG group didn't take the distributor change, and since its a read only node we're doing some experiments with different ways to do that,

our tests of updating the [master].[sys].[sysobjvalues] table (which is where the 'datasource' value is actually stored (the sysservers is just a view) have resulted promising results if you want the server to never work ever again. Tearing things down and rebuilding it is painful, but its by far the safest thing to do.

Ste

-