Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need suggestions on replication Expand / Collapse
Author
Message
Posted Sunday, February 3, 2013 3:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 6, 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 #1415066
Posted Sunday, February 3, 2013 11:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1415122
Posted Monday, February 4, 2013 2:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:12 AM
Points: 1,541, Visits: 8,191
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'.


BrainDonor
Linkedin
Blog Site
Post #1415192
Posted Tuesday, February 5, 2013 12:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 6, 2013 2:23 PM
Points: 6, Visits: 26
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
Post #1416036
Posted Tuesday, February 5, 2013 4:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:33 AM
Points: 273, Visits: 851
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)
Post #1416157
Posted Wednesday, February 6, 2013 8:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 6, 2013 2:23 PM
Points: 6, Visits: 26
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,

Post #1416553
Posted Wednesday, February 6, 2013 2:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 1,321, Visits: 1,729
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
Post #1416740
Posted Thursday, April 18, 2013 10:58 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 353, Visits: 868
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.
Post #1444016
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse