Use Linux SQL Server with R (JDBC)

Over the past few months, I have been working to learn R, a free software environment for statistical computing. Its been gaining popularity over the past few years, and Microsoft just gave it a huge boost by integrating R into their Power BI visualization software and the Windows version of SQL Server 2016. Since a good deal of my work involves connecting to Microsoft SQL Servers and a Linux version of SQL Server is now available, its a good opportunity to show how to connect to a SQL Server installed on Ubuntu from R, using a JDBC connection.

For this tutorial, I am going to assume that you already have R installed. For my purposes,  I am running R on the same Ubuntu machine as the SQL Server. If you need instructions for installing SQL Server on Linux, Microsoft has provided a write-up already. So now let’s get started.

Continue reading

Run SQLServer service on Ubuntu 16.04 on demand

Greetings all! I have been a bit absent from the blog for the past few months due to a variety of external factors, but I am endeavoring to be more regular on here.

This time around you may be aware that Microsoft recently released a Linux version of SQL Server.  Because I was testing something else, I thought this would be useful to install as well as part of that process.  I won’t bother with instructions on how to install it, as Microsoft has provided a decent write-up already. in my case, I run a variety of different applications on my development system so I don’t always want those apps to start when the PC starts up.  I blogged previously on how to start services on demand in Ubuntu 15.04, so I went back to that post to test it out with the new SQL Server.  Happily, my previous instructions work with this new application!

I’ll present them here in modified form for SQL Server after the break…

Continue reading

Create a Pentaho Kettle Repository on SQL Server

As I have stated previously when creating ETL workflows, its useful to store the information in a database repository, rather than as individual files on your workstation. This allows multiple users to have access to the information (why recreate the wheel?),  it allows you to pull it into your jobs quickly and easily, and you can back it up quickly and restore it if necessary. With the community version 7.0 of PENTAHO® DATA INTEGRATION (PDI), I am happy to report that you can finally create a repository for your ETL code on Microsoft SQL Server. Previously, you could setup a repository on MySQL or PostgreSQL with the community edition but there were compatibility problems with the code that Kettle used that didn’t work with SQL Server. After downloading the latest version I was attempting to make a connection to SQL Server, and decided to test setting up a repository again. I am happy to say it works so the remainder of this article will walk through the process of setting up a Pentaho repository on SQL Server 2016 from a Windows 10 machine.

Prerequisites:

  • Download the jTDS open source SQL Server JDBC driver. Extract the ZIP file, and copy the jtds-1.3.1.jar file from your download and save it into the data-integration\lib folder of your Pentaho application. Although Microsoft provides a JDBC driver, it did not work for me.
  • Create an empty database on your Microsoft SQL Server. I created one called “PentahoRepository”
  • Setup a SQL Server user account (not an Active Directory account) on your database server and give the account  DBO (owner) permissions on the database. Using a DDLADMIN level does not work. I created my account and called it “repository”. I also set the default database for this account to the new database.

Now that we have our prerequisites setup, we can start the PDI client.

Continue reading

Microsoft SQL Server on Docker (part 2) – Use AdventureWorks

sql_on_dockerIn part one of this series, I provided some info on Microsoft’s implementation of Sql Server on Docker and provided a method to have your SQL Server databases saved on your Docker host system so that they would remain persistent if the SQL Server container was shutdown. This time around, we’ll look at how to restore Microsoft’s sample database AdventureWorks to your SQL Server container.

DOWNLOAD ADVENTUREWORKS

If you followed my instructions from part 1 of this article you have a SQL Server container up and running with a data folder on your Docker host. Within that folder, you have several subfolders that SQL Server uses. You need to download a copy of AdventureWorks and save it in that folder. There are multiple versions available, as Microsoft provides a new version with every upgrade to SQL Server. The 2014 version should work fine for this purpose (If you download the 2016CTP version the restore command below will not work because that version includes FILESTREAM as part of the backup. An addition MOVE command needs to be supplied to include that in the RESTORE). Go to this website, and download Adventure Works 2014 Full Database Backup.zip. I renamed the file to just AdventureWorks.zip to make it easier to transfer to my Docker host system and used scp to transfer it: Continue reading