Re: Design choice in the Use of SQl Server Views for ETL

Hello jeje,

The queries (or the views) that need to be built will be fairly complex and
will involve a large number of records. Now coming to moving data from M to
MCopy, it will not be done using views. Since M is in a SQl 2000 server and
MCopy will be in a SQl 2005 server, I cant use Log shipping to do this. I am
planning to do a full backup and then regular differential backups of the
database M. I shall then create some SQL jobs based on some T-SQl script
which will then do a full restore and a differential restore based on a set
schedule. This would keep my Mcopy as uptodate as I need to M.

All the views etc will then run off MCopy rather than M and will be the
source to populate my ODS.

Workable do you think and also doing you think?

Thanks,

RK

"Jeje" wrote:

well... the impact depends on the complexity of your queries.
if your queries do large and complex joins, then the impact can be important
on the M server. so going through MCopy is a good idea.

moving data from M to MCOPY or M to ODS (using simple views) should results
on the same performance, but the design is simplified.

compare the price of maintaining MCOPY versus adding memory and disks on the
M server. adding disks and memory can improve the end user access and your
design, so everybody is happy.


"RK" <RK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7884C0B1-CE23-441A-8805-D1DFEDA5B7F2@xxxxxxxxxxxxxxxx
Hi Jeje,

Wont the use of views based in the database M cause performance problems.
For instance if I were to use normal data views every time a view is run
as a
part of the ETL, its going to run a query on the M database further
constraining performance of the already constrained database M.

Using indexed view directly on database M in that server would mean the
overhead of having to maintain the data in the indexed views by the
updates,
inserts and deletes happening in the underlying tables in database M means
again potentially poor response times on the application runnng based on
database M.

Hope you understand my concern.

Thanks

RK

"Jeje" wrote:

your solution M --> MCOPY --> ODS appear to be a good one.
but have you try to do M --> ODS?
maybe you can upgrade the M server to support more queries, this solution
simplify the overall process.

where do you want to use indexed views?
if it's on the MCOPY server, there is no advantage of these indexed
views.
but using views as the source of your ETL process is a good option.



.

Relevant Pages

  • Re: Design choice in the Use of SQl Server Views for ETL
    ... maybe you can upgrade the M server to support more queries, this solution simplify the overall process. ... 2000 database. ... Server 2005 server and lets call this database as MCOPY. ... similar structure in the ODS using SSIS packages. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Design choice in the Use of SQl Server Views for ETL
    ... if your queries do large and complex joins, then the impact can be important on the M server. ... moving data from M to MCOPY or M to ODS should results on the same performance, ... Wont the use of views based in the database M cause performance problems. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Design choice in the Use of SQl Server Views for ETL
    ... backup/restore is a good option. ... this method allows you to copy only required content and also give you the option to create different indexes on the MCopy server. ... MCopy, it will not be done using views. ... > Wont the use of views based in the database M cause performance> problems. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Update with SQL Data Adapter
    ... If all you are doing is moving data from database table to database table ... >> procedure on the server. ...
    (microsoft.public.dotnet.framework.adonet)
Loading