Tag Archives: command line

Top 5 New Features in SQL Server 2017 (that I care about)

New Year, New Database Engine

http://icons8.com/
This is either a database or a stack of licorice pancakes.

It’s finally here! A few days ago as of this writing, SQL Server 2017 was released for Windows, Ubuntu, RedHat and Docker. There are a lot of new things in SQL Server 2017 from Python support in Machine Learning* to better CLR security. But I thought I’d narrow down the list to changes that I’m most interested in.

1. sudo Your Way To A Better SQL Server

SQL SERVER IS ON LINUX NOW! No surprise this is the first thing on my list as I keep going on and on about it. But it’s really here!

Installation is a cinch, especially if you’re a Linux or Unix person. Curl the GPG keys, add the repository, apt-get (if you’re on a real Distro) the installer and run the setup. It’s really that easy.

If you want it even easier, then check out Docker. Slap it in and go!

All the main features are there, the engine (of course), agent, Full-text search, DB Mail, AD authentication, SQL command-line tools etc. Pretty much everything you need to get going on Linux even if you’re in a Windows-dominated environment.

2. It’s Like A Car That Repairs Itself

So this is a big one. With automatic tuning on, SQL Server can detect performance problems, recommend solutions and automatically fix (some) problems. There are two flavors of this, one in SQL Server 2017 and one in Azure; I’ll be talking about the one in SQL 2017 here.

Automatic plan choice correction is the main feature and it checks whether a plan has regressed in performance. If the feature is so enabled, it reverts to the old plan. As you can see below, a new plan was chosen (4) but it didn’t do so well. SQL 2017 reverted to the old plan (3) automatically and got most of the performance back.

Image Source: Microsoft

I’m sure that Microsoft will be expanding this feature in the future and we can expect to see more from this. Azure already has automatic Index tuning in place, so we’ll probably see that in the On-Prem version eventually.

3. Indexes That Start, Stop And Then Start Again

This is a feature I didn’t know I needed. Basically, it allows an online index rebuild that has stopped or failed (say, it ran out of disk space) to be resumed. The index build fails, you fix whatever made it fail, and then resume the rebuild, picking up from where it left off. The rebuild will be in the ‘PAUSED’ state until you’re ready to RESUME or ABORT it.

Code:
-- Start a Resumable index rebuild
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable]
REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=60)

-- PAUSE the rebuild:
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable] PAUSE

/* If you'd like to resume either after a failure or because we paused it. This syntax will also cause the resume to wait 5 minutes and then kill all blockers if there are any. */
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable]
RESUME WITH (MAX_DURATION= 60 MINUTES,
WAIT_AT_LOW_PRIORITY (MAX_DURATION=5, ABORT_AFTER_WAIT=BLOCKERS))

/*Or if you just want to stop the whole thing because you hate unfragmented indexes */
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable] ABORT

This is also great if you want to pause a rebuild because it’s interfering with some process. You can PAUSE the rebuild, wait for the transaction(s) to be done and RESUME. Pretty neat.

4. Gettin’ TRIM
A cute Sheepy.
There hasn’t been a picture in a while and I was afraid you might be getting bored.

This one is kind of minor, but it excited me a lot because it was one of the first things I noticed as a DBA that made me say ‘Why don’t they have a function that does that?’ (note the single quotes). TRIM will trim a string down based on the parameters you provide.

If you provide no parameters it will just cut off all the spaces from both sides. It’s equivalent to writing RTRIM(LTRIM(‘ SomeString ‘))

Code:
SELECT TRIM( '.! ' FROM ' SomeString !!!') AS TrimmedString;

Output:
SomeString

5. Selecting Into The Right Groups

Another small important change. In previous versions of SQL Server, you could not SELECT INTO a specific Filegroup when creating a new table. Now you can, and it uses the familiar ON syntax to do it.

Code:
SELECT [SomeColumn] INTO [dbo].[SomeNewTable] ON [FileGroup] from [dbo].[SomeOriginalTable];

