Replication

  • What are the issues with using replication to move data between a live database and one used for reporting?

    is this the best way to get real time data into a reporting database?

  • erics44 (9/5/2012)


    What are the issues with using replication to move data between a live database and one used for reporting?

    is this the best way to get real time data into a reporting database?

    There is no "best way". It depends on your situation.

    For near real time you will need to use transactional replication and control access to prevent reporting data modification.

  • reporting data could change from time to time, why would you need to prevent this?

    sorry i dont really understand exactly what replication is all about

  • Replication uses magazine/newspaper terminology.

    Publisher = Source Server

    Subscriber = Destination Server

    Article = Table/Stored procedure/view

    Publication = Group of articles

    You would want to limit access to the subscriber as there is very little to prevent someone accessing the subscriber and changing/removing data. Its a rare occurrence but very possible that an application with R/W functionality is accidentally pointed at a reporting server. Its not to big an issue but can break reports and cause replication errors.

  • ahh i see

    so you mean its good to prevent someone being able to go into the reporting server and changing data where as the data should be changed in the live server

    another question, does replication have to be a direct copy of the live server?

    so can you change it so when a transaction is updated in the live database the information is stored in the replicated database in a different way?

    to try and clarify what i mean, you might have 2 tables in the live database (linked by a foreign key perhaps) that you want to store as a single table in the replicated database, so when the replication happens it brings through the foreign key from the other table too

  • erics44 (9/5/2012)


    ahh i see

    so you mean its good to prevent someone being able to go into the reporting server and changing data where as the data should be changed in the live server

    another question, does replication have to be a direct copy of the live server?

    so can you change it so when a transaction is updated in the live database the information is stored in the replicated database in a different way?

    to try and clarify what i mean, you might have 2 tables in the live database (linked by a foreign key perhaps) that you want to store as a single table in the replicated database, so when the replication happens it brings through the foreign key from the other table too

    SqlServer will prevent you from updating replicated data. As to your other question about storing 2 tables in 1, replication will not handle that. you would have to write a process to do that on your own.

  • ok perhaps replication isnt what i need

    it seems like more of a dev / back up / disaster recovery method

    I am looking into ways of moving data into a reporting database and trying to keep the data real time

    if i need to write procedures that will organise the data after the replication then this kind of defeats the object for me

    i think 🙂

  • Not in the way you describe.

    You can publish tables using a foreign key constraint type link using Merge replication but not to a single table. i.e. you can publish the primary table with a filter, call it WHERE active=1, and because of the publication links the related records to those filtered would accompany the row(s) in the primary table.

    You "could" create a customized process to merge the data on the subscriber. Maybe a logical database with only cross database views would work for you to merge the data.

  • erics44 (9/5/2012)


    ok perhaps replication isnt what i need

    it seems like more of a dev / back up / disaster recovery method

    I am looking into ways of moving data into a reporting database and trying to keep the data real time

    if i need to write procedures that will organise the data after the replication then this kind of defeats the object for me

    i think 🙂

    triggers aren't always the best idea, but might be something you want here (on insert/update/delete). you can use it to insert into another table that "flattens" the data you want out.

  • crashdan (9/5/2012)


    SqlServer will prevent you from updating replicated data.

    Sorry. That is incorrect. It will not.

  • thanks for the replies

    i obviously still do have options with replication, triggers and such as mentioned

    one more question, if i am using all of these processes will the performance of the live database be affected much?

    so if i have a trigger on the replicated database, I know how tempremental triggers can be, could a broken trigger on the replicated database affect the live in any way?

  • MysteryJimbo (9/5/2012)


    crashdan (9/5/2012)


    SqlServer will prevent you from updating replicated data.

    Sorry. That is incorrect. It will not.

    I stand corrected. I looked into my permissions and only allow selects. Thank you for the correction

  • erics44 (9/5/2012)


    what do you think about my moving data to a reporting database issue?

    It sounds like you are looking for 2 different things. one to move data to another database for reporting, and a second piece to flatten that data out to one table.

    You can use replication for the first piece, but that second piece will require some coding.

  • crashdan (9/5/2012)


    erics44 (9/5/2012)


    what do you think about my moving data to a reporting database issue?

    It sounds like you are looking for 2 different things. one to move data to another database for reporting, and a second piece to flatten that data out to one table.

    You can use replication for the first piece, but that second piece will require some coding.

    yep i think so

    can a broken trigger say in the replicated database affect the live database at all?

    so if for some reason the replication fails due to the trigger will that be all that fails?

  • erics44 (9/5/2012)


    crashdan (9/5/2012)


    erics44 (9/5/2012)


    what do you think about my moving data to a reporting database issue?

    It sounds like you are looking for 2 different things. one to move data to another database for reporting, and a second piece to flatten that data out to one table.

    You can use replication for the first piece, but that second piece will require some coding.

    yep i think so

    can a broken trigger say in the replicated database affect the live database at all?

    so if for some reason the replication fails due to the trigger will that be all that fails?

    depends on the type of replication you use. I would use transactional replication, the build from there. The only way it could affect the live data would be if the trigger were written to touch the live data.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply