1

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.

4
  • I would guess it won't work (I believe MS SQL uses file level locking when it attaches to the MDF and so the second instance will be refused), however, this seems to be the description of a solution, what problem are you trying to resolve by having two different instances of SQL use a single file? Nov 20, 2012 at 15:07
  • @EightBitTony I'm trying to figure out the best way to facilitate a hot-cold fail over system for a customer of ours. There is an SQL server which will be used as a primary SQL engine. If for any reason this machine fails I want to be able to switch over (in our software) to the second SQL engine on a different machines. I don't want the complexity of a cluster, but a procedure to 'Attach' the MDF to the second SQL engine is ok, if this is an acceptable process.
    – Damo
    Nov 20, 2012 at 15:48
  • Then I believe you can do that as long as they're not both running at the same time. Nov 20, 2012 at 16:19
  • @EightBitTony thanks, do you want to form that as an answer so I can credit you with it?
    – Damo
    Nov 20, 2012 at 16:22

4 Answers 4

3

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).

2
  • Is the clustering part of this answer still accurate? Because I think smb3 file share can be used instead of iSCSI now.
    – John L.
    Feb 5, 2018 at 16:57
  • 1
    I believe that you are correct @JohnL. - times have moved on :) I would also almost never recommend SQL Log Shipping these days except in extremely specific circumstances. Feb 5, 2018 at 19:20
1

With this kind of scheme, there is no guarantee that the MDF won't be corrupted when server A fails. Or the NAS fails.

The scheme ignores the LDF file. It is common for people to think that the LDF is not important, but this is not the case. The LDF functions as a write-ahead log and changes are "replayed" when an instance restarts (or the other instance starts, as in a cluster). You need the LDF or you will lose data.

Another thing to consider is that SMB-based NAS devices often have horrifically bad performance. If the device supports iSCSI, the situation probably isn't as bad. If you use SMB, using a mapped drive is a hassle. You will have to tweak SQL Server to store files on a network share.

If you want to avoid clustering, just do what everyone else does and look into database mirroring (which was introduced in SQL Server 2005) or log shipping (which can work with pretty much any SQL Server version, though official support did not debut until SQL Server 2000).

Following the crowd is usually the safest thing to do, though it might not be the most interesting thing to do. When you are dealing with a client's data, you want "safe", not "interesting".

Regardless of which tactic you use, there are other details to worry about, like making sure that the jobs, logins, passwords, user ids, etc. are kept current on both instances, make sure that you keep your backups straight, etc.

0

From the reading around I've done, MS SQL uses file level locking on MDF files. As a result, you can't have two instances of MS SQL using the same file if they're both running. However, you can have two MS SQL instances use the same MDF if you have a mechanism to ensure only one runs at a time.

0

If you are looking for a method where you want a nearly ready to run backup SQL server, one option would be to just have a backup of the database generated on the primary server on a periodic basis. Then you can have a process that copies the backup to your secondary server. Then you just create a script to import the backup to the secondary server whenever you need it.

This also eliminates the external NAS device as being the single point of failure if each server has its own storage. This technique works fine for older versions of SQL Server. However, if you have the latest version, SQL Server 2012 Standard Edition (and higher) supports an even more robust method of Database Mirroring. See the link below:

SQL Server 2012 Database Mirroring

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .