Installing stand alone SQL Server Express 2008 on shared network drive

[Xancholy]

How can I get SQL Server Express 2008 with Tools to install as a stand-alone on a shared network drive ?

Thanks for any help.

[jmcilhinney]

What exactly do you mean by "a stand-alone"? SQL Server 2008 runs as a Windows service so you need to either install a separate instance on each machine or else install a single instance on a server and then have everyone connect to that. If you want to distribute the MDF data file with your app and attach on demand then you'd want a local instance.

[Xancholy]

Here's the scene. I need to install an instance on a shared network drive.

I then distribute my app frontend to networked users with connection string pointing to that database instance on the shared drive.

Hope that makes it clearer. I dont see the option of 'where' we want sqlserver to install to.

[jmcilhinney]

Shared drives are for data. You need to go to a machine on the network and install SQL Server Express on that machine. You then simply specify the name of that machine in your connection string instead of the usual dot for the local machine, i.e. "Data Source=Server\SQLEXPRESS" instead of "Data Source=.\SQLEXPRESS".

[Xancholy]

So the instance of SQLExpress is not considered data or stored on the shared drive ?

This seems very different from an Access mdb that would be stored on a shared drive and all front ends would be pointed to it using connection strings.

[jmcilhinney]

Absolutely it's different. Access databases are simply MDB data files. SQL Server - even the Express edition - is a fully-fledged RDBMS, so it is server-based. SQL Server databases themselves are just MDF files, but those data files are useless on their own because it's SQL Server, which runs as a Windows service, that provides access to those data files. Without the service the data is essentially inaccessible.

The benefit is that all the data access and manipulation is managed by SQL Server instead of the free-for-all you get with Access. It's this difference that makes Access unsuitable for scenarios that SQL Server can handle with ease.

[Xancholy]

If you want to distribute the MDF data file with your app and attach on demand then you'd want a local instance.

Thanks for the explanation. In my scenario, I'm deploying the common database as well as frontend that networked users tap into.

You mentioned that I could have a sqlserver instance on my pc as it is a Windows service.

How does the distributed mdf work in this case ?

[jmcilhinney]

How does the distributed mdf work in this case ?

It doesn't. If you have multiple users accessing a common database then you have multiple users connecting to a single instance of SQL Server.

If you have a local instance of SQL Server then you can also attach local databases on demand, but they would not be shared databases. They would only be local to an application on the same machine.

[techgnome]

All the connections would point to your machine. Also the MDF isn't distributed.... it's part of the SQL Server instance. You're still thinking in terms of files the way Access works... this is a totally different animal that requires a paradigm shift in thinking.

Basically any machine that everyone can see can have SQL installed. then all you need to do is point all the connection strings to that machine. Depending on the number of people connecting, I'd recommend using a machine that doesn't get used much, has a bit of HD space available, and it fairly speedy.

-tg

[GaryMazzone]

Also (at least in Express 2005) SQL Express is not set to allow outside connections by default (accepts on local machine connections). You must change that using the SQL Surface Area Configuration tool to allow access from remote computers.

[Xancholy]

Basically any machine that everyone can see can have SQL installed. then all you need to do is point all the connection strings to that machine. Depending on the number of people connecting, I'd recommend using a machine that doesn't get used much, has a bit of HD space available, and it fairly speedy.

-tg

Thanks people. Therein is the problem. This is going to be in a conservative corporate setup where machines don't 'see' each other but do have access to a common network drive.

In order to have successful implementation, what do you suggest ?

[si_the_geek]

They don't need to be able to access the computer via Explorer etc, they just need to be on the same network - as you connect to SQL Server directly.

You should install SQL Server on a computer which is always running (so a server of some sort), and preferably does not have much else running on it.

You will then use the computer name (or IP address) and the database name (not the file name, but the name inside SQL Server) in your connection string.

[GaryMazzone]

They do not see the machine... They see the database instance. You install on a server and do not share it. The connect string will be used to get to the database instance.

Slow typing SI bet me to it