Installing sqlcmd without SQL Server

I just ran into a need for sqlcmd without SQL Server. I’m on Windows Server 2016 Datacenter.

I installed Microsoft® ODBC Driver 11 for SQL Server – Windows, with filename  msodbcsql.msi

Installed from

This is a dependency for Microsoft Command Line Utilities 11 for SQL Server, with filename MsSqlCmdLnUtils.msi

 I installed from

I chose the 64-bit versions of both.

Restarted my console session and able to run sqlcmd without SQL Server!

Set up an SMTP Replay (or smarthost) in Windows 2012 R2 with PowerShell

I just set up for our servers that need to send mail, we have a smart host relay to sendgrid.

Part of the setup is done via PowerShell, part through the GUI. If you can direct me on how to configure the additional steps via PowerShell I would be keenly interested and would update this site.

Start by setting up the SMTP Server, which is managed by the IIS 6 Admin console, with these commands from an elevated PowerShell prompt:

Import-Module ServerManager 
Add-WindowsFeature SMTP-Server,Web-Mgmt-Console,WEB-WMI

Then follow the steps 2-8 on this SpiceWorks Post

I would test from every server that must send email. Here’s the PowerShell way to send a test email, using splatting by building $params as a hash table for the parameters then passing in @params. Note that values can be single quoted if they’re not expanding, meaning they’re not containing any variables.

$MyDomain = ''
$MyEmailPrefix = 'dcobb'
$SMTPRelayServerName = 'SMTPSERVER1'

$params = @{
From = "testing@$MyDomain"
Subject = 'Testing SMTP relay from PowerShell'
To = "$MyEmailPrefix@$MyDomain"
SmtpServer = $SMTPRelayServerName
Verbose = $true
Send-MailMessage @params

I want to do the whole process via PowerShell, and hope to investigate further.


Install SQL Express 2014 or 2016 from PowerShell

This may be useful for some folks out there. While the documentation on SQL Books online for command line installs is extensive, the examples there are limited, and the troubleshooting steps are not described clearly.

You will need to download the SQL installer iso or files and mount or extract them, and locate the setup.exe.

For SQL 2014 Take note that:

  1. This is installing SQL Express, a /FEATURES parameter was not required. Changes will be required if using a different distribution.
  2. This creates a default instance of SQL, change MSSQLSERVER to your desired instance name.
  3. Local Windows administrators will have SQL access by default
  4. The line of PowerShell code below that defines $params has no carriage returns between the single quotes.
  5. The results of a successful or failed install can be found in the folder: “$env:ProgramFiles\Microsoft SQL Server\###\Setup Bootstrap”, where ### is 120 for SQL 2014, 130 for SQL 2016.
#Set-Location to where your SQL Setup.exe is located, then run:
$params = @'
Start-process .\setup.exe $params -wait

For SQL 2016, I’m experimenting with SQL 2016 Enterprise Evaluation, and this worked as well:

#Set-Location to where your SQL Setup.exe is located, then run:
$params = @'
Start-process .\setup.exe $params -wait

Presenting PowerShell for the DBA, Part 2 – Objects and Tools – for the PASS DBA Virtual Chapter

Wednesday March 8, 2017 will be presenting online  for the PASS DBA Virtual Chapter.

The topic is PowerShell for the DBA, Part 2 – Objects and Tools

If you’d like to join us, here is the attendee link!

Here’s the link to part 1 of the talk, including videodownloadable scripts, and resources to learn more.

Link to part 2 content on the PASS DBA Virtual Group – Meeting Archive page.

Link to the event page, with video and zip of demo scripts.

Hope to see you there!

Web Deploy 3.6 installed from Web Platform Installer – Features missing

Having issues setting up Web Deploy 3.8 on IIS 7.5 for 2008R2? I feel your pain.

I was installing Web Deploy 3.6 via the Web Platform Installer (WPI) onto IIS 7 on Server 2008 R2, all seemingly goes smoothly, but the interface options in IIS manager don’t show up.

After much investigation, I go to Programs and Features, and see Web Deploy 3.6, right-click and choose change, and here’s what I see:

Web Deploy 3.6 features

Turns out WPI installs it without all the features you’d actually need to deploy software from Visual Studio! The deployment handler is not installed by default. I added these features and completed install. After reboot, still can’t deploy, get an error.

Other issues with Wb Deploy 3.6 forced me to uninstall the version installed from WebPlatform Installer and install the latest Web Deploy version directly from Microsoft site, then I was able to deploy.

More info on Web Deploy 3.6 issues on Stack Overflow

Hope this helps someone else!

Presenting online for PASS DBA Virtual Chapter

I’ll be presenting on POWERSHELL FOR THE DBA  to the PASS DBA Virtual Chapter

Sept 28, 2016, Noon Eastern Time

Abstract: This webinar introduces the SQL DBA to PowerShell and how it can be used to automate many common SQL Server tasks and monitor SQL Server metrics. You will be shown the basics of PowerShell and see examples of using it to automate and monitor SQL Server more efficiently, utilizing freely available SQL PowerShell modules, including the newly available SQLServer PowerShell module released with SQL Server Management Studio 2016, and other community SQL modules.

Registration link is here:

Will share my powershell demo scripts and recommended PowerShell resources on my PowerShell and SQL presentation page.

My first book review! SQL Server 2014 with PowerShell v5 Cookbook

Has been a very busy year for me, haven’t posted lately, but was very excited to share this.

I’ve had the opportunity to be a technical reviewer for three PowerShell books this year!

One that I’ve really enjoyed reviewing has just been released, and the publisher Packt is offering an end of year sale for $5 eBook editions.

Donabel Santos ( & @sqlbelle) is a wonderful blogger, author, trainer, and Microsoft MVP. This is her second PowerShell cookbook for SQL, and it’s an incredibly useful resource for learning the practical details of PowerShell, both in general and when working with SQL Server. All her examples come with code samples so you can leverage her scripts to get productive right away.

You can check out the book on the Packt website. It’s only $5 for a great PowerShell & SQL eBook, or $30 for the eBook and print version. It’s a phenomenal resource, and a great deal for DBAs looking to take the plunge into PowerShell, and DBAs familiar with PowerShell ready to expand their knowledge about all that’s possible with PowerShell in SQL.

Please let me know if you found her book useful, and have a Merry Christmas, Happy New Year, and enjoyable holiday season. 🙂



SQL 2016 WILL have HTML5 reports!

Very exciting news for Reporting Services users, who may have felt left out regarding all the new SQL features of late.

SQL 2016 will have a new rendering engine that will generate HTML5 reports!  You can provide feedback to Microsoft right from within SSRS development, so I imagine they will use that feedback to produce a lot of hotfixes and workarounds over the first few months after release.You can always switch back to the original rendering engine in a report if HTML5 doesn’t work out for you.

I’ll be interested to investigate how reports can be CSS-styled for functionality, for mobile presentation, and to coordinate with the  look and feel of the site in which they are presented.

More info on books online:

SQL 2016 CTP2 is out, with Technet Virtual Labs ready to learn!

Microsoft just released SQL 2016 CTP 2. CTP is a customer technology preview, that lets us explore new features of the product before it’s released to marketing (RTM).

I found it in my MSDN new downloads.

Not ready to download and install it but still want to learn?

Technet Virtual Labs already has SIX virtual labs you can use to explore the new features!

SQL Server 2016 Virtual Labs!
SQL Server 2016 Virtual Labs!

Just search for SQL 2016