we have created a temporary solution in our data warehousing dept. that is
causing much havoc. We have a main instance of SQL 2000 with which we do all
of our ETL work.

another department has already migrated to SQL 2005, and we need access to
one DB from that server to do our data pulls from.

we have created a SQL 2005 instance on the same server as the SQL 2000
instance. they are not getting along well. performance has suffered by a
factor of 4X.

is there a way to configure memory management (we have 4 gigs available) to
isolate the two instances from one another?

is SQL 2005 smart enough to not try to use the 2 gigs allocated already to
SQL 2000?

has anyone attempted a hack of this nature before. we were forced into this
position and cannot extricate ourselves, nor can we move to a single 2005
instance due to the complexity of existing DTS packages.

I would suggest you re-consider your scenario.

Instead of trying to 'PULL' data from the SQL 2005, why not have the SQL
2005 'PUSH' the data to your SQL 2000 server. Then you would not 'need' the
SQL 2005 instance on your ETL server (until you are ready to upgrade the ETL
functionality to SQL 2005, that is...)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"ggruber" <ggruber (AT) discussions (DOT) microsoft.com> wrote

unfortunately, that scenario is not possible. we have been committed to this
architecture by people who were not fully cognizant of the ramifications.

i have no control over this and we are to far along in the process to stop
and re-assess what we have done. it is truly a case of putting the cart
before the horse.

we need to find some viable solution.

thanks for your input.

"Arnie Rowland" wrote:

I don't understand why you needed a 2005 instance just to access another
2005 instance? But in any case you can set the MAX Memory of each instance
to limit how much memory each uses.

--
Andrew J. Kelly SQL MVP

"ggruber" <ggruber (AT) discussions (DOT) microsoft.com> wrote

it is a 2000 instance that accesses a 2005 instance. we need it because we
are ETL, not transaction processing. we are in the middle of migration pains,
and for the time being, half the company is on one side of the fence, half on
the other.

we must be able to provide a solution for all. we cannot migrate to 2005
fully, but we must be able to access data that resided there.

"Andrew J. Kelly" wrote:

Just FYI, you can still run 2000 DTS packages in SQL2005. But in any case I
think I see what you have now. I originally thought the other 2005 instance
was on another machine. As I stated before you can set a max memory setting
for SQL Server so that it does not use more than what you set for the memory
pool. But DTS and SSIS packages run outside SQL Servers memory space so you
have to account for them as well. I would still recommend putting the 2005
instance on another box or add more memory if it is causing you so much
pain. People have to understand that you can't simply double your load or
instances and expect everything to work as it did before with no
modifications in hardware.

--
Andrew J. Kelly SQL MVP

"ggruber" <ggruber (AT) discussions (DOT) microsoft.com> wrote