Hi, we are changing our application from VB6 to VB.Net. This is far from a
trivial upgrade. We have over 400 forms and 40 dll's. We can't just take
down our production application and start the whole project from scratch. We
have to convert this stuff in stages. Our application design is pretty much
2-tier. We connection to SQL server when the application starts and then
disconnect when the application unloads. We even call ADO code from our dll
so transactions are scattered all over the code between dll's and the main
application. I guess the problem comes in when we are upgrade some code will
be ADO & some code will be ADO.Net. I came up with an idea which i think
would work which would be to hose both our ADO object ( a wrapper we wrote
for ADO) and ADO.Net wrapper in COM+. We could then use the DTC cordinator
from COM+ to handle the transactions between ADO.Net & plain ADO for us.

Does anyone see a better solution. The solution i came up with would require
two connections to the DB and if possible we would like a solution only
requiring one conneciton. We are also concerned about throughput since COM+
transactions are at a layer above ADO transactions and we don't want any
bottlenecks in our new code if possible. Does anyone have any better
solutions?

thanks,
Brent

Brent,

DTC transactions are not only heavy, they might also cause deadlocks due to
the high isolation level they work under. SQL2k5 is a bit better, but I
assume you are using Sql2k.

Converting from VB6 to VB.NET is trivial only if your existing application
architecture is in line with the recommendations - but if you have
transactions scatterred all over, and logic not seperated into class
libraries - you don't have much choice left. Though, even then I would
recommend upgrading to .NET.

Here is an approach I took with a client. I instead of rewriting and
replacing their existing system, created a new modular system written in
..NET which was eventually to replace the old system. The new system had bits
and peices of functionality that slowly weaned the users away from the old
code. Not sure if that applies in your situation - but just a thought :-).
BTW, the easiest thing to pull out, are reports.

And also I wouldn't recommend keeping an open connection to the sql server
for the life of the application. You should instead rely on connection
pooling.

Also, it is very hard to give specific hard hitting advice without being in
your shoes :-), but above are a few general pointers.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------

"Brent" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi, we are changing our application from VB6 to VB.Net. This is far from a
> trivial upgrade. We have over 400 forms and 40 dll's. We can't just take
> down our production application and start the whole project from scratch.
> We have to convert this stuff in stages. Our application design is pretty
> much 2-tier. We connection to SQL server when the application starts and
> then disconnect when the application unloads. We even call ADO code from
> our dll so transactions are scattered all over the code between dll's and
> the main application. I guess the problem comes in when we are upgrade
> some code will be ADO & some code will be ADO.Net. I came up with an idea
> which i think would work which would be to hose both our ADO object ( a
> wrapper we wrote for ADO) and ADO.Net wrapper in COM+. We could then use
> the DTC cordinator from COM+ to handle the transactions between ADO.Net &
> plain ADO for us.
>
> Does anyone see a better solution. The solution i came up with would
> require two connections to the DB and if possible we would like a solution
> only requiring one conneciton. We are also concerned about throughput
> since COM+ transactions are at a layer above ADO transactions and we don't
> want any bottlenecks in our new code if possible. Does anyone have any
> better solutions?
>
> thanks,
> Brent
>

I'm with Sahil on virtually all of this--except in a client/server
application there's little benefit to constantly closing and reopening the
connection--it thrashes the server unnecessarily and discards useful server
state. Some would say this would limit scalability. If this is an issue, the
application should go "dormant" and close the connection if the operator
goes away and quietly reopen the connection when they return. I've seen this
approach scale to thousands of users--this should be enough for most rigs.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


