My Architecture:
I need to build a database with SQL Server, where the data from each server is distributed to the other server.
How can I achieve this kind of architecture?
How do I maintain consistency between the tables location on insert, and when selecting?
How can I select all the data of both servers with the same code?
Both instances need to accept writes. The idea is to work like DDB so one server knows that the other has the data and requests it.
Best Answer
The typical method employed where you need to replicate the data to both servers is to use a
NEWSEQUENTIALID()
as the default value for the primary key columns.From the Microsoft Documentation for NEWSEQUENTIALID():
SQL Server uses the UuidCreateSequential function to generate globally unique identifiers. This function guarantees that no duplicates will be generated when used in a machine with a manufacturer-assigned ethernet MAC address. If you are using virtual machines with locally generated MAC addresses where those MAC addresses are not guaranteed to be unique there is a slight possibility that the values will not be unique.
An example
CREATE TABLE
statement for your location table:Inserting location values looks like:
The inserted row now looks something like:
I've created the same table structure on a second server; here we're inserting different rows on that server:
As you can see in the two sets of output shown above, each row has a unique LocationID value. The values aren't generated in random order, they are generated sequentially, which is important for insert performance since it will limit fragmentation via page splits. Be aware, however, those sequential starting points will change whenever the server hosting SQL Server is restarted.
Merge replication, which is a feature included in all versions of SQL Server from Standard to Enterprise (not SQL Server Express), automatically replicates rows between both servers. The
ROWGUIDCOL
property included in theCREATE TABLE
statement marks theLocationID
column for use by Merge Replication. Directions for setting up Merge Replication are beyond the scope of this answer.Once Merge Replication completes, both servers will have this in their
dbo.Locations
table: