I posted this on the forums earlier:
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:
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,
asked Nov 19, 2009 at 04:56 PM in Default
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.
answered Nov 20, 2009 at 04:04 AM