Connect To .mdf file on Network?

[PvdG42]

Attempting to create a DataSource (SQL Server database file) for a C#
Windows Forms app where the project/solution is on one machine and the .mdf
is on a second machine accessible through a simple "Microsoft Network". The
drive and its content are accessible from the project machine, but the "Test
Connection" button in the DataSource Wizard gives this error message:

The file \\<machine name>\e\SQL_DatabaseFiles\Cottages.mdf is on a network
path that is not supported for database files.
An attempt to attach an auto-named database for file \\<machine
name>\e\SQL_DatabaseFiles\Cottages.mdf failed. A database with the same name
exists, or specified file cannot be opened, or it is located on UNC share.

Visual Studio 2008 and SQL Server Express 2005.
Is there a way to configure VS to allow this, or is there a workaround?

 

[Gregory A. Beamer]

Is the MDF attached to an instance of SQL Server on the other box? If so,
there is no way you can attach it to your local instance. In that case, you
can create a linked server, although I am not sure Express allows linked
servers. If not, you need a full version of SQL Server.

If it is not attached, you might try creating a "local" drive using a UNC
path and attaching. I have a feeling there is someting built in the SQL
engine to prevent this, however, as SQL intimately manages the MDF and LDF
files and your suggestion is far from optimal for intimate management.

--
Gregory A. Beamer
MVP: MCP: +I, SE, SD, DBA

Blog:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think Outside the Box! |
********************************************

 

[Paul Clement]

On Thu, 13 Nov 2008 19:22:35 +0100, "Patrice" <http://www.chez.com/scribe/> wrote:

¤ Try :
¤ http://support.microsoft.com/kb/304261/en-us (in particular it seems you
¤ have to position a trace flagf to by pass this check).
¤
¤ Of course this is quite an unexpected usage for SQL Server. Do this only if
¤ you have a compelling reason for not using a client server architecture...

Yes, and the potential for database corruption is pretty compelling reason not to. ;-)


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[PvdG42]

Is the MDF attached to an instance of SQL Server on the other box? If so,
there is no way you can attach it to your local instance. In that case,
you can create a linked server, although I am not sure Express allows
linked servers. If not, you need a full version of SQL Server.

If it is not attached, you might try creating a "local" drive using a UNC
path and attaching. I have a feeling there is someting built in the SQL
engine to prevent this, however, as SQL intimately manages the MDF and LDF
files and your suggestion is far from optimal for intimate management.

Thanks very much for the suggestion.
The MDF is not attached to an instance of SQL Server, and unfortunately,
cannot be (according to the student who brought me this rather bizarre
scenario from his DOD workplace). The deal appears to be that he is supposed
to develop an app that will be used by ~ 20 - 30 people concurrently and
needs a single shared database instance, but that no real server (i.e. a SQL
Server instance available through a network) can be provided. He did a
prototype using an Access database file, but thinks that will not be good
enough for the amount of data involved or the potential number of concurrent
users.

 

[PvdG42]

Try :
http://support.microsoft.com/kb/304261/en-us (in particular it seems you
have to position a trace flagf to by pass this check).

Of course this is quite an unexpected usage for SQL Server. Do this only
if you have a compelling reason for not using a client server
architecture...

Patrice and Paul,

Thanks very much for your suggestions and comments. I agree completely that
it's all wrong. However, to reveal the origin of the scenario:

The MDF is not attached to an instance of SQL Server, and unfortunately,
cannot be (according to the student who brought me this rather bizarre
scenario from his DOD workplace). The deal appears to be that he is supposed
to develop an app that will be used by ~ 20 - 30 people concurrently and
needs a single shared database instance, but that no real server (i.e. a SQL
Server instance available through a network) "can be provided". He did a
prototype using an Access database file, but thinks that will not be good
enough for the amount of data involved or the potential number of concurrent
users.

I'll pass your suggestion and concerns along to him.

 

[PvdG42]

So instead of using an Access file you'll need :
- to have SQL Server Express installed on 20-30 machines
- each of those instance would point to the same mdf file
- assuming this is possible which is afaik very unlikely you'll use the
file throught a network share exactly the same way Access would. SQL
Server can be magically better but because only "statements and data" are
sent over the wire and the server process that listen server side is the
only one allowed to mess with the file rather than to use a file using a
network share. So you annihilate the architecture benefit brought by SQL
Server.

So I would either :
- stick to Access
- or use SQL Server as it should

Here it looks like an overcomplicated "solution" that will bring all the
problems of both solutions (and I don't think this is even possible
anyway).

More excellent advice!
Thanks again, Patrice. I'll pass it along.

 

[Gregory A. Beamer]

It seems a better option would be to set up a SQL instance with a WCF
service for access. Then shut off port 1433 access to the server,
essentially forcing everyone through the web service. Then write the clients
to hit the service. Solves the "we cannot install a server due to security"
and still alllows the server to be used as it should.

Another option is educate how SQL Server can be secured.

The third is to find a new carpenter's job, as this company is asking him to
build a house, but with no building materials.

I agree with the Access issue. It is easy to outgrow. There are some other
file based databases, but you will generally find you outgrow them rather
quickly or they need some type of server on top of them.

--
Gregory A. Beamer
MVP: MCP: +I, SE, SD, DBA

Blog:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think Outside the Box! |
********************************************

 

[Gregory A. Beamer]

BTW, you can also set up SQL Express (I know the employer states NO SERVER,
but ...) and then have it set up so the security only allows application
access (beyond SA, which you really cannot get rid of). You then set up the
application clients on the boxes and still have the server.

You cannot attach multiple SQL Express instances to the same MDF. You can
with SQL Server Enterprise, as a cluster, but that would be an expensive
option to use a networked MDF file. It would also not be the proper way to
cluster.

--
Gregory A. Beamer
MVP: MCP: +I, SE, SD, DBA

Blog:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think Outside the Box! |
********************************************

 

[PvdG42]

BTW, you can also set up SQL Express (I know the employer states NO
SERVER, but ...) and then have it set up so the security only allows
application access (beyond SA, which you really cannot get rid of). You
then set up the application clients on the boxes and still have the
server.

You cannot attach multiple SQL Express instances to the same MDF. You can
with SQL Server Enterprise, as a cluster, but that would be an expensive
option to use a networked MDF file. It would also not be the proper way to
cluster.

--
Gregory A. Beamer
MVP: MCP: +I, SE, SD, DBA

Blog:
http://feeds.feedburner.com/GregoryBeamer

Thanks again for the additional insights.
Perhaps the cost of something like aSQL EE licanse will cause the
decisionmakers to rethink their position

 

[Gregory A. Beamer]

Thanks again for the additional insights.
Perhaps the cost of something like aSQL EE licanse will cause the
decisionmakers to rethink their position

or at least give you real concerns that you can fight with proper
documentation. ;-)


--
Gregory A. Beamer
MVP: MCP: +I, SE, SD, DBA

Blog:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think Outside the Box! |
********************************************