"Sahil Malik [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Brent,
>
> DTC transactions are not only heavy, they might also cause deadlocks due
> to the high isolation level they work under. SQL2k5 is a bit better, but I
> assume you are using Sql2k.
>
> Converting from VB6 to VB.NET is trivial only if your existing application
> architecture is in line with the recommendations - but if you have
> transactions scatterred all over, and logic not seperated into class
> libraries - you don't have much choice left. Though, even then I would
> recommend upgrading to .NET.
>
> Here is an approach I took with a client. I instead of rewriting and
> replacing their existing system, created a new modular system written in
> .NET which was eventually to replace the old system. The new system had
> bits and peices of functionality that slowly weaned the users away from
> the old code. Not sure if that applies in your situation - but just a
> thought :-). BTW, the easiest thing to pull out, are reports.
>
> And also I wouldn't recommend keeping an open connection to the sql server
> for the life of the application. You should instead rely on connection
> pooling.
>
> Also, it is very hard to give specific hard hitting advice without being
> in your shoes :-), but above are a few general pointers.
>
> - Sahil Malik [MVP]
> Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
> ----------------------------------------------------------------------------
>
> "Brent" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi, we are changing our application from VB6 to VB.Net. This is far from
>> a trivial upgrade. We have over 400 forms and 40 dll's. We can't just
>> take down our production application and start the whole project from
>> scratch. We have to convert this stuff in stages. Our application design
>> is pretty much 2-tier. We connection to SQL server when the application
>> starts and then disconnect when the application unloads. We even call ADO
>> code from our dll so transactions are scattered all over the code between
>> dll's and the main application. I guess the problem comes in when we are
>> upgrade some code will be ADO & some code will be ADO.Net. I came up with
>> an idea which i think would work which would be to hose both our ADO
>> object ( a wrapper we wrote for ADO) and ADO.Net wrapper in COM+. We
>> could then use the DTC cordinator from COM+ to handle the transactions
>> between ADO.Net & plain ADO for us.
>>
>> Does anyone see a better solution. The solution i came up with would
>> require two connections to the DB and if possible we would like a
>> solution only requiring one conneciton. We are also concerned about
>> throughput since COM+ transactions are at a layer above ADO transactions
>> and we don't want any bottlenecks in our new code if possible. Does
>> anyone have any better solutions?
>>
>> thanks,
>> Brent
>>
>
>

There is one thing that might help here, if you're using SQL Server. This is
a feature that we don't advertise a lot and we've been removing the focus
from it over time; so read on if you need this as an intermediate step while
you migrate your app, *not* as a long-term solution:

SQL Server supports something called "bound sessions", where two connections
(to the same server) can be bound to the same transaction, without involving
a distributed transaction coordinator. Bound sessions require careful
coordination, as while you use one (e.g. have a pending data-reader) you
cannot use the other. If those limitations are ok with you, then you can
find out how to use bound sessions in SQL Server Books Online (it's fairly
easy), start by looking at the sections for "sp_getbindtoken" and
"sp_bindsession"

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


"Brent" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi, we are changing our application from VB6 to VB.Net. This is far from a
> trivial upgrade. We have over 400 forms and 40 dll's. We can't just take
> down our production application and start the whole project from scratch.
> We have to convert this stuff in stages. Our application design is pretty
> much 2-tier. We connection to SQL server when the application starts and
> then disconnect when the application unloads. We even call ADO code from
> our dll so transactions are scattered all over the code between dll's and
> the main application. I guess the problem comes in when we are upgrade
> some code will be ADO & some code will be ADO.Net. I came up with an idea
> which i think would work which would be to hose both our ADO object ( a
> wrapper we wrote for ADO) and ADO.Net wrapper in COM+. We could then use
> the DTC cordinator from COM+ to handle the transactions between ADO.Net &
> plain ADO for us.
>
> Does anyone see a better solution. The solution i came up with would
> require two connections to the DB and if possible we would like a solution
> only requiring one conneciton. We are also concerned about throughput
> since COM+ transactions are at a layer above ADO transactions and we don't
> want any bottlenecks in our new code if possible. Does anyone have any
> better solutions?
>
> thanks,
> Brent
>

Wow thats hitech. But I can see why that is being discouraged.

One thing you mentioned is, if you have a pending datareader, the other
cannot be used. Can this restriction be gotten around using MARS? (But isn't
MARS session pool limited to one connection?).

Sounds like a hairy solution but a solution nonetheless.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------



