Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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

sp_configure ‘allow updates’, 0

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

share|improve this question

1 Answer 1

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.


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.