from 5-20 g a week. We are running out of space and want to archive data
older than 45 days off of the database to an archive database that could be
used for reporting.
The question is would replication be the best method for moving data to the
Reporting/Archive database, in that data older than 45 would be deleted from
the OLTP but needs to remain in the Archive. We also do upgrades to our
internal applications about once a month, and sometimes the upgrades involve
database changes.
The suggestion has been made to simply run the OLTP and Reporting from the
same database and simply move it to a larger SAN (and a server that isn't
running Biztalk and housing its databases), since we don't seem to have any
processing or memory issues, unless backups are being done.
I think a separate reporting and OLTP solution would be better in that we
will be continuing to grow in transactions, possibly doubling it this year.
The reports would be for internal purposes and also for web reporting to the
payors and payees we handle. Actually a lot of the database bloat is in XML
overflow from the forms we receive in and that need to be reassembled for
some of the web clients.
Just to note we are using SQL Server 2000 EE active/passive cluster, on
Windows 2003 servers
Any insight or advice welcome.
Nancy
Sounds more like a job for DTS/SSIS. If you delete a replicated record on
the Publisher (OLTP) side, it deletes on the other side (Subscriber) as
well.
Write scripts/packages that move the data that is X days old or has been
changed since Y days to the other database. You will of course need some
way to determine when a record changed or was deleted/inserted...
Other opinions may vary
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:emsbntFTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> We have a high volume OLTP database of about 300 g and growing at anywhere
> from 5-20 g a week. We are running out of space and want to archive data
> older than 45 days off of the database to an archive database that could
> be used for reporting.
> The question is would replication be the best method for moving data to
> the Reporting/Archive database, in that data older than 45 would be
> deleted from the OLTP but needs to remain in the Archive. We also do
> upgrades to our internal applications about once a month, and sometimes
> the upgrades involve database changes.
> The suggestion has been made to simply run the OLTP and Reporting from the
> same database and simply move it to a larger SAN (and a server that isn't
> running Biztalk and housing its databases), since we don't seem to have
> any processing or memory issues, unless backups are being done.
> I think a separate reporting and OLTP solution would be better in that we
> will be continuing to grow in transactions, possibly doubling it this
> year. The reports would be for internal purposes and also for web
> reporting to the payors and payees we handle. Actually a lot of the
> database bloat is in XML overflow from the forms we receive in and that
> need to be reassembled for some of the web clients.
> Just to note we are using SQL Server 2000 EE active/passive cluster, on
> Windows 2003 servers
> Any insight or advice welcome.
> Nancy
>
|||The problem is that both databases need to be insync,
with the Archive database holding all the data and the OLTP holding all the
"current" data. The only difference will be that whatever data is deleted
by the "Archiving process" will be deleted from OLTP but should not be
deleted from Archive/Rpt DB. That is the hard part to work around. Unless
you push all data to Archive and run any "non-Archive deletes" on Archive
separately, but that might be a maintenance nightmare.
But you've given me more to think about.
Thanks,
Nancy
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23F3F$%23FTHHA.3948@.TK2MSFTNGP05.phx.gbl...
> Sounds more like a job for DTS/SSIS. If you delete a replicated record on
> the Publisher (OLTP) side, it deletes on the other side (Subscriber) as
> well.
> Write scripts/packages that move the data that is X days old or has been
> changed since Y days to the other database. You will of course need some
> way to determine when a record changed or was deleted/inserted...
> Other opinions may vary
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:emsbntFTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||You can use replication for this, and just have deletes be effective is they
are on data recent than your archive interval. You will need a filter for
this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:emsbntFTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> We have a high volume OLTP database of about 300 g and growing at anywhere
> from 5-20 g a week. We are running out of space and want to archive data
> older than 45 days off of the database to an archive database that could
> be used for reporting.
> The question is would replication be the best method for moving data to
> the Reporting/Archive database, in that data older than 45 would be
> deleted from the OLTP but needs to remain in the Archive. We also do
> upgrades to our internal applications about once a month, and sometimes
> the upgrades involve database changes.
> The suggestion has been made to simply run the OLTP and Reporting from the
> same database and simply move it to a larger SAN (and a server that isn't
> running Biztalk and housing its databases), since we don't seem to have
> any processing or memory issues, unless backups are being done.
> I think a separate reporting and OLTP solution would be better in that we
> will be continuing to grow in transactions, possibly doubling it this
> year. The reports would be for internal purposes and also for web
> reporting to the payors and payees we handle. Actually a lot of the
> database bloat is in XML overflow from the forms we receive in and that
> need to be reassembled for some of the web clients.
> Just to note we are using SQL Server 2000 EE active/passive cluster, on
> Windows 2003 servers
> Any insight or advice welcome.
> Nancy
>
|||So in essence we would be filtering out any deletes of data older than 45
days (or however they decide to do the Archiving)? How would you prevent
the deletes from being pushed over to the subscriber? I'm having a hard time
picture a filter to prevent deletes from being pushed.
Anyway, thank you for your time and answer.
Nancy Lytle
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eylT5YHTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> You can use replication for this, and just have deletes be effective is
> they are on data recent than your archive interval. You will need a filter
> for this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:emsbntFTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||There are several approaches to this. If there is a date column on each
table you are replicating you could include this logic in your proc.
Here is an example :
create procedure "sp_MSdel_dbotest"
@.pkc1 int
as
/*
begin
delete "dbo"."test"
where "pk" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
*/
delete "dbo"."test"
where "pk" = @.pkc1 and DateColumn>getdate()-25
You can also do your the delete statement which does your archive by
replicating the execution of a stored procedure and have the stored
procedure do nothing on the subscriber.
Another option is to put a dummy filter on the tables you are replicating,
and toggle it so it returns false when you are doing the archiving deletes.
In this situation all DML will not be replicated so it is not optimal but
can work for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:eev7s4HTHHA.3592@.TK2MSFTNGP03.phx.gbl...
> So in essence we would be filtering out any deletes of data older than 45
> days (or however they decide to do the Archiving)? How would you prevent
> the deletes from being pushed over to the subscriber? I'm having a hard
> time picture a filter to prevent deletes from being pushed.
> Anyway, thank you for your time and answer.
> Nancy Lytle
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eylT5YHTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||Thank you very much for the explanation and options to work with.
I am sure replication can work in this situation, I think the developers
have just been wary of it best of problems we have had when they have run
update scripts on OLTP and Reporting, without mentioning schema changes,
adding/deleting columns, which we find out when the replication breaks. I
think that is one reason they are afraid to start Archiving with
replication.
But again, thank you so much for your advice and assistance.
Nancy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uKRpy0ITHHA.1600@.TK2MSFTNGP05.phx.gbl...
> There are several approaches to this. If there is a date column on each
> table you are replicating you could include this logic in your proc.
> Here is an example :
>
> create procedure "sp_MSdel_dbotest"
> @.pkc1 int
> as
> /*
> begin
> delete "dbo"."test"
> where "pk" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> */
> delete "dbo"."test"
> where "pk" = @.pkc1 and DateColumn>getdate()-25
> You can also do your the delete statement which does your archive by
> replicating the execution of a stored procedure and have the stored
> procedure do nothing on the subscriber.
> Another option is to put a dummy filter on the tables you are replicating,
> and toggle it so it returns false when you are doing the archiving
> deletes. In this situation all DML will not be replicated so it is not
> optimal but can work for you.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:eev7s4HTHHA.3592@.TK2MSFTNGP03.phx.gbl...
>
No comments:
Post a Comment