Voice of the DBA

Category: Editorial

  • 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

  • The Push to Upgrade from SQL Server 2005

    It was interesting to see the results of my straw poll last week on SQL Server 2005. Quite a few people still run that version, and some aren’t looking to upgrade anytime soon. However when support ends, you should be aware that there is potentially an impact to your organization.

    If you work for a government, or you are bound by regulations such as PCI, HIPAA, or something else, you need to upgrade. I would hope that an audit sometime in the last 3-4 years would have alerted you to the issues of running unsupported software, but given the wide variability in auditors in the world, perhaps not. In any case, consider this a warning that you should be sure any databases that contain data regulated by law are upgraded by April 12, 2016. Otherwise your organization will likely be out of compliance with the regulations.

    If you haven’t upgraded, and want to avoid an upgrade for the next decade, this is a tough time. I’m not sure that SQL Server 2016 will RTM before support ends, which is the version you’d like to pick. This means that counting on this event is unwise. Even if you were sure SQL Server 2016 will release on April 1, is that really enough time to test your application?

    That’s really the bottom line for most of us. We need to test and be sure that an upgrade will, in fact, work for our particular environment. We need extensive testing, which probably means that those of us aiming to upgrade should be aiming for SQL Server 2014 (or SQL Azure Database). Microsoft would like you to choose one of these, as it benefits their bottom line, and they are pushing customers to upgrade. However, that’s somewhat the nature of software. We need to upgrade at times in order to maintain our systems, and vendors won’t support old software indefinitely.

    There are a couple months left, and I think you’ll need all that time to be sure your software still runs fine. I’d recommend you use new hardware, a completely separate instance (side by side upgrade) and use Distributed Replay to capture a workload on your existing instance and replay it on the upgraded server to check that your system performs well. To do this, you really need to be sure you are monitoring your current (and the upgraded) server to measure timings and performance.

    Monitoring isn’t simple, but it’s not that hard either. You should always be monitoring your system (we use SQL Monitor at SQLServerCentral), as well as keeping baselines, including wait statistics, that you can use to analyze the performance of your database. This is invaluable in determining if your new database will outperform the old (hint, it should).

    However you approach your upgrade from SQL Server 2005, be aware that you need to be on SP4. I recommend you use the Upgrade Advisor, perform a side by side upgrade, and don’t be afraid to use the compatibility level to allow the database to continue to behave as a 2005 instance. Above all, be sure you have good backups you’ve tested, just in case things fall apart.

    Lastly, SQL Server 2008 and SQL Server 2008 R2 are both out of mainstream support, so don’t forget about future planning for those versions as well. Best of luck with your upgrades, and please let us know if you find specific issues or problems in upgrading. Post a note in the forums to get help, or maybe help someone else complete their own upgrade.

    Steve Jones

    The Voice of the DBA Podcast

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