Alternatives when trying to keep two databases in sync...

I have two production SQL Server 2005 databases that I want to keep in
sync ever few hours. One database is an OLTP database with high
INSERT activity, the other is strictly for reporting. The OTLP
database will contain data for 24 months of data. The reporting
database will contain that same 24 months plus an additional 8 years of
data.
The tables in each database are identical but I would like to have
different indexing strategies in each database. On OLTP side, indexing
would be minimal to facilitate rapid insert activity. The reporting
database would need many indexes in order to process queries in a
reasonable amount of time.
Is it possible to keep these two databases in sync with log shipping?
>From what I can see, log shipping really wants the two database to be
identical. Having the two different indexing strategies would not be
easy. Keeping more data in the reporting database would also present
some challenges.
Is there a better alternative? One-way transaction level replication
seems like an alternative but seems like it would require a great deal
of maintenance.
I have given Triggers some thought but the developers push back saying
that there are over 50 tables that need to be synchronized and that
maintaining the triggers is to much work.
Any suggestions would be appreciated.
Thank you.
Jim Maurer
DBA
Harleysville Insurance
I think you need to look at Replications, because triggers are hurting
performance and
log shipping is intended to different proposes.
I'm thinking what if you could take one big massive transferring of data
let me say once a day at night by using DTS package , is it accetable at
you company?
<jmaurer@.harleysvillegroup.com> wrote in message
news:1166023880.609621.98730@.79g2000cws.googlegrou ps.com...
>I have two production SQL Server 2005 databases that I want to keep in
> sync ever few hours. One database is an OLTP database with high
> INSERT activity, the other is strictly for reporting. The OTLP
> database will contain data for 24 months of data. The reporting
> database will contain that same 24 months plus an additional 8 years of
> data.
> The tables in each database are identical but I would like to have
> different indexing strategies in each database. On OLTP side, indexing
> would be minimal to facilitate rapid insert activity. The reporting
> database would need many indexes in order to process queries in a
> reasonable amount of time.
> Is it possible to keep these two databases in sync with log shipping?
> identical. Having the two different indexing strategies would not be
> easy. Keeping more data in the reporting database would also present
> some challenges.
> Is there a better alternative? One-way transaction level replication
> seems like an alternative but seems like it would require a great deal
> of maintenance.
> I have given Triggers some thought but the developers push back saying
> that there are over 50 tables that need to be synchronized and that
> maintaining the triggers is to much work.
> Any suggestions would be appreciated.
> Thank you.
> Jim Maurer
> DBA
> Harleysville Insurance
>
|||Thank you. We are begining to realize this is not going to be as easy
as it orginally sounded. Thank you again for your suggestions...
Uri Dimant wrote:[vbcol=seagreen]
> I think you need to look at Replications, because triggers are hurting
> performance and
> log shipping is intended to different proposes.
> I'm thinking what if you could take one big massive transferring of data
> let me say once a day at night by using DTS package , is it accetable at
> you company?
>
>
> <jmaurer@.harleysvillegroup.com> wrote in message
> news:1166023880.609621.98730@.79g2000cws.googlegrou ps.com...