Voice of the DBA

Category: Editorial

  • The Future for Database Administrators

    I’ve been working in technology for over 25 years. As I look forward in time, I suspect I have less time left in a data professional career than I’ve already experienced. That’s fine, and while I don’t plan on retiring anytime soon, I know I likely will retire before another 25 years pass.

    While it might not be as much of an issue for me, I do think it’s important to think about where your career may go. I ran across this post on the future of IT Pros and thought, “this sounds familiar.” A friend I knew used to manage a 20,000+ Exchange system for a large company. In 2000, he was sure that the advances in email technology from Microsoft would mean that he wouldn’t have a job in five years. Within two he had moved on to another industry altogether, trying to establish a new career before he was made obsolete.

    Certainly if he had planned to still manage a 20,000 mailbox Exchange system the same way today, he’d be out of a job. However, if he evolved to either learn new skills along with Exchange, or added O365 administration, he might still be employed, and earning a very good salary in technology. Likely more than he makes now.

    I think about this regularly as I see the Microsoft stack world changing and the emergence of new database technologies like NoSQL, data lakes, and Azure SQL Databases/Azure Data Warehouses. Those technologies certainly might mean that less DBAs are needed to actually install, back up, manage, and care for on-premise database instances. However, I still think there are plenty of opportunities for the Database Administrator. High Availability and Disaster Recovery technologies are more in demand than ever, and there is a complexity in Availability Groups that ensures there are still good jobs. Increasing security and auditing requirements require humans, usually with knowledge about querying and understanding the data more than setting up the technology.

    The bar may be raising in terms of skills needed from database IT pros, but I still see lots of opportunity. Continue to work on your career, learn more, adapt, work hard, and you’ll have a job for a long time. Try to ride out the next few years on the same skills you’ve used for the last decade, and it’s entirely possible you’ll find yourself replaced by either a piece of software or another human that’s willing to learn a few new tricks.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Security Leaks from Websites

    One of the main issues with connecting databases to the Internet is that if a hacker finds a way to get access to the database with credentials, perhaps using a well known account (*cough* sa *cough*) and a weak password, they can get a lots of data. However, even if your database is not on a DMZ or addressable from the Internet, chances are some application that accesses your database is connected to the Internet. Websites are a perfect example of this.

    That means our data security is controlled by the security of the web application, and the security coding practices of our developers. That’s a scary in many cases, primarily because we, as an industry, don’t do a good job of actually sharing secure coding practices and habits widely. As much as people talk about and publish information about writing secure code, this doesn’t get spread around to many developers, who have years and years of bad habits. Even here at SQLServerCentral, we don’t have nearly enough information on SQL Injection, which is something I need to work on.

    However, even if you aren’t exposing your database server and aren’t vulnerable to SQL Injection, is your data secure? I read a rather sad article from Troy Hunt that looks at how personal data can be leaked. Far too many applications don’t follow secure information handling practices and unintentionally allow information to leak. Perhaps the most common item is verifying whether an account or email address actually exists or not in a login or password reset box. There are other examples, including not properly securing an entire page with SSL, allowing a simple thing like an order number to return user details, or even allowing someone to reset a password by guessing the answer to a security question.

    These seemingly benign issues often expose real vulnerabilities. I’m sure many managers and even developers would say that a hacker wouldn’t sit at a web page and try lots of email addresses. That’s probably true. However, does your web application have limits for how often someone can submit code? Extremely powerful scripting tools like Perl, Python, and PowerShell mean that a user could make submissions to your website in an automated fashion, retrieving the code and parsing it to find details.

    Most attacks on a system aren’t of the Hollywood movie variety. A hacker doesn’t find some hole and miraculously gain access to data in a few minutes or hours. Instead, a malicious user (outside or inside your organization), might make a series of attacks across days or weeks. They might try millions of emails or order numbers, gathering details and planning their future attacks that will actually reveal sensitive information. All those tools that make your job easier as a developer, or allow complex data analysis, can work just as well against your database as they do with your data.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Is Rollback Feasible for Database Changes?

    One of the things I used to show people in SQL Compare was the ability to generate a quick rollback script by switching the source and target. We can generate a deployment script going from Dev to Production, switch the source and target, and then generate the rollback script, from Production to Dev. This was the automation of a manual process I used to go through, examining changes made in deployment scripts and producing the reversing changes for various schema items.

    Really the only object that causes us problems is the table. We can easily grab previous versions of views, functions, stored procedures and other code objects, applying them on top of our deployment without worrying about maintaining state. We can go back and forth with different versions of code. Certainly our applications might have issues, but the database itself works fine and deployment is quick.

    However, that’s not the case for tables. If I add a column to a table in a deployment, how do I remove it? Certainly I can drop the column, but what happens if there’s been a data transformation, or users have entered data? I might need to preserve the data that’s been added, which is a process I might not want to script. What if I split a column or refactor data to a new table? The movement of data back to it’s original spot, while preserving any changed data, is not a trivial task. Add up enough changes in a deployment and it wouldn’t be worth running any rollback scripts. A database restore might be more efficient.

    Recently I was discussing these issues with a developer, who remarked that they never built rollback scripts. It wasn’t a good use of time. If there were issues, developers or DBAs would need to make decisions about how to handle the issues. These days, as we’ve become better at architecting database applications, we might even decide to leave the actual table alone and use view or stored procedure change to effect a virtual rollback for the application, erasing the changed schema with code rather than actually returning the table schemas to their previous versions.

    As I see our organizations depending on databases to a larger and larger degree, requiring no more downtime than actually needed, I think that rollback scripts for tables might be a thing of the past. Certainly rolling back object code is still possible, with an understanding of the impact on both the database and application when old code is used. That’s definitely a decision that needs to be made on a deployment by deployment basis.

    My view is that whenever possible, both the application and database should try to be forward and backward compatible by one version to allow changes to be made to one or the other without breaking the system. That’s not always easy, but in many cases it is with a little forethought, practice, and awareness of the architecture techniques that allow this, it can be relatively easy. In some cases, especially with smaller databases or discrete, limited changes, rollback scripts might make sense. However, I think for most of us this is a software development pattern that’s not worth considering most of the time.

    Steve Jones

    The Voice of the DBA Podcast

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

  • PoSh Everywhere

    This week we had an announcement that PowerShell has been released as an open source project and is available on some Linux platforms as well as OSX. I was actually talking with someone else that had been informed prior to the MS announcement, and they told me to go to github.com/powershell/powershell. I first I thought I was getting some inside information. However this is a public site, obviously updated and released in time for the announcement, so no NDAs were harmed this week.
    I have an Ubuntu VM I use at times, and I decided to take a few minutes to try out PoSh on Ubuntu 16.04. Installation was easy, as are most things on Linux. Not quite as simple as Chocolatey, but close. This is certainly one of the ways that Linux is a bit easier to use than Windows. Or maybe we’re just more used to the command line in Linux. In any case, it’s a very simple process, which I documented in a blog. For those of you that use PoSh, this will be easy, and for those of you used to Linux and shell scripting, you’ll feel right at home.
     I wondered how much stuff would work, but my first few tests, grabbing some generic, non SQL Server, scripts worked fine. The .Net CORE project has already moved some of the namespaces and functionality over, and this means  that a lot of the management of your infrastructure systems is there. I don’t see the SQL Server libraries ported over (if I’m wrong, someone let me know), but there certainly are some other items, including Azure modules. I thought the demos in this Channel 9 webinar, while simple, were pretty impressive.
    Do most of us care about this? No, not really. If you’re a person that runs Windows on laptops and desktops, this doesn’t really matter to you. I run Linux for fun, but not for daily work and my use of these tools will likely be limited to testing. However, if I were to move to an Android or Linux machine in the future, it’s nice to know that I could run the same packages across various platforms without changing any code. That’s useful for collaboration with people that might want to work together on different platforms.
    This is where this makes the world easier. If you have friends running Macs, or using Linux, then you can share code. Or they can write PoSh scripts and share them with you. While I love virtualization, sometimes it’s a pain, and this is why I keep an installation of SQL Server on my host. It’s simpler and easier to work with in some situations. This is also why I look forward to a SQL Server developer edition on Linux (and hopefully OSX). It’s just a smoother world when the OS becomes a matter of choice, rather than a requirement to run some code.