SEO

I am having some problems in the process of moving an Access back-end
to an SQL Server back-end. Everything works but there are severe
problems of response times when the number of users builds up.

At present we are using the Developer version before committing to the
Enterprise version. BOL (quoted below) seems to suggest that, apart
from licensing, they are the same.

Can anyone please confirm that this is so. If not, what is the
difference?

I may explain in another topic the problems I am facing but I first
wish to be clear on this particular question.

---------------------------------------------------------------------------------------------------------------
Quote from BOL

Microsoft® SQL Server™ 2000 is available in these editions:

SQL Server 2000 Enterprise Edition
Used as a production database server. Supports all features available
in SQL Server 2000, and scales to the performance levels required to
support the largest Web sites and enterprise online transaction
processing (OLTP) and data warehousing systems.

SQL Server 2000 Developer Edition
Used by programmers developing applications that use SQL Server 2000 as
their data store. Although the Developer Edition supports all the
features of the Enterprise Edition that allow developers to write and
test applications that can use the features, the Developer Edition is
licensed for use only as a development and test system, not a
production server.

On 11 Aug 2006 12:25:19 -0700, "Jim Devenish"
<internet.shopping@foobox.com> wrote:

>I am having some problems in the process of moving an Access back-end
>to an SQL Server back-end. Everything works but there are severe
>problems of response times when the number of users builds up.
>
>At present we are using the Developer version before committing to the
>Enterprise version. BOL (quoted below) seems to suggest that, apart
>from licensing, they are the same.
>
>Can anyone please confirm that this is so. If not, what is the
>difference?

I believe that's correct.

What do your RAM, HD, CPU, and network bandwidth look like?

You have to do some non-obvious tweaks to let SQL Server use more than
2 GB of RAM.

Jim Devenish (internet.shopping@foobox.com) writes:
> I am having some problems in the process of moving an Access back-end
> to an SQL Server back-end. Everything works but there are severe
> problems of response times when the number of users builds up.
>
> At present we are using the Developer version before committing to the
> Enterprise version. BOL (quoted below) seems to suggest that, apart
> from licensing, they are the same.
>
> Can anyone please confirm that this is so. If not, what is the
> difference?

The difference is exactly in the license. And when you mention users
building up, I get a little vervous that you are violating it...



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

We are trying to evaluate the use of SQL Server as a back-end and
although the system works with a very small number of users we are
encountering difficulties when the number rises to about 8-10. If we
cannot overcome these we will revert to Access back-end.

If we are successful, the licenses will be acquired for Enterprise
edition. There is no intention of violating licences. We do however
wish to test the system fully before advancing.

As I said in my original post, I am happy to explore the problem in
another post. I just wanted to eliminate the possibility that the use
of the Developer version was contributing to the problem.


Erland Sommarskog wrote:
> Jim Devenish (internet.shopping@foobox.com) writes:
> > I am having some problems in the process of moving an Access back-end
> > to an SQL Server back-end. Everything works but there are severe
> > problems of response times when the number of users builds up.
> >
> > At present we are using the Developer version before committing to the
> > Enterprise version. BOL (quoted below) seems to suggest that, apart
> > from licensing, they are the same.
> >
> > Can anyone please confirm that this is so. If not, what is the
> > difference?
>
> The difference is exactly in the license. And when you mention users
> building up, I get a little vervous that you are violating it...
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jim Devenish wrote:
> We are trying to evaluate the use of SQL Server as a back-end and
> although the system works with a very small number of users we are
> encountering difficulties when the number rises to about 8-10. If we
> cannot overcome these we will revert to Access back-end.
>
> If we are successful, the licenses will be acquired for Enterprise
> edition. There is no intention of violating licences. We do however
> wish to test the system fully before advancing.
>
> As I said in my original post, I am happy to explore the problem in
> another post. I just wanted to eliminate the possibility that the use
> of the Developer version was contributing to the problem.
>

SQL Server can easily handle thousands of simultaneous users. But it's
equally true that poor design and implementation can kill performance
even in a small database with a handful of users.

Did you review and redesign the logical and physical data model when
you ported to SQL Server? Did you rewrite your data access code to run
as server-side T-SQL stored procedures? If you did not do those things
or if you are still binding tables to Access forms then you are not
taking good advantage of SQL Server and N-tier architecture. Do not
expect SQL Server to be a supercharged version of Jet - it just isn't.

Assuming you have put in the effort to re-architect for SQL Server then
take a look at the following article for suggestions on troubleshooting
performance issues. These are primarily tuning and troubleshooting
suggestions. They are not a substitute for effective design up-front.

http://support.microsoft.com/?id=298475

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Jim Devenish (internet.shopping@foobox.com) writes:
> We are trying to evaluate the use of SQL Server as a back-end and
> although the system works with a very small number of users we are
> encountering difficulties when the number rises to about 8-10. If we
> cannot overcome these we will revert to Access back-end.

Certainly one woujld expect SQL Server be a lot better on handling many
users than Access. But with poor indexing etc, you can make things to
really slow on SQL Server as well. And if you just migrated the Access
database with the up-sizing wizard, you most likely have a non-optimal
database.

> If we are successful, the licenses will be acquired for Enterprise
> edition. There is no intention of violating licences. We do however
> wish to test the system fully before advancing.

When I think of it, moving from Access to Enterprise for a back-end is
quite a step, not the leasr financially. Are there any special feature
in Enterprise you are yearning for? Else, Standard or even Workgroup
Edition may work just as well.

For evaluation purposes, you should probably use Evaluation Edition, but
I'm not the license police. :-)

By the way, your post says SQL 200, I suppose that means SQL 2000. Why
not SQL 2005?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx