Art of the DBA Rotating Header Image


FILESTREAM on Server Core

A couple weeks ago, I did a post for T-SQL Tuesday on the Filetable, a cool new SQL 2012 feature. One thing I glossed over a little bit was enabling FILESTREAM for your SQL Server, where I just linked the MSDN article walking you through configuring the service using the Configuration Manager.  This is great as long as you have the GUI to use. However, I’ve said previously that I’m trying to do all my work (demos and blog posts) using SQL 2012 on Server core, which means no GUI. This makes it a leeeeeeeeeeeeeetle trickier and requires some more advanced techniques.

We need to flex our Powershell muscles for this. For me, this still means hitting the Google search to find the command syntax I need, though I understand it a little better. I found this excerpt from Wrox’s Microsoft SQL Server 2008 Administration with Windows PowerShell on Google Books.  The example shows, quite clearly, how to use Powershell to hit the WMI and the appropriate methods to enable FILESTREAM with the right perms and names.

I took the example there and reworked it slightly, mostly to change the namespace so that it will use the correct SQL 2012 interface.  It’s not much, when you get right down to it.  The core purpose is to create the WMI object and then use the EnableFilestream method (which may be hard to do if you don’t know the appropriate namespace to use):

$wmi=Get-WmiObject -namespace "root\Microsoft\SqlServer\ComputerManagement11" -class FILESTREAMSettings | where {$_.InstanceName -eq $instance}

There are two parameters to the EnableFILESTREAM method. The first is access level, with 4 possible values that relate to how much access is granted to the filesystem. The easiest way to understand it is to relate the values to the settings in the GUI:

(Access level 0 is where no boxes are checked, essentially disabling FILESTREAM)

The second parameter is simply the share name, so in this example we would create\\<SERVER NAME>\MSSQLSERVER for our FILESTREAM objects.  Now, note that you still have to run the SQL to configure the SQL Server side of things (see my previous post).  This step just replaces activating the service properties from the Configuration Manager.

The other alternative to this method is to use the remote management tools to connect to the instance and alter the service settings through the GUI. I like this way better, though, because I have more control over it and I don’t need any additional connections or tools.

Edit:  I forgot to mention that this will require you to restart the SQL Service to apply (thanks Hayden Hancock for pointing this out in the comments).  Since we’re Powershelling it:

Get-Service $instance = “MSSQLSERVER” | Restart-Service

Thursday Bonus Series – Core Mechanics, Part Three

So here we are. After installing Server Core and configuring it, we’re ready to install SQL 2012. Fortunately, this is the easy part. We only have two steps and both are things that have been common actions for many years now.

The first step is to install the .Net 4.0 libraries. SQL Server requires these and they’re currently not part of the Windows configuration/setup. We’ll need to head out to MSDN and download the standalone .Net 4.0 installer for Server Core. You’ll need to do this on a separate machine since you won’t have access to a web browser on your core machine itself, but it’s not a large file(~50 MB). Once you have it, copy it over to your core machine and run the executable. The install should take 5-10 minutes and then you’re ready for SQL Server.

Step two is something I’ve done a lot of and strive to make a habit in most of my SQL Server environments: an unattended install. For those of you unfamiliar, this is simply the practice of installing SQL Server from the command line using a configuration file. This is actually how SQL Server is typically installed, you’re just building your configuration file as you click through the GUI. Read up on it on MSDN.

For my install, I’m just going to use the sample configuration.ini file found on MSDN, with only a couple adjustments for my environment and a couple additional flags. I won’t go through the whole file, but here are a couple key options that are used:

  • QUIET=TRUE – You *must* have this set, as any other option requires GUI elements of the install and your install action will fail. Like I said earlier, Server Core hears ya’, Server Core don’t care. We’re flying without visuals here, folks.
  • IAcceptSQLServerLicenseTerms=TRUE – Typical license agreement acceptance, but since we don’t have a GUI with a checkbox, we need to indicate our acceptance in our configuration file.
  • TCPENABLED=1 – By default, SQL Server won’t enable TCP communication. So instead of going into the configuration tools after the fact, we’re going to enable it in our setup with this flag.
  • SECURITYMODE=SQL – I rarely see servers that are Windows authentication only, but SQL Server uses that mode by default. We need to use this option in order to set mixed mode authentication. This also means we MUST use the SAPWD option to declare our SA password.

Once we have this, it’s a simple matter of running setup.exe and declaring the configuration file. With my VM, I’ve mounted the SQL 2012 RC0 image to my E:. From here, it’s one line and off we go. 

Now we just wait for SQL to install, which should take 5-10 minutes depending on your system. Once it’s complete, then we can fire up SQL Server Management Studio and connect to our brand new instance.

After this, it’s just SQL Server folks. Everything we’re used to doing, whether it’s using T-SQL or the Object Explorer, we’ve done before. It’s just now we’re doing it on a box that has a smaller OS footprint, leaving us with a happier place for our SQL Instance to live.

With our install complete, I want to leave you with a couple extra tips. Keep in mind that while the server itself has no GUI, we can still use MMC snap-ins to connect to the server remotely and manage it. This way, if you need to change firewall rules, add users, or any other management task, you can still use the tools your familiar with. Also, by forcing remote administration, you’re adding an additional level of security to your systems.

While AlwaysOn and many of the business intelligence features are being highly touted, I’m pretty excited about this bit of functionality in SQL 2012’s feature set. There’s a lot of power here for automation, security, and performance (all things I love), so I’m going to be pushing this where ever I can. Hopefully you can benefit from it as well.


Thursday Bonus Series – Core Mechanics, Part 2

Previously on Art of the DBA, I walked you through a basic install and setup of Windows 2008 Server Core. Now that we have our server setup with an OS, a name, and an IP, it’s time to prep it so we can install SQL 2012. Let’s get started, shall we?


Before we get too far, I have a cheat I confess to. A lot of these commands I am going to run are saved in scripts to spare myself from typing commands. As I’ve mentioned previously, I’m lazy and the beauty of automation is having scripts. So the first thing I’ll do is open up the firewall so I can copy my scripts down:

netsh firewall set service fileandprint enable

This command enables file and print sharing. Now, normally this will be off (and I tend to turn it off when I’m done), but for the initial setup I want to be able to get at the machines local drives and put my scripts there.

The second thing to do is to activate Windows. Just because we’re using Server Core doesn’t mean Microsoft doesn’t care about us registering the OS, even if they make it harder. Go ahead and run:

SLMgr.vbs -ipk <<Serial Key>>
SLMgr.vbs –ato

The first command will load your serial key into the machine, the second command will communicate with Microsoft to validate your key and activate your machine.

Great, with all that out of the way, let’s get to the fun stuff.

Configuring your Console

Full disclosure, I’m still pretty new to Powershell. Oh, I’m no stranger to scripting (VB scripts as well as Korn/Bash are in my repertoire) and, as we’ve discussed, I love the command line. But I’m still getting used to the syntax and all the different calls. This means that I want to make sure Powershell is loaded and set as my default shell for my Server Core installation. To do this, I’ll run the following script:

REM Install base packages
REM install .Net
start /w ocsetup NetFx2-ServerCore

REM Install powershell and modules
start /w ocsetup MicrosoftWindowsPowerShell
start /w ocsetup ServerManager-PSH-Cmdlets

The comments explain what’s going on, but let’s touch on a couple of things. First is ocsetup, which is a command line utility to install Windows components. What I am using it here for is to first install my basic .Net libraries, then Powershell and Powershell’s ServerManager commandlets. Once that’s all installed, I restart the machine and then fire up powershell itself (by typing ‘powershell’ and the command prompt) so I can wrap up the rest of the configuration.

Finishing touches

With powershell running, we can start on our final steps. First, I will set my script execution policy so I can run scripts (and save myself some effort):

set-executionpoilicy remotesigned

For more on what this means, here’s a link. With this in place, I can now execute my final steps, using the following script:

#Run from Powershell, configure powershell
import-module ServerManager

#configure powershell as default shell
set-itemproperty "HKLM:\Software\Microsoft\Windows NT\CurrentVersion\WinLogon" shell 'powershell.exe -noexit -command "import-module ServerManager"'

This script will go ahead and import the ServerManager commandlets for the current session (commandlets are not loaded automatically). This makes our life easier for setup. The next command then allows for remote powershell script execution. While I don’t use it for this job, it’s nice to have. Finally, I make a registry key change so that when I log on in the future, it will use Powershell as my default shell.

Finally, we configure our Windows features and firewall:

#Install x64 components
Get-WindowsFeature WoW64-NetFx*,WoW64-Powershell | Add-WindowsFeature

#update firewall
netsh advfirewall set currentprofile settings remotemanagement enable
netsh advfirewall firewall add rule name="SQL Server(1433)" dir=in action=allow protocol=TCP localport=1433

With this, I add all the necessary .Net and Powershell libraries I’ll want for future use. Then I enable the remote management settings in the firewall, along with opening up port 1433 for SQL Server.

And with that, we’re ready to actually install SQL 2012, which I will cover in the next post! Stay tuned!

Thursday Bonus Series: Core Mechanics Part One

I’m not sure how many folks out there have heard of Server Core yet. The quick and dirty is that it’s a version of Windows Server that has most of the GUI elements stripped out. No, Microsoft didn’t develop this version for technological masochists (like me), but wanted to provide administrators with a Server installation that was leaner, meaner, and more secure. By taking out the graphical portions, you get some nice benefits for your environment, including a smaller footprint for your install, tighter security, and reduced security patching (look at how many of those updates fix bugs with IE).

Up until SQL 2012, SQL Server was too dependent on the graphical libraries in Windows to run on Server Core. Now with the next version, we get the support to install SQL Server to Core, something I’m pretty excited about it. I went ahead and created a VM installation of Core with SQL 2012 and then provide some blog posts to walk you through the process so that hopefully you can do the same in your lab environment.

First things first

Before we do anything, we must (of course) get Core installed on a VM. For information on setting up a blank VM, check out my virtualization posts. Then just fire up the machine with your OS install and make the usual selections. Well, usual until you get to the install version:

I’ve gone ahead and selected the 2008 R2 Enterprise version, Server Core installation. There’s also corresponding Core installations for the other versions, so go with what works for you. Once you click ‘Next’ here, the install will happily churn along (go progress bar, go) until it’s ready for you to enter an administrator password. Once that’s set, you’ll boot into the OS and see…

BAM! Command line! “But wait!” you say, “There’s so much to do before I’m ready for this!” Server Core hears ya’, Server Core don’t care. Fear not, though, because with Server Core, we get a utility that allows us to do some of the basic configuration of your machine. Just type ‘SConfig.cmd’ at the prompt and you’ll see:

Ah, much more comfortable, right? The utility is easy to use, just select and option and respond to the prompts. Using this utility, I’ll do the following:

  • Change my computer name to ‘ALBEDO’
  • Update Windows with all current patches
  • Go into Network Settings and set my IP as (Static)
  • Confirm that my date and time settings are correct.
  • Create a local admin account – ‘mfal’ (Because only bad people use the default Administrator)

This is just the start, of course. We still have several things to do to prep our machine for SQL 2012. In the next post, I’ll cover the basic OS setup, including setting up Powershell as our default shell, enabling the firewall to support SQL Server, and making sure we have the correct Windows features installed.


Virtualization at Home

As tech geeks, we love to poke, prod, and play with all sorts of new software. We love to tinker. We love to explore. Most of all we love to be able to do all of this without wrecking our own machines.  Having a sandbox where it’s easy to play and not worry about rolling back changes or uninstalling software is ideal, but that isn’t so easy to do on just one machine and very few of us have 5-10 computers that we can wipe/reinstall at a moments notice. That is, until we start building our own virtual servers at home.

I’m sure most of you are familiar with virtual servers now. I always considered it heavy hitting stuff, enterprise level tech that I really couldn’t use at home. Well, at least up until I discovered VirtualBox. Since then, I have fallen in love with this particular piece of software and I’m trying to use it as much as possible. Walk with me for a bit and I’ll show you the some of what I learned when I created a machine to host SQL Denali CTP 1. Hopefully it helps you enough so you can set up your own virtual host and take your learning to another level.


VirtualBox is available on pretty much any platform and you can download it for Windows, Linux, and Mac OS X. I’ve installed it on my XP laptop and an Ubuntu 9.0 desktop I have at home, finding both installations to be pretty straightforward. For Windows, just download the latest setup executable and fire it off. You’ll walk through the typical series of setup screens and there aren’t really any options to worry about. Ubuntu was even easier, just typing in sudo apt-get install virtualbox-4.0 with my admin password(’cause I’m a su-doer not a su-don’t-er) and off to the races.

Quick Tour

With the installation complete let’s take a quick look at the interface:

VirtualBox Main Screen

The three big buttons there at the top are really what we want to be concerned with:

  • New – Create a new machine
  • Settings – Look at the setup of the selected machine
  • Start – Power on the selected machine.

As you can see, it’s pretty simple. Of course, the devil is in the details, so let me show you how I got a machine ready for Denali.

Creating a new machine

The wizard for creating a new machine is very good, just select the OS type and version that’s appropriate (for Denali, I did Windows 2008 64-bit) and go with all the defaults. Some things to keep in mind:

  • Base Memory – This is only the minimum amount of memory VirtualBox will reserve for the machine. The virtual machine will grab whatever free memory is available on the host, so keep this in mind if you get specific with the memory settings on your SQL Server install on the virtual machine. If running multiple virtual machines, be careful that your total base memory allocations don’t exceed total memory on the box!
  • I typically do dynamically sized storage. Unless you have a space crunch on your box, it should be fine to allow it to grow.

Starting a new machine

Before you actually start the machine, there’s two items you’ll want to look at first. The first thing to consider is the virtual machine’s network settings. VirtualBox offers you four options (found under Settings->Network) which are described in full detail on the manual page.

VirtualBox Network Settings

While in all cases your virtual machines will piggy-back on the host’s network card, the setting you select will determine how it shows itself to the world. I won’t get into the details of each specific setting, but this is the place where you do not want to used the default setting of NAT (Natural Address Translation). Instead, I suggest you go with one of these two settings, depending on what you intend to use the machine for:

  • Bridged Networking – This allows the machine to appear as any other machine on your local network. It can join domains and workgroups, as well as receive an IP address from your network DHCP host. I usually use this setting, since at home my virtual host machine is different than the machine where I sit and do my work.
  • Host-only Adapter – This ones a little trickier to setup, but is useful if you’re running VirtualBox on a laptop that you’re doing demos on. The virtual machine will join a network that is only visible to the host and virtual machines on that host. At this point, VirtualBox itself will act as the DHCP provider, which is setup under File->Preferences->Network.

The other gotcha to address only applies to 64-bit machines. When I created my machine for Denali, I kept getting an error message about “VT-x/AMD-V hardware acceleration has not been enabled”. After some Googling, I discovered that this is a BIOS setting and you’re basically enabling your CPU to handle virtualization. Now it’s called different things for different motherboards, so you’ll need to do a little research on yours, but for my Asus board (M4785-M) I had to enable Secure Virtual Machine Mode under Advanced/CPU Configuration. Note, you’ll only have to enable this once on your hardware.

Now we can start the machine. There’s a wizard to guide you through the first run, which all you’re going to do is tell it where the CD-ROM drive is so it can find your install media. I didn’t do anything fancy here, like mapping it to a shared folder or a network drive, and it just worked for me. On both Windows and Ubuntu, VirtualBox had no issues finding the drive and using it on boot of the new machine.

Installing the OS and software

From this point forward, everything was a normal Windows and SQL install. I used a demo copy of SQL Server 2008 R2 and had no issues with the install. While I didn’t join the virtual machine to a domain (I’m not that fancy at home), it found my local workgroup just fine(since I was using bridged networking).

Virtual Box - Running!

The only other setup piece I had to do was to make sure the the network configuration within the virtual machine was setup properly.  First off, I disabled the Windows Firewall on the virtual machine. I’m sure with some time you could configure it appropriately, but I’m a DBA, not a network administrator.  Then I went into the SQL Server Configuration Manager and made sure Named Pipes and TCP/IP protocols for my instance were enabled.

When you’re using the virtual machine on the host (not RDC-ing into it, which is what I usually do), there’s a couple tricks to keep in mind:

  • The cursor will lock itself into the virtual machine when you click on it. To release the cursor from the virtual machine back to the host, press right CTRL.
  • CTRL+ALT+DEL is not registered within the machine itself due to the VirtualBox application. To send that command to the virtual machine, release the cursor, then in your toolbar go to Machine->Insert CTRL+ALT+DEL.

This should help you get started with virtual machines at home. While it’s not specific to SQL Server, I’ve found this tool to be immensely helpful for building demos at home and testing out new versions of the software. Hopefully this will be just as useful to you. Feel free to contact me, either by email or twitter me with any questions or experiences you’ve had using virtualization at home.