Voice of the DBA

Tag: SQL Connections

  • On the Move to SQL Server Connections

    Brian Kelley, speaking on the SQLServerCentral track.

    A bit of a crazy day for me, starting off a crazy week. I leave this afternoon for SQL Server Connections in Orlando, where it’s in the 80F with some rain. It was about 60F yesterday and sunny in Denver, a beautiful t-shirt day and today I woke up to snow on the ground and more coming down now. I’m a little torn, since I’d rather be heading to the mountains to snowboard than the pool to swim, but I am looking forward to SQL Server Connections.

    SQLServerCentral is sponsoring a track, with Brad McGehee, Brian Kelley, and Jonathan Kehayias speaking. We have set up sessions and I’ll be down there as the roving reporter and SSC host, sporting a Hawaiian shirt, my cowboy hat, and  no pants. At least, I’m hoping I get by with shorts for the two days I’m down there.

    We have sponsored a track the last two years and it’s great fun. We get the chance to pick speakers, all seasoned SQL Server experts and presenters that will give you a great experience on the SQL Server track. Unlike PASS, the speakers are invitation only, both from our track, and the SQLskills track, managed by Paul Randal and Kimberly Tripp. Unlike some other events, I always see great sessions at Connections and I am almost never disappointed.

    There’s also great variety since the conference is located in the same hotel as the ASP.NET, Silverlight, Visual Studio, Sharepoint, Exchange, and Windows conferences from the DevConnections event. If you work with multiple technologies, this is the best conference to go to and get a variety of knowledge on different sessions.

    It’s also in a great location. I’m one of the strange ones that likes winter, and I don’t necessarily want it to end quickly, but most people are happy to get away from the cold for a warmer location. The spring event in Orlando, and the fall event in Las Vegas, give you the chance to do that.

    If you aren’t coming this week, keep an eye on the blog for notes and content from the show where I’ll be reporting on sessions, taking some pictures, and perhaps some video. Think about coming to the fall show in Vegas, and get some great SQL information in a great location.

  • SQL Server Connections in a week

    I’m heading to SQL Server Connections next week, part of the DevConnections conference featuring a whole slew of technologies. Once again SQLServerCentral is sponsoring a track at the event, and I’m playing host and reporter.

    We’ve picked some great speakers, including Brian Kelley, Joe Webb, and Jonathan Kehayias to talk on security, locking/blocking, and extended events. We have Brad McGehee as well, talking trace and helping me host the party.

    This spring the event is back in Orlando, which is a great location and while I don’t mind the cold weather of Colorado, it is good to get down to Florida for some warm weather and cold drinks and talk some SQL Server

    Hopefully I’ll see a few of you down there. Look for me in the Hawaiian shirt and cowboy hat.

    There’s still time to register and you can save $200 with “DevCon1” as the registration code.

  • A SQL Conference in the Sun

    SQL Server Connections is in Orlando this Spring

    The SQL Server Connections spring conference is coming up in a few weeks in Orlando, FL. This is a part of the DevConnections multi-technology conference and this event is the chance to get some great SQL knowledge and inspiration from experts in a fantastic location. Once again SQLServerCentral is sponsoring a track at the conference; Brad McGehee and myself will be down there, looking forward to meeting a few of you.

    I think that conferences are a great way to take a break from work, recharge, and get some excitement back in your job. The speakers at these events teach me about SQL Server, and inspire me to come back and try some new feature or technique in my own environment. I might get the solution to a problem, and usually find that the work is just a little more fun when I return. To me, that’s an ROI that’s hard to argue against .

    DevConnections puts on a great conference event, recognizing that while people want to come learn, they also want to do it in a nice location. This event is in Orlando, with great weather, at a place you can bring your family for a mini-vacation before or after the event. There’s no shortage of things to do in Florida, and it’s a relatively inexpensive place to visit. The conference hotel even offers complimentary transportation to many popular attractions. Who knows, we might even end up sitting by the pool and talking high availability or Resource Governor in the late afternoons. That’s more interesting to me than discussing things in a conference hallway.

    So many people are hybrid IT workers, using more than one technology, and this conference reflects that. The last time I went to a DevConnections event,  I attended mostly SQL Server sessions, but did manage to see an ASP.NET session and a couple Sharepoint presentations. Becoming more rounded in my IT knowledge has paid off for me in the past, and the DevConnection events give you the chance to grow skills in a variety of areas.

    Hopefully I’ll meet a few of you at SQL Server Connections this spring and share a few drinks while we talk about SQL Server.  I should be easy to find in the Hawaiian shirt and cowboy hat.

    Steve Jones

    Save $200 with the “DevCon1” code when you register.

    The Voice of the DBA Podcasts

  • VLDB Issues at SQL Server Connections

    Kim Tripp is a great speaker, with a wealth of knowledge. This session was talking about some of the issues with VLDBs.

    What’s a VLDB? Definitely the TB range, but Kim talks about the issues that come with 100s of GBs as well.  She mentions one client with a 3TB table. Definitely time for VLDB planning.

    She also says that in the last couple of years, almost all of her clients use some type of partitioning. Granted, Kim tends to work with larger, more expensive operations, but interesting to see that partitioning is part of many clients.

    This is a DR session. When data is damaged, what do you do? The first think is think about recovery as a step by step process. Don’t freak out.

    Your first questions should be who, what, and when. Who noticed it, what is wrong, and when did it happen. Key questions to know, not necessarily for blame, but for recovery purposes. Knowing who is important for security or tracing purposes. Kim says no select/insert/update/delete on tables. Always some other mechanism to access data.

    If you don’t know who did something, then it becomes more of a detective case to investigate what might be wrong. Definitely more challenging if you don’t know who did something.

    What do you do first? It depends. You might not be able to take the database or server offline, so what do you do? It’s an issue. You can’t usually shut down the database services, so you must think carefully about what you will do.

    Recovery from a dropped table

    Interesting. A nice sales/customer/employee/product table relationship, with “Sales” being dropped. That’s the easiest one to delete with FKs, but it’s the most important table.

    A good backup is needed first. As Kim notes, if you don’t have a good backup, you can’t go very far in recovery.

    The sales table gets dropped, and one of Kim’s insert scripts starts failing. As expected, if this for real, likely your phone is going to ring.

    First thing: note the time that you first hear of something going wrong.

    Second thing, Kim looks to set the database access to “restricted access” meaning only dbos are allowed in. I have never used this, but if you really lost something big, you want to do this. If this happens, that means that your application also needs to handle this and show a good message to clients.

    Is it better to be back up or have all the data. That’s a good question. It probably will be different for each business, and maybe each database. What is more important? Might be just getting back up in many cases.

    MAKE SURE YOU GET THE TAIL of the log if you can. You don’t want to restore without having this.

    Use stopat in all restore statements. It doesn’t have an effect in RESTORE DATABASE, but allowing this means that you get in the habit of using it in all RESTORE commands. The same with the NORECOVERY option.

    Her final statement has a WITH RECOVERY, RESTRICTED_USER. That’s nice to have, and let the DBA check without users/systems connecting right away.

    Right away, Kim creates a snapshot. She can then easily go back to this point. This is also then used to compare to an investigation database that is a copy of what you restored.

    She also resets the identity seed to a higher value in this case. She has a gap, but it’s a known gap that might be useful if you recover more data.

    One of the key things that I see is Kim keeping notes of times, and what is done, as she goes through the recovery process for this demo. That’s key in disasters. Having a pad/pen or Notepad around is indispensible.

    Kim has a cool script that starts running log restores with ever incrementing STOPAT values to find out where there was an issue. In each restore, then uses tablediff to grab the differences between the snapshot and the test restore to get the missing data that might not be in the database that is now live.

    Main points:

    • contain the damage
    • decide if data loss or downtime is more important
    • recover the damaged data
    • bring offline components online
    • prevent this in the future

    A good DR list to keep handy, and think about first.

    Containment part 2

    One important thing is to contain the issue and perhaps not take the entire db or server offline.

    One thing in Enterprise Edition is to take the secondary files offline, can be useful to take an .ndf file offline. You cannot take an .mdf or .ldf offline, but you can with a .ndf, so for large dbs, separating tables into separate filegroups is a good thing.

    Kim shows an online index rebuild that moves a table across multiple files and it’s pretty cool. While users are in the system, the table is moved to a new filegroup/files. This partitions the table to get better control of disasters.

    Now we lose a file, and the file needs to go offline. This may or may not affect your application. The connections are killed to the db, but a good application will retry and handle this as a transient error. Users that access the partition that is offline will get a trappable, level 16 error.

    Restoring a single file with the move command. That’s cool. Never had to do that myself. Not sure you could do this in SQL 2000 when I had a few disasters. Not sure I had Enterprise Edition in most of my jobs.

    Very cool session, highly recommended if you have to plan for, or execute, a DR plan.