Synchronize table data between SQL Server instances using SQL Table Dependency and NServiceBus

Data sync is the task which needs to be done very diligently. It should work effectively. In this post, you’ll see how we did data synchronization between the two SQL Server instances.

Challenge:

We had to capture the transaction in a few tables and move data to another database which is located on the other side of the world. There is no direct connection between the two databases. There is an integer primary key in the few tables. We had the flexibility to add a column but we couldn’t modify the existing columns because that is used by other applications.

Solution:

SQL Table Dependency – To monitor table change.

NServiceBus – A reliable data persistent storage where messages can be stored and that can be polled by the individual nodes.

We started with the SQL Table Dependency application which will track DB Changes. We followed the article Monitor Table Change with SQL Table Dependency. 

With this application, we were able to track Insert/Update/Delete changes in the table. It gives the notification message which has a complete record and we can play with it.

We were then suggested to use NServiceBus. It will persist the message until the message is read by the receiver. The advantage of using NServiceBus is it gives consistency guarantees till the message is read and processed by the receiver. The receiver will notify the Sender for the transaction processed successfully. You can start with NServiceBus Step by step to understand the architectural concept behind the software. The best about NServiceBus is it provides a lot of sample code which you can download and run the application to see how it works and understand. All the sample code should be working as you run the application. I personally tried with three sample codes.

We initially started with the Publisher-Subscriber application and used MSMQ for a data persistence. In this case, the sender and receiver both should run on the same server and can use the MSMQ to transfer the data. Because of some security norms, there wouldn’t be a direct connection between the source and target database. So we changed the approach and decided to go with NServiceBus Sender-Receiver application with NHibernate Persistence Usage. We did a POC keeping in mind the required business objectives, which worked very well and then we added the required business logic in SQLTableDependency, Sender, and Receiver to transfer the data from one server to another. It takes as little as 3-5 seconds for the data to be transferred and processed. The NServiceBus application has a mechanism to retry sending data if any issues, you can go through with the recoverability article to know more about it.

One SQLTableDependency application can track one table, we had to track other 5 tables as well. Data in the tables are associated with the Users table which the SQLTableDependency is monitoring. In SQLTableDependency, it’s not easy to monitor 5 tables in one application, there is some way but we didn’t invest time into that. We followed another approach. Users table is monitored by SQLTableDependecy, we added GUID column to track the record and push the same GUID on the target server tables. The operations on the Target Database is done based on the GUID.

We created triggers for 5 tables and one audit table to track the changes. In trigger we did two things, first, log the changes in the audit table and then update the user’s table which is monitored by SQLTableDependency with the GUID of the audit table. A business logic is in the SQLTableDependency to fetch the table changes using the Audit table and pass on the data to NServiceBus sender application.

I’m sure, you would have a question of how the application get notified about the data change?

We used MSMQ for storing the data from SQLTableDependency. NServiceBus sender will monitor the MSMQ. Any record added in MSMQ will be processed by the sender application and then removed from the MSMQ. NServiceBus NHibernate Sender-Receiver application uses SQL database internally for processing.  It creates a new database. Sender and Receiver both should have access to the database.

So we have three applications – SQL ServiceBroker (SQLTableDependency), Sender and Receiver. These applications will be running as a Windows Service and deployed on the servers. Receiver application will be running somewhere in the world and has access to Source and Target Databases using the secured VPN.

We created:

  • Triggers
    • Triggers for Insert, Update and Delete operations for the required tables. Triggers will add details about the transaction in the Audit Table and update the Users table with the SyncID.
  • Audit Table
    • SyncOperation table in SQL DB to track the changes in the above tables
  • SQL Service Broker with SQL Table Dependency application
    • This will track the users’ table.
    • On Insert/Update – It will take the values from the Users table and Sync Operation Table (if any) and push them to the Messaging Queue.
  • NServiceBus Sender
    • This will keep track on the MSMQ
    • Any data added in the MSMQ will be sent to the receiver and it will be cleared from MSMQ.
  • NServiceBus Receiver
    • A receiver will receive the data sent by the Sender.
    • It will then process the data by syncing it with the target Database.
    • Once the data is updated, a receiver will then notify the sender about the successful processing of the given data.
  • Stored Procedure
    • Stored procedure on the target database for each table to process the data

