Log explorer tool?

Hi,
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 log
s
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 o
r
> 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 wit
h
> 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|||I recommend ApexSQL Log. It can do 'auditing' just as you describe, and it
also has a command line executable so you can create batch files to extract
and migrate records and schedule those batch files to run every 15 mins
using windows scheduler.
TheSQLGuru
President
Indicium Resources, Inc.
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:F18F4630-B9CB-4A74-BDE0-26FED9459F17@.microsoft.com...
> Hi,
> 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
>|||Thank you James and Aaron. I will approach these companies and check whether
they have a solution that will meet our need (in our budget).
Thank you.
Karthik
"Aaron Bertrand [SQL Server MVP]" wrote:

> I haven't looked at all of their offerings but maybe parallel computers te
ch
> 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...
>
>|||FWIW, Katmai (next release of SQL Server) has a planned feature called "Chan
ge Data Capture"
targeted at this situation. I haven't played with it yet, but I've skimmed t
he documentation. A
pre-release of Katmai is available for download.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:ONKJ98osHHA.4800@.TK2MSFTNGP05.phx
.gbl...
>I recommend ApexSQL Log. It can do 'auditing' just as you describe, and it
also has a command line
>executable so you can create batch files to extract and migrate records and
schedule those batch
>files to run every 15 mins using windows scheduler.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Karthik" <Karthik@.discussions.microsoft.com> wrote in message
> news:F18F4630-B9CB-4A74-BDE0-26FED9459F17@.microsoft.com...
>