SIMS .net MSDE connection limit

[localzuk]

I recently ran a series of commands on the Sims.net server to see if we had often reached the MSDE connection limit. I can not remember or find those commands now.

 

Can anyone point me in the direction of how to check if MSDE is limiting connections or not?

 

Thanks

[Toby.Belfield]

This is interesting, because MSDE does not have a connection limit. Although, some non-server operating systems, such as XP, have a connection limit of 10.

 

Could you be referring to a "performance penalty?" With MSDE there is a performance penalty when more than 8 simultaneous connections are "busy!"

 

You can view the application event log for SQL Server 3629 messages or use the DBCC CONCURRENCYVIOLATION statement to assess how often the workload governor is activated. If the governor is frequently activated in a well-designed and well-tuned system, and the system is generating a lot of logical reads and writes, you should consider upgrading to SQL Server 2000 Standard Edition.

 

Incidentally, we will be looking at a move to SQL 2005 (inc Express) which have no connection penalties. No timescales for this move, as yet.

 

Hope this is of some help.

 

Regards,

 

Toby Belfield

CAPITA

[localzuk]

Yep that is what I mean. The penalty works as a limit. So at any one time the server can only service 8 connections, queuing the others until later, when one of the 8 'slots' opens up.

 

What about moving over to one of the other free database engines such as Postgresql or Mysql? I can understand wanting to stick to tools that .NET works with natively though.

[PhilNeal]

The real limit with MSDE is actually the DB size which I think in 2G. We've tried >150 concurrent users without seeing any performance hit.

 

With something as valuable as the data in the MIS we need to throughly test and introducing another variable such as the server itself would multiply costs hugely. MS costs to schools for SQL Server are very low.