Attaching an MDF database to SQL Server and sharing that database

Question

Could someone explain the correct method of making a database available to two computers.  I am using sql server 2008 R2 and vb.net applications on two laptops joined by a workgroup.  I want both applications to concurrently use the same database.  I have googled that the database must be attached to the instance of SQL Server running on one of the machines, do I do this using the connection string and conn.open() or should I use sp_attach_db?

Thanks for your help

Brad

---

BHend

Answer 1

Hi Brad, attaching the database and connecting to the database are two different topics.  In this scenario, you only need one instance of the database.  On that instance, you can use one of several techniques to attach your database, the easiest probably being through TSQL (sp_attach_db) or through the SSMS UI.  Attaching the database is a one-time operation and after attaching it, it is ready to be used.

Now that the database is attached, you need to create a connection string in your vb.net code to connect to the instance.  Both clients can use the application to connect to the database at the same time.

Here is a great article with samples for connection code:
http://code.msdn.microsoft.com/Esempio-applicazione-dati-494c129a

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

Hello Sam,

Thank you for your answer, could you tell me how I use sp_attach_db within vb code? 

I will work through the link you provided to carmelo's example.  Being new at sql & vb.net I have to learn each new step, so it will take a little while. 

Thanks again

Brad

---

BHend

Answer 3

You won't want to go through VB code to attach the database, but instead use SSMS to do so since this only needs to be done one time.  Do you have a detached copy of the database or do you need to create the database yourself that you'll use for the application?  If you have the detached copy of the database, you can copy it to the local machine running SQL Server, connect to the instance, and attach the database through the UI.  The database will be attached and you won't need to detach/attach ever again.  Take the AdventureWorks database as an example.  You can go online, download the sample database, attach it, and start to run queries against it from SSMS.  From then on, you have this database in your instance and you don't ever need to attach again. 

Now that the DB exists, from the VB code you'll need to create a connection to the database in order to execute some sort of stored procedures / functions / TSQL code against the database.  This is where you'll want to follow the many good blogs/examples online for executing TSQL through VB.  Some basic advice is to create stored procedures in your database to handle the database operations, then call the stored procedures from you VB code. 

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 4

Hello Sam,

The full scenario is that I have developed this application for a small business that operates chattel auctions that sells hundreds of lots to hundreds of buyers with between 20 to 30 different sellers.  The way I have set it up is that each auction day is a separate mdf file.  So we have 30 odd mdf files from the last 3 years each representing a separate auction day.  I have also given the user the ability to create their new auction database (by copying a blank database and renaming to their new database name).  This has been working successfully for 3 years but now I wanted to add the ability of a second laptop to share the work load on auction day by inputing bidder names and buyer payments etc to the same database while the main laptop is also performing these funtions.  So now the flaws in the application are becoming very apparent. 

I was hoping that I could do the following:

1.  Have the main user on laptop A still create the database as before and attach it to SQL through the vb.net application( because they can't be relying on me to perform the SSMS function). 

2.  If workload demands permit laptop B to run the application and access the same database to assist in those tasks required.

To do this I was going to check if the database is attached, if not then attach it.  Then using VB connect from both laptop A and laptop B.

Is this possible? 

Thanks again Brad

---

BHend

Answer 5

Hi BHend,

I want to inform that we can execute VB.NET codes on laptop B to attach database to SQL Server instance on laptop A. I made the following codes and test on my computers and found they worked, you can refer to them:

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common


' connect to the server on laptop A 

Dim srv As Server
srv = New Server("ServerName")

' To check whether the database has been attached 

Dim Attached As Boolean = False
For Each dbitem As Database In srv.Databases
If dbitem.Name = "DatabaseName" Then
Attached = True
End If
Next

' If the database has not been attached yet, we can use the following codes to attach it. 

If Not Attached Then
Dim owner As String = "OwnerName"
Dim datastr As String = "D:\SQLData\DatabaseName.mdf"
Dim logstr As String = "D:\SQLData\DatabaseName_log.LDF"
Dim sc As New StringCollection()
sc.Add(datastr)
sc.Add(logstr)
srv.AttachDatabase("DatabaseName", sc, owner, AttachOptions.None)
End If

For more detail information about how to attach a database to SQL Server instance, please refer to the following document:

Create a Visual Basic SMO Project in Visual Studio .NET:
http://msdn.microsoft.com/en-us/library/ms162562.aspx

Server.AttachDatabase Method (String, StringCollection):
http://msdn.microsoft.com/en-us/library/ms210158.aspx

---

Best Regards,
Allen
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 6

Hello Allen,

Just had to add this line:

Imports System.Collections.Specialized

and it works perfectly.  Thanks Allen and Sam for your help it is greatly appreciated.

Brad

 

 

Imports

System.Collections.Specialized

---

BHend