Search code examples
sqldatabasesql-server-2012replication

How to create two databases connected between each other different servers


Edit: Example if one server is down, the other one has the same data and i will be able to use it.

Example of what I want to achieve:

Server 1 -> DBExample

INSERT INTO DBExample (idExample, nameExample) VALUES (123, 'Example');

SELECT * FROM DBExample;
+-----------+-------------+
| idExample | nameExample |
+-----------+-------------+
|       123 | Example     |
+-----------+-------------+

Server 2 -> DBExample2

SELECT * FROM DBExample2;
+-----------+-------------+
| idExample | nameExample |
+-----------+-------------+
|       123 | Example     |
+-----------+-------------+

And vice versa:

Server 2 -> DBExample2

INSERT INTO DBExample2 (idExample, nameExample) VALUES (1234, 'Example2');

SELECT * FROM DBExample;
+-----------+-------------+
| idExample | nameExample |
+-----------+-------------+
|       123 | Example     |
|      1234 | Example2    |
+-----------+-------------+

Server 1 -> DBExample

SELECT * FROM DBExample;
+-----------+-------------+
| idExample | nameExample |
+-----------+-------------+
|       123 | Example     |
|      1234 | Example2    |
+-----------+-------------+

Solution

  • As this is SQL Server look at bi-directional transactional replication, or peer to peer replication.

    http://sqlblog.com/blogs/hilary_cotter/archive/2011/10/28/implementing-bi-directional-transactional-replication.aspx