locked
Web service to copy table data from one database server to other database server RRS feed

  • Question

  • User1428336426 posted

    I want to create a webservice, where i will write all the webmethods to collect data from one server and then I want to copy those data into another server.

    I want to understand how can I go about it. I alreadty search google for this idea..but didnt help much.

    Please anybody guide me on how can I proceed for this.

    Tuesday, April 16, 2013 6:11 AM

Answers

  • User220959680 posted

    create a webservice, where i will write all the webmethods to collect data from one server and then I want to copy those data into another server.

    It is standard practice to implement SQL job or SSIS package for Data export jobs. Depending on the volume of data the job can run for longer time, which is not a best practice to implement the same in web based services.

    Being said that it is feasible to implement WCF Service where service implements the functionality (as explained below), a console applicaiton consumes the service that is being scheduled using windows task scheduler.

    Service with methods:

    class DataExportService
    {
        public void GetData()
         {
               //query the data from server1
               
         }
    
         public bool Export()
         {
               //Get the data from GetData method
               //Export to another database on server 2
    
              //flag to indicate Success or Failure
         }
    }


    Note that it is required to increase send / receive Timeout etc in service and client side.

    More at http://msdn.microsoft.com/en-us/library/ms733099.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 16, 2013 6:57 AM

All replies

  • User-1388383071 posted

    Picture is not clear.. where you want to copy the data ( to a database) or file copying

    Tuesday, April 16, 2013 6:26 AM
  • User1428336426 posted

    to a database

    Tuesday, April 16, 2013 6:28 AM
  • User-1388383071 posted

    Here my understanding is listed here

    1) Your application has two database, one contains application related data another is user for other purpose. you need to save som data to second db ?

    if my under standing is clear , i dont think it is heavy task just switch the connection string of your DB while saving data

    else xplain your picture with eg

    Tuesday, April 16, 2013 6:34 AM
  • User1428336426 posted

    I have a database which is called as Master database. And I have many application which needs to have access to that data which is getting updated every now and then. So every application hitting that master database. I want to create a web service whenever I want to fetch the records from the master table and copy it to my application level database. So that particular application will hit the local database to access the master data/.

    Please let me know if it is enough for you to understand the scenario .

    I dont want to pass the connection string to each application due to security purpose.

    Please help !!

    Tuesday, April 16, 2013 6:45 AM
  • User-1388383071 posted

    I got our scenario but i am doubt about one thing. how web service is fit with this scenario, insted you need to consider Sql Sync framework

    where client application database collect the data then they syncronize application changes to master database. but it need some level of Db design every row in master Db must have unique row identifier. and your scenario . my client CL ask for Data DT. now your application read

    This data from AD(application Data) Or MD( Master data) ? .Then if data not present in AD , you have to check in MD ,actually this is a hit to MD. So download all the application specific data to corresponding application DB and Sync master DB based on routine..

    Tuesday, April 16, 2013 6:57 AM
  • User220959680 posted

    create a webservice, where i will write all the webmethods to collect data from one server and then I want to copy those data into another server.

    It is standard practice to implement SQL job or SSIS package for Data export jobs. Depending on the volume of data the job can run for longer time, which is not a best practice to implement the same in web based services.

    Being said that it is feasible to implement WCF Service where service implements the functionality (as explained below), a console applicaiton consumes the service that is being scheduled using windows task scheduler.

    Service with methods:

    class DataExportService
    {
        public void GetData()
         {
               //query the data from server1
               
         }
    
         public bool Export()
         {
               //Get the data from GetData method
               //Export to another database on server 2
    
              //flag to indicate Success or Failure
         }
    }


    Note that it is required to increase send / receive Timeout etc in service and client side.

    More at http://msdn.microsoft.com/en-us/library/ms733099.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 16, 2013 6:57 AM
  • User1428336426 posted

    SQL job or SSIS package
    and
    WCF Service 
    two different ways to implement this.. or it is one solution to achieve what i want.

    I have no experience on working with WCF nor SSIS package..but i am going through google for some tutorials..

    Thanks for your help

    Tuesday, April 16, 2013 7:18 AM
  • User220959680 posted

    two different ways to implement this.. or it is one solution to achieve what i want.

    Two different solutions suggested. SSIS package is best one to consider.

    http://miteshsureja.blogspot.co.uk/2012/04/creating-simple-ssis-package-using-sql.html

    More can be found on web.

    Tuesday, April 16, 2013 8:14 AM
  • User1428336426 posted

    What if I want to repeat this thing periodically to check if any tables has been updated since the last transfer?

    Wednesday, April 17, 2013 12:50 AM