Are You Smarter Than An MCM?

1. Are You Smarter Than An MCM?

2. Meet the Masters Pam (Pammy) Lahoud Senior Premier Field Engineer (PFE) James/Jimmy (Buckaroo) May Senior Program Manager (CAT) Cindy (Cinister) Gross Senior Premier Field Engineer (PFE) Robert (Robby) Davis Program Manager SQL Certified Masters Program DBA-414-M| Are You Smarter than an MCM

3. Microsoft Certified MasterSQL Server

4. Microsoft Certified Master Strategic Value of MCM Over 80% of MCMs report increases in: • Project success rate* • Customer return rate* • Annual revenue* • Billable rate* *Source: MSL Survey Conducted November 2010

5. Microsoft Certified Master Community feedback on exams “…after taking this exam today I can tell you that the integrity of this exam is intact. ” -Blogger on NetworkWorld, Dec 17, 2010 “…I will tell you it is by far the hardest Microsoft Exam I’ve taken.  Even harder I think than the Business Intelligence exams which were very tough.” -Blogger on IT Knowledge Exchange, Dec 17, 2010 “…the exam is tough.  Anyone that thought that the changes were going to inappropriately lower the bar for this certification should guess again.” -Blogger, SQLServerPedia, Nov 22, 2010

6. Are You Smarter Than an MCM? History Math Geography Writing

7. Question We hear that history repeats itself. How do you make sure only part of history is repeated without having to repeat all of history?

8. Answer Initialize the subscriber from a filegroup backup.

9. Problem: Very large database (5 TB) with a small percentage of tables (20% or ~ 1 TB) to be replicated. How would you handle this?

10. 5 TB Database w/ lots of tables

11. 5 TB Database w/ lots of tables System Tables

12. 5 TB Database w/ lots of tables System Tables Tables to be Replicated (~ 1 TB)

13. Options: Initialize from snapshot? Do you really want to generate a snapshot of 1 TB of data? Initialize from backup? Do you really want to backup, copy, and restore 5 TB of data? And then manually delete the other 80% of tables?

14. Solution: Initialize from backup …

15. Solution: Initialize from backup … … a filegroup backup.

16. Initialize From Backup: BOL Says • Books online says: “… any recent backup can be used if it was taken after the publication was enabled for initialization with a backup.”

17. Initialize From Backup: BOL Says • Books online also says: “A backup contains an entire database; therefore each subscription database will contain a complete copy of the publication database when it is initialized. … It is the responsibility of the administrator or application to remove any unwanted objects or data after the backup has been restored.”

18. 5 TB Database w/ lots of tables System Tables Tables to be Replicated (~ 1 TB)

19. Initialize From Backup: Filegroup • Filegroup backups • Must include Primary filegroup • All backed up together to ensure same base LSN • Includes enough log file to restore to consistent state • Two options • Read/Write filegroups • Filegroups by name • Relies on partial database availability • Online piecemeal restore • Enterprise only feature

20. Answer Demo

21. Are You Smarter Than an MCM? History Math Geography Writing

22. Question Pammy is trying to do her homework but it’s taking too long to generate her list of assignments.  At what point is she able to generate her homework list in a reasonable amount of time?

23. Answer The Tipping Point

24. The Tipping Point • The point at which the SQL Server optimizer will choose a table scan over a non-clustered index with a lookup • Rows read / Total Pages ≈ 30% • Meant to minimize logical reads • Can vary based on hardware, system settings, parallelism etc. DBA-414-M| Are You Smarter than an MCM

25. The Tipping Point: Issues • Small data changes can cause big query plan changes • Greater exposure to parameter sniffing issues • Unpredictable behavior DBA-414-M| Are You Smarter than an MCM

26. The Tipping Point: What to do • Use INCLUDE to create covering indexes • Consider a Query Hint • OPTION(RECOMPILE) • OPTION(OPTIMIZE FOR @variable = <value>) • OPTION(OPTIMIZE FOR UNKNOWN) • Nothing – sometimes those scans really are faster! DBA-414-M| Are You Smarter than an MCM

27. Answer Demo

28. Are You Smarter Than an MCM? History Math Geography Writing

29. Question A boat is traveling at 40 knots from China to the US. How long does it take to transfer 2.5 GB of Azure data in 2000 tables from an Asian to a US data center? In other words: How Do We Get There From Here?

30. Answer It depends

