Thanks to BrentOzar for hosting this month of T-SQL Tuesday. Please find the original post here.
Brent asked us to share the last issue that we resolved. I recently resolved a replication issue. I completed a transactional replication which is set between 4 servers. Let’s say, A, B, C, and D servers. A replicates to B which then replicates to C and then from Server C to Server D. The setup is complicated. On a regular process, Server A needs to import the fresh data on Server B tables that are involved in replication. It is not possible to drop the tables as they are currently involved in replication to B Server which then replicates the data to Server C. To make this possible, publications have to be dropped on Server B and Server C before importing fresh data from Server A to Server B. Once the tables are created on the Server B, then the publications are recreated.
As all these servers are involved and linked through transactional replication on the same tables, and publications are created on each of these servers, it gets complicated to drop and recreate the publications on Server B and Server C. We tried automating the process by creating the drop and recreate publication SQL agent jobs and using triggers to trigger the execution of the jobs one by one.
I know we can use other technologies in SQL Server to accomplish the same goal but the requirements and the limitations from vendors made us to stick with this plan.
So, the setup is complete. Soon after, I saw one of the recreate publication jobs fail due to the replication agent account being locked, and the downstream jobs all failed as they were interlinked to each other. Though the resolution is simple to enable the locked account, it is hard to figure out that this is the reason for the replication being broken. Especially, when these jobs fail across multiple servers.
I enabled the replication agent account and rerun the job to success. Then manually executed each of those failed jobs to succeed on the rest of the Servers. I am currently in the process of figuring out why the account is locked in the first place.
I know it is challenging to maintain the replication but it gets even tougher with complicated replication setups across multiple servers.
This is the last issue that I fixed the last Saturday night at 1am, LOL. Glad the issue is resolved.
Though the setup is tough and things seem complicated, I was able to successfully migrate the servers from older versions to new versions without breaking the replication setup that is required between multiple servers.
I am looking forward to reading the other SQL family posts on this month’s T-SQL Tuesday.
Thank you for reading!
Deepthi Goguri's SQL Server Blog 
It’s evident that you handled the situation with skill and dedication, even working late into the night to ensure the issue was resolved promptly. Your commitment to maintaining the integrity and functionality of the replication setup is invaluable to the smooth operation of the SQL Server infrastructure. Great job in resolving the issue effectively and ensuring the continuity of replication across your servers!
To determine the specific cause of the account lockout, you can check the Windows Event Logs on the server where the account is locked out. Look for event entries related to account lockouts, which often provide information about the source of the failed login attempts (e.g., specific IP address, workstation name) and the reason for the lockout (e.g., bad password attempts, account lockout policy).
LikeLiked by 1 person