Is it possible to bind a database to different sql instances at the same time?
I have an sql database stored on a shared network drive, after a set of configurations, I can attach this database over the network to my sql server 2008 instance.
But I want to attach the same database on a different machine that contains a different sql instance (I think there is logically no data corruption as I only want it to read).
So my question is, can I bind the database to multiple sql instances at the same time? And How?
Am I doing this to use a centralized database? if i can't, what is another solution to support sharing databases between different sql instances on different machine?
(I know there is SAN storage, but it's expensive for me and I think a Fiber / SCSI connection won't support sharing)
a source to share
I don't think you can attach a single db file to multiple server or server instances if both servers are online.
If your goal is to have a replicated copy of the database on a different server, look at SQL Server Replication . There are several types of replication that you can use depending on your requirements. Another option is Database Mirroring , which will allow you to have a mirror copy of your database on a different server, but I don't think it really suits your needs, as with mirroring, you can only get a database on the current master.
Mirroring is primarily used for high availability and disaster recovery scenarios. In SQL Server Replication, you can use both servers, and you can even upgrade both copies of your database and keep them in sync on both servers.
a source to share
The closest you'll get is this: Scalable Shared Databases
Otherwise, it just isn't. Each instance wants to use files exclusively.
There are also problems with the use of files on network shares: the drives are expected to be local in the device manager (local, NAS or SAN). You might be able to attach files on UNC paths, but you definitely cannot create new DBs on UNC paths (without some trace flag trick).
a source to share
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 normal connections? If necessary, you can configure a user who can only access that database and only get SELECT permissions on tables and views (beware of stored procedures permissions) and let them go without risking one of them accessing unauthorized databases , perform an illegal operation on it, or that some problem with one instance corrupts the database for everyone.
I would think twice about the possible risks of this.
a source to share