Sql merge replication requirements

The following questions and answers provide guidance on a variety of tasks faced by administrators of replicated databases. Activity can continue on a database while a publication is being created. Be aware that producing a snapshot can be resource-intensive, so it is best to generate snapshots during periods of lower activity on the database by default a snapshot is generated when you complete the New Publication Wizard. For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.

For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.

sql merge replication requirements

Because locks prevent other users from updating the tables, the Snapshot Agent should be scheduled to execute during periods of lower activity on the database, especially for snapshot publications.

A subscription is available after the snapshot has been applied to the subscription database. Even though the subscription database is accessible prior to this, the database should not be used until after the snapshot has been applied. Use Replication Monitor to check the status of snapshot generation and application:. The snapshot is generated by the Snapshot Agent. View the status of snapshot generation on the Agents tab for a publication in Replication Monitor.

For more information, see View information and perform tasks using Replication Monitor. The snapshot is applied by the Distribution Agent or Merge Agent. However, you must be aware of the following:. If the Distribution Agent or Merge Agent is configured to run continuously, the agent applies the snapshot automatically after the Snapshot Agent completes.

If the Distribution Agent or Merge Agent is configured to run on a schedule or on-demand, and there is no snapshot available when the agent runs, the agent will shut down with a message stating that a snapshot is not yet available. You must run the agent again to apply the snapshot after the Snapshot Agent has completed. Scripting the replication configuration is a key part of any disaster recovery plan for a replication topology.

For more information on scripting, see Scripting Replication. Replication functions properly using any of the recovery models: simple, bulk-logged, or full. Merge replication tracks change by storing information in metadata tables. Transactional replication tracks changes by marking the transaction log, but this marking process is not affected by the recovery model. To track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table.

To accomplish this:. If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed.

A filter must not include the rowguidcol used by replication to identify rows. By default this is the column added at the time you set up merge replication and is named rowguid. Transactional replication requires a primary key constraint on each published table. Merge replication does not require a primary key, but if one is present, it must be replicated. Snapshot replication does not require a primary key.

By default, primary key constraints, indexes, and check constraints are replicated to Subscribers.Avoid replicating text, ntext and image columns. These data types require more storage space and processing than other column data types. This parameter specifies whether the output should be verbose.

Some tips for using Merge Replication in SQL Server 2016 (Part 1)

There are three available values: 0 — only error messages are printed 1 — all of the progress report messages are printed 2 — all error messages and progress report messages are printed The default value of the -OutputVerboseLevel parameter is 2. You can increase performance when only error messages are printed. So, during the testing period you can use the default values of this parameter, but when you move your databases to production, consider setting the -OutputVerboseLevel parameter to 0.

If you do not use indexes on columns used in filters the SQL Server must perform a table scan, which can results in performance degradation. Consider specifying that Subscribers can initiate snapshot generation during the initial synchronization.

In SQL Server merge publications with parameterized filters can be difficult to manage with hundreds or thousands of Subscribers. In this case, you can specify that Subscribers can initiate snapshot generation during the initial synchronization. This option is used to set a maximum amount of memory allocated to SQL Server. You can specify this option to avoid having the operating system page to disc for memory. You can also set this option when SQL Server works on the same computer with other applications.

These stored procedures provide replication support for AlwaysOn Availability Groups. So, you can use them to get advantages from the AlwaysOn Availability Groups. Use precomputed partitions for filtered merge publications. When the precomputed partitions are used, then when a Subscriber synchronizes with the Publisher, it can immediately start to download changes relevant to its partition without having to go through the partition evaluation process.

This can significantly improve merge replication performance when a publication has a large number of changes, Subscribers, or articles in the publication. Consider using vertical and horizontal filtering to restrict the amount of data that need to be replicated. This can results in good performance benefits, because SQL Server will publish only the amount of data required.

This can reduce network traffic and boost the overall replication performance. Use static filters instead of dynamic filters, whenever possible.

Because SQL Server requires more overhead to process dynamic filters than static filters, you should use static filters instead of dynamic filters, whenever possible. This parameter specifies whether the Merge Agent should process in parallel the changes uploaded to the Publisher and those downloaded to the Subscriber.

When the -ParallelUploadDownload parameter is set to 1, then parallel processing is enabled, otherwise, the parallel processing is disable. Do not configure the distribution database to expand or shrink automatically. Microsoft recommends setting a fixed size for the distribution database. Setting a database to automatically grow results in some performance degradation, therefore you should set a reasonable initial size of the distribution database.

This improves the performance of the initial snapshot, but this parameter is not compatible with the XML data type and cannot be used with character mode snapshots. Place the distribution component of replication on its own dedicated server.

This topology is used for performance reasons when the level of replication activity increases or the server resources become constrained. It reduces Publisher loading, but it increases overall network traffic. Consider using the web synchronization for merge replication.The volume of data retained, managed, and accessed today is unprecedented.

Businesses expect the IT department to keep data online and accessible indefinitely, putting intense pressure on the databases required to store and manage it. SQL Server Replication is one of the techniques to accommodate such demands. SQL Server replication is a technology for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency and integrity of the data.

In most cases, replication is a process of reproducing the data at the desired targets. SQL Server replication is used for copying and synchronizing data continuously or it can also be scheduled to run at predetermined intervals. There are several different replication techniques that support a variety of data synchronization approaches; one-way; one-to-many; many-to-one; and bi-directional, and keep several datasets in sync with each other.

An article can consist of tables, stored procedures, and views. It is possible to scale the article, horizontally and vertically using a filter option.

We can also create multiple articles on the same object with some restrictions and limitations. Using the New Publication wizard, the Article can be navigated. It allows us to view the properties of an article and provide options to set properties for the articles.

How to setup SQL Server database replication for a reporting server

After the creation of a SQL Server replication publication, for instance, if some property requires a change, it will, in turn, require a new replication snapshot to be generated. If the publication has one or more subscriptions then the change requires all subscriptions to be reinitialized.

To get detailed information about an article in the listed publisher, run the following T-SQL. To list the columns that are published in transactional replication in the publication database, run the following T-SQL.

A Publication is a logical collection of articles from a database. The entity allows us to define and configure article properties at the higher level so that the properties are inherited to all the articles in that group.

The publisher is a database that contains a list of objects that are designated as SQL Server replication articles are known as publication database.

The publisher can have one or more publications. Each publisher defines a data propagation mechanism by creating several internal replication stored procedures.Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Use replication to distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet. Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing.

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.

sql merge replication requirements

You may also leave feedback directly on GitHub. Skip to main content. Exit focus mode. Yes No. Any additional feedback? Skip Submit. Send feedback about This product This page. This page. Submit feedback. There are no open issues. View on GitHub. Is this page helpful?It's important to plan for security before you configure your replication topology.

This tutorial shows you how to better secure a replication topology. It also shows you how to configure distribution, which is the first step in replicating data. You must complete this tutorial before any of the others. To replicate data securely between servers, you should implement all of the recommendations in Replication security best practices. This tutorial teaches you to prepare a server so that replication can run securely with least privileges.

This tutorial is for users who are familiar with fundamental database operations, but who have limited exposure to replication. SQL Server Compact cannot be a subscriber in transactional replication. Download the AdventureWorks sample database. In this section, you create Windows accounts to run replication agents. You create a separate Windows account on the local server for the following agents:. The publisher and subscriber might share the same instance of SQL Server, but it is not a requirement.

If the publisher and subscriber share the same instance, the steps that are used to create accounts at the subscriber are not required. For more information, see Replication agents overview. In this section, you configure the snapshot folder that's used to create and store the publication snapshot.

On the Sharing tab in the repldata Properties dialog box, select Advanced Sharing. In the Permissions for repldata dialog box, select Add. Select Check Namesand then select OK. After your share permissions are configured correctly, select OK to close the Permissions for repldata dialog box. Select OK to close the Advanced Sharing dialog box. In the repldata Properties dialog box, select the Security tab and select Edit :. Select the Sharing tab again and note the Network Path for the share.

You'll need this path later when you're configuring your snapshot folder. For more information, see Secure the snapshot folder. In this section, you configure distribution at the publisher and set the required permissions on the publication and distribution databases. If you have already configured the distributor, you must disable publishing and distribution before you begin this section.

Do not do this if you must keep an existing replication topology, especially in production. Right-click the Replication folder and select Configure Distribution :. Then select Next. Select Next. This path should match what you saw previously under Network Path for your repldata properties folder after configuring your share properties.

You might see the following error when configuring the distributor. It's an indication that the account that was used to start the SQL Server Agent account is not an administrator on the system.

On the General page, select Search. On the User Mapping page, in the Users mapped to this login list, select both the distribution and AdventureWorks databases.

You have now successfully prepared your server for replication.Replication restricts the specific actions that a user can perform based on the roles to which the user's login is mapped. The following table summarizes the authentication level necessary for common replication setup tasks:. The following table summarizes the authentication level necessary for common replication maintenance tasks:. Replication Security Best Practices View and modify replication security settings.

You may also leave feedback directly on GitHub. Skip to main content. Exit focus mode. Security Role Requirements for Replication Setup The following table summarizes the authentication level necessary for common replication setup tasks: Setup task Membership requirement Enable a Distributor, Publisher, or Subscriber.

Enable a database for replication. Create a publication. View publication properties. Create a subscription. Configure agent profiles. Security Role Requirements for Replication Maintenance The following table summarizes the authentication level necessary for common replication maintenance tasks: Maintenance task Membership requirement Modify or drop a Distributor, Publisher, or Subscriber.

Modify or drop a publication. Modify or drop a subscription at the Publisher.

Part 69 Merge in SQL Server

Modify or drop a subscription at the Subscriber. Mark a subscription for reinitialization.

sql merge replication requirements

View replication activity, errors, and history using Replication Monitor. A user cannot modify agent profiles, schedules, and so on, unless the user is a member of the sysadmin server role. Maintain replication agents. If the agent was created by a user in the sysadmin role, and a proxy account was not specified for the agent, the agent runs under the context of the SQL Server Agent account.

Start or stop a replication agent. Owner of the agent job or sysadmin server role on the appropriate server.

SQL Server Replication

Yes No. Any additional feedback? Skip Submit. Send feedback about This product This page.Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data.

Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred. Merge replication is typically used in server-to-client environments. Merge replication is appropriate in any of the following situations:.

Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers. Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.

The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher to reflect the net data change that is, the fifth value. Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one node, the same data may have been updated by the Publisher and by more than one Subscriber.

Therefore, conflicts can occur when updates are merged and merge replication provides a number of ways to handle conflicts. If the publication is unfiltered or uses static filters, the Snapshot Agent creates a single snapshot.

If the publication uses parameterized filters, the Snapshot Agent creates a snapshot for each partition of data. The Merge Agent applies the initial snapshots to the Subscribers. It also merges incremental data changes that occurred at the Publisher or Subscribers after the initial snapshot was created, and detects and resolves any conflicts according to rules you configure.

To track changes, merge replication and transactional replication with queued updating subscriptions must be able to uniquely identify every row in every published table. If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed. A filter must not include the rowguidcol used by replication to identify rows.

The newid function is provided as a default for the rowguid column, however customers can provide a guid for each row if needed. However, do not provide value You may also leave feedback directly on GitHub.

Skip to main content. Exit focus mode. Merge replication is appropriate in any of the following situations: Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.


comments

Leave a Reply

Your email address will not be published. Required fields are marked *

1 2