Best Way of data transfer

Hello All,
I am a newbie dba and need some expert advice on one of my development
scenario I am currently struck with. I am in process of designing a
reporting solution for my company. It is going to be a web based
intranet thin client multitiered application using sql server 2000 and
..net platform.
This application basically shows the real time KPI's or statistics in
different forms of reports on an hourly basis to the senior managers to
right on their desktop. Eventually these reports will help them in
decision making for the better performance of the business...
My question here is what is the best way of transferring large chunk of
data (not entire table(s)) from production server (SQL 2000) to a
reporting server or staging database with an hourly refresh without
stressing the production environment?
Is it a) Replication preferably snapshot? Or
b) BCP? or
c) DTS?
Or do you suggest any better way of achieving this task?
Any suggestion or tips would be greatly appreciated.
Looking forward for your responses.
Many Thanks,
AK
I'd recommend using transactional replication - after the snapshot, it'll
just take the changes to the data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks very much for your reply. Paul,
Is it viable to do hourly refresh through out the day using your
advised approach? Will there be any performance issues?
Thanks very much for your reply.
Ak
Paul Ibison wrote:
> I'd recommend using transactional replication - after the snapshot, it'll
> just take the changes to the data.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log-reader will ov course affect the production system, and if you have
a publisher/distributor, there will be disk access required to write and
read from the distribution database. Exactly what this all amounts to is
difficult to say and is more empirically determined, although the section
entitled "Cost of Transactional Replication at the Publisher" in this
article will give you some idea:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.
The other thing to take into account is the effect on reporting queries to
have the distribution agent aplying transactions, and the consequential
potential blocking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I would use transactional replication as it offers the lowest latency.
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
"AK" <arshad.khan@.policyadmin.co.uk> wrote in message
news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...
> Hello All,
> I am a newbie dba and need some expert advice on one of my development
> scenario I am currently struck with. I am in process of designing a
> reporting solution for my company. It is going to be a web based
> intranet thin client multitiered application using sql server 2000 and
> .net platform.
> This application basically shows the real time KPI's or statistics in
> different forms of reports on an hourly basis to the senior managers to
> right on their desktop. Eventually these reports will help them in
> decision making for the better performance of the business...
>
> My question here is what is the best way of transferring large chunk of
> data (not entire table(s)) from production server (SQL 2000) to a
> reporting server or staging database with an hourly refresh without
> stressing the production environment?
> Is it a) Replication preferably snapshot? Or
> b) BCP? or
> c) DTS?
> Or do you suggest any better way of achieving this task?
> Any suggestion or tips would be greatly appreciated.
> Looking forward for your responses.
>
> Many Thanks,
> AK
>
|||Thanks very much for all your replies guys.
I will give at a go this way then. Will post more queries on this
thread if i get stuck any where.
AK
Hilary Cotter wrote:[vbcol=seagreen]
> I would use transactional replication as it offers the lowest latency.
> --
> 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
>
> "AK" <arshad.khan@.policyadmin.co.uk> wrote in message
> news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...