Replication programming concepts

Applies to: SQL Server Azure SQL Managed Instance

Before developing an application that uses replication functionalities, follow these general planning steps:

1. Define your replication topology.

2. Define application functionality.

3. Plan for security.

4. Choose a development environment.

5. Choose the appropriate replication programming interface.

The rest of this article describes these steps in more detail. To help illustrate the planning process, an example is included.

Defining the replication topology

The first step in programming replication is to define the replication topology for your application. If you're writing an application that uses an existing replication topology, such as a client application that accesses data at an existing subscriber, move on to the next step.

The replication topology that you define depends on many factors, including the following:

• Whether replicated data needs to be updated, and by whom.

• Your data distribution needs regarding consistency, autonomy, and latency.

• The replication environment, including business users, technical infrastructure, network and security, and data characteristics.

• The types of replication and replication options.

• The replication topologies and how they align with the types of replication.

If you're new to Microsoft SQL Server replication, see Types of Replication.

Defining application functionality

After you define the replication topology, decide on the functionalities that your application offers. These functionalities can range from a script that synchronizes a subscription to an application with a user interface to configure replication. Replication supports the following general programming tasks:

• Setting up replication.

• Synchronizing Subscribers.

• Maintaining a replication topology.

• Monitoring a replication topology.

• Troubleshooting replication.

It's also common to extend your application by combining replication functionalities with other functionalities provided by SQL Server. The following table highlights some extended functionalities that you might provide in your replication application.

Planning for security

Security is important in any application, and you should plan for security before writing any code. Application security can be divided into three main parts: securing the database, securing replication, and writing secure code.

The following articles provide information on security:

• View and modify replication security settings

• Security Center for SQL Server Database Engine and Azure SQL Database

Choosing a development environment

When you develop a replication application, consider these three basic development environments. Each development environment has access to the same replication functionalities with some exceptions. You can develop replication applications in each of the following environments.

• Managed code

  Object-oriented development environment that leverages the benefits of the .NET Framework programming and the .NET common language runtime (CLR). Managed code is the recommended programming environment for both .NET development and SQL Server applications. Managed replication interfaces enable you to program replication administration in an object-oriented manner without having to know Transact-SQL. It also provides some callback functionalities when running replication agents that aren't available from scripts. Managed code is the best environment for developing reusable components and user interface applications.

• Scripting

  Simple applications that execute a series of commands as either replication system stored procedures in Transact-SQL scripts or commands in batch files. While you can execute scripts in a managed environment by using the SQL Server in-process managed provider, you can get the same functionality by using managed replication interfaces, which also provide callback functionalities. Scripting is the best environment for executing tasks that run only a few times and where callback functionalities aren't required, such as installing a replication server.

• Native code

  Object-oriented development environment that utilizes direct access to the system or COM objects such that code isn't managed by the CLR. Native code replication interfaces are deprecated or discontinued. For more information, see Deprecated Features in SQL Server Replication or Replication Backward Compatibility.

Choose the appropriate replication programming interface

The final planning step is choosing the appropriate replication programming interface that implements the desired replication functionality for the chosen development environment. The following table shows the available replication programming interfaces.

Example

At Adventure Works, data needs to be published for 200 sales representatives around the world. The sales representatives travel often and need to use laptop computers or personal digital assistants (PDAs) to change customer data and add new orders. The sales representatives need to synchronize the changes with the Publisher when they connect the laptop to the network.

For this application, the planning steps might look like the following:

1. The replication topology for this application already exists. However, you must create a new pull subscription at the client. Use parameterized filters in the publication to replicate a unique set of data to each sales representative.

2. In addition to the typical data access required for a sales application, this application should enable a salesperson to synchronize the pull subscription on demand by clicking a button. Since a sales representative installs and runs the application, it also needs to be able to configure a subscription and apply the initial snapshot at the client. Optionally, the application uses the infrastructure provided by Windows for sensing wireless connectivity to automatically synchronize the subscription when a connection is detected.

3. Follow all of the security guidelines for replication, including using Windows Authentication and a virtual private network (VPN) when connecting to the Publisher. If you implement Web synchronization, use a Transport Layer Security (TLS), previously known as Secure Sockets Layer (SSL), connection. For more information, see Configure Web Synchronization.

4. To take advantage of the features of the .NET Framework, develop the application by using a managed code language.

5. Based on these requirements, the Replication Management Objects (RMO) managed interface can provide all of the needed replication functionality for this application.

You can download the AdventureWorks sample application that implements this example scenario from SQL Server.