Reporting Database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

csl - 29 Sep 2004 16:48 GMT

We want to create a reporting database from our OLTP database. We are
thinking to take an initial snapshot and then use transactional replication.
However, we also want to keep the OLTP database with only one year of data.
If after one year, we remove records from OLTP, the transactional
replication will be replicated to the reporting database. That means we are
losing data in the reporting database. What is the best way to do this?

Reply to this Message

Keith Kratochvil - 29 Sep 2004 16:54 GMT

"Usually" (but not always) a database created for reporting purposes is a
different strucuture than your OLTP database. This allows you to have a
database layout that is more suited for reporting.

How often do you run reports?
What is your "lag" requirement? Does the data have to exist within the
reporting database the minute it is in production or can there be some lag
(one hour, one day...)?

You might find that you would want to create your own data movement scripts
to move the new data. You could use DTS or stored procedures to move and
massage the data as you need.

Signature

Keith

> We want to create a reporting database from our OLTP database. We are
> thinking to take an initial snapshot and then use transactional replication.
> However, we also want to keep the OLTP database with only one year of data.
> If after one year, we remove records from OLTP, the transactional
> replication will be replicated to the reporting database. That means we are
> losing data in the reporting database. What is the best way to do this?

Reply to this Message

Jaxon - 29 Sep 2004 18:19 GMT

The way we are doing this is as follows:

1. Log Ship From OLTP To a "Standby Server"
2. USE ETL (DTS Packages) to copy the necessary data to our Reporting Server

we discussed Replication but for various reasons we went down the
logshipping path.

cheers

Greg Jackson
PDX, Oregon

Reply to this Message

Hilary Cotter - 30 Sep 2004 03:16 GMT

such business logic is best encapsulated in custom replication stored
procedures. So you could have the delete procedure only delete rows if the
date range is less than one year.

This way legitimate deletes of data younger than a year will be replicated
but deletes which are intended to delete old data on your OLTP server will
not be replicated.

Another hint is to put bogus filters on your articles ie where 1=1 and
modify the filter procs when you are doing deletes on your OLTP database so
they will always return a 0

so do an alter on your filter proc so it looks like this

create procedure [dbo].[FLTR_authors_1__58] for replication as
if exists (select * from [dbo].[authors] where 1=1) return 0 else return 0

instead of

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Reply to this Message

Hilary Cotter - 30 Sep 2004 03:27 GMT

sorry that went out prematurely

do make your filter unresponsive to deletes (and inserts, deletes) while
doing batch deletes do this

alter procedure [dbo].[FLTR_authors_1__58] for replication as
if exists (select * from [dbo].[authors] where 1=1) return 0 else return 0

When you have finished return your filter to normal like this

alter procedure [dbo].[FLTR_authors_1__58] for replication as
if exists (select * from [dbo].[authors] where 1=1) return 1 else return 0

If you do incorporate custom business logic into your delete proc you will
have to modify your delete proc to look like this:

create procedure "sp_MSdel_authors" @pkc1 varchar(11)
as
delete "authors"
where "au_id" = @pkc1
and datecolumn >getdate()-365
--if @@rowcount = 0
-- if @@microsoftversion>0x07320000
-- exec sp_MSreplraiserror 20598

GO

If you don't make an adjustment for a date range in your delete proc you
will get data consistency errors.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

> such business logic is best encapsulated in custom replication stored
> procedures. So you could have the delete procedure only delete rows if the
[quoted text clipped - 24 lines]
> are
> > losing data in the reporting database. What is the best way to do this?

Reply to this Message