Transcript Document

Are You Smarter
Than An MCM?
October 11-14, Seattle, WA
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
2
Microsoft Certified Master
SQL Server
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
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
Are You Smarter Than an MCM?
History
Math
Geography
Writing
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?
Answer
Initialize the subscriber from a
filegroup backup.
Problem:
Very large database (5 TB) with a small
percentage of tables (20% or ~ 1 TB) to be
replicated.
How would you handle this?
5 TB Database w/ lots of tables
5 TB Database w/ lots of tables
System Tables
5 TB Database w/ lots of tables
System Tables
Tables to be Replicated (~ 1 TB)
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?
Solution:
Initialize from backup …
Solution:
Initialize from backup …
… a filegroup backup.
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.”
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.”
5 TB Database w/ lots of tables
System Tables
Tables to be Replicated (~ 1 TB)
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
Answer Demo
Are You Smarter Than an MCM?
History
Math
Geography
Writing
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?
Answer
The Tipping Point
The Tipping Point
• The point at which the SQL Server optimizer
will choose a table scan over a nonclustered 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
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
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
Answer Demo
Are You Smarter Than an MCM?
History
Math
Geography
Writing
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?
Answer
It depends
Wherever you go, there you
are.
—Buckaroo Bonzai
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
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 to
git-'r-done?
DBA-414-M| Are You Smarter than an MCM
33
Multiple Guess
Brainstorming
, er ah……..
1. SneakerNet: Take a slow boat to China,
backup their data to fast USB, smuggle
through customs, & return.
2. Find a willing, reasonably priced, competent
hacker to access the South Central Asia SQL
Azure data center.
3. SQL Azure Migration Wizard by George Huey
4. SQL Azure Data Sync Service beta
5. CREATE DATABASE...AS COPY OF... statement
6. SSIS
7. bcp
Responses
1.
2.
3.
4.
5.
6.
7.
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 oldtimers 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.
SQL Azure Demo
1.
2.
3.
4.
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)
5. Destination: DDL
6. Destination: Execute bcp in
Are You Smarter Than an MCM?
History
Math
Geography
Writing
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?
Answer
Excessive VLFs
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
Answer Demo
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
Microsoft SQL
Server Clinic
Microsoft
Product Pavilion
Work through your
technical issues with SQL
Server CSS & get
architectural guidance
from SQLCAT
Talk with Microsoft SQL
Server & BI experts to
learn about the next
version of SQL Server
and check out the new
Database Consolidation
Appliance
Room 611
Expo Hall
Expert Pods
Meet Microsoft SQL
Server Engineering
team members &
SQL MVPs
6th Floor Lobby
Hands-on Labs
Get experienced through
self-paced & instructorled labs on our cloud
based lab platform bring your laptop or use
HP provided hardware
Room 618-620
DBA-414-M| Are You Smarter than an MCM
43
Complete the Evaluation Form
to Win!
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
Sponsored by Dell
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
44
Thank you
for attending this session and the
2011 PASS Summit in Seattle
October 11-14, Seattle, WA