SQL Service account and Transactional Replication

Question

We have sql server 2014 running standard edition. It has transaction replication configured to replicate data over to External SQL Instance. Now we are planning to modify the service account sql is running on . Does this going to impact the Replication ? What steps need to be taken before that ?

Answer 1

Hi @Banu Manjappa
Please follow the Microsoft document if you want to update the password of the replication agent https://learn.microsoft.com/en-us/sql/relational-databases/replication/security/view-and-modify-replication-security-settings?view=sql-server-ver16 As per doc- you might want to change the connection of the Log Reader Agent to the Publisher from SQL Server Authentication to Windows Integrated Authentication, or you might need to change the credentials used to run an agent job when the Windows account password has changed. Thank you!

Answer 2

Changing the SQL Server service account can potentially impact your replication setup if the service account is used for security contexts within the replication topology. Here are some steps you should consider before making this change: Take a Backup: Take a backup of your publisher and subscriber databases. This will give you a recovery point in case anything goes wrong. Document Current Settings: Document your current replication settings, including all publisher, distributor, and subscriber settings, replicated articles and any custom procedures or scripts related to replication. Impact Analysis: Check if the current SQL Server service account is being used explicitly anywhere in your replication setup. Look at the connections between the Publisher, Distributor, and Subscriber(s). If the service account is used anywhere (for example, in jobs or agent profiles), those will need updating. Change Service Account: Change the service account from the SQL Server Configuration Manager as it will automatically assign the necessary permissions for the new account. Update Jobs and Agent Profiles: Update any SQL Server Agent jobs related to replication that might be running under the old account to run under the new one. Also check the Replication Agents (Snapshot, Log Reader, Distribution, and Merge) to see if they're using the service account. If so, update them accordingly. Restart SQL Service: After changing the service account, you'll need to restart the SQL Server service for the changes to take effect. Test: Once completed, validate the replication setup by checking the replication status, and by doing test transactions on the publisher database.

Remember, it's important to communicate with your team about the planned changes and potential downtime. In case issues occur or cannot be resolved promptly, be prepared to roll back to the original state using the backup taken earlier.