Voice of the DBA

Tag: Redgate

  • You need to run DBCC CheckDB regularly

    You never know when you’ll encounter corruption. It can happen at any time, usually due to some sort of hardware problem or driver issue. Corruptions don’t disappear and you can’t necessarily

    Corruptions can be caused by numerous factors, and it isn’t something you can predict. However sooner or later, you’ll get corruption on one of your instances. I don’t know which one, whether it’s the finance production instance or the vacation tracker development database, but it will happen somewhere.

    Since corruption can flow through to backups, and can exist in your system for some time, you could end up losing lots of data. Your best defense is to run DBCC on a regular basis and ensure that you catch corruption or problems as early as possible.

    My recommendations in order. If you can’t do #1, do #2, or #3, but try to get something in place.

    1. Run DBCC on every database, every day – If you can, run this on all your servers. I know it’s a large request for some of you, but if you can do it, do it.
    2. Run DBCC on every database on another instance, restoring from last night’s backup – If you can’t run DBCC on your main server, offload it onto another one.
    3. Run DBCC every day on some database, rotating so that all databases are covered – Do this so that at least every week or two you have a DBCC run on your production databases. You can do a random sample of some sort, but hit all of them as often as possible.
    4. Work on a your resume or CV – If management won’t let you run DBCC checks, sooner or later you’ll have an issue, take some blame, and need a new job. Be prepared for that, if for nothing else.

     

    sqlbackuproOne of the recommendations to minimize impact from CHECKDB operations is to run them on another server, using an automated process. SQL Backup Pro 7 from Red Gate makes this very easy to do with automated backup file copy operations, restores, and CHECKDB execution.

  • SQL Backup 7

    sqlbackuproWhat more could you add to a SQL Server backup product? It seems that many software products, including Red Gate’s SQL Backup Pro, have been able to handle the things most DBAs care about for some time: compression and encryption. Today my company has released a new version of SQL Backup, version 7, which does add a couple of very nice features.

    One of the problems that exists in many database configuration is the lack of verification of the backups, and a lack of consistency checking for corruption. Both of these are fairly rare events, but when they do strike, they can propagate through backups for weeks, months, or even years at times. When a disaster does strike, this can result in a tremendous amount of lost data for an organization.

    SQL Backup Pro 7 helps to ensure your backups can be verified and checked for issues with the addition of two great new features: scheduled restores and automated verification checks.

    You can see a walkthrough of the features from my colleague, Grant Fritchey, on the Red Gate website. Grant shows how you can automate the restores, to another server, and run your DBCC checks.

    Run these Checks

    When corruption strikes, you may not be able to actually recover data. It could be too late, so it’s important that you regularly run DBCC checks. However it can be a performance issue on your production server since DBCC is resource intensive. The solution is to offload these checks to another instance, but for many DBAs, the scripting required to ensure this runs every day is rather complicated. You can do it any number of ways (T-SQL, PowerShell, VBScript, etc), but it’s important that you do it.

    The addition of this feature to Backup Pro is something myself, Brad McGehee, Grant Fritchey, and others have been requesting for some time. The ease with which you can set this up means there is no reason not to run your DBCC on almost every backup file.

  • Turning 40

    I turned 40 a few years back, and it wasn’t that big a deal for me. In some ways my wife thinks I’m a little old for my age, and when I hit 40, it wasn’t the milestone it is for some people. I think the night I turned 40 I was at a Cub Scout meeting with my son, delaying a sedate, quiet family dinner for a day or two because of that commitment.

    Last week I was in Cambridge, at the Red Gate offices.

    English phone boxes
    Not in Kansas (or Colorado) anymore.

    Every year I go over once for twice for meetings with my team at Red Gate where we brainstorm, do some evaluation on where we are, and look forward to the future. Most of my time is away from the office, but I do spend a little time at our building (Red Gate has a building!!) and say hi to a few friends.

    On Thursday morning, I was there for a surprise, and a rare event. The entire company, some 200 odd people gathered in the first floor (floor zero for you non-American, counting-building-floor-challenged people), with everyone in a Red Gate shirt.

    Red Gaters wearing red shirts
    A sea of red

    The two founders of Red Gate, our co-CEOs, Neil Davidson and Simon Galbraith, both were turning 40 within a few weeks of each other. A few people in the company had organized a celebration with sweets

    This was less than half of the treats available.

    cards

    Large 40th Birthday cards signed by everyone.

    and a large rendition of Happy Birthday from the employees.

    It was quite a celebration, with a funny “roast” speech given by one employee, a video Happy Birthday tribute, and even a parody of “Never Gonna Give You Up” from the Red Gate Chorus group.

    A nice break in the day, and quite a celebration. Simon and Neil have built something rather amazing at Red Gate. It’s the kind of company I’d want to build if I every started another venture. Successful, hard working, respectful, fun, and very supporting of employees.

  • Use Your Tools

    I used this tool for the first time and found it very handy.

    Someone asked me recently if I’ve ever exported a table using SQL Packager, a tool from my employer, Red Gate Software. I hadn’t, and in fact, hadn’t ever even opened the tool. So I started it up and exported a table. I was surprised how easy it was, and I wrote up a short blog on it.

    That reminded me of a common issue that many of us have: we get into a rut. It’s easy to stick to doing things the “old way” we’ve learned, and not updating our skills to take advantage of newer features.  It’s also easy to get used to going through a process one way and never trying, or experimenting with different techniques or applications of the tools.

    I have been guilty of this, and am trying to rectify it, working with new features, and trying out new SQL Server 2008 R2 or SQL Server 2012 T-SQL changes where I can. I noticed Grant Fritchey recently talking about Extended Events and the advantages of using them with deadlocks, and also using them for performance tuning. Learning Extended Events is an area I need to spend some time with in the future, and I’d recommend most other SQL Server professionals do the same.

    However it’s not just the changes in SQL Server. There are changes in tools from vendors that can help us do our jobs better, or even tools we don’t know we have. I have spoken with lots of Red Gate customers that weren’t even aware of the capabilities of the tools on their machines. In some cases they weren’t even aware they had the tools because they were purchased in a bundle. Not every tool is useful, and not always appropriate, but it’s worth a little time investment to learn how to use the tools and gain some awareness of their capabilities.

    We publish some information on the Red Gate tools at SQLServerCentral, and I constantly find great blogs written by people that actually use the tools in their daily work. I suspect that’s the case for other tools as well. Take some time and read about the tools you own, and even the ones you don’t. You might find one that will actually save you time, make your job easier, and provide a nice ROI for your company.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.