Post reply

using replication in sql server 2008 r2 SE (OS: windows server 2008 R2 SE)

  • February 1, 2013 at 12:46 pm

    #288416

    We are implementing a new architecture for one our modules. There are 100 tables in the database and out of which 45 tables are using for reporting extensively.

    However I want to seperate out to the tables used for reporting meaning I will create a new database and put these 45 tables keep actual transactional database small and slim. So its like having a copy of the same tables but this copies will be used for reporting only from a different database. The retention of data in source tables is 1 day where as the retention for the data in reporting(or destination) tables is an year and a half. so I was thinking of implementing replication based on a condition like based on a specific column value. But the issue here is when I run a purge job (maintenance) on a nightly basis on the transactional tables how would i prevent the impact of not getting the data deleted from the reporting tables. The application that reads data from reporting tables needs the data to present all the time (cannot break replication at any given point of time meaning data should get replicated continuously)

    Appreciate your thoughts and inputs

  • February 3, 2013 at 5:53 am

    #1583709

    Experts I need your inputs...

  • February 3, 2013 at 10:42 pm

    #1583790

    oops..is everyone busy?

  • February 3, 2013 at 11:12 pm

    #1583794

    Your issue is not very clear! still trying to answer partially

    If the retention of data in source is only 1 day, for reporting tables create a staging area in Reporting DB. Before purging the data in reporting tables (in source), move the data to the staging area.

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • February 4, 2013 at 8:14 am

    #1583981

    You can set up replication so that insert and update are replicated but delete is ignored. Books Online has some advice on this and Google can find you more.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • February 4, 2013 at 8:38 am

    #1583994

    Thanks EdVassie, you reminded me of this.

    OP, here is how this can be done.

    In SSMS, right click on “Local publication” and click “properties”. In the “Publication properties” window, click on “Articles”, in the left menu. Select the Article (used for reporting), Click on “Article Properties “and choose, “Set properties of Highlighted table article”.

    In the article properties window, change the "DELETE delivery format" to "Do not replicate DELETE statements".

    Reinitialize your subscripts and you are done.

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

Viewing 6 posts - 1 through 5 (of 5 total)

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