Bundle: SQL Replication

To provide an easy and flexible way to setup a replication to SQL servers, we have replaced the Index Replication bundle with new mechanism that is a part of a SQL Replication bundle.

Supported SQL providers:
* System.Data.SqlClient
* System.Data.SqlServerCe.4.0
* System.Data.SqlServerCe.3.5
* System.Data.OleDb
* System.Data.OracleClient
* MySql.Data.MySqlClient
* Npgsql

Setup

To configure SQL Replication, we need to enable the SQL Replication Bundle and insert a SQL Replication Configuration document into our database. This can be done by using the Studio or manually by inserting proper document (Raven.Database.Bundles.SqlReplication.SqlReplicationConfig) under Raven/SqlReplication/Configuration/name_here key.

The document format is as follows:

public class SqlReplicationConfig
{
	public string Id { get; set; }

	public string Name { get; set; }

	public bool Disabled { get; set; }

	public string RavenEntityName { get; set; }
	public string Script { get; set; }

	public string FactoryName { get; set; }
	public string ConnectionString { get; set; }
	public string ConnectionStringName { get; set; }
	public string ConnectionStringSettingName { get; set; }

	public List<SqlReplicationTable> SqlReplicationTables { get; set; }
}

public class SqlReplicationTable
{
	public string TableName { get; set; }
	public string DocumentKeyColumn { get; set; }
}

where:
* Id is a document identifier
* Name is a configuration name
* Disabled marks replication as enabled/disabled
* RavenEntityName contains a name of entities that will be replicated
* Script contains a replication script
* FactoryName, ConnectionString, ConnectionStringName and ConnectionStringSettingName are used to provide connection strings to destination DB in various ways
* SqlReplicationTables is a list of tables to which the documents will be replicated

Example

Let us consider a simple scenario, where we have an Order with OrderLines and we want to setup a replication to MSSQL.

public class Order
{
	public string Id { get; set; }
	public List<OrderLine> OrderLines { get; set; }
}

public class OrderLine
{
	public string Product { get; set; }
	public int Quantity { get; set; }
	public int Cost { get; set; }
}

First we need to setup our MSSQL by creating a database with two tables. In our case the database will be called ExampleDB and the tables will be called Orders and OrderLines.

CREATE TABLE [dbo].[OrderLines]
(
	[Id] int identity primary key,
	[OrderId] [nvarchar](50) NOT NULL,
	[Qty] [int] NOT NULL,
	[Product] [nvarchar](255) NOT NULL,
	[Cost] [int] NOT NULL
)
 
CREATE TABLE [dbo].[Orders]
(
	[Id] [nvarchar](50) NOT NULL,
	[OrderLinesCount] [int] NOT NULL,
	[TotalCost] [int] NOT NULL
)

Last step is to insert a document with our configuration. This can be done using Studio or manually.

Manual

session.Store(new SqlReplicationConfig
{
	Id = "Raven/SqlReplication/Configuration/OrdersAndLines",
	Name = "OrdersAndLines",
	ConnectionString = @"
		Data Source=.\SQLEXPRESS;
		Initial Catalog=ExampleDB;
		Integrated Security=SSPI;",
	FactoryName = @"System.Data.SqlClient",
	RavenEntityName = "Orders",
	SqlReplicationTables =
		{
			new SqlReplicationTable
			{
				TableName = "Orders", DocumentKeyColumn = "Id"
			},
			new SqlReplicationTable
			{
				TableName = "OrderLines", DocumentKeyColumn = "OrderId"
			},
		},
	Script = @"
		var orderData = {
			Id: documentId,
			OrderLinesCount: this.OrderLines.length,
			TotalCost: 0
		};

		replicateToOrders(orderData);

		for (var i = 0; i < this.OrderLines.length; i++) {
			var line = this.OrderLines[i];
			orderData.TotalCost += line.Cost;

			replicateToOrderLines({
				OrderId: documentId,
				Qty: line.Quantity,
				Product: line.Product,
				Cost: line.Cost
			});
		}"
});

Using the Studio

In Studio the configuration page is found under Settings -> SQL Replication.