At PASS 2010 after the public release of CTP1 of SQL Server Denali, the product team demonstrated a replacement for SQL Profiler built on top of Extended Events. The writing is on the wall for SQL Trace and the future of diagnostic profiling in SQL Server is Extended Events. In this workshop you will learn Extended Events from the ground up; from the original implementation in SQL Server 2008 to the enhancements that have been made in SQL Server Denali that redefine data collection for troubleshooting in SQL Server. Extended Events provide more information about the operations of SQL Server than have ever before been available in the product. Unfortunately, there is a significant learning gap between Extended Events and SQL Trace; and the lack of a UI has only made the learning curve that much steeper. This full-day deep dive workshop will explain the basic concepts of extended events, spin that information into some basic patterns, and build on those patterns to create complex custom functionality that will prepare you for the future. Learn firsthand from the developer of the SSMS Addin for Extended Events in SQL Server 2008 how to leverage Extended Events in your own environment. Dynamic Management Objects(DMO) 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, but they don't have to. This session will show how to gather information from the DMOs for currently executing, and recently executed queries. The session will demonstrate combining this information with other DMOs to get more interesting 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. In this session we'll be looking at all the various places within the application stack that data can be encrypted or hashed, so that as an application/database administrator/developer/architect you can make the best decision as to where in the application stack to do this work. These layers include the application layer, the middle tier, the database layer, encrypting over the wire, transparent data encryption, encrypting using your MPIO driver and offloading encryption to your HBAs. It's Friday, 05:00pm. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast? Which tools provides you SQL Server for this task? In this session you will see a lot of different tools with which you can do effective SQL Server troubleshooting. You will learn how to identify performance bottlenecks with those tools, and how to resolve the identified bottlenecks. You have mastered the art of linking a dataset to a table in SQL Server Reporting Services. You have solid, reliable reports that you and others depend on. Learn how to take them from basic to "wow", using features such as tablix, lists, images, and charts. At the end of this session, you will be familiar with: - Report properties such as headers, footers and backgrounds - Tablix properties such as ToolTip and NoRows - How to build and customize a matrix - How to effectively use lists, an often overlooked feature - Adding charts to make reports visually appealing and easy to understand “The system is slow. It was much faster last week! This is taking forever.” Statements like these from users can be frustrating for any DBA, unless you have the data to prove how the system regularly performs. In this session we will cover some of the tools and techniques freely available to SQL Server DBAs for capturing baselines that can be used for measuring performance, capacity planning and understanding usage patterns. We also discuss how these utilities and their data can be used to identify warning signs and troubleshoot performance issues. Long-term collection and storage methods for data are reviewed to help you create a plan for building your own repository when you get back to the office on Monday. Tools covered include: Windows Performance Monitor, PAL, Profiler, ClearTrace, RMLUtils and SQL Server DMVs. Database administrators - you don't really trust your third party software vendors, do you? Software vendors - you think most of the DBAs out there are hiding things, don't you? Well guess what, you're both right! Join Erin Stellato (representing vendors) and Mike Walsh (representing DBAs) for an interactive session where they will tackle some of the toughest challenges that DBAs and vendors face when working with each other. Attendees will walk out with a better understanding of what both parties can do to contribute to a successful relationship, as well as a set of fair questions to ask to set the right expectations from the start. Attend our session to see if a Vendor and DBA really can get along, and listen in on some real life horror story examples of how not to do it. You’re a T-SQL developer or DBA and you learned most of what you know about T-SQL by the seat of your pants. You can handle day-to-day T-SQL querying and programming tasks in a reasonable manner, but you’re looking for better and more efficient solutions. This seminar is for you. It covers common T-SQL querying and programming tasks and shows polished, optimal techniques to handle those. Some of the techniques that you will learn in this seminar are very recent developments. You will learn how to utilize T-SQL constructs available in SQL Server 2008 in creative and efficient ways. You will also get introduced to new T-SQL features planned in the next major release of SQL Server—code-named Denali, and see how you will be able to improve your solutions in the near future. There are lots of exciting new T-SQL features planned in SQL Server Denali—some quite profound—and knowing about them ahead can make you better prepared, affecting how you right code today. <p> Read <b><a href="http://www.sqlpass.org/Community/PASSBlog/entryid/345/2011-PASS-Summit-Pre-Con-Preview-Itzik-Ben-Gan.aspx">Q&A blog</a></b> on this session.</p> Some behaviors in T-SQL are clear bugs and some are clear features. But being a relational language with many unique aspects compared to procedural languages, sometimes you may find yourself wondering whether a certain behavior is in fact a bug or a feature. This session covers various cases that aren’t clear cut and explains why they are considered a bug or a feature. Through those cases the session tries to shed some light on some important principals of this unique language. Anyone new to developing Analysis Services Cubes will be thrilled when processing a cube successfully for the first time. Even though a cube is built there are many potential design mistakes that are often made. This session reviews common design mistakes that are made when building Analysis Services Cubes. You will learn how you can build a more effective cube that provides faster performance, faster development and simpler maintenance. Page and Row Compression are powerful new tools. Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2. We can turn it on, we can turn it off, but we want more! What are Access Methods and how in the SQL Engine do they affect Compression? What are the “Gotchas” of Page Compression? How does Compression differ in the way it treats Non-Leaf & Leaf Level pages? What additional functionality did we get in DBCC Page, DMV’s, Perfmon Counters, and Extended Events to support our usage of Compression? How do complex query plans affect Compression? Come find out! During last year's 24 Hours of PASS, Brent Ozar gave you a script to quickly assess the health, performance, and security of a SQL Server you've never seen before. He's been steadily refining it, adding more checks and helpful information, and now he's ready to unveil the next generation. This script makes the life of a production DBA easier, and the whole session will be spent doing one long live demo on a server with all kinds of dangerous pitfalls. You will walk away from this session with an understanding of how to use the Policy Based Management and Central Management Server to complete a daily checklist against your whole SQL Server farm. We will cover the basics to evaluate, monitor and apply best practice policies, which are included out of the box with SQL Server 2008. We will also make custom policies to cover additional best practices to evaluate daily checklist items. Finally, we will combine Policy-Based Management and Central Management Server with the Enterprise Policy Management Framework on codeplex to automate the monitoring process and create manager friendly reports to monitor your checklist items against the whole SQL Server farm. Do your users have the same old, mundane table reports they’ve been using for decades? Stop living in the past and make your reports pop. Learn to use design patterns, best practices and visuals that pack more usable information into fewer, more usable reports. Use Reporting Services and other Microsoft BI tools to create dynamic, browse-able reports that answer important business questions at a glance and then let them explore the details to gain business insight and take action. Sure, Business Intelligence is all the rage but you don’t have to invest in an entire BI platform to have fresh, usable reports. These design techniques apply to enterprise analytic solutions and smaller-scale operational reporting. When it comes to the availability landscape, Denali changes the game for SQL Server deployments. There many new enhancements and for traditional failover clustering deployments for instances such as flexible failover policies and multi-site clusters that can and will change the way you plan, deploy, and administer. In addition to the changes to failover clustering, there is a whole new feature: availability groups. Availability groups may enable some scenarios that you have had trouble with in the past, such as using a standby database for reporting and some maintenance. This full day will take a deep dive into these two features and tell you everything you need to know about not only the changes to failover clustering, but the proper way to plan, deploy, and administer availability groups to be successful from day one with Denali. If you do not want a basic "this is failover clustering" or a quick introduction to availability groups, nor do you want just a day of theory but want to see everything live (including combining clustered instances with availability groups to increase availability even more), this day is exactly what you need. Read <b><a href="http://www.sqlpass.org/Community/PASSBlog/entryid/362/2011-PASS-Summit-Pre-Con-Preview-Allan-Hirt.aspx">Q&A blog</a></b> on this session.</p> Learn the fundamentals of MDX query design for Analysis Services cubes. Migrate your SQL skills to this simple and elegant language that will enable you to unlock the awesome power of a cube and to gain deep insight from a single version of the truth. Learn to develop dynamic, advanced reports by parameterizing MDX queries using expressions and custom code. One of the most exciting new features in SQL Server Denali is support for Windows Server Core. Server Core is a minimal installation of Windows which has no GUI. For those who want to reduce patching and increase security, Server Core is the way to go. This session will cover the tips, tricks, and best practices for what you need to do to get SQL Server Denali up and running both for clusters and standalone implementations without any user interface. SQL Server implements three different physical operators to perform joins. In this session, we will examine how each of these operators works, including its advantages and challenges. Using real life examples, we will better understand the logic behind the optimizer’s decisions on which operator to use for various joins. Finally, we will learn how to avoid some common join related pitfalls and how to get better performance from our queries. Many virtualization customers have begun looking at virtualizing their tier-1 applications, such as their ERP systems, SAP and SharePoint environments. At the heart of many of these business critical systems is Microsoft SQL. As virtualization has evolved applications once considered too heavy to move out of the physical world are now being reevaluated for virtualization. Microsoft SQL can now take advantage of many of the features that vSphere provides out of the box such as support for 64-bit technologies, NUMA support and hot-add CPU and memory. This session will cover design considerations for virtualizing SQL workloads, performance and scalability information, high-availability options, support considerations, as well as what’s new in ESX 5. SQL Server "Denali" delivers many highly anticipated features that some feel are long overdue. In this session you will get a first-hand look at many of these new features relating to the engine (contained databases, AlwaysOn, and T-SQL enhancements), client tools (Management Studio, Books Online, and "Juneau"), and changes to setup and system requirements. You'll also learn about some features that will not be present in Denali, deprecated functionality that will stop working, and how to submit feedback to Microsoft about bugs and feature suggestions for current and future versions of SQL Server. Bad habits: we all have them. SELECT * is the obvious one; but in this session you will learn about various other habits and why they can be bad for performance or maintainability. Come learn how these habits develop, what kind of problems they can lead to, and how you can avoid them. Eliminating these habits will lead to more efficient code, a more productive work environment, and - in a lot of cases - both. Come learn the ten most important skills that every accidental DBA, developer, or even manager should be able to do in SQL server. If you want to know where to start working with SQL server or which 10 things will get you through most of your days, some see Steve Jones show you how to accomplish these ten important tasks and explain why they are important. These ten skills represent the 80/20 rule in action; if you can do these ten things, you can successfully get through most of your days at work. Everyone wants a dream job that they enjoy going to each week. However finding that job and getting yourself hired can be hard for most people. Steve Jones will give you practical tips and suggestions in this session that show you how to better market yourself, how to get the attention of employers, and help improve the chances that the job you want will get offered to you. Learn about networking, blogging, writing, speaking , and more from Steve Jones, who has successfully used these techniques to find his dream job. This session introduces you to the need for scalability in the cloud, and presents various aspects of data partitioning in SQL Azure by offering a tour of SQL Azure Data Federation and an introduction to the popular open-source Enzo Framework available on CodePlex. This session will provide sufficient information to give you the tools necessary to decide whether or not sharding may be appropriate for your applications, what are some of the challenges related to data federation and how to deal with them, and will give you a solid understanding of how sharding can improve overall application performance and scalability. You are designing a BI Solution and your customer ask you to keep a snapshot of the status of all their documents (orders, insurances, contracts, bills...whatever the word "document" may mean) for all the days of the year. They have millions of documents and they want to have in their Data Warehouse all the data they have gathered right from the very first operating day. If you have 1 million of documents (on average) and you have to keep a snapshot of them for each one of the 365 days in a year, and you have 10 year of history, you're going to have a 3 billions table just to start with. That's a very big and challenging number, and you may have not the option to buy a Parallel Data Warehouse. In this session, we'll see how we can turn the usual snapshot tables into temporal table so that we can store time intervals in order to avoid data duplication, while keeping the Data Warehouse design usable by Analysis Services (that doesn't know what an interval is) and optimizing it to have very good performance even on standard hardware. The explained technique is a result of several month of research and has been applied to the Data Warehouse of an insurance company where we had to deal with two times the number said before. 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. If you've seen (and liked) Brent's one-hour session on this topic, you'll like the pre-con. He goes into more depth and detail, showing examples and live demos to prove points about memory, storage, and CPU. You'll leave ready to challenge your SAN and VMware admins. Read <b><a href="http://www.sqlpass.org/Community/PASSBlog/entryid/352/2011-PASS-Summit-Pre-Con-Preview-Brent-Ozar.aspx">Q&A blog</a></b> on this session.</p> Performance tuning is hard, everyone knows that. Attend this session and learn how to define, measure, and analyze performance issues as well as implementing changes and how to make sure those changes continue to have the desired effects. In short, I break down performance tuning into pieces that anyone can understand. Leave this session knowing what actions to take when you get back to work on Monday. Are you suffering from memory issues? Is your SQL Server configured for proper memory management? Attend this session and learn how to best determine if you are suffering from memory pressure, how to resolve issues that result from memory pressure, and how to properly configure your SQL Server to minimize the chance of having memory issues. Dates and times seem simple at first. Kendra Little will show you there's more to it than you think. She'll give you five best practices that will help you select the right temporal data type, avoid common issues, and use the most effective techniques to aggregate data. She'll also explain painful problems with query performance and how to avoid them. Choose wisely: the correct types and high performing data access logic will scale like magic. Microsoft rebuilt SQL Security from the ground up in SQL Server 2005, and since then they've evolved database security in each new version. Denali continues that trend with some interesting new features, such as new server-level role features, new hashing algorithms, and new permissions. It also adds contained databases, which let users connect to the database without authenticating at the server level. This is a great feature that solves problems when you move databases, but it sounds scary from a security perspective. During this session, we'll explore all of the new security features in Denali. At the end of the session you'll have everything you need to decide for yourself whether your databases will be adequately protected in Denali or if security is getting way too onerous in SQL Server, and whether these new features alone make Denali a compelling upgrade! In this session, two SQL Server MVPs, one specializing in business intelligence and one specializing in core engine, will explain how normal day to day BI workloads impact the database engine, from ETL and cube processing to queries and report server management. During this session, we'll discuss what happens to the OLTP database during ETL as well as what happens to the data warehouse as data is being loaded. We'll talk about what you can do to speed up data warehouse loads and, most importantly, why these changes speed up the data warehouse loads. Then we'll review how cube processing affects the data warehouse database and describe how you can tune the data warehouse database for optimal cube processing. Also during this session, we'll compare performance differences between queries running against the OLTP application, the data warehouse, and the OLAP cube. We'll also assess the impact on the database engine when the reporting services databases are housed on servers which are hosting other databases. An in-depth session examining "Waits" happening in your SQL Servers, using the well-known "Waits & Queues" methodology. With particular focus in the session on understanding and interpreting the information found the (DMV) sys.dm_os_wait_stats, Taking note of which “Waits “with the highest counts or excessive wait times, as well as those which “Waits” can be safely ignored, We can discover those "performance hurdles" and ways to overcome or remove them, restoring your SQL Servers performance levels. By using established methodologies like “Waits & Queues”, we can systematically identify and remove performance hurdles; to do this we must have a way to interpret this wait information, the session will incorporate demos based on real-world examples to illustrate how to find those "Waits" that cause the degrading of performance levels in your SQL Servers – can you afford not to know this information? Refactoring SQL is not like refactoring application code. This talk will cover proven SQL refactoring techniques that will help you identify where performance gains can be made, apply quick fixes, improve readability, and help you quickly locate places to make sweeping performance improvements. Jeremiah Peschka has years of hands on experience tuning SQL applications for performance, throughput, and concurrency. Failover Clustering, Database Mirroring, Backup Strategies, etc. These are just some of the terms that you hear when tasked to work on a disaster recovery project. However, you’re missing a lot on the non-technology aspects. This session will describe the principles that should drive disaster recovery strategies and how they need to become the foundation of the technology solutions that you need to implement. You will certainly look at your disaster recovery strategies differently after learning these concepts. Entity Framework 4 and before used a database-first style of development, wherein the database had to exist before you built the entity data model. But Microsoft has made available an update to EF 4 that supports code-first development, in which you create the model first using tools in Visual Studio 2010. With code-first development, you don't have to create entities in a designer or do any kind of XML mapping. Instead, you define model objects by writing regular .NET classes, including validation rules and a context class that handles retrieving and persisting data, and then create a database from your model. But how good is the resulting database? Are there any pitfalls to this style of development? Can a developer do real database development with code-first? During this session you'll learn about this powerful feature in Entity Framework and see how to put it to use, as well as explore the issues involved with its development. This session will provide a thorough overview of the certification and training offered by Microsoft in the area of SQL Server 2008 technologies. It is not a technical session but will provide the information that you require from those who have experienced the range of Microsoft certifications available in order to inform and help you with your forthcoming exams in •Microsoft Certified Technical Specialist •Microsoft Certified IT Professional •Microsoft Certified Master •Microsoft Certified Architect An exploration of the available learning channels to support you meeting your cetification requirements will also be explored including •Microsoft Official Courses – (Instructor Led Training) •Microsoft Elearning •Microsoft Press Books To many DBAs, the transaction log is a black box that just works and doesn’t need any attention. While this might be true on low activity SQL Server instances, for busy systems, the transaction log needs lots of focused attention if you want it to perform optimally and help provide high availability. In this session, you learn how the transaction log works from the inside out, and how to optimize it for the best performance in most any environment. This session is targeted towards more experienced DBAs who are interested in the internal workings of the transaction log, and how to apply that knowledge to the real world. If you were asked what average runtime was of your slowest stored procedure, could you provide the answer? What if you needed to provide that information for last month? Do you review your error logs each day? Do you know the growth percentages of your databases for the past year or even the past month? If your database has an unusual growth event will you know about it? In the utility database that we create during this session we will track stored procedure information along with information about the hard drives such as free space. We will look at how you can have the errors in your error logs emailed to you each day. The goal of this session is to help you automate and centralize as many of your day-to-day tasks as you can, freeing up your time and helping you provide those critical answers quicker. While in classical OLAP cubes the clear winner data modeling technique is dimensional modeling, it is interesting to understand what will happen with the new Vertipaq engine. In Vertipaq there is no real need to do dimensional modeling, because the engine is able to manage relational modeling too. In this session we will introduce the new modeling capabilities of Vertipaq, showing how the same scenarios can be modeled in both UDM and Vertipaq. This will lead to an interesting discussion about how to develop your data warehouse in order to make it working with both UDM and Vertipaq. The introduction of many to many dimension relationships in SSAS (since 2005) opened new scenarios that can be solved using OLAP cubes. Now, with the introduction of the Vertipaq engine and the DAX programming language, it seems that many-to-many relationships are no longer supported. As we are going to show, this is not true: many-to-many relationships can be leveraged in DAX too, even if this means some deep understanding of the Vertipaq engine and of the DAX programming language. During the session we will show many scenarios where many-to-many relationships can be managed by using DAX. The implementations are different from those used in SSAS, both from the data modeling and from the programming points of view. Gaining the ability to master many-to-many relationships will open new modeling scenarios that look very promising in SSAS. What Happened? Exploring the Plan Cache Being pro-active,and using one of the various tracing capabilities of SQL Server, is the best way to keep track of what is going on in your SQL Server and what might be causing performance problems. But does that mean if you haven't set up any tracing, there is nothing you can do to find out what problematic queries might be running? The answer is no. In this session, we'll explore SQL Server's plan cache, from the way it is managed in memory, to the tools and techniques for discovering what plans are in cache, how long they've been there, how often they've been run, and whether they contain any suspected sub-optimal operators, such as table scans and hash joins. Knowing what's happened is the first step in knowing what needs to be fixed. Have you inherited queries that are not your own, and are finding that performance isn’t so great? Removing cursors in favor of set-based queries is useful, but even set-based queries can perform poorly. Understanding the impact that various constructs can have on a query plan could be key to resolving many of these issues. In this seminar, irrepressible SQL MVP Rob Farley will take a look at some real-life queries and take the audience through examples of constructs that can have significant effects on tuning. This will include complex nested joins, join simplification, procedural functions, SARGability v residuality with predicates, better execution plan reading, start-up parameters, force hints, complex sorting, ORs, effective Dynamic SQL, GROUP BY v DISTINCT, unique indexes, temporary tables, APPLY considerations, and more. You'll discover profoundness in things you thought you knew, and you'll even see when a covering index that returns a single row can be a bad thing. This will be a day spent in Management Studio, not PowerPoint. If you want to know how to persuade the Query Optimizer to do a better job of running your query, this day is for you. The examples will apply to a variety of versions, with most of it being useful even in a SQL 2005 environment. Read <b><a href=" http://www.sqlpass.org/Community/PASSBlog/entryid/361/2011-PASS-Summit-Pre-Con-Preview-Rob-Farley.aspx">Q&A blog</a></b> on this session.</p> You wouldn't believe how often people just aren't using their indexes effectively, whether it be searching for data, or joining tables. Quite often, this comes down to predicates becoming residual. Yes, residuality is a problem, and once a predicate has become residualicious, you may as well be scanning instead of seeking. There’s so much more to SARGability than people think, and people can often miss out on significant performance benefits by not appreciating this – particularly with new and improved query hints becoming available in SQL 2008 R2 SP1 and Denali. SARGable means Search ARGument Able and relates to the ability to search through an index for a value. Unfortunately many database professionals don’t really understand it – especially in regard to joins – leading to queries which don’t run as well as they should. In this talk, you'll learn how to tell whether a predicate is being used correctly, and to evaluate what's really going on in your Seek or Join. You'll even learn to use new features in SQL 2008 R2 SP1 and Denali to affect the residuality of your predicates. This is a talk involving lots of demos, showing plenty of queries and execution plans. How your business applications interact with the corporate database directly can have a dramatic impact on how successful you are, and one of the biggest issues is performance with large databases. By designing the application to use asynchronous messaging for non-critical updates you can focus performance concerns to where the timeliness is critical. SQL Server Service Broker handles the asynchronous requests quickly and efficiently, and minimizes the conflicts with your critical updates. This session will walk you through the steps to get Service Broker up and running and providing a consistent delivery of your business data. The Policy-Based Management feature in SQL Server 2008 provides a great way to ensure your systems are configured consistently and correctly, but it can be tedious to implement on each server in your environment. PowerShell scripts allow you to automate the implementation of your policies so you can focus on more important problems. This session will walk you through how PBM works, how to define your policies in PowerShell, and how to set up Agent jobs to evaluate those policies regularly to let you know when you need to take action. In this session Mike shows you how to load a data Warehouse using SSIS and T-SQL. You will see the pros and cons of each method. You will see demos showing how to load Type 1 and Type 2 dimensions with both methods. Learn the different methods to load a fact table using both technologies. After this session you will be able to load a complete Data Warehouse using both methods. The key to understanding how SQL Server is processing your queries is the execution plan. This full day session focuses on the execution plan. We will start right at the beginning and talk about the compile process. We’ll also go over how, and more importantly, why, plans are stored in cache and how they are removed. We’ll spend time exploring the key differences between actual and estimated plans, and why those descriptions are more than a little misleading. We’ll also show you assorted methods to obtain a query's execution plan and what the differences and tradeoffs of each are. A full day class on execution plans would not be complete without spending time learning to reading them. You’ll learn where to find useful information in execution plans, what the common operators are and how to decipher the sometimes cryptic messages the plans are sending to you. We'll also debunk some myths surrounding query operators and execution plans. All of this is meant to further your understanding of how queries work in order to improve the queries you’re responsible for. With this in mind, we’ll show how you can use execution plans to tune queries. All of the information presented will be taken from real world examples. We’ll build on the information through the day so that at the end, after following us through multiple examples at your own computer, you’ll have a stronger understanding of how to read, interpret and actually use execution plans in your day-to-day job. Read <b><a href=" http://www.sqlpass.org/Community/PASSBlog/entryid/354/2011-Pass-Summit-Pre-Con-Preview-Gail-Shaw-and-Grant-Fritchey.aspx">Q&A blog</a></b> on this session.</p> As SQL Server professionals, we often think of memory in vague, instance-level terms: buffer pool, procedure cache, Virtual Address Space, and so on. But certain tasks require a more in-depth focus, and query tuning is one of them. Large, complex queries need memory in which to work--workspace memory--and understanding the how's, when's, and why's of this memory can help you create queries that run in seconds rather than minutes. This session will teach you how to guide the query processor to grant enough memory for top performance, while also keeping things balanced for the sake of concurrency. You will learn advanced monitoring techniques, expert-level application of specialized query hints, and the memory internals needed to put it all together. If you work with large queries and are serious about achieving scalability and consistently great performance, you owe it to yourself to attend this session. How did the development world conclude that an integer is the best primary key? What has been added to SQL Server in 2005 and 2008 to help change the way database design has evolved over the years? Can we still use a VarChar(xx) for a primary key? What is the difference between a lookup and Parent/Child relationship? What is an example of a Many-to-Many relationship? What is 4th and 5th normal form? This session will go through the history of 22 years of experience with various database designs – normalized and denormalized. The discussion will include the benefits and forward looking that should be required for using various design techniques. The flow will be a discussion with attendee participation to share success and pains in database development, leading to standards for all of us to take advantage of while designing databases. Are you looking to maximize the performance of your Analysis Services queries? This session explains what aggregations are and how they work. You’ll learn how to create aggregations, tune the aggregations based on actual usage, and design custom aggregations when necessary. You’ll discover the benefits and pitfalls of flexible aggregations, when to use rigid aggregations, and the meaning of lazy aggregation processing. You’ll dive into queries to see if aggregations are being used and learn when aggregations aren’t useful. If you thought aggregation design began and ended with the aggregation wizard, you owe it to yourself (and your cubes) to attend this session. Transactions are essential to maintaining data integrity. Getting transactions right means understanding both how they work and the business rules that govern your data. This session will explain the fundamentals of transactions and how they behave in SQL Server. It will delve into ACID properties, isolation levels and nested transactions. The session will also review how to structure transactions to satisfy business rules and ensure efficient performance. Bad execution plans are the bane of database performance everywhere they crop up. But what is a bad execution plan? How do you identify one in your system and, once identified how do you go about fixing it? In this top-rated session from the 24 Hours of PASS we’ll look at some things that make a plan ‘bad’, how you might detect such plans and various methods of fixing the problem, both immediately and long-term. This session was TOP 3 in its track last year and is always a crowd pleaser. I won a coveted Summit Spolight Session at SQL Rally this year by popular vote and it has been a hit at SQL Saturday's all over the east coast because it is 100% demo and driven with real world data the attendees can get and use themselves. In this session, the attendees will learn how to drive and build an Analysis Services cube and reports as they rotate through the podium as volunteers. This session will show the crowd how simple it is to begin delivering powerful analysis to your end user community without going through a significant development cycle. In this session, you’ll learn how to load a database, specifically a dimension table, using SQL Server Integration Services (SSIS) and PowerShell. Watch a battle royale between two database professionals loading data with the Denali version of their favorite tool. They’ll also show new features for managing SSIS packages in Denali’s new SSIS Catalog using both Management Studio and new PowerShell features. You decide who does so in the fastest, simplest, and most reliable way and who wins the ETL Smackdown. Making the right hardware selection decisions is extremely important for database scalability. Having properly sized and configured hardware can both increase application performance and reduce capital expenses dramatically. Unfortunately, there are so many different choices and options available when it comes to selecting hardware and storage subsystems, it is very easy to make bad choices based on outmoded conventional wisdom. This session will give you a framework for how to pick the right hardware and storage subsystem for your workload type. You will learn how to evaluate and compare key hardware components, such as processors, chipsets, and memory. You will also learn how to evaluate and compare different types of storage subsystems for different database workload types. This session will give you the knowledge you need to make sure you get the best performance and scalability possible from your hardware budget! In this demo-rich presentation, we'll walk through the creation of BI Dashboards using PerformancePoint Services and Analysis Services cubes. We’ll cover the process from end-to-end including creating a Business Intelligence site within SharePoint 2010, creating dashboards and dashboard components such as graphs, charts and KPI scorecards with Dashboard Designer, configuring data source security, creating drill-through actions and the power of decomposition trees. We’ll also cover various deployment scenarios including the security challenges of a per-user identity extranet deployment. Global operating websites and enterprises all face the same problem: Maintenance windows get smaller and smaller every year and the requirements for availability go up the same time. This session shows how to create a SQL Server infrastructure that will solve both problems at the same time. After a short explanation of the theory behind the concept you will learn how to create the infrastructure and most importantly how to store your data on it in a way that enables 100% availability and gives you 24 hours of maintenance every day. How is SQL Server really accessing the data and log files on the disk? What's done asynchronous and what's synchronous and what impact does that have on our queries and DML statements. Which impact have features like database mirroring and replication, what's different when SQL Server is starting up. Which I/O Subsystems (including SSD) are best for which type of data and what's the impact if you choose a different one. Which files can be placed on the same volume and which should not. In warfare, understanding your enemy is a supreme advantage. The same is true when it comes to protecting SQL Server. We’ll get into the mindset of an attacker, looking at the methods and techniques attackers use to go after SQL Server. Once we understanding the threats, we’ll then examine defensive techniques to secure and protect our SQL Servers, using both new and creative methods, as well as the tried and true. In this session, we’ll take a look at Windows operating system internals, including kernel architecture, threads and processes, memory usage, and I/O, and how they affect how SQL Server performs and acts. By having a good understanding of how the operating system works under the covers and how it interacts with the SQL OS, you will gain a deeper knowledge of what’s going on with your SQL Servers and where performance issues may be on your systems. Are you attending the PASS SUMMIT for the first time, consider yourself a shy networker or want to take better advantage of these conference benefits? • Meet and connect with the “movers and shakers” in the SQL community • Discuss industry trends and technical innovations with MVPs and other experts • Turn informal contacts into mutually beneficial professional relationships • Uncover career-boosting opportunities • Plus, make new friends and reconnect with old colleagues Revised with new networking exercises, tips, techniques and strategies, Don Gabor will guide you step-by-step through the networking process. You’ll learn how to confidently connect with other conference attendees, have more fun mingling and build profitable professional relationships that will pay off for years to come. Take-aways include how to: • Break the ice and remember names • Meet people in small and large groups • Keep the conversation going and change topics • End conversations and leave a positive and professional impression • Work the room and join other conversations • Use social media to follow up and build business relationships Each workshop attendee will receive an autographed copy of Don’s best-seller book, How to Start a Conversation and Make Friends. This is a special 2-hour networking workshop from 3:00PM - 5:00PM on Tuesday, October 11th, 2011. Please note that the registration cost for this session is $70.00. What’s going on in your physical data models and databases? Who actually decides what goes into the database design? How do you choose your primary keys? How do you implement them? Are GUIDs bad, good or "it depends"? Are your datatypes the right ones for the data? How can you measure the cost, benefits and risks of any design recommendation? Are there universally good design practices? Universally bad design practices? In this presentation we discuss five physical database design mistakes that cost you dearly: performance snags, development delays, bugs, and professional respect. Data professionals are often tasked to prepare physical data models, yet these skills usually overlap those of other team members and this overlap can lead to contention, confusion, and complacency. In this presentation, you’ll learn about the five blunders, how to find them as well as many tips on how to avoid them. You learn how to talk about and defend your design recommendations and how to ensure that you have the information to demonstrate they are the right designs for your project. Bring your armor, snark and humor. Blunders can be fun no matter how bad they are. Moving your data to the cloud can be challenging as SQL Azure environment and rules are somewhat different than your typical SQL Server. If you are thinking about migrating your database from any RDBMS (SQL Server, Oracle, MySql, etc.) to SQL Azure, then you've come to the right place. In this session, Nabeel will discuss the challenges you should be prepared for, and the techniques and tools you can use to move your schema and data to the cloud such as DAC, SSIS, and BCP, etc.. , plus what are the latest SQL Azure updates and features (Federations, Data Sync, etc.). How can you scale SQL Server? Scaling up is relatively easy (but can be expensive), while scaling out requires significant engineering time and effort. If you suggest hardware upgrades you may be accused of simply "throwing hardware at the problem", and if you try to scale out, you may be thwarted by a lack of development resources or 3rd party software restrictions. As your database server nears its load capacity, what can you do? This session gives you concrete, practical advice on how to deal with this situation. Starting with your present workload, configuration and hardware, we will explore how to find and alleviate bottlenecks, whether they are workload related, configuration related, or hardware related. Next, we will cover how you can decide whether you should scale up or scale out your data tier. Once that decision is made, you will learn how to scale up properly, with nearly zero down-time. If you decide to scale out, you will learn about practical, production-ready techniques such as vertical partitioning, horizontal partitioning, and data dependent routing. We will also cover how to use middle-tier caching and other application techniques to increase your overall scalability. 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 been 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). When queried, most database professionals would mention normalized as one of the most important characteristics that tell the difference between a good and bad database design. I won't disagree in the least, but there is so much more to be considered. Even if you did a great job of normalization, poor naming, poorly implemented keys, too many or too few indexes, and so on can derail your design. In this session I will present seven primary characteristics of a design that differentiates between an ugly design that will have your colleagues nitpicking you to death and one that will have them singing your praises. Characteristics such as comprehendible, documented, secure, well performing, and more (including normalized, naturally) will be discussed. It is no secret that a main deficiency of SSIS is deployment. Have you wanted to punch a wall before when trying to move a package from one server to another? You’re not alone. This session will be more than just SSIS deployment anger management. We’ll look at how to overcome the deployment challenges by considering configurations, package and connection security, and deployment approaches. And don’t miss the decision workflows that will help you down the right on choosing the right storage and deployment approaches depending on your situation! But there’s more… you’ll also get a preview of the SSIS server in the next version of SQL Server. Is the way in which SQL Server 2008 uses and manages the plan cache a mystery to you? Do you understand why this cache is so important and why you should not ignore it? If you have any doubts you should attend this session. We will break down the plan cache into manageable pieces and give you a thorough understanding of each and how they all fit together. Identifying the plans that are not reusing existing query plans and addressing them is essential for peak performance in SQL Server. We will also explore what options you have to minimize the impact to performance when you may not have 100% control over the way calls are made to the database. Wouldn’t you like to know how to identify what type of calls are being made to your SQL Server instance that are affecting the performance the most? Of course you would who wouldn’t? While some 3rd party tools certainly make the whole process of monitoring for performance much easier there are some metrics that SQL Server keeps on its own that can guide you in the right direction in this regard. We will cover each of these metrics and show you how you can quickly and easily decide which queries would best benefit from your precious time the most. Come to this session and get a ROI that is sure to make your day if you have any doubts what so ever on which queries are calling your name the most when it comes to performance. It's time to have fun... WITH SCIENCE! Since 1869, the periodic table of elements has been the standard for organizing and presenting many metrics for the substances from which life is based on. I embrace the table as a prime example on how to present information - a great deal of it - in a concise and deeply informative fashion. Taking the periodic table of elements into consideration I've created The Periodic Table of Dynamic Management Objects as a reference tool for these functions and views that have become so critical for today's SQL Server DBA to performance tune and gain metadata insights into their various SQL instances. In navigating the table we will examine key DMVs and DMFs of interest: requests, sessions, wait stats, indexes, system information will all be covered. We will also delve into how these DMOs - just like their elemental counterparts - can be combined to yield interesting insights into your servers. We will also look at how things can go awry as well! Like mixing vinegar and baking soda, sometimes combining DMOs can cause a big mess too! Alright… So you've got your data warehouse in tip top condition. Now it's time to deliver a few hundred reports to your customers. Oh, and not just build them… you also need to maintain and improve them over the course of their lifetime. This session will walk you through building robust report templates, managing and maintaining report metadata, and creating a full featured deployment system to make deployments a breeze. The most important part of your SQL Server is also the slowest, Storage. This talk will take you through the fundamentals of your server's Disk I/O System. This session should give you a solid foundation over storage systems and help you understand why they are slow and how to overcome some of their limitations. We will cover the basics of computer systems, hard disks and disk controllers. Next, we will move to the fundamentals of RAID and how to configure your arrays for performance and reliability. Later, we will discuss the relationship between SQL Server and the file system, what is required for SQL Server and how to configure the file system for optimal performance. Finally, we will touch on how to monitor the performance and health of your storage system with some real world scripts that you can apply to your own environment. Along the way we will touch on other storage mediums like SAN’s and Solid State Disks, covering some pitfalls, gotchas and personal stories from the field. There’s nothing quite as satisfying as writing a cool bit of T-SQL. Luckily for us, there are some truly awesome ways to make your data do just what you need it to do, all while improving performance and/or readability. Come learn three new-school ways to expand your T-SQL repertoire with: • Recursive Common Table Expressions (CTE) • EXCEPT and INTERSECT • CROSS APPLY and OUTER APPLY We will look at data models and development scenarios you might encounter that lend themselves to these techniques, see old-school ways to solve T-SQL challenges, and then compare alternate, new-school ways to get the same result set. Evaluating different approaches based on performance, readability, and maintainability, we’ll ask the important questions: Is it faster? Is it prettier? Can I hand it off to the new guy to maintain? The goal of this session is simple: Be able to impress your friends and confound your frenemies with your mad T-SQL skills. Does your organization have data policies that can't or aren't being enforced consistently? Well guess what? You can do all that and more with Policy-Based Management, a powerful and easy to configure feature added in SQL Server 2008! With a few simple clicks, you can check a setting on every server or database in your enterprise. Set up a policy that automatically enforces object-naming conventions. Create a policy to ensure your databases have been backed up within a specified period of time. In this demo-packed session you'll learn how to set these up in your own shops, setup an elegant (and free) reporting solution, and much, much more! Data mining is one of the key hidden gems inside of Analysis Services but has traditionally had a steep learning curve. In this session, you’ll learn how to create a data mining model to predict who is the best customer for you and learn how to use other algorithms to spend your marketing model wisely. You’ll also see how to use Time Series analysis for budget and forecast prediction. Finally, you’ll learn how to integrate data mining into your application through SSIS or custom coding. In this session, you'll learn from author and SQL Server MVP Brian Knight how to build a data warehouse for your company and support it with the Microsoft platform. We'll start with how to design and data model a data warehouse including the system preparation. Then, we'll quickly jump into loading the data warehouse with SSIS. After SSIS, you're ready to roll the data up and provide slice and dice reporting with SSAS. Brian walks through cube development and enrichment with things like key performance indicators, which are essential for your future dashboards. Lastly, Brian covers how to report against the data warehouse with SSRS, including a primer in how to write MDX queries against the SSAS cube. Make sure you bring your own optional laptop as you'll be able to follow Brian in the session as he designs, loads and reports against the data warehouse with you. Update session description: Read <b><a href=" http://www.sqlpass.org/Community/PASSBlog/entryid/353/2011-PASS-Summit-Pre-Con-Preview-Brian-and-Devin-Knight.aspx">Q&A blog</a></b> on this session.</p> To get the most of the presentation layer tools in the Microsoft BI stack when data sources include cubes, PowerPivot workbooks, and data mining models, you can't rely solely on your T-SQL skills. To produce high-performing reports and analytical tools, you'll need to use other query languages like MDX, DAX, or DMX. In this workshop, you'll learn which languages each presentation layer tool supports and the basic syntax used by each language. You'll also learn how take advantage of query builder tools to help you produce queries until you've developed the proficiency to create more complex queries on your own. In addition, you'll learn how to work with expressions to add calculations to cubes and PowerPivot workbooks, and how to use expressions to customize the appearance of reports, scorecards, and dashboards. Expand the value of your Business Intelligence infrastructure by delivering powerful forecasts and predictions using SQL Server Reporting Services visual capabilities. Using proven predictive models that forecast sales by store, region and product, future customer buying patterns, and customer demographics, you will see how to create appealing charts, graphs and maps that allow business users to include predictive analytics in their decision making process. In this session, Carlos Bossy will show you how to combine the visual features of SQL Server Reporting Services with the power of Data Mining and Analysis Services to take your organization’s BI competency to the next level. Elegant solutions deliver function and form. SSIS - in Denali and previous versions - supports elegant design. In this presentation, SQL Server MVP and author Andy Leonard demonstrates key components of an SSIS Framework to facilitate SSIS development that performs and is manageable in the modern enterprise. You can have it all - fast ETL processes, execution lineage, real-time reporting, and historical auditing - and Andy will show you how! In this day-long seminar, SQL Server MVPs Andy Leonard and Tim Mitchell - along with Microsoft SSIS Developer Matt Masson - demonstrate an SSIS development methodology for teams building enterprise-class solutions. Topics include: - SSIS Design Patterns - ETL Best Practices - Metadata-Driven SSIS Execution - Centralized Logging - SSIS Frameworks - Package Execution Monitoring - Predictive Analytics - Source Control - ETL Auditing - Deployment - Security The presentation team provides examples of SSIS Design Patterns from their upcoming book: SSIS Design Patterns; actionable demonstrations for building and maintaining SSIS packages in the enterprise; practical patterns that support ETL performance, instrumentation, and deployment; and pragmatic guidance for enterprise ETL Lifecycle Management. They share from their experiences as consultants, developers in the enterprise, and experience managing teams of SSIS developers for years. The information and advice of this team is applicable in organizations of any size. <p> Read <b><a href="http://www.sqlpass.org/Community/PASSBlog/entryid/347/2011-PASS-Summit-Pre-Con-Preview-Andy-Leonard.aspx">Q&A blog</a></b> on this session.</p> Are consultants better than people who work for ‘regular’ companies? Perhaps it is the other way around? No classification of IT worker is inherently smarter or better than another. However, I believe that many successful SQL Server professionals share similar characteristics that shape their careers and success. Of course they have interesting differences as well! This session explores 20 years of my ideas, learning, and research on this topic. The session also highlights the experience of top SQL Server professions who will share personal thoughts on what has made them indispensible. The panel includes leading professionals from the consulting and corporate side of life as well as a mix of BI/relational and technology/leadership roles. Brian's 20 year career in SQL Server and personal experience in working with and mentoring dozens of highly succesful SQL Server professionals around the world adds unique value to this panel discussion. Ready to take the next step in your career? Ready to build a plan that will get you there? This session will show you a process for building a comprehensive professional development plan - a road map to get to where you want to go! This session is designed to help you build a professional development plan using the same techniques that are used for building business plans. We'll talk about obtaining and managing resources, setting realistic goals, tracking progress, and what to do when you get off plan. We're going to look at more than pure technical learning. We'll discuss how and when to modify your plan to include networking, writing, social engagement, and managing and leadership skills - and talk about the most effective ways to do it. We'll touch lightly on the value of a mentor and ways to find a good mentor. We will also discuss changing how you look at your career, adopting the mindset of the consultant even if you prefer to retain your status as an employee. It's not an easy change, but it's a powerful one, and that is an important component of a professional development plan. At the end of this session you should be ready to spend a day building a plan that will help you move your career forward to a goal that you've selected. There are a number of important trace flags that should be implemented in most production environments. Yet many DBAs think that trace flags should only be used for testing purposes or to "show-off" at conferences. That they are not supported. Yet nothing could be further from the truth. A good example is trace flag 4199. (Yes, it's probably worth your while reading KB974006 to understand what trace flag 4199 does!). In this session, Victor Isakov (MCA, MCM, MCT, MVP) and Julie Koesmarno (MCITP) will present the important trace flags that all DBAs should know, what they do, when to implement them and how best to implement them in a production environment. This will enable you to maximize the performance and reliability of your SQL Server infrastructure. Cloud computing is being hailed by some as the future silver bullet to everyone’s data availability woes and as such SQL Azure is Microsoft’s cloud based data platform. However, the platform is much different from simply a SQL Server that sits in the cloud and needs to be approached in an intelligent manner. In this fast paced session, we will be going well beyond the simple “hello world” scenario to show you how to leverage the cloud architecture for data storage as well as educate you in how the cloud works differently than your local database instance. We’ll give you all of the powers and perils that exist in the architecture to ensure that your first flight into the cloud is a successful one. This session is going to be an eye opener for sure and will have many of you dreaming of your data sitting in the cloud. It's amazing how many myths and misconceptions have sprung up and persisted over the years about SQL Server - after 12 years helping people out on forums, newsgroups, and customer engagements, Paul's heard it all. Building on the success of the original DBA Mythbusters session from last year, Paul brings another 90 minutes of myth debunking of a whole new set of myths and misconceptions in this fast-paced session on how SQL Server operates and should be managed and maintained. Come and see how many YOU get right! The session will cover the main scenarios for using MDS and will focus on the major improvements and new functionality in Denali release, both for IW through improved user experience (Web UI and Excel) as well as data stewards through improved staging, increased scalability and simplified security. Objectives: o Identify the scenarios and usage for MDS. o Introduce the main functionality of MDS in Denali o Identify the scenarios and usage where MDS Excel Add-In provides the best data management experience. You can have the best hardware possible: SSDs, TBs of RAM and 100s of processors. (Don't we all wish we had such budgets.) But if you don't have the correct indexing strategy in place your database can still perform poorly. With the next version of SQL Server there are even more options available. Confused about what indexing strategy to use? Then this is the session for you! In this session Victor Isakov (MCA, MCM, MCT, MVP) will cover all of the indexing options that are available in SQL Server and when best to use them. Victor will cover "practical" internals that will help you understand how indexes work and why you need to maintain them. Finally he will cover what indexing strategies to use given the most important considerations: your users, their query patterns and your data. This should prove to be a most informative and practical session that will enable you to optimize your database performance. Execution plans are stored after execution in the plan cache. This metadata about how queries are executed can provide insight into how your SQL Server environment is functioning. By using XQuery to browse and search the plan cache you can find potential performance issues and opportunities to tune your queries. This information can be used to help reduce issues related to parallelism, shift queries from using scans to using seek operations, or discover exactly which queries are using what indexes. All of this and more is readily available through the plan cache. In this session we will explore the plan cache and start you on the road to discovery. There are many ways to performance monitor your SQL Server environment. In this session we’ll review Extended Events, which is one of the newer SQL Server monitoring platforms. Learn the ins and outs of how to get detailed information on the errors and events that occur within SQL Server and how to dig into the information. With a few T-SQL statements, issues that could take weeks to research can be investigated in minutes. Indexes make queries run faster, right? Not always, no. And finding the right indexes for your needs is often more of an Art than a Science. Attend this session as we take a visual tour of how indexes are laid out and the way data is accessed when indexes are utilized. We will discuss clustered indexes as well as non-clustered indexes and also when to use indexed views along with some other variations. Event Notifications are a powerful tool in the Database Administrators tool kit that are often overlooked and rarely used. This session will provide an overview of the Service Broker components used by Event Notifications and the difference between Event Notifications and other features of SQL Server like SQL Trace and DDL Triggers. It will look at the events that are available for use with Event Notifications and how to find information about the data returned by those events in the Books Online. The session demos will teach you how to leverage the functionality of Event Notifications to automate responses to events inside of SQL Server, and how to build a monitoring solution for problems like blocking and deadlocks. This session will provide you with some surprising and interesting insights into how dashboards and reports should be designed. Called Information Visualization, there is a whole line of thinking which maps how the brain interprets what the eyes see. We will translate this into best (and worst) practices related to visualization in dashboard, scorecard, table, and report design. Both funny and informative, you will probably learn, as I did, that you have been designing your dashboards/scorecards and reports badly. We are going to take a visualization test, and learn how to convey information in a clearer, better way. You will be surprised. Join me - this session is going to be hoot! A review of the hardware and software components and internals of the SQL Server Parallel Data Warehouse appliance. We will review the hardware layouts, the basic components of the system. We will examine the physical layout of data in a PDW appliance, including the differences between replicated and partitioned tables. We will then review the basics of parallel query execution, with an example showplan step-through for PDW. ColumnStore indexes, a new feature in Denali, is Microsoft’s first step into columnar based storage. There are predictions that the future will hold specialized all-columnar data stores for Data Warehouse and other specialty uses. Having tested in a large data warehouse environment, Wayne will show what he has learned in the process. We will learn how to use these new indexes in Denali, and more importantly – when to use them. We will create indexes and compare optimizer choices versus standard nonclustered indexes. We will also discuss the the pros and cons which come with this new index type. A good working knowledge of how SQL Server makes use of locking and transaction isolation levels can go a long way toward improving an application’s performance. In this session, we will explore SQL Server’s locking methodology and discover techniques for enhancing query response times. Delivered by an expert on the subject, this seminar will make you familiar with one of the most mysterious component of Microsoft SQL Server – its own internal built-in operating system, or SQLOS. SQLOS is little known and even less documented. It provides operating-system-like services to other components running within the engine and forms a foundation of SQL Server scalability, reliability and performance. As the scalability requirements increase and hardware becomes more and more powerful, SQLOS’ role grows more and more important – and so does our need to understand its internals. During the course of the day, we will cover all aspects of SQLOS – from the basics of its architecture, through thread scheduling, memory management and Resource Governor, to troubleshooting, Extended Events and advanced SQL Server configuration. Through a series of lectures and demonstrations, you will become familiar with all aspects of its operations and how it enables SQL Server to take advantage of modern, powerful hardware architectures with many CPUs and vast amounts of memory available. You will also learn how to diagnose and troubleshoot advanced performance issues on such servers, including tips and tricks that can make a big difference in performance of high-end servers under heavy load. In today’s consolidated environments you may not have time to fine-tune every query. But there are some low-effort, high return things you can do to make those environments more likely to perform “good enough”. The same settings also help fine-tune those tier one instances as well. We’ll discuss tempdb configuration, power saving settings, compression, VLFs, grow/shrink, patching strategies, and configuration settings such as ‘optimize for ad hoc workloads’ and MAXDOP. Scratching your head, you stare at the screen. Should you rebuild an index? Create a new one? Reboot the server? Why is this query so slow?!? Figuring out performance problems can sometimes feel like fumbling your way through a dark room. Maybe you’ll get lucky and find the right solution?or maybe you’ll stub your toe. Either way, it’s a slow, potentially painful process. Yet finding the root cause of most performance issues is a simple exercise, once you understand where to look and when. In this full day seminar, you will learn a proven methodology that can be used to approach virtually any performance problem. Created and refined by Adam Machanic over several years, this strategy leverages core SQL Server performance tools (including dynamic management views, Extended Events, and WMI counters), applying them to various performance troubleshooting techniques (such as waits and queues analysis, baselining, and real-time activity monitoring). Each of these tools and techniques has a unique role, and you will learn to use them cooperatively to quickly and adaptively find the actual cause of performance issues. All of this will be illustrated through complete demonstrations that will teach you how to drill from high-level problem detection all the way to specific spot in a query plan or deeper?pinpointing the exact problem and helping you to quickly solve it. Attend this seminar to take full control of your databases—and never again stumble blindly through the dimly lit world of performance troubleshooting. <p> Read <b><a href=" http://www.sqlpass.org/Community/PASSBlog/entryid/346/2011-PASS-Summit-Pre-Con-Preview-Adam-Machanic.aspx">Q&A blog</a></b> on this session.</p> Many attendees know the basics of dimensional modeling: facts, dimensions, and dimension attribute change management. But we never seem to find time to talk about the hard problems. This session addresses a thorny dimensional design challenge in a holistic manner: How do we handle multivalued dimension relationships, where a fact row corresponds to potentially many rows in the dimension table? The solution – as with most solutions in the DW/BI space – begins with the data model. But there are issues to be concerned about during ETL, SSAS, usability, and query performance as well. In this session we describe how to identify various types of multivalued dimension relationship problems; you'll learn about the standard design solutions, and the pluses and minuses of each approach. We'll show you some tricks (none perfect) for avoiding multivalued dimensions. You'll learn how to build the ETL to populate said dimensions, as well as the Analysis Services cube and associated calculations. Finally, learn what you'll to teach your users about the ad hoc use of the multivalued dimension. Execution plan reuse is one of the most important aspects of building well-performing and scalable SQL Server solutions. But it's also the one that is often overlooked during the design phase and becomes very difficult to troubleshoot later. In this session we will discuss in details the following aspects of SQL Server execution plans: compilation, re-compilation, parameterization, caching, reuse and aging. You will leave this session with full understanding of how to improve your server's performance by increasing execution plan reuse (and how to avoid reuse in case this is necessary). The SQL Server Query Optimizer is a cost-based optimizer: it analyzes a number of candidate execution plans for a given query, estimates the cost of each of these plans, and selects the plan with the lowest cost. In this session I will go into the internals of the Query Optimizer and will show you the steps that it performs in the background covering everything from the time a query is submitted to SQL Server until an execution plan is generated. I’ll show you how the Query Optimizer generates possible alternative execution plans, how these plans are stored for the duration of the optimization process, how heuristics are used to limit the number of alternative plans considered, how each candidate plan is also costed, and finally how the best alternative is chosen based on those costs. I will also cover why query optimization is an inherently complex problem and why challenges in some of its most fundamental areas are still being addressed today. Spend a couple of hours creating data flows in SSIS and you'll quickly figure out how things work - initialize a connection, connect to a source, and write to a destination. Add a few components such as lookups, conditional splits, and derived column transformations, and you've covered the most common functions of data movement in SSIS. Move beyond the basics, though, and you'll find a treasure chest of components that are used much less often, but are no less valuable for some of the off-the-beaten-path ETL needs you'll eventually encounter. In this session, we'll examine some of these less frequently used tools. We'll briefly tour and then demonstrate SSIS components such as the fuzzy lookup, fuzzy grouping, term extraction, term lookup, and the script component. Getting started with SSRS in SharePoint can be a daunting experience and just jumping in with both feet without thinking about a framework can cause some major headaches down the line. A properly designed set of frameworks and guidelines can create a reporting ecosystem that can easily support the needs of both enterprise and self service reporting and make report development more secure, efficient, maintainable and consistent. This session will cover topics such as security, user permissions, templates, shared data sets, shared data sources, report parts, content types, and other features of SSRS and SharePoint that can be utilized to build a reporting infrastructure environment. You will leave the session armed with real world information on how to start building your SSRS environment or how to reign in your existing environment. The Advanced Reporting Services Seminar will enable you to develop visually rich reports that will meet the needs of your business. Almost anything is possible with Reporting Services and in this seminar you will learn how to achieve those difficult tasks. The seminar is based on SQL 2008 but will, where possible, show you how to achieve the same in SQL 2005. We will also highlight what’s coming up in Denali. The scenarios that we will cover are: 1. Building an enterprise reporting suite 2. Re use of report components 3. Dashboard interaction 4. Report layout and Exporting reports 5. Document production Topics that will be covered are, 1. Handling Parameters, Multi select, defaulting, passing to queries, displaying on a report 2. How to produce formatted documents How to have rich formatted text (fonts, colors, text sizes) on the report, how to combine with data (mail merge), how to generate formatted data from SQL Server 3. Nesting of chart items to provide a rich experience. This will include the reference 4. The chart control in depth Handling multiple aligned charts, combining chart types, adding trend lines, handling common axes types. 5. Exporting data to PDF and excel Common pitfalls and ways to get round them. How to get the correct layout. 6. How to template your report How to control formatting from outside of the report, customising logos etc After this seminar you will be able to achieve almost anything your users demand of you and your reports in an efficient manner. Read <b><a href="http://www.sqlpass.org/Community/PASSBlog/entryid/356/2011-PASS-Summit-Pre-Con-Preview-Simon-Sabin.aspx">Q&A blog</a></b> on this session.</p> The future of BI is self-service reporting and PowerPivot is the conduit to that goal. We will explore how to use the PowerPivot add-in for Excel 2010 to tie different data sources together and deliver analytics. In this session you will learn how to use PowerPivot (within the familiar interface of Excel) to combine different data sources such as Analysis Services cubes, TSQL queries, Stored Procedures, Excel spreadsheets, flat files, Microsoft Access databases and data from the web to create basic PivotTables and PivotCharts in Excel. The data these new spreadsheets overcomes the 1,048,576 row limitation in Excel 2007/2010, contains refreshable data sources and has excellent data compression and lighting fast speed thanks to the utilization of the VertiPaq engine. Together we will walk thorough how to use PowerPivot to import data from different sources into an Excel spreadsheet, create pivot tables, pivot charts, "slicers" and drill down in to relationships between the data sources. Please come learn how you and end users at your organization can use PowerPivot to do data exploration and create first class self services business intelligence! Ever tried deleting 100 million records. If you have you will know your transaction log will likely blow up, you will block all access to the table and it will generally be painful. In this session we will look at some of the techniques you can use to make this easier. We will look at how •deletes and updates work and how indexes affect them •how to break the job into smaller chunks •how to run those chunks in parallel After this session you will understand more about why these operations are painful, what you can do to make them easier to do and reduc the impact on your end users. Parameter sniffing is a good thing: it is used by the Query Optimizer to produce an execution plan tailored to the current parameters of a query. However, due to the way that the plan cache stores these plans in memory, sometimes can also be a performance problem. This session will show you how parameter sniffing works and in which cases could be a problem. How to diagnose and troubleshoot parameter sniffing problems and their solutions will be discussed as well. The session will also include details on how the Query Optimizer uses the histogram and density components of the statistics object and some other advanced topics. Data Visualisation is an essential tool for Business Intelligence specialists. It reveals patterns in the data, which businesses can use as intelligence to perform better. Edward de Bono once commented that “Creativity involves breaking out of established patterns in order to look at things in a different way” – but business users need to see the patterns in order to be creative problem solvers! I will discuss the perceptual and cognitive processes involved in finding patterns in the data. I will use SQL Server Reporting Services 2008 R2 to demonstrate some of the less well-known data visualisations, such heatmaps, bullet charts, and other visualisations based on Tufte's "small multiples" such as Sparklines and Sploms. The naked truth about engaging your audience in a lively and entertaining presentation where both speaker and attendees have fun! Preparing for your presentation doesn’t start with PowerPoint, but it ends there. Learn how to tell your story with passion and bring your audience on the journey. This session covers the process of building your story, picking the right content, and designing your presentation with simplicity and focus in mind. Note that neither the presenter nor the attendees will be naked for this great how-to session! SQL Server provides a few key features that allow VLDBs to survive—and keep processing—after isolated disasters: table and index partitioning, partial database availability, and online piecemeal restore. In this demo-packed session, Kimberly will show you some of the key things to know when architecting your solution as well as recovering from disaster. If your databases have varying access patterns and you’re required to keep more and more data online and available—this session is a must. It's becoming all too common these days for data to just "exist", without know who's truly responsible for updating it, maintaining it and validating it. It's falling on the DB Professional to act as the owner, the steward and the governor of data. That won't work. Business areas need to retain ownership. The concep of "stewardship" seems to have died completely, and "governance" just falls under the umbrella of master data management. This session attempts to split out what the business and technical roles are when it comes to data, and how to encourage your entire business organization to participate in the responsibility of data. XML is a technology that Developers and DBAs alike share a love-hate relationship with; it's powerful and flexible yet it can be bloated and difficult to work with at the same time. Regardless, XML has found a home in many applications - including SQL Server (e.g. execution plans) - so it's important to understand how to work with it in your databases. This session will show you how to use the XML data type, perform basic operations on XML data, and how to use FOR XML to create XML from relational data. This isn't a session to convince you to use XML, but to show you what you can do with XML when you do need to use it! There are a lot of options when it comes to disaster recovery and high availability with regards to SQL Server. Most business owners (and many DBA's) lump disaster recovery (DR) & high availability (HA) together and while they do share some pieces, they call for different strategies. Do you pick one or more than one of these options, and what criteria do you use to make the choice? It’s incredibly important that you understand the decision tree that helps you pick the right options(s) for your business and that you can explain the choices clearly to the stakeholders. It’s not as complicated as it sounds, but it is complicated. This presentation will give you a high level understanding of the options, the costs, complexities, & reasons for using each of them. Like many reporting tools (ok, all), there are aspects to Reporting Services design that you love, and other things that make you want to throw it out the window. Don't despair! There are tricks around the more frustrating items (pagination anyone?) and lots of features that you've never considered using. This session will go into some of the most common frustrations faced with report development and some of the more useful but rarely used features that SSRS provides. 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. Think you’re too busy to learn PowerShell? Even a little PowerShell will free up time to tackle many of your DBA, developer and BI tasks. This session covers the “How” and “Why” Data Professionals world-wide are turning to PowerShell to manage their SQL Server environments. You’ll learn to complete common tasks like: checking database space, last backup date, table sizes and server/database configuration settings. We will explore deploying new users, groups, tables and columns. We’ll build on these fundamentals to tackle multi-server tasks we can automate with SQL Agent. We’ll include reports to monitor your environment. We’ll dive into advanced topics like gathering Performance Counters, detecting worst performing queries, scripting out and comparing databases, importing data from websites, atom feeds, and spreadsheets; all with a very small amount of code. We’ll also cover parameter binding, splatting, remote jobs, remote installs, and SQLPSX in depth to maximize functionality and keep you productive. We’ll wrap things up with some language nuances that will help you avoid getting bogged down. This is a hands-on workshop; bring a laptop if you can. No previous PowerShell experience is required for this session. The afternoon will be spent touring the many capabilities and scripts available throughout the community. The intent of this session is to take you from PowerShell Zero to PowerShell Hero, joining the other champions of PowerShell in the SQL Server community! Key Performance Indicators (KPIs) are important measurements that influence behavior and allow an organization to evaluate whether they are meeting their target for specific objectives. This session will commence by introducing how KPIs are defined in Analysis Services, in both the Unified Dimensional Model (UDM) and the upcoming release of PowerPivot, and then made available for consistent reporting using Excel, Excel Services, Reporting Services and PerformancePoint Services. Next, consideration will be given to where the KPI sources its target data from. The session will explore additional capabilities of the UDM to collect planning data, specifically by using cube write-back, and Data Mining to seed planning values. Finally, the session will introduce how the Excel 2010 What-If Analysis feature can be used to support the collection of planning data from users. One of the pillars of SQL Server 2008 R2 is Managed Self-Service BI. The SQL Server PowerPivot for Excel add-in is a key offering in this pillar, and delivers an entirely new analytic experience to Excel 2010. The add-in allows analysts to load and prepare large volumes of data from various sources to create a multidimensional model. The model can be enriched with sophisticated calculations. Then the model can then be used as the source for PivotTable and PivotChart reports. And yet that’s not the end of the story. With the SQL Server PowerPivot for SharePoint add-in, the Excel workbooks that host the PowerPivot model can be catalogued in SharePoint and exposed as a data source for other Excel and Reporting Services reports. These SharePoint hosted models can then be managed by IT with scheduled data refreshes from the originating data stores. In this session, you will be introduced to the two add-ins. An end-to-end series of theory, demonstrations and discussion will describe how you can effectively leverage the add-ins with your own data and analysis requirements. If you are in the healthcare industry, using these acronyms is second nature; however, adding a data warehouse to the mix may take you out of your comfort zone. Not to worry, this session will take that healthcare knowledge and turn it into a lean, mean, fact-finding machine. Learn how to create a payer data warehouse model, created from experience with numerous insurer organizations. If you are not in the healthcare industry, learn about data warehouse modeling best practices, and maybe you will even understand your insurance company better! Have you ever received that dreaded call from the CEO? The one where they ask what happened to their morning reports? If so, you've been in an "Oh, Poop!" situation. Using the Report Manager, Reporting Services provides a series of ways to track, log, and alert you in the event that something goes wrong. You also have the ability to discover important report execution information by using the underlying database and data. Learn the series of steps you should take to prevent being in that type of situation again. SQL Server Denali introduces a number of exciting new features that will change the way we deploy and manage SSIS packages. In this session, we will explore these new features, understand how to take advantage of them and formulate some best practices. Finally, we will explore an existing SSIS package that embodies current management best practices and framework and see how to upgrade it to SSIS in Denali to take advantage of the new features. ADO.NET is a set .NET Framework libraries responsible for database access. It’s composed by two (2) assemblies (System.Data.Dll and System.Data.DataSetExtensions.Dll) and 4 components: • SqlClient: provides native access to SQL Server, SQL Server Express Local Database Runtime and SQL Azure; • Managed ODBC: provides ODBC support for .NET applications; • Managed OLEDB: provides OLEDB support for .NET applications; • DataSet: provides an offline cache for local data manipulation and control binding. In .NET4, only one feature was added: • BigInteger support in DataSet: BigInteger is a new type introduced by the BCL team in .NET4 And one breaking change was introduced: • Implicit transactions: before .NET4, rollbacks used to happen when the connection was closed. After .NET4, rollbacks happen when the connection is disposed. Also, it’s important to note that OracleClient was deprecated. Currently, the plan of records is to remove it from .NET Framework 5. In .NET4.5, the following feature will be added: • AlwaysOn support in SqlClient; • Support for the new spatial types in Denali; • Connection pool resiliency; • Support for NBCROW/Sparse Columns; • SQL Express Local Database Runtime support in SqlClient; • Async.NET support in SqlClient; • Password stored in memory as a SecureString; • Extended Protection for NTLM authentication in SqlClient; • SSL Certificate Validation in SqlClient; • Correlated Tracing ID support in SqlClient; • ARM Processor support in ADO.NET. The purpose of this session is to describe these features. By now you all know about the major SSIS improvements in SQL Server code name "Denali" – the enhanced Developer Experience, the Project & Parameter model, and the new Server based deployment features. This session will cover many of the other major improvements to the product that are sure to bring a smile to your face. With hundreds of customer reported Connect issues fixed, the upcoming release will be the highest quality release to date. Come find out more! SQL Azure Federations provide best of breed scalability for large database workloads in the cloud. Applications can break through single database limits in SQL Azure, improve their support for multi-tenant db tiers and gain great elasticity and price-performance through the use of federations. To take advantage of federations, application need to design for scalibility through decentralization of applications workload and partitioning their data. This talk covers the considerations for moving to federations technology and types of changes needed to move application and data to federations for new and existing applications. Diagnosing production issues affecting Microsoft SQL Server can be difficult and time consuming. Determining whether the problem lies in the client or the server adds even more difficulty. With SQL Server code name “Denali”, diagnostic capabilities have been enhanced with Extended Events and integration with Data Access Trace. These enhancements provide the capability to trace requests from clients to the lowest levels of SQL Server, find resource contention and determine what is really slowing down your queries. This talk will focus on using the diagnostic capabilities of Extended Events, which provides visibility into the internal operations of SQL Server and how these capabilities can be combined with client traces to pinpoint the source of production issues. It seems like everyone wants to talk about how to administer OLTP SQL Servers, and all BI discussions are limited to development and architecture topics. As a result, BI Administration experts are hard to find or almost nonexistent. Did you know that SSAS has DMVs? They do!! Did you know that you can run a SQL Trace on SSAS using an XMLA script? You can!! Did you know that you can backup, detach, restore, and attach SSAS databases and cubes? You can!!! This session will be an introduction to administrative practices for BI systems. Topics covered include: 1. Diagnosing SSAS problems with dynamic management views 2. Investigating SSAS issues using SQL Trace 3. Maintaining SSAS cubes and databases 4. Best practices for configuring SSAS and SSIS for maximum performance 5. Providing High Availability for SSAS and SSIS Do you have Oracle, MySQL, Sybase or Access databases that you like to migrate to SQL Server? Do you know that Microsoft offers a free tool to automate database migration? In this demo heavy session, we examine how SQL Server Migration Assistant (SSMA) migrates non-SQL Server databases to SQL Server and SQL Azure. Discover how the tool assesses complexity of the database to be migrated. Explore how the tool converts data types and sql statements not natively supported in SQL Server. See how SSMA migrates the databases directly to SQL Azure. Come to this session to learn how SSMA can save time and reduce risk of migration to SQL Server. The purpose of this session is to highlight issues, best practices, and lessons learned from a select group of SQL Server's Enterprise customers. For this session, we will cover Yahoo! and their 14TB cube and [name kept] and their complex analytics environment. The issues covered include scaling out, distinct count, design, IOPs, and other Big Data scenarios including Hadoop. A component of the Dow Jones Industrial Average, Travelers has more than 30,000 employees and generated revenues of approximately $25 billion in 2010. We have successfully consolidated more than 3000 instances of SQL Server using multi-instances and virtualization. In this session we cover the motivation for consolidation, technologies trade-offs and selection, challenges encountered during consolidation, critical success factors, and lessons learned during the process. Please attend this session if you want to better understand the real world challenges and the strategies we used to navigate through them. We will cover these topics from not only from the technology but also from people and process perspective. Since the beginning in 1937, the Progressive Group of Insurance Companies has been an innovator and we are now one of the largest U.S. private passenger auto insurance groups, with annual premiums over $14 billion. We have successfully migrated a mission critical Tier-1 Auto Policy Management system from mainframe to the SQL Server platform. The application requirements included: optimum performance, high HA DR expectations, and interface with more than 100 external systems; this application being the hub for auto business. In this session we cover the motivation for migration, technologies evaluation, anticipated and actual challenges encountered during migration to SQL Server, critical success factors, and lessons learned. Aside from interfacing with more than 100 external applications (both Windows and mainframe-based) it is designed to serve customers across the US where each state has its unique insurance regulations. Please attend this session to better understand the real world challenges encountered when we deployed this Tier-1 application and the strategies we used to minimize risks. Perhaps one of the most frustrating, least understood, and contentious areas (no pun intended) of the SQL Server Engine is tempdb. In this half-day session, we will drive into the internals of the tempdb database. I will warn you up front. This is a 500 level session and will be a very deep internal discussion. It will be very fast-paced with much information to consume. As with past presentations I’ve done I will be using tools like the Windows Debugger, DMVs, DBCC commands, and possibly even hex editors. I will use as aid in presentation details of memory structures, classes, and bitmaps, Even with the depth of this presentation, I believe anyone will walk away with many practical pieces of knowledge and advice that can help you avoid problems and maximize your usage of tempdb. This presentation will cover (but not limited to) the following topics: • Differences between tempdb and other databases • Internals of tempdb allocation • Internals of temporary objects (temp tables, table variables, worktables, ….) • Caching of temporary objects • Internals of tempdb logging • Tempdb latch and lock contention • Pushing the limits of tempdb • Monitoring usage and behavior of tempdb • Troubleshooting the common and advanced tempdb problems While this presentation include slides and information about tempdb it will also be mixed with a rich set of demonstrations to see the behavior (or misbehavior) of tempdb with SQL Server applications. A new class of workload is rapidly emerging on the cloud - massively scalable analytics engines providing real-time insight into data streams produced by hundreds of thousands of connected devices and applications:   · Media.  Delivering streaming video to hundreds of thousands of concurrent users is challenging.  Understanding the quality of the end user experience, their activity and behavior is critical in delivering a compelling, targeted service. · Mobile.  Beyond the challenges of delivering a mobile experience to a broad user base, how do you instrument, acquire and analyze information about user activity and behavior?  · Automotive.  How do we tie the flood of vehicle telemetry into our cloud and provide actionable, targeted and personalized analytics?  How can both vehicle manufacturers and owners derive value from having deeper insight into vehicle behavior, performance and maintenance? · Utilities.  Advances in infrastructure such as Smart Grids are driving an explosion in widely dispersed telemetry.  How can our ISVs develop next generation systems to provide real-time analytics into millions of electrical meters and sensors? This session will cover architectural patterns and lessons learned through developing real-world solutions on Azure for the web telemetry and utilities verticals. It will address specific approaches, code techniques, performance baselines and analytics patterns used and discuss best practices. In this session we'll provide an overview of SQL Azure, Microsoft's cloud relational database service. We will share the latest on the capabilities of the SQL Azure services for developer productivity across a wide range of application scenarios, including Web applications, Business Intelligence, and as a backing data store for mobile applications. We will also discuss hybrid cloud scenarios, such as synchronizing SQL Azure with on-premises databases, and utilizing on-premises SQL Server databases in conjunction with Windows Azure-deployed application logic. How do the largest SQL Server projects in the world scale? This session shows the architecture of 10 large projects, divided by OLTP and Data Warehouse. Presenting the architecture behind the largest Azure (both Windows and SQL Azure) projects in the world. This session covers real-life customer scenarios on how they achieve high availability and disaster recovery (regional and distant) today and how they plan to leverage SQL Server Denali AlwaysOn for their deployments. The lessons learned, best practices, operational procedures. The content is not merely coverage of available functions and features of Microsoft SQL Server, but based on various customers' practical real-life production deployment scenarios for mission critical Tier-1 systems. This session will describe how to build practical end-to-end HA and DR solutions for mission critical applications using SQL Server code name "Denali" AlwaysOn technologies, for example, how to combine Failover Cluster Instances with Availability Groups to provide a complete HA and DR solution, how to use Availability Groups Multiple Secondaries to replace existing database mirroring + log shipping solutions. We will share learnings from early customer engagements, and discuss some practical customer examples on why they picked a particular solution. We will also discuss upgrade from existing solutions to new Denali solutions with minimal downtime. In this session we will try to provide the “full story” about I/O. We start at the hardware level & work our way up the stack through Windows and into the SQL Server storage engine (including Analysis Services). We aim to provide detailed information about all aspects related to high performance storage design for SQL Server and best practices on this complex subject. This session incorporates the latest information on cutting edge technologies & new learnings from real world customer deployments from our continued efforts to learn more about the ever changing storage landscape. Are you familiar with the basics of PowerShell? Excellent! This session will build on your basic knowledge of PowerShell and show you how to be more productive through the use of profiles, modules, running against multiple servers, and leveraging Excel for reporting. You will learn techniques you can immediately apply to your day-to-day and impress your fellow DBAs. Have you struggled to configure SharePoint 2010 security to backend data sources with Excel Services, Reporting Services, PowerPivot and PerformancePoint in complex topologies? Would you like to discuss security Kerberos and non-Kerberos security options for these scenarios? Would you like to know what changes are coming in this area with SQL Server code name "Denali" to reduce the reliance on Kerberos for connections to Analysis Services data sources, especially with respect to crossing domain boundaries? This session will discuss a variety of complex topologies and security solutions with both SQL Server 2008R2 and with Denali. This session will also demonstrate how to use PowerShell scripts to configure the Microsoft BI stack, with and without Kerberos. You may have seen a demo showing how much faster queries can run when using a columnstore index. But how do columnstore indexes make queries run so fast? And what does batch mode processing have to do with columnstore indexes? What queries benefit and what queries do not? If you want to know more about how the technology works, and how to use columnstore indexes to make your data warehouse queries fly, this session is for you. We’ll talk about the technology, how to use it, and emerging best practices from early customer experiences on real world data. If you are interested in or currently developing non-Windows application for SQL Server and Azure, then find out how the Microsoft multi-platform strategy on SQL Server can help you utilize the features of SQL Server. During this session, hear about the enterprise-readiness and multi-platform connectivity components that will enable you to harness the power of SQL Server and Azure. We dive deep into features planned for our Java, native and PHP developers for future release of SQL Server and SQL Azure. This session is a great opportunity for you to provide feedback on our long-term roadmap. Come learn about Microsoft's invesment in Cloud BI - SQL Azure Reporting is ready for production implementations, learn how to deploy reports in the cloud, how to consume cloud data from SQL Azure via on prem tools and how to use Windows Azure Datamarket to your business advantage. The SQL Server Team has been doing the popular SQL Server Engine Unplugged sessions for the last several years. We will spend time on bringing you up to speed on the latest new developments in SQL Server code name "Denali" as well as leave plenty of time for great dialog and Q&A. We will cover topics such as SQLOS and Storage Engine, Access Methods, AlwaysOn (HADR), Performance, Query Optimization and Execution, Apollo, Beyond Relational, Manageability, SQL AzureAs with tradition we will have a lot of fun doing it! Microsoft StreamInsight 1.2 includes exciting new capabilities for both developers and administrators. The ability to protect StreamInsight applications against outages has been a common request, and version 1.2 delivers this with its new resiliency functionality. New capabilities for user-defined stream operators make it easy to include statistical modeling and time series algorithms into event-driven applications. Numerous enhancements to StreamInsight LINQ—including support for nested types—enhance the development experience. And administrators will benefit from new monitoring capabilities like performance counters and administrative logs. Join us to learn about the new functionality and how it can benefit new or existing StreamInsight applications. In this session, we will explain the new features and walk you through various demos that illustrate their use. In the SQL Server code-named “Denali” release, the SQL Server Developer Tools, code-named "Juneau", provide a brand-new developer experience for database developers by bringing a familiar Microsoft Visual Studio development paradigms to bear; debug, refactor and maintain your database declaratively. This session introduces you to the new tools and how you can leverage them to build and maintain you SQL Server and SQL Azure database, standalone and inside a team environment. Here is your chance to come and see the SQL Connectivity leadership team unplugged. No holds barred open discussion on all things relevant for application development on SQL Server and SQL Azure. Come and hear about our current investments and our thinking on the future direction to help support application developers on SQL. Security continues to be a top concern for database administrators as they face the ever-present threat of data breaches and the increasing pressures from compliance regulations such as PCI and HIPAA. Fortunately, SQL Server 2008 delivered some key features, such as TDE and SQL Server Audit, to help administrators achieve their security goals and Denali makes further progress with enhancements to Audit and security manageability. This session takes an experienced users approach to the advanced topic of recent security enhancements to SQL Server and explains the new changes coming to Denali and how they can help you satisfy your security objectives. Come and learn about what is new and cool in T-SQL in SQL Server code name "Denali" and SQL Azure. There are several interesting additions to T-SQL, this session will focus on what they are, why they were added to the product and how you can benefit from using them. In this session we will talk about the new high availability solution that is being introduced in SQL Server code name " Denali". The session provides an overview of AlwaysOn and explains how you can build a high availability solution using the new features and capabilities. In this session you will learn about how to deploy an AlwaysOn solution, how to utilize your secondary hardware for better return of investment and how the new solution simplifies deployment, management of high availability in your environment. The session will also describe how to manage application connectivity of your primary OLTP applications and reporting applications in an AlwaysOn environment. In this session you will learn about the internals of Availability Groups and other capabilities of the new AlwaysOn high availability solution in SQL Server code name “Denali". The session will also focus on the best practices associated with deploying a high availability solution using AlwaysOn. This session uses demos to dive into the details of the solution and you will learn everything you need to know to implement a high availability solution using AlwaysOn In SQL Server code name "Denali", the new AlwaysOn solution introduces Active Secondaries a new capability which enables secondary hardware in a high availability configuration to be utilized. This radically improves cost efficiency in your IT environment and helps improve performance of applications running on the primary instances. In this session we talk in depth about the new capability and the benefits of Active Secondaries. Learn about how applications can utilize the Active Secondaries for offloading your reporting workloads to the secondary and achieve close to real time reporting. The session will also explain how Active Secondaries can be used to offload backup jobs and dives into the internals of the implementation. Learn how SQL Server fits into your company's strategy for virtualization and private cloud. In part 1 we will cover the key drivers and technologies for SQL Server in private cloud, SQL Server features that are most relevant for virtualization and private cloud deployment. New features in SQL Server code name "Denali" which help you leverage your investment in SQL Server and private cloud. This is part2 of the optimization SQL Server for Private Cloud. We will talk about best practices and lessons learned around performance, dynamic memory, AlwaysOn and manageability of SQL Server in a Windows private cloud environment. We will touch on relevant features, running SQL Server on VMWare and the early lessons learned of database consolidation appliance. Are you game to take on the Microsoft Certified Masters? Dive deep into SQL Server for some of our MCM panel’s favorite advanced SQL topics. Our distinguished panel of MCM “classmates” will demonstrate the answers to some of the more interesting and puzzling SQL Server subjects to advance your SQL Server expertise! Pit your game against their game in the ultimate challenge of SQL Server intellect! The next version of SQL Server – code name "Denali" – comes with a number of new features and enhancements that will make database developers quite happy. This session focuses on exploring those new and exciting features from the point of view of a database developer and DBA. This session will showcase developing, deploying, and managing databases using the data-tier application (DAC). The DAC was introduced in SQL Server 2008R2 and Visual Studio 2010. This session will show the latest features of the DAC and how to use it against SQL Server and SQL Azure. In SQL Server 2008 R2, we introduced PowerPivot which is a self-service BI experience that enables information workers to build, share and collaborate on their personal and team BI applications within the familiar environment of Excel and SharePoint. In SQL Server code name “Denali”, we’re bringing some of the breakthrough technologies in PowerPivot into Analysis Services to support Corporate BI applications. At the same time, we’re also bringing some of the more sophisticated BI capabilities in Analysis Services and making them available to information workers in PowerPivot v2. The goal of this session to bring you up to speed on the new developments in Analysis Services and PowerPivot for the SQL Server code name "Denali" release. A Deep dive into the new tabular data model in SQL Server code name "Denali" covering the new features and changes in PowerPivot for Excel 2010, including the new measure grid, hierarchies and KPIs, as well as new DAX expressions to name a few. Use the new features in SQL Server code name "Denali" to develop and manage a BI Semantic Model in Analysis Services. We will discuss the new capabilities in creating your BI Semantic Models, using PowerPivot as a starting point, and how to troubleshoot your models. A deep tour of the PowerPivot v2 with SharePoint that is coming in SQL Server code name "Denali" including architecture, capacity planning, deployment and management. In this session we will present what is new in Data Analysis Expressions (DAX) coming with the SQL Server code name "Denali" release. We will show measures and calculated columns and DAX as a Query language using many demo’s. In this session we'll cover the new BI Semantic model capabilities and how you can create the right models for users to create their own insights using Project Crescent, the new Self-Service reporting technology as part of SQL Server code name "Denali". Today’s enterprise depends on rapidly growing volumes of quality data for critical business insight. Manage growing data volumes, integrate with Microsoft business intelligence (BI) products, and scale a warehouse to perform across hundreds of terabytes of data with Microsoft SQL Server 2008 R2. In this session you will learn all about Microsoft Data Warehousing platforms, Fast Track Data Warehouse, SQL Server 2008 R2 Parallel Data Warehouse. You’ll learn about the key features and product positioning, and identify query performance using Massive Parallel Processing with Parallel Data Warehouse. Your database environment is still running on SQL Server 2000 or SQL Server 2005 and you are considering upgrading? This session will show you all the caveats, tricks and tips and guidance on how to perform successful database upgrades to SQL Server 2008 R2, and SQL Server code name “Denali”. By the end of this session you’ll understand the key steps involved in the upgrade process. You will learn how to assess upgrade issues, and find resolutions to accomplish a successful upgrade. Learn how SQL Server code name “Denali” is reshaping FILESTREAM functionality to enable non-transactional access to Win32 Applications and while at the same time allowing you to manipulate it equally well through SQL relational algebra. Coupled together with another exciting new feature Statistical Semantic Search that allows you to extract statistically relevant phrases as potential tag recommendations, or find potentially similar content based upon these phrases, SQL Server code name “Denali” now provides unique advances in how unstructured data can be managed and mined using database technologies. Session provides feature demonstrations as well as technical drill down on how these technologies are built into SQL Server engine and how developers can take advantage on them in their applications. Since SQL Server 2008, Spatial Data processing has become a built-in capability of SQL Server’s programming support. In order to achieve the required performance and scalability in spatial database applications, we need to define spatial indices. This presentation describes how SQL Server 2008 spatial indexes work and gives tips and presents the tools on how to analyze and improve your spatial application's performance even when it needs to scale to large amounts of spatial data. We will also give an outlook on some of the improvements in spatial indexing in SQL Server Denali and even provide a demo. Have you ever written an XQuery against the XML data type and were having performance issues? Have you ever wondered how to improve your performance of your XML-based application? In this presentation, I analyse commonly encountered query patterns and performance issues and provide best practices and improved queries that can improve performance up to an order of magnitude. I will provide guidance on how to best store XML inside the database (relational vs as XML datatype, with or without a schema, which XML Index should I use?) and provides performance tips when using XQueries and FOR XML queries. We will dive into how Kerberos works and what types of scenarios you will see in your environment when working with the SQL Stack. This will include the SQL Engine, Reporting Services and Analysis Services along with PowerPivot/Excel Calc Services. We will look at how different deployment topologies can affect your configuration and how to plan properly for those. This includes Web Farms and SQL Clusters. I will be using tools such as Network Monitor, SetSPN along with some Active Directory tools to demonstrate how to configure and diagnose issue that may occur in your environment. You will walk away with a checklist for what you need to do to configure Kerberos correctly as well as an understanding of what tools are available to you to help diagnose issues when you get into trouble. This session will include (but not limited to) the following areas: • Breaking down the Service Principal Name (SPN) • Constrained Delegation vs. Full Delegation • SQL Kerberos Configuration • Analysis Services Kerberos Configuration • Reporting Services Kerberos Configuration • Web Farm and Cluster Considerations • How PowerPivot using Excel Calc Services within SharePoint can affect you • A look at Claims Auth and how that affects Kerberos So, bring your questions and your tiger blood, and we will delve into the complex topic that is Kerberos. We’re taking a wrecking ball to SQL Server and trying to knock it down in this nearly all-demo session. (Actually, we only want to damage it badly. Or else we’ll have to wait around while all those demos restart.) In the process, we’ll catapults cache-problem and run blocking battering rams at Castle SQL Server and then watch, live as it happens, which diagnostic and troubleshooting techniques do the best job of catching, describing, and alerting on the problem. Join speaker and evil overlord Kevin Kline with rank upon rank of his evil minions: deadlocking disasters, blocking barrages, memory morasses, craven CPUs, inadequate IO, recompiling renegades, trolls, orcs, and Ringwraiths! Ok, nix those last few. Arrayed across the battlefield, as a shining beacon of hope, are performance monitor counters, Profiler and server-side traces, wait stats, XEvents, SQL Agent alerts, and a few hairy, smelly hobbits. (Hobbit attendance is optional.) In this session, we are going to look at ways to help DBAs work smarter rather than harder when troubleshooting SQL Server performance problems! We will look at the Management Data Warehouse (MDW), which is a SQL Server database which holds data about the performance of SQL Server databases. Specifically, the MDW stores the standard performance counters which help performance monitoring and troubleshooting, thereby helping DBAs to commence a detailed analysis of the performance more quickly – leading to quicker performance troubleshooting and resolution. This session will cover: - The extraction of data from the MDW - The standard reports available - How to create customized reports using SSRS 2008 R2 using the MDW using best principles of data visualization (Stephen Few, Edward Tufte, to name a few). Join this session to help you to proactively manage your performance monitoring using the MDW whilst using intelligent laziness to get quicker answers to your performance monitoring issues. We will explore the basics of SQL Azure and then dig into the similarities and differences between SQL Azure and SQL Server. We will also discuss ways to configure SQL Server so that you can mimicking SQL Azure, thus allowing you to come closer to matching your Development and Production environments. Lastly, we will touch on how to transfer your existing troubleshooting skills from SQL Server to SQL Azure. Transact-SQL is not a very difficult language to learn. As long as the syntax is correct, it can be quite forgiving. However, to truly get the best performance from your SQL Server, careful consideration should be given to the structure and logic of the queries. In this session, we’ll discuss some Transact-SQL tips and tricks that can be employed to help you write better queries, allowing your server to perform better. The session will discuss the importance of managing the dimensions of the a data warehouse / data marts and demonstrate the steps to do it using MDS. In this session you will learn about SQL Server Database Consolidation solutions, and what technologies are available build massively scalable, high performance, easy to manage, energy efficient consolidation projects. Go behind the scenes on what it takes to build, tune, optimize and enhance a database consolidation solution that can manage thousands of database instances, grow on demand, and offer the benefits of a private cloud infrastructure like resource pooling, self service, elasticity and control. N/A Despite all the hype, it’s rare for a business to actually “move” an application directly to the cloud. The cost, risk and effort aren’t usually worth the benefit of what you gain. Security considerations, performance, and control are just a few of the reasons that many applications need to stay in your datacenter. But there are times when including a function from the cloud makes a ton of business sense, either to extend part of your internal applications to remote workers, customers or partners, or to act as a HA/DR solution. In this session, Buck Woody (Microsoft Senior Technology Specialist on Distributed Computing) will talk about how you can embrace Cloud on Your Terms through SQL Server Code Name "Denali" and Windows and SQL Azure architectures, and the considerations for creating a hybrid architecture across on-premise databases and cloud technologies. You’ll learn: • How to create a decision matrix for which elements can be extended to the cloud • A Windows and SQL Azure technology overview, code name Juneau, and DAC Fx • Component options for hybrid solutions, including: • SharePoint, • Biztalk • .NET applications • Non-Microsoft languages and technologies • Other RDBMS’s We will cover all the new capabilities of SSRS including Project Crescent, the new ad-hoc self-service reporting technology, self-service alerting, setup and integration with SharePoint, and SSRS in the SQL Azure. Everything you need to do know to get Crescent up and running, SharePoint and end to end security configuration and options Come learn how to become a Crescent hero and delight your business users with beautiful, meaningful self-service reporting. What's new for RS SharePoint mode in SQL Server code name "Denali" and best practices for configuring Reporting Services for SharePoint. Tips and tricks for building reports using SSRS (from 2008 to SQL Server code name "Denali"). In this session, you will see how to create and solve solutions using feature like Tablix, and tipis on how best to author reports and how to manage them. SSRS introduces Alerting for end-users with SQL Server code name "Denali", enabling users to set up their own alerts directly against the elements of a report. We'll cover this new capability, what you need to know for configuring and implementing this new capability, and how to manage alerts within your environment. Microsoft’s SQL Server Data Quality Services (DQS) is a unique solution that is based on the creation and maintenance of Data Quality Knowledge Bases (DQKB) and the ability to use them efficiently for a variety of Data Quality improvements. In this session we’ll review the DQS solution and its main concepts, as well as demo some of the more advanced functionalities of DQS in SQL Server code name "Denali". Performance troubleshooting is a key part of every DBA’s job, and the dynamic management views provided by SQL Server provide a wealth of information to help understand what is happening in the system. But pulling that information out of the DMVs manually can be a time consuming and tedious task—especially if you don’t do it frequently. This session will showcase how the newly updated Performance Dashboard Reports allow you to utilize this DMV data to easily identity and troubleshoot the most common performance problems on SQL Server 2008, 2008 R2 and Denali. Andy Leonard - Some thoughts on Community Bradley Ball - 24 A 5 Minute Horror Story Sharon Dooley - An Awesome Permissions Query Robert Davis - Do as I say, Not as I Do Mark Rasmussen - Revealing the Magic Dev Nambi - Top 10 Ways to Break Your Deployments Audry Hammonds - How to Make Your Data Model Suck Less Rob Farley - I Should've Looked the Other Way Gail Shaw - Common mistakes in query performance comparisons Chris Webb - Calculating the 'Last Never NonEmpty' value in MDX Amy Lewis - Fuzzy Lookup in 5! Neil Hambly - Alerting with WMI in under 10 minutes Grant Fritchey - Backup Testing, The Rant Nic Cain - Powershell: It's your new BFF John Sterrett - Thinking of hosting a SQL Saturday? Niko Neugebauer - Build Up Craig Purnell - Making Active Directory and SQL Server Hold Hands David Stein - DBA Lessons Learned from The Godfather Doug Lane - The Two Most Powerful Properties for Dashboards Chris Shaw - 10 Things I Wish I Knew Then Argenis Fernandez - Quick Tips to Enhance TempDB Performance Mark Broadbent - SQL Server Clustering for Dummies Allen White - So, You Want to Be a Consultant Join us for an informal Meet and Greet Q&A gathering on Friday, October 14 from 12:15 – 1:30pm in room 307-308, and share your thoughts, ideas and feedback on PASS in an open forum setting. PASS Board members will discuss anything and everything that is of interest to the community: PASS events, initiatives, processes, Chapters, member benefits, governance and much more. Everyone is welcome. We won't lie to you. Some of the technical professionals at PASS have been doing this a LONG time. They've seen trends come and go. (SOA, anyone? N-tier architectures? "i-" or "e-" everything?) At least one professional, Former PASS director Kevin Kline, has been around the block a time or two and has seen fads come and go. Buck Woody, Microsoft's Senior (in every sense of the word) Technical Specialist on Windows and SQL Azure doesn't think "Cloud" is a fad. In fact, he thinks he can prove to Kevin it isn't, and if you're not careful, you might learn something in the process. This ain't your father's presentation, kids. OK, maybe it is, but it promises to be useful, funny and informational. Grab your prune-juice and stake out your Lazy-Boy lounger early. We have to get out of here in time for these guys to get back to the home before dark. (WIT Luncheon) It can be intimidating to ask for promotions, raises, or benefits. However, asking is often the first step to receiving. How should you ask? What should you ask? What information should you bring to the discussion? These topics and more will be answered by panelists who have successful track records at negotiating. Learn how to make your voice more powerful! WIT Panelists: Karen Lopez Yanni Robel Rick Bolesta Sharon Dooley This Event has Live Streaming: http://www.sqlpass.org/summit/2011/Live/LiveStreaming.aspx