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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » using replication in sql server 2008 r2 SE...

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

Rate Topic

Display Mode

Topic Options

Author

Message

AccDba

Posted Friday, February 01, 2013 12:46 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 2:23 PM
Points: 6, Visits: 26

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

Post #1414814

AccDba

Posted Sunday, February 03, 2013 5:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 2:23 PM
Points: 6, Visits: 26

Experts I need your inputs...

Post #1415011

AccDba

Posted Sunday, February 03, 2013 10:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 2:23 PM
Points: 6, Visits: 26

oops..is everyone busy?

Post #1415106

Lokesh Vij

Posted Sunday, February 03, 2013 11:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 1,230, Visits: 1,366

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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1415111

EdVassie

Posted Monday, February 04, 2013 8:14 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:46 AM
Points: 2,636, Visits: 2,788

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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 4 June 2013: now over 24,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #1415332

Lokesh Vij

Posted Monday, February 04, 2013 8:38 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 1,230, Visits: 1,366

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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1415347

« Prev Topic | Next Topic »

Permissions