All-in-One is All You Need Get complete coverage of all three Microsoft Certified IT Professional database developer exams for SQL Server 2005 in this comprehensive volume. Written by a SQL Server expert and MCITP, this definitive exam guide features learning objectives at the beginning of each chapter, exam tips, practice questions, and in-depth explanations. Detailed and authoritative, the book serves as both a complete certification study guide and an essential on-the-job reference. Get full details on all exam topics including how to: Install and configure SQL Server 2005 Manage database design Use Transact-SQL and XML Work with functions, triggers, and CLR integration Optimize, monitor, and secure databases Create stored procedures Handle disaster recovery Work with Service Broker, Web Services, and MARS Use SQL Server Reporting Services and Notification Services Manage locks, deadlocks, and cursors Transfer data using Replication and SQL Server Integration Services The CD-ROM features: Six full practice exams-two for each exam: 70-431, 70-441, & 70-442 Scripts from the step-by-step exercises in the book Video training clips from the author Complete electronic book

Microsoft SQL Server 2005 Management and Administration, based on Service Pack 2, addresses the challenges database administrators regularly encounter on SQL Server 2005 by providing detailed guidance in the areas of management, administration, security, and monitoring. With coverage of the new features and functionality of SQL Server 2005 Service Pack 2, this book is designed to be comprehensive, resulting in something for all database administrators—from simple tips to tactical solutions. Microsoft SQL Server 2005 Management and Administration goes far beyond the basic installation and setup information found in many other resources. The book looks at day-to-day administration, best practices, tips, and step-by-step configurations based on real-world examples found in the industry. Unlike others, this book includes not only administration and management details on the Database Engine, but also coverage of other SQL Server 2005 components often overlooked, including Analysis Services, Reporting Services, and more. Understand how to… Configure and tune the Database Engine, Reporting Services, Analysis Services, Integration Services, and Notification Services Harden a SQL Server implementation Implement SQL Server highavailability alternatives, such as Failover Clustering, Log Shipping, Database Mirroring, and Replication Monitor a SQL Server 2005 infrastructure with Operations Manager 2007, including how to configure the SQL Server Management Pack and install Operations Manager 2007 Automate SQL Server routine maintenance Encrypt SQL Server data and communications, including setting up a Certificate Authority Performance tune and troubleshoot a SQL Server environment Create Integration Services packages and transfer data On the Web: Download bonus chapters from www.informit.com/title/9780672329562 Introduction 1 Part I Administering SQL Server Components 1 Administering SQL Server 2005 Database Engine 11 2 Administering SQL Server 2005 Analysis Services 67 3 Administering SQL Server 2005 Reporting Services 99 4 Administering SQL Server 2005 Notification Services 149 5 Administering SQL Server 2005 Integration Services 183 6 Administering SQL Server Replication 209 Part II Managing SQL Server 2005 7 Conducting a SQL Server 2005 Health Check 261 8 SQL Server 2005 Maintenance Practices 289 9 Managing and Optimizing SQL Server 2005 Indexes 317 10 Managing Full-Text Catalogs 353 11 Creating Packages and Transferring Data 387 Part III Securing the SQL Server Implementation 12 Hardening a SQL Server 2005 Environment 435 13 Administering SQL Server Security 469 14 Encrypting SQL Server Data and Communications 509 The following chapters are located online: Part IV SQL Server 2005 Overview (located online) 15 SQL Server 2005 Technology Primer 537 16 Tools of the Trade 571 Part V Disaster Recovery and High Availability (located online) 17 Backing Up and Restoring the SQL Server 2005 Environment 597 18 Administering and Managing Failover Clustering 647 19 Administering and Managing Database Mirroring 691 20 Administering and Managing Log Shipping 721 Part VI Monitoring and Troubleshooting SQL Server (located online) 21 Monitoring SQL Server 2005 753 22 Performance Tuning and Troubleshooting SQL Server 2005 787 Appendix SQL Server 2005 Management and Administration 821

Dive deep into the internals of query tuning and optimization in SQL Server 2005 with this comprehensive reference. Understanding the internals of SQL Server helps database developers and administrators to better create, access, and effectively process information from enterprise data. Written by experts on SQL Server, this volume from the Inside Microsoft SQL Server series of books focuses on query tuning and optimization. You'll take an in-depth look at the best ways to make queries more efficient and effective, while maximizing existing resources. Includes extensive code samples and table examples to help database developers and administrators understand the intricacies and help promote mastery of query tuning and optimization.

SQL Server Compact Edition is a slimmed down version of SQL Server that you can use on Pocket PCs, Smart phones, Tablet PCs, and Desktops. With this book, you will learn how to build applications for resource constrained devices. You’ll discover how the SQL Server Compact Edition database can also be used as an embedded in-proc database for desktop-based applications. You’ll learn how to manage the SQL Server Compact Edition database using SQL Server Management Studio, Visual Studio, and Query Analyzer. Laptops, Tablet PCs, and mobile devices enable information workers to access enterprise data from remote locations. With the step-by-step exercises provided in this book, you’ll learn how to synchronize data between SQL Server and SQL Server Compact Edition. You’ll also learn Remote Data Access–a lightweight method to exchange data between SQL Server and SQL Server Compact Edition. Also in this book are code samples to help you write database applications for mobile devices and for desktop PCs. Throughout the book you’ll find expert tips for building and deploying enterprise applications. Understand how to… Build mobile and desktop applications using SQL Server Compact Edition Set up the development and production environment with Visual Studio, SQL Server, and SQL Server Compact Edition Optimize performance and plan for security Manage databases using SQL Server Management Studio, Visual Studio, and Query Analyzer Develop applications using ADO.NET and OLEDB provider for SQL Server Compact Edition Upgrade from earlier versions of SQL CE, .NET Compact Framework, and Visual Studio Use Merge Replication to synchronize data between SQL Server and SQL Server Compact Edition Exchange data between SQL Server and SQL Server Compact Edition using Remote Data Access Introduction 1 1 Getting Started with SQL Server 2005 Compact Edition 7 2 Platform Support and Installation35 3 Getting to Know the Tools 63 4 Managing the SQL Server 2005 Compact Edition Database 109 5 Defining Database Structure 131 6 Manipulating the Database 175 7 Programming SQL Server 2005 Compact Edition with ADO.NET 205 8 Introducing Native Access 247 9 Using Operators in SQL Server 2005 Compact Edition 279 10 Using the Built-In Functions 295 11 Upgrading from a Previous Version 327 12 Synchronizing Data with Merge Replication 345 13 Synchronizing Data with Remote Data Access 411 14 Securing the SQL Server 2005 Compact Edition Database 459 15 SQL Server 2005 Compact Edition Performance Tuning 505 Index 551

This ebook does not provide access to the CD-ROM files. Covering Microsoft's brand-new SQL 2005 administrator exam, this study guide walks you through setting up, maintaining, and troubleshooting database solutions. You'll review key topics such as defining high-availability solutions, automating administrative tasks, defining security solutions, monitoring and troubleshooting the database server, and designing and executing deployments. Plus, the CD-ROM features leading exam prep software with an assessment test, test engine of case study practice questions, and electronic flashcards.

Teach yourself the fundamentals of SQL Server Integration Services—one step at a time. This practical, learn-by-doing tutorial delivers the guidance you need to transform and consolidate data—and build solutions that support your business intelligence needs. Discover how to: Design and execute packages that transform data between files and relational databases Configure connection managers to access other data sources Create data flows that alter, split, match, and merge data Develop event-handlers and monitor package performance Encrypt sensitive information and control access to packages Customize script code to automate Control Flow and Data Flow tasks Debug, troubleshoot, and optimize packages CD features: All practice exercises Sample databases Fully searchable eBook For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

While building on the skills you already have, Expert SQL Server 2005 Development will help you become an even better developer by focusing on best practices and demonstrating how to design high-performance, maintainable database applications. This book starts by reintroducing the database as a integral part of the software development ecosystem. You'll learn how to think about SQL Server development as you would any other software development. For example, there's no reason you can't architect and test database routines just as you would architect and test application code. And nothing should stop you from implementing the types of exception handling and security rules that are considered so important in other tiers, even if they are usually ignored in the database. You'll learn how to apply development methodologies like these to produce high-quality encryption and SQLCLR solutions. Furthermore, you'll discover how to exploit a variety of tools that SQL Server offers in order to properly use dynamic SQL and to improve concurrency in your applications. Finally, you'll become well versed in implementing spatial and temporal database designs, as well as approaching graph and hierarchy problems.

Just as every business needs to effectively employ business intelligence (BI) to stay competitive, every IT professional needs to master BI to stay employed in this fastest-growing segment of information technology. Foundations of SQL Server 2005 Business Intelligence is the quickest path to understanding BI, and it is essential reading for all who work with SQL Server 2005. It is written from a practical perspective, perfect for anyone who uses the tools in SQL Server 2005s extraordinarily rich BI product suite. This book explains how best to use Analysis Services, SQL Server Integration Services, SQL Server Reporting Services, and SQL Server Data Mining. It also describes best practices for implementing end-to-end BI solutions in small, medium, and large business environments. And it provides important information about integrating BI with various client tools, including Excel, Business Scorecards, Proclarity, and SharePoint Portal Server. Developers, end users, and even managers will find this an enlightening guide to the power and promise of SQL Server 2005 BI.

Database professionals will find that this new edition aids in mastering the latest version of Microsoft’s SQL Server. Developers and database administrators (DBAs) use SQL on a daily basis in application development and the subsequent problem solving and fine tuning. Answers to SQL issues can be quickly located helping the DBA or developer optimize and tune a database to maximum efficiency. Basic questions are easily located on the topics of filtering, sorting, operators, conditionals, pseudo columns, single row functions, joins, grouping functions, sub queries, composite queries, hierarchies, flashback queries, parallel queries, expressions and regular expressions. Assistance on DML, data types (including collections), XML, DDL for basic database objects such as tales, views and indexes, partitioning, and security is also considered. * Identifies the most common issues DBAs face day to day for easy reference *Provides DBAs with solutions actually used by the authors in enterprise environments to resolve common and specialized problems to optimization issues. * Addresses issues that have been introduced by new features which can add more control but reduce performance.

SQL Server 2005 Integration Services (SSIS) lets you build high-performance data integration solutions. SSIS solutions wrap sophisticated workflows around tasks that extract, transform, and load (ETL) data from and to a wide variety of data sources. This Short Cut begins with an overview of key SSIS concepts, capabilities, standard workflow and ETL elements, the development environment, execution, deployment, and migration from Data Transformation Services (DTS). Next, you'll see how to apply the concepts you've learned through hands-on examples of common integration scenarios. Once you've finished this Short Cut, you'll have the background and understanding you need to start building your own SSIS integration solutions.

Microsoft SQL Server Analysis Services provides fast access to data by means of multidimensional data structures and the multidimensional query languag MDX. Analysis Services provides the capability to design, create, and manage multidimensional cubes based on data warehouse tables, and it serves as the foundation for the Microsoft Business Intelligence strategy. Microsoft SQL Server 2005 Analysis Services gives the reader insight into the way Analysis Services functions. It not only explains ways to design and create multidimensional objects, databases, dimensions, and cubes, but also provides invaluable information about the reasons behind design decisions made by the development team. Here's what you will find inside: Understand the key concepts of multidimensional modeling Explore the multidimensional object model and its definition language Learn the main concepts of the MDX language and gain an in-depth understanding of advanced MDX concepts Understand the mechanisms of integrating multidimensional and relational databases Learn how to build client applications to access data in Analysis Services Examine server architecture, including main data structures, data processing, and query resolution algorithms Gain a deep understanding of the internal and external protocols for data transfer, including the XML/A protocol Explore how Analysis Services manages memory Explore the security model, including role-based security, code-access security, and data security Discover how to monitor and manage Analysis Services All the code for the sample database used in the book can be found at www.informit.com/title/0672327821.

This practical, hands-on book offers deep, thorough coverage of the internals of architecture and resource management in SQL Server 2005, focusing on the Storage Engine. The book features extensive code samples and table examples.

This comprehensive book guides readers through Microsoft's brand-new SQL 2005 administrator exam: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005 (Exam 70443) Addresses the database tasks that the MCITP exam focuses on such as design, development, deployment, optimization, maintenance, and support Reviews key topics, including defining high availability solutions, automating administrative tasks, defining security solutions, monitoring and troubleshooting the database server, and much more The CD-ROM features leading-edge exam prep software with test engine plus hundreds of practice questions and electronic flashcards Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

In-depth coverage of Microsoft’s powerful new data integration tool Learn to maximize the features of SQL Server 2005 Integration Services from this essential guide. Hands-On Microsoft SQL Server 2005 Integration Services makes learning SSIS easy through the use of extensive real-world exercises structured around every component within the tool. You will learn to collect corporate data from various sources and transform that data for analysis, mining, and reporting.

SQL Server 2005 is Microsoft’s next-generation data management and analysis solution that delivers enhanced scalability, availability, and security features to enterprise data and analytical applications while making them easier to create, deploy, and manage. Now you can teach yourself how to design, build, test, deploy, and maintain SQL Server databases—one step at a time. With STEP BY STEP, you work at your own pace through hands-on, learn-by-doing exercises. Instead of merely focusing on describing new features, this book shows new database programmers and administrators how to use specific features within typical business scenarios. Each chapter puts you to work, providing a highly practical learning experience that demonstrates how to build database solutions to solve common business problems.

