Voice of the DBA

Tag: syndicated

  • SQL Saturday Advice – Build a Foundation

    I think this is a good idea, and I’ll give you some reasons, but every SQL Saturday ought to consider having a few sessions an hour before the main event starts.
    When the Rocky Mountain TechTrifecta v2 came to Denver this past February, I was one of 7 or 8 people that was asked to run an early morning session. From 7:30-8:30, before the 9:00am kickoff, we had a number of sessions in various rooms that were essentially discussions. I actually picked up someone else’s session since they couldn’t make it, and had about 10 slides, but it was really me leading a discussion with 6 or 7 people in the room.
    I wondered what was the point, but as I talked with Julie Yack, the organizer, she said that the previous year they’d had lots of people show up early to register and then stood around, not knowing what to do. I’ve seen that in other events, including many SQL Saturday’s as well. It takes time to register 200+ people, even if you are just giving out a name badge, and so the first person to come in has a lot of time to waste.
    There’s also a lot of people that come to SQL Saturday that are very new beginners. They don’t understand a lot about SQL Server, and they’ll get lost during other sessions. Or they are experienced in one area, but not another.
    This is the time for a basic foundation for SQL Server. Honestly I would suggest that every SQL Saturday recruit 4 or 5 speakers, and offer these basic sessions, a very, very junior level session, before the main event.

    1. Introduction to SQL Server
    2. Introduction to Reporting Services
    3. Introduction to Querying in T-SQL
    4. Introduction to Integration Services
    5. Introduction to Analysis Services (if you have speakers in this area)

    Set up a few rooms, start them at 7:30 if you start the event at 9:00 and publicize them for beginners. You will have some very, very happy attendees.

  • Core DBA Skill – Backing up the tail log

    I’ve never had to do this in production, and I’ve only practiced it a few times, but I think this is a core DBA skill. Along with being able to backup and restore your databases, you should be able to recover to a point in time. That can mean a tail log backup.

    I read this in Paul Randal’s blog recently and decided to practice it. So I created my own new database and added a few transactions:

    create database db5
    go
    use db5
    go
    create table MyLog
    ( Txt varchar(max)
    , LogDate datetime default (getdate())
    )
    go
    insert MyLog select 'No backup', GETDATE()
    go
    backup database db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_full.bak' with init
    go
    insert MyLog select 'Full backup complete', GETDATE()
    insert MyLog select 'Misc Transaction', GETDATE()
    go
    backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn' with init
    go
    insert MyLog select 'Log Backup Complete', GETDATE()
    go

    I’ve added a couple backups here. If I were to restore this full, I ought to have 1 row in this table. If I add the log restore, I’ll have 3, but I’ll be missing the last line that says “Log Backup Complete”. Now I’ll wreck the database, as per Paul Randal.

    use master
    go
    alter database db5 set offline
    go

    I then rename the mdf file from db5.mdf to db5xxx.mdf, essentially “deleting” it from view by the SQL Server service. When I set this db online

    alter database db5 set online
    go

    I get

    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db5.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
    Msg 945, Level 14, State 2, Line 1
    Database 'db5' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.

    As expected, we have a problem. How do I backup the tail of the log? Remember that file is still visible. We’ll use Paul’s trick to add NO_TRUNCATE to the command:

    backup log db5 to disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn' with init, no_truncate

    I get a successful backup, so let’s test. Here’s my restore script, restoring this db as a new database on this instance.

    RESTORE DATABASE [db7]
    FROM
    DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\db5_full.bak'
    WITH
        MOVE N'db5' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7.mdf'
    ,  MOVE N'db5_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db7_1.LDF'
    ,  STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7.BAK'
    ,  NOUNLOAD,  STATS = 10
    GO
    select * From db7.dbo.mylog
    go
    RESTORE log [db7]
    FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log.trn'
    with standby = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK'
    go
    select * From db7.dbo.mylog
    go
    RESTORE log [db7]
    FROM disk = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\Backup\db5_log_tail.trn'
    with standby = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_db7_log.BAK'
    go
    select * From db7.dbo.mylog

    When I go through this, I get result sets of 1, 3, and 4 rows respectively. I see all my inserts, so despite having a corrupted, destroyed, or renamed (in my case) MDF, I can get all my data back with the log.

    Learn and practice a tail log restore. It’s worth it.

    And don’t forget to set the db active:

    restore database db7 with recovery

  • T-SQL Tuesday #008 – Learning

    It’s time for another T-SQL Tuesday, the brainchild of Adam Machanic (Blog|@AdamMachanic) of SQLBlog.com. This time we have a SQLServerCentral author, and MCM, Robert Davis, running the show.

    How To Learn

    I’ve had a lot of school in my life. 12 years of primary education, 5 years of undergraduate level education, 2 years of graduate school education, 7 semesters of calculus, and even a few programming classes since then. In all those hours, I have learned a few things, but the most important things were learning how to learn.
    A high school degree (US) or an undergraduate degree (BS/BA) doesn’t really teach you a lot of practical skills, IMHO. However what they subtlety teach you is how to learn. You have to develop the skills more and more to teach yourself, research, analyze information. Those are the skills you will need as you move into a career. Even engineers, who learn a larger percentage of skills in skill, need to learn more in the workplace, and successful students tend to have an easier time picking things up later.
    Not that average/below average students can’t. Sometimes it’s just finding something that brings out your passion, which often isn’t school.

    How I Learn

    So how do I learn? As much as I like lectures, and I listen to many of them at SQL Saturdays or other conferences, it’s not necessarily helping me learn or build a skill. It increases my knowledge base, gives me the ability to think more laterally when confronting issues or searching for a solution, and it inspires/excites me. It gets the juices flowing.
    However to actually learn something, to build a skill, I need to do. I typically learn by actually writing SQL statements. Setting up mirroring, testing restores. Those skills, just like muscle memory from performing a task, are built for me by repetition and practice.
    That’s one reason that I have taken relatively few classes in my career. Taking a week out and being immersed in something like VB or SQL hasn’t helped me nearly as much as having hours a day across months to actually write code and try to solve a problem. This blog, at least the T-SQL parts, have tended to be focused on rebuilding those skills for me. Practicing things that are new, or that I haven’t spent much time on.
    I prefer working with books to classes, but it’s the same thing for me. I retain some snippets from books, but if I don’t practice the skills, actually get hands on time, I haven’t learned much.

    How You Learn

    I don’t know. I think you have to try some different things and then evaluate if they work. If you attend a class, see if you use those skills across the next couple months. Did you really learn something? Try something you learned a couple weeks afterwards. Did it stick? If not, try something else. Read about it and do the same test. Work through examples from a blog post/article/book/class, and see if that helps.
    Learn how you learn. It’s one of the most valuable things you’ll ever do.

  • Why I Write

    I have a great job. Actually it’s a fantastic job for me. I have flexible hours, I can work almost anywhere, which means that I can do things like go with my kids to events, even trips out of town and still work. I get to express myself, and talk about interesting things.
    However there are hassles, it can be a grind, and there is pressure to come up with something interesting on a regular basis. I do enjoy it, but I have had times where I thought about going to be a DBA somewhere and letting someone else point out work for me to do.
    Then I get notes like this:
    I’ve been in this business since 1980, starting with CDC punch cards, so you can see how much ‘experience’ has accumulated in what seems like the wink of an eye. I don’t get the chance to read all your editorials, but from the ones I have read, you have the gift ( because it is a gift ) of seeing the whole picture!
    … Your insight and knowledge of not just ‘work’ but of people and careers is the best, more so than any other I’ve read. Don’t lose that deep appreciation of life/work, care and intelligence your editorials have come to be.
    That’s cool. It arrived in my Inbox recently and made my day.