One challenge with SQL Server database automation is that eventually you’re going to have to execute a native TSQL statement. There are some very trendy tools out there, ie. Puppet, Ansible, etc… and it can be a challenge to accomplish everything using their native framework. Have you ever seen a linked server resource type in Puppet? SQL Server is so feature rich that implementing every possible object and setting would take years. So, it often becomes practical to re-use existing TSQL scripts and find a way to execute them from the command line.
I’ve recently gotten more exposure to Ansible and one of the drawbacks is that Ansible is only executable on Linux. So, as the title suggests, how do we install SQLCMD on Linux so that we can expand our capabilities with Ansible?
Oh, I almost forgot, there’s a million distros of Linux. The version I’m using is rhel7. Also worth noting, I’m a total Linux newbie.
Microsoft has decent instructions on installing SQLCMD on linux. My current company blocks access to external yum repositories, so I followed the “Offline” installation. There’s two packages to install:
- msodbcsql – aka Microsoft’s ODBC driver for SQL Server
- mssql-tools – SQLCMD
sudo yum install msodbcsql-184.108.40.206-1.x86_64.rpm sudo yum install mssql-tools-220.127.116.11-1.x86_64.rpm
Microsoft is a little misleading in parts of their instructions, you do have to install the ODBC package prior to SQLCMD.
ODBC will be installed in /opt/microsoft and MSSQL-Tools in /opt/mssql-tools. You’ll find both SQLCMD and BCP in /opt/mssql-tools/bin
I then added the SQLCMD path to my bash profile to save me some typing:
At this point, you should be able to execute SQLCMD. Note: I capitalize SQLCMD but Linux is case sensitive, so executions must use a lowercase sqlcmd.
Executing sqlcmd with no parameters will give you a brief overview of the available parameters. If you’ve used SQLCMD before, you’ll be happy to see that there’s not much difference between Windows and Linux parameters:
My next step here is really to explore Ansible further. In future posts I’ll show how to run TSQL scripts using SQLCMD on Linux via Ansible.