Msg 21892, Level 16, State 1, Procedure sp_hadr_validate_replica_hosts_as_publishers, Line 70 Unable to query sys.availability_replicas at the availability group primary associated with virtual network name 'AG2Listener' for the server names of the member replicas: error = 18456, error message = Error 18456, Level 14, State 1, Message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'..',

Recently i have configured Transactional replication on Always on availability group databases. And i came across below errors . My configuration is like below

NODE1-->Orginal Pubisher and Primary replica
NODE2-->Secondary replica
NODE3-->Distributor server and not part of any replica
NODE4--> Subscriber server.

 I got this error when i am running the below query after remotely login in into NODE1 server which is primary replica server and from there i connected to NODE3 server which is a distributor and i have ran the query and got the below error.


So this time i have logged into the NODE3 which is a distributor server remotely and ran the below query and again i got the below error.
So again i ran the sp_adddistpublisher command from the distributor server which is NODE3 and i got the below error
After that i have observed one thing on the environment, if you connect to the NODE2(Original Secondary replica) right click on replication tab, you will not find Publisher properties tab, where as you can see the same under NODE1(OriginalPublisher)  that means NODE2 secondary replica is  really promoted as a Publisher but we have not specified distributor server name 
to NODE2. To do this i went to NODE2 secondary replica and right click on Replication folder and click on Configure distribution and choose NODE3 as distributor server,dont chose "This will act as its own distributor". And click on below and next buttons, because of this action what happend is you have cofigured NODE3 as distributor server in NODE2(Secondary replica)

 Once this done you can successfully configure subscription without any problem.

Comments

Post a Comment

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Pre login Handshake or Connection Timeout Period

Image
Service was unable to open new database connection when requested. SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16225; handshake=14840; Connection string - Data Source=sqlserverintancenamehere;Initial Catalog=CatalogNamehere;Integrated Security=True;Persist Security Info=False;Max Pool Size=1000;Connect Timeout=20;Load Balance Timeout=120;Packet Size=4096;Application Name=Applicationname hereBusinessLayerHost.exe;Workstation ID=HostnameHere setspn -a MSSQLSvc/hostname.doaminname.net:1433 ServiceAccountNameHere need to run the above command in a command prompt, most of the time you would NOT have permissions to run this, please check with Active Directory Team or Wi...
Read more

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server

Image
/*  There is only one master key, you can not create  multiple */ use master go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPasswordHere' GO /*  Creating certificate in the master database, after creating  master key */ use master GO CREATE CERTIFICATE MyFirstCert WITH SUBJECT = 'TDECertificate' ; GO /* Enable databse encryption key but you can not see under this specific user database. */ USE G GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyFirstCert GO ALTER DATABASE G SET ENCRYPTION ON;  /*  Backing up certificate */ use master go BACKUP CERTIFICATE MyFirstCert TO FILE = 'C:\Certificates\MyFirstCert' WITH PRIVATE KEY ( FILE = 'C:\Certificates\MyFirstCertKey' , ENCRYPTION ...
Read more