<

SQL 2012 With Linux Shared Data File Storage

Published on
8,127 Points
2,127 Views
Last Modified:
Approved
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);

Open in new window


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

Viola!
0
Comment
Author:kevp75
1 Comment

Expert Comment

by:Alex Angus
What about Samba 4 to emulate Windows AD?
0

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Join & Write a Comment

Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month