The MCTS 70-431 Exam Cram is a must-have resource in your MCTS 70-431 exam preparation. The Exam Cram focuses on exactly what you need to know to get certified. This includes features like: Complete coverage of all 70-431 exam objectives Exam alerts, practice questions, notes, tips, sidebars, cautions, test-taking strategies, and time-saving tips Access to MeasureUp practice questions and innovative testing software The popular Cram Sheet tear card, giving you the key facts you should review before you enter the testing center Insight from author Thomas Moore, a Microsoft Certified Trainer (MCT) and SQL Server expert who has helped hundreds of students earn their SQL Server certifications

All medium to large companies have to deal with data being stored in a number of databases, spreadsheets and proprietary applications. Bringing all this data together into one system can be useful to analyzing the business; but can also be quite difficult to do. Integration Services is the next big bet in Microsoft's business intelligence lineup, providing you with true enterprise extract transform and load features (ETL). Microsoft SQL Server 2005 Integration Services concentrates on providing you with real world solutions to real world problems, giving you a head start on various projects. If you are only evaluating Integration Services, you'll be able to focus on the early chapters with the option of drilling down into details in the later chapters where some of the more complex concepts will be discussed in detail. The interesting sidebars peppered throughout the book provide insight into the processes, people and decisions that yielded the final IS product.

SQL Server 2005 is Microsoft’s next-generation data management and analysis solution that delivers increased security, scalability, and availability to enterprise data and analytical applications while making them easier to create, deploy, and manage. This book shows readers with fundamental SQL Server skills, as well as new-to-topic but experienced database developers, techniques to design, build, test, deploy, and maintain better SQL Server databases. The format is a hands-on, sequential, developer’s companion, providing beyond-the-basics guidance. This book is not a feature-driven reference manual, but a highly practical learning experience demonstrating how to build database solutions to solve business problems.

“Buck Woody’s new book will help new and experienced users alike in getting productive with SQL Server 2005 quickly. His special attention to security and performance make this an exceptional reference that is sure to remain relevant for some time.” —Chip Andrews, CISSP, MCDBA, Director of Research and Development Special Ops Security, Inc. “SQL 2005 is the most exciting upgrade to come out of Redmond in years; this book will help you get up and running quickly to take full advantage of it! If you are looking for a book about upgrading to SQL 2005, look no further, this one is all you need. It gives you all you need to take advantage of everything SQL 2005 has to offer, and that is a lot!” —Doug Ellis, Area Technical Manager, Cognos “Buck Woody provides insight into administering SQL Server 2005 that all developers and database administrators will find useful; the book assumes very little knowledge by providing ‘DBA 101’ sections which will not alienate the experienced reader while providing the necessary introduction to topics for those less experienced.” —Doug Holland, Visual C# MVP, Precision Objects “SQL Server 2005 is a landmark release and this title does an awesome job of hitting everything new in SQL 2005. It covers new and improved administration tools, and contains an overview of Notification Services, SQL Server Service Broker, Reporting Services, Integration Services, and Analysis Services. As a result, you’ll be able to work on your own SQL Server 2005 solutions quickly and painlessly. Ultimately, this book provides you with a starting point, a roadmap, and then the tools and knowledge to exploit this powerful new database server to its fullest power. So, start your computer and get ready to learn SQL Server 2005!” —Alexzander N. Nepomnjashiy, SQL Server DBA, NeoSystems NorthWest “A solid introduction to administering SQL 2005. The ‘DBA 101’ sections provide useful tips for the new DBA, good review for the experienced DBA, and the 101 label enables the seasoned DBA to skip basic material and jump ahead to where the REAL action is…. Buck’s conversational writing style makes this an enjoyable and refreshing contrast to the majority of tech books written these days.” —Brad Russell, MCDBA, Visionary Integration Professionals The Complete, Practical, 100% Useful Guide for Every SQL Server 2005 DBA! This book has one goal: to help database administrators and their managers run SQL Server 2005 with maximum efficiency, reliability, and performance. Renowned SQL Server expert Buck Woody covers every facet of database administration, from installation and configuration through maintaining enterprise-class business intelligence environments. If you’re a new SQL Server administrator, Buck will help you master core tasks rapidly—and avoid costly mistakes that only show up when it’s too late. If you’re familiar with previous versions of SQL Server, he’ll get you up-to-speed fast on everything new that matters, from high availability improvements and Integration Services to the SQL Server Management Studio. Whatever your background, you’ll find best practices, real-world scenarios, and easy-to-use automation scripts—all grounded in Buck’s unsurpassed SQL Server knowledge. • Timesaving tips, practical applications, and start-to-finish examples throughout • Understand the key “decision points” at every stage of your SQL Server deployment • Perform day-to-day maintenance more efficiently, from hotfixes to backup/recovery • Automate maintenance tasks with the SQL Server Agent • Establish and audit security on SQL Server 2005 and the underlying Windows Server platform • Monitor and optimize SQL Server: baselining, profiling, index strategies, query refactoring, and more • Maximize availability using replication, clustering, mirroring, and SQL Server’s new online indexing • Leverage important new improvements in Integration Services and Reporting Services • Use Analysis Services to support enterprise-class Business Intelligence deployments • Even set up a simple configuration of SQL Server 2005 for free—legally! Foreword xviii Chapter 1: Installation and Configuration 1 Chapter 2: Architecture and Tools 35 Chapter 3: Maintenance and Automation 99 Chapter 4: Security 173 Chapter 5: Monitoring and Optimization 237 Chapter 6: High Availability 295 Chapter 7: Notification Services and the Service Broker 365 Chapter 8: Integration Services 399 Chapter 9: Reporting Services 451 Chapter 10: Analysis Services 515 Appendix A: Upgrading to SQL Server 2005 581 Index 605

"I come from a T-SQL background, so when I first laid my eyes on SQL Server 2005, I was shocked--and then, I was scared! I didn't have a CLR or XML background and suddenly had an urgent need to learn it. SQL Server 2005 is too big of a release to learn from the books online. Fortunately, now there is a book for developers who need to go from SQL Server 2000 to SQL Server 2005 and to do it as painlessly as possible. Basically, it's one-stop shopping for serious developers who have to get up to speed quickly. I'll keep this one on my desk--not on my bookshelf. Well done, Bob and Dan!" --Dr. Tom Moreau SQL Server MVP and Monthly Columnist SQL Server Professional, Brockman Moreau Consulting Inc. "A SQL book truly for developers, from two authorities on the subject. I'll be turning to this book first when I need to understand a component of SQL Server 2005." --Matt Milner Instructor Pluralsight "An excellent book for those of us who need to get up to speed on what's new in SQL Server 2005. The authors made sure this book includes the final information for the release version of the product. Most other books out now are based on beta versions. It covers key areas from XML and SQLCLR to Notification Services. Although the wide variety of information is great, my favorite part was the advice given on when to use what, and how performance is affected." --Laura Blood Senior Software Developer Blue Note Computing, Inc. "SQL Server 2005 is a massive release with a large number of new features. Many of these features were designed to make SQL Server a great application development platform. This book provides comprehensive information about the SQL Server features of most interest to application developers. The lucid text and wealth of examples will give a developer a clear understanding of how to use SQL Server 2005 to a whole new class of database applications. It should be on every SQL Server developer's bookshelf." --Roger Wolter Solutions Architect Microsoft Corporation "While there will be a lot of good books on SQL Server 2005 development, when people refer to the 'bible,' they'll be talking about this book." --Dr. Greg Low Senior Consultant Readify Pty Ltd "SQL Server 2005 is loaded with new features and getting a good overview is essential to understand how you can benefit from SQL Server 2005's features as a developer. Bob and Dan's book goes beyond enumerating the new SQL Server 2005 features, and will provide you with lots of good examples. They did a good job striking a balance between overview and substance." --Michiel Wories Senior Program Manager, SQL Server Microsoft Corporation Few technologies have been as eagerly anticipated as Microsoft SQL Server 2005. Now, two SQL Server insiders deliver the definitive hands-on guide--accurate, comprehensive, and packed with examples. starts where Microsoft's documentation, white papers, and Web articles leave off, showing developers how to take full advantage of SQL Server 2005's key innovations. It draws on exceptional cooperation from Microsoft's SQL Server developers and the authors' extensive access to SQL Server 2005 since its earliest alpha releases. A Developer's Guide to SQL Server 2005 You'll find practical explanations of the new SQL Server 2005 data model, built-in .NET hosting, improved programmability, SQL:1999 compliance, and much more. Virtually every key concept is illuminated via sample code that has been fully updated for and tested with the shipping version of the product. Key coverage includes Using SQL Server 2005 as a .NET runtime host: extending the server while enhancing security, reliability, and performance Writing procedures, functions, triggers, and types in .NET languages Exploiting enhancements to T-SQL for robust error-handling, efficient queries, and improved syntax Effectively using the XML data type and XML queries Implementing native SQL Server 2005 Web Services Writing efficient, robust clients for SQL Server 2005 using ADO.NET, classic ADO, and other APIs Taking full advantage of user-defined types (UDTs), query notifications, promotable transactions, and multiple active result sets (MARS) Using SQL Management Objects (SMO), SQL Service Broker, and SQL Server Notification Services to build integrated applications