Sql-server – Two MS SQL Instances, one database file

sql serversql-server-2008

Is it possible to have one database file attached to two different instances of SQL?

Consider that the MDF is located on a NAS and I want a primary and secondary instance of MS SQL on two different servers (not considering clustering in this question) and I wish both instances to reference this same MDF.

Can only one instance be attached to the MDF at a time or can both.
Is there an issue with attaching SQL instance 'A', un-attaching then attaching instance 'B' to this MDF?

Thanks for your input.

Best Answer

Feb 2018: This answer is very old and rather outdated. Please don't rely on it any for modern SQL installations (2014 or above). Keeping it below for historical purposes


The correct way of having a cold standby server in MSSQL is SQL Log Shipping. This involves having two copies of the database on two servers, with A restoring log files to B at regular intervals.

The correct way of having a warm standby server in MSSQL 2008 R2 or below is SQL Database Mirroring. In SQL Server 2012 is AlwaysOn. This involves having two copies of the database on two servers in lockstep with eachother.

The correct way of having a hot standby server in MSSQL 2008 R2 or below is SQL Clustering (in 2012 this is also a part of AlwaysOn). This involves a single copy of the database on a SAN (not a NAS, unless your NAS can expose iSCSI volumes that support custering; some do), Windows Server 2008 R2 Enterprise (or 2012 Datacenter), SQL Server Enterprise, and a correctly configured Windows Failover Cluster.

No version of SQL Server will allow its MDF/LDF files stored on a CIFS/NFS/SMB share. They must be stored on block storage, which gives you the option of local disks, or SAN volumes (such as iSCSI or FC exposed volumes).

Related Topic