Using Identity Columns as Primary Keys

[satpal123]

I am trying to get my head around the Filtering. Here is my explanation:

We have a main Master DB with all client data with different site codes. Due to the huge size of the Master DB, we are planning to split it into smaller DB's based on the Site code.

In the end, the users will only be able to add/update/delete data from the Site DBs and then the data will be synced back to the master DB. I have managed to filter them out to the different small DB's but I am not sure how to sync all the Site DBs data back to the Master DB.

Master DB Data
------------------------------------------------
ID	Name	PatientNo     SiteCode
1	Client1	    123	       BNE
2	Client2	    345	       CAU
3	Client3	    567	       NEW
4	Client4	    789	       MOO
-----------------------------------------------
SiteDB1

ID	Name	PatientNo	SiteCode
3	Client3	   567	         NEW
-----------------------------------------------
SiteDB2

ID	Name	PatientNo	SiteCode
1	Client1	   123	         BNE

Now if a user creates a new client in SiteDB1, it will create it as ID - 4 because of Identity Increment. When the sync occurs, the ID - 4 row will get updated in Master DB. Is there a way to prevent this or is there another way to implement it?

[gentledepp]

That's an easy one:
Do not use integer keys in your database - at least not those tables that are synced and where new entries could be inserted in the client databases.
Use GUID/uniqueidentifier instead!
This is the whole point of why you'd use GUIDs as keys. While they may have multiple disadvantages over integer keys (index fragmentation, slower, etc.) they have one advantage: They are uniue. Therefore you can add client rows as much as you like and this will work

[satpal123]

Problem is that the database is from a third party application so they are using the primary key as integer. Though there is GUID column too which is not the primary key.

Maybe there is a way we can intercept and compare the GUID on applying to master dB?

[Mimetis]

As @gentledepp mentioned, working with GUID is the most convenient way.

Another technic is to use seeding. You have two main methods:

Method 1:

The idea is to work with Identity columns as Primary keys and range of ids.
For instance, you can define something like that:

• Server IDS range: 1 - 99 999 999
• Client 1 IDS range: 100 000 000 - 100 999 999
• Client 2 IDS range: 101 000 000 - 101 999 999
• Client 3 IDS range: 102 000 000 - 102 999 999

You can store these values in any configuration table.
When you reach the limit for one client (or server), just change the range for a new one.

Method 2:

In the same concept, another technic is to use the IDENTITY (m, n) formula as follows:

• m = Serial number of a node (Server = 1, First Client = 2, Second Client = 3, etc.)
• n = Number of replication nodes (Server + All Clients)

For example, we have 4 nodes (n = 4) to sync (1 Server + 3 Clients). The Serial number m will be 1 for Serve and 2, 3, 4 for Client – A, Client – B and Client – C respectively.

According to the formula, the data will be populated as follows:

• Server : IDENTITY (1, 4) - the column data sequence will be 1, 5, 9, 13…
• Client – A: IDENTITY (2, 4) - the column data sequence will be 2, 6, 10, 14…
• Client – B: IDENTITY (3, 4) - the column data sequence will be 3, 7, 11, 15…
• Client – C: IDENTITY (4, 4) - the column data sequence will be 4, 8, 12, 16…

The downside of this method is that you need to now the MAX number of clients you will have in your organization.

Links

Some useful links:

• https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-identity-columns
• https://www.mssqltips.com/sqlservertip/6093/sql-server-merge-replication-identity-range-management/

[Mimetis]

@gentledepp For Method 1, a good catcher is to have a routine that will inspect the the identity value from each table.
Let's say,

• When application starts, supposing you will never increment more than 10% of the id range in the biggest table.
• You have a range of 1M identities values
• When you start the application, if you have reached identity 900 001 (10%), you can grab a new identity range from the server.
• Obviously, the server should track all ranges from all clients. Not really complicated I guess.

That being said, I'm 1000 % agree that it's sounds easier than it actually is !!!

Honestly, if you have no choice, and that would be my choice I guess, Method 2 should be easier, supposing you know the max of clients you will ever reach.
Ok, you can start with n=1000 and you have room to think about it later :)
Having n=1000 allows you to have 2.1M rows as node insertions

[gentledepp]

@Mimetis Maybe this very information you gave here should be in the docs?

integer identities and synchronization

1. advice: Only use integer identities for records you'll only update or delete on the client side.
   In our application, it is e.g. users. You cannot create a user directly from the client, as the server requires some security checks first. (e.g. the password is not synced at all but is required for new users)

2. if you do have to create rows on the client side, use GUID!

3. what if I cannot use GUID - is there a way to use integers here as well?
   -> yes

• Method 1 ...
  plus: Drawbacks and when to avoid
• Method 2
  plus: Drawbacks and when to avoid

What do you think?

[Mimetis]

Theoretically, I 100 % agree.
My motivation to work on the documentation these days is reaching a level of ... 0.1% :D

[gentledepp]

I was not suggesting to do this now, but just create a "documentation" issue pointing to this discussion, so that when you finally find time and motivation to document this, you won't have forgotten about it.
Also, maybe someone will do a PR for this ;-)

[satpal123]

I found this article for the old Sync framework https://jtabadero.wordpress.com/2011/07/20/part-3-synchronizing-tables-where-client-and-server-primary-keys-are-different/

It works fine with 0.5.7 without the SQL built in change tracking Dotmim.Sync but not with change tracking enabled.

I have created a issue for this for version 0.6.1 #444