Voice of the DBA

Tag: Redgate

  • I’m Going on Tour

    I’ve done quite a bit of speaking over the last few years, which has required a lot of travel. I had 15 events that I attended last year, only one of which was in Denver, so I spent quite a few days on a plane.

    sqlinthecitylogoThis year I’ve cut back a bit, with only 3 events so far this year, but it will get busy this fall. The SQL in the City events last year in London and LA were a big success for my employer, Red Gate Software, and this year we’ve decided to expand and build a mini-tour of events.

    We are back in London this July, for two dates this time on July 13, and 14. Friday is booked, but you can still register for Saturday, July 14 and come attend a free training event, which will talk about SQL Server issues and include information about how Red Gate’s products make you more efficient and effective at your job.

    This fall, we are going on a 5-cities-in-11-days tour all over the US. We have booked the following dates:

    In addition, we’ve added a sixth event in Seattle, on Monday, Nov 5, just before the PASS Summit.

    sqltoolbeltThese are free events, and we’ll have some tool tracks, talking about how to better get value from Red Gate tools, but we’ll also have tracks that talk about real issues you encounter as DBAs. I’ll be speaking, along with Brad McGehee and Grant Fritchey on writing better T-SQL, database maintenance, disaster recovery, and more.

    This tour is something we’ve talked about for a few years, and we’ve finally gotten the time, money, and permission for a tour. So look for us to be traveling through your part of the US. I’m not sure if we’ll have a real tour bus to travel in style.

    tourbus2

    With quite a few people coming over from the UK, and a frugal boss, our budget might end up supporting a tour in something like this.

    tourbus

    In any case, if you will be in one of the cities on any of the dates above, register today. It’s an exciting experiment, and one that I hope will be valuable for you and us, and something we’ll get to do every year in different cities.

  • From the Labs of SQL Prompt

    I love SQL Prompt as an add-in for SSMS. The intellisense is very handy for me and I’ve gotten used to certain shortcut combinations that make it easy for me to write T-SQL quickly and get information on parameters without opening Books Online. It’s works better than the native intellisense for me, though perhaps I’ve just gotten used to it. When it’s not installed on an instance in one of my VMs, writing code is a chore.

    When I was in Cambridge recently, I had the chance to sit down with one of the developers of SQL Prompt and he showed me a few things I had never seen.

    For the most part when I install SQL Prompt, I leave it with the defaults. There are a few snippets that I change quickly, like the ssf snippet. This normally produces a “SELECT * FROM” and I add a “TOP 10” to it in order to reduce the amount of data I bring back.

    However there are a few features in SQL Prompt that are “experimental” in nature. They are complete, but not deployed into the product by default. You can access them from the SQL Prompt menu in Management Studio.

    prompt3

    This brings up the Experimental Features tab in the options dialog, which you can see below. There aren’t a lot of features, but these are ideas that have been suggested, or are working, but they developers aren’t sure if they are completely spec’d out.

    prompt4

    You can enable a few of these to see if you really want to see how they work. For example, I’ve enabled “Automatic Refresh Suggestions”. Since I tend to work in one database at a time and create lots of objects, I want this to happen. I can ALT+S, Enter for this, but I’d like to tool to do it for me.

    These items change periodically, and some link to related tools (like SQL Tab Magic), and they give you a chance to test the way the feature works and provide feedback. If you are a SQL Prompt user, you might check out this tab.

    If you’d like to see what SQL Prompt can do for you, download a free trial and give it a try:

    14-day-free-trial

  • 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.