I've recently been assigned to a Rails project that needs to connect to a SQL Server database and on this post I'll go through the process of configuring a Windows 2008 VM with SQL Server 2008 Express using Vagrant. If you are just interested on the code you can get it from here.
First of all, let me preface this post by saying that I haven't used Windows in a loooong time and this was the first time I interacted with PowerShell. This post is a result of lots of googling and trial and error so if you know how to do something in an better way feel free to submit a PR to the project or drop a comment below :)
Another good thing to mention is that this will eat A LOT of your disk space,
the Vagrant base box
of your hard drive and the VirtualBox VM with SQL Server installed along with
its dependencies will take around
9 GB of your disk (which is A LOT
when compared to Linux VMs / LXC containers)
12GBof disk space (
3GBfor base box +
~9GBfor the VM)
More information can be found on the box page at Vagrant Cloud.
It wasn't the first time I had to set up a Windows VM with SQL Server but it was the first time I had to do it from the command line. The process is very straightforward and it involves:
When it comes to using a Windows VM with Vagrant, we also want to enable remote desktop connections as it seems to be the primary method for performing administrative tasks on Windows.
I've automated all of that apart from the downloading the installer (and the
of course :P) and the code can be found on GitHub so you are
git clone and a
vagrant up away from having a SQL Server instance at
your disposal. What follows is some information on how I automated that process and
instructions on connecting to the SQL Server from Ruby apps.
Please keep in mind that the process of creating the Vagrant VM from scratch after
downloading the base box and setting things up will take a while (around 15 minutes
on my machine), so go grab a coffee when you
vagrant up for the first time
This is currently the only manual step involved on the process, there was enough
experimenting in place already for me to figure out how to download a file using
PowerShell. Since you are likely to be on a Linux / Mac machine, you can easily
download it using
vagrant-mssql-express project root, run the following command to download
1 wget http://download.microsoft.com/download/0/4/B/04BE03CD-EAF3-4797-9D8D-2E08E316C998/SQLEXPRWT_x64_ENU.exe
Given that everything under the directory where your
Vagrantfile is located
gets automagically shared with the VM by default, this should be enough to
continue with the provisioning process.
This is an easy one for Windows servers, we can just import the
PowerShell module and add the Windows feature as outlined below:
1 # http://stackoverflow.com/a/9949105 2 $ErrorActionPreference = "Stop" 3 4 import-module servermanager 5 echo "Installing .NET Framework" 6 add-windowsfeature as-net-framework
.NET is not really required to run the SQL Server, but it is needed for the Management Studio so that you can manage the server instance.
Given that you've downloaded the installer into the
root, the installer will be available from
C:\vagrant from within the VM. To
install it I chose not to use PowerShell because the installer spawns a new process
and provisioning would continue without waiting for the installation to complete.
To speed up the provisioning process I also added the code to disable the Windows Firewall on the same script as I had trouble disabling it with PowerShell too:
1 @echo off 2 3 echo Installing SQL Server 2008 Express R2, it will take a while... 4 C:\vagrant\SQLEXPRWT_x64_ENU.exe /Q 5 /Action=install 6 /INDICATEPROGRESS 7 /INSTANCENAME="SQLEXPRESS" 8 /INSTANCEID="SQLExpress" 9 /IAcceptSQLServerLicenseTerms 10 /FEATURES=SQL,Tools 11 /TCPENABLED=1 12 /SECURITYMODE="SQL" 13 /SAPWD="#SAPassword!" 14 echo Done! 15 16 echo Disabling firewall 17 netsh advfirewall set allprofiles state off
Line breaks were added for readability, I'm not sure how Windows will handle that so make sure you get the snippet in a single line if you are doing the process manually and trying things out by hand
That will install SQL Server 2008 Express and will set up the
sa user password
#SAPassword!. More information about the parameters that can be passed on to
the installer can be found here.
By default, the SQL Server instance will not expose the default TCP port to the outer world and it will configure some random port, meaning we'd need to open up the SQL Server settings to find out what port we should connect to.
The PowerShell script below will do the magic to make it allow connections on the
1 # http://stackoverflow.com/a/9949105 2 $ErrorActionPreference = "Stop" 3 4 echo "Configuring TCP port" 5 6 # http://technet.microsoft.com/en-us/library/dd206997(v=sql.105).aspx 7 # Load assemblies 8 [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 9 [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") 10 11 # http://www.dbi-services.com/index.php/blog/entry/sql-server-2012-configuring-your-tcp-port-via-powershell 12 # Set the port 13 $smo = 'Microsoft.SqlServer.Management.Smo.' 14 $wmi = new-object ($smo + 'Wmi.ManagedComputer') 15 $uri = "ManagedComputer[@Name='WIN-2008R2-STD']/ ServerInstance[@Name='SQLEXPRESS']/ServerProtocol[@Name='Tcp']" 16 $Tcp = $wmi.GetSmoObject($uri) 17 $wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties.Value="1433" 18 $Tcp.alter() 19 20 echo "DONE!" 21 22 echo "Restarting service..." 23 # Restart service so that configurations are applied 24 restart-service -f "SQL Server (SQLEXPRESS)" 25 echo "DONE!"
That is handled by Vagrant itself and is a matter of adding the following line
1 VAGRANTFILE_API_VERSION = "2" 2 Vagrant.configure(VAGRANTFILE_API_VERSION) do |config| 3 # ... other settings go here ... 4 config.vm.network "private_network", ip: "192.168.50.4" 5 end
192.168.50.4 ip collides with another machine on your network, you can
change it to another IP on the private adress space.
Last but not least, we allow remote desktop connections with the following PowerShell script:
1 # http://stackoverflow.com/a/9949105 2 $ErrorActionPreference = "Stop" 3 4 # http://networkerslog.blogspot.com.br/2013/09/how-to-enable-remote-desktop-remotely.html 5 set-ItemProperty -Path 'HKLM:\System\CurrentControlSet\Control\Terminal Server'-name "fDenyTSConnections" -Value 0
Given that you have the Remote Desktop client installed on your host (
apt-get install rdesktop
on Ubuntu) and that all went well with the Vagrant provisioning process, you can
vagrant rdp, log in using
vagrant as the username and password and fire up
the SQL Server Management Studio. From there you can create databases, tables
and even connect to remote servers if you need.
Connecting to the SQL Server from an Ubuntu / Mac OS host requires us to install
freetds. If you are on Ubuntu just
apt-get install freetds-*
and if you are on a Mac
brew install freetds
On the GitHub project I created, you'll find a Sinatra app that you can use to test the connection with the SQL Server host.
Please note that the ActiveRecord DB adapter can't handle the
task when using MSSQL databases, so you'll need to manually create that using
a Remote Desktop connection and the SQL Server Management Studio.
After spending so much time configuring Linux machines over the past couple years it was a fun experience to learn some Windows "magic". I hope you enjoyed reading this post and that it can save you some time on the future in case you need to interact with SQL Server from a Linux / Mac machine like us