11/25/2008

SQL PASS Community Summit 2008

Wow. What a load of information! Almost an overload, but managed to keep my head above the water in almost every session.

The breakdown:

Day 1:

SQLCAT - Overall Lessons Learned from SQL 2008 Experiences
Quite a good bit of variety here, touching on:

   Data Compression

Backup Compression

Performance Tuning (Extended Events)

SQL Server Reporting Services

Database Mirroring (compressed LogStream)

Encryption

Capitalizing on the SQL Server 2005 System Information
Way more info here than I was able to process...took a few notes to look up after Thanksgiving


Understanding and Troubleshooting Transactional Replication Performance
Lots of discussion on subscription streams/mutli-threading and read/write activity of the Log Reader and Distribution Agents. Got a few things to play with.


SQL Server 2008 Policy-Based Management - Sharon Dooley
Not a whole lot here I didn't pick up at the SQL 2008 launch in LA...still a very cool feature I won't get to use much in our environment.


Day 2:

Guiding your Query Plans (by Kalen Delaney)
Guiding query plans didn't actually enter the picture until late in the presentation, but it was fantastic nonetheless. Most of this session was about Hints...Table, Index, Option, etc. A BUNCH of stuff that never crossed my monitor before. 3 pages of notes worth. Being able to force a recompile of a portion of the overall query is really slick. And the very simple new feature of assigning a value in the declare:


DECLARE @MyID INT = 123


Smart Database Design - MVPs DrSQL and Paul Nielson
Well outside of what I do from day to day, but pretty cool anyway, since I was a developer many moons ago. Worth the lack of elbow room just to hear from DrSQL and Paul live...too bad my on call phone went a little nuts and I had to leave early


Advanced Troubleshooting with SQL Server Extended Events
So far over my head I'm still not sure what I learned...good thing I have notes


Business Continuity with Backup and Restore - Peter Ward
I could probably have taught this session. Should have been labeled 200 level, but still good solid information on backup/restore and some of the new (05/08) options (Online restore, Partial, COPY_ONLY, etc.). Thought about arguing a point with Peter, but decided the way he presented his wasn't likely to cause harm.


Day 3:

Data and Backup Compression Lessons Learned

Some fairly impressive numbers here...300GB DB compressed to 45GB, 50% time savings, etc.

Money-maker: use sp_estimate_data_compression to do custom analysis for customers considering whether to move to SQL compression. Assuming you have the install to do it on.

Collecting and Analyzing Performance Metrics in SS2005/8
Got here late, stuck in the back, couldn't see or hear very well. Saw a little perfmon on the screen I think...


Writing Technical Articles (Kathi Kellenberger)

The only Personal Development session I attended, since I was on the company nickel. Interesting stuff. I've thought about teaching and writing a SQL Basics series for new or accidental DBAs that really will never do more than backup/restore or set up Dev systems. Kathi Kellenberger did a fine presentation, including various places to publish to, and what they pay (if anything). I'm completely convinced to NEVER get in on a book project :)


End-to-end troubleshooting for SQL Server 2005
Kevin Kline rocks. Many others have blogged this presentation, so I won't repeat here.




11/17/2008

PASS Summit 2008

Heading out tomorrow morning....feel free to grab my arm and say hello if you see me there. I'll be the 40 year old slightly overweight dude sporting a laptop in a backpack ;)

Kevin3NF

11/11/2008

I hate it when that happens...

A good friend of mine has a fairly simple community site, with 30K members, about 7K active.

SQL Server 2000, ASP Classic. DB and site on separate servers.

KerBam! SQL Injected last Monday. Major trashing of data.

Problem 2: No backup since October 2, as the SQL Agent password had changed so the Agent wasn't started.

Problem 3: Started the agent and it deleted the last full backup because it was older than 4 weeks.

SO: I get the call for help.

Time passes.

More time passes.


After a dozen uploads and downloads of .mdf/.ldf and backup files (from August), I am able to recover much of the data using the fantastic tools from Red-Gate software:

Log Rescue: Identified what got injected, into where and when
SQL Compare: enabled me to create a schema script to replace the relationships I had to remove to fix the data
SQL Data Compare: Helped me replace the trash data with what it looked like in August.

Also, a shout-out to Narayana Vyas Kondreddi for his Search and Replace code that at least got the bad URL out of the data we couldn't fix.


The site is back up in read-only, and my buddy is reviewing all the code one page at a time.

Lessons learned:
Validate your inputs!
Back up your data, and verify it!

Not only does this one get fries, he gets 10+ hours of recovery effort at no charge, just for having a really cool site I want to see come back up :)

Kevin3NF