Voice of the DBA

Tag: databases

  • The Great and Powerful Internet of Things

    Will we have the IoUT (Internet of Useful Things? As some have said, the IoS (Internet of Sh**e) is really what we have because of the poorly built, poorly secured hardware and software devices. It seems that almost every month I see new devices introduced in the consumer space, most of which aren’t well designed for widespread use, and certainly aren’t very durable. There are, of course, exceptions, including a few I’d like, but for the most part, the rush to market means that many of the IoT devices sold aren’t much better than most of the home built Kickstarter projects. That’s not to say Kickstarter (or Indiegogo or any other site) doesn’t produce good products, just that some aren’t.

    We’ve got an infographic from Website Guide today that you can examine. It’s an interesting item, which is why I published it. There are a lot of great possible applications for IoT style devices. Plenty of businesses and industries are seeing the benefits of using sensors and devices that are (semi-) smart and connected to a network. Using well written applications, companies can come up with new ways of gathering and using information. When these systems are well designed, this can result in lower costs, higher sales (or usage), happier customers, or all three.

    As you scroll down the infographic, you’ll see the dark side of IoT, which is getting more press all the time. Security is the number one concern, as far, far too many devices don’t include the necessary security to protect either the device or the end user. I did watch an IoT demo at Live!360 last year using the Azure IoT hub, and was glad there was some authentication and ability to shut down devices that might be compromised or misbehaving, but far too many IoT systems aren’t well secured.

    That’s disconcerting for me as a database professional. I expect that more and more companies will use IoT in some way. I’d even like to find time for some IoT sensors around the ranch, tracking horses and ensuring they’re all walking around every day. The thing that concerns me is what happens when someone finds ways to hijack or hack a device? Even sensors could be hijacked. What if you’re processing temperature information in dynamic SQL and someone decides to heat and cool the sensor to create certain values? Are you sure your database isn’t vulnerable?

    These concerns leave out the potential for the misuse of any data that’s collected about users, especially if users aren’t aware of what’s being collected and its use. With so many ways of collecting data now, I’m not even sure that anyone has a good handle on all the data collected about them, not to mention how it’s being stored, secured, and used.

    I think there’s incredible potential for some great IoT applications and data in the future. All the forecasts and spending seem to point towards the next 5-10 years being a period where there will be an explosion of the number of types devices built and deployed. Those devices will produce a tremendous amount of data for us to manage in databases. We will have big challenges in storing, indexing, managing streams, and certainly, the pruning of old data.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Purpose of a Database

    A great quote from a blog on machine learning in SQL Server: “…nobody buys a DBMS for the sake of DBMS. People buy it for what it enables you to do”. The post is from Rimma Nehme, who has given a few keynotes at the PASS Summit. While the focus of the post is how you can implement deep neural network learning with R Services in SQL Server, I thought that quote stands out for any database, relational or NoSQL, from Microsoft, another vendor, or open source.

    I think it’s easy to get caught up in the debate over which features are better than others, or which database might perform better for the money spent. Pehaps we want to debate how easy or difficult it can be to build an application with the platform. We can look at the ROI, the ability to easily implement HA, DR, or some particular subsystem that we need. Those are all good questions, and certainly part of the decision to use a particular platform.

    At the end of the day, it doesn’t often matter which database platform you choose. Whether a JSON file, a relational platform like SQL Server, or the Neo4J graph database. The people that will use the database to query information, make decisions, or just store information need the system to work for them. The system needs to do something that helps their organization in some way. Often that’s based on the capabilities of the software that connects with the database, the capabilities and performance of the platform, and certainly the abilities and execution of the staff that work on the system.

    There’s plenty to debate about using SQL Server with the R language. We can make some determination about whether or not there’s value in spending licensing dollars on expensive SQL Server licenses and using those cores for analytics rather than some other, cheaper hardware. Microsoft R Server (another some other service) might be a better choice. Ultimately, the value to the end user is in getting the data processed and returned to them, whether this is through a query, a report or some recommendation from a machine learning algorithms.

    My view is that more complex processing, whether through machine learning or other types of data analysis, are going to be more important for data professionals in the future. As we build new applications, or even seek to keep older ones viable for a long time, we need to keep in mind that the DBMS isn’t the reason we have a project or job. It’s because we can somehow extract information from the DBMS and process it in a way that adds value to an organization. Whether we do this in a database or application is up for discussion and debate for each individual situation, but we need to ensure we are providing value for our customers.

    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.

  • Writing the Correct Query is Important

    There’s a saying in the data world: garbage in, garbage out. We use that when we can’t get good information from our database because the data we’ve stored isn’t as useful as we would like. That’s a problem, and it’s one reason why data professionals want to spend time thinking about the data we need to collect and how to store it. We want to be sure that we’ve at least made an effort to collect useful data that someone will use.

    We sometimes have the data we need, but still struggle to use it effectively. I think this is an area where machine learning and similar technologies may help in the future, but there is a lot of work to be done to allow most of us to take advantage of those tools. In the meantime, many of us make do with basic T-SQL to perform data analysis, generate reports, and provide the answers to questions. When we do so, it’s important that our queries actually work correctly to answer the questions we need.

    I don’t want this to be a political discussion, and I would appreciate that any comments be limited to the technical subject. I ran across a piece about a failure of the US government in determining the status of people being checked for immigration status. The interesting quote in this article was “… officials blamed computer code for the problem.” Leaving aside the implications in this case, the idea that computer code, likely some sort of query code, is not working as expected, querying the correct data, or isn’t being used properly is disturbing.

    I’ve run across quite a few stories like this from various consultants that were called in to help organizations, only to find out the queries that had been used for long periods of time were incorrect. They didn’t filter appropriately, didn’t convert or aggregate data as intended, or didn’t even query the correct data.

    We use databases and queries extensively in today’s world, and the growth is only going to increase. As much as I like the idea of DevOps and more frequent deployments, I also want higher quality for our software. This means that we need to ensure that our queries actually work as intended against databases. Code reviews, independent checks, using known data sets that evolve and include edge cases of data are all ways we can work to ensure we are actually writing the correct queries for our data. Above all, we need to be sure we are using test of some sort, preferably unit tests, that ensure the queries are actually the ones we want.

    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.

  • Looking Back at 2016

    We’re coming to the end of a crazy year. 2016 has seemed to be one of the craziest of my life with world events like Brexit and the US election as well as an astounding number of data breaches. More people who impacted my life passed in 2016 than in any other year I can remember, and I traveled far, far too much this year. Quite a change from the beginning of the year when the Denver Broncos won SuperBowl 50. 2016 has also been a very interesting year in the data world.

    Certainly the release of SQL Server 2016 was exciting for many of us. For the first time since 2012, or really since 2008, I thought this was a true, major release of the platform. I was surprised and pleased by the amount of features added and improvements made to this version. I very much liked to see the inclusion of a number of security features. While some of these need some maturity and work, they do bring us some additional capabilities that I think start to help us implement better data protection for our database systems.

    We’ve also seen a few things I’ve written about for years coming true in the SQL Server world. We have a Linux version in CTP status, due to be released next year. Whether adding a Linux edition is a good idea or not remains to be seen, but I am glad that Microsoft is making an attempt to port SQL Server to other host platforms. With SQL Server 2016 SP1, we also have a common programming surface, allowing almost all of the T-SQL features that were previously only in Enterprise edition to be used in other editions. This means we’re closer to paying for SQL Server based on the scale of data we process. I think this is a good move that makes sense for Microsoft and customers. While some might lament the hardware limits on Standard Edition, I think they are fine. I just wish it wasn’t sure a big jump to move from Standard to Enterprise, or there were an option in between the two.

    The cloud has grown tremendously in 2016, in many areas, but certainly for data. While AWS and Azure grew in size, they also lowered prices for users. It’s not clear how much of this usage is just for database work, but I certainly think that more and more organizations are looking at moving a portion of their data to the cloud. When you can store data cheaply and scale your query computing up and down, this starts to look like a viable option for some workloads. I don’t know that I think most RDBMSes used for on-premise applications make sense in the cloud, but some do, especially when your customer base is distributed and your workload has predictable spikes.

    I think the idea of cloud databases for analytics and warehousing makes more sense. Those are the workloads that require larger hardware for peak workload levels and become expensive for local systems. Getting your data to the cloud is a challenge, but I suspect that data movement, gateways, and other innovative ETL (or ELT) solutions are coming. The Azure SQL Data Warehouse is a very interesting product to me, as is the Azure Data Lake, and I look forward to seeing how people start to use these solutions in the future. Certainly the cloud is going to continue to play an interesting role for data professionals in the future.

    This was an interesting year of hardware for me. The DevOps movement has said that we should treat servers like cattle, not pets. I’ve started to try and do this with hardware as well. I got a new laptop (VAIO Z Canvas) in 2016, and after setting up my old one with Chocolatey, I did the same with the new laptop, becoming productive with my new machine in a few hours. It helps to have various distributed data services like Evernote, Dropbox, and remote Git Repos, but I suspect many of you have similar services inside of your organization. When I rebuilt my desktop this year, I was using it about an hour after I rebooted the new hardware thanks to Chocolatey. This really make me rethink of my individual machines as cattle. Provided I have some good way to remotely keep various data accessible. That brings me to another interesting issue.

    Data breaches were an issue in 2016, as in years past. They seem to be occurring on a regular basis and increasing in size, though it’s hard to determine whether or not the impact to individuals is greater. The Yahoo breaches were incredible in size, with over 1 billion accounts affected. However, other security issues are just as worrisome. The DDOS attack on DYN shut down a number of sites, but what if a more subtle attack managed to change DNS entries. I’d worry that as more of our data is accessible through public networks, the compromise of credentials could lead to more data loss issues.

    However, one of the most common issues with computer security has been shown to be out of date software with vulnerabilities where patches have been available. To me, this means we could thwart a significant number of breaches by keeping software up to date with patches. This brings to mind plenty of other issues, but ensuring our platforms are patched seems to be important. Perhaps ensuring we can patch our application software quickly if there are issues from patching platforms is one way to improve security.

    Perhaps one of the items that I think dramatically changed in 2016 was the growth of data analysis. Whether we look at the call for data scientists, R analysis of data, visualizations with tools like Power BI, big data or something else, it seems like these were important topics in 2016. This might be the first year where I think that business intelligence truly took a leap forward with tools designed to make analysis easier, and lower the bar. This is interesting, and perhaps profitable, for the average data professional to use in their jobs, but all these tools and options for analyzing data don’t necessarily mean that we will better analyze data. I suspect that many of the initiatives started by individuals and organizations will be abandoned in the short term because the experiments aren’t well designed, and the effort to cleanse and prepare the data for some predictive analytics is greater than what most companies want to invest in this project. However, there are more tools and ways for people to begin their journey to better understanding statistical methods and use them for analysis. The data professional of the future (say 10 years from now) will have a better understanding of data analysis techniques as they begin their careers. Just as we expect most data professionals to have some understanding of relational databases and SQL today.

    It’s been a long year, and I, for one, am glad to see it ending. Some great memories and trips, but too much travel for me. I only took 16 work trips (and 5 personal ones), but three of those were over two weeks and I spent about 80 nights in hotels. Hopefully I can substantially lower those numbers in 2017.

    Steve Jones

    The Voice of the DBA Podcast

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