Voice of the DBA

Tag: administration

  • T-SQL Tuesday #83–The Same Old Issues

    tsqltuesdayThis month is an interesting T-SQL Tuesday topic, and it’s brought to us by Andy Mallon, with the topic of the same old issues we’re still dealing with. I think that’s an interesting issue, since I do find myself answering the same old questions over and over.

    If you’ve never participated, T-SQL Tuesday is a day when people should publish a post on the specified topic. This is a way to generate some posts and interest, and perhaps learning, about a topic. Share your thoughts, either on the second Tuesday of each month, or catch up later on your blog.

    We Still Don’t Restore

    I’d like to write something about T-SQL, and I could. Certainly SELECT * is an issue, or why we should not use old style joins (that’s almost gone), but there’s a SQL Server topic that I think bears repeating.

    A backup isn’t enough. You must also test restores.

    The reason isn’t complex, but plenty of people still don’t seem to understand why a backup isn’t enough. After all, if I copied a file, or I ran BACKUP DATABASE successfully, isn’t that enough?

    Suppose you had an issue at 3am and needed to restore a database on a new instance. You run this T-SQL:

    USE [master];
RESTORE DATABASE [Finances]
FROM DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\Finances.bak'
WITH FILE = 1,
    NOUNLOAD,
    STATS = 5;

GO

    And you get this error:

    Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint '0xD9B9E685D465E29C11E15346D995DEF59E53B4A3'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

    What do you do? Hopefully you recognize the issue and can fix the issue. Maybe more importantly, you have a backup of the missing certificate.

    Most people don’t deal with encryption, but you never know when your backup job might start failing, perhaps writing to a damaged file that appears to work (if you write as a device) but really isn’t capturing the backup file. Perhaps you don’t know that your backups are being written to a location and deleted a day later, but the process that is supposed to copy them to tape or a remote file share is broken.

    Any number of things can happen. The point is that you want to be sure that you are actually getting useable backup files.

    That means testing restores.

    It’s 2016. I shouldn’t have to remind anyone of this.

  • DBA Support

    This editorial was originally published on Oct 28, 2012. It is being republished as Steve is out of the office.

    There was a time when I managed two production databases on SQL Server. Two. I had a development version of one database where we paused development for testing, and only two production databases to manage. Since I had to also handle development, application support and hardware repair/replacement, that seemed like plenty to me. I was the accidental DBA, with database administration being the lowest priority of my day.

    After that I moved on to administer databases in a number of jobs, sometimes as a priority, sometimes not, but in each case, I learned to work more efficiently and effectively. My goal was to automate as much as possible of the routine work so that I could spend my days adding value to the company. I learned to use scripts, alerts, jobs, and more to keep systems running while I was doing other work.

    I’m sure many of you work in a similar manner, or at least I hope you do. This Friday I wanted to ask you at what scale do you need to become efficient, based on the size of your organization. The question this week is:

    How many databases does each DBA in your organization manage?

    I know some of you manage lots of databases in raw numbers, but also let us know if you need to do much with these databases. Is maintenance automated, or is there much active management you need to do in order to ensure these databases are running on a weekly basis. Let us know the size of your load as well, perhaps the amount of data is a better way of measuring the DBA load.

    Steve Jones

     

  • What is the Future of Monitoring?

    I read Tom LaRock’s post on the future of monitoring, looking at the rise of machine learning and complex algorithms to evaluate systems. Tom notes that the data input into a system is crucial in determining whether the system can produce a prediction or output that has value. Since the humans that decide what data is included may not choose wisely, the system won’t necessarily reduce the time required to manage a system.

    I tend to agree. From my conversations with those people having success with machine learning systems, the data preparation is the most critical (and difficult) step. Shortcut the cleansing and organizing, or make poor choices of what data to include and you won’t likely get actionable results.

    However, I have a different view of monitoring. After my watching of the Darpa Grand Challenge (editorial on this recently), I think the future of monitoring is perhaps actually making changes to code in-line. Perhaps with approval from humans, but I suspect that we can train some process to understand how particular batches can cause issues. We can certainly set filters that might note index changes on large tables could be problematic and should be investigated before an issue arises. We can teach a system to recognize code deployments, perhaps even roll back certain changes if the application fails. We certainly could have a machine learning system watching index usage and query plans to recommend indexes in a real time manner, perhaps even turning on and off on-line rebuilds.

    I think there is possibility, but where I’d really like to see advanced monitoring is not in production environments. I’d like to see better systems that can watch development, helping suggest or rewrite SQL before it’s deployed. Perhaps applications can warning developers of potential performance or security issues. Such systems could help us in building more consistency into our applications. We can have systems that help our developers code better.

    As our databases and software become more critical to the functioning of most of our businesses, we certainly need more reliable and robust development practices.

    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

  • The Danger of xp_cmdshell

    Securing a computer is a challenge. There are all sorts of potential issues in every platform, and ensuring safety for your data can be less a reflection of your ability and more the good fortune there isn’t a focused effort to attack your systems. However, we certainly also face issues with inside users, many of which may make mistakes that are accidental more than malicious. It’s for these reasons that we look for secure by default applications and a reduced surface area for any system.

    Many people refuse to turn on xp_cmdshell as an option for scripting in SQL Server. This is disabled by default, and quite a few DBAs are glad of this setting. However, there are plenty of people that think xp_cmdshell isn’t a big security risk. There are certainly ways to mitigate the usage by non-privileged users, and this can be a tool that is very handy for accomplishing work without a lot of development time.

    This week, as security issues become more important to us all, I’m curious how you feel.

    Do you think xp_cmdshell is dangerous?

    I have to admit that I’m torn. I don’t think this inherently dangerous. It does open up some attack vectors, but the last few versions of SQL Server have allowed some limitations, so I would enable this if needed to solve some issues without too many concerns. However, I wonder if many of you feel the same way.

    Steve Jones

    The Voice of the DBA Podcast

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