Voice of the DBA

Author: way0utwest

  • Are You Patched?

    It’s been a few weeks since the Spectre/Meltdown bugs were announced for most CPUs. Microsoft has been working hard to build patches, and they’ve provided fixes for Windows and SQL Server. Other manufacturers have released fixes for other platforms, though I wouldn’t be surprised if more patches are coming. We put together a page at SQLServerCentral with information and links, and if you haven’t checked it out, you should.

    If you haven’t patched systems, patch them ASAP.

    This is a bad bug, affecting many CPUs, across multiple architectures, and includes potential issues with virtual machines. The guidance and conversations I’ve heard from various vendors is that many of them aren’t completely sure of all the potential risks or attack vectors, but they are worried that customers will leave this vulnerabilities open in the future. Since this affects hardware, it’s entirely possible that an exploit could read memory from other applications and processes.

    Again, if you haven’t patched systems, patch them.

    There are reports of potential issues, so everyone certainly needs to test systems. Perform a P->V (Physical to virtual conversion) and patch a VM. Make sure the server still runs. If you’re on VMs, snap a copy and patch it as a test. Older processors might see a performance penalty with the patch, but worse performance is better than having a security hole in your CPU available to operating systems.

    This is the type of fundamental architectural bug that’s is very worrisome. The race to be efficient, to copy what works from others, this leads to less innovation not more. I hope that this is a bit of a lesson that we do need separate architectures and approaches to computing problems, both in hardware and software. I love relational databases, but I’m glad that there are other types of systems being used for data storage. I think Windows works really well, but I like competition and think it’s good that we have MacOS, Linux, and more.

    It’s good to have standards and interoperability, but I do think that a heterogeneous environment is good for security, and I hope the world continues to try new architectures as we advance computing ever further.

    Steve Jones

    The Voice of the DBA Podcast

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

  • tSQLt tests for Day 5 Advent of Code 2017

    This is day 5 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1.

    As I worked through the puzzles, I decided that I should be testing using their test sets and solving the issues that way. This lets me use the sample data, but also add in my own sets to cover strange situations.

    Here are the tests that I used for each part.

    Part I

    For part 1, only a short test is needed. Since we’re looking for a scalar value, I could easily just added an INT variable for the actual and expected values. I set this to the value given in the problem.

    Then I fake the table and insert the test set. From here, I can call my proc that implements the algorithm and get the result value.

    EXEC tsqlt.NewTestClass @ClassName = N'tDay5';
 GO
 CREATE OR ALTER PROCEDURE tDay5.[test day5 a initial set]
 AS
 BEGIN

-- Assemble
 DECLARE @actual INT = 0, @expected INT = 5;

EXEC tsqlt.FakeTable @TableName = N'Day5' , @Identity = 1
 INSERT Day5
 VALUES
 (0 ), (3), (0), (1), (-3);

-- Act
 EXEC @actual = SolveDay5a;

-- Assert
 EXEC tsqlt.AssertEquals @Expected = @expected, @Actual = @actual, @Message = N'Failed to account'

END

    Part II

    The test is the same, just calling a different procedure that implements the part II algorithm.

    CREATE OR ALTER PROCEDURE tDay5.[test day5 initial set]
 AS
 BEGIN

-- Assemble
 DECLARE @actual INT = 0, @expected INT = 10;

EXEC tsqlt.FakeTable @TableName = N'Day5' , @Identity = 1
 INSERT Day5
 VALUES
 (0 ), (3), (0), (1), (-3);

-- Act
 EXEC @actual = SolveDay5b;

-- Assert
 EXEC tsqlt.AssertEquals @Expected = @expected, @Actual = @actual, @Message = N'Failed to account'

END

  • Innovation Needs Information

    We are great about sharing knowledge in the technical community. I think the SQL Server community is even better than most technologies as we do a good job of doing so without the rude RTFM that I find in other communities. However, overall we share lots of information with others. I do know that many other communities are catching up and I really appreciate the answers I’ve gotten from others when trying to repair my tractor or auto.

    As the world looks to bring innovative solutions to various problems, it seems that machine learning will play some role. There are some amazing advances taking place in all sorts of areas, and certainly no shortage of organizations that are trying to see if ML enhancements will improve their capabilities. We hear about ML all the time as data professionals, and some of you are starting to try and use these algorithms to build new applications.

    I ran across a great post about innovation that notes that the fuel for ML and related technologies is data. It’s important for training and evaluating systems, and there are companies making a lot of money providing training data. However, there is also lots of data inside companies, especially some of the big social media and communication companies that gives them an advantage. I think that’s OK, after all, these companies have innovated to build large scale enterprises and devoted resources to collecting data.

    However, I do think that more and more data is needed, and companies will work to collect it if they can. That means more and more of us will manage additional data, all of which needs to be cleansed, stored, managed, and protected. More work for us, but also lots of opportunity. Especially if you learn how to process data in new ways.

    Steve Jones

    The Voice of the DBA Podcast

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

  • SQL in the City is coming Feb 28

    The next edition of the SQL in the City broadcast is coming Feb 28, and I’m planning travel now to head back to the UK for the event. The schedule is up on the Redgate Hub Livestream page.

    This edition has lots more GDPR/security/masking content as well as updates on the new products.

    Mark the date on your calendar, let your boss know it’s a training day, and get some friends together to join us for the day.