I have a long story that I will condense to a few bullet points that leads up to my question
- 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.
My question is this. 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?