31. Wherever you go, there you are. —Buckaroo Bonzai

32. SQL Azure True-Life Scenario: • Buckaroo is based in Beijing. He’s parachuted with his Chinese customers into Redmond’s world-famous SQL CAT Customer Lab. • They need to copy a 2,000 table 2.5GB SQL Azure database from an Asian data center to a US data center. • Their customers & the MSFT VP executive sponsor are whistling the Jeopardy! theme—expediency is critical! SQL CAT to the rescue! DBA-414-M| Are You Smarter than an MCM

33. Requirements: • Buckaroo has been using SQL Server for over a decade, but he’s not necessarily facile with all the fancy tools & features—he’s a command line geek! • Many primary keys are comprised of Chinese characters • Third-party tools require a management exception. Only RTM products are permitted. • What's the most expedient way for Buckaroo togit-'r-done? DBA-414-M| Are You Smarter than an MCM

34. Multiple Guess , er ah…….. Brainstorming SneakerNet: Take a slow boat to China, backup their data to fast USB, smuggle through customs, & return. Find a willing, reasonably priced, competent hacker to access the South Central Asia SQL Azure data center. SQL Azure Migration Wizard by George Huey SQL Azure Data Sync Service beta CREATE DATABASE...AS COPY OF... statement SSIS bcp

35. Responses SneakerNet: Per requirements, non-compliant with "expediency". Hacker: Clearly contrary to Microsoft Standards of Business Conduct protocols. Besides, DefCon was in August. SQL Azure Migration Wizard: Per requirements, 3rd party tools, even open source, require an exception as well as time to master the tool. Data Sync Services beta: Per requirements, only RTM products are permitted. Primary keys with non-standard collations are incompatible with Data Sync Services beta. CREATE DATEBASE...AS COPY OF... command: BOL correctly states this operation is permitted only when the Azure databases reside in the same data center. SSIS: Alas, not only is Buckaroo not savvy with the latest-&-greatest features of SQL, time spent optimizing could be non-compliant with the expediency requirement. bcp: Bingo! An oldie-but-goodie; tried-&-true; easy-peasy. Expedient, easy, well-known, robust solution that SQL old-timers such as Buckaroo have in their bag of tricks. It’s easy to Script-o-Magically® transmogrify of thousands of tables in one swell foop.

36. SQL Azure Demo • Source: DDL & Load • Source: Script-o-magically generate bcp out • Source: Execute bcp out • Source: Script-o-magically generate bcp in (Yes, from the Source) • Destination: DDL • Destination: Execute bcp in

37. Are You Smarter Than an MCM? History Math Geography Writing

38. Question Cindy has ADHD. In the past she we able to keep up with multiple tasks at once, but lately she has slowed down. Why?

39. Answer Excessive VLFs

40. VLFs • VLF = Virtual Log File or the internal boundaries within the transaction log • 100s might be ok, 1000s is probably bad • More = slower replication, mirroring, CDC, recovery, backup/restore DBA-414-M| Are You Smarter than an MCM

41. Answer Demo

42. VLF Lessons Learned • Pre-size, avoid autogrow, alert on growth • Proper autogrow settings • If too many, shrink and regrow • Otherwise don’t shrink • Did I mention pre-sizing? DBA-414-M| Are You Smarter than an MCM

43. Hands-on Labs Get experienced through self-paced & instructor-led labs on our cloud based lab platform - bring your laptop or use HP provided hardware Microsoft SQL Server Clinic Work through your technical issues with SQL Server CSS & get architectural guidance from SQLCAT Expert Pods Meet Microsoft SQL Server Engineering team members & SQL MVPs Microsoft Product Pavilion Talk with Microsoft SQL Server & BI experts to learn about the next version of SQL Server and check out the new Database Consolidation Appliance Room611 ExpoHall 6th Floor Lobby Room 618-620 DBA-414-M| Are You Smarter than an MCM

44. Complete the Evaluation Form to Win! Sponsored by Dell • Win a Dell Mini Netbook – every day – just for submitting your completed form. Each session evaluation form represents a chance to win. • Pick up your evaluation form: • In each presentation room • Online on the PASS Summit website • Drop off your completed form: • Near the exit of each presentation room • At the Registration desk • Online on the PASS Summit website DBA-414-M| Are You Smarter than an MCM

45. Thank you for attending this session and the 2011 PASS Summit in Seattle