There are other ways as well to solve this business problem, We used this approach based on the requirement and suggestion from the client. Thanks to Senthilvel for helping me in this.

If you are going to use SQLTableDependency/NServiceBus and having any concern you can reach out to me.

How to use Linq Lambda Expression IEqualityComparer For IEnumerable.Except

One of the thing that sometimes annoys is Lambda Expression with IEqualityComparer. I was trying to pick items with Except Comparer from the list. After doing quick search on google found the below extension method which can be used in such scenario. 

public static IEnumerable Except(this IEnumerable first, IEnumerable second, Func<TSource, TSource, bool> comparer)
{
return first.Where(x => second.Count(y => comparer(x, y)) == 0);
}

How to use:

List myItemsList = source.Except(myDeals, (x, y) => x.ID == y.ID).Take(requiredCount).ToList();

This will return the list of items except item(s) which match with an ID in myDeals. 

Sitecore query with hypen or dashes

Challenge:

Sometimes your sitecore query might contains hypen or space and you get an error: “unterminated literal string in Query”. It is because the query contains an illegal character and the solution is to escape the “-“.So we need to add hash(#) around the word in the query.

Query: /sitecore/content/My Site/Event/ABC-Items

Earlier I referred the below article and implimented in one of the project:

Escaping dashes/“-” in Sitecore Queries. Datasource query Update

This is actually the nice example. But while working on another project, I applied below piece of code which I think is better and easy solution then the one given in above link.

Read More

Error: Could not load file or assembly Ninject

Challenge:

Error: Could not load file or assembly ‘Ninject, Version=3.2.0.0, Culture=neutral, PublicKeyToken=c7192dc5380945e7’ or one of its dependencies. The system cannot find the file specified.

Above error started appearing suddenly in our local Sitecore (Sitecore.NET 8.1 (rev. 151207)) project. We eyed lot of articles on-line but none helped, So that is why I thought it’s better to blog this out. We investigated for couple of hours to get this fixed, may be this post saves someone’s time.

Read More

Using AutoMapper

Challenge:

We defined the Web-Service and were using it in an application. Web-Service returns an object or list of an object.

 

There was already code written to do the processing once we get the response object from web-service.But when we get the object via web-service, it append the Web Service Reference Name because of which we were not able to utilize the already written code for processing that object. We believe in Re-usability as much as possible and wanted to re-utilize that code which was also used by other functions. Web-Service was returning lot of data which includes list and list of object inside the list etc.

How to encode Trademark symbols ™ in C#

Hello Folks, Sorry was away for a while. As was very busy with Project.Challenge:
We were facing one strange issue with encoding Trademark symbol in C#. If you are also facing such similar issue then this post is for you.It was strange for us see that ™ isn’t encoding with HttpUtility.HttpEncode. As google is my best friend [so as yours], I tried to get the answer.Before we dig into the solution, I tried on following websites which help us to get the result live for the Http.HtmlEncode()http://htmlencode.org/
http://www.opinionatedgeek.com/dotnet/tools/htmlencode/encode.aspx

And we see that HTML Encode isn’t encoding ™.
Read More

How to escape line breaks while creating CSV or TSV file using C#

Hello C# friends.

Are you creating a CSV (Comma Separated Value) or TSV (Tab Separated Value) file in CSharp and you don’t know why your CSV file is breaking when you open it into Excel, then this post is for you.

Mr. Brian has written a very nice article on how to generate CSV file which we referred in our project.
http://www.csharptocsharp.com/generate-csv-from-generic-list

It was difficult to identify the problem. Values in object were html based and we were doing Html.Encode, so i thought that we need to do Html.Decode, but was unlucky.
To get better understanding of Html.Decode and Html.Encode refer: http://www.dotnetperls.com/httputility
Then thought that it can be  Encoding issue so tried to pass different encoding formats to StreamWriter but was unlucky in that too. I tried to identify in depth and reached to the problem and then solution.

Problem:
As my values were in Html format it were containing “t” or “n” or “r” which were creating an issue when writing a file using StreamWriter. As it get “t” or “n” or “r” it was breaking the file.

Read More

Migrate Visual Studio Solution from SVN to TFS

I love to work on Microsoft Technologies and while working with them recently we got a challenge to migrate our project from SVN to TFS. So I would be discussing about:
1) How to migrate visual studio solution from SVN to TFS.
2) Different approaches for migration
3) Challenges faced during migrating our solution.
As it is our basic approach to first always search on google and do some analysis and research before doing any such task, similar way we did and found two approaches – Manual and Automated.
SVN2TFS.EXE – (Automated) we didn’t dig much also read few comments by which we got unhappy.

