<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL 2012 With Linux Shared Data File Storage

Published on
8,222 Points
2,222 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
Author:kevp75
1 Comment

Expert Comment

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

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month