x

SQL Server 2005 Express used for multi-user

I posted this on the forums earlier:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136072

but maybe I should have posted it here.


This is my current scenario: I have a program that currently runs as a single-user application connecting to a 2005 Express database (both on the same machine).

This is what I am tasked to create: I have 3 physically separate locations that need to connect to a SQL Server 2005 Express database (Windows Server name: Server2003, database name: myDB) in real-time. Basically just your ordinary every-day multi-user program.

While reading the book "Programming SQL Server 2005", I came across this section in the "SQL Server Express" section: "Databases running on a user instance are opened in single-user mode only, and multiple users cannot connect to databases running on a user instance."

This seems to contradict the testing I have already completed: I created a test program that allows record creation in my test database. I ran 2 instances of this program from the same computer, and was able to create records in the test database. I could then refresh the DataGridView of each program to show the newly created records of both program instances.

Is the book wrong or am I not reading something correctly?

During installation of 2005 Express, I selected the "Named instance" of "SQLExpres", which is the default name provided.

My other questions are:

  1. Will my program be able to allow 15 users to connect to 2005 Express, all in real-time, accessing the same database, creating/updating records in that database?

  2. Given the machine specs below, what would be a practical maximum user limitation? Users will mostly be viewing data, with the occasional record insert/update.

  3. Does "Named instance" = "user instance"?

  4. Assuming the database server is a Core2 Duo Dual-Processor with 4GB of memory and knowing the limitations of Express (1 proc, 1GB ram, 4GB database), are there any items I'm overlooking as I push forward with this project?

I am assuming that I will need to enable TCP to allow the remote clients to connect over TCP/IP, using a connection similar to this: Data Source=10.94.40.33\SQLExpress,13579;Network Library=DBMSSOCN;Initial Catalog=myDB;User ID=sa;Password=MySecurePassword_81

Thanks in advance for any and all comments,
clu

more ▼

asked Nov 19 '09 at 04:56 PM in Default

clu gravatar image

clu
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
  1. Yes. SQL Express has no limitation on concurrent connections (your server hardware, network configuration, etc. would of course affect this).
  2. A max user limitation would depend on many things. Application architecture, network bandwidth, raid configuration, etc.
  3. No. A user instance is not normally used in a production application. A named instance is what you get by default when installing SQL Express. As you have mentioned, "SQLExpress" is the instance name. SQL Server (non express) installs as a default instance by default.
  4. In addition to those limitations, Express also does not have an agent, database mail support, or database mirroring. Look into the limitations here. If 15 concurrent users is your baseline then I would say Express is a perfect fit. You can always scale to a full blown MSSQL installation at a later date if needed. That migration would be a snap.

I noticed in your connection string example you referenced the dynamic port of 13579. I would recommend assigning a static port and disabling the dynamic port assignment in the SQL Server network config. You can also read up on the SQL Browser service to learn more about how express and other named instances handles request routing. Your remote connection questions are handled here.

more ▼

answered Nov 20 '09 at 04:04 AM

Nathan Skerl gravatar image

Nathan Skerl
432 1 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936

asked: Nov 19 '09 at 04:56 PM

Seen: 1240 times

Last Updated: Nov 19 '09 at 04:56 PM