Indexing in a Merge Replication environment

SQL Server Administration
1

Hello SQL Gurus

I am new to Database design. We are building an application that is expected to have mid-level volumes - Around 10K to 100K records per day based on the day of the week. It is a retail application and will have multiple databases with a separate database for each branch and one for the central or Head Office.

We will be using SQL Merge replication to merge data between the branches and the head office.

I am thinking of having a Primary Key that uses two Int columns - one of which will be the branch ID and another will be a simple sequence. I am also planning to have this as the clustered index. Will this design work or will I need to use a GUID as a primary key with the above key as a separate clustered index considering that we will be using SQL Merge Replication. What would be the best primary index for the tables?

We will be on SQL Server 2016 SP2.

Any guidance on this will be highly appreciated.

Thank you all in advance.

2

Why did you opt for the separate database for each branch?

Is that a done deal and its already implemented as such?

3

hi

please see if the below link helps ..

4

Hi @yosiasz,

Thank you for responding. The reason why we opted for a separate database for each branch is that the branches are physically separated and the databases will reside on separate physical servers. We have to follow this option because the application is a POS application and the stores will need to continue operating normally even if the internet connection between the store and the HO is down for some reason.

It is by no means a done deal. If there is another option to reliably operate an application in a scenario where connectivity between the various physically separated branches is not stable or assured, I am open to all ideas and suggestions.

Sudarshan.

5

Hi @harishgg1,

Thanks for your reply. My question is about what type of index to add to user tables in an environment running merge replication. Going by the contents of the article " SQL Server Merge Replication Identity Range Management", I can set an INT column as the identity (Clustered Index and Primary Key?) column and then handle duplicates using Identity Range Management. I am expecting to have a high transaction load so I am not very sure whether or not I can rely on this to keep performance high while avoiding data conflicts caused by duplicate values in the identity columns. But, it is a thought that can be carried forward.
Thanks once again for responding.
Sudarshan.

6

hi

hope this link helps

https://www.codeproject.com/questions/825557/how-to-prevent-insert-duplicate-id-in-sql-server

7

I think you would know best on the reliability of internet in the location of the branches and have considered this issue in your design and I wont second guess you.

What is the percentage/rate of connectivity issues? Does it differ from one branch to another? Have you looked to a cloud solution, hosting your database in cloud (aws,azure) If the connectivity issues are minimal, and if compared to the design/cost approach of multiple physical servers and replication have you weighed the value of the current design? Have you looked at the legal/security ramifications of hosting physical server in each branch in case servers got in the wrong hand (credit card, address etc of customers from stolen server), flood, fire damage etc.
Backups of multiple branches. where would backup be stored. etc etc

Just questions to consider...

8

Hi @yosiasz, your questions are valid. Based on experience, a cloud solution will not work for our customers. The speeds will not be enough and the connectivity is just not stable enough. As, for the investment in infrastructure, that is already done as we will be replacing an existing software that uses a similar structure.

Thanks for your suggestions. I am actually looking for some pointers on what sort of indexing works best in an environment using SQL Server Merge replication. The target is to achieve the best possible performance while also ensuring that the database size remains reasonable. Performance has priority over database size concerns.

Thanks
Sudarshan

9

Hi @harishgg1, thanks for responding. I am looking for pointers on the best practices for creating indexes in an environment using SQL Server replication. We can expect medium to heavy write and read loads with cyclical surges.

Sudarshan.