Pro SQL Server Linux–Connecting and Building an App

As part of my learning goals for 2018, I wanted to work through various books. This is part of my series on Pro SQL Server on Linux from Bob Ward.

I set up my Red Hat server as a command line only instance, so installing VSCode or Azure Data Studio aren’t options. I could add a graphical manager, but I decided not to for now. Instead, I want to use some of the tools from my local Windows machine.

If you’ve tried some networking with VMs recently, you’ll realize that this isn’t always simple. First, you need to figure out where your VM is. On Linux, ifconfig will do this.

2019-01-16 18_30_32-RHEL74 Bob - VMware Workstation

However, when I go to SSMS and try to connect to 192.168.1.43, I get the network error that the SQL Server isn’t found. I know this is because of the firewall because my SQL Server is running locally on Linux and sqlcmd.exe works.

To open the firewall, on Red Hat you use the firewall-cmd command. This wasn’t in the book, so I had to search for the process. Once I did, this is a simple thing to do.

2019-01-16 18_29_17-RHEL74 Bob - VMware Workstation

Now that this is open, let’s connect from SSMS. I can see that worked.

2019-01-16 18_32_59-SQLQuery3.sql - 192.168.1.43.master (sa (52)) - Microsoft SQL Server Management

My first step is to create a database. The standard CREATE DATABASE works, but I can see when I query metadata that this isn’t Windows anymore.

2019-01-16 18_35_22-SQLQuery3.sql - 192.168.1.43.WideWorldImporters (sa (52))_ - Microsoft SQL Serve

From here I used scripts to create the objects in the sample database. That’s a first step to getting an application working.  To get the scripts, I used:

git clone https://github.com/Apress/pro-sql-server-on-linux.git

This is the repo where Bob has his scripts. If you don’t know how to use git, start learning. I’ve got some posts, but there are lots of tutorials out there.

Once the database is created, with some tables and data, I can now work on an application. Bob is using a node app, which I’ve never written, so this is a chance to learn a little something new.

I tried the npm installs, but had issues. I kept getting errors from installing tedious. I had to change to use http: instead of https, and I was able to instally async, but I kept getting errors.  I know this because:

2019-01-24 17_40_59-cmd (Admin)

Let’s try something else. I want to just connect, to see if my system is working. I could use SQLCMD, but let’s be creative.

2019-01-24 17_46_48-cmd - python (Admin)

I’m more comfortable in Python and this works. SQL Server 2019 here.

There is a good tutorial with some sample code at https://www.microsoft.com/en-us/sql-server/developer-get-started/python/windows/. I took some of the code from there to get this working.

In chapter 4, Bob shows how to use T-SQL and an application to read and write data. This is essentially sending SQL batches to the server, like I did with SELECT @@version above. I can do this stuff, and I know how to send batches. Executing code, including stored procedures, isn’t something I want to do here.

A number of the parts of chapters 3, 4, 5, 6, 7 are normal stuff that really are a part of working with any SQL Server version. There was little I saw in here that I hadn’t learned as a part of my work on Windows with SQL Server 2012/2014/2016/2017. Not that it’s not good content, but it’s more basic.

I read it anyway to get an idea of how Bob presents these things and there were good reminders of how things like Temporal tables or how bcp work. It’s worth reading if you aren’t familiar with the various concepts of working with SQL Server, getting performance information, and what tools are available. It certainly was a nice refresher for me.

From here, it’s moving on to more features, hoping that I see more Linux specific things.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.