A Fine Slice Of SQL Server
Richie Lee explains how to get the SQL Tool Service running on Linux:
So, to briefly sum up, to use SMO on Linux, you need to do the following:
- Install .NET Core 2.0
- Install PowerShell beta 2
- Install SQL Tool Service
You can use PowerShell from the Terminal, but I prefer something like an IDE so this is optional:
Download Visual Studio Code
Install PowerShell plugin
Change settings file to point explicitly to PowerShell beta 2.
Read the whole thing.
Comments closedSteve Jones shows how to install Bash on Windows 10:
With SQL Server coming on Linux, some people will want to learn a bit of Linux. Or perhaps they need to get re-acquainted with the OS, which is my situation. I grew up on DOS, but moved to Unix in university. I’ve dabbled in Linux over the years, but with no real use for it over Windows, I abandoned it a decade ago.
Now, I’m trying to re-learn some things as I play with SQL Server on Linux.
Recently I saw a quick video from Scott Hanselman on the Bash subsystem in Windows. I actually first saw this live at the Build 2016 announcement, but when it was added in Beta to Windows 10, I didn’t add it. I’ve been meaning to, but hadn’t.
Until today.
Read on to see how to set this up on your Windows 10 machine.
Comments closedAndrea Lam announces the a new version of the JDBC Driver for SQL Server:
Performance improvements for Prepared Statements
Improved performance for Prepared Statements through caching (including prepared statement handle re-use). This behavior can be tuned using new properties to fit your application’s needs.Azure Active Directory (AAD) support for Linux
Connect your Linux applications to Azure SQL Database using AAD authentication via username/password and access token methods.Federal Information Processing Standard (FIPS) enabled Java virtual machines
The JDBC Driver can now be used on Java virtual machines (JVMs) that run in FIPS 140 compliance mode to meet federal standards and compliance.
Click through for more information, including a couple interesting features like additional timeouts you can set.
Comments closedSteve Jones shows how to use the OPENROWSET command to bulk load data into SQL Server on Linux:
I wanted to import the million song dataset in SQL Server on Linux. There’s a github repo that has the SQL to allow you to use this with the graph database features. However, it’s built for Windows.
Linux is a slightly different beast. Once I started down this path, I had memories of working on SunOS in college, messing with permissions and moving files.
I run Ubuntu in VMWare, so I first downloaded the files to my Documents folder. That’s pretty easy. However, once there, the mssql user can’t read them. Rather than mess with permissions for my home, I decided to move these to a location where the mssql user could read them.
Much of the post is about file permissions. This is because SQL on Linux is SQL on Windows, and that’s a glorious thing.
Comments closedSomething isn’t right…as DBAs we think of things in rows and columns. So we’re going to count across the top and think the 7th column is going to yield the 7th column and it’s data for each row, right? Well, it will but data processed by awk is whitespace delimited by default and is processed row by row. So the 7th column in the second line isn’t the same as the output in the first line. This can be really frustrating if your row data has spaces in it…like you know…dates.So let’s fix that…the output from the DMVs via dbfs is tab delimited. We can define our delimiter for awk with -F which will allow for whitespaces in our data. Breaking the data only on the tabs. Let’s hope there isn’t any tabs in our data!
I’m a little surprised that these metrics don’t end up in /proc, but I imagine there’s a reason for that.
Comments closedAnthony Nocentino shows how to enter Powershell sessions using OpenSSH-basted remoting:
Nothing special here, simple syntax, but the seasoned PowerShell remoting pro will notice that we’re using a new parameter here -HostName. Normally on Windows PowerShell you have the -ComputerName parameter. Now, I don’t know exactly why this is different, but perhaps the PowerShell team needed a way to differentiate between OpenSSH and WinRM based remoting. Further, Enter-PSSession now has a new parameter -SSHTransport which at the moment doesn’t seem to do much since remoting cmdlets currently use OpenSSH by default. But if you read the code comments here, it looks like WinRM will be the default and we can use this switch parameter to specify SSH as the transport.
Once we execute this command, you’ll have a command prompt to the system that passed as a parameter to -HostName. The prompt below indicates you’re on a remote system by putting the server name you’re connected to in square brackets then your normal PowerShell prompt. That’s it, you now have a remote shell. Time to get some work done on that server, eh? Want to get out of the session, just type exit.
It’s interesting to see how well Microsoft is integrating Linux support into Powershell (and vice versa, but that’s a different post).
Comments closedIn my case, I got various systems setup: Windows and Ubuntu 16.04. So, I make sure I download correct *zip or *tar.gz file
As, pre-requisite, you will needed to have already installed *”.NET Core 2.0 Preview 1” for the SQL Service Tools to work and remember this need to be installed in all systems.
Just in case, here’s the link to download “.NET Core 2.0 Preview 1“: https://www.microsoft.com/net/core/preview#windowscmd
https://www.microsoft.com/net/core/preview#linuxubuntuNow, because we are working with PowerShell Core, don’t forget to install the latest build found at:
https://github.com/PowerShell/PowerShell/releases
Read the whole thing.
Comments closedDavid Alcock has a couple posts covering installation of SQL Server on a brand new Ubuntu VM. First, David installs Ubuntu:
The system requirements for running SQL Server on Ubuntu 16.04.2 contains the followingNote
You need at least 3.25GB of memory to run SQL Server on Linux. For other system requirements, see System requirements for SQL Server on Linux.On the create VM window the Memory is currently set to 1024 MB so by clicking the Customize Hardware button I can change the allocated memory to 4GB (4096 MB) as in the screenshot below:
Then, he explains the process of installing SQL Server:
Let’s break it down a little bit. First sudo, which is giving root permissions to a particular command this is as opposed to sudo su which I had to do later on in the install to switch to superuser mode for the session.
Next is apt. Apt is a command line tool which works with the Advanced Packaging Tool and enables to perform installs, updates and removals of software packages. In this case we’re installing curl so we use the install command.
I think Microsoft did a good job of simplifying the installation process on Linux and making it “Linux-y,” with an easy installation and then post-installation configuration.
Comments closedDavid Crook shows how to use Jupyter notebooks inside Kubernetes:
We start with a 16.04 image, we run some upgrades, install python, upgrade pip, install our requirements and expose port 8888 (jupyter’s default port).
Here is our requirements.txt file
numpypandasscipyjupyterazure_commonazure-storagescikit-learnnltkplotlyNotice how Jupyter is in there, I also added a few other things that I very commonly use including numpy, pandas, plotly, scikit-learn and some azure stuff.
The big benefit to doing this is that your installation of Jupyter can exist independently from your notebooks, so if you accidentally mess up Jupyter, you kill and reload from the image in a couple commands.
Comments closedYou’ll want to learn the syntax for one of the console-based text editors. My personal favorite is ‘vi‘. It’s quick, streamlined, but does have a significant learning curve. Emacs is another editor that works great. Many others are out there, and your options open even more if you’re using a GUI. You’ll need an editor to edit configuration files.
The folder structure of Linux is one of the biggest changes. Whereas Windows is based off of an arbitrary drive-letter assignment system that dates back to the DOS era, Linux is is based off of a tree structure. All folders and files are based on a single point, ‘/’ or the root folder, and everything is based off of folders from this point. Certain folders from Windows, such as C:\Windows, C:\Users\username, or %WINDOWSTEMP%, are mapped to certain folders within the Linux operating system.
This is really high-level stuff; if you’re looking at administering a Linux box in a production environment, I’d highly recommend taking the time to learn Linux in detail.
Comments closed