Voice of the DBA

Category: Editorial

  • The Non Profit Cloud

    I ran across someone awhile ago that works with a nonprofit charity. The organization was seeking to move some of their IT infrastructure to the cloud. This person was wondering if Azure had any discounts or programs for charities, and it didn’t appear there was any type of program when I looked. That was disappointing as many charities work hard to help those affected in some way, but budgets and money are always tight.

    I wish I’d know at the time that Microsoft had announced a program that can help. They are donating $1 billion over five years to nonprofits and university researchers. Their goal is to reach 70,000 nonprofits and help them use IT to better serve their causes. This is part of the Microsoft Philanthropy segment of the company and it’s a welcome change to me. I support Microsoft and use their software, but I also am glad to see them looking at more than profits and incorporating some social responsibility into their work.

    I’m sure charities appreciate the assistance, though this isn’t without some costs. Moving applications to the cloud can be expensive in terms of software development, and certainly there are ongoing costs. I’d like to think that the ability to scale resources to meet demand works better in the cloud, and certainly there are some software products Microsoft has that charities will find cheaper in the cloud over time.

    The donations will also help many university projects, which I’m guessing will both gain insights from data analysis in the cloud, and hopefully also help Microsoft (and the rest of us) better understand what techniques for working with data work well and don’t work well in the cloud. I know the Azure SQL Data Warehouse looks interesting to me, especially in terms of building a DW in a way that doesn’t require large on premise investments. I’m hoping to get a chance to take some SSC data and experiment with it there in a limited basis, turning off the systems when I don’t have time to work with them.

    I’m glad to see Microsoft supporting nonprofit organizations, and I hope that I see more GiveCamps and other efforts from software professionals to likewise support organizations that are aligned with their own values.

    Steve Jones

    Listen to the podcast at Libsyn

  • Spread the Word about Basic Security

    I went with my daughter the other day to set up her bank account. She’s now an adult and this is one of those items we need to get done, especially in today’s world of electronic banking and money transfers. It’s time she takes another step towards being responsible and accountable for her finances.

    As we went through the process, one of the steps was her setting up an account with the online presence of this bank. I told her to be sure to use a new password, one not in use anywhere. She agreed, but then said she has 3 or 4 passwords that she uses in different places. I told her that’s not enough, and explained why. There is an interesting article about a MySpace security flaw in 2017.

    Most of you don’t use MySpace, but you might have. Even if you haven’t, your kids or parents might use some popular tool, such as Facebook, Snapchat, Instagram, etc. Tomorrow we might discover a similar security issue, or worse, a disclosure of your passwords. If you think it can’t happen, go try a few emails over at https://haveibeenpwned.com. You might be surprised at the results. My kids were.

    It’s not that your bank is necessarily vulnerable to hacking a password. However, if you’ve used the same (or very similar password like PasswordCNNdotcom) for your news reading at CNN, then if that company is hacked, it’s not too difficult to take all emails  and then try PasswordChasedotcom at Chase National Bank’s site.

    Reusing passwords is a bad idea. I do it for demo accounts across virtual machines, and even that bothers me. Everywhere else, I use a password manager and I have hundreds of passwords, separate ones for each site. My wife is annoyed if she needs a password for any of our accounts and I give her something like “4Gbv8A^f8” on my phone, but I’m not going to make simpler ones or reuse them.

    Separate accounts and separate passwords are a mantra I’ve used for years at work and at home. Please spread the word and get those in your life to use separate, strong passwords for their online activities.

    Steve Jones

    Listen to the podcast at Libsyn.

  • Killing FTS

    In almost every application that I’ve helped build, we had a need to perform textual searches of fields. Sometimes this was in short fields, like names, where we usually needed some sort of wildcard search to let users find information. When we got to larger volumes of text, such as note or description fields, it was essential that the application allow a way to find information that doesn’t exactly match some term.

    SQL Server has had the Full-Text Search (FTS) subsystem for a long time. Built as an improvement over the LIKE keyword, FTS implements its own index system that better understands language and takes some burden off the developer when trying to find keywords and terms inside of a volume of text. However, this system hasn’t advanced much since 2005, with limited improvements in each version, and more stable performance, but not substantially improved. New languages get added, but not development language improvement.

    These days, many of the individuals that need to search text fields used ElasticSearch instead. In previous years, Lucerne was a popular choice, along with other software add-ons. In fact, most people want to use anything other than SQL Server’s FTS. Even at SQLServerCentral, our experimentation with FTS led us to abandon this early on as the work required to structure a UI around the CONTAINS code, along with poor search performance, made the decision easy. Google custom search or another package are far superior.

    I wonder if it’s time for SQL Server to abandon FTS. Maybe their limited development resources would be better spent integrating other third party engines into SQL Server. Or maybe some of their profits are better spent licensing or purchasing a different technology for searching. Certainly they should deprecate the current FTS implementation and allow applications using it to continue to do so, but cease additional work beyond limited maintenance.

    At the very least, they should move in some direction. I’m sure Microsoft could come up with ways to improve FTS, but I don’t know they can do it quickly enough, or that it’s a good use of developer resources. There is other technology that does this very well, so take advantage of it and build hooks that make integration simple. That might be the big search win for SQL Server databases.

    Steve Jones

    Listen to the Podcast at Libsyn.

  • Microsoft, Think DevOps First

    This post from Melissa Coates is a good example of not thinking through your product architecture early on. The short version of the post is that Power Bi Desktop is a better place, perhaps really the only place, to author your Power BI Reports. Melissa covers a few potential issues while using the Power BI online editor, but the big item is that you cannot easily prevent conflicts and track versions there.

    Power BI started as the online service, with the Power BI Desktop tool seeming like a bit of an afterthought. At least from the marketing perspective as all of the early demos and media from Microsoft was about the online report service. Since then it’s evolved to become the primary way we will build reports in SQL Server moving forward, which I think is a good move.

    While I can understand the developers at Microsoft not really thinking that the service would on-premises and maybe not considering the need to provide some text format for the reports, I can’t understand why they didn’t learn from the Integration Services team and realize that binary versions of programmable items don’t make sense. We need a format that can be easily versioned, and maybe more importantly, stored in a VCS and diff’d in a way humans can understand.

    When building a format for storing code, please consider the need to work in a team and version the changes made. This means any format should not be a) binary and b) difficult to decode. Separate visual elements from logical elements and ensure a text version of this can be examined by developers. You can use XML, JSON, YAML, or any other text based format, but choose something that makes sense. Even if you add your own extension, ensure that standard tools that work with code can use this.

    I do know the PBIX format is a ZIP file, but zip files don’t easily integrate with a VCS. We could use hooks to extract/rebuild out files on commit/checkout, but that’s cumbersome and silly. I’d rather that the PBIX were a folder with the files inside. Users, including my Mom, can zip a folder and email that if needed. To me, that would have been a better structure from the beginning.

    Microsoft is supposed to be a company providing platforms that we build upon and use in our work. The decisions for the Power BI service seem to be poorly thought through with that in mind. I’d urge them to create a baseline set of rules for future products that consider DevOps, teams, and the need to track code.

    Steve Jones