Also, you can now SELECT INTO to import data from Polybase. You know, if you’re into that sort of thing.

Honorable Mentions!

Here are some things that caught my eye, but didn’t really need a whole section explaining them. Still good stuff, though.

Query Store Can Now Wait Like Everybody Else

Query store was a great feature introduced in SQL Server 2016. Now they’ve added the ability to capture wait stats as well. This is going to be useful when trying to correlate badly performing queries and plans with SQL Server’s various waits.

See the sys.query_store_wait_stats system table in your preferred database for the deets. Obviously, you’ll need to turn on Query Store first.

Con The Cat With Strings

Just as minor as TRIM in some people’s books, but this is a great function for CONCAT_WS’ing (it’s not Concatenating, right? That’s a different function) strings with a common separator, ignoring NULLs.

Code:
SELECT CONCAT_WS('-','2017', '09', NULL, '22') AS SomeDate;

Output:
2017-09-22

Get To Know Your Host

sys.dm_os_host_info – This system table returns data for both Windows and Linux. Nothing else, just thought that was neat.

Get Your Model Serviced

Not something I’m jumping for joy about, but it is a good change of pace (I think). No more Service Packs, only Zuul… er… just Cumulative Updates. Check out my article on it if you want to know more about all the Service Model changes.

And Lots Of Other Things

Of course, there’s hundreds other things that are in SQL Server 2017 and related features. I didn’t even touch on the SSIS, SSAS, SSRS, MDS or ML stuff. Check out the shortened list here, broken down by category. Exciting new toys!

-CJ Julius

* Seriously, how do you release a Data Science platform and not include Python? That’s like releasing a motorcycle with only the rear tire. Yes, you can technically use it, but you you’re limiting yourself to a customer base with a very selective skill-set.

How I Got My Android Tablet to Boot Windows 95

I was rummaging through some old software of mine a few weeks ago and taking stock of the old operating systems that I had commercially. I noticed that along with some older versions of Redhat and Ubuntu Server, I owned every version of Windows since 95, including quite a few server versions. I wondered what I could possibly do with them, since I don’t even use my store-bought copy of Windows XP anymore.

Hey, I remember you.
Hey, I remember you.

Then I looked at my new Galaxy Note 10.1 tablet and got an idea. I wondered if I could get Windows 95 to boot on it. So, I fired up Virtual Box and an old machine I had and got to work.

Note: I am using Ubuntu 12.04LTS and a Galaxy Note 10.1 to do this project. Also, I had access to another, older machine with which I could install Windows 95 myself. Your mileage may vary.

Build 95

There are a few ways to go about this. One is to use Virtual Box to create working Windows 95 VDI file and then convert that to an IMG after you’ve got it running and another is to just find a computer with Windows 95 and make an image of the drive. Either way you’ll have to do three things:

  1. Install DOS 5.x or better before installing Windows.
  2. Install Windows 95 and get it working.
  3. Make your image (.IMG) file.
Click to Enlarge
In Virtual Box, you’ll need to set up an MS-DOS environment first and then probably migrate to 95 later.

Now, I’ve tried both ways, and they’re both complex. In the first example, using Virtual Box to create a Windows 95 compatible area for the OS to work in is a pain. This is because the Windows 95 disk is not bootable (and neither is Windows 98 for that matter). You have to have DOS 5.x or later installed first and THEN go to Windows 95. This is as much work today as it was back in when Win95 came out.

Then, once you have Windows 95 running you need to get all the drivers (and you’ll probably have to use an older version of Virtual Box because of compatibility issues), some of them custom-made, install them, and squash bugs as they come up.

When you have everything set up Virtual-Box side, you can convert the VDI to an IMG file to make it usable with the vboxmanage command in termninal:

vboxmanage clonehd Win95.vdi Win95.img --format RAW

