Fog Creek Software
Discussion Board




SQL Server: Can multiple instances access one DB?

Hi All,

I've just spent a considerable amount of time looking for the answer to this and can't find a definitive answer either way.

Can two machines with SQL Server 2000/MSDE 2000 both work from the same .MDF database file at the same time?

This is for an app that will be sold for small workgroup use as well as full corporate network use. For the small installs I need the users to be able to either 1) use a local data file, or 2) point their app to a data file on a network share that they or a few other users may be accessing at the same time. Can this be done with MSDE 2000?

I'd greatly appreciate any tips as to the concepts and keywords involved in addition to the final answer. I see that you can call a detach and attach command, as well as specify an initial DB to attach to with the connection string, but nothing I've found specifically confirms or denies the ability for multiple instances to be attached to the same database file.

Thanks in advance.

  --Josh

JWA
Wednesday, August 11, 2004

No.

If you want to do it that way use MS Access.

The SQL (MSDB) works is that you will set up one server. Then you can have multiple applications accessing the server over the network (not using shared files, but rather with SQL's own protocol.) You only need to have SQL(MSDE) installed on the server.
I'm sure that this is a better solution to your problem.

Gary

Gary van der Merwe
Wednesday, August 11, 2004

I don't see why you would want or need two instances of sql sever. Just allow the second server to access the first one using the standard protocols.

www.MarkTAW.com
Wednesday, August 11, 2004

The final goal is to make it as easy as possible for users of the standalone installation to network their data. Ideally they could do it themselves over their network without needing their IT department to setup a DB server. On our end, we already have our DAL setup to work with SQL Server, using stored procedures with return values, so JET/Access won't work directly and  MSDE for the smaller app would be the easiest fit.

With MSDE the user installs the app (which also installs MSDE) and then they can work from their computer alone. If two or three others in their department need to work with the app and data also, it would be ideal if we could provide a function where they select a network folder where they want to store the data and then all of the users point to that location to access it.

The only way that I can see to do this is to move the .mdf and related files to the network share and have each user's local instance of MSDE access the single database. Are there any other ways to accomplish this?

  --Josh

JWA
Wednesday, August 11, 2004

How about this:

Have a setting for the connection string.

Have the instalation default it to the local server. If the user wants to, they can change it to a different server.

Hope this helps.

Gary van der Merwe
Wednesday, August 11, 2004

No, I see his point. His target audience is more likely to undertand shared drives than IP addresses.

www.MarkTAW.com
Wednesday, August 11, 2004

To clarify: it needs to be over a network share because all machines involved will be user's machines, not network servers. Pointing later users to the first guy's machine won't be intuitive to them to set up and we need data availability beyond that single machine being up and on the network.

Any of the clients who are large enough to have/need the primary version of our program will be running it from a full SQL Server. It's the separate users in smaller workgroup situations that I need to address here.

  --Josh

JWA
Wednesday, August 11, 2004

"His target audience is more likely to undertand shared drives than IP addresses."

Exactly, well put.

JWA
Wednesday, August 11, 2004

You don't have to use a IP address in the connection string. That's what WINS/DNS are for.

I agree that connection string may be a bit tricky for a end user. But one could easily create a nice interface to just select a server (and chose between Win Authentication, or to enter a user name and password for SQL Authentication)

Gary van der Merwe
Wednesday, August 11, 2004

Even if you make it as easy as possible to configure, the problem is that you're still storing all data on the one user's machine and all access is dependant on it being available.

  --Josh

JWA
Wednesday, August 11, 2004

Yip, that sums up it situation.

SQL was designed to be a Server application.

Your other options, which have been previously examend are to for them to install a central server, or you provide Jet support.

I know you can pass prameters to a Jet Query. Maybe there is a way to set the @RETURN_VALUE

Gary van der Merwe
Wednesday, August 11, 2004

How about when the software is installed you put a file in the same folder as the data that has the connection information.  The users can point to the mdf file.  You can read the other file with the connection string.  Figure out a standard username/pw that you can use in your program and can setup when installing the software.

Maybe there is some way of telling if another instance of SQL server is currently using the MDF file.  If not one of the other computers could take over and update the file.  That way they don't need to make sure that Bob's computer is on, even when he's on holiday.

I think it's going to be messy with SQL Server/MSDE though.

Steven
Wednesday, August 11, 2004

I finally found at least a few pertinent mentions of this: http://sqlteam.com/Forums/topic.asp?TOPIC_ID=5894 which contains a link to this discussion: http://tinyurl.com/5oo3g (Google Groups).

The bottom line appears to be that it can't be done, the first instance acquires a file system level lock on the files.

SQL Server 2005 Express is supposed to support Xcopy deployment of the files, as well as network storage of them, but no mention is made of being able to share/attach them to multiple instances at once.

So, it looks like our only choice is to build in Jet functionality. I might make a new post asking for tips on downsizing SQL Server stored procedures for use with Jet.

Thanks for the help, I've literally been up all night working that one out.

  --Josh

JWA
Wednesday, August 11, 2004

"SQL Server 2005 Express is supposed to support Xcopy deployment of the files, as well as network storage of them, but no mention is made of being able to share/attach them to multiple instances at once."

No, you certainly can't do this. SQL Server 2005 basically made detach/attach functionality a little easier, but it still expects to basically own the file itself.

I think you need to take a look at replication in the SQL Server world - the idea is that you generally have one central server, and whenver the users are connected to the company network all of their individual data is replicated to the central store, where everyone can access it, and all central data is replicated to their remote copy (including other users data. There are an infinite number of setup possibilities with replication so it requires a lot of planning), however they can then detach and take their PC remote, logging information in their own database and such.

Dennis Forbes
Wednesday, August 11, 2004

It sounds to me like you're trying to make SQL Server do something it was never meant to do and therefore any SQL Server solution is is likely to be flawed. Have you looked at a middleware solution like a directory server to advertise the local servers? How about a multicast solution? Tibco isn't cheap but if you want location independence then IP Multicast is the only way to do it.

I suspect you are going to have a nightmare at the workgroup level. What happens when workgroups want to consolidate their data? How do they migrate their data to a full SQL Server version?

John Dudmesh
Wednesday, August 11, 2004

Only one person said the magic word -- 'Replication'.

I've used it under Sybase, and once set up (which was really hard under Sybase, by the way) it worked well.

Each transaction on your 'A' server is replicated to your 'B' server.  Each transaction on your 'B' server is replicated to your 'A' server.  Thus each application/person accessing whichever server sees the same data.

Of course, you do have to have two installations of Sybase to do this -- but you were already going to have two installations of SQL Server, right?

This way, each install 'owns' its own files, yet its files are updated to match the other install.

AllanL5
Wednesday, August 11, 2004

But replication would still require that the users deal with IP addresses/machine names and each new install would require changes to all other installs.

JWA
Wednesday, August 11, 2004

"But replication would still require that the users deal with IP addresses/machine names and each new install would require changes to all other installs"

While you certainly could implement a solution that would require this, that is not generally the case. Generally replication works with one central server (I mean for cases such as yours) that is the combination of all data from all peer machines (which would be the laptops or detached machines) -- All of them replicate changes on the central server to themselves, while pushing local changes back to the central server. Obviously if Peer 1 does a replication, once Peer 2 connects he'll also see the changes of Peer 1 (because they've now been replicated to the central server). Neither of them need to have any knowledge of each other, or any replication setup referencing anything other than the central server.

Dennis Forbes
Wednesday, August 11, 2004

Right, but replication was reccomended in response to the need for a system without a central database server. We have a config supporting the use of a DB server, we're looking for a config using a network share. If it's a workgroup with a few separate installs all running MSDE and relying on replication to sync up each node would need to know about each other.

  --Josh

JWA
Wednesday, August 11, 2004

Josh,

where would your network share live? Either it is at some fixed address or you'd have to use some discovery protocol. But when you are able to do this, you'd also be able to do the same for an MSDE install: either it lives at a fixed location, or you need a discovery protocol.

BTW: dealing with a share or server living on a peer that might (and will often) go down at any moment is not going to be fun in either case.

Just me (Sir to you)
Thursday, August 12, 2004

I'm sorry, I guess I might have not explained that we can assume a dedicated/fixed file server exists and is available, we just can't assume that the users know which machine it is or have actual access to it.

A typical user will have access to an existing shared network drive on a file server setup by their IT department or someone who knows enough to do so. The user will at a minimum know that he and his other colleagues can all access files saved to a certain drive/folder. They could follow the directions to choose a shared folder where they want to save the data and instruct everyone else to choose the same folder as part of the process.

However they would not be able to go to this file server, install a service, and specify the machine name/ip on the clients. It may be a small office where the file server is just another machine in the back closet. In that case this would be easy enough to do if they knew about it, but much more likely they will be a single department in a medium sized company and be on a corporate network. In this case their IT guys will have to set up MSDE on the server and to the average user that changes it in their mind from being something that they can do themselves to a long drawn out process.

