can I attach database on different sql instances at the same time?

Question

I have sql database stored on a shared netwrok drive , after set of configurations I can attach this database over the network on sql server 2008 instance.

But I want to attach the same database on different machine that contains different sql instance (I think logically there is no data corruption as I want it to be reading only).

So my question is can I attach databse to more than one sql instance at the same time? and how?

I am doing this to use a centralized database? if I can not, so what is the other solution to support database sharing between different sql instances on different machine?

(I know there is SAN storage but it is costly for me, and I think Fiber/SCSI connection will not support sharing)

Answer 1

The closest you will get is this: Scalable Shared Databases

Otherwise, it's a simple no. Each instance wants to use the files exclusively.

There are also issues using files on network shares: disks are expected to be local in device manager (either local, NAS or SAN). You might be able to attach files on UNC paths but you definitely can't create new DBs on UNC paths (without some trace flag trickery).

Answer 2

It's not clear to me WHY you want to do this: why don't you just attach to one instance and let people access it, through the usual connections? In case of need, you could setup a user who can access only that database and is granted only SELECT permission on tables and views (beware of permission on stored procedures) and let them go, without running the risk that one of them accesses unauthorized databases, execute illegal operation on this one or that some problem with one instance corrupts the database for all.
I would think twice about the possible risks of doing this

Answer 3

I don't think you can attach one db file into multiple server or server instance, if both servers are online.

If your goal is to have a replicated copy of database on another server, then have a look at SQL Server replication. There are several types of replication that you can use depending on your requirement. Another option is Database Mirroring, which will allow you to have mirrored copy of your database in another server, but I don't think it really fits your need as with mirroring only the database in the current principal server can be accessed.

Mirroring is mostly used for high-availability and Disaster recovery scenarios. In SQL Server Replication, you can have both of your server available and you can even update both copy of your database and have them synchronized on both servers.