February 3, 2013 at 3:59 pm
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 11:43 pm
AccDba (2/3/2013)
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.
How will you uodate the tables in NEw database from main database. ? Batch process ? Partitoning ?
AccDba (2/3/2013)
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.
Where do you run this job , on Main(source) database ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 4, 2013 at 2:20 am
I assume that you want to replicate insertions from the Publisher, but keep them on the Subscriber for longer; so when they are deleted from the Publisher the Subscriber still has them?
I don't have a development environment in front of me right now, but you can replicate just the inserts to a subscriber database. Using this method you can keep data on the Subscriber for a different length of time than the Publisher.
The command for adding articles (sp_addarticle) has parameters (if I recall correctly) for @ins_cmd (the insert process), @del_cmd(for the deletion process) and @upd_cmd(for the update process).
If you have 'NONE' set for @del_cmd and @upd_cmd then the replication will only process Inserts; Deletions and Updates won't have any impact on the Subscriber.
You can achieve this by scripting the Add Article or within the Wizard there is a 'Statement Delivery' section within the 'Article Properties' option when selecting the Articles. 'Update Delivery format' can be set to 'Do Not replicate' and 'Delete delivery format' set to 'Do not Replicate'.
February 5, 2013 at 12:35 pm
Bhuvnesh (2/3/2013)
AccDba (2/3/2013)
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.How will you uodate the tables in NEw database from main database. ? Batch process ? Partitoning ?
I am thinking about transaction replication where the publisher, distributor and subscriber are located on the same database server instance.
AccDba (2/3/2013)
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.Where do you run this job , on Main(source) database ?
The purge/delete job is run on the source database which the publisher database. But I came to know that we have a fix for this as mentioned in the below url:
http://www.mssqltips.com/sqlservertip/2520/options-to-not-replicate-sql-server-delete-commands/
But can you tell me if there is a way that I can restrict the updates specific only to a column? Lets say if updates occur on source table(publisher database) on column a, column b and column c then I dont want any thing to get updated in the destination database tables (subscriber database). But if update occurs on column d on source table(publisher database) then I want it to get updated in destination table too (subscriber database). Is this possible?
Thanks Again
February 5, 2013 at 4:26 pm
You could customize the replication update proc on the subscriber to only update the columns you want.
You custom proc script would have to be a post_snapshot_script (@post_snapshot_script in sp_add_publication)
February 6, 2013 at 8:38 am
I found the stored proc that needs to be modified. I am not sure where the changes have to be made and I need some help from experts on this. In the stored proc below I want to update subscriber tables only if col6 value gets updated and do nothing if other columns get updated.
USE [CCF_SubscriberDB]
GO
/****** Object: StoredProcedure [dbo].[sp_MSupd_dbo2006] Script
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_MSupd_dbo2006]
@c1 datetime = NULL,
@c2 int = NULL,
@c3 int = NULL,
@c4 int = NULL,
@c5 int = NULL,
@c6 int = NULL,
@c7 money = NULL,
@c8 nvarchar(20) = NULL,
@pkc1 datetime = NULL,
@bitmap binary(1)
as
begin
if (substring(@bitmap,1,1) & 1 = 1))
begin
update [dbo].[2006] set
[Date] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [Date] end,
[col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,
[col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,
[col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,
[col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,
[col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,
[col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,
[col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] end
where [Date] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [dbo].[2006] set
[col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,
[col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,
[col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,
[col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,
[col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,
[col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,
[col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] end
where [Date] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
end
GO
Thanks,
February 6, 2013 at 2:28 pm
AccDba (2/6/2013)
I found the stored proc that needs to be modified. I am not sure where the changes have to be made and I need some help from experts on this. In the stored proc below I want to update subscriber tables only if col6 value gets updated and do nothing if other columns get updated.
USE [CCF_SubscriberDB]
GO
/****** Object: StoredProcedure [dbo].[sp_MSupd_dbo2006] Script
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_MSupd_dbo2006]
@c1 datetime = NULL,
@c2 int = NULL,
@c3 int = NULL,
@c4 int = NULL,
@c5 int = NULL,
@c6 int = NULL,
@c7 money = NULL,
@c8 nvarchar(20) = NULL,
@pkc1 datetime = NULL,
@bitmap binary(1)
as
begin
if (substring(@bitmap,1,1) & 1 = 1))
begin
update [dbo].[2006] set
[Date] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [Date] end,
[col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,
[col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,
[col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,
[col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,
[col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,
[col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,
[col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] end
where [Date] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [dbo].[2006] set
[col1] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [col1] end,
[col2] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [col2] end,
[col3] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [col3] end,
[col4] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [col4] end,
[col5] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [col5] end,
[col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end,
[col7] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [col7] end
where [Date] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
end
GO
Thanks,
In the above update statement delete columns except for col6 and then add only the below update in both places:
update dbo.2006 set [col6] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [col6] end where
[Date] = @pkc1
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
April 18, 2013 at 10:58 am
Snapshot replication is usually a good option if you generally have lots of activity happening on a large database. I would not recommend snapshot replication since this would replace the historical data in your reporting system with just that days data available in the production environment.
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply