Two MS SQL Instances, one database file
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.
Solution 1:
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).
Solution 2:
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.