About a year ago SDP took over maintenance on an application developed a few year back for one of our clients. The application was developed using C# and SQL Server. In short the C# portion of the application is a service that listens on a UDP port for a specific piece of hardware to communicate with the application. Results are then recorded in the SQL Server database.

Recently we’ve had a need to run this application in a distributed environment. The requirements for the install were to have the application work at 2 remote locations and a centralized home office. Most importantly, records generated at one remote location need to be available at the second remote location in a reasonable amount of time. Additionally we needed the application to be able to run in the event of a network outage. The remote locations have good connectivity, but the client cannot afford downtime during business hours. After a bit research and prototyping on our dev box we decided to utilize SQL Server’s merge replication features. As nice as it would have been to have a plug and play solution, we did have to make some changes to the application.

1. We modified all tables in the application to change our INT primary keys to UniqueIdentifier columns as primary keys.
2. Additionally we changed all the new primary key fields to be row guids. This makes the columns act as a primary key in the replication system. Without this change SQL Server will add another uniqueidentifier column automatically for the replication.
3. We updated all stored procedures to use unique identifiers instead of int fields where applicable.
4. In any stored procedures that have an insert and later used the INT id created by using Scope_Identity, we modified the procedure to first create a uniqueidentifier variable and assign the key value using NEWID. Then you include the new variable in the insert statement and it’s available later in the procedure.
5. A few areas in the C# application utilized int’s as references to rows in the table, these references were changes to string variables to hold the uniqueidentifier. We could have used the GUID type, but chose strings as we display the value in a few places for administrative users.

In all these changes we’re very minimal on the application. From this point we simply setup a merge replication at the home office as the publisher, subscribed each location as a subscriber with continuous updates. To ease subscription management all the subscriptions are managed at the home office server.

Overall we’re very pleased with this solution, it allows us to work offline for an extended period of time if need be. All records will be caught up as soon as a connection is available. The majority of the time the remote locations are able to communicate and are no more than 1 minute ahead of the database system at the home office.