I'm sure that the major difference of opinion is that I am operating from the assumption that the "average" guy on a corporate network is loath to have to involve the IT department, whereas most people on here assume that's just how things are or should be done.

I am going to spend some time going through use cases to see if maybe it wouldn't be too much trouble to just go with MSDE with a good installer that makes it easy and obvious how to run alone or set up a simple db server. Maybe it wouldn't be as large of a roadblock as it appears to me. Any time that the user is on a network large enough that they don't have an admin account they are going to have to call IT anyways. If they are a medium shop with a hub and a computer in the back closet they wouldn't have the IT paralysis and one of the guys would be able to run an installer on the machine used as the server. And if they really are just two or three guys on a hub with no server they would have to do some syncing anyways so we could set up a replication based syncing process.

I'm going to think that option through in detail based on all of the push-back I'm sensing here.

  --Josh

JWA
Thursday, August 12, 2004

Obviously I second everyone else's view that you are trying to do something strange which is not going to work.
Your problem is letting the clients find their servers

There are APIs for finding a list of servers and a list of the databases on that server. They do not, in my experience, work particularly well however (either in ones own software or or in Microsoft software) - however insofaras they work you can populate a combo box of servers on the network with SQL server databases, then when they select one you can populate another combo box with the databases on that server.
The method are  ListAvailableSQLServers and GetDatabases (both methods of SQL-DMO as far as I can see)

An alternative would be to define a text config file which could happily sit on a server somewhere and get the users to navigate to that e.g.

#HELLO I AM A CONFIG FILE
server=weasel
database=wombat

If people are used to servers may be this would be a better idea (particularly given the APIs seem somewhat flakey to me)

Harvey Pengwyn
Thursday, August 12, 2004

JWA,

I see what you are trying to do. You want to have the lowest possible technical barrier to entry so as to enable more impuls/"grassroots" adoption of your product.
What you could do is keep the app on MSDE (so as not to have to change code). Give each install a local MSDE. Now on the file share you'll put a single file where you will record all updates. Have each copy write all updates to this file, and have all instances poll the file for updates. If any they merge them into their local DB.
I know their are some tricky bits their wrt. shared file access, but it is the simplest I see without giving it too much thought.

Just me (Sir to you)
Thursday, August 12, 2004

Ah, I see, the 'how do we get a database in under the I.T. department radar?' problem, not the 'how do users find an installed database on the network' problem, which was what I was answering.

Choices are, I guess.
1. Switch to a file based database e.g. Jet. Hope the users do not get too pissed off when their Jet database gets corrupt and, because they have done this under the radar they don't have any proper back-ups.

2. Find another embeddable RDB that has a less weird installer than MSDE, it will probably cost money

3. Go with MSDE. Convince the users it is warm and fluffy.

4. Persuade the users to go through their I.T. channels and have a proper backup strategy.

In cases 2 and 3 they will probably not do proper backups anyway because users don't in my experience, and it will probably be harder with MSDE (particularly given the absence of a UI, you will have to write your own front end to the backup mechanism and you will be blamed when it all goes wrong)

Harvey Pengwyn
Thursday, August 12, 2004

*  Recent Topics

*  Fog Creek Home