Replication Failover

Question

Replication Failover

antares_dark2

Old Hand

Points: 371
More actions
December 29, 2015 at 5:46 am

#314483

Hello, I have a SQL 2014 Replication with AlwaysOn Availability Group configured in 2 nodes (SRVSQL1 and SRVSQL2), 1 distributor (SRVSQL3) and a listener called 'SQL', configuration was made following this tutorial http://www.techbrothersit.com/2015/07/how-to-setup-replication-with-alwayson.html and works fine except when I do a failover from SRVSQL1 to SRVSQL2, when it occurs subscriber continue taking SRVSQL1 as primary and updates on subscriber take place because SRVSQL1 continue in read mode, but if I shut down the SRVSQL1 (simulating a harware failure) the subscriber lost connection with SRVSQL1 and no replication occurs, the susbcriber continue waiting for SRVSQL1 that may be unavailable for a long time.
I've tested with sp_redirect_publisher and sp_get_redirected_publisher returns me the new publisher SRVSQL2, but without changes on subscriber...
Also I've deleted the subscription and configured again from scratch, but it continues linked to SRVSQL1 wich is not available...
What I'm missing?
How can I tell to all subscribers (30 approx) or distributor that the current publisher is SRVSQL2 ?
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 1

https://msdn.microsoft.com/en-US/library/hh710046.aspx

5. Redirect the Original Publisher to the AG Listener Name
--------------------------------------------------------------------------------
At the distributor, in the distribution database, run the stored procedure sp_redirect_publisher to associate the original publisher and the published database with the availability group listener name of the availability group.
USE distribution;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = 'MyPublisher',
@publisher_db = 'MyPublishedDB',
@redirected_publisher = 'MyAGListenerName';

which is "SQL" (not "SRVSQL2")

And

At each secondary replica host, make sure that the push subscribers of the database publications appear as linked servers.

Plus the steps I omitted.

Or are you using pull subscriptions?

antares_dark2 Old Hand Points: 371 More actions · Answer 2

In my case this will be:

Run on the distributor (SRVSQL3)

USE distribution;

GO

EXEC sys.sp_redirect_publisher

@original_publisher = 'SRVSQL2', -- the new publisher

@publisher_db = 'MyPublishedDB',

@redirected_publisher = 'SQL'; -- my AG Listener

Later I check with:

DECLARE @redirected_publisher sysname;

EXEC sys.sp_validate_replica_hosts_as_publishers

@original_publisher = 'SRVSQL2', -- the new publisher

@publisher_db = 'MyPublishedDB',

@redirected_publisher = @redirected_publisher output;

select @redirected_publisher

And I get 'SQL' as result, that's fine?

My subscriptions are Push subscriptios, run agent on distributor (SRVSQL3)

And yes, my push subscribers are listed as linked servers in both publishers and distributor.

antares_dark2 Old Hand Points: 371 More actions · Answer 3

Also, under my Replication Monitor after a faillover I can see the publication still depends on SRVSQL1 and SRVSQL2 is empty but acting like primary node :crazy:

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 4

For your last question see 7 in https://msdn.microsoft.com/en-US/library/hh710046.aspx

Can you post your results for each step (including step 4) from that URL?

antares_dark2 Old Hand Points: 371 More actions · Answer 5

Yes, sure!

I'll start over again from scratch and log results

Just one clarification, I'm using SQL Server 2014 and the link you provided is intended for the upcoming SQL Server 2016

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 6

Just below the title, to the right of "SQL Server 2016" there is an "Other Versions" link. Click that.

antares_dark2 Old Hand Points: 371 More actions · Answer 7

Hello, I'm again dealing with this, and it's driving me crazy :w00t:

I've start over again and folowed the MS steps https://msdn.microsoft.com/en-US/library/hh710046(v=sql.120).aspx, most of them give the result => 'Command(s) completed successfully.'

After manually failover from SRVSQL1 to SRVSQL2, I go to distributor SRVSQL3 and execute the following code:

-- run @distributor SRVSQL3

USE distribution;

GO

EXEC sys.sp_redirect_publisher

@original_publisher = 'SRVSQL2',

@publisher_db = 'SIPS',

@redirected_publisher = 'SQL';

-- results => 'Command(s) completed successfully.'

USE distribution;

GO

DECLARE @redirected_publisher sysname;

EXEC sys.sp_validate_replica_hosts_as_publishers

@original_publisher = 'SRVSQL2',

@publisher_db = 'SIPS',

@redirected_publisher = @redirected_publisher output;

SELECT @redirected_publisher

-- results => 'SQL'

And all works like yesterday... 🙁

In the Replication Monitor window the publication continue as a child of SRVSQL1 and SRVSQL2 (now primary) is empty.

Also I've changed the AG so secondary replicas being not readable (thought this could force change Publisher), but nothing happend

I can't see what's wrong or what step I'm missing

duport_th Valued Member Points: 55 More actions · Answer 8

Hi,

I have the same problem and no solution... If the primary server shutdown... the replication with always On shutdown too...

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 9

Enable trace flag 1448 on all AG nodes. Without that trace flag, transaction log reader agent STOPS if any asynchronous AG replica is down. That makes transactional replication of AG even less available than a single node (non AG) setup, as all AG nodes need to be "up" for replication to work. That TF should be a default IMHO, but unfortunately it isn't (yet).

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths