Tribal Sql PDF
Preview Tribal Sql
2 TRIBAL SQL By Dave Ballantyne, John Barnett, Diana Dee, Kevin Feasel, Tara Kizer, Chuck Lathrope, Stephanie Locke, Colleen Morrow, Dev Nambi, Bob Pusateri, Mark S. Rasmussen, Wil Sisney, Shaun J. Stuart, David Tate, Matt Velic First published by Simple Talk Publishing, 2013 3 Copyright for each chapter belongs to the attributed author, 2013 ISBN – 978-1-906434-80-9 The right of Dave Ballantyne, John Barnett, Diana Dee, Kevin Feasel, Tara Kizer, Chuck Lathrope, Stephanie Locke, Colleen Morrow, Dev Nambi, Bob Pusateri, Mark S. Rasmussen, Wil Sisney, Shaun J. Stuart, David Tate and Matt Velic to be identified as the author of his or her attributed chapter has been asserted by each in accordance with the Copyright, Designs and Patents Act 1988. All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written consent of the publisher. Any person who does any unauthorized act in relation to this publication may be liable to criminal prosecution and civil claims for damages. This book is sold subject to the condition that it shall not, by way of trade or otherwise, be lent, re-sold, hired out, or otherwise circulated without the publisher's prior consent in any form other than which it is published and without a similar condition including this condition being imposed on the subsequent publisher. Technical Review and Edit: Kat Hicks Editor: Tony Davis Cover Image: Rain Cao Typeset: Peter Woodhouse and Gower Associates 4 Table of Contents About This Book The Tribal Authors Computers 4 Africa The Tribal Reviewers and Editors The Tribal Sponsors Introduction SQL Server Storage Internals 101 Mark S. Rasmussen The Power of Deductive Reasoning Records Pages Investigating Page Contents Using DBCC Commands DBCC PAGE DBCC IND Heaps and Indexes Heaps Indexes Crunching the Numbers Index design Storage requirements Summary SQL Server Data Compression Bob Pusateri Compression Basics Overview of SQL Server compression Version and edition requirements Row Compression Page Compression Stage 1: Column prefix compression Stage 2: Page dictionary compression Additional overhead of page compression Where and When to Deploy Compression Data compression ratio vs. CPU overhead Performance monitoring Data usage patterns A Brief Review of Data Compression Syntax Combining Data Compression with Backup Compression and Transparent Data Encryption Summary Verifying Backups Using Statistical Sampling Shaun J. Stuart Why Backup Validation? Why Automate Backup Validation? Planning Considerations Automating Restores with Statistical Sampling: the Parts Retrieving a list of databases and backup files from each server 5 Performing test restores The magic happens here Summary Performance Tuning with SQL Trace and Extended Events Tara Kizer Is SQL Trace Still Relevant? How SQL Trace Works Profiler: pros, cons, and best practices Server-side trace Resolution Preparing for Migration to Extended Events Conclusion Windows Functions in SQL Server 2012 Dave Ballantyne Sliding Windows Defining the window frame extent Behavioral differences between RANGE and ROWS Performance differences between RANGE and ROWS Analytic Functions LAG and LEAD FIRST_VALUE and LAST_VALUE Statistical analysis functions Performance of analytic functions Summary SQL Server Security 101 Diana Dee Securing SQL Server, the Bigger Picture SQL Server Security Architecture, a Brief Overview The hierarchy of principals The hierarchy of securables Granting permissions in the hierarchy The three permission keywords Applying the Principle of Least Privilege Creating SQL Server logins Fixed server-level roles Server-level permissions Database users Database roles Granting Least Privileges in the database: schema: object hierarchy Taking the “Least Privileges” Challenge Example 1 Example 2 Answer 1 Answer 2 Summary and Next Steps Further reading What Changed? Auditing Solutions in SQL Server Colleen Morrow Auditing Options Not Covered C2 auditing 6 Policy Based Management Change Data Capture and Change Tracking The Basis of Auditing: Events and Event Classes SQL Trace Server-side trace for DDL auditing Using the default trace SQL Trace: pros and cons SQL Audit SQL Audit: how it works SQL Audit: terminology SQL Audit: creating the audit SQL Audit: creating the audit specification SQL Audit: viewing audit output SQL Audit: pros and cons Develop Your Own Audit: Event Notifications or Triggers Event notifications: how it works Event notifications: creating an event notification DDL and Logon triggers: how they work DDL and Logon triggers: creating triggers Event notifications and triggers: pros and cons Third-party Solutions Conclusion SQL Injection: How It Works and How to Thwart it Kevin Feasel My First SQL Injection Attack Attacking Websites Defending Websites Blacklists Why blacklists don't work Whitelists Parameterized queries Protecting Stored Procedures Other Forms of Defense QUOTENAME and REPLACE instead of sp_executesql Appropriate permissions Automated tools Web application firewalls Additional Resources Using Database Mail to Email Enable Your SQL Server John Barnett Getting Started with Database Mail Enabling Database Mail Configuring Database Mail Testing Database Mail Security requirements Using Database Mail in Your Own Applications Sending an email with a file attachment Sending an email with the results of a T-SQL statement Sending an email to multiple recipients Producing customized email alerts Reporting On Success or Failure of SQL Server Agent Jobs Enable a database mail profile for alert notification Designate a Fail-safe operator 7 Configure an operator Configuring notifications for Agent jobs Provide a Real-time Notification System for SQL Server Alerts Troubleshooting Database Mail Common problems Interrogating the Database Mail system views Maintaining the Database Mail log table The SSMS Database Mail log Summary Taming Transactional Replication Chuck Lathrope Transactional Replication Architecture Transactional Replication Use Cases Other Types of Replication Snapshot replication Merge replication Transactional replication with updating subscriptions Peer-to-peer transactional replication When Not to Use Replication Deeper into Transactional Replication Publishers, publications and articles The Distributor The Subscribers The Replication Agents Transactional Replication Requirements Transactional Replication Walk-through Configuring the Distributor Configuring the Publisher and publication Configuring the Subscriber Tuning Query Performance on the Subscriber Monitoring Replication with ReplMon Subscription Watch List Replication latency and tracer tokens Getting details of errors Configuring Alerts Performance Tuning Server and database file configuration and tuning Tuning replication Troubleshooting Replication Finding more details on an error Using verbose error output Finding large batches Restoring accidentally dropped replication objects Reinitializing Subscribers Summary Building Better Reports Stephanie Locke Key Report Development Skills Communication Technical skills and versatility Report writer, Business Intelligence (BI) specialist or data scientist? The Fundamentals of Good Report Design Requirements gathering 8 Initial visualization Designing tables and graphs Choosing the Right Reporting Tools Reporting in the Microsoft stack Predicting the future Outside the Microsoft stack Who decides and, most importantly, who pays? A personal story Summary Communication Isn't Soft Matt Velic The Written Word Read more to write better Write right now The Spoken Word A different kind of audience Lunch-and-learn User groups, SQL Saturday and beyond Practice Pays Off Self-belief Communicating expertise Practice Makes Perfect Guerrilla Project Management for DBAs David Tate A DBA's Crazy Workload A DBA's Place in the Organization The Dark Side of Being a DBA The Shield of Project Management Common project management terms cheat sheet Specific defensive moves Offensive project management Onwards Agile Database Development Dev Nambi Agile 101: A History Agile 201: Implications ABC – Automate, Balance, and Communicate Automation Balance Communicate, constantly DDT – Design, Deployments and Tests Design Deployments Tests Conclusion Nine Habits to Secure a Stellar Performance Review Wil Sisney Habit 1: Work Your Tail Off Habit 2: Establish the Parameters for Your Success Habit 3: Work with Vision 9 Habit 4: Train to Gain an Edge Habit 5: Stand on the Shoulders of Giants Habit 6: Control the Headlines Habit 7: Write a Self-Appraisal that Sparks Memory of Success Habit 8: Use Your Review to Negotiate Rewards Habit 9: Don't Rest on Past Success 10
The list of books you might like
