Now that SQL Server vNext for Linux CTP1 is available, you want jump in and test you installing existing databases. You may want to use databases with compatibility to SQL Server 2012 and greater. As I’m not a full Linux Admin but this opportunity to setup and use SQL Server on Linux definitely has help stepup to learn about Linux OS.
Check out Microsoft SQL Server vNext for Linux page:
https://www.microsoft.com/en-us/sql-server/sql-server-vnext-including-Linux
So, before you start working or trying to restore your Windows SQL Databases to SQL Server on Linux, there are a few things you may need to do. This will involved installing components which are not installed by default when first build your Linux System.
One thing I had to say with confidence! You WILL find all your answer on the internet. Any of the Linux Distro forum have your resolution to any of the issue you may encountered. Also, most of the time you Linux system will tell you (or gives a tip) what do with missing dependencies.
Other than that, there are Linux Application Updater solution to help keep your system up-to-date.
Here’s some tips on what’s needed to connect Windows to Linux shared folders in Ubuntu LTS 16.04.1
1. understand your SQL Server database Linux path – as you may know this is totally different than you Windows database location. In Linux, your databases will be preferably stored at ‘var/opt/mssql/data‘ which ti will be seem on SSMS as ‘C:\data’ folder and this may change on later builds.
Linux path to SQL Databases = ‘var/opt/mssql/data‘
Windows in SSMS shows Linux path as = ‘C:\var\opt\mssql\data’ (path corrected for SQL Server CTP1)
2. Create a Windows Shared folder – This one is easy to do using the Windows Explorer. But, you will need to play with assigning permission. To speed up the processes (for testing) use “Everyone” with Read-Only.
3. Creating a Linux Shared folder – You could use bash ‘sudo nautilus’ to create the folder using GUI. Or, straight command line:
[sourcecode language=”bash”]
$ sudo su
# mkdir /var/opt/mssql/data/TempSQLBackup
# chmod 777 TempSQLBackup
[/sourcecode]
4. Install Linux application/Libraries/Dependencies – As I mention before, the internet will provide answers to any issues. Here I will provide some to the point solutions:
a. Install samba:
[sourcecode language=”bash”]
$ sudo apt-get install samba
[/sourcecode]
b. *Add your domain name(or workgroup) to samba configuration:
[sourcecode language=”bash”]
$ sudo gedit /etc/samba/smb.conf
[/sourcecode]
*note: Look for -> workgroup = WORKGROUP (or Domain)
c. Restart samba process:
[sourcecode language=”bash”]
$ sudo service smbd restart
[/sourcecode]
5. Testing Linux can see the Windows Shared folder – Use this command:
[sourcecode language=”bash”]
$ smbclient -L IPAddr_or_Sysname -U UserName
[/sourcecode]
5. Connect to your Linux to Windows Shared folder command: (It will prompt for the user password)
[sourcecode language=”bash”]
$ sudo mount.cifs //10.0.0.31/TempSQLBackup /var/opt/msql/data/TempSQLBackup -o user=max_t
[/sourcecode]
6. Verify you can see the windows backup files on Linux. (use nautilus or bash command)
Now, we can successfully restore a database from a Windows Shared folder. No need to FTP your Windows backup to Linux FTP site.
Using PowerShell
From your Windows client, Using PowerShell to restore your Windows Database Backup to Linux. Using the SQL PowerShell cmdlet “Restore-SQLDatabase” available when installing SQL Server Management Studio.
Here’s a sample script:
[sourcecode language=”powershell”]
Restore-SqlDatabase -ServerInstance orion -Database AdventureWorks2014.bak -BackupFile “C:/data/TempSQLBackup”;
[/sourcecode]
Keep in mind, for now you can manage your “SQL Server vNext for Linux” (up to some extend) using either the existing SQL PowerShell cmdlets or buidling your own PowerShell scripts with SMO.
Here’s an example using SMO:
In above sample, although I have connected to a SQL Server Linux engine, the platform still showing as “NT x64”. This issue has been addressed and will be corrected soon.
So, try running your Windows PowerShell scripts against your SQL Server Linux systems.
Keep Learning PowerShell!!