Read More

Setup SVN on Local Network

Subversioning plays a very vital role for Developers. It keeps track of changes to a file over time. It maintains the history of all the changes. This way you do not end up with millions of copies of the project that you cannot make heads or tails out of.  With a version control system, every time you commit a set of changes you write a comment describing what you did. Then you can look at the log to see what was changed when and by whom. You can easily look at the differences between versions and easily roll back changes if you need to. You also always have easy remote access to the current version of your project without having to remember which file name you used for it: you simply do an update of your working directory or check out the head version. For more information regarding version control please refer: http://en.wikipedia.org/wiki/Revision_control

Read More

Quick way to deploy web application to multiple environment.

Hello, Folks. It’s really interesting to know about MSBuild. Recently while working on one project we found that deploying Project Files on multiple environments consumes a lot of time. So we thought to take the help of MSBuild and deploy project files on live Server(s) automatically.
What is MSBuild?
MSBuild is Microsoft Build Engine for Visual Studio. MSBuild is completely transparent with regards to how to process and build software developed in Visual Studio.
Why MSBuild?
Initially from Visual studio we do publish via Publish Profile(s). MSBuild uses that publish profile and does deployment without the help of visual studio. It can reduce deployment time up to 70%.
This is the basic Dev environment which I have created to demonstrate you on Automatic Build. Developer can publish files to staging as well production Environments.
How MSBuild?
MSBuild command needs few command line options mainly MSBuild.exe, Visual Studio Project Solution File (.sln), and Publish Profile.
First of all, you need to get the path of MSBuild.exe in my case it is located at C:WindowsMicrosoft.NETFramework64v4.0.30319msbuild.exe
Create a new File and enter the below two MSBuild Commands. (Note: You need to change the path according to your project solution directory.)
"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\msbuild.exe"
"C:\Users\npunjabi\Documents\Visual Studio 2013\Projects\DeveloperEnvironment\DeveloperEnvironment.sln"
/p:DeployOnBuild=true /p:PublishProfile "C:\Users\npunjabi\Documents\Visual Studio 2013\Projects\DeveloperEnvironment\DeveloperEnvironment\PropertiesPublish\ProfilesStaging.pubxml"
 
"C:WindowsMicrosoft.NETFramework64v4.0.30319msbuild.exe"
"C:\Users\npunjabi\Documents\Visual Studio 2013\Projects\DeveloperEnvironment\DeveloperEnvironment.sln"
/p:DeployOnBuild=true /p:PublishProfile"C:\Users\npunjabi\Documents\Visual Studio 2013\Projects\DeveloperEnvironment\DeveloperEnvironment\PropertiesPublish\ProfilesProduction.pubxml"
/p:DeployOnBuild=true – This will build the solution before deployment and will throw an error if build fails before it deploys the files.
Save this file with any name and provide extension as “.bat”.
Open Command Prompt in Administrator Mode and execute the MSBuild (.bat) file.
Note: If you are using SVN then before deployment you can also write the SVN Command which will update the solution. After that MSBuild.exe will build the code and then publish.
Please download this file for more user-friendly deployment file which will prompt the user for deployment whether to start deployment or not on particular servers. Thanks to Kiran Sir (http://kiranpatils.wordpress.com/) for helping me in creating deployment file.

To know more about Web Deploy: Click HereIf you have a better idea do share with us.

Have a nice Deployment! 🙂