We have most of our OLTP systems on SQL Server 2000 & 2005. But we (top
management) plan to have our Data Warehouse hosted on a different database
like Greenplum/Netezza/Terradata.
One of our aims is to propogate changes in the OLTP db's to the warehouse
database once in 15 minutes. Our idea is to use a tool that can read SQL logs
and determine the changes and thus propogate it to the data warehouse
database.
I am aware that ETL tools can do this, but we would prefer to avoid one.
Also, ETL tools seem to have their own overhead especially while running it
so frequent.
Can someone suggest a few tools? Also, how feasible do you feel is this idea?
Thank you.
Regards,
Karthik
> One of our aims is to propogate changes in the OLTP db's to the warehouse
> database once in 15 minutes. Our idea is to use a tool that can read SQL
> logs
> and determine the changes and thus propogate it to the data warehouse
> database.
> I am aware that ETL tools can do this, but we would prefer to avoid one.
> Also, ETL tools seem to have their own overhead especially while running
> it
> so frequent.
> Can someone suggest a few tools? Also, how feasible do you feel is this
> idea?
It sounds like a very expensive and less reliable mode of transactional
replication.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||Yes. But I cant implement Transactional Replication as some of the tables in
our database does not have primary keys.
I am looking at tools like Data Mirror, Attunity etc...But I wanted to
understand whether you have actually heard of a setup / product like this.
Money doesn't seem to be a problem with the management. Given a choice I
would have gone with a nice huge OLAP database in SQL Server 2005.
Karthik
"Aaron Bertrand [SQL Server MVP]" wrote:
> It sounds like a very expensive and less reliable mode of transactional
> replication.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
|||Well, how up to date does the warehouse have to be? If it can be hourly or
so you could use log shipping. If it can be refreshed daily or every 12
hours you could go with a backup / restore methodology. That would
certainly be less taxing on your production system than continuous log
retrieval / application.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:1F6C97BD-D6CD-41AB-B65E-BAFA9E8B66A6@.microsoft.com...[vbcol=seagreen]
> Yes. But I cant implement Transactional Replication as some of the tables
> in
> our database does not have primary keys.
> I am looking at tools like Data Mirror, Attunity etc...But I wanted to
> understand whether you have actually heard of a setup / product like this.
> Money doesn't seem to be a problem with the management. Given a choice I
> would have gone with a nice huge OLAP database in SQL Server 2005.
> Karthik
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Hi Aaron,
But my taget database will not be in SQL Server. As I said earlier, it will
be a database like Greenplum/Netezza/Terradata.
So log shipping, backup/recovery etc will not work for me.
That is the reason why I am looking at a tool that will read changes from
the SQL log and propogate to the destination database.
Thank you.
Regards,
Karthik
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, how up to date does the warehouse have to be? If it can be hourly or
> so you could use log shipping. If it can be refreshed daily or every 12
> hours you could go with a backup / restore methodology. That would
> certainly be less taxing on your production system than continuous log
> retrieval / application.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Karthik" <Karthik@.discussions.microsoft.com> wrote in message
> news:1F6C97BD-D6CD-41AB-B65E-BAFA9E8B66A6@.microsoft.com...
>
>
|||> But my taget database will not be in SQL Server. As I said earlier, it
> will
> be a database like Greenplum/Netezza/Terradata.
Sorry, those sound like products, not platforms, and I am not familiar with
them.
> That is the reason why I am looking at a tool that will read changes from
> the SQL log and propogate to the destination database.
But if it's a different kind of database entirely, I am not sure that is
possible. It is more likely you will need to use some kind of ETL process
that looks at the data, not the log.
A
|||On Jun 18, 4:28 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Sorry, those sound like products, not platforms, and I am not familiar with
> them.
>
> But if it's a different kind of database entirely, I am not sure that is
> possible. It is more likely you will need to use some kind of ETL process
> that looks at the data, not the log.
> A
Both Attunity and DataMirror can read the SQL Server log to identify
which records changed in near real time. The main difference between
the tools is how they transform and apply the changes to the target
system.
DataMirror has its own engine to perform "mini" transformation and
apply the result to the target system (e.g. Netezza). Attunity will
push the change records to an ETL tool or the Enterprise Service Bus
(ESB) to perform the transformation and update the target system. I
believe Attunity can plug into SSIS, Microsoft ETL engine.
--James
|||Thanks for the info James. Are you aware of tools other that DataMirror and
Attunity? Attunity doesnt seem to support SQL 2005 yet. Data Mirror is very
expensive
.Thank you once again,
Karthik
"jamesharring@.gmail.com" wrote:
> On Jun 18, 4:28 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> Both Attunity and DataMirror can read the SQL Server log to identify
> which records changed in near real time. The main difference between
> the tools is how they transform and apply the changes to the target
> system.
> DataMirror has its own engine to perform "mini" transformation and
> apply the result to the target system (e.g. Netezza). Attunity will
> push the change records to an ETL tool or the Enterprise Service Bus
> (ESB) to perform the transformation and update the target system. I
> believe Attunity can plug into SSIS, Microsoft ETL engine.
> --James
>
|||On Jun 19, 11:18 am, Karthik <Kart...@.discussions.microsoft.com>
wrote:
> Thanks for the info James. Are you aware of tools other that DataMirror and
> Attunity? Attunity doesnt seem to support SQL 2005 yet. Data Mirror is very
> expensive
.> Thank you once again,
> Karthik
>
>
>From what I heard the Attunity CDC for SQL Server 2005 is now in Beta.
As for alternatives... If you already own an ETL tool or ESB you may
want to check with your vendor and see if they have a solution for
that. Many of these vendors provide their own change data capture
adapter for SQL Server as an add-on product. Another option might be
GoldenGate but I don't assume they'll come cheap either...
--James
|||I haven't looked at all of their offerings but maybe parallel computers tech
has something you can leverage.
http://www.pcticorp.com/
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:93D0F78A-2213-4B63-8548-4E0B63914E56@.microsoft.com...[vbcol=seagreen]
> Thanks for the info James. Are you aware of tools other that DataMirror
> and
> Attunity? Attunity doesnt seem to support SQL 2005 yet. Data Mirror is
> very
> expensive
.> Thank you once again,
> Karthik
>
> "jamesharring@.gmail.com" wrote:
No comments:
Post a Comment