Hi,

Help me.  We have a new application using SQL Server 2005  database.
The requirement is we are planning a parallel  run of this new
application with the older one using SQLServer 2000 databse.  The
challenge is

1) When a data change occurs in the new databse, the older database
also should be updated
2) It should happen the other way also\
3) We need to apply some conditions before  updation

My doubt is about the technolog we should use -

1) SQL Server replication
2) Biztalk

Thanks

Nambu

[ Post a follow-up to this message ]

On 24 Jul 2006 23:09:14 -0700, "Nambu" <binuam@gmail.com> wrote:

>1) When a data change occurs in the new databse, the older database
>also should be updated
>2) It should happen the other way also\
>3) We need to apply some conditions before  updation
If you don't use BizTalk for anything else, I woudln't use it for
this, either.

If you allready have a BizTalk installed, you can go with this option,
but it will be a polling behaviour, since the SQL Adapter is polling.
I would go with triggers and manually do it.

[ Post a follow-up to this message ]

Replication is the more appropriate option here.  You don't really have any
process or need to have an orchestration, and you don't need the adapter
framework as you are just connecting two sql databases.

Matt


"Nambu" <binuam@gmail.com> wrote in message
news:1153807754.282062.124790@m73g2000cwd.googlegroups.com...
> Hi,
>
> Help me.  We have a new application using SQL Server 2005  database.
> The requirement is we are planning a parallel  run of this new
> application with the older one using SQLServer 2000 databse.  The
> challenge is
>
> 1) When a data change occurs in the new databse, the older database
> also should be updated
> 2) It should happen the other way also\
> 3) We need to apply some conditions before  updation
>
> My doubt is about the technolog we should use -
>
> 1) SQL Server replication
> 2) Biztalk
>
> Thanks
>
> Nambu
>

[ Post a follow-up to this message ]

Hi,

Thanks for the reply.

Again on Replication I have some concerns -

1)  The two databases are entirely different - can we map the fields ?

2)  Is two-way replication possible ? Apart from the database D1
changes replicating to D2 database - the D2 changes should reflect in
D1also ?

Thanks

Nambu

Matt Milner wrote:
[vbcol=seagreen]
> Replication is the more appropriate option here.  You don't really have an
y
> process or need to have an orchestration, and you don't need the adapter
> framework as you are just connecting two sql databases.
>
> Matt
>
>
> "Nambu" <binuam@gmail.com> wrote in message
> news:1153807754.282062.124790@m73g2000cwd.googlegroups.com... 

[ Post a follow-up to this message ]

Hi Jan,

Thanks a million for your response.

My Company wants me to utilize the BIZTALK it has purchased now.  Can
you give me some information on how to "replicate" using BIZTALK.

Can the orchestrisation be used to handle the field mismatch between
the database ?
I mean if the database D2 has some fields missing when compared to
D1,can we use this to put some defaults.?

Thanks

Nambu

Jan Eliasen wrote:

> On 24 Jul 2006 23:09:14 -0700, "Nambu" <binuam@gmail.com> wrote:
> 
> If you don't use BizTalk for anything else, I woudln't use it for
> this, either.
>
> If you allready have a BizTalk installed, you can go with this option,
> but it will be a polling behaviour, since the SQL Adapter is polling.
> I would go with triggers and manually do it.

[ Post a follow-up to this message ]

On 28 Jul 2006 09:57:33 -0700, "Nambu" <binuam@gmail.com> wrote:

>My Company wants me to utilize the BIZTALK it has purchased now.  Can
>you give me some information on how to "replicate" using BIZTALK.
Well... You will need the SQL Adapter, of course.

It will depend on whether you only need to replicate inserted lines or
also updated lines?

If you only need to replicate inserted lines, then:

Set up a stored procedure that will read the lines that must be
replicated. The SP wil have to mark the lines read, so it doesn't
return them the next time it is called.

Then, setup a SQL Server receive location to receive this input.

Create an updategram for the database the lines go into and use a map
in BizTalk to transform between the two schemas. Use the SQL Adapter
to send the data to the database you are replicating to.

If you need two-way-replication, then do the same - only the other way
around.

You will need to read up on the SQL Adapter - you will need to
understand how it works, how to create the needed schemas and how the
updategrams work.


Now, if you need to replicate also modified lines, then things get a
bit messy... do you need that?
eliasen, representing himself and not the company he works for.

private email: jan@eliasen.dk

[ Post a follow-up to this message ]

Yes, I need to replicate the modified records also. Also it should
handle deletion. What is the best BIZTALK solution (even it is not
appropriate in this context)?

Could you please help..

I visualize the result like this -- One project pumping the changed
data from Source databse to a folder and another reading those and
updating the Target Database.

Did I take something wrong.

Thanks

Nambu

[ Post a follow-up to this message ]

On 29 Jul 2006 04:06:36 -0700, "Nambu" <binuam@gmail.com> wrote:

>Yes, I need to replicate the modified records also. Also it should
>handle deletion. What is the best BIZTALK solution (even it is not
>appropriate in this context)?
You are in for a messe solution, just so you know it! :-)

Deletion is tricky, as the SQL Adapter only retrieves lines that are
the result of either a query or a SP.

I would go with this approach:

1. Create a trigger on the table that must be replicated.
2. Create a table that has the same schema as the "real" table, but
which also has a field that lets you know if the line was inserted,
modified or deleted.
3. Let the trigger copy contents of inserted lines into the new table
and set the new field to "Insert" or something like that.
4. Let the trigger copy contents of modified lines into the new table
and set the new field to "Modified"
5. Do the same for deleted items.
6. Create a SP that retieves all lines from this new table and also
deletes them.
7. In your map, when creating updategrams, use the value of the new
field to determine how to create the updategram.

8. GOOD LUCK! :-)
eliasen, representing himself and not the company he works for.

private email: jan@eliasen.dk

[ Post a follow-up to this message ]

You can do two way replication.

I don't know that mapping is supported in replication, but it would be in
SSIS (2005) or DTS (2000).  So you might have to do a scheduled job in this
layer to do your data movements.

Matt


"Nambu" <binuam@gmail.com> wrote in message
news:1154105568.351880.164720@i42g2000cwa.googlegroups.com...
> Hi,
>
> Thanks for the reply.
>
> Again on Replication I have some concerns -
>
> 1)  The two databases are entirely different - can we map the fields ?
>
> 2)  Is two-way replication possible ? Apart from the database D1
> changes replicating to D2 database - the D2 changes should reflect in
> D1also ?
>
> Thanks
>
> Nambu
>
> Matt Milner wrote:
> 
>

[ Post a follow-up to this message ]