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> 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:
> > 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
>
>|||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...
> 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:
>> > 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
>>|||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...
> > 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:
> >
> >> > 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
> >>
> >>
> >>
>
>|||> 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:
> > 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
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:
> > > 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
> 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...
> 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:
>> > > 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
>> 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 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...
> > 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:
> >> > > 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
> >>
> >> 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
> >>
> >>
>
>|||FWIW, Katmai (next release of SQL Server) has a planned feature called "Change Data Capture"
targeted at this situation. I haven't played with it yet, but I've skimmed the 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...
>> 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
>|||I too am anxious to see how this functionality is implemented.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:D44A5C96-36F9-48D6-9890-431FAC763B20@.microsoft.com...
> FWIW, Katmai (next release of SQL Server) has a planned feature called
> "Change Data Capture" targeted at this situation. I haven't played with it
> yet, but I've skimmed the 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...
>> 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
>>
>
.