Voice of the DBA

Category: Editorial

  • Legacy Limits

    We are seeing changes to the SQL Server platform every month in Azure. Since that’s the same codebase used to product SQL Server on premise, that means the enhancements are not only being tested in the cloud, but they are available for release on a regular basis. SQL Server has been on a two year release cycle for major versions, but things are speeding up and I expect a new version (SQL Server 2017?) around a year after the last version (SQL Server 2016) was released.

    My thoughts are that this cadence will mean that many of us with more than a dozen servers will end up supporting more versions of SQL Server in the future. New applications will want newer versions, as do the employees, but there isn’t always a business case to upgrade all the older instances. If we were to see new versions every 18 months, given a 5-7 year life cycle (the standard support time frame) for database servers, I would expect that many of us would be always supporting the last 4-5 versions of SQL Server. If we go to a ten year life cycle (may be more realistic to me), then we would be looking at 6-8 versions.

    If you think ten years is too long, SQL Server 2005 is just over 11 years old. How many of those instances do you support? SQL Server 2008 is almost 9 years old and I bet a few of you have those instances around. Certainly if you have a support agreement and you have applications that use fairly core SQL Server features you can upgrade, but certainly keyword and language behavior changes might limit your flexibility.

    I read a piece recently noting that many organizations still use Windows XP for various systems. A number of the reasons given are to support legacy hardware or software. Some don’t have replacement versions of the hardware/software, or can’t find any value in managing an upgrade. The same issues hold true for SQL Server. I did some work for a company in 2008 that was still running a SQL Server 6.5 instance to support their keycard system. The database was virtualized, worked fine with Internet connectivity, and essentially cost a few hundred dollars a year for consulting fees. An upgrade to newer software, which would support SQL Server 2005+ would have cost over $50,000. Plus support.

    For no new useful features. No wonder they didn’t want to upgrade.

    This week I’m wondering how many of you are tied to older versions of SQL Server because of compatibility issues with software (or hardware). Are there reasons you maintain old database platforms? Any plans to upgrade, or is the cost not worth the benefits?

    I find legacy software and hardware to be a problem with the technology paradigm. While vendors want to move to new versions and reduce their support burden, many people feel that the cost of regular upgrades every few years is too high. Perhaps we could move to renting software, and getting constant development, but developers seem to be loathe to just constantly develop one version of the software for decades, preferring to fundamentally change the architecture at some point. Certainly that may make sense for some customers, but others who don’t need new features might wish for software to just work.

    I don’t have a great solution, but I would like to see options for software like we have with many other products. An example might be autos, where there are companies manufacturing parts that repair or upgrade older models, without the need to purchase a new vehicle. Maybe older software can be licensed in some way to allow independent developers to produce patches, security or otherwise. The vendor could continue to sell new versions, but a licensee could support old ones. I know there are times I’d prefer to have an older version of software work well, and continue to work for the foreseeable future.

    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.

  • The (Former) Complexity of PowerShell

    When I first looked at PowerShell, it was v1.0, and I was in a TechEd presentation. The language seemed a mile past the VBScript I was using when T-SQL didn’t function well. The ability to access the .NET namespace, work with objects, and program with error handling was exciting. I played with the language a little, but didn’t find that many places to use it at the time.

    Certainly file operations were much easier with PowerShell, and I built scripts to copy backup files around the network. AD operations were easy in PoSh. However, when I tried accessing SQL Server, I thought the code was complex. In fact, whether I was running a stored procedure, or performing a restore, the PowerShell code required was cumbersome. A good example is shown at the beginning of Aaron Nelson’s recently updated post on querying with PoSh. The complexity shown to just make a connection to PoSh made me think I should just write that kind of code in C#, with all the debugging and other software support available in Visual Studio. Building quick utilities with PoSh was something I’d like to avoid.

    When I heard that SQL Server Powershell was being updated, I wasn’t too excited. I had visions of there being newer versions of cmdlets, but a similar level of effort to work with SQL Server. However, as I’ve played with the new cmdlets, along with the dbatools module, I see PoSh becoming easier to use than T-SQL in some cases. In fact, I’ve started to keep a command line window open (with ConEmu) and access that to perform lots of quick tasks that aren’t based on simple queries of data. Even a backup with Backup-SqlDatabase seems as simple as it might be in T-SQL, perhaps more so if I don’t have an SSMS connection open.

    I know there can be a debate over whether you need PoSh as a DBA or you can get by with just T-SQL. I don’t want to take a side here, and no matter how you feel, if you can get work done, then you are successful. Your job depends on you being able to reliably write code to perform some task over and over. The language doesn’t matter.

    My view is that I think there are cases where PoSh seems a better fit and places where T-SQL works really well. I want to improve my skills in both so that I can decide what works best in any particular situation, and feel comfortable in building a solution either way.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Delaying Patches is Problematic

    I was listening to a DevOps podcast from Josh Corman, of Rugged Software. Rugged Software aims to improve security by asking developers and sysadmins to adhere to their manifesto, which recognizes both the importance of software in the modern world, as well as the problems associated by not properly securing and patching software. These are duties that both developers and administrators should perform in their respective roles.

    The goal of the Rugged Software is to promote best practices and constant vigilence to create and deploy secure code, and ensure that software installations remain that way. This isn’t a security state, but rather a process. A way to approach software development, deployment, administration, and maintenance to improve the security of our computer systems. This maps nicely to a DevOps approach to software development, with a focus on security. The ideas here include not just patching, but fixing underlying issues, learning from mistakes and from other organizations, improving the skills of their developers and administrators. This is a holistic approach to ensuring security.

    This not only includes the software we write, but also the software products, platforms, libraries, etc. that we use. For many of us, this means the Windows host OS, the networking software and connections, http servers, vendor .NET libraries, and more. This also includes database software of all sorts, whether you use an RDBMS like SQL Server, perhaps a search service like ElasticSearch, a caching database like Redis, or some other NoSQL data store.

    The idea is that we not only code securely, but also make sure we patch our software. That’s a problem in many cases. In fact, MongoDB has had a security issue for the past few years that was well publicized but still affects many installations. Far too few people have patched their MongoDB databases, and it’s a problem. Data is exposed to anyone on the Internet, and it’s likely someone is taking advantage of this to access the bits. In fact, many hackers exploit flaws in software for which patches are often available. The updates just haven’t been deployed.

    Patching software is a challenge, and it seems that there is no shortage of ways in which companies deploy updates. Microsoft is pushing automatic patches. Some applications make these optional. Some apply changes in the background, some require downloads. If you use a service, then patches are seamless, but the changes may break functionality or force a new workflow on the user.

    Ultimately I think that we, as an industry, need to mature with the ways in which we patch software. Customers and clients should demand better, and the impact of applying patches should go down. Quality should go up, and while that has happened with some companies, I think we need better patterns and practices that are taught and adopted by more developers. Perhaps more people sharing their techniques, templates, and practices will help others become better at building and managing our software updates.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.5MB) 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.