CODEWITHSUNDEEP
mysqlsql-serverreplication
please delete me
please delete me

Reputation:

SQL Server equivalent of MySQL multi-master replication?

Is there an MSSQL equivalent of MySQL's multi-master replication? If so, is it available in SQL Express 2008? I've also seen it referred to as two node circular replication.

Basically, I have an in-office database that I want to be perfectly (relatively :) )in sync with a cloud database (we will have access via VPN). Reads and writes occur at both nodes.

Upvotes: 8

Views: 20768

Answers (4)

Remus Rusanu
Remus Rusanu

Reputation: 294297

Peer-to-Peer Transactional Replication. Is an Enterprise only feature. Another option is an updatable subscription for Transactional Replication, see Updatable Subscriptions for Transactional Replication. And finally you can roll your own using Service Broker, which is the only option which will work with an Express client, as long as the 'cloud' edition is non-Express.

Upvotes: 13

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

In addition to what others have posted, I'd also mention merge replication, and an article on MSDN about Selecting the Appropriate Type of Replication. Basically, whether you choose Merge or Transactional (with updating subscribers) depends on what the proportion of writes are at the different nodes, and the likelihood of conflicting changes.

Upvotes: 0

JonPayne
JonPayne

Reputation: 566

When you say a cloud database, do you mean SQL Azure? If so, SQL Azure doesn’t support replication.

If your cloud database is a full copy of SQL Server (i.e. not Express) you can set up a transactional publication with updatable subscriptions, or use a merge publication. Both options will let you synchronise changes in either database.

Edit

Just to clarify, you can use SQL Server Express for replication, but only as a subscriber. The publisher needs to be Workgroup edition or above.

Upvotes: 1

8kb
8kb

Reputation: 11406

SQL Server 2008 Enterprise and Standard editions offer:

SQL Server 2008 Express has limited functionality with:

In both cases, you'll need to write a customized solution.

There is an excellent chapter in SQL Server MVP Deep Dives called "The Poor Man's SQL Server Log Shipping." It will take you through the entire process.

Upvotes: 4

Related Questions