Voice of the DBA

Category: Editorial

  • 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

  • Practice Those Scripts

    I’ve written lots of scripts that were deployed to production. I’ve often had another set of eyes look them over, and still, we made mistakes. In fact, a recent Salesforce outage was blamed on a poorly written database script that gave users more rights than they should have gotten. There wasn’t an actual outage caused by the script, but since customers might have been able to see data and change from other customers, Salesforce took its own service down to prevent anyone from doing so.

    I’m a big fan of DevOps, and certainly including the database in a DevOps process to build a better software development flow. Part of that is ensuring that you can deploy by practicing the act multiple times. In a database world, this would mean that we run a script not just on a development server, but on a QA server, on a staging server, on any other environment we can find to practice and test the deployment. At that point, we should be confident of execution on the production system without issues.

    Good in theory, but sometimes you can’t easily test scripts in intermediate environments. I think changing security is a place where it can be hard to actually test things, especially if specific accounts are referenced that might not need or have access in that environment. Certainly some data changes might be easily be tested in intermediate environments, especially when these refer to configuration differences, like email or messaging systems.

    In this case, I suspect the “access changes” were data changes that updated values in certain tables in the Salesforce application. In that case, why wasn’t this tested? A restore of production to a staging environment would allow developers to test their script. It’s not multiple executions on intermediate servers, but it is better than nothing.

    I’m sure many of you have had the need to execute scripts to change data, alter permissions, or something else in production. Could the same thing that happened to Salesforce happen to you? What precautions do you take, or what would you recommend to prevent this type of issue. Let us know today.

    Steve Jones

    Listen to the podcast at Libsyn.

  • Technology Flows Downstream

    A few years ago, I was listening a session on Azure SQL Data Warehouse at an event. At the time, one of the features I found fascinating was the separation of the storage from the compute portions of the service. We could load a lot of data in the system and pay for the storage, but we could spin up and down compute nodes to essentially scale out query processing to match our workloads. Azure Data Lake has something similar, and I was excited.

    I wasn’t excited about ASDW or ASDL because I don’t really use those platforms in my work, and I don’t have customers to talk to about those. I think they are useful, just not for me. I was, however, impressed with the technology, hoping that it would be coming in the next few years to SQL Server in the box and Azure SQL Database.

    It’s coming in SQL Server 2019, or at least, the start of it. We will get compute nodes, managed by Kubernetes, that will query other storage nodes that scale out I/O access. We also get this with Azure SQL Database in the Hyperscale edition, where there will be query servers and cache servers, designed to scale to infinite, or at least multi-petabyte, ranges

    I wasn’t always sure that the investments in Azure would pay off for the database side of things. It felt like Azure Stack was a natural place to help us manage and deploy SQL databases, and that the contained database work started in 2012 would get pushed by the needs of the Azure cloud, evolving and growing in future versions. We haven’t seen those pieces of technology grow, but I do think technology in other parts of the data platform has flowed to the “regular” OLTP database space.

    The separation of compute and storage really begins with the SQL Server 2019 era release, and I suspect that’s just the first step of how our SQL Server database systems will evolve in the future.

    Steve Jones

    Listen to the podcast at Libsyn.