This is not the method I recommend, as it is the hardest even with a walk-through, however it may be the easiest for people with limited access to hardware. I had, luckily, a piece of hardware that would run Win95 with minimal effort so I went that route.

First, I put I installed MS-DOS 5.0.7 (available legally and for free here) from some image files to actual real-live 720KB disks. Yes, I still have a few of those. Then I set up my CD-ROM*, no small feat, and began the Windows 95 install.

Once this had been done, I pulled the HDD out of the computer and connected it to an IDE slot in another machine. I then used the dd command to make a raw image file of the newly-added drive. This ended up giving me a large file because I had given a Gig of space to the virtual drive so I’d have lots of space to move around. You could probably get away with only 200 or 300 MB if you wanted to do so. In any case, the command to image the drive was:

dd if=/where/drive/is/mounted/ of=where/you/want/image/ bs=4K

Now I had my Windows 95 image and it was time to get it running on the tablet!

Install 95

There are multiple ways to get Windows to run on your tablet once you have an image you like. I personally went through my version and pulled out all the things I didn’t want so I could create a smaller image. I eventually got the entire thing down to 200MB, but that was with a lot of work. There are also two ways to get the image running on your tablet. There’s the way I did it initially, and then the easy way. I’ll be showing the easy way and then give a brief overview of the more difficult path.

The Easy Way

You’re also going to want to use something like AirDroid, which I’ve reviewed before, to move the files over because chances are you’re going to be doing this a lot. As you make tweaks or move different things back and forth that GUI is going to come in real handy.

Click to Enlarge
After you put in the image location and name, it will need to copy it to the SDLlib’s directory, probably on your internal memory.

Move your image file over to your device and take note of its location. You’ll probably want to write it down or something, make sure you note the CASE of the letters, because that will be very important. Also you’ll need to make sure you have enough space to copy the image over to the working directory of the emulator that we’re going to use here in a minute. So you’ll need at least twice the space of the original IMG file to use it.

Go to the Play Store and find Motioncoding’s Emulator. It looks like an Android with the Windows XP flag colors on it. Download, install and run it.

Once running, go through the menus (using the forward/back buttons, it really couldn’t be more simple) until it asks you to install libSDL and do so. Then select the option under “Import from Library” to Add Custom Images. Name the image whatever you want and put in the path to the image in there. For example, mine is:

/storage/extSdCard/SDL/Win95.img

Select the image from My Images and continue to the end. You should see your OS boot.

The Hard Way

The reason I’m putting the hard way on here is because it gives you a bit more control over your install and, I think at least, runs a bit faster. In any case I’m going to assume that you’re doing it this way because you’re a little more experienced/curious and don’t need me to hold your hand.

Click to Enlarge
Copying over the SDL apk and related software.

Step one is getting a working version of the SDL apk and installing it. You can do a quick Google search for it, but I’m not sure of the legal ramifications (or its copyright) so I’m not putting a direct link here. Keep in mind that you will need to allow “Apps from Unknown Sources” to be installed on your device. This can usually be found in the “Application Settings” area, depending on your version of Android.

Place your Win95 image in the SDL folder with the APK and rename it c.img, and load SDLlib. You may have to do more tweaking at this point as Networking didn’t work out-of-the-box for me. I needed to modify some already existing .bin and .inf files to coax them into doing what I needed to do, and even then it’s a little haphazard. You’ll need to have some method of editing the img file if you can’t get networking going or you’re going to need to re-image the drive every time you want to make a change.

This way you’ll also have access to the BIOS and VGABIOS bin files, if needed, but I didn’t end up touching them.

Android 95

My reasons for doing this were purely academic. I just wanted to see if I could get it to boot and get it usable. After several weeks of poking at it I was, by all of the above methods, able to get 95 and 98 going this way. Windows 98 was just a matter of upgrading 95 and creating a new image file. I can’t think of many reasons to do this other than for the learning experience, though there are lots of pieces of software out there that don’t work so well in modern versions of Windows and maybe you want to take them with you.

