1

We have a pair of SQL Server 2012 availability groups that we have set up transactional replication to a 2012 distributor and subscriber.

We are moving databases for the (synchronized) secondary nodes to new faster less congested hard drives. Since these secondaries are synchronized, the transactions never commit and replication doesn't happen.

According to the BOL sp_redirect_publisher should be used if the replica on the primary is taken offline. However it doesn't appear to have a scenario quite like what I'm looking for.

Has anyone had to move database files of an AG database to new drive media while that db was being published? If so what steps did you take?

1

Apparently I was overthinking the problem. Running the following:

Exec dbo.sp_redirect_publisher @original_publisher   =  'SQLServer03',
                               @publisher_db         =  'LMDB25',
                               @redirected_publisher =  NULL;
Go

where @original_publisher is the currently and original primary node, and @publisher_db is the database name.

To replace the redirects so that replication will work normally again simply re-run the command changing the NULL parameter value to the server node name of the previously offline server node:

Exec dbo.sp_redirect_publisher @original_publisher   =  'SQLServer03',
                               @publisher_db         =  'LMDB25',
                               @redirected_publisher =  'SQLServer04';
Go

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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