Setting Up Replication for SQL Server

Following are steps to enable distributor for SQL Server running on an EC2 instance. It should work for other machines too.

 

Create ReplData directory

ReplData directory is where replication data will be stored for databases. The following command creates the directory and assigns required permissions:

sudo mkdir /var/opt/mssql/data/ReplData/
sudo chown mssql /var/opt/mssql/data/ReplData/
sudo chgrp mssql /var/opt/mssql/data/ReplData/

 

Enable Agent XPs

This step is optional. If Agent XPs is not running (disabled) on the server, then you might see some warning messages while enabling replication.

Follow this guide to check if Agent XPs is disabled. If disabled then follow all the steps in the guide, and execute the following command in the EC2 instance

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

Restart the SQL Server service again, and sp_configure should show 1 as the value for run_value.

 

Enable Replication

Find SQL Server name using the following T-SQL command 

select @@servername

Use it to set servername in this SQL script. Also, set a suitable passphrase in the script.

Execute the script to enable replication.

Attachments

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request