SQL 2012 With Linux Shared Data File Storage

I recently “upgraded” my local server, and finally made the jump into Linux servers.  I am now running Ubuntu 13.04 Server for everything from hosting to file services.  As a result of this jump (and the need to still be able to develop for .Net and other MS web based services), I still needed to be able to have a SQL Server instance, and IIS 7.5.

As a result, I installed VirtualBox with Windows 2008 R2 server, configured all my networking and shared some folders from my Linux install for usage and storage purposes in my virtual machine.

Most of the reasons for doing it like this was because I did not want to make the virtual drive for the machine too large, another is so I can have my sync services run without fail.

So, my thought was that I could simply attach the drive, and be able to attach any databases on it to my SQL Server instance, however, this failed miserably.  

The issue was permissions between the SQL Server instance and my linux box, and since you cannot have windows account, let alone windows service accounts on a linux box, I asked my good buddy over at Internet Services Inc., who happened to confirm my suspicions, but also mentioned that if I were able to install AD (active directory), or LDAP (lightweight directory access protocol), on the linux box, and make that machine the provider for all my networked machines accounts, that I should be able to do it.

So, I researched, and searched, and searched… and found that OpenLDAP is pretty difficult to install and configure.  Not being much of a networking guy, I decided to experiment a bit.  I decided that if I create a user on the windows box, that matched a user on the linux box, that it may just work.

I was pleasantly supprised, and found that it did indeed work as I intended it to, however, I was not able to simply attach a database from the networked location.  It just didn’t show when I browsed for it.  I had to script it (below).

So, in summary, this is possible to do:

1. Install LAMP with SMB services
--------remember your login, you will need this later

2. Share a folder on the LAMP

3. Install VirtualBox

4. Create a new Virtual Machine, and install Windows 2008 R2 Server on it, with SQL 2012 Server
--------Note, this was done with this version, so I do not know if previous versions allow the same results

5. Create a new user on the Windows Install, with the same username and password from your LAMP install

6. Now, open up ‘Sql Server Configuration Manager’

7. Under ‘SQL Server Services’ right-click ‘SQL Server’ and select ‘Properties’

8. Under the ‘Logon’ tab, click the ‘Browse’ button next to ‘Account Name’, and find your newly created user account

9. Hit ‘OK’, and let the service restart

10. Run the following script (Transact-SQL)

DBCC TraceOn(1807);
                       
                      EXEC sp_attach_db 
                      @dbname = N'YOURDATABASENAME',
                      @filename1 = N'\\192.168.2.X\DB_FOLDER_LOCATION\DATABASEFILENAME.mdf',
                      @filename2 = N'\\192.168.2.X\DB_FOLDER_LOCATION\DATABASELOGFILENAME.ldf';
                       
                      DBCC TraceOff(1807);

Select all Open in new window

Now, fire up SMSS and verify that it is indeed attached

Viola!