Tag Archives: distro

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.

Linux Computer Forensics: Deft Linux 8.0b

Deft Linux 8.0b is out and it's looking great.
Deft Linux 8.0b is out and it’s looking great.

A month or so ago I did a walk-through of some simple computer forensics using Deft 7 Linux (Carve and Sift: My Primer to Linux Computer Forensics). There have been several other versions of this distro to come out since then, but now that the beta for 8.0b has been released publicly, it marks a slight shift in the way Deft handles.

While my previous guide is still valid, there are a few additions that really place this version above its predecessors. Now, I’m not going to go through every change, you can do that by going to their website, but there are some really neat features that I’d like to point out.

New Feel

The first thing that will hit you when you start Deft 8.0b is the new layout. While the base operating system is still Ubuntu (Lubuntu to be precise) the LXDE desktop has been further customized from its 7.x version and now looks and feels like its own OS rather than a 1-off from an Ubuntu derivative. The menu is themed for Deft 8.0 with a little 8-ball and more icons have been added to the bottom panel.

The Desktop is more reserved and better organized.
The Desktop is more reserved and better organized.

[Screenshot of Deft 7]
(Opens in a New Window)

The desktop still has the LXTerminal (a must) and the evidence folder, but gone is the “Install” option. Since this is a beta version it is unclear whether this is gone forever or if it will be back later. 8.0b is certainly installable as the boot menu attests.

Guymanager, a very nice disk managing/imaging tool, has been added as well as the file manager for quick access. You’ll see in my screenshots that there is a “Get Screenshot” icon on the desktop, but that was added by me for this article and is not default.

The menu panel is almost entirely new, with only LxKeyMap being carried over with the standard desktop selector. There is a whole host of new software moved in, some from previous versions of Deft but were housed in the menu (like Autopsy) or on the command line. All-in-all this is a good move, as the most used programs are put front and center and the more specialist and less-used are in the easily navigable menus.

New Software

GuyManager is a welcome addition to Deft 8.
GuyManager is a welcome addition to Deft 8.

Deft 8.0b brings a lot of new software to the distro by default and the latest versions of most of it. This version is 64-bit only, and able to work in up to 256TB of RAM. Previous versions could only “see” 4GB because of the 32-bit limitation.

Again, their post on the update gives a broader view of the changes, but there are a few that I wanted to note in summary:

  • Cyclone is now at 0.2 and appears to be mostly the same as before. I’m assuming the changes are back-end.
  • Sleuthkit 4.0 stable is now included, but the Deft devs say that 4.1 will be on the official 8.0 release. [Website]
  • Guymanager 0.7.1, mentioned before, is a very nice forensics tool/disk mounting utility. [Website]
  • Tor is now available pre-installed with browser. I’ve not much use for this, but it is an increasingly-popular internet-access method. [Website]

Skype Xtractor is also new and is probably my favorite addition to Deft 8. While I’m not a criminal investigator, and I’m generally only using the distro for file-recovery, its future utility could be invaluable. Skype Xtractor is a command-line program that extracts the tables from Skype’s main.db and chatsync files and outputs them to html. So far, you can only get it on Deft 8, but it’s so useful I can’t imagine that it won’t show up elsewhere.

New Everything Else

SciTE is a new-ish text editor to Deft 8 and is the sole resident of the new Programming menu.
SciTE is a recently added text editor and is the sole resident of the new Programming menu.

Almost every other piece of software has gotten an update since Deft 7 and some have been given GUI front-ends, which is nice for beginners or those not terribly familiar with Linux command-line. The focus on 64-bit architectures with this version will mean that it probably won’t supplant my use of Deft 7 completely; there are quite a few machines in use out there that are single-core systems.

If you’re familiar with Deft 7, then I’d recommend getting 8 and using it on your 64-bit machines when able, since everything that was in the previous version is in this one (even though it’s beta) and better. Switch back to 7 only if you have to do so. However, if you’re new to computer forensics then I’d recommend sticking to 7 or waiting for the official Deft 8 release which should be very soon.

-CJ Julius

Setting Up a Raspberry Pi with Ubuntu

I had been putting off posting about this project until I had gotten RaspBMC to work, as that was step two, but it looks like the problem I need to be resolved is going to be a little while coming. So, I’m going to come back later and put an update if I get it running correctly. Either way, the Raspbian (the Debian Wheezy Raspberry Pi distro) setup is pretty clear and the same for every model of Raspberry Pi.

Here is the hardware that I’m working with:

  • Raspberry Pi Model B
  • Logitech USB Wireless Mouse Keyboard combo
  • 4GB SDHC Class 10 Memory Card
  • Edimax USB wireless adaptor
  • 4GB USB stick (for extra storage)
  • Gearhead Passive USB hub
  • USB 1.0A power adapter and Micro USB cable
Raspberry Pi Model B with SD card and wireless adapter inserted.
Raspberry Pi Model B with SD card and wireless adapter inserted.

I did this all in Ubuntu 12.04, so my work will be related to that OS; though commands are pretty similar across many distributions. Also, I have an SD card slot in my laptop, which means I did not need an adaptor to access the card directly.

The first step is to get the image on the card. I snapped in the card, it mounted and I went to the disk utility to find out where it had put it (in the system). It was mounted at /dev/mmclbk0. Once I knew that, I was ready to go get the Raspbian OS.

You can get the latest image off of Raspberrypi.org’s downloads page. I’d recommend the straight Raspberry Pi Wheezy image, as the “soft float” one is slow, and the others are more for advanced users that want to do very specific things.

Raspberry Pi booting for the first time
Raspberry Pi booting for the first time

In any case, once I had it downloaded I checked the SHA1 sum, because we’d hate to have a corrupted image from the word go. If you’re unfamiliar with SHA1, then it’s simply a method of verifying file integrity. Quite basically, an algorithm generates a unique number for a file and then that number can be checked against a copy of a file to make sure that it’s in good condition. In terminal, and in the folder that I downloaded the file into you put the command:

sha1sum 2013-02-09-wheezy-raspbian.zip

And you’ll get an output that looks something like the string listed on the downloads page. In my case, I was looking for the following: b4375dc9d140e6e48e0406f96dead3601fac6c81

Then, I just opened the archive and drag/dropped the file into a folder I had created previously, and returned to terminal. We’re going to be using the dd command to copy the extracted image (input file) to the card (output file). We’ll set the byte size to 4M and need be superuser to do this. My command was:

sudo dd bs=4M if=2013-02-09-wheezy-raspbian.img of=/dev/mmcblk0

Raspberry Pi Wheezy default Desktop
Raspberry Pi Wheezy default Desktop

Once it was done, I unmounted my card and slapped it in my Raspberry Pi for boot. On first boot you’ll get a lot of options. I’m not going to go through them one by one, as it’s pretty clear what each one is. The two I want to point you to however, are the expand rootfs and the memory split.

Expand rootfs is necessary if you have, like me, a larger than 2GB SD card. This opens up the rest of your card to be used by the system, so you have more storage space for your OS.

The memory split is important because the Raspberry Pi has a unified memory structure, meaning that it has one unified “bank” of memory that it divides towards certain tasks. If you’re going to be doing processor-heavy tasks like number crunching or multiple cron jobs, then you might want to push this towards the system memory side. However, if you intend to be using a lot of the graphical features, then you might want to lean towards the GPU.

My Raspberry Pi as it I use it now.
My Raspberry Pi as it I use it now.

The system is installed and ready to go. If you hit a command-line on boot, use startx to start the X Windows system (the GUI), and that’s it. I spent a good few hours customizing it, changing the wallpaper and such, but also removing and adding some software from the system to make it more useful to me, but that’s the basic setup.

I’ll come back at a later date if I get RaspBMC working, but as of right now it forgets that I have a mouse and keyboard attached to it, and there isn’t a simple solution that works so far. Everything works in Raspbian, and I’ve got quite a few things that I want to do in that, including Python that I mentioned in a previous post.

-CJ Julius