Click to Enlarge
Windows 95 successfully running on my Galaxy Note 10.1 with mouse and keyboard support

Also, I was able to get my Logitech keyboard/mouse combo to work through the 30-pin charging port, and while dragging the cursor across the screen and “clicking” by tap was interesting, the keyboard is the way to go. It’s just too cumbersome for daily use otherwise.

So there it is, an Android tablet booting Windows 95/98! You can supposedly do this with Windows 2000 or XP, but I have not tried. If you have let me know, because I’d be interested in how you got native NTFS to work.

*There’s no instruction here because it really depends on your CD-ROM as to how you’d go about this. You’ll have to find one that will work with Win95 and DOS. I had one in the machine already so it was just a matter of setting it up manually through DOS.

-CJ Julius

Using Python 3 on Ubuntu 12.04

Python on Linux
Python on Linux

Recently, I’ve turned my attention to Python, the programming language. I had some work with it in the past, but never really gotten that far. As a hobby, it was time consuming and other things got in the way. Now that I’ve freed up a small chunk of time every week I’ve decided to devote that to working on learning the new Python 3, since 2.x is going away eventually.

I quickly found out that Python 3 is not directly supported on my platform of choice: Ubuntu 12.04 LTS. So, I needed to get this running from scratch, which involves downloading, compiling and making it easy to get to for working in.

Compiling and Installing

If you haven’t done so already, you’ll need to get a C compiler for Ubuntu. In general, it’s good to keep this resident on your machine anyway, since you don’t always know when you’ll need it and it doesn’t take up a whole lot of space.

sudo apt-get install build-essential

Then, we’ll need to get our Python installer from the web. I’m currently pointing towards the 3.3.1 version, but there will always be newer versions on the horizon, so check the download page.

wget http://www.python.org/ftp/python/3.3.1/Python-3.3.1.tar.bz2

This will download and the bzip tarball of the source code from the python website. Then, we need to un-ball it and change to the newly created directory.

tar jxf ./Python-3.3.1.tar.bz2
cd ./Python-3.3.1

Lastly, we’ll configure the source code, tell it where to install and then point our compiler (the first thing we did) at Python and tell it to put it all together.

./configure --prefix=/opt/python3.3
make && sudo make install

And now the basic Python core is ready to go. You can test it by putting the following in the command line.

/opt/python3.3/bin/python3

You should get the following output, or something quite similar:

Python 3.3.1 (default, May 12 2013, 22:10:01)
[GCC 4.6.3] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>

Getting Fancy

A command line-type of person may want to create a symbolic link that will let them have a sort of “python command”. Keep in mind that in the following code, you can substitute the “/bin/python” for anything you want the command to be (ie. “/bin/py” or “/bin/pthn” which will make the command py or pthn respectively).

mkdir ~/bin
ln -s /opt/python3.3/bin/python ~/bin/python

Alternatively, you may want to install a virtual environment for testing or whatnot. To do this and activate it, use this in the command line.

/opt/python3.3/bin/pyvenv ~/py33
source ~/py33/bin/activate

Integrated Development Environments

If you’re anything like me, then coding directly from gedit or the like is cumbersome and not really all that fun. I like options, a GUI and all the bells and whistles, so I went looking for a an IDE.

KomodoEdit install is as simple as downloading it and running the install.sh
KomodoEdit’s install is as simple as downloading it and running the install.sh

Netbeans was the first choice, as I’d used that before for PHP work. Here, I wanted something more dedicated to Python. If you do decide to go this route, make sure that you get the one from the Netbeans website and install it yourself. The version in the Ubuntu Software Center is terribly out of date and judging from the reviews, fatally flawed.

My second choice was KomodoEdit, the stripped down version of the Komodo IDE which I’ve heard some good things about (but never used). You can get it for both x86 and x64 as an AS package from their website.

If you have another IDE that you like better, let me know and I’ll take a look at it. I’m always on the hunt for a better/easier way to code.

-CJ Julius