"Pablo Castro [MS]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> There is one thing that might help here, if you're using SQL Server. This
is
> a feature that we don't advertise a lot and we've been removing the focus
> from it over time; so read on if you need this as an intermediate step
while
> you migrate your app, *not* as a long-term solution:
>
> SQL Server supports something called "bound sessions", where two
connections
> (to the same server) can be bound to the same transaction, without
involving
> a distributed transaction coordinator. Bound sessions require careful
> coordination, as while you use one (e.g. have a pending data-reader) you
> cannot use the other. If those limitations are ok with you, then you can
> find out how to use bound sessions in SQL Server Books Online (it's fairly
> easy), start by looking at the sections for "sp_getbindtoken" and
> "sp_bindsession"
>
> --
> Pablo Castro
> Program Manager - ADO.NET Team
> Microsoft Corp.
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>
> "Brent" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi, we are changing our application from VB6 to VB.Net. This is far from
a
> > trivial upgrade. We have over 400 forms and 40 dll's. We can't just take
> > down our production application and start the whole project from
scratch.
> > We have to convert this stuff in stages. Our application design is
pretty
> > much 2-tier. We connection to SQL server when the application starts and
> > then disconnect when the application unloads. We even call ADO code from
> > our dll so transactions are scattered all over the code between dll's
and
> > the main application. I guess the problem comes in when we are upgrade
> > some code will be ADO & some code will be ADO.Net. I came up with an
idea
> > which i think would work which would be to hose both our ADO object ( a
> > wrapper we wrote for ADO) and ADO.Net wrapper in COM+. We could then use
> > the DTC cordinator from COM+ to handle the transactions between ADO.Net
&
> > plain ADO for us.
> >
> > Does anyone see a better solution. The solution i came up with would
> > require two connections to the DB and if possible we would like a
solution
> > only requiring one conneciton. We are also concerned about throughput
> > since COM+ transactions are at a layer above ADO transactions and we
don't
> > want any bottlenecks in our new code if possible. Does anyone have any
> > better solutions?
> >
> > thanks,
> > Brent
> >
>
>

The issue of one result-set (reader) at a time has to do with transaction
ownership, not with being able to multiplex the session, so no, MARS won't
help in this particular case. That's why this is to be used only in
exception cases, it has many limitations.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


"Sahil Malik [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Wow thats hitech. But I can see why that is being discouraged.
>
> One thing you mentioned is, if you have a pending datareader, the other
> cannot be used. Can this restriction be gotten around using MARS? (But
> isn't
> MARS session pool limited to one connection?).
>
> Sounds like a hairy solution but a solution nonetheless.
>
> - Sahil Malik [MVP]
> Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
> ----------------------------------------------------------------------------
> ---------------
>
>
>
> "Pablo Castro [MS]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> There is one thing that might help here, if you're using SQL Server. This
> is
>> a feature that we don't advertise a lot and we've been removing the focus
>> from it over time; so read on if you need this as an intermediate step
> while
>> you migrate your app, *not* as a long-term solution:
>>
>> SQL Server supports something called "bound sessions", where two
> connections
>> (to the same server) can be bound to the same transaction, without
> involving
>> a distributed transaction coordinator. Bound sessions require careful
>> coordination, as while you use one (e.g. have a pending data-reader) you
>> cannot use the other. If those limitations are ok with you, then you can
>> find out how to use bound sessions in SQL Server Books Online (it's
>> fairly
>> easy), start by looking at the sections for "sp_getbindtoken" and
>> "sp_bindsession"
>>
>> --
>> Pablo Castro
>> Program Manager - ADO.NET Team
>> Microsoft Corp.
>>
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>>
>> "Brent" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Hi, we are changing our application from VB6 to VB.Net. This is far
>> > from
> a
>> > trivial upgrade. We have over 400 forms and 40 dll's. We can't just
>> > take
>> > down our production application and start the whole project from
> scratch.
>> > We have to convert this stuff in stages. Our application design is
> pretty
>> > much 2-tier. We connection to SQL server when the application starts
>> > and
>> > then disconnect when the application unloads. We even call ADO code
>> > from
>> > our dll so transactions are scattered all over the code between dll's
> and
>> > the main application. I guess the problem comes in when we are upgrade
>> > some code will be ADO & some code will be ADO.Net. I came up with an
> idea
>> > which i think would work which would be to hose both our ADO object ( a
>> > wrapper we wrote for ADO) and ADO.Net wrapper in COM+. We could then
>> > use
>> > the DTC cordinator from COM+ to handle the transactions between ADO.Net
> &
>> > plain ADO for us.
>> >
>> > Does anyone see a better solution. The solution i came up with would
>> > require two connections to the DB and if possible we would like a
> solution
>> > only requiring one conneciton. We are also concerned about throughput
>> > since COM+ transactions are at a layer above ADO transactions and we
> don't
>> > want any bottlenecks in our new code if possible. Does anyone have any
>> > better solutions?
>> >
>> > thanks,
>> > Brent
>> >
>>
>>
>
>