Voice of the DBA

Author: way0utwest

  • Citizen Scientists

    The last few years have seen quite a bit of demand for people that can analyze data. Certainly the Microsoft platform has embraced more ways to import and visualize data with tools like Power BI, more intelligent analysis with SQL Server Machine Learning and Azure Machine Learning. I think this is because there’s been quite a demand for data scientists, and with all the media attention, plenty of IT executives are searching for these people.

    It’s a good time to change to that type of career, if it’s what you want. You certainly should investigate what’s involved if you have any interest. Buck Woody has a Data Science series that can help you. Microsoft has a Professional Program in Data Science, and there are not shortage of R and Python resources that can help you experiment with data analysis in a new way. SQL queries work well, but you might find that having other tools in your toolbox is helpful.

    As much as there is a demand for professionals, Microsoft, Amazon, Google, and others are trying to find ways to reduce the cumbersome nature of the tools so that anyone that understands the science part can do the work. Will this mean the average business analyst be able to leverage tools and platforms to perform complex data analysis? Maybe, but I don’t believe these citizen scientists will remove the need for dedicated professionals. There’s an argument in this piece that they might, so you’ll have to decide what you think.

    The danger is that it becomes so easy to perform some analysis and create a visualization that we will likely have lots of people building reports and drawing conclusions without really understanding how they’ve aggregated or filtered data, perhaps without even understanding the implication of making these changes. I could see all sorts of poor decisions being made because a manager thinks anyone can use a tool to extract information from data, so they let just anyone do so.

    Maybe this is where the data scientist steps in. Help users to refine their analysis, understand the problems when data is put together or taken apart with these tools. Certainly they should ensure that users have good, clean data sets. The last thing we want is another IT bottleneck, but perhaps using highly technical people to review other analysis and ensure no fundamental mistakes are being made by the analysts is a good use of IT skills.

    This is another place where we need to ensure that different groups inside of a company can work together to be more effective for the organization. That’s the DevOps mentality. We get things done, regardless of who does the work or whose responsibility it is on paper.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Don’t Use Code Coverage

    I got asked in a seminar recently how to perform code coverage for T-SQL. There aren’t a lot of options, but there is a project from Ed Elliot called SQLCover that looks to examine how much of your code gets called from a test. There may be other methods, but my question back was why does code coverage matter? There were a few reasons given by various people, but ultimately the original questioner said that their boss wanted a report.

    That’s fine, and it’s a valid reason to try and measure code coverage, but is that an effective use of developer time? I don’t think so, especially as trying to meet some code coverage goal is fraught with all sorts of issues. Apart from various good points on Stack Overflow, no shortage of people have blogged about the problems of relying heavily on a code coverage metric. I won’t repeat all the arguments, but I’ll give a few of my own.

    First, someone pointed out that you want to be sure all your code is tested. I disagree with that, especially in many  long dev cycle applications. There are all sorts of enhancements I’ve seen in many applications that never get used. While you should certainly check them for security issues, if the features aren’t being used, remove them. Since most of you don’t implement tests for security issues, I’m not sure there’s value in telling you to write a unit test for logic or functionality.

    The other issue with code coverage is that anyone other than a developer using this metric will put pressure on the developers to write more tests. Some developers may write good tests, but I wouldn’t be surprised to have tests written that always pass and aren’t checked for correctness. Tests like these are misleading and potentially create more bugs since anyone refactoring code might think their changes haven’t caused an issue if the test passes. Bad tests, IMHO, are worse than no tests.

    I do think that code coverage can be valuable. If a developer is looking to ensure that some part of their code is being checked, especially in long stored procedures, then code coverage can guide them to a place that might need  testing. Since most code needs more than one test, covering more than one case, code coverage doesn’t help. I’d prefer a developer spent more time thinking about how to build better tests (or experimenting) than looking at coverage reports.

    And if you need a code coverage report, try my technique for building one

    Steve Jones

    The Voice of the DBA Podcast

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

  • Using xp_delete_file

    First, this is an undocumented proc, and there’s nothing in BOL on this. Second, it’s a holdover from previous versions, so you’d be better served by using Powershell or some other type of scripting mechanism. This procedure is not supposed to be supported in the future, so I’d remove this from your code. In fact, if you want to just remove these, use Remove-DbaBackup from dbatools.

    If you use this, or want to know what to refactor, Patrick Keisler has a nice post on the proc as does Andy Leonard, and there’s a parameter list on StackOverflow. I dug in recently as a customer was having issues, and I needed to refresh my memory.

    Essentially, there are a few parameters that you use with this procedure, but bear in mind this only deletes SQL Server backup files or report files. You choose this with the first parameter, a 0 for backup files, and a 1 for report files.  The rest of the items are fairly self-explanatory, but keep a few things in mind.

    First, the date is a datetime value. Meaning if you just include a date, this is the beginning of the day (midnight). You can see this here. I’ve got some backup files.

    I then run this code:

    EXEC xp_delete_file 
   0
   , N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup'
   , N'bak'
   , '20170901'

    Now, I see this:

    The file from Sept 1 still exists because it’s at 9:56am, and the parameter is midnight (2017-09-01 00:00:00). Keep that in mind, and use the appropriate values. If I’d run this:

    EXEC xp_delete_file 
   0
   , N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup'
   , N'bak'
   , '20170901 10:00:00am'

    The file from Sept 1 is removed.

    Next, you need to use the extension to decide which files to remove. That means you might need to have separate calls for .bak and .trn (and .dff), or just remove all old files. The choice depends on what your requirements may be.

    Lastly, make sure that if you use separate folders for each database, you set the last parameter.

    Again, I wouldn’t use this command, especially not in a modern system, but if you do use this, make sure it’s working.

  • Simplified Networking with a Wsky USB NIC

    I used to share an office with my wife. We both needed Internet connections, with both desktops needing Ethernet cables. That was fine when my DSL router was in the office, but at some point we got a new provider and the connection to another room, where we put the new router.

    That was an interesting time as I needed to get a range extender when range extenders were rare. Many people used OpenWRT to flash one of a few devices and build a range extender. I almost did that, but discovered a Securefi Almond router that would to the same thing, give me Ethernet locally and extend my wi-fi. That worked for years, but seems to have lost some capability recently. Despite this being connected to a UPS and surge protector, it can’t find some networks and loses its range extender capability about once a week. The speed has also been flaky, seeming to lose some bandwidth at times.

    I’m alone in the office, but still need Ethernet, or so I was thinking. As I priced range extenders, I realized that I really only need one port, and I don’t need more range anymore. So I looked at wireless NICs instead. I found the Wsky Wireless adapter, with lots of capabilities, good reviews, and low cost. I ordered one and when it arrived, I started the install.

    It comes with a mini-CD, but I don’t have a CD drive anymore. I mean, why? So I found the driver on their site and downloaded it. I ran install, rebooted, then plugged in the adapter. It found my network, I entered the password, and inside of 5 minutes of opening the box, I was connected.

    My wife has tried 3 or 4 different USB adapters, with various success. All seem to die in 9-12 months, which isn’t what she wants, but most of them are < US$20, so it’s hard to be too upset. Both of us remember paying $100 for NICs in our careers.

    I’ve had this a week, and it appears to work better than my Almond was working. We’ll see how things work over time, but for now, this cleans up my spectrum of wireless devices (2 other routers) and I have one less thing to plug in.