Voice of the DBA

Author: way0utwest

The Multilingual Programmer

At the recent SQL Konferenz in Germany, the keynote was from Michael Rhys of Microsoft. His talk was on the evolution and design of the U-SQL language. If you haven’t looked at it, U-SQL is what the Azure Data Lake (ADL) uses, and it’s designed to improve your ability to query various data sources in the ADL. If you want to know more, and begin working with U-SQL, we have a stairway you can go through.

Michael opened his talk by looking at the languages he’d learned in his career. He started with APL and moved on from there. He asked if anyone had used APL, and there were few of us. It was my second language at University, and one I didn’t enjoy. The nature of the language was un-intuitive to me, and I was glad I only suffered for a few months. If you’d like to try it, you can tryapl.

I thought this would make a fun discussion, so I wanted to ask: what languages did you learn for programming and in what order?

For me, I started with BASIC, and a little assembler with early systems. I moved to Pascal in high school, trying to develop fun games and computer assisted homework help for myself. In University, I began with LISP, which caused plenty of people to drop out of computing. I’m not sure if that was a good idea or not, but I enjoyed that. From there, I went to APL, Assembler, Fortran and C before switching away from computers for a bit. When I returned, C++ was all the rage, and I soon found jobs that paid me to write FoxPro/Clipper code, then VB, then a touch of Java before the web became popular and I worked in ASP and ASP.NET. Along the way SQL became more and more of my career, and I’m glad it did.

These days I’m trying to improve my C#, PowerShell, and Python skills, more for fun than anything else, but those are sueful as both languages are useful in data work. I haven’t done much with R, but I have fingers crossed that the sp_execute_external_script call that allows a parameter of @language=N’Python’ gets added to SQL Server before I need to learn any R. After all, most of the R libraries exist in Python, and I find the language much more intuitive.

Let us know today what your journey has been, and if you haven’t been a developer, maybe its time to learn some programming skills. After all, I think that’s important for a DBA.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

February 24, 2017
Upgrading SQL Server on Linux
I saw this week that there was a new CTP (v1.3) of SQL Server v.Next. I haven’t had a lot of time to work on the Linux version lately, but I thought I’d try and see how well the upgrade went.

There’s an install and upgrade page at Microsoft you can use, but on Ubuntu, things are easy. First, connect to your system and run this:
```
sudo apt-get update
```
That will download updated packages and get the system ready. you can see that I have a lot of stuff to update on this particular system.

Once this completes, you just run
```
sudo apt-get install mssql-server
```
This will actually perform the install. That takes a minute, and in my case, I walked away, letting this run. When it finished. I tried to connect from a local machine, but got an error. So I ran this:
```
systemctl status mssql-server
```
This should give me the status, which was that things had stopped.

OK, no problem. This starts the service.
```
systemctl start mssql-server
```
Once this completed, I could connect.

I’ve done this a few times over the last year, but not since CTP 1.0, so I reminded myself of the process.

So far, in my testing, most everything I’ve done with the core database engine, all scripts, etc., seem to work. More and more work is being done, and I’m interested to see how this version progresses.

If you like Linux, maybe you want to give this a try.
February 23, 2017
The Cloud is Just a Tool

The cloud is a term that’s full of hype. We hear from various media outlets all the time: the cloud is the answer, the cloud is cheaper, the cloud is the way of the future, the cloud handles your DR, the cloud managers availability, and more. Microsoft has been pushing the message of “cloud-first” (and mobile-first), which has many SQL Server professionals confused, concerned, or even angry. There are also plenty of professionals that dismiss the idea of cloud anything when it comes to data.

I’ve felt similar emotions, and certainly I have been skeptical of the cloud versions of databases. I remember the first cloud service, a key-value store, which seemed woefully inadequate for most purposes. Since they I’ve seen the Azure SQL Database grow, and many other products get released. Across that time, I’ve become more and more impressed with what Microsoft has done, and as Visual Studio Team Services has expended, I’ve come to really embrace and get excited by the cloud. It’s still not something I’d always recommend, but I would always start there.

Mike Walsh wrote a great blog post on the move to the cloud, which I recommend you read. The end message that I get from Mike’s thoughts are that the cloud is a tool, and it can be a tool that really enables you to solve issues without getting caught up in the details of implementing every little part of the system. That’s a mantra that I think many of us embrace, even if we don’t really realize it. How many of you deal with SQL hardware? How many of you install or configure Windows? For many of you, do you even worry about backups or do you have scripts/tools/products that just start backing up new databases? I used to do all those things, but I haven’t even seen a production database server with my own eyes in a decade, despite connecting to many.

We all move at different paces. Some of us still deal with SQL Server 2008, 2005, 2000, or even earlier versions. Some of us will need to manage those platforms for years to come, even as we may end up helping build applications on Azure SQL Database and deal with data integrity, quality, and security issues through a remote connection. I’d like to be even more hands off. Enabling TDE in Azure is clicking a button. I wish it were that simple on premise (whether really here or in an IaaS scenario), because it should be. I should be able to click a button, get prompted to confirm, pick a backup location for my cert backup, maybe give the cert a name, and it should just get completed.

The cloud really is a set of tools and services that take away some of the details and drudgery. Sometimes that’s fantastic, and it enables more rapid, more scalable deployment of resources. Sometimes it’s dangerous because the vendors haven’t really thought through the process completely. I really think that’s where we add value as professionals. We shouldn’t be doing too many tasks that can be more easily automated. We should understand what the automation does, and be able to examine it, but we should be spending our time examining problems and evaluating solutions. We should be using tools, of which the cloud is just one, to ensure our organizations become more productive and more efficient over time.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

February 23, 2017
What’s a DACPAC and a BACPAC?

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

If you’ve worked with SQL Server development and database projects, you might have heard about DACPACs. However, if you haven’t, this was a concept that didn’t seem to catch on with many companies. I’m not a fan of the format, but it works and you should be aware of what a DACPAC is and how it can be used.

The DAC part of the moniker is show for Data-tier Application. This is the container that includes all of the object definitions for the objects that are contained inside of the DACPAC. The PAC part is just an easy way to note this is a contained in a compressed format.

In fact, the .DACPAC is a zip file. If I rename one of them, I can open is like any other zip file. Here’s one I’ve added a .zip to the end of and opened in Windows Explorer. There are a few files in here.

The only really important one is the model.xml, which is a model of my objects. If I look inside, it’s a cumbersome XML format, but I can easily see my Order table as a part of the file.

These are useful files for having a machine read the format and reproduce database objects in a live database. SQLPackage.exe will do this, as will other tools such as a the DacFX (Data-tier Application Framework).

I don’t love the format, but it is machine readable and can allow you to package and deploy database changes. There are limitations, especially between versions, and I think that it’s harder to understand than the formats that SQL Compare (From my company, Redgate Software) uses, but that’s me. I’m biased, but I don’t love DACPACs.

In any case, you can right click and “Unpack” this, or use SSMS to create and read them into a database. In the next post, I’ll show how that works.

What’s a BACPAC?

That’s easy. It’s a DACPAC with the data included.

February 22, 2017