|
|
You're in the
sql server replication
group:replication without deletes?
sql server replication:
I have a database that is going to be replicated. I need to keep only about 3 months worth of data but the replicated database needs to keep about 3 years worth of data for reporting. What's the easiest way to do this? Is there a way to have replication not replicate the deletes? I really only dealing with one table in the database. Thanks in advance.
Hi Sherpa,
From your descriptions, I understood that you would like to filter the data within three months. Have I understood you? Correct me if I was wrong. Based on my scope, your table must have an column to specify the datetime like this CREATE TABLE [dbo].[Table1] ( [c1] [char] (10) NULL , [d1] [datetime] NOT NULL , [c2] [char] (10) NULL ) ON [PRIMARY] GO Then, you could use Data Filter to define the data you want to replicated vertically and horizontally. Like the sampel below, you could noticed that where clause is defined in @filter_clause as d1< getdate() and d1 [quoted text, click to view] >dateadd(month, -3,getdate()). -- Adding the article's partition column(s) exec sp_articlecolumn @publication = N'mingqing', @article = N'Table1', @column = N'c1', @operation = N'add' GO exec sp_articlecolumn @publication = N'mingqing', @article = N'Table1', @column = N'd1', @operation = N'add' GO -- Adding the article filter EXEC sp_articlefilter @publication = N'mingqing', @article = N'Table1', @filter_name = N'dbo.FLTR_Table1_1__51', @filter_clause = N'd1< getdate() and d1 >dateadd(month, -3,getdate())' GO In the dynamic filter, you can specify a function or a user-defined function that is evaluated differently for each Subscriber based on the connection properties of the Merge Agent when the merge process is replicating data between the Subscriber and Publisher. Besides HOST_NAME(), the system function SUSER_SNAME() can also be used for this purpose. You can also use a user-defined function in a dynamic filter, but unless the user-defined function definition includes SUSER_SNAME(), HOST_NAME(), or the user-defined function evaluates one of these system functions in the filter criteria (such as MyUDF(SUSER_SNAME()), the user-defined function will be static. Thank you for your patience and corperation. If you have any questions or concerns, don't hesitate to let me know. We are here to be of assistance! Sincerely yours, Mingqing Cheng Online Partner Support Specialist Partner Support Group Microsoft Global Technical Support Center --------------------------------------------------------------- Introduction to Yukon! - http://www.microsoft.com/sql/yukon This posting is provided "as is" with no warranties and confers no rights. Please reply to newsgroups only, many thanks!
If you are using Transactional Replication, you can type
none, for the delete command to be replicated (sp_addarticle, @del_cmd = NONE). HTH, Paul Ibison (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
you missunderstood perhaps I should have explained better.
On my publisher I want 3 months, My subscriber I would like 3 years. any ideas? [quoted text, click to view] ""Mingqing Cheng [MSFT]"" <v-mingqc@online.microsoft.com> wrote in message news:xg6yvW3pEHA.3356@cpmsftngxa06.phx.gbl... > Hi Sherpa, > > From your descriptions, I understood that you would like to filter the data > within three months. Have I understood you? Correct me if I was wrong. > > Based on my scope, your table must have an column to specify the datetime > like this > > CREATE TABLE [dbo].[Table1] ( > [c1] [char] (10) NULL , > [d1] [datetime] NOT NULL , > [c2] [char] (10) NULL > ) ON [PRIMARY] > GO > > Then, you could use Data Filter to define the data you want to replicated > vertically and horizontally. Like the sampel below, you could noticed that > where clause is defined in @filter_clause as d1< getdate() and d1 > >dateadd(month, -3,getdate()). > > -- Adding the article's partition column(s) > exec sp_articlecolumn @publication = N'mingqing', @article = N'Table1', > @column = N'c1', @operation = N'add' > GO > exec sp_articlecolumn @publication = N'mingqing', @article = N'Table1', > @column = N'd1', @operation = N'add' > GO > > -- Adding the article filter > EXEC sp_articlefilter @publication = N'mingqing', @article = N'Table1', > @filter_name = N'dbo.FLTR_Table1_1__51', @filter_clause = N'd1< getdate() > and d1 >dateadd(month, -3,getdate())' > GO > > In the dynamic filter, you can specify a function or a user-defined > function that is evaluated differently for each Subscriber based on the > connection properties of the Merge Agent when the merge process is > replicating data between the Subscriber and Publisher. Besides HOST_NAME(), > the system function SUSER_SNAME() can also be used for this purpose. > > You can also use a user-defined function in a dynamic filter, but unless > the user-defined function definition includes SUSER_SNAME(), HOST_NAME(), > or the user-defined function evaluates one of these system functions in the > filter criteria (such as MyUDF(SUSER_SNAME()), the user-defined function > will be static. > > > Thank you for your patience and corperation. If you have any questions or > concerns, don't hesitate to let me know. We are here to be of assistance! > > > Sincerely yours, > > Mingqing Cheng > > Online Partner Support Specialist > Partner Support Group > Microsoft Global Technical Support Center > --------------------------------------------------------------- > Introduction to Yukon! - http://www.microsoft.com/sql/yukon > This posting is provided "as is" with no warranties and confers no rights. > Please reply to newsgroups only, many thanks! > >
you have to encorporate the delete functionality into your custom delete
replication stored procedure. This is what your custom delete proc would look like create procedure "sp_MSdel_authors" @pkc1 varchar(11) as delete "authors" where "au_id" = @pkc1 and DateColumn > getdate()-90 --if @@rowcount = 0 -- if @@microsoftversion>0x07320000 -- exec sp_MSreplraiserror 20598 GO -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html [quoted text, click to view] "Sherpa" <NoSpam@Nospam.com> wrote in message news:OjHY3i8pEHA.3592@TK2MSFTNGP09.phx.gbl... > you missunderstood perhaps I should have explained better. > > On my publisher I want 3 months, > My subscriber I would like 3 years. > > any ideas? > > > > ""Mingqing Cheng [MSFT]"" <v-mingqc@online.microsoft.com> wrote in message > news:xg6yvW3pEHA.3356@cpmsftngxa06.phx.gbl... > > Hi Sherpa, > > > > From your descriptions, I understood that you would like to filter the > data > > within three months. Have I understood you? Correct me if I was wrong. > > > > Based on my scope, your table must have an column to specify the datetime > > like this > > > > CREATE TABLE [dbo].[Table1] ( > > [c1] [char] (10) NULL , > > [d1] [datetime] NOT NULL , > > [c2] [char] (10) NULL > > ) ON [PRIMARY] > > GO > > > > Then, you could use Data Filter to define the data you want to replicated > > vertically and horizontally. Like the sampel below, you could noticed that > > where clause is defined in @filter_clause as d1< getdate() and d1 > > >dateadd(month, -3,getdate()). > > > > -- Adding the article's partition column(s) > > exec sp_articlecolumn @publication = N'mingqing', @article = N'Table1', > > @column = N'c1', @operation = N'add' > > GO > > exec sp_articlecolumn @publication = N'mingqing', @article = N'Table1', > > @column = N'd1', @operation = N'add' > > GO > > > > -- Adding the article filter > > EXEC sp_articlefilter @publication = N'mingqing', @article = N'Table1', > > @filter_name = N'dbo.FLTR_Table1_1__51', @filter_clause = N'd1< getdate() > > and d1 >dateadd(month, -3,getdate())' > > GO > > > > In the dynamic filter, you can specify a function or a user-defined > > function that is evaluated differently for each Subscriber based on the > > connection properties of the Merge Agent when the merge process is > > replicating data between the Subscriber and Publisher. Besides > HOST_NAME(), > > the system function SUSER_SNAME() can also be used for this purpose. > > > > You can also use a user-defined function in a dynamic filter, but unless > > the user-defined function definition includes SUSER_SNAME(), HOST_NAME(), > > or the user-defined function evaluates one of these system functions in > the > > filter criteria (such as MyUDF(SUSER_SNAME()), the user-defined function > > will be static. > > > > > > Thank you for your patience and corperation. If you have any questions or > > concerns, don't hesitate to let me know. We are here to be of assistance! > > > > > > Sincerely yours, > > > > Mingqing Cheng > > > > Online Partner Support Specialist > > Partner Support Group > > Microsoft Global Technical Support Center > > --------------------------------------------------------------- > > Introduction to Yukon! - http://www.microsoft.com/sql/yukon > > This posting is provided "as is" with no warranties and confers no rights. > > Please reply to newsgroups only, many thanks! > > > > > >
Hi Sherpa,
I would like to thanks Hilary Cotter for his greatest solution for this issue. I wanted to post a quick note to see if you would like additional assistance or information regarding this particular issue. We appreciate your patience and look forward to hearing from you! Sincerely yours, Mingqing Cheng Online Partner Support Specialist Partner Support Group Microsoft Global Technical Support Center --------------------------------------------------------------- Introduction to Yukon! - http://www.microsoft.com/sql/yukon This posting is provided "as is" with no warranties and confers no rights. Please reply to newsgroups only, many thanks!
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |