Sql replication setup

Next, the New Publication Wizard appears and outlines the general information about creating Publication. In the Publication Database page, select the Adventureworks database and click Next.

On the Publication Type page, select Transactional publication , and click Next. Now, on the Articles page, choose the articles should be part of this publication.

In this case, 2 tables, 1 Stored Procedures are selected. Next, in the Filter Table Rows page, define filters that should be applied to your articles.

Let us go with the default values. In the Snapshot Agent page specify when to run the Snapshot Agent. It can be run immediately or it can schedule to run at a later time. In this case, Create a snapshot immediately is used.

Now, in the Agent Security page, specify the account to use to run the Snapshot Agent using Security settings…. You can create the Publication immediately or save the configuration in the script file to run at a later time. The final step in setting up replication is to create the subscription. On the next page, we see the status of each task performed by the distribution wizard. You can note here it failed to change the SQL Server agent service to automatic mode because in my demo the service account does not have administrative permissions on the server.

We can ignore this error message in the distribution wizard. It opens the publication wizard with brief information about the wizard. Select the publication database. In my demo, DBARepository database is part of the availability group, choose this database and click Next. Select the publication type on the next page. We require a transaction replication in this article. In the articles page, select the objects that we wish to replicate.

In my case, I have a single article eligible for transactional replication. Select the article and click Next. You can schedule a Snapshot agent run at a specific schedule or run it immediately. Select the first option and create a snapshot immediately.

In the agent security, we configure the account under which these agent services run. It is good to configure these services under the SQL Server Agent service account however you can change it as per your security requirements. We have provided all inputs in the publication wizard. Select the option- Create the publication, as shown below. Specify an appropriate publication name, verify the publication summary and click on Finish. Either data movement is suspended or the availability replica is not enabled for read access.

To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. This is expected behavior. You must verify the presence of the subscriber server entries at these secondary replica hosts by querying for the sysserver entries directly at the host.

Replication Administration FAQ. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful? Please rate your experience Yes No. Any additional feedback? Note If any modified replication agents run on a computer other than the distributor, use of Windows authentication for the connection to the primary will require Kerberos authentication to be configured for the communication between the replica host computers.

Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale POS applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.

For backward compatibility information see, Replication Backward Compatibility.