SQL Server database on UNC share

Question

I have two machines running a C#.net windows forms application each. I want them both to connect to a single SQL Server database which is in one of the machines. And I like both applications to work with that one single database. But Unfortunately I'm getting this UNC share problem. Is there a way to solve this? Or do I have to try a different approach. Please Help Me. I'd appreciate It.

Answer 1

AttachDbFileName is a terrible and misleading feature. What happens is that each application that connects to this "database" makes a copy of the data file. So if Machine1 connects, and makes changes, those changes are invisible to Machine2. This causes a lot of confusion even in a single-machine scenario because people will connect via Visual Studio, make some update, and then won't see it from Management Studio. Or vice-versa. The User Instance feature has been deprecated for a reason; please stop using both of these.

Since you want multiple machines/applications to connect to the same copy of your database, the solution you want is to have a single copy of the database attached to a single instance of SQL Server, and both applications/machines just connect to that single copy.

On MACHINE1-PC do this:

1. Move ShopDatabase.mdf and its associated .ldf file out of your user folder and into the data folder for your instance. This will be something like:

   C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Data\

2. Connect to the local Express instance using .\SQLEXPRESS. Attach the database using the following code:

   CREATE DATABASE ShopDatabase 
     ON (FILENAME = 'C:\...\ShopDatabase.mdf'), 
     -------------------^^^ fill this in
        (FILENAME = 'C:\...\ShopDatabase_Log.ldf') 
     -------------------^^^ fill this in
   FOR ATTACH;
   
   -- if there is no log file, you may need to do:
   
   CREATE DATABASE ShopDatabase 
     ON (FILENAME = 'C:\...\ShopDatabase.mdf') 
     -------------------^^^ fill this in
   FOR ATTACH_REBUILD_LOG;
   

3. Determine the external IP address of MACHINE1-PC (you can do this via ping from another machine - locally it will tell you 127.0.0.1 which is useless). Assuming you have a fixed IP and are not using DHCP, this is likely going to be a more reliable way to connect, or at least is missing an extra step (resolving the name). If you use DHCP though you won't want to rely on this because your IP address will potentially change over time. Your connection string should now be:

   Data Source=MACHINE1-PC\SQLEXPRESS;
     Network=DBMSSOCN;
     Integrated Security=True;
     Initial Catalog=ShopDatabase;
   
   -- or:
   
   Data Source=<<IP Address>>\SQLEXPRESS;
     Network=DBMSSOCN;
     Integrated Security=True;
     Initial Catalog=ShopDatabase;
   
   -- (replace <<IP Address>> of course)
   

On Machine1 your connection string could use Data Source=.\SQLEXPRESS, but better to be consistent than to save a couple of keystrokes. This way if you make other changes to your config file etc. they can be distributed to other machines without needing to change the machine name.