Voice of the DBA

Category: Editorial

  • IoT Pros and Cons

    The Internet of Things is coming, or maybe it’s a wave that’s growing underneath us right now. It certainly hasn’t crested if that’s the case, and I expect that we’ll see more and more devices, sensors, and applications coming in the next decade. We’ll get larger and smaller devices, commercial and industrial tools, some amazing innovations and some silly ones that will make most people roll their eyes.

    I ran across an article with some pros and cons from the general perspective. Certainly security is a huge concern, and one that isn’t well addressed by many organizations. In fact, many of the demos of the Azure IoT hub don’t do a great job of showcasing security. I think the Azure IoT Hub is a great idea, with some well thought out security measures, but developers need to build in habits early, and certainly include them in their applications. Let’s leave aside security for a moment and stipulate that it is, and likely always will, be a concern and issue.

    What about the data perspective of IoT? The age of new sensors and devices mean a glut of data, perhaps an overload. We may get a scale of data that we don’t expect and need to plan for more TB or PB sized databases. Those are serious challenges for any organization, and likely we’ll need archival plans to remove old data, or at least ensure we don’t have to scan it for queries. We might even need some sort of governors that prevent the errant “select * from table” queries that try to query 100 billion rows.

    We also may see crazy rates of data acquisition as well. One of the strengths of some NoSQL platforms is that they handle quick streaming sets quicker than relational engines. In fact, in some domains we might not even want to bother storing all the data we can and may need to process this as we get the data, only storing certain samples or ranges. I worked with a stock market application once and the streaming of prices was enough to overwhelm our SQL Server until we learned to limit the data we actually needed to store in the system.

    There’s also the chance we’ll get new, or different types of data that we aren’t sure of the value. Data that might not seem like there is an obvious use for it, but we’re just gathering the bits because a sensor or device captures them. This is where I see more intelligent analysis, more of the “data science” being used. Most of us won’t know how to create these queries, but we certainly will know how to implement and run them, which is a much simpler process. This also means we might need to learn more about to partition, archive, or otherwise move some data of OLTP type, transactional systems to help manage our loads.

    I think IoT is somewhat scary, but also very exciting. New types of data, new applications, new uses for data, which will make our jobs very interesting in the coming decade.

    Steve Jones

  • The Secret Password

    As I work with more server systems that help developers run Continuous Integration and automated releases, one of the things I see used often are variables. These are values you can set for a particular process and reference inside of that process. Great for setting server names, paths, etc. In releases, these are great for specifying specific values that change for each environment, such as the instance name or IP address.

    As with many developer based systems, security is not always set tightly on these systems and any developer can access the build server to kick off builds, reconfigure a process, etc. That makes sense in a CI process, but not so much in a release system. This is one reason I do recommend a separate release server from the CI server. You can use Jenkins or TeamCity to perform releases, but is it a good idea? Have you thought through the security?

    In the release servers, one thing that most systems allow the admin to do is use a variable for a password and mark it secret, so the value cannot be recovered. In this way, if some developer is working on the release process, they can’t get the password to the production server. They can only click the buttons that deploy to that server.

    However.

    They can deploy to that server, and they don’t need the password. If I were to execute a script in the release system that executes the “CREATE USER” and a “ALTER ROLE ” commands to give me access to data, does it matter if I know the deployment password is “G4da%$2h#5f” or $(ProdPwd)? It doesn’t. The actual value isn’t relevant; I just need to be able to use it.

    I think release systems are great pieces of software for reducing the risk of your deployments, but I do think the security models need to be carefully designed and easy to configure, especially when it comes to allowing arbitrary code to be submitted and executed by one person. Be sure that developers can’t necessarily deploy code directly to production servers, whether the password is hidden or not. If there is a way to use the value, someone will find it.

    Steve Jones

     

  • More Open Data

    Steve Ballmer retired from Microsoft almost three years ago. It seems like a small eternity, and since his purchase of the NBA Los Angeles Clippers, I haven’t heard much about any ventures with which he’s been involved. Apparently, he’s spent some of his time, and quite a bit of money to build a site that discloses data about revenue and expenditures for the US Government. There’s a piece in the NYT about this project, as well as a shorter Engadget summary of the site, which provide a short look at the project.

    USAFacts.org is the site, and it’s a treasure of data sets. I look at this as a really interesting way to examine data sets that might be more difficult to gather than you’d expect. The Data Act deadline takes effect in May of 2017, which should also provide another way for anyone to look at public data and perform an analysis. I am disappointed the downloads aren’t working yet, but I hope that this will come soon, along with some sourcing information about where the data comes from and how it was gathered.

    Having data sets to analyze is important for any organization. Certainly within our organizations we spend a lot of time producing reports and queries that help various people analyze data. In fact, finding, collating, cleaning, and organizing information can be a taxing proposition in any size organization. Our data sets and sources are so diverse and often inconsistently producing data that it’s amazing at times that our organizations run well. It seems on a regular basis someone wants to rebuild the methodology used to gather and organize information. I am not surprised that I constantly find incorrect calculations in software because the basis we use changes too often.

    The big issue for me is that so many of us are amateurs when it comes to analyzing information. There aren’t many organized classes or a good structure for most data professionals to learn how to analyze data. We learn on the job, we make guesses and assumptions, and overall do a good job. However, data analysis is highly inconsistent from person to person. I’d like to see that change, and as I see more and more people blogging and talking about how they look at a particular data set, I hope more people are thinking about how to analyze information and how the choices we make for calculations, visualizations, and even ordering can affect how the results are interpreted.

    I’m glad Mr. Ballmer has started this project, and I look forward to seeing how people might use this data and other data sets to provide some analysis of the world.

    Steve Jones

  • Starting to Use SQL Server 2017

    We’ll have a new version of SQL Server this year. If you missed the Data Amp keynote, in it Scott Gu announced the name as SQL Server 2017, which I assume means we’ll have installable RTM bits sometime this year. Maybe in June, maybe August, maybe later. In any case, there are some interesting changes coming, though not a ton. We will get a new platform (Linux) and certainly some nice improvements in a few areas, but overall a limited release. Given this will be a year or so after the last one, that’s not surprising.

    This rapid release cycle means that all of us will support more versions of SQL Server. I don’t know many organizations that wholesale upgrade all their servers. The last time I saw that at any size was in SQL Server 2000, and even then it was painful. Since then it seems many organizations will add some instances of new versions, but continue to support old versions. The more people I talk to, the more I think the ten year rule does exist in many companies. I met someone years ago that said their company (Fortune 100) expected any server installed to run for ten years. Period.

    That seems to be the case. Lots of people still have SQL 2005, or at least SQL 2008 instances. A few SQL 2000, and certainly newer versions, but a mix. Keep that in mind, as most of your companies will move forward with new work even as you support old server. This week I wanted to ask, when do you think you’ll have SQL Server 2017 in production.

    Realistically, when will you have a 2017 platform running a live workload. I don’t care how big or small, Windows or Linux, bare metal or in a container. When will you start to use resumable index rebuilds, graph structures, adaptive query processing or more? Will it be in 2017? 2018? For budget or other reasons, later than that?

    I’m at an interesting place. We still run SQL 2008 at SQLServerCentral, and it works fine. The site needs a basic relational system, though certainly some of the T-SQL changes in 2012+ would be welcome and make some code easier to write. The thing is, code is already written and most things work. We’re debating going to 2016, but I wonder if we shouldn’t just aim at 2017, get one more version ahead, and delay the next upgrade for another decade. I’m certainly tempted as most of the work we through at the database is simple relational work that really would run on SQL 2000 if it had to.

    Regardless of when you might upgrade, are you interested in doing so? Anything catch your eye in the new version? Or have you not even bothered to pay attention? All valid answers, and I’m interested in your view of the new platform.

    Steve Jones