Voice of the DBA

Category: Editorial

  • Power BI Week

    This past week was T-SQL Tuesday #75, with the topic of Power BI. There were some interesting posts, and we’ve included a few in this week’s newsletter. This was also the week where the winners of the Power BI report contest were announced, and it was a good entry. This was the one I actually thought was the best one. If you haven’t seen it, take a few minutes and go look at the entry and play with the data. It’s interactive, so be sure to see how well the Denver Nuggets have competed against Mr. Curry.

    I first looked at Power BI last summer, watching a presentation and being intrigued at the ability of the tool to easily get data from web pages. I then talked with a friend that works with Power BI regularly, and they showed me how to loop and gather information from many pages, easily assembling the data into a dashboard that was interactive. I was very impressed, and I’m looking forward to doing a bit more work on the platform myself.

    Quite a few people have been concerned that the development of tools like Power BI mean that there is less of a need for IT staff. I find myself thinking quite the opposite. There will always be data quality issues with something like Power BI, and there is a certain level of programming to build more complex reports. Not to mention the refresh and update process is slow, which can make the tool feel cumbersome.

    However users will find some reports are more useful, or more important, and need some help from technology professionals. Whether that’s developing more DAX formulas and measures to enrich displays or just providing stable, clean data sources that consolidate data from various places, there will be plenty of work for IT staff that support companies using Power BI.

    To me, Power BI is the type of tool that empowers users and lets them experiment a bit on their own. Once they determine what they really want, then they can engage me to build a more robust, reliable, and useful tool that helps the business.

    Steve Jones

  • Forcing Strong Passwords

    It seems that every week we have some sort of data breach that results in emails and passwords for users being released. That alone is a good reason why you should not have passwords shared across sites or systems. I can’t stress this enough, and if you do this, please go get a password manager today and stop. Really, I mean it.

    However is it just the users that create poor passwords? In many cases, sure. However as the people that develop software, we can help. In fact, we should. Security should be on the mind of everyone that writes software.

    I ran across a piece on The Hacker News that says far too many sites don’t require users to choose strong passwords. I think this is changing, but far, far too many sites don’t check a user’s password for strength, or reuse. Some even force users to choose less than ideal passwords. I’ve run into plenty of sites that don’t accept the characters in my strong passwords for some reason. Often the length or addition of any special characters if prohibited.

    I’ve said it before, but I’ll note it here. Most of us aren’t competent at writing authentication routines and should just use one that’s already written and been vetted. More importantly, we should be sure the code, application and database, is modular enough and can be replaced if we find that our application needs stronger security. Because it will. We are constantly racing Moore’s law to implement better security.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Should Production Databases Be in a VCS?

    There was a great discussion recently on my true version of code editorial. It was interesting to see how some people viewed the idea of a version control system as the source of code, while others truly see the production SQL Server database as the truth. No matter how you view things, I had an interesting conversation with somebody later.

    This person asked if we could put the production database code into a VCS. Not as a way of tracking development tasks, but as a way of auditing production and ensuring that any changes in the VCS match with a separate VCS repository. This person noted that they would even like a process that automatically scripts objects and checks changes into a VCS. It’s an interesting idea, and one I hadn’t considered. However as I think about it, keeping a separate repo for production makes some sense.

    When I speak about Version Control, I usually recommend that the production database not be linked to a repo, but that’s usually because I don’t want production changes impacting ongoing development tasks. However, having a separate, production repo means that you have an independent way for the production DBA to verify what versions of code are deployed, and previously existed. This is separate from the development repo, which has to manage it’s own branches, merges, and potentially make mistake about tracking the versions sent to other environments.

    Production DBAs are often conservative about depending on others for the integrity and auditing of their own systems. After all, even when another individual or group makes a mistake, it’s the production DBA that is ultimately responsible. Keeping the object changes and deployments in a VCS, using tooling like SQL Source Control or even automated scripts, can give the DBA their own version of history, an audit trail, and a way to recover from problematic deployments without depending on developers or a full database restore.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Zero Downtime for 2016

    I’ve never felt that zero downtime is possible for a system over any length of time. There are no shortage of companies that seek to prove me wrong, and some are doing very well. For example, when is the last time that the Google search engine was down? It happens, though I haven’t seen this in a long time. Many of the highly available applications out there are built using as distributed applications across many machines, so that even if there are failures, they don’t cascade to interrupt users.

    I was thinking about this as I saw a post asking database engineers to architect for zero downtime in 2016. That’s a good goal, and certainly if you work at a high profile retailer or service company, you should look for ways to improve availability.

    In fact, I would guess that anyone struggling with specific events, like Black Friday, would be working on this problem constantly. I remember years ago Michelle Ufford spoke about the challenges at GoDaddy during the Super Bowl due to the advertisements the company ran. They had log files that couldn’t catch up to the load for hours and spent an entire year working to build a better database system.

    Ultimately I think the best way to handle large spikes of activity is by building an application that avoids putting large spikes of activity on your database. Use messaging and queues to buffer traffic. Use read only copies of your database for traffic that doesn’t need to write to the main database. Anywhere that you can limit the load on your system can help prevent the database becoming a bottleneck for your system.

    Perhaps more importantly, if you can spread the load, you don’t need to purchase more and more hardware. If nothing else, I think this is a good argument for better database architectures for applications. However if you’re like me, most of your systems will have to deal with the hardware that is available. In that case, the best you can do is write better T-SQL and ensure you have given SQL Server enough, but not too many, indexes.

    Steve Jones

    The Voice of the DBA Podcast

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