The Best of SQLServerCentral

High Performance SQL Server

The Best of Vol 7

ISBN: 978-1-906434-35-9

The Best of ? Vol.7

The Best of Vol.7

Aaron Akin Adam Aspin Adam Haines Alceu Rodrigues de Freitas Junior Bennie Haelen Bill Richards Boyan Penev Brian Kelley Chad Miller Chris Kinley David Dye David McKinney David Poole Deepa Gheewala Divya Agrawal Drew Salem Francis Rodrigues Gail Shaw Glen Cooper Glen Schwickerath Gregor Borosa Gus "GSquared" Gwynne

Ian Stirk Jack Corbett Jacob Sebastian Jagan Kumar Jason Shadonix

Joe Celko Johan Bijnens Jonathan Kehayias

Ken Simmons Ladislau Molnar Lanre Famuyide

Louis Roy Marios Philippopoulos

Martin Cremer Michael Cape Michelle Ufford Mike Walsh Nicholas Cain Oleg Netchaev

Paul Els Phil Factor Ranga Narasimhan R. Barry Young Renato Buda Robert Cary Roy Ernest Rudy Panigas Soloman Rutzky Sylvia Moestl Vasilik Thom Bolin Tim Mitchell Thomas LaRock Timothy A Wiseman TJay Belt Vincent Rainardi Wagner Crivelini Wayne Sheffield Zach Mattson

The Best of ? Vol.7

The Best of ? Vol. 7

Red Gate Books Newnham House Cambridge Business Park

Cambridge CB2 0WZ United Kingdom

ISBN 978-1-906434-35-9

Copyright Notice

Copyright 2009 by Simple Talk Publishing. All rights reserved. Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced under the Copyright Act of 1976. No part of this publication may be reproduced in any form or by any means or by a database retrieval system without the prior written consent of The Central Publishing Group. The publication is intended for the audience of the purchaser of the book. This publication cannot be reproduced for the use of any other person other than the purchaser. Authors of the material contained in this book retain copyright to their respective works.

Disclaimer

The Simple-Talk Publishing, , and the authors of the articles contained in this book are not liable for any problems resulting from the use of techniques, source code, or compiled executables referenced in this book. Users should review all procedures carefully, test first on a non-production server, and always have good backup before using on a production server.

Trademarks

Microsoft, SQL Server, Windows, and Visual Basic are registered trademarks of Microsoft Corporation, Inc. Oracle is a trademark of Oracle Corporation.

Editors

Steve Jones

Cover Art

Matthew Tye

Typeset

Alice Smith

The Best of ? Vol.7

Table of Contents

Introduction .................................................................................................. 6 SQL Server Preproduction Tasks.................................................................7 Scope: The drastic caveat with Logon Triggers...........................................10 The Date Dimension in Analysis Services...................................................15 SCOME - Centralize Monitoring with - Part 1...........................22 Monitoring Changes in Your Database Using DDL Triggers .....................27 Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part I.................................................................................................30 9 Things to Do When You Inherit a Database.............................................33 Cursors for T-SQL Beginners......................................................................36 DAC - What to Execute when Connected?..................................................42 Getting a Clue about Your Databases..........................................................43 Ordering Tables to Preserve Referential Integrity .......................................47 Creating a recycle bin for SQL Server 2005\2008.......................................52 Using SQL Profiler to Resolve Deadlocks in SQL Server ..........................62 What SQL Statements Are Currently Executing?........................................67 Duplicate Records using SQLCMD.............................................................70 Automating Excel from SQL Server............................................................75 Moving Indexes ...........................................................................................80 On Indexes and Views .................................................................................85 Missing Indexes in SQL Server 2005 ..........................................................89 Using the Script Component with Multiple Outputs ...................................91 SSIS and Stored procedures using temp tables............................................99

iii

The Best of ? Vol.7

SSIS Custom Error Handling.......................................................................105 Simple Steps to Creating SSIS Package Configuration File........................110 Using Checkpoints in SSIS (Part 1).............................................................117 Reporting Services: Read Data from SSAS and SQL Server in One Dataset..................................................................................................121 SQL Server 2008 Mirroring Testing............................................................135 On-Call Duties .............................................................................................140 Configuring Replication for Partitioned Tables Using T-SQL ....................143 Performance Implications of Database Snapshots .......................................147 Filtering Unneeded Dimension Members in PerformancePoint Filters ...........................................................................................................151 Powering up DTS with PerlDTS..................................................................153 Loading Data with Powershell.....................................................................159 Add Styles to Your Reporting Services Reports..........................................165 Configuring Kerberos Authentication..........................................................171 Use Operations Manager to Monitor Your SQL Agent Jobs.......................177 Oracle for the SQL Server Guy - Instances and Databases .........................181 Default trace - A Beginner's Guide..............................................................187 Streaming Data into SQL Server 2008 from an Application .......................194 SQL Server 2008 and Data Compression ....................................................201 The FILESTREAM Data Type in SQL Server 2008...................................205 Investigating the new Spatial Types in SQL Server 2008 - Part 1...............212 SQL Server 2008 SSMS Enhancements - Debugging Support ...................219 Deploying Scripts with SQLCMD...............................................................228 Real-Time Tracking of Tempdb Utilization Through Reporting Services ........................................................................................................ 234

iv

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download