While most database professionals have had some exposure to XML, few have actually embraced the potential of using XML in their relational design. This presentation focuses on a basic introduction to XML. This session will cover the basic structure of XML (it's more than text with tags), and the basic XQuery methods in SQL Server. In addition, specific use case scenarios will be introduced including how to shred an XML document, moving data as XML, and other tips and tricks. Driven from the persepctive of the average database professional, this talk is designed to "get your feet wet" in handling XML data within SQL Server 2008. While some advanced topics will be referenced (such as FLWOR queries), this talk is primarily designed to introduce the audience to basic concepts and tools; participants should expect to walk away with unanswered questions, as well as the motivation to learn more. Are you still on SQL Server 2005 or 2000 and wonder what you are missing? Did you upgrade to SQL Server 2008 and haven’t had the time to check out the new features? You will see demos that give an overview of the new features for developers included with SQL Server 2008. This presentation will include Management Studio (SSMS) Enhancements, T-SQL enhancements, Table Value Parameters, Merge and more… This session is for software developers tasked with database development. Attend and learn about patterns and anti-patterns of database development, one method for building re-executable Transact-SQL deployment scripts, a method for using SqlCmd to deploy re-executable Transact-SQL deployment scripts, and fodder for a lively discussion about NULLs. Dealing with the SQL Server optimizer can be maddening at times. One moment it will take a seemingly impossible query and make it run in no time flat. At other times, the optimizer will insist on a poor plan even though a much better alternative is available. What should you do when the optimizer won't behave? One option we have available is the use of query hints. While almost everyone agrees that query hints should be avoided whenever possible, there are some situations that make this difficult to achieve. Sometimes the judicious use of a query hint can save the day, but since hints are a taboo subject, we do not discuss them as much as we should. In this session, Chris Leonard will examine many commonly-used query hints in detail, and will also discuss alternatives that can help you avoid the unnecessary use of query hints. Along the way, he will touch on many aspects of performance tuning that affect the need for query hints and their effectiveness. XML data type is available from SQL Server 2005, and XML support is even enhanced in SQL Server 2008. However, there are not many DBAs and database developers using all of the XML possibilities in SQL Server. One of the obstacles for broader usage might be XQuery, the XML query language, which is not well known in the relational community. In this session, we are going to explain basic XQuery structures and data types. After that, we are going to move to slightly more advanced topics, like using FLWOR expressions. We are going to show how you can iterate through XML nodes and even join two or more XML instances, and finally how you can design the returned XML instance. SQL Server 2008 introduced Filtered Indexes and Sparse Columns. Although they are perhaps especially powerful when used together, they also can be used independently. This session covers the basics and then dives into the areas where they work well—and not so well—both together and separately. There are dozens of things we do in T-SQL which require some type of iteration. "Iteration" means "counters", "loops" and recursion to most people. To those well versed in the “Black Arts” of high performance "Set-based" programming, it means a "Numbers" or "Tally" Table, instead. This “Developer-to-Developer” session starts with the basics of how SQL Server can be tricked into counting faster than you can imagine, how that simple act of counting replaces slothful loops and other forms of “RBAR”, and provides a substantial introduction to “set based thinking”. Code to generate a permanent Tally Table and a refined CTE version of the Tally Table will be demonstrated and compared to a While loop for performance and ease of coding. The session is filled with code demos of how the Tally Table can be used to replace certain While Loops and also includes tips on using CROSS APPLY and INLINE TABLE VALUED FUNCTIONS (iTVFs) to make application of the Tally Table run even faster. You're a good SQL professional - you attend conferences and webinars, you read articles and books, you know your way around SQL Server. But sometimes - just SOMEtimes - some piece of T-SQL slips by you unnoticed, or falls out of memory. Come and revisit old favorites, and brush up on new T-SQL features and enhancements like MERGE, OVER, and PARTITION BY. This session is chock full of code examples, including before-and-after demos and how-to illustrations. Have you ever considered using pre-existing pattern models to jump start your database projects? Have you considered purchasing proprietary models? Did you know that there are hundreds of models available to you for free or for minimal cost? In this presentation, Karen discusses some of the benefits and gotchas of working with acquired models - industry standard models, patterns, and other universal model concepts. This session includes topics such as: * The costs, benefits, and risks of working with industry standard data models * The benefits of using industry standards in your package acquisition projects * Choosing the right process * Myths in working with pattern models * 10 Tips for successfully working with third party models * What you should know before committing to project plans and estimates * Lessons Learned * Resources Introduce a proven master data management methodology that shows the overall approach of mastering core data categories such as Customer data and outlines the key deliverables and artifacts of an MDM project. Also provides a detailed description of the steps that must be taken to "master" a core data category such as Customer, discusses a solid framework that includes a robust participation model and depiction of business rules and workflow that must be followed. And, provides a working example that utilizes Microsoft SQL Server 2008 Master Data Services. A brief presentation exploring the marriage of XML and relational databases, including when it works and when it doesn't. Coverage will include an overview of four of the five basic xQuery functions supported by SQL Server 2005+, a couple of use case scenarios, and some tips on how to improve performance using design techniques. Hierarchies always seem to end up using RBAR (Row By Agonizing Row) methods to build them or use them. Even the new HierarchyID data type requires RBAR for sub-tree moves. Once built, many hierarchical structures require rather complex code to use and maintain the hierarchical data. The “Nested Set” method solves a lot of those problems but has its own maintenance problems to contend with. This “Developer-to-Developer” session quickly introduces the attendee to the 3 basic hierarchical structures, the advantages and disadvantages of each at the code level, and how to build each structure using typical/classic T-SQL methods. After a brief review of the advantages of the Nested Set method, a new very high performance method for creating Nested Sets from the Adjacency List model is demonstrated and culminates in a surprise ending; a new set based method for using the Adjacency List model to quickly resolve the 4 most frequently asked questions of a million node hierarchical structure. Speaker Bio: With more than 21,000 posts, Jeff Moden is the leading poster on SQLServerCentral.com where he coined the term “RBAR” which is pronounced “ree-bar” and is a “Modenism” for “Row By Agonizing Row”. Jeff is mostly self trained in what he calls the “Black Arts” of T-SQL and SQL Server and is known worldwide for his informative articles, his high performance T-SQL coding methods, and his friendly mentoring. His dedication to and skill in helping people write better and faster T-SQL have earned him the Microsoft SQL Server MVP award for the last three years. Session Goal #1: Teach a new very high performance method for creating the Nest Set hierarchy from the easy to maintain Adjacency List Model to be used at both the batch and GUI levels. Session Goal #2: Teach a new very high performance method to accumulate COUNTs and SUM aggregates at the node & downline levels for all nodes in a million row hierarchy or just one. May eliminate need for Nested Sets. Session Goal #3: Ancillary learning includes further "Black Arts" methods and a continuation of training in the paradigm shift necessary to achieving set based thinking. (Please note: cannont click through to speaker details from speaker's name below) With all the hype about NoSQL going around in the SQL community its about time we looked at a Microsoft's distributed cache-aside solution, AppFabric Cache. In this session we will briefly go over what AppFabric Cache is, what it can and cannot do, as well as some of the compelling features that make it a complimentary product to SQL Server. We will go over some real-world examples on how AppFabric Cache can co-exist with SQL Server and increase performance. In addition, we will compare the speed of the AppFabric Cache in comparison to SQL Server 2008 in both queries and inserts/updates. This chalk-talk will go more in-depth into some of the information presented in the SQLCAT Optimizing application workload & performance by leveraging AppFabric Cache along with SQL Server session. Microsoft SQL Server 2005 and 2008 include a fantastic feature that few people understand or use. That feature is the Microsoft SQL Server Service Broker. The SQL Service Broker is one of the least used features within the database engine, and that lack of use is simply from last of knowledge about the feature. In this session we’ll dig into how to configure the service broker for not only intra-database message queuing, but database to database queuing as well as server to server database queuing. We will also dig into a real life scenario where the SQL Server Service Broker was used to do ETL from an OLTP database to an OLAP database in near real time for near real time reporting. By the end of this session you'll understand where SQL Service Broker is the right fit to be used, and you'll hopefully be ready to deploy it within your environment. In this session I will give an overview of how to design a database, including the common normal forms and why they should matter to you if you are creating or modifying SQL Server databases. Data should be easy to work with in SQL Server if the database has been organized as close as possible to the standards of normalization that have proven for many years. Many common T-SQL programming "difficulties" are the result of struggling against the way data should be structured and can be avoided by applying the basic normalization techniques and are obvious things that you find yourself struggling with time and again (i.e. using the SUBSTRING function in a WHERE clause meaning you can't use an index efficiently). ADO.NET Entity Framework 4 (EF4) is Microsoft’s recommended data access layer for new applications. EF4 can be used to perform CRUD operations with very expressive query capabilities. After a brief overview of EF4 use from a developer perspective we will discuss how the Entity Framework can be a compliment to the general concerns that Database Administrators express. In particular this talk will investigate how the Entity Framework enables developers and DBAs to agree on a conceptual model that acts as a shared contract, providing DBAs control over their database and the entry points into the system that developers utilize by using the Entity Framework’s mapping technology and related features. We will then look at EF query performance including dynamic SQL generation, stored procedure use, and views and will outline recommendations for how to use each with EF. “Dallas” is an information marketplace that provides both trusted public domain and premium commercial data simplifying the purchase and consumption experience for businesses. In this session we will introduce the core concepts behind “Dallas”, provide a quick intro on how to use it in your applications, see and discuss on how our partners are mashing up “Dallas” data with other data and explain the Azure architecture that powers the platform. We will also take a look at the “Dallas” business model, highlighting the benefits for developer, end-users and content providers. SQL Azure provides a highly available and scalable relational database engine in the cloud. In this session we will provide an introduction to SQL Azure and the technologies that enable such functions as automatic high-availability. We will demonstrate several new enhancements we have added to SQL Azure based on the feedback we’ve received from the community since launching the service earlier this year. In this session you will learn how to build a client application that operates against locally stored data and uses synchronization to keep up-to-date with a SQL Azure database. See how Sync Framework can be used to build caching and offline capabilities into your client application, making your users productive when disconnected and making your user experience more compelling even when a connection is available. See how to develop offline applications for Windows Phone 7 and Silverlight, plus how the services support any other client platform, such as iPhone and HTML5 applications, using the open web-based sync protocol. The Speed Phreak T-SQL competition from Simple-Talk pits world class gurus in a crazy race to the finish. But what about the rest of us with less than world class skills? Simple-Talk has asked Kathi Kellenberger to explain the winning solutions to their challenges to the rest of the world. One interesting theme has emerged from the challenges: the winning solutions are set based and not cursor based. Learn how to start with a cursor based solution and end up with lean, mean set based code. When you mention CLR does your DBA run screaming from the room? Have you considered using the CLR but are intimidated by .Net and how foreign it seems compared to T-SQL? Have you ever thought that working with strings and dates in T-SQL is needlessly difficult and painful? This session will help demystify CLR integration, show you how to expose basic CLR functionality to your database, demonstrate how to get large performance gains from very little CLR code and give you the information and evidence you need to convince your DBA that the CLR is not inherently evil, and in the right circumstance, is the best solution to a problem. Hierarchical solutions used to be "reserved" for experts in the theory of trees and graphs. Not anymore! Learn how the new data type HIERARCHYID in SQL Server 2008 helps to simplify storage and manipulation of hierarchies. Practical examples and comparison with traditional methods like nested sets, adjacency list, and materialized path. Come one, come all… Be amazed at the giant query that can shrink into a tiny plan… Be shocked! Be awed! … The Query Optimizer is a powerful thing, and understanding some of the ways it handles objects such as CTEs, views, stored procedures and functions can impact the way your query runs. In this session, MVP Rob Farley will demonstrate ways that the Database Engine can simplify queries, and how you can leverage this in your database implementation. No slides here – just lots of queries and plans. Today's server hardware ships with a massive amount of CPU power. While SQL Server is designed to take advantage of available resources, there are a number of options and patterns that can be used to optimize parallel processing. This full-day seminar starts with an overview of CPU scheduler internals in both Windows and SQLOS. Next, you will learn about intra-query parallelism, where larger tasks are broken into smaller pieces that can be processed simultaneously. You will come to understand how to interpret parallel query plans and wait stats, as well as best practices for parallelism settings. Finally, you will be introduced to techniques for parallel processing at the query level—patterns to help the optimizer do a better job of parallelizing your query and other options, including Service Broker and SQLCLR. After attending this seminar, you will be in total control of your CPUs, able to compel your server to use every clock tick to your end users' advantage. <a href="http://sqlblog.com/blogs/adam_machanic/archive/2010/08/04/pass-summit-2010-post-conference-seminar-on-parallel-processing.aspx">Get more information on this seminar--including a full outline--from this post on Adam's blog</a> Click <a href="http://performance.sqlpass.org/LinkClick.aspx?fileticket=FTaNKqOMN3c%3d&tabid=2185">here</a> to download a preview of some of the content that will be presented during this seminar! Read <a href="http://www.sqlpass.org/Community/PASSBlog/entryid/188/2010-PASS-Summit-Post-Con-Preview-Adam-Machanic.aspx">Q&A blog</a> on this session. Do you believe that testing software belongs in the realm of the developer? Are you confused about how to even start testing a database? Buck Woody, Microsoft's Real-World DBA will give you an overview on what levels of testing the data professional should provide, what free and not-so-free tools are available, and valuable resources for testing your database to enable a secure, reliable and high-performing back-end system for your developer's data. In my highly rated spotlight session at PASS 2009, I spent only 90 minutes on this incredibly important topic and I could have done many more demos. This year, we’re going to spend the entire day focusing on the concepts, concerns and costs associated with the single most important tuning feature in indexing strategies: covering. Do you really know when and how to use this appropriately? Do you know what features provide covering options? Indexing is by far the most important aspect to database performance and health. But, do you have the right indexes? And, how are these indexes being used? This will be a demo packed session with numerous examples so that your indexing strategies come together for the best balance. In addition to looking at which indexes work for what types of queries, we'll look at the DMVs that can help us better understand where to look but we’ll also get insight into the pitfalls of these tools. DBAs, Devs and DB Architects can all benefit from this workshop. This session will demonstrate how SQL Server execution plans can be used to identify problems with the database design, or the TSQL code, and address those problems. The session takes the user through various common issues such as poor or missing indexes, badly written code and generally bad query performance, demonstrating how to identify the issues involved using execution plans. The session will then demonstrate different methods for addressing the issues and show how the fixed query's execution plans differ. Multiple methods for accessing execution plans including graphical, DMV's, and trace events are demonstrated. All this is meant to lay a foundation for a general troubleshooting approach to empower the attendee to make their own queries run faster and more consistently. Since the addition of the GUID (Microsoft’s implementation of the UUID), my life as a consultant and "tuner" has been busy. I’ve seen databases designed with GUID keys run fairly well with small workloads but completely fall over and fail because they just cannot scale. And, I know why GUIDs are chosen – it simplifies the handling of parent/child rows in your batches so you can reduce round-trips or avoid dealing with identity values. And, yes, sometimes it's even for distributed databases and/or security that GUIDs are chosen. I'm not entirely against ever using a GUID but overusing and abusing GUIDs just has to be stopped! Please, please, please let me give you better solutions and explanations on how to deal with your parent/child rows, round-trips and clustering keys! Come to this session and you’ll really understand the *true* cost of GUIDs. We’ll look at programming, storage, maintenance, and above all – performance; I think you’ll be surprised at how much these really cost! SQL Server 2008 provides the new FILESTREAM storage attribute, which stores binary data in the NTFS file system. In this session you will learn how you can use this new storage attribute directly within T-SQL code, and how you can use it from client applications through the native Win32 Streaming IO API. Furthermore you will learn about the performance inpacts and penalties you have to worry about when using FILESTREAM in real-world applications. For very large mission-critical applications you will also see how you can combine FILESTREAM with the partition feature of SQL Server 2008. In this session, you will see how to manage the SQL Server database development lifecycle in an agile environment. In this session you will learn how to manage changes to the database structure, ensuring quality through T-SQL unit testing, and how to automate building and deploying SQL Server databases. This session focuses on Data Tier Applications in SQL Server 2008 R2 and how this new deployment process can maximize productivity while minimizing mistakes. When we discuss performance tuning, we often focus on how to make query execution more efficient, and with good cause since execution is the single most important piece of performance tuning on SQL Server. However, query execution can only begin once query parsing, optimization, compilation and caching have occurred. These steps are generally less well-understood than execution, but can also have a significant effect on the overall performance of your SQL Server. This session will look at the work SQL Server has to do to produce that executable query plan, as well as choices we can make as database developers that affect the pre-execution stages of query processing. After this session, you should have an understanding of ways to reduce the amount of "grunt work" that SQL Server has to do before it can get down to the business of actually running a query. Today, more and more data is being exposed via interactive web applications such as social networking, news, sports, video sharing, etc. Such web sites can now be accessed via different devices such as mobile phones, netbooks and easily exposed to 1000s of users. Hence, application architectures must leverage distributed application caching along with the database tier for building highly scalable, high throughput and highly available systems. In this session, we will cover some of the lessons learned based on customer engagements for AppFabric Cache, Microsoft's distributed caching solution, with some performance data. A majority of database applications have a spatial component, ranging from an address field to a GPS location. This session shows techniques on how this data can be prepared, processed and utilized for spatial quering in SQL Server 2008. We will introduce the SQL Spatial functionality and present examples on geocoding, data loading, spatial quering and visualizing the spatial results on Bing Map. As we look ahead, SQL Server continues to invest in two of our major beyond relational scenarios: Spatial support and Managing Rich Unstructured Data and Services such as Fulltext Search. Come hear about our about exciting plans ahead. SQL Server 2008 R2 and Windows Server 2008 R2 introduce support for up to 256 logical processors. Come to this session to learn about some of the biggest OLTP scale-up customer deployments o n the planet and demonstrations from our partner hardware vendors. We will share learnings, tips, best practices and recommendations from the field. We will also show how support for 256lps are used to consolidate hundreds of databases in a single instance. Do you want to know what it takes to get your application to scale? Do you really need more powerful hardware? In this session we discuss all aspects of scalability and show you how to design your logical and physical database structures, architect your application, and select hardware and software to achieve the ultimate performance for online transaction processing (OLTP). Are you looking to migrate your on-premise applications and database from MySql or other RDBMs to SQL Azure? Or are you simply focused on the easiest ways to get your SQL Server database up to SQL Azure? Then, this session is for you. We cover two fundamental areas in this session: application data access tier and the database schema+data. In Part 1, we dive into application data-access tier, covering common migration issues as well as best practices that will help make your data-access tier more resilient in the cloud and on SQL Azure. In Part 2, the focus is on database migration. We go through migrating schema and data, taking a look at tools and techniques for efficient transfer of schema through Management Studio and Data-Tier Application (DAC). Then, we discover efficient ways of moving small and large data into SQL Azure through tools like SSIS and BCP. We close the session with a glimpse into what is in store in future for easing migration of applications into SQL Azure. SQL Azure is a great fit for elastic, large scale and cost effective database solution with many TBs and PBs of data. In this talk we will explore the patterns and practices that help you develop and deploy applications that can exploit full power of the elastic, highly available and scalable SQL Azure Databases. The talk will detail modern scalable application design techniques such as sharding and horizontal partitioning and dive into future enhancements to SQL Azure Databases. Real life learning of VLDB using filestream and RBS technology. The session will describe challenges and intricacies around delivery of very large blob storage (multi-terrabyte), including architectural, operation and coding learnings and best practices. The session will also cover areas of scaling, backup and restore, filegroup strategy associated with large blob storage. Window functions are of SQL’s greatest and most powerful querying constructs, and at the same time, underutilized. They help you address a wide variety of common querying problems in a set-based manner with great efficiency. Window functions are conceptually fairly new and require a new way of thinking. But once you gain knowledge and experience with those, they are natural and intuitive to use. This seminar helps you get over the initial barrier, get comfortable with window functions, and unleash their power to address common problems more efficiently. The seminar covers in detail window functions that are already supported by SQL Server (as of 2005, 2008, 2008 R2), like ranking calculations and window aggregate functions. The seminar also examines standard windowing functions and functionality that SQL Server doesn’t yet support, like distribution and offset functions, as well as ordering and framing options, and provides supported alternatives. Read <a href="http://www.sqlpass.org/Community/PASSBlog/entryid/189/2010-PASS-Summit-Pre-Con-Preview-Itzik-Ben-Gan.aspx">Q&A blog</a> on this session. Given a SQL Server querying problem there’s much that you can do to enable a good performing solution. Tuning involves arranging an optimal physical environment,(e.g., by creating supporting indexes), as well as writing the query in a way that it would get an optimal execution plan. Many factors can affect the efficiency of the solution including the availability of indexes, data distribution and density, and others. In different scenarios, a different solution could be the most efficient for the same querying problem. Query tuning could be considered an art. This session will provide various tips to do efficient query tuning and demonstrate those through specific tuning examples. StreamInsight ships with SQL Server 2008 R2, and provides complex event processing for tackling high volume, low latency analytics and data processing challenges. This talk will explain the business goals and target scenarios for StreamInsight, its architecture, and how to leverage StreamInsight to complement extant data processing and BI capabilities by exploring a series of customer implementations (including Sunday Night Football, and the 2010 Winter Olympics) for large scale real time web analytics. A well designed dimensional model provides the best performing, most flexible, and most easily understood data architecture for the DW/BI system. The dimensional model underlies every successful Microsoft-based DW/BI project. This is true both for the relational platform, leveraging the Star Join optimization in SQL Server 2008 database engine, and for Analysis Services which was fundamentally re-architected in 2005 to support true dimensional models. This session covers the key concepts in dimensional modeling, including facts, dimensions, change-tracking with slowly changing dimensions, junk dimensions, mini dimensions, and bridge tables. We look at examples of each concept, and of several dimensional models for different business functions and industries. We’ll also explore the primary differences between 3rd normal form models and dimensional models. This session is a SQL Saturday legend! Have you wanted to start using Analysis Services to deliver self-service BI to your end users? Have you started a cube project but have trouble using additional functionality like Actions, KPI’s and others? This session is a great all hands on experience where we build a fully functional cube in one hour with all the bells and whistles. This session is ALL demo and very interactive to make sure everyone gets the information and has a good time! Even experienced SSAS developers will learn from Adam Jorgensen, BI Expert and SQL Server pro for more than 15 years. This session will cover: 1. Best practices for database design to support good cube functionality 2. Major components of an Analysis Services project 3. Best practices for building your first cube 4. Tips and Tricks for making the process simple and repeatable 5. How to best handle adding advanced functionality to your cube 6. Reporting on your cube from multiple sources Business Intelligence (BI) is IT that IT should never pay for; it should always be aligned and driven by the business. Statistics for failed data warehousing and BI projects are discouraging, but you can prevent this in your organization. In this session we cover how to assess and agree on realistic return on investment (ROI) calculations before, during and after a BI project in addition to other critical considerations that will assure your BI project remains focused on the needs for all stakeholders. What is Master Data Services, and why should you care? Is it part of the data warehouse, part of the transaction system, or something else entirely? In this session we describe: - SQL Server 2008 R2 Master Data Services overview - The role of master data management within the Kimball Method architecture - What Master Data Services can add to your overall information delivery architecture… and what it costs - How the design of your BI solution changes with the addition of Master Data Services. This one day seminar aims to educate and excite attendees about the capabilities, features and potential of SQL Server 2008 R2 Analysis Services to deliver OLAP cubes. Analysis Service’s OLAP component can present users with a Unified Dimensional Model (UDM) that is designed for rapid ad hoc information retrieval. A well designed UDM becomes an important corner stone for delivering Business Intelligence (BI), and in particular for delivering data to non-technical users through Excel, Reporting Services, PowerPivot, SharePoint and PerformancePoint Services. In this seminar attendees will learn how a basic UDM can be designed and developed. They will then learn how it can be enriched to encapsulate business rules and calculations, and advanced BI features including KPIs and actions. By the end of the seminar attendees can reasonably expect to be both inspired and empowered to implement their own robust analytic solutions. Want to get a preview of this all-day seminar? Get a one-hour taste at Peter Myers' "<a href="http://www.sqlpass.org/24hours/fall2010/Home/DeliveringKPIswithAnalysisServices.aspx">Delivering KPIs with Analysis Services</a>" online webinar at 24 Hours of PASS. Business Intelligence is becoming more important as competition becomes more fierce and global. If you are a traditional DBA supporting OLTP databases, how can you begin to understand the dimensional model? This session will take you step-by-step through designing a data mart, the first stop on your journey in this exciting and growing field. Analysis Services stored procedures (ASSP) are nothing like the stored procedures you create for your SQL Server databases. Not much is written about this feature, so you might wonder whether you need to bother with them at all. Put simply, you can simplify your MDX queries, combine cube data with relational data, or personalize calculations for specific users–just to name a few possibilities with a little C# (or VB) code and MDX. Come to this session to learn some practical ways you can take advantage of Analysis Services stored procedures. A successful data warehouse project begins with the right design and solution architecture. Why? Because a poor design leads to bad performance, limited scalability, and even user rejection. And poor solution architecture leads to solution instability, data quality issues, and challenging data integration. This session gets you started in the right direction by covering the design and architecture principles of data warehouse solutions. We'll look at the most common mistakes and address how to drive toward the right data modeling and solution architecture for your data warehouse application. We will begin by reviewing dimensional design theory and then dive into planning your data warehouse infrastructure and data architecture. If you are involved in planning, developing or maintaining a DW solution, don't miss out! SQL Server Integration Services (SSIS) has provided an enterprise platform for ETL for more than 5 years now. Nearly any ETL scenario can be handled by SSIS and this seminar explores many of those scenarios through demo-intensive, interactive mini case studies. In this day-long session by SSIS expert and author Brian Knight, you will spend the first hour catching up on what you may have been missing with SSIS in SQL Server 2005 and 2008. Then, the seminar rapidly progresses into much more advanced topics like how to load a data warehouse and advanced ways to log errors and handle events. Brian also covers best practices for the enterprise in package configuration, parallelism in SSIS, and hidden switches for performance for speed and administration. The content moves quickly through the SSIS concepts and depicts common scenarios that new and experienced SSIS developers run into. You will walk away confident in how to achieve success with SSIS. Want to get a preview of this all-day seminar? Get a one-hour taste at Brian Knight's "<a href="http://www.sqlpass.org/24hours/fall2010/Home/UpgradingDTSPackagestoSSIS.aspx">Upgrading DTS Packages to SSIS</a>" online webinar at 24 Hours of PASS. Read <a href="http://www.sqlpass.org/Community/PASSBlog/entryid/186/2010-PASS-Summit-Pre-Con-Preview-Brian-Knight.aspx">Q&A blog</a> on this session. The ETL application is the unloved--and often mistreated--workhorse of the data warehouse / business intelligence system. But the design and smooth operation of the ETL system is second only to the data model in its impact on the acceptability and performance of your BI system. In this session we describe: - The Kimball Method recommended architecture for an ETL system on the SQL Server 2008 R2 platform - ETL team composition, skills, and management , and how to most effectively use external resources - Different styles of ETL development within SSIS, and how to decide which style works best for your team In the most basic way, the Unified Dimensional Model (UDM) combines the best aspects of traditional OLAP analysis and relational reporting. In doing so it presents a "single version of the truth" that can satisfy multidimensional analysis and relationship reporting. But that's just the tip of the iceberg. The UDM can also define server-side calculations, Key Performance Indicators (KPIs), actions, perspectives, and translations to provide a richer experience than just querying for results from a database. So beyond storing your data, the UDM can store information about how our data is represented and how it should be delivered and presented to the business user. In this session, discover through theory, demonstration, and best practices discussion, how to enrich the design of the UDM beyond the data itself. Having problems figuring out how to best debug your SSIS packages? Whether you're in the middle of development or have a problematic package in production, it helps to have some tools in your arsenal to find out where things are going wrong. I've got a set of techniques I'd like to share with you. Take this opportunity to soak up some good practices, and contribute your successful methods as well. This workshop is aimed to all people who find themselves thrown into the big, big world of Business Intelligence. Here you'll learn how to create a business intelligence solution from zero, learning the basic principles and different design techniques: star and snowflake schemas, fact and dimension tables. Inmon and Kimball will be names which you will be familiar with after this intensive BI deep dive. Of course, after having defined the data warehouse, we'll move on to show how to use Integration Services and Analysis Services to load the data that will empower your end users so they will enjoy your work at its maximum and you'll love the work you have done! Everything explained will come from real-world experience, giving you not only the technical knowledge but, equally important, the architectural vision and the best practices you’ll need to enforce to make a BI solution a success. Read <a href="http://www.sqlpass.org/Community/PASSBlog/entryid/187/2010-PASS-Summit-Post-Con-Preview-Davide-Mauri.aspx">Q&A blog</a> on this session. Introducing SQL Azure Reporting Services – An in-depth review of the recently announced SQL Azure Reporting Services feature complete with demo’s, architectural review, code samples and just as importantly a discussion on how this new feature can provide important cloud capabilities for your company. If you are a BI professional, System Integrator, Consultant, ISV or have operational reporting needs within your organization then you must not miss this session that talks to Microsoft's ongoing commitment to SQL Azure and Cloud computing. It’s one thing to have lots of data, it’s one thing to have access to data, but if you don’t have good quality data, really what’s the point? In this session we will walk through the important scenarios of data quality and master data. You’ll learn about important new developments in our technology that will change the way think about these problems on the Microsoft platform. For anyone concerned with provisioning data for business users, or building the famous “Single version of the truth” this is an important session to see. If you are building Business Intelligence solutions or are interested in the architecture of SQL Business Intelligence you must not miss this session. In it we will discuss BI in customer use case terms, focusing on Dashboards, Reporting, Analysis Services and the concepts of Self-Service BI (PowerPivot) whilst also discussing, comparing and contrasting the impact of cloud computing on the BI field. When you create a data warehouse, you spend most of the development time for the implementation of the ETL process (Extract, Transform, Load). During the ETL process you move data from various source systems to your central data warehouse where you structure it for queries issued by information workers through business intelligence tools. But how you can concrete implement a robust, scalable, and performant ETL processs with SSIS? In this session you will see some best practices how you can build an ETL process and how you can enhance it during the development time very easily. Furthermore you will see how you can use the partitioning feature of SQL Server 2008 transparently in your ETL process for the management of very large database tables. As more and more organizations and teams within organizations have started adopting SQL Server Integration Services for data processing and other management task automation, there is increasing need for standardization early in the process to avoid common management problems that arise from lack of standardization and good management techniques. In this session, we will help you identify areas of standardization and pitfalls that arise from lack of standards, reviews some common standardization approaches and best practices and finally walk you through a standardized template example that can be customized, deployed and used as a starting point for an SSIS project. This session presents strategies for analyzing and debugging performance problems in Analysis Services. The session will show how to capture the MDX sent from a client application to the Analysis Server and how to analyze that MDX so that the performance problem can be understood and fixed. Performance problems can be addressed by redesigning the cube, creating new aggregations, or optimizing calculations - but first you have to understand what is causing the performance problem. The session will also include a listing of key Analysis Services performance tips. Data Marts and Data Warehouses are fast becoming an integral part of the critical reporting infrastructure in organizations. As a result, the ETL processes that load data into these systems become very important. They need to be robust, reliable and dependent and easily manageable. As a result, your ETL processes need proper mechanisms to handle process failures and restart, rollback, manage dependencies as well as perform data consistency check. Many of these repeatable processes for loading dimensions and fact tables can be implemented in templates to ensure consistent process and management across the entire ETL process. This session will cover the common ETL scenarios and concerns, review common processes and finally show how to implement these repeatable processes in templates that allow consistent process management across all packages and improve developer productivity. Sure you've read the SSIS books. And sure you've seen a lot of SSIS presentations. But how do you put it all together to create a solid ETL framework for loading your data warehouse environment? In this (updated for 2010) session, we'll look at the ins and outs of a real-world ETL environment built using SSIS. We'll discuss the ETL design patterns used to handle a multi-tiered data warehouse environment with multiple data sources, different loading schedules, and different data transformation requirements. We'll also discuss how the development team responsible for the ETL environment at the Department of Veterans Affairs handles package auditing and logging, team development, package testing, and automated deployments. To detect possible performance and service level issues with Analysis Services (SSAS), it is important to understand how it interacts with the operating system. In fact, resources consumed by Analysis Services are controlled by the operating system and this session will show what counters to monitor (and how to do it) for getting memory, CPU and I/O consumption made by SSAS. Then, there will be a description of specific tools like SQL Server Profiler, ASTrace, Activity Viewer and MDX Studio, which will be used both alone and integrated with operating system counters. This session content is based on a chapter of the book Expert Cube Development with Microsoft SQL Server 2008 Analysis Services, written by Marco Russo, Alberto Ferrari and Chris Webb. You Integration Services users out there are a loyal and committed bunch – so don’t miss this session! We’ll be looking at some of the exciting things coming in the next version – features and capabilities that move SSIS from a powerful took, to a very robust, high-end platform with many requested management, monitoring, deployment and development abilities. You’ll learn about the team’s new approach to integration, and you’ll see many of the capabilities in action. There has been a lot of buzz about self-service BI over the last few years. But let’s not forget the bread-and-butter of business decision support: the enterprise class, corporate system that delivers high-performance, high-quality, data – integrated, validated and calculated. This session will explore new directions we are taking with Analysis Services, integrating some of our newest technologies, including in-memory and DAX, to deliver a unique corporate BI experience. This session is a must for anyone developing with SSAS today. Evan will discuss and demonstrate how to troubleshoot Reporting Services performance issues. This will cover both tools included in the product plus some real-world scenarios he has dealt with in the past. Have you struggled to configure Excel Services, Reporting Services, or PerformancePoint in SharePoint 2010? Are you confused about the security options in SharePoint 2010 for configuring these services? This session will show you how to configure these services both with and without Kerberos – and to understand the issues with both options. We’ll include important background on claims-based authentication and Kerberos in SharePoint 2010 and identity delegation to the back end data source. This is a SQL CAT and CSS presentation, which means that you will learn about actually deployment environments, problems faced, and solutions found. BI deployments are increasing, and with all of these new technologies, they are becoming more complicated to deploy and configure. This is even more the case in a distributed environment. We will look at what this deployment and configuration would look like for some of the newest technologies from Microsoft. This will allow you to take full advantage of your BI environment. <li>SharePoint 2010 Configuration including multiple Web Front Ends and multiple zones</li> <li>PowerPivot/Excel Services Integration</li> <li>Reporting Services 2008 R2 Integration</li> <li>PerformancePoint Integration</li> <li>SQL Server configuration</li> <li>Analysis Services Configuration</li> You will be able to walk away with checklists used by CSS to get these items configured and deployed in a production environment. You will also get to see some of the common pitfalls that CSS has experienced when working with these deployments. PowerPivot is the hot new thing that everyone's talking about. But is it the right tool for your BI project? In this session we'll look at what PowerPivot is and isn't good at, and compare it to its older brother Analysis Services. We'll see where PowerPivot scores over Analysis Services and where Analysis Services has important functionality that PowerPivot still lacks. Do you have SSAS queries that you feel should be faster? Multidimensional Expressions, or MDX, is the language used to work with Analysis Services. Similar to SQL for the relational engine, MDX is a language for queries and DDL statements on multidimensional cubes in Analysis Services. Troubleshooting slow MDX queries is often challenging because the level of support provided by the tools and the Analysis Services engine is not as mature as that found in the relational engine. This session covers the methods for examining slow queries using the SQL Profiler and Performance Monitor to determine the need for aggregations, caching problems, memory constraints, and so forth. Best practices for optimizing MDX will be examined, and various server properties will be discussed as necessary to help correct query issues. Attendees will leave armed with a practical approach to locating bottlenecks and strategies for speeding up queries. The application of standard data cleansing routines is a best practice to ensure consistency across data integrated from multiple systems. Examples of data cleansing include translation of NULL values to defaults, trimming string values, and consistent formatting for textual data such as telephone and social security numbers. While these routines can be implemented using standard SSIS components, the resulting logic must be duplicated for each source and column which creates a development and maintenance burden. This session will walk through the process of creating common data cleansing routines in a custom component, enabling code reuse with minimal effort across many packages. Best practices for component development will be a focus throughout the session. This session provides an in-depth technical review of the PowerPivot architecture – deeper than you have ever seen before. We look at the PowerPivot Excel addin, its various components, how they interact, and the ways that they communicate with the server. Also covering various aspects of in-memory VertiPaq engine behavior. On the server side, we look at how PowerPivot uses the new SharePoint 2010 shared service architecture and how it accomplishes its main capabilities: data access (connections to PowerPivot data), data refresh, report gallery (with its various Microsoft Silverlight controls) and its usage infrastructure and reporting system. This is sure to be another popular presentation by the inimitable PowerPivot Twins – Denny Lee and Dave Wickert. If there is anything you need to know about deploying and managing PowerPivot, they are sure to have the answers – or no one does. There simply isn’t a better way to learn than to hear these guys get technical – and it’s fun too! "Design Patterns" is more than a trendy buzz phrase; design patterns are a way of breaking down complex development projects into manageable tasks. They lend themselves to several development methodologies and apply to SSIS development. Chances are you're using your own design patterns now! At the PASS Summit 2009, Andy Leonard demonstrated ways to use some less-deocumented features of SSIS. At the PASS Summit 2010 Andy shares more SSIS Design Patterns. This session includes patterns: - to migrate SSIS packages through your enterprise without editing connection managers; - to load data from variable-length row files; - to extend the functionality of the parent-child pattern to create SSIS execution groups. The session is designed for those with little or no exposure to design patterns or application development methodologies; but with good experience in SSIS. The goal is to introduce the science, terminology, and philosophy of design patterns to those wishing to learn more. The purpose of this session is to highlight issues, best practices, and lessons learned from a select group of SQL Server's Enterprise customers. Some example of the customer projects we will cover in this session: - AdCenter and their 6TB SSAS environment: two 3TB OLAP cubes - A very large beverage and bottler company and their issues (may be able to reveal name by TR10) - XBox Live and their planned petabyte warehouse and cubes that go with it; issues include distinct count, and SSD drives We will also cover in detail technical issues surrounding: - Analysis Services Scale Out Using Read-Only - Analysis Services ROLAP Case Study. With SQL Server 2008 R2, Reporting Services and the new Report Builder 3.0 will introduce many new capabilities. In this fast-paced, demo-intensive session you will see how the new visualization techniques, especially the new map control can be used for a completely new way of looking at data and make your creativity room, together with the news sparkline, data bar and indicator controls. A special highlight will be an implementation of dynamic zooming in & out on maps without losing focus. Other things to watch in action are the new Lookup-Functions, Aggregates of Aggregates, rendering-format dependent layout and valuable Report Server improvements such as report parts, shared datasets. If you want to find out what the new release of Reporting Services holds in store for you, you won't want to miss this session. Is your business looking to deliver better information by implementing scorecards, dashboards, and analytic reports? PerformancePoint Services (PPS) allows businesses to easily deliver content from their data warehouses to a wide variety of users throughout the organization. The Monitoring server enables businesses to create powerful, intuitive scorecards that focus on anything from the entire enterprise to an individual employee’s goals. Analytics provides tools for users to perform in-depth analysis of the data in the warehouse. Learn how to create interactive scorecards and analytic reports and deliver these items to users at all levels of your business. As an enterprise reporting solution, Reporting Services can consume many different types of data sources, including Analysis Services cubes and PowerPivot workbooks. You can build simple reports using a graphical design interface in Reporting Services without learning the Multidimensional Expressions (MDX) language, but to fully leverage multidimensional data in your reports, you should develop a solid understanding of this language and how to structure your query to facilitate the report design process. In this session, we'll review how the graphic designer constructs an MDX query, explore ways that you might want to change the generated query, and demonstrate how to work with parameterized MDX queries. We'll also explore how to use T-SQL stored procedures to call MDX queries. There’s a lot of chatter about the cloud these days. We’re sure you are wondering what it means for Business Intelligence in general, and for Microsoft BI in particular. In this interactive session we will chat about the recently announced SQL Azure Reporting Services and show you how to publish and run reports in SQL Azure Reporting. The secret to designing compelling report solutions is in the ingredients and the technique. Learn to apply proven design patterns and best practices to create a reporting solution masterpiece. These techniques employ expressions, parameters, custom programming and years of field experience. This presentation is based in-part from our Wrox Press Book: Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports. Operating Reporting Services in SharePoint integrated mode adds a number of features to the SSRS environment. Learn why your organization should consider running in SP integrated mode or if your organization is already using SP integrated mode, make sure you are getting the most out of this environment. First there was a Report Designer, then Report Builder – now, this session will prepare you for the next developments in reporting technology from Microsoft. We’re giving nothing away in the abstract, just be sure to see this session! If you have not seen it, you would hardly believe it. The ever-popular Power Hour is difficult to summarize. Wacky, frequently unseemly, and generally funny demos, all with a serious point about the flexibility and power of Microsoft BI. Almost endless swag. There’s nothing quite like I – don’t miss it! This session should leave you breathless. Amir and Donald will take you at breakneck speed through the features you don’t know you have – the hidden gems of SQL Server BI. There’s something for everyone – developer, administrator or business user – and there’s something from almost every technology too. What’s the use of having business intelligence if you can’t make it actionable? In this session you’ll learn how to better collaborate with SharePoint 2010 and Microsoft’s BI suite. You’ll learn how PowerPivot in SharePoint will allow your users to do self-service BI. You’ll also learn how Performance Point can help you develop scorecards and aid your users in answering questions they didn’t even know they had. During this session we are going to analyze common business problems that require and avanced usage of DAX functions and data modeling. Examples include banding of price in ranges, advanced simulation with the usage of many to many relationships, usage of dynamic sets in PowerPivot and many other examples, all taken from budgeting common problems. A common theme among many SSRS environments is that they have grown very organically and often have little standards for report development. In this session we will quickly cover the benefits that you can achieve by having standards in place. We will then delve into techniques that you can use such as Template Reports, Custom DLLs, and interfacing with reports programmatically, that you can use to standardize your reports. Lastly, we will talk about some strategies for migrating from a non-standard environment to a standard environment. How much do you know about what your report server is doing? Are you able to determine who's running what reports when? Their costs? How about who has access to what reports or folders? Or if you have a server farm, where is most of the work going? Can you intepret the esoteric log files? Better yet, do you know how to correlate that information with activity recorded in the ReportServer database? This session will focus on the massive amount of information available from the ReportServer database, how to properly access it/manipulate it (a straight SQL query isn't a great idea) and the various tools available to expose ReportServer information. This session will include mostly demonstration and sample code to really learn about your installation. DAX (Data Analysis Expressions) is the language for the definition of calculation expressions in PowerPivot. It has syntax similar to Excel, but it also offers a set of features that are specific to PowerPivot. In this session we will see how DAX works, starting with a fast introduction of the syntax elements and spending most of the time understanding how evaluation context works, looking at both row context and filter context. This knowledge is fundamental to build more complex calculation and also to understand performance implications of different ways of writing DAX expressions to get the same results. Ok, so this wasn’t the best advert for Australian tourism, but it is a question often asked by data applications. We understood spatial data in SQL Server 2008, and we love the map functionality in SQL Server 2008 Reporting Services. But if you have a pile of addresses, how can you convert that into data that you can map? And how do you make a map that lets you zoom in and out? In this talk, Rob Farley looks at how to handle shape files in Report Builder 3.0, the Bing Maps functionality for searching for addresses and displaying a map, and how this can tie into the spatial types we’ve been seeing for the last couple of years. It may also include a venture into the world of Live Labs Pivot, investigating new and interesting ways to display your data. Sharepoint is becoming more ubiquitous in both the enterprise and SME market as a core business application. With Sharepoint 2010 Microsoft has re-engineered Sharepoint 2010 so as to leverage SQL Server 2008 R2's capabilities. Consequently it is more important than ever for DBAs to be cognisant of Sharepoint's architecture and how to best provision, configure and manage the SQL Server infrastructure for Sharepoint. In this session Victor Isakov (MCT, MCM, MCA) will present the Sharepoint 2010 architecture and considerations that every DBA should know, including capacity planning, performance management, configuration, disaster recovery and high availability. In this session, we will explore a number of new techniques that we have developed to improve the performance of Excel reports against Analysis Services cubes. You will learn how to enrich the BI user experience by utilizing the features of the Excel client with Excel Services. You will discover how combining these technologies enables you to execute complex calculations on powerful servers and to reduce performance penalties related to slow network connections, while still providing the user with the full Excel client experience. This session will include a demonstration of MSIT solution for querying Analysis Services in a worldwide deployment, maximizing performance over slow network connections. In addition, we will explore how MSIT is enabling power users to create and deploy their own highly performant cubes based on IT-approved conformed dimensions and IT-sanctioned fact tables. Reporting Services is a powerful tool for building and deploying reports. But, what happens when you need more than just reports? If you need to build a content sharing portal along with your reporting solution SharePoint integration might be for you. Come learn about how to successfully implement Reporting Services in SharePoint integrated mode and some of the pitfalls that you might run into along the way. When we mention “Self Service BI” do you think only of PowerPivot with its mash-up and pivoting features? In fact, there is another equally important aspect to “self service” – enabling business users to easily build compelling reports and visualizations over the data they already have access to. Report Builder 3.0 is a true self service BI tool, and in this session you will see why. Expect persuasive and insightful demos of important functions such as new visualizations and componentized reports. Many non-DBAs and novice DBAs take advantage of SQL Server’s Maintenance Plan Wizard in order to create SQL Server database maintenance plans. If used properly, the Maintenance Plan Wizard can be an easy way for less experienced DBAs to help maintain their SQL Server databases. On the other hand, if the Wizard is used improperly (and unfortunately, the Wizard doesn’t prevent DBAs from making mistakes), database performance and uptime can be put at risk. In this session, attendees will learn, step-by-step, exactly how to use the Wizard in order to create a maintenance plan that will enhance the performance and uptime of their SQL Server databases. Every step, and every option, will be thoroughly explained in detail, providing the DBA with the information they need to create a maintenance plan that will work well for their environment. This session is filled with demonstrations, and assumes attendees have little or no practical experience with the Maintenance Plan Wizard. The veteran DBA left and your boss decided you should become the new DBA. And now you've come to the Summit to learn everything you can about SQL Server so you can be a great DBA. But where do you start? Which questions should you ask? You don't even know what it is you don't know. This is the session for you! Of course one session can't cover everything, but we'll hit the major areas of SQL Server so you'll at least have a basic understanding of the breadth of SQL Server. We'll discuss correctness, concurrency, maintenance, replication, high availability and disaster recovery, storage, performance, and monitoring. We'll touch on many other areas so you'll at least have heard a bit about them and you'll know to learn more and ask questions on them. It'll take more than just this session to be able to answer your customer's questions, but at least you'll have a chance at understanding the questions, and you'll be able to follow along in some of the other in-depth sessions at the Summit. Agile software development emphasizes continuous deployment and its methods do not directly include long term planning. DBAs must ensure data integrity and have a long term view for application scale, so Agile methods present challenges. Come learn about successful real-world practices iteratively developed in a high transaction internet service environment over the last five years. We have created a flourishing Agile shop while meeting high requirements for uptime, customer response, and data consistency. In this session we’ll cover key habits for success, practices to avoid, how and when to get started, and why Agile development can be a great thing for DBAs. Topics will also include how "Testing in Production" can be a huge benefit. You've always wanted to get promoted into the DBA position. The good news is you got it - the bad news is you have no idea what to do first. What's important, what's not a big deal, and what can get you fired if you're not careful? If your answer wasn’t, “update my resume”, then this session is a good start. We’ll chat about a natural priority for the overwhelming flood waiting in our offices. By focusing on a few words ending in “ity” we’ll realize what is most important. Through the interaction, examples and demos you’ll leave feeling ready to attack your environment. Be warned – along the way to knowing where to start, you may get that feeling in your stomach that makes you logon to the VPN to check a few things out. Offering a highly available SQL Server environment depends on adherence to best practices and awareness of the latest patches and updates. More information on managing your SQL Server environment can help increase uptime by decreasing the time to resolve unplanned downtime. Learn about the latest online Microsoft tools available to help analyze the configurations of your SQL Server deployments, identify potential issues and data corruption, and provide more information about your systems to make problem resolution faster and more efficient. PowerShell is the new scripting environment from Microsoft for managing server environments. Because PowerShell supports the .Net Framework, accessing SQL Server in a scripting environment is easier than ever before. Whether you need to access data or manage SQL Server, PowerShell makes it possible. This session will give an introduction to PowerShell, explain the basic ADO.Net objects required to connect with SQL Server, and demonstrate accessing SQL Server from PowerShell scripts, both from a data and a management perspective. Want to get a preview of this all-day seminar? Get a one-hour taste at Allen White's "<a href="http://www.sqlpass.org/24hours/fall2010/Home/GatherSQLServerPerformanceDatawithPowerShell.aspx">Gather SQL Server Performance Data with PowerShell</a>" online webinar at 24 Hours of PASS. <a href="http://sqlblog.com/blogs/allen_white/archive/2010/08/19/pass-powershell-sql-server-precon.aspx">Get more information on this seminar--including a full outline--from this post on Allen's blog</a> You’ve heard it said, “If you have to do it twice, automate it.” That’s great advice and PowerShell provides a simple but extremely powerful way to do it. PowerShell is the future of Windows scripting. Cut the learning curve and get a real handle on this powerful automation tool. This session walks you through a dozen scripts to simplify and easily automate time-consuming and tedious elements of your day to day job. This isn’t stuff you’ll use SOMEDAY, these are scripts you can use when you get home tonight. Harness the power of PowerShell to easily find servers short on space, script out tables and constraints across all of your databases at once and backup databases and restore them to a different environment. These tricks and many others will allow PowerShell to simplify your job like no other tool. Statistics are a key part of getting solid and repeatable performance and is easily just as important as having good indexes. Whether you're a DBA or trying to get by without one, this session will give you the information you need to know including how to create, manage, troubleshoot, and more! The SQL Server 2008 Data Collector provides some low overhead data collection functionality to store performance and diagnostics historic information of your SQL Server instances. See how you can use this information to troubleshoot problems and to provide trend analysis for the performance of your SQL Server instance. In addition to show the basics and architecture of the new Data Collector, this session focuses on the predefined system data collection sets that are provided by SQL Server 2008 that automatically collect data from the disk usage, instance activity, and queries statistics. You will learn about the Disk Usage collection set, which gathers statistics regarding the growth of the data and transaction log database files; explore the Server Activity collection set which focus on the server activity and resources utilization; and learn about the Query Statistics collection set which collects data regarding the queries running in your instance. When you are on the most recent version of SQL Server, many tasks are just that much easier. Help is easier to find, recent articles and blog posts most likely apply to you and you can take full advantage of all the new features. In this session we will address the challenges of upgrading or migrating your SQL Servers to new hardware. In this case study based session, this real life example with show how I moved a VLDB with less than 15 minutes of downtime. In this session we will review not only the technical options that you must evaluate but the most important aspect of your database move; the end users. We will discuss how to provide information to each group so you have a company standing behind you in support as you migrate. This case study included a Version upgrade and a Hardware upgrade during the same outage. SQL Azure provides high availability by maintaining multiple copies of your database, but that doesn't mean that you should just trust Azure and assume your data is safe. If your data is mission critical, you should maintain a backup outside the Azure infrastructure. The database is also vulnerable to administration errors. If you accidentally truncate a table in your production database, that change will immediately be copied to all replicas, and there is no way to recover that table. In this session, you'll see how to use SSIS and BCP to back up a SQL Azure database. We'll also demonstrate processes you can use to move data from an on-premise database to SQL Azure. Finally, we'll discuss procedures for migrating your database from staging to production, to avoid the risks associated with implementing DDL directly in your production database. The foundation of database performance is the underlying server hardware and storage subsystem. Even the best designed and optimized database application can be crippled by an inadequate hardware and storage infrastructure. Recent advances in new processors and chipsets, along with improvements in magnetic and SSD storage have dramatically changed the evaluation and selection process compared to the past. Many database professionals struggle to keep up with new technology and often simply let someone else make their hardware selection and sizing decisions. Unfortunately, the DBA usually gets the blame for any performance issues that crop up later. Don't let this happen to you! This session covers current and upcoming hardware from both Intel and AMD and gives you the tools and resources to make better hardware selection decisions to support SQL Server OLTP workloads. If you've used the Audit feature before in SQL 2008, like me, you may have taken a look at it and thought "Oh, this is pretty straight foward". I don't know about you, but I was dead wrong. The Audit feature is extremely powerful, deceptively complex and potentially resource prohibitive. This session will go over the technical details of working with Audit (both database and server audtiting), the most likely times to misconfigure or under-anticipate, as well as how to properly set expectations with management as to the true cost of using the audit feature. This session is intended for both decision makers and SQL Server DBAs. Do SQL Servers suddenly appear all over your organization and are you thinking about consolidating? More and more are talking about consolidation and especially virtualizing, but beware it can become a very expensive experience in licenses to Microsoft. I will try to give my recipe on how to consolidate your SQL Server environment. - Do you know all your servers? - Do you know the amount of resources they use? - Are you in control of versions? - Are you in control of access to the databases? - Are you in control of the developers? Per Andersen from the insurance agency IF, will give a brief description of how they have consolidated nearly 180 SQL Servers to 20 in one year The plan cache in SQL Server 2005 and 2008 has a wealth of information available to assist DBA's in performance tuning. This session will show how to query the plan cache to find plan information and then how to parse the XML plans contained in the cache to identify missing indexes by query, high cost parallel queries with high usage counts, implicit column conversions caused by data type mismatches, and other interesting information about how queries have executed in the system. As the demand for data keeps growing and security and compliance keep tightening, the role of the DBA has become an increasingly critical part of maintaining compliance within the organization. This session will cover real world examples you can use with Policy-Based Management to ensure you have the proper server configuration and security settings in place along with the appropriate encryption and auditing options for your environment. Indexes are a crucial component of SQL Server, especially in performance tuning, yet many DBA's don't fully understand how indexes work. In this in-depth session, Michelle will examine the anatomy of indexes, from how they're stored to how they get fragmented. And to make our inner geeks happy, she'll look at page data to show you what's happening behind the scenes. Topics covered will include index structure, fragmentation and defragmentation, index partitioning, and index filtering. If you've ever wondered just *what* goes on in an index, don't miss this session! SQL Server 2008 R2 introduced support for Sysprep for a SQL Server installation. This is a big leap in manageability of virtualized SQL Server environments. Sysprep allows you to roll out a preconfigured base image for virtual servers that can be easily completed on roll-out of the image to a new virtual server. This session will demonstrate how to implement the new Sysprep functionality in SQL Server 2008 R2 when partnered with Hyper-V virtual server. The session will also explore ways to automate the rollout of the process of completing the SQL Server installation as part of the rollout process. If your SQL Server was hacked, do you think you would know about it? Would you know how to discover who attacked your server? What was compromised? When? How? Thankfully most DBAs are not fighting off criminals on a daily basis, but understanding SQL Server forensics will allow you to better understand your SQL Servers and will give you the ability to answer that frequent question “What happened?” In this session, I will walk you through the process of how to determine what happened on your server without the use of expensive third-party tools, using only features available in all editions of Windows and SQL Server. You will learn how to parse the various Windows, SQL Server and database transactional logs. We will also explore the wealth of information available within Dynamic Management Views for clues as to what is happening on you SQL Server. As an enterprise application grows and load increases, some concurrency issues are bound to surface. Deadlocks are one of the more aggravating of this class of problems. Fortunately, each release of SQL Server includes better tools for troubleshooting deadlocks. Trevor will provide a strong foundation on deadlocks and then demonstrate approaches for handling them in SQL Server 2000, 2005, 2008/2008 R2. A columnar DBMS is an implementation of the relational theory, but with a twist. The data storage layer does not contain records. Due to the variable column lengths within a row, a small column with low cardinality, or variability of values, may reside completely within one block while another column with high cardinality and longer length may take a thousand blocks. In columnar, all the same data – your data – is there. It’s just organized differently and automatically by the DBMS. Go beyond Dewitt and into the actual data pages. Learn about the columnar orientation and how it can be effective for your needs. This is the orientation of Microsoft SQL Server 2008 R2, which will have an optional column-oriented storage layer. Creating indexes can be one of the most useful performance gains you can add to your database. However, having too many indexes can hurt performance as well as require additional space and maintenance time. The introduction of Dynamic Management Views has made indexing easier; however, there are still many tactics you need to be aware of when applying indexes. This session will cover strategies such as discovering and applying missing indexes, finding and removing overlapping indexes, and identifying rarely used indexes that are expensive to maintain. DBAs often don’t spend a lot of time watching tempdb performance. They think of it as a black box that takes care of itself. What many DBAs don’t fully realize is how tempdb performance can affect SQL Server’s overall performance. This is especially true in SQL Server 2005 and SQL Server 2008, which use tempdb much more than previous versions. In this session you will learn: What Objects are Stored in tempdb; How tempdb Works Internally; Different Types of tempdb Problems; How to Monitor tempdb; and Ways to Optimize tempdb Performance. This session is targeted towards those DBAs who want to learn how tempdb works, and how to optimize its performance. It is assumed that attendees have a basic foundation of DBA knowledge. After attending the session, attendees will be able to return to work and immediately put what they have learned into practice. This session will focus on outlining the different SQL Server high availability alternatives in order to achieve site resiliency and disaster recovery. Technologies covered include Failover Clustering, Database Mirroring, Log Shipping, Peer-to-Peer Replication and Live Migration. Moreover, this session will teach you how to combine the different high availability alternatives in order to gain maximum availability with your database infrastructure. Case Studies and Best Practices learned from the Microsoft Technology Center (MTC), SQL Server CAT Team and the SQL Server Product Group will be shared. Parallel Data Warehouse or PDW is the new scale-out SQL Server offering that enables SQL Server to scale to 100’s of terabytes for data warehousing workloads. This fast paced session provides an overview of the PDW hardware / software architecture, what makes PDW different, the appliance experience and the roadmap ahead. In this session we will introduce you to the concept of "Getting Data Where You Need It". We will show you how our new cloud based SQL Azure Data Sync Service enables on-premises SQL Server data to be shared with cloud-based applications. We will then show how the Data Sync Service allows data to be synchronized across geographically dispersed SQL Azure databases. This session looks at getting the most out of SQL Server running on Hyper-V, and how to leverage new features such as Live Migration and Dynamic Memory Learn about the enhancements in the new SQL Server 2008 R2 Management Pack for Operations Manager 2007 R2. Highlights include: streamlined deployment, reduced noise, health model improvements, and new additions. Also learn about the new SQL Server 2008 R2 Best Practices Analyzer (BPA). Highlights include: rules for Reporting Services, Integration Services, and Setup; new KB articles and content; and new capabilities that stem from the underlying Microsoft Baseline Configuration Analyzer (MBCA) v2 framework. In this session, you will learn how to leverage the Utility Control Point and Data-tier applications within SQL Server 2008 R2 to Detect problems, Automate solutions, and Practice prevention using the DAP methodology. You'll also see what enhancements are planned for the next major release of SQL Server. Are you the "SQL person" on your team? Have you become a DBA (maybe involuntarily) and you find yourself managing SQL Server databases more and more? The one thing you NEED now, to manage the system correctly is knowledge! Paul and Kimberly will run through their top-ten database maintenance recommendations with lots of tips and tricks along the way. These are distilled from almost 30 years combined experience working with SQL Server customers and are geared towards making your databases more performant, more available, and more easily managed (and to save you time!). Everything in this session will be practical and applicable to a wide variety of databases you create, implement and manage yourself, as well as third party databases you manage. Topics covered include: data and log file configuration, tempdb, backups, consistency checking, database settings, statistics, and much more! Focus will be on 2008 but we'll explain where there are key differences for 2005 as well. Read <a href="http://www.sqlpass.org/Community/PASSBlog/entryid/198/2010-PASS-Summit-Pre-Con-Preview-Paul-Randal-and-Kimberly-Tripp.aspx">Q&A blog</a> on this session. These two technologies can make a very big – and very bad – difference in how your SQL Server performs. Wouldn’t it be great if you could get the real, honest lowdown from a virtualization administrator, a SAN administrator, and a DBA? Wouldn’t it be even better if one person had done all three, and could give you the pros and cons of each point of view? That person is Brent Ozar, a Microsoft Certified Master who’s been there and done that. Many experienced DBAs are aware that there are many undocumented commands and traceflags in SQL Server, and some DBAs may even keep track of all references to such options whenever they read about them in an article or blog posts, or hear about them at a conference. In this session, I’ll not only tell you about some of my favorite undocumented features, I’ll also tell you some of my tricks for discovering more undocumented secrets. It's amazing how many myths and misconceptions have sprung up and persisted over the years about SQL Server - after more than 10 years helping people out on forums, newsgroups, and customer engagements, Paul's heard it all. Are there really non-logged operations? Can interrupting shrinks or rebuilds cause corruption? Can you override the server's MAXDOP setting? Will the server always do a table-scan to get a row count? These are just a few of many, many myths that Paul will debunk in this fast-paced session on how SQL Server operates and should be managed and maintained. SQL Server code named “Denali” introduces a new data warehouse query acceleration feature based on a new type of index. This new index, combined with enhanced query optimization and execution features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times since these indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables and indexed (materialized) views. Hear about the details here first! Failing to design an application with concurrency in mind, and failure to test an application with the maximum number of expected simultaneous users is one of the main causes of poor application performance. SQL Server offers two methods for an application to provide data consistency: Pessimistic and Optimistic Concurrency Control. In this seminar we’ll discuss what consistency might mean to your applications, and describe the details of how each of the concurrency models works internally. We’ll examine the costs and trade-offs between the two concurrency models that SQL Server 2008 supports through different transaction isolation levels. Finally, we’ll examine the tools available for analyzing and troubleshooting locking problems, including metadata views and the Management Data Warehouse. Want to get a preview of this all-day seminar? Get a one-hour taste at Kalen Delaney's "<a href="http://www.sqlpass.org/24hours/fall2010/Home/IsolationvsConcurrencyWhatAretheChoices.aspx">Isolation vs Concurrency: What Are the Choices?</a>" online webinar at 24 Hours of PASS. Read <a href="http://www.sqlpass.org/Community/PASSBlog/entryid/194/2010-PASS-Summit-Pre-Con-Preview-Kalen-Delaney.aspx">Q&A blog</a> on this session. Even the simplest and most common problems in your Data Access applications like connection timeouts and authentication can take really long to isolate and resolve. In this presentation we will show you how to use publically available tools like Network Monitor, BID Trace, SQL Profiler, Windows Debugger etc to resolve these problems with applications using ADO.Net, SQL Native Client, MDAC etc. Instead of deploying physical hardware for their server deployments, many companies are now turning to virtualization. Since this wave is now also affecting SQL Server deployments, DBAs need to know how to ensure their deployments will be available, scalable, and reliable. Besides covering the things to think about when deploying in a virtualized environment, this session will demonstrate how touse the new SysPrep feature of SQL Server 2008 R2 with Windows SysPrep to create an image for deployment. Hyper-V's Live Migration and VMware's vMotion will also be demonstrated to show how they fit into the availability picture for virtualized SQL Servers. SQL Server Extended Events, new functionality to SQL Server 2008, is a general event handling system. Event Tracing for Windows® (ETW) is a general-purpose, high-speed tracing facility provided by the operating system. Together ETW and Extended Events can be correlated to allow for complex diagnostic analysis and performance troubleshooting. This session will introduce Extended Events, discussing them and showing how to set up sessions and use the technology. Examples will be shown for troubleshooting deadlocks using Extended Events alone and troubleshooting excessive CPU usage using Extended Events correlated with ETW. Most organizations have multi database and application provider strategies which in the past has led to a division of responsibility in the provisioning, maintaining and use of these resources. However, in recent years organizations have looked to streamline these resources which has led to the combination of technologies and the individuals who look after and consume Oracle and Microsoft technologies. This pre conference day is an opportunity to discuss the differences between Oracle and Microsoft technologies and explore how they can be used in conjunction to enhance the IT and information experience. Windows and SQL Clustering are the fundamental technologies for Highly Available SQL infrastructures. Understanding Clustering elements and principles will give you the tools to implement Microsoft and Windows Failover Clustering in your environment. SQL 2008 (R2) and Windows 2008 (R2) make it easier than ever to build stable SQL clusters that increase system availability. ADO.NET Entity Framework 4 (EF4) is Microsoft’s recommended data access layer for new applications. Come see how EF4 enables new capabilities to leverage multiple development approaches, for example the use of code-first, model-first, and database-first. Hear how, regardless of the development approach, developers will benefit from EF4 and the deep integration with the rest of the Microsoft .NET Framework, such as Microsoft ASP.NET MVC, Dynamic Data, and Windows Presentation Foundation. SQL Extended Events is one tool in the arsenal of the DBA who needs to monitor and troubleshoot SQL Server. By combining Extended Events with other SQL Server capabilities such as Reporting Services and StreamInsight you can build advanced diagnostic solutions. This chalk talk discusses some of the finer points of using the advanced capabilities of Extended Events and the ways to combine Extended Events with other SQL Server technologies to implement solutions based on wait based analysis and real time monitoring. This 1-day 300-level seminar focuses on performance and tuning tasks: architecture, memory, waits and events, storage, CPU, DML processing, concurrency, TempDB considerations, and diagnostics toolsets in a scale-up or scale-out infrastructure. Throughout the course, students are exposed to the art of tuning many key components of the SQL Server 2008 environment through seminar content and demonstrations. Performance and tuning best practice methodologies are emphasized demonstrated. The course covers a broad spectrum of topics, including Defining Performance Requirements, Designing Data Collection scenarios and various diagnostic approaches, Defining the SQL08 engine architecture, Best Practices for physical database design, Performance and Configuration considerations, DML and indexing best practices, Storage and Architectural considerations (RAID, SAN, remote storage, backup and recovery) and Operations and Management considerations for large SQL Server installations. Learn from the best: this content was developed by Scalability Experts, who are well-renowned for their enterprise SQL Server consulting services. In this session we will walk through the new T-SQL-related features that are being delivered in the next version of SQL Server. We will be showing off some cool demos as well as describe the use-cases that these language extensions enable. SQL Server Denali introduces a new high availability solution that is designed to increase application availability, provide customers with a better return of investment on their high availability hardware and simplify deployment of high availability. In this session we introduce the new solution. After this session you will leave with a very good understanding of how the solution will help meet the business continuity requirements of your enterprise, the different features and capabilities that allow you to build the solution and the underlying architectural details. Data Compression has been one the most popular features of SQL Server 2008. In this presentation, we look into the common customer questions regarding usage scenarios and performance. We will then examine changes to Data Compression (i.e. Unicode Compression) available in SQL Server 2008R2. We expect you to be already familiar with the basics of ROW and PAGE compression features introduced in SQL Server 2008. This session will cover the set of upgrade planning tools we are working on for the next release of SQL Server. You will learn how these tools will help you assess upgrade readiness and conduct application compatibility testing prior to undertaking the actual upgrade in the production environment. You will see a demo and provide your feedback on other features under planning. Application development has become very interesting and at the same time complex to architect. This session will focus on looking at the key design imperatives that developers and architects need to focus on that can address areas in application development such as connectivity challenges, scalability, high performance, optimal utilization of resources and future proof the application from a supportability perspective. There is typically an ocean of divide between the metrics SAN administrators require in order to set up LUN’s on their SAN’s and the information DBA’s typically have at their disposal. Many of the performance books and online articles try to equate OS counters directly to IO/sec; however as this talk will demonstrate certain statistical and mathematical formulae need to be used in order to get this right. This session describes exactly what to gather and then, using sampling statistical techniques as well as Fourier transform equations in Excel, it will be shown just how to convert the gathered metrics into average and peak loads understandable in SAN parlance telling the SAN administrator exactly what needs to be planned for. Finally, it will be shown exactly how to measure seemingly under-performing SAN’s in order to derive the precise amount by which they are under-performing. SQL Azure was released in January, 2010 and this session will discuss the learnings from a few of our first live, real-world implementations. We will showcase a few customer implementations by discussing their architecture and the features required to make them successful on SQL Azure. The session will cover our lessons learned and best practices on SQL Azure connectivity, sharding and partitioning, database migration and troubleshooting techniques. This is a 300 level session requiring an intermediate understanding of SQL Server Session focuses on SQL Server Application model today and new developments in this area that will revolutionize and greatly simplify the development and management of SQL Server applications. This one-day 300-level seminar provides students with the knowledge to help manage their highly-available Microsoft SQL Server 2008 database server deployment. Some of the High-availability topics covered include: Failover Clustering Configuration and Management (including geographically dispersed failover clustering and storage replication), Implementing Database Mirroring, Peer-to-peer Replication, Configuring Log Shipping to maintain a warm standby server, Managing SQL08 R2 Replication, Managing Scalable Shared Databases, Performing Online Index Maintenance and Recovering from user errors. This seminar presents various demos and highlights key topics to ensure that the students understand the considerations for the various high-availability solutions available with Microsoft SQL Server 2008. Learn from the best: this content was developed by Scalability Experts, who are well-renowned for their enterprise SQL Server consulting services. Tier-1 OLTP workloads represent a daunting challenge for the DBA. Tuned and balanced HW/SW architectures deliver predictable high performance, and reliability. Learn how modern architectures can deliver optimized and balanced performance, faster out-of-the-box time-to-value, and significant TCO advantages. In this interactive presentation we will share our latest research so you can understand how to deliver extreme performance in very large Tier-1 OLTP workloads. Advances in virtualization/management and hardware technologies have created tremendous opportunities to consolidate data based applications. See how modern architectures can deliver efficient virtual and scalable infrastructure for 100s of DB applications, that can be efficiently, reliably and manageably consolidated. In this presentation we will focus on tuned and balanced architectures that can drive DB deployment and management costs towards $0. SQL Azure represents significant shift in DBA’s responsibilities. Some tasks become obsolete (e.g. disk management, HA), some need new approaches (e.g. tuning, provisioning) and some are brand new (e.g. billing). This presentation goes over the discrepancies between administering the classic SQL Server and SQL Azure. In this session we will try to provide the “full story” about I/O. We will start at the hardware level working our way up the stack through Windows and into the SQL Server storage engine (including Analysis Services). We aim to provide in depth information about all aspects related to high performance storage design for SQL Server and best practices on this naturally complex subject. This session is similar to past SQLPASS sessions however as we continue to learn more about an ever changing storage landscape we share our latest learnings from real world customer deployments. How do they scale; do they scale up or scale out? How are High Availabilty and Disaster Recovery architected? Are there any common techniques that bring the largest gain? This session is intended for people interested in learning how we have solved some very hard problems in the database tier. Take the lessons we have learned back to your business to improve your solutions. In this session we will share best practices on building a robust replication infrastructure to ensure availability in large mission critical scenarios. Deploying and managing distributed architecture that can scale and solve key business problems requires a clear understanding of the pros and cons of the technology and also an understanding of deployment and maintenance best practices. We will: • Illustrate this based on real-world customer scenarios and replication architectures. • drill down into some of the key challenges and talk about mitigations SQL Server 2008 and 2008 R2 provide a strong foundation for building data warehouse applications. This talk describes how to use them most effectively for data warehouses and data marts. We describe best practices for choosing a processor, storage, and memory configuration for your DW, including a brief description of Fast Track reference architectures. We cover how to choose file and file group structures, and how to define index structures to reduce and speed up I/O. We describe data modeling with a focus on star schemas. We cover how to use pre-built aggregates to speed up queries, including use of indexed views and user-defined summary tables. We describe how to load data into your data warehouse efficiently during a typical nightly load cycle. In addition, we briefly review new features for DW in SQL Server 2008 and 2008 R2, such as few outer rows parallelism, partitioned table parallelism, MERGE, minimally logged INSERT, and Change Data Capture, with an emphasis on how they can make your life easier for querying, updating, and loading your DW. In this session, we will discuss lessons learned loading data into PDW using SSIS and the DWLOADER bulk copy utility. You will learn about exploiting the power and speed of CTAS to perform inserts and updates coupled with partition switching. You will also learn about differences between loading distributed vs. replicated tables. Finally, you will learn about loading PDW using ETL vs. ELT. The best DBAs work hard so that they don’t have to, well, work hard. In this session, we’ll discuss how you can use the PowerShell cmdlets and snap-ins to create scripts that automate the more mundane tasks in your role as a DBA or developer. We’ll create scripts that check the status of SQLAgent jobs, verify the configuration of your servers, and retrieve information from your SQL Server database. You can even store your results in a database table if you’d like. This session is mostly demos with only a few PowerPoint slides to get us started. This session will show you how a better understanding of how the Query Optimizer works can help you to improve the performance of your queries. I will show you the top 10 Query Optimizer topics that can give you the most benefit by focusing both on concepts and practical solutions. The SQL Server Query Optimizer is a cost-based optimizer whose job it is to analyze the possible execution plans for a query, estimate the cost of these plans and select the one with the lowest cost. So a better knowledge of how the Query Optimizer works can help both database developers and administrators to get better performance from their databases. Several areas of the query processor will be covered, everything from troubleshooting query performance problems and identifying what information the Query Optimizer needs to do a better job to the extreme cases where, because of the its limitations, the Query Optimizer may not give you a good plan and you may need to take a different approach. The one-day 300-level seminar focuses on the various security features available in SQL Server 2008 to help you satisfy your compliance requirements, whether from HIPAA, PCI DSS, Sarbanes-Oxley Act, or any other industry governing body. Throughout the seminar will be exposed to the security principles and various tools in the SQL Server 2008 environment through a series of demonstrations. Participants will learn about key topics including: Secure installation and configuration of SQL08, managing access and range of user control at both the instance and database level, setup and management of certificates and asymmetric keys, setup of secure communications to and from SQL08, setup of transparent database encryption, creation of SQL Server Agent jobs with varying roles and proxies and auditing with SQL Server Audit/C2 Audit Tracing/DDL and Logon Triggers, Security best practice methodologies are emphasized in the speaker content and demonstrations. The seminar covers a broad spectrum of features and highlights key areas required for building a secure database environment in real-world scenarios. Throughout the seminar and where appropriate, feature availability in SQL Server versions – Developer, Standard, Enterprise, as well as key feature differences between SQL Server 2005 and SQL Server 2008\2008R2 will be explained. Learn from the best: this content was developed by Scalability Experts, who are well-renowned for their enterprise SQL Server consulting services. In this presentation Ralf Dietrich and Andreas Wolter will assume the role of the DBA/DBDev and an external consultant. Playing through different scenarios, the external consultant will resort to all kinds of tricks to access data in a supposedly properly arranged database environment, which was actually not intended by the DBA/DBDev. Of course, he will then have the chance to fix this gap. The match is repeated on different levels with different scenarios until a desirable condition is reached. This session holds in store surprising insights even for experienced administrators, covering topics from schema ownership implication to best practices for user-permissions, while administrators may still allow the external consultants to do all necessary work. This session will discuss the latest developments in tools and environment for SQL Server developers Maintenance of SQL Server can be easier. This session will show how to utilize SQL Server Agent for automating repeating tasks, alerting DBAs and enforcing Business Rules. Session will be a series of demos that illustrate ways of using existing SQL Server functionality to achieve business objectives. Live session will include the following scenarios: 1.Configuring SQL Server Agent for first time 2.Creating a simple Job 3.Creating multistep Job 4.Creating Periodic Job 5.Create Job Notifications 6.Creating simple Alerts This one-day 300-level seminar provides students with the knowledge to help efficiently manage their Microsoft SQL Server 2008 database server. Some of the Manageability topics covered include: working with the various SQL08 R2 Management tools, managing data files and log files, designing & implementing efficient backup and restore strategies, understanding configuration options, securing a SQL08 database, setting up a central management server, leverage policy-based management for compliance, storage optimization, using Resource Governor, and automating administrative tasks using maintenance plans and SQL Server Agent. Various best practices and methodologies are demonstrated within the demos highlighted in seminar material with special emphasis on managing and maintaining large databases greater than 10TB. Learn from the best: this content was developed by Scalability Experts, who are well-renowned for their enterprise SQL Server consulting services. SQLDIAG is a great tool that was introduced in SQL Server 2005 to help co-ordinate the collection of perfmon logs and SQLTraces as well as gathering other system data. In this session you’ll learn how to configure, customize and run SQLDIAG as well as the perfstats script from Microsoft which adds locking, blocking and wait stats to the list of collectors that SQLDIAG co-ordinates. This tool is one of the secrets of the trade for efficient data collection and this session is a must for anyone not using it extensively already. Using the output from sqldiag we'll then look at SQLNexus to analyze the results. I'll show you how to configure, run and draw conclusions from the reports created by this tool which is by far the most useful piece of software in the troubleshooting kit bag of people that have taken the time to learn it. This session is based on 2 chapters from Professional SQL Server 2008 Internals and Troubleshooting. Regulatory bodies...end-users...your boss. They all want answers. Many questions are easy enough to deal with: "Did someone drop my view?" Others are a bit trickier: "What was the previous value of this row?" And some are seemingly impossible: "Who selected the data from this table over the past week?" For many DBAs, the answer to some or all of these questions is often "Umm..." But don't blame yourself; getting this information in SQL Server has never been easy—until now. SQL Server 2008 ships with new features designed to help you track and report on what happened, who did it, and when. In this session, you will learn about Change Tracking, Change Data Capture, and SQL Server Audit. Each provides a distinct set of capabilities and has specific strengths and weaknesses. Looking at all three in turn, you will see how they work and where you can leverage them in your SQL Server infrastructure. If you're used to saying "Umm..." get ready to say "I'll be right back with the answer." Most administrative functions consist of repetitive sequences of tasks which just take time. PowerShell is a powerful scripting environment which allows an administrator to automate those repetitive tasks, providing better results to the organization while saving the administrator time, and ensuring that the tasks get done properly every time. This session will give an introduction to PowerShell and demonstrate a number of scripts which help SQL Server administrators effectively manage their environments. Dynamic Management Views (DMV) expose a wealth of information to the database administrator. However, they also expose information that is vital to the database developer. More often than not people gather performance metrics through server side traces. This session will show how to gather information from the DMVs for currently executing, and recently executed queries. The session will demonstrate combining this information with other DMVs to get more intersting information such as the query plan and query text. I'll show where you can get aggregate information for the queries in cache to determine which queries are being frequently accessed or using the most resources. I'll show how to determine which indexes are being used in your system and which are not. All of this will be focused, not on the DBA, but on the query writer, the developer or database developer that needs information to tune and troubleshoot data access. Taken performance tuning 101? Know the common stuff like Perfmon, SQL Trace and getting used to DMVs? Even impressed friends and family with (mostly correct) explanations of a few of the several hundred waitstats but still feel something is missing? Feel like Groundhog Day at work, solving the same performance problems day after day? Performance tuning is a tough subject. Many experts charge lots of money to perform tasks that either make no sense or look so easy you think your pet Capuchin monkey can do it. The results are almost as varied as the methods. A few work great, others leave the system with more problems than before they started. What next? This session will dissect some real life performance tuning cases and identify common mistakes, why some remedies actually work while others fizzle after a while and why some solutions end up creating more problems. Most of all, we will learn to identify and fix root causes and instead of chasing symptoms endlessly. Transactional replication can support synchronizing from as few as a hundred to as many as hundreds of millions or more of changes a day across multiple servers. However, replication is more than clicking your way through wizards; Monitoring and tuning are required to achieve optimal performance and the number of processes involved increase the likelihood that problems can (and usually will) occur. This session is for DBA already familiar with the basics of transactional replication and looking to go beyond the setup wizards to gain a deeper understanding of the technology. We'll cover monitoring techniques, calibrating performance, and troubleshooting common replication problems plus share some tricks and tips gleaned from years of experience working with high volume, multiple datacenter topologies. Learning how to detect, diagnose and resolve performance problems in SQL Server is tough. It often takes years of on-the-job experience to learn how to use the tools and techniques that help you detect when a problem is occurring, diagnose the root-cause of the problem, and then resolve the problem. So strap yourself in to a time machine, because you’ll learn in less than two hours what it took Kevin years to figure out through hands-on troubleshooting and problem solving. This hands-on session will take you from the very start of a problem situation on SQL Server, showing you how to use the native tools that help you keep SQL Server at top performance and problem free. This session will detail and demonstrate: - Performance Monitor (PerfMon) - SQL Profiler/Server-side Trace - Correlating PerfMon and Profiler results - Using Dynamic Management Views (DMVs) - Advanced Diagnostics Using Wait Stats - Graphic Execution Plan and Transact-SQL Showplan - When to use Trace Flags These tools and techniques can make your long days shorter and make difficult troubleshooting activities much faster and easier. Every DBA needs to know how to keep their SQL Server in tip-top condition, and you’ll need these skills to do it. Please come to this session to learn the options for SQL Server consolidation and the available tools to help you plan, deploy, and manage the consolidated environment. We will share what other customers are doing to consolidate their server sprawl and discuss best practices gained from working with them. Microsoft SharePoint is a large part of many organizations; landscapes, and becoming larger each day. For the most part, the SharePoint systems have been the purview of the Systems Administrator, and the data professional had little interaction with it. But as the systems become larger and organizations try to make better use of their data resources, the data professionals finds themselves inheriting the SQL Server portion of SharePoint, often with little background or resources. Come hear Buck Woody, Microsoft’s ‘Real World DBA’ explain what you need to know about SharePoint as a DBA. He’ll cover a quick overview of the product and the duties of the SharePoint administrator, and then focus in more on the database side of the equation. You’ll learn: <li>SharePoint System Architectures, and how to lay out and configure the database servers for them</li> <li>What the SharePoint Databases are, what they do, and where they are stored</li> <li>Best Maintenance Practices for SharePoint Databases</li> <li>Security for SharePoint and SQL Server</li> <li>Disaster Recovery options</li> <li>Monitoring, Tuning and Performance for SharePoint Database systems</li> A variety of customers have been working with the new Parallel Data Warehouse -- PDW -- both in the TAP program and lab-based validation engagements. In this session we will share a number of best practices and learnings about optimal database design for PDW, pointing out any differences from SMP-based SQL Server, and walk through some real world examples of tuning complex queries to optimize performance. SQL Server provides a rich set of security features that allow customers to satisfy their security goals. Everything from encrypting sensitive data to recording all activity within a particular database is easily attainable with the features introduced in 2008. However, one topic that many struggle with is in the area of Separation of Duties, sometimes called Role Separation. The subject frequently comes up when facing regulatory compliance, usually in the form of “How do I prevent my administrators from seeing my data?” While SQL Server does not provide a packaged solution to facilitate Separation of Duties, it does provide functionality that allows Separation of Duties to be achieved. Come to this session to understand how to leverage the functionality available in SQL Server implemented through free community tools and achieve all of your compliance goals. This talk will focus on query performance troubleshooting starting with Query Processor. We will focus specifically on sources of sub-optimal plans and how to detect them; : excessive recompiles, long compiles, memory gating mechanisms during compile, memory grant mechanisms during execution and possible ways to prevent and/or workaround some of the stated problems. SQL Server 2008 introduced the Data Collector and Management Data Warehouse (MDW) for helping track down performance problems with low overhead for the monitored server. This session goes deep into the warehouse and shows you how you can write your own reports with Reporting Services leveraging stored procedures and views provided in the MDW database. This session focuses on the most practical aspects of troubleshooting performance problems related to SQL Server. This will start with an overview of the core aspects of SQL Server and its environment and how they all play together in terms of performance. We will then cover various techniques for narrowing down the bottlenecks affecting performance. From there we will drill down into each type of bottleneck and investigate ways to address the problems affecting performance. Areas to be covered include but are not limited to: storage and I/O, memory, CPU, tempdb, plan reuse, query optimization, indexes, file and wait stats, and more. The session will include lots of demos with code that can be easily utilized in your own SQL Server environment. This session provides an in-depth discussion on how to build a high performance, scalable architecture with SQL Server 2008 R2. The session will dive into latest features and their specific implementation requirements. Several real-world customer reference architectures, scale-up and scale-out, will be discussed along with implementation best practices. This session focuses on scalability, HA and DR features of SQL Server 2008 R2. This session provides enhancements in SQL Server 2008 R2 and how they can be used to bring business enhancement in an enterprise database environment. Along with specific features in R2, the session will discuss a real-world implementation of R2 and the lessons learned. Topics such as database mirroring, snapshot databases, clustering, resource governance, Peer to Peer replication and other related topics will be discussed in-depth. Why are my spatial queries slow?! If you ask yourself that question or want to learn how to investigate and address performance issues with spatial queries in SQL Server, this is the presentation for you! This presentation describes how SQL Server 2008 spatial indexes work and gives tips and presents the tools on how to improve your spatial application's performance even when it needs to scale to large amounts of spatial data. This session will be a two part session in which we will be focusing on two of the biggest topics in the DBA field, how to properly design your storage and virtualization solutions. Storage can be one of the biggest bottlenecks when it comes to database performance. It’s also one of the hardest places to troubleshoot performance issues because storage engineers and database administrators often do not get along. We’ll be digging into LUNs, HBAs, the fabric, as well as the storage itself. In the second half of the day we'll be looking into the pros and cons of moving SQL Servers into a virtual environment. Specifically we’ll be looking into when it’s a good idea and when it’s probably not a good idea. Like everything in the database world there are no hard set answers as to if virtualization is a good idea or not. We'll look into how tie the virtual platforms to the storage array so that you can maximize the storage performance for your SQL Servers and the virtual environment. Want to get a preview of this all-day seminar? Get a one-hour taste at Denny Cherry's "<a href="http://www.sqlpass.org/24hours/fall2010/Home/StoragefortheDBA.aspx">Storage for the DBA</a>" online webinar at 24 Hours of PASS. Read <a href="http://www.sqlpass.org/Community/PASSBlog/entryid/183/2010-PASS-Summit-Pre-Con-Preview-Denny-Cherry.aspx">Q&A blog</a> on this session. Starting with SQL 2008, there is a new feature called Extended Events which every DBA and Developer should become familiar with. Extended Events allow us to monitor and troubleshoot in ways not possible with previous methods while incurring less overhead on the server. Come see how we can utilize this new feature to track waits all the way down to the query level with minimal overhead. We will also cover the common waits found in most SQL Server instances and discuss ways to avoid or address them. This session covers real life customer deployment scenarios to achieve high availability and disaster recovery, lessons learned, and best practices. The content is based on various customer real-life production deployments. Technologies include: Fail-over Clustering, Database Mirroring, Log Shipping and Peer-to-Peer Replication. You will learn how customers utilize these technologies to achieve their HA and DR goals, including reduced time for upgrades from SQL Server 2005 to 2008 and for applying Service Packs. In addition, there will be a panel of customers to interact with and get responses to practical questions. Kerberos is a common pain point for customers and accounts for a large volume of calls to Customer Service and Support (CSS) at Microsoft. We will dive into how Kerberos works and how to configure it correctly for your deployment. Find out where that SPN goes and what the big deal is with delegation. This will include SharePoint, Reporting Services and SQL Server. We will also bust a few myths regarding Kerberos that will help you with your environment. There are also some common tools that will be demonstrated that you can use to pinpoint issues and correct them. Ever wonder how an update query actually works? What are all those strange operators in the query tree? How do I tell if an update query is performing well? In this talk, Conor will go into depth about how the Query Optimizer builds plans for Insert/Update/Delete/Merge queries, including understanding operators like common subexpression spools and split/sort/collapse and optimizations like narrow vs. wide plans, non-updating updates, prefetch optimizations, the “with outputs” feature, and how we can maintain indexed views incrementally. The SQL Server database engine uses latches for internal synchronization to critical operations. So why would you care about what a latch is? Well, if you have ever monitored a SQL Server closely, you know you can’t escape latches. Come to this advanced session exploring the internals of how SQL Server uses the concept of latches for synchronization of key internal data structures. This talk is a very advanced “500” session that may include the use of advanced diagnostics such the Windows debugger and discussion of Windows APIs. I will assume the audience has a basic working knowledge of the SQL Server engine including an understanding of some of the more common SQL Server Engine DMVs such as sys.dm_exec_sessions, sys.dm_exec_requests and sys.dm_os_wait_stats. NEW to PASS this year - Lightning Talks. A lightning talk is a short, prepared talk that is no more than 5 minutes in length-which keeps things moving quickly. One session everyday will be dedicated to lightning talks and 7 speakers will participate in each talk. NEW to PASS this year - Lightning Talks. A lightning talk is a short, prepared talk that is no more than 5 minutes in length-which keeps things moving quickly. One session everyday will be dedicated to lightning talks and 7 speakers will participate in each talk. NEW to PASS this year - Lightning Talks. A lightning talk is a short, prepared talk that is no more than 5 minutes in length-which keeps things moving quickly. One session everyday will be dedicated to lightning talks and 7 speakers will participate in each talk. Learn to network and make professional connections that will pay off for years to come from best-selling author, communications trainer and “small talk” expert, Don Gabor. Using demonstrations, exercises, role plays, and coaching that focus on networking at breakfast, lunch, between sessions and at the evening reception Don will guide you step-by-step through the networking process. By practicing confidence-boosting skills, tips and strategies you’ll see that connecting with your colleagues is easy, fun and profitable! Take-aways include: <li>Using body language to build instant rapport</li> <li>Introducing yourself and remembering names</li> <li>Breaking the ice & keeping the conversation going</li> <li>Transitioning from topic to topic</li> <li>Ending conversations and working the room</li> <li>Following up and building business relationships</li> Each workshop attendee will receive an autographed copy of Don’s best-selling book, Turn Small Talk into Big Deals. Click <a href="mms://passmedia.sqlpass.org/share/summit2010/dongabor/2009_testimonials.wmv">here</a> to see testimonials from Summit 2009 attendees! Most database professionals don’t look for a new job until they are either not happy with their current employment, or they have recently become unemployed. That, however, is the worst time to start getting your information in order. During this session we will discuss what you should be doing on a regular basis, rather than waiting for a job changing event to dust off your resume. Understanding that the competition is growing for each database role out is the first step in ensuring that you remain marketable. Have you ever considered that your image should be part of your professional plan? Many people would be surprised at how a slight change in outward appearance can not only build confidence but also positively affect their bottom line. This session will focus on increasing professionalism and approachability in the workplace as well as boosting personal confidence. Christine will review simple ways to improve your outward appearance with help from SQL Server Community personalities Jeremiah Peschka and Brent Ozar. They will model a variety of comfortable yet savvy fashion options you can sport to meet clients, test servers, or even belt out a tune at SQL Karaoke. If you’re like many SQL Server professionals, communicating with non-technical colleagues can present a variety of challenges, especially if you need to give project updates, detail procedures, share information, correct mistakes, or explain why a process does or doesn’t work. No matter where you work or in what capacity, you will come in contact with many coworkers whose communication styles are different from your own. In this interactive workshop you will learn how to get your points across more effectively to your non-technical colleagues. Take-aways include: • Determining your personal communication style • Understanding the strengths and weaknesses of the four communication styles • Adapting and expanding your communication style • Creating a communication strategy for specific colleagues. Each workshop attendee will receive an autographed copy of Don’s book, "Speaking Your Mind in 101 Difficult Situations." Are you thinking of starting a blog? Or are you interested in presenting at events like SQL Saturday and the PASS Summit but not sure how to get in the game? Don’t let uncertainty keep you from contributing – the SQL Server community needs you! This interactive session will explore reasons for blogging and speaking and offer advice on topic selection, improving writing and speaking skills, seeking out places to write and speak, and things to (and not to) do to once you get started. The number of girls and women choosing technical careers has been decreasing. As women who remain in those careers and the men who work with them, we can just look around our work environment and see that the men greatly out-number the women when it comes to hi-tech jobs. It is important that we all work together to inspire the girls and women in our lives to consider technical careers. Women bring a different, and necessary, perspective to the technical jobs they hold. From participation in organized programs to informally supporting female co-workers, there are many ways to make a difference. Join the discussion and learn how to encourage and inspire the women in your life to join the technical venue. This session will be led by members of the PASS Women in Technology Virtual Chapter. It will be a continuation of the discussion initiated at last year's WIT luncheon. All are welcome. Learn about Microsoft's most advanced technical SQL Server certification from program owner Joseph Sack. Hear about key changes that make this certification is more accessible to top tier candidates than ever before, along with how you can achieve the coveted SQL Server MCM certification. Come hear Buck Woody (Microsoft's Real World DBA) and Brent Ozar (SQL Server Certified Master) explain how they make high scores at presentations. It's not luck, charm or (surprisingly) good looks - there are tips and tricks you can use to make your own presentations rock. With Buck and Brent in the same room it's much like Forest's Box of Chocolates, but you're sure to learn more about presentation techniques that you can extract into your own style. Speaking at a conference like the PASS Community Summit can seem like a daunting task. You might be asking yourself “How would I even get a session considered for something like that?” Each year, hundreds of abstracts are submitted for the precious few slots at the PASS Community Summit. During this session we will discuss the anatomy of an abstract submission and work through writing a sample abstract. Managers and leaders are not necessarily the same. In a work setting, if asked to think of a manager we tend to think of “the boss”. If asked to think of a leader we tend think of those that make things happen. Effective leadership goes beyond managing and is about making things happen. Using my career experiences from DBA to Vice President as a backdrop and teachings from two inspiring books, Leading Change and Execution, this presentation will explore key attributes, behaviors and techniques that will help you close the gap between what you promise and what you deliver. Regardless if you are already a manager, planning to lead a team or just want to be prepared when given the opportunity to lead, this presentation will help you understand key attributes of effective leadership and techniques for making things happen. Interaction is a key part of this unique presentation. There are many pros and cons that need to be weighed when considering a move into a consulting position. Do I become self-employed or should I join a firm? What is the difference between a sole proprietorship and an LLC? How can I get business? These questions will be discussed as we probe into the life of a consultant. Ever been in a meeting that drones on and on? It starts late, runs long, and doesn’t really accomplish anything. It’s a complete waste of everyone’s time. Worse yet, since nothing was resolved you’ll have to have a follow up meeting. Argh! In this session you’ll learn some of the keys to conducting an effective meeting. You’ll gain practical tips for making your meetings more productive and dramatically improving one of the most inefficient parts of your day. A good professional development plan has to go far beyond ‘learning more technology’; it has to encompass the wide variety of skills you need to build the next step in your career, and it has to include a variety of learning methods. We’ll discuss how much time and money you should budget when building your plan, and how the distribution of time and money changes as your career evolves. We’ll look at the growth phases you’ll encounter, learning how you learn best, how to keep up with new technology without burning out, and even how things like blogging and Twitter can be an interesting part of your plan.