locked
Please suggest RRS feed

  • Question

  • Hi

    I need to create a program which will move some data from one database to another database on scheduled basis.One is a Master database having large amount of data and other database will contain subset of data from Master database.Program  will move some data  from Master to other database depending on some business rule.This program will run on scheduled basis.
    I have a little knowledge about SSIS and sql server replication but think that above thing can be acheived using SSIS or replication.
    Can anyone help me out which service I should use SSIS or Replication ?
    Thursday, August 27, 2009 4:37 AM

Answers

  • now you need to take decision, better start with SSIS.
    We are here to help you.

    Thanks-


    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    • Marked as answer by Tony Tang_YJ Thursday, September 3, 2009 6:07 AM
    Thursday, August 27, 2009 11:09 AM
  • Thanks

    Could u please suggest which is better in this senario SSIS or replication.AS I am new to both of this and I have little time to implement I need to know which is better for this senario so that I can focus on one of them and learn the related aspects.


    I would suggest to use SSIS.
    If you need to add some transfonmations after some time, you can easily achieve that in SSIS.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by Tony Tang_YJ Thursday, September 3, 2009 6:07 AM
    Thursday, August 27, 2009 11:12 AM

All replies

  • hi,
    this you can easly achive through SSIS. just need to use DFT and define the connection mgr for your both database. for scheduling you need to create a SQL Job and simply schedule it.

    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    • Proposed as answer by Sudeep Raj Thursday, August 27, 2009 8:32 AM
    Thursday, August 27, 2009 4:47 AM
  • Hi,
    Please check the following link for your reference:


    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Thursday, August 27, 2009 5:30 AM
  • Based on the current information you mentioned I'd vote SSIS too. Replication is good at "replicating" data. If you need to extract data based on some business rues, SSIS is more helpful.

    Thanks.
    Liu An - MSFT
    Thursday, August 27, 2009 9:19 AM
  • Thanks for your reply,but I have below senario

    Data that will be moved from Master to other database will be a copy(subset  of data) from Master.For example there are 1000 rows in master but i need to copy only 100 rows to other database.The other database is a clone of master database.Can this be acheived by SSIS.

    Thursday, August 27, 2009 10:37 AM
  • Thanks for your reply,but I have below senario

    Data that will be moved from Master to other database will be a copy(subset  of data) from Master.For example there are 1000 rows in master but i need to copy only 100 rows to other database.The other database is a clone of master database.Can this be acheived by SSIS.

    Thursday, August 27, 2009 10:38 AM
  • this you can acheive in SSIS, but as you informed that you need not to modify your data which is coming from your master. Replication also do this easly.

    if you need to change your data from master to destination database then SSIS will be the best tool for you.

    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Thursday, August 27, 2009 10:42 AM
  • Thanks .

    Can u please tell me where can we write queries to perform this job through replication ?

    And also before moving data from master to other database(clone) I need to check whether both database have same schema or not.It may happen that a table structure of some of the tables is changed on master but same changes have not made on other database.Both master and other database(clone) needs to have same schema (mainly the table structure of the two).

    Can we check the schema differences of database before moving data from master to clone using replication?

    Can we sync or replicate the schema from master to clone database befor making any data migration.? also during sycn data should not be overwritten or deleted from clone database.

    Also this program will run on daily basis so previouly stored data in clone should not be overwirtten.Can we acheive all the above using replication

    Thursday, August 27, 2009 10:58 AM
  • Thanks

    Could u please suggest which is better in this senario SSIS or replication.AS I am new to both of this and I have little time to implement I need to know which is better for this senario so that I can focus on one of them and learn the related aspects.

    Thursday, August 27, 2009 11:00 AM
  • now you need to take decision, better start with SSIS.
    We are here to help you.

    Thanks-


    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    • Marked as answer by Tony Tang_YJ Thursday, September 3, 2009 6:07 AM
    Thursday, August 27, 2009 11:09 AM
  • Thanks

    Could u please suggest which is better in this senario SSIS or replication.AS I am new to both of this and I have little time to implement I need to know which is better for this senario so that I can focus on one of them and learn the related aspects.


    I would suggest to use SSIS.
    If you need to add some transfonmations after some time, you can easily achieve that in SSIS.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by Tony Tang_YJ Thursday, September 3, 2009 6:07 AM
    Thursday, August 27, 2009 11:12 AM