Real Time DSS

Right now we tend to provide extracts to our data warehousing system once a
day. Users seem to be presseing for near real time warehousing. It seems to
be a lot of processing to happen on the production side to get this data
during regular hours and hence the nightly extracts..
I am critical to know how we can provide such a real time solution and not
affect the OLTP performance..
Thanks
There are ways they are usually products by third parties like Informatica
or Ascential. However, this sounds far more like an ODS or reporting
database than a warehouse. The solutions range in complexity and cost from
simple replication of require tables for reporting to providing a service
oriented architecture where transactions can be posted in multiple
directions.
Post more details like number of transactions, size of warehouse, DW
architecture, ETL tools in use and perhaps the group can provider further
advice.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
> Right now we tend to provide extracts to our data warehousing system once
> a
> day. Users seem to be presseing for near real time warehousing. It seems
> to
> be a lot of processing to happen on the production side to get this data
> during regular hours and hence the nightly extracts..
> I am critical to know how we can provide such a real time solution and not
> affect the OLTP performance..
> Thanks
>
|||What is SOA ? I keep hearing about it. Can you send me a good reading link
to that
"Ray" <someone@.nowhere.com> wrote in message
news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.c om...
> There are ways they are usually products by third parties like Informatica
> or Ascential. However, this sounds far more like an ODS or reporting
> database than a warehouse. The solutions range in complexity and cost
from[vbcol=seagreen]
> simple replication of require tables for reporting to providing a service
> oriented architecture where transactions can be posted in multiple
> directions.
> Post more details like number of transactions, size of warehouse, DW
> architecture, ETL tools in use and perhaps the group can provider further
> advice.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
once[vbcol=seagreen]
not
>
|||why don't you try having 2 AS servers pointing to the same Warehouse
(cleaned) databases for their source dimensions and facts. Then have the
application switch between them (via control table indicating which AS is
currently processing data and which one is not so the app would call a stored
procedure to look in the control table to tell it which AS it should connect
to get data). This way you can process the cubes at appropriate time
(whether at a regular time interval or when data volume coming into warehouse
tables met certain row count threshold) then switch over to the other server
to process it also when you are done with the first one.
The user experience is that they will see data is always available as close
to real time as possible...depending on your processing time. By the way,
AS2005 can 'sniff' your warehouse source tables for changes in data to update
the cube automatically (you can do the same now in AS2000 but via ROLAP
storage model which looks at your fact tables so indexing is critical here).
"Hassan" wrote:

> What is SOA ? I keep hearing about it. Can you send me a good reading link
> to that
> "Ray" <someone@.nowhere.com> wrote in message
> news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.c om...
> from
> once
> not
>
>

Real Time DSS

Right now we tend to provide extracts to our data warehousing system once a
day. Users seem to be presseing for near real time warehousing. It seems to
be a lot of processing to happen on the production side to get this data
during regular hours and hence the nightly extracts..
I am critical to know how we can provide such a real time solution and not
affect the OLTP performance..
ThanksThere are ways they are usually products by third parties like Informatica
or Ascential. However, this sounds far more like an ODS or reporting
database than a warehouse. The solutions range in complexity and cost from
simple replication of require tables for reporting to providing a service
oriented architecture where transactions can be posted in multiple
directions.
Post more details like number of transactions, size of warehouse, DW
architecture, ETL tools in use and perhaps the group can provider further
advice.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
> Right now we tend to provide extracts to our data warehousing system once
> a
> day. Users seem to be presseing for near real time warehousing. It seems
> to
> be a lot of processing to happen on the production side to get this data
> during regular hours and hence the nightly extracts..
> I am critical to know how we can provide such a real time solution and not
> affect the OLTP performance..
> Thanks
>|||What is SOA ? I keep hearing about it. Can you send me a good reading link
to that
"Ray" <someone@.nowhere.com> wrote in message
news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.com...
> There are ways they are usually products by third parties like Informatica
> or Ascential. However, this sounds far more like an ODS or reporting
> database than a warehouse. The solutions range in complexity and cost
from
> simple replication of require tables for reporting to providing a service
> oriented architecture where transactions can be posted in multiple
> directions.
> Post more details like number of transactions, size of warehouse, DW
> architecture, ETL tools in use and perhaps the group can provider further
> advice.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
once[vbcol=seagreen]
not[vbcol=seagreen]
>|||why don't you try having 2 AS servers pointing to the same Warehouse
(cleaned) databases for their source dimensions and facts. Then have the
application switch between them (via control table indicating which AS is
currently processing data and which one is not so the app would call a store
d
procedure to look in the control table to tell it which AS it should connect
to get data). This way you can process the cubes at appropriate time
(whether at a regular time interval or when data volume coming into warehous
e
tables met certain row count threshold) then switch over to the other server
to process it also when you are done with the first one.
The user experience is that they will see data is always available as close
to real time as possible...depending on your processing time. By the way,
AS2005 can 'sniff' your warehouse source tables for changes in data to updat
e
the cube automatically (you can do the same now in AS2000 but via ROLAP
storage model which looks at your fact tables so indexing is critical here).
"Hassan" wrote:

> What is SOA ? I keep hearing about it. Can you send me a good reading link
> to that
> "Ray" <someone@.nowhere.com> wrote in message
> news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.com...
> from
> once
> not
>
>

Real Time Backup Practises

Hi All,
I was questioned by my boss to come out with a solution that can provide 100% data protection.
The best I can provide right now is a 1 hour interval transaction log backup which is still not good enough.
My question is beside making the applications write to 2 different databases is there any other feasible ways of performing real time backup ?
Many thanks in advance for any feedback.
--
Bernard GohHi,
I recommend you to perform a transactional level backup every 10 minutes
time. THis will be the best approch to
have the most recent data backed up in a real time system with less cost.
If you have a seperate system with SQL Server licenses then you can do :-
1. Transactional replication with PUSH Subscription.
This will ensure that you have the latest data in your standby server
all the time. Publisher will Push the data to subscriber immediately
--
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh|||I'd suggest if your boss is after 100% data protection, and I would guess
100% uptime, you need to look at clustering. There is no real way to get
100% data protection through backups alone, you can get close, but
completely there. The only option really is to ensure that any hardware
failure does not cause any downtime or data loss, so fail over clustering is
probably the way forward.
Keith
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh|||Bernard,
This requirement is a balancing act between 100% data protection and
cost. When you say "data protection", do you mean availability? i.e.
could your data be unavailable for users for 1 day, but still
recoverable within a long time frame?
The best (and most expensive) way I can think of is to use a clustered
server with the data files residing on a SAN. You then replicate this
SAN off-site using the SAN replication technology synchronously to
another SAN. If you do not do this synchronously you will be exposing
yourself to data loss, albeit this will be small on an asynchronous
replication solution over small distances, even transatlantic, the data
loss should only be 30 secs ~ 1 minute, and with my testing I have
managed 30 secs transatlantic on HDS equipment.
Transactional replication is another solution worth exploring, but there
are many caveats in using transactional replication as a high
availability solution. Check out this article:
SQL Server 2000 High Availability Series: Implementing Transactional
Replication
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog03.mspx
Other solutions will likely not be 100% protected. Log shipping is way
short of this requirement, as is backup/restore. I'm looking forward to
Database Mirroring in Yukon, however this even will not provide 100%
guarantee against data loss.
If I had the budget, I would go with a clustered replicated SAN. You may
wish to test the performance of this as replicating synchronously has
performance implications.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
SummerCoke wrote:
> Hi All,
> I was questioned by my boss to come out with a solution that can provide 100% data protection.
> The best I can provide right now is a 1 hour interval transaction log backup which is still not good enough.
> My question is beside making the applications write to 2 different databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.

Real Time Backup Practises

Hi All,
I was questioned by my boss to come out with a solution that can provide 100% data protection.
The best I can provide right now is a 1 hour interval transaction log backup which is still not good enough.
My question is beside making the applications write to 2 different databases is there any other feasible ways of performing real time backup ?
Many thanks in advance for any feedback.
Bernard Goh
Hi,
I recommend you to perform a transactional level backup every 10 minutes
time. THis will be the best approch to
have the most recent data backed up in a real time system with less cost.
If you have a seperate system with SQL Server licenses then you can do :-
1. Transactional replication with PUSH Subscription.
This will ensure that you have the latest data in your standby server
all the time. Publisher will Push the data to subscriber immediately
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh
|||I'd suggest if your boss is after 100% data protection, and I would guess
100% uptime, you need to look at clustering. There is no real way to get
100% data protection through backups alone, you can get close, but
completely there. The only option really is to ensure that any hardware
failure does not cause any downtime or data loss, so fail over clustering is
probably the way forward.
Keith
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh
|||Bernard,
This requirement is a balancing act between 100% data protection and
cost. When you say "data protection", do you mean availability? i.e.
could your data be unavailable for users for 1 day, but still
recoverable within a long time frame?
The best (and most expensive) way I can think of is to use a clustered
server with the data files residing on a SAN. You then replicate this
SAN off-site using the SAN replication technology synchronously to
another SAN. If you do not do this synchronously you will be exposing
yourself to data loss, albeit this will be small on an asynchronous
replication solution over small distances, even transatlantic, the data
loss should only be 30 secs ~ 1 minute, and with my testing I have
managed 30 secs transatlantic on HDS equipment.
Transactional replication is another solution worth exploring, but there
are many caveats in using transactional replication as a high
availability solution. Check out this article:
SQL Server 2000 High Availability Series: Implementing Transactional
Replication
http://www.microsoft.com/technet/pro...y/hasog03.mspx
Other solutions will likely not be 100% protected. Log shipping is way
short of this requirement, as is backup/restore. I'm looking forward to
Database Mirroring in Yukon, however this even will not provide 100%
guarantee against data loss.
If I had the budget, I would go with a clustered replicated SAN. You may
wish to test the performance of this as replicating synchronously has
performance implications.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
SummerCoke wrote:
> Hi All,
> I was questioned by my boss to come out with a solution that can provide 100% data protection.
> The best I can provide right now is a 1 hour interval transaction log backup which is still not good enough.
> My question is beside making the applications write to 2 different databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.

Real Time Backup Practises

Hi All,
I was questioned by my boss to come out with a solution that can provide 100
% data protection.
The best I can provide right now is a 1 hour interval transaction log backup
which is still not good enough.
My question is beside making the applications write to 2 different databases
is there any other feasible ways of performing real time backup ?
Many thanks in advance for any feedback.
--
Bernard GohHi,
I recommend you to perform a transactional level backup every 10 minutes
time. THis will be the best approch to
have the most recent data backed up in a real time system with less cost.
If you have a seperate system with SQL Server licenses then you can do :-
1. Transactional replication with PUSH Subscription.
This will ensure that you have the latest data in your standby server
all the time. Publisher will Push the data to subscriber immediately
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh|||I'd suggest if your boss is after 100% data protection, and I would guess
100% uptime, you need to look at clustering. There is no real way to get
100% data protection through backups alone, you can get close, but
completely there. The only option really is to ensure that any hardware
failure does not cause any downtime or data loss, so fail over clustering is
probably the way forward.
Keith
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:783933BC-0A18-4C20-8E5D-243E99ECC989@.microsoft.com...
> Hi All,
> I was questioned by my boss to come out with a solution that can provide
100% data protection.
> The best I can provide right now is a 1 hour interval transaction log
backup which is still not good enough.
> My question is beside making the applications write to 2 different
databases is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.
> --
> Bernard Goh|||Bernard,
This requirement is a balancing act between 100% data protection and
cost. When you say "data protection", do you mean availability? i.e.
could your data be unavailable for users for 1 day, but still
recoverable within a long time frame?
The best (and most expensive) way I can think of is to use a clustered
server with the data files residing on a SAN. You then replicate this
SAN off-site using the SAN replication technology synchronously to
another SAN. If you do not do this synchronously you will be exposing
yourself to data loss, albeit this will be small on an asynchronous
replication solution over small distances, even transatlantic, the data
loss should only be 30 secs ~ 1 minute, and with my testing I have
managed 30 secs transatlantic on HDS equipment.
Transactional replication is another solution worth exploring, but there
are many caveats in using transactional replication as a high
availability solution. Check out this article:
SQL Server 2000 High Availability Series: Implementing Transactional
Replication
http://www.microsoft.com/technet/pr...oy/hasog03.mspx
Other solutions will likely not be 100% protected. Log shipping is way
short of this requirement, as is backup/restore. I'm looking forward to
Database Mirroring in Yukon, however this even will not provide 100%
guarantee against data loss.
If I had the budget, I would go with a clustered replicated SAN. You may
wish to test the performance of this as replicating synchronously has
performance implications.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
SummerCoke wrote:
> Hi All,
> I was questioned by my boss to come out with a solution that can provide 1
00% data protection.
> The best I can provide right now is a 1 hour interval transaction log back
up which is still not good enough.
> My question is beside making the applications write to 2 different databas
es is there any other feasible ways of performing real time backup ?
> Many thanks in advance for any feedback.