none
Sync two databases using webservice RRS feed

  • Question

  • Hi All,

    I have two SQL Express servers and i want to write a C# program / webservice that would sync data between the two servers, this should work how SQL replication works.

    Can someone please help me , I am pretty new to this so any kind of help would be much appreciated.

    Thanks in advance

    • Moved by Kristin Xie Friday, January 16, 2015 2:58 AM move to appropriate forum
    Thursday, January 15, 2015 2:40 PM

Answers

  • SQL Express with Advanced Services supports mirroring replication IIRC.  Refer to the link for the features. Will that not work for you?

    If you want a .NET solution then I would probably recommend using SMO, if possible. While you could use ADO.NET you're going to run into issues.  For example the PKs on tables are generally identity and controlled by the DB. You cannot insert values into said columns without first turning on identity insertion. But you cannot do that directly in ADO.NET so you'll end up having to write batch scripts or sprocs to handle the logic for you.  Additionally you're going to have to determine if two rows are the same.  That is dependent upon your DB schema. How you handle PK insertions is going to be important. 

    Deletions with cascading is another issue.  You'll have to understand the table relationships in order to delete rows in the correct order or you're going to have to modify your schema to support cascading deletes and/or drop constraint checks.  You're going to end up writing a lot of code to implement something the DB already supports. Good luck.

    • Proposed as answer by IWolbers Thursday, January 15, 2015 4:38 PM
    • Marked as answer by Shawn ZhaoModerator Friday, January 23, 2015 6:25 AM
    Thursday, January 15, 2015 4:31 PM
    Moderator
  • I was not able to understand that part thats more of a theory, if someone can help with code snippet , that would be great help.

    I am going to give it to you one more time. Do you know how to make XML? Even if you don't follow what is in the link, you can still make dynamic XML of the SQL transactions that would be part of the WCF client side solution pointing to the "from" database. You send the XML to the WCF service with the WCF service pointing to the 'to' database and persist the data to the 'to' database

    https://www.simple-talk.com/sql/database-administration/manipulating-xml-data-in-sql-server/

    You are sitting here talking about you wanted to use a Web service. It was your first thought. The suggestion was made to use WCF using a client/service solution. You may not want to use a WCF Web service, becuase it's too slow, You should look into to using a WCF selfhosting exe service that's using TCP/IP (NetTCP). Or you can host the WCF service in a Windows service.

    http://msdn.microsoft.com/en-us/library/ff649818.aspx

    It doesn't take a rocket scientist to figure out such a simple solution, since you don't wnat to use the power of MS SQL Server.

    Thursday, January 15, 2015 8:15 PM

All replies

  • Unless it is a small DB with a single table I wouldn't recommend that approach.  Ideally simply use replication that is already supported by SQL Express Advanced Services.  If that isn't an option then consider upgrading to a full version of SQL and then use SSIS which is designed for this.

    Michael Taylor
    http://blogs.msmvps.com/p3net

    Thursday, January 15, 2015 3:15 PM
    Moderator
  • If you were not using Express editions, then this could be easily done with MS SQL Server Service Broker where SB would do the mirroring over the LAN or WAN between two computers hosting databases, which would be done directly by SB -- database to database.

    You can use a WCF service over TCP/IP and not a WCF Web service over HTTP. Over HTTP is too slow.

    http://www.codeproject.com/Articles/515253/Service-Oriented-Architecture-and-WCF

    I suggest that you go get yoyrself a good book on WCF.

     
    Thursday, January 15, 2015 3:16 PM
  • its a relatively small DB with about 70 tables. 

    SQL server isint an option i need to find a .NET approach to achieve this.

    Thursday, January 15, 2015 3:59 PM
  • SQL server isint an option i need to find a .NET approach to achieve this.

    It was given to you.

    http://www.codeproject.com/Articles/515253/Service-Oriented-Architecture-and-WCF

    Thursday, January 15, 2015 4:29 PM
  • SQL Express with Advanced Services supports mirroring replication IIRC.  Refer to the link for the features. Will that not work for you?

    If you want a .NET solution then I would probably recommend using SMO, if possible. While you could use ADO.NET you're going to run into issues.  For example the PKs on tables are generally identity and controlled by the DB. You cannot insert values into said columns without first turning on identity insertion. But you cannot do that directly in ADO.NET so you'll end up having to write batch scripts or sprocs to handle the logic for you.  Additionally you're going to have to determine if two rows are the same.  That is dependent upon your DB schema. How you handle PK insertions is going to be important. 

    Deletions with cascading is another issue.  You'll have to understand the table relationships in order to delete rows in the correct order or you're going to have to modify your schema to support cascading deletes and/or drop constraint checks.  You're going to end up writing a lot of code to implement something the DB already supports. Good luck.

    • Proposed as answer by IWolbers Thursday, January 15, 2015 4:38 PM
    • Marked as answer by Shawn ZhaoModerator Friday, January 23, 2015 6:25 AM
    Thursday, January 15, 2015 4:31 PM
    Moderator
  • I was not able to understand that part thats more of a theory, if someone can help with code snippet , that would be great help.
    Thursday, January 15, 2015 7:19 PM
  • I was not able to understand that part thats more of a theory, if someone can help with code snippet , that would be great help.

    I am going to give it to you one more time. Do you know how to make XML? Even if you don't follow what is in the link, you can still make dynamic XML of the SQL transactions that would be part of the WCF client side solution pointing to the "from" database. You send the XML to the WCF service with the WCF service pointing to the 'to' database and persist the data to the 'to' database

    https://www.simple-talk.com/sql/database-administration/manipulating-xml-data-in-sql-server/

    You are sitting here talking about you wanted to use a Web service. It was your first thought. The suggestion was made to use WCF using a client/service solution. You may not want to use a WCF Web service, becuase it's too slow, You should look into to using a WCF selfhosting exe service that's using TCP/IP (NetTCP). Or you can host the WCF service in a Windows service.

    http://msdn.microsoft.com/en-us/library/ff649818.aspx

    It doesn't take a rocket scientist to figure out such a simple solution, since you don't wnat to use the power of MS SQL Server.

    Thursday, January 15, 2015 8:15 PM