As a DBA one of the tools of tuning that I use is effective indexing; This is a walk through on the basics of indexing from a performance point of view, this isn't best practice or text book solutions, this is based around hard pratical experience. This session will also cover the new index features of SQL2008 which in effect allow you to create partitioned indexes on a non partitioned table. Also covered will be some of the myths surrounding index performance degredation and why and for whom we actually create indexes.
It is reckoned that 80-90% of data has a spatial component to it. But what do we do with it now? At best, we constrain it to postcodes. Well, that would be great if we were delivering letters, but the majority of us aren’t. In this session we look at Spatial Queries in SQL Server to see how it works and what can be done with it.
Here's what Gary Short said about the session when he saw it: -
"Colin Mackay's Spatial Data talk last night really rocked! If you get the chance to see it you should."
SQL Server introduces the Data Collector and Management Data Warehouse for troubleshooting common performance issues for a SQL Server database engine instance. This session demonstrates how to troubleshoot various problem scenarios using the new tools within SQL Server Management Studio (SSMS). It demonstrates how to plan for a deployment of the data collection tools taking into account lessons learned from deploying the tools within Microsoft IT. Learn how you can create your own collection sets and get the data out of the Management Data Warehouse.
Understanding memory architecture and how SQL Server uses it can help a great deal
with how you approach performance troubleshooting or setup a new SQL Server and while
a lot of information is available on the topic its often lightweight or very
in-depth with nothing to help join the two. In this session I'll be discussing common topics
like PAE, AWE, /3GB and 64bit along with more advanced subjects concerning SQL Server's
usage in a way that's very accessible to any audience. At the end of the session you will
have a good understanding of the best practices and normal usage profile for SQL Server
memory as well the architecture to help you understand why certain best practices exist
and when they can be flexible.
Statistics say “25 – 30 % of all tables for an application are lookup tables”. Whether you are working on a “small” or “big” application it is worth analysing this problem and try to generalise the solution. Saving developer’s time is saving money, less code means less bugs, code once and reuse the code through the application(s). I’ll show you my thinking about this problem and give you some examples you can use in your applications. You can, of course change the code, improve it and modify the solution in any way.
This session is aimed towards those who are new to using Microsoft SQL Server 2008 Reporting Services and those who wish to learn the features of Reporting Services 2008.
It is advantageous if you understand
• Experience using Microsoft Windows Operating System
• Basic Transact SQL Select statements
• An understanding of basic relational database concepts
With Reporting Services 2008, Microsoft takes a step forward in presenting SQL Server as an enterprise data platform rather than just a database engine. With innovations in data regions, vast improvements in visualisation, and a new Report Designer, Microsoft SQL Server 2008 Reporting Services provides a tool that can be used by all members in the organization.
This session will walk through the authoring, management and delivery of reports with a focus on the new features of Reporting Services 2008. At the end of this session you will understand how to create a report in the new report designer. Be aware of the Report management options available and the delivery mechanisms that can be used to deliver reports. This seesion will also finish on the
At the end of this session, students will appreciate how to:
• Create and explore report structures in Business Intelligence Development Studio
o New Report Designer
o Tablix data region
o Gauge data region
o Improved visualisation through charts
• Delivering reports to your User
o Report Manager
o Report Builder
• Managing Report in Report Manager.
• Reporting Services 2008 Installation.
o Where is IIS?
o Sharepoint Integrated mode
o Migration issues
This session will present techniques for minimizing downtime during database upgrades. We’ll look at what’s required to deliver a reliable database upgrade while maximizing data availability. During the session I will present my own upgrade checklist, relevant whether you’re upgrading to SQL Server 2005 or SQL Server 2008.
We’ll consider all aspects of upgrade planning, testing and preparation - including upgrading system databases, options for high availability upgrade, ensuring predictable post-upgrade performance and data consistency.
Cloud Computing with SSDS. That's not a typo that's SQL Server Data Services. Currently in beta this could be the next big thing. We will first look at the model of cloud computing and try and identify where it 'fits' in today's enterprise. Will this be the product that replaces Express or Enterprise edition? Will it replace them both? Only time will actually tell but by looking at the functionality provided in the current (whatever that is) release of SSDS we will try and draw out a picture of this emerging market. This will be followed up with some practical demonstrations of the functionality available in SSDS as we attempt to build our own data cloud.
SQL Server 2005 introduced new types of Query Plans and how Query Plans can be used as query hints.
In this session we will do a deep-dive into Query Plans, looking at how Query Plans are stored and how to interpret Query Plans. We will also look at different system management vies who can help developers and DBA's to better understand what goes on with query plans and subsequently write better performing queries.
Do you feel that there's something missing in your DBA life? I did ... before I discovered unit testing with SQL Server! Fact: you're a busy, hard-working DBA ... are unit tests relevant to you? You probably feel that you have enough to do already just "keeping the lights on".
BUT - consider for a moment that unit tests are now a crucial part of all modern development. It's at the core of agile development: Test First/Test Driven Development. So why is unit testing neglected for the database?
This session's presenter, awarded "Best Presenter" in last SQLBits(!) - will show you quickly and easily how to get started with SQL Server unit testing ... and as a bonus the presenter has several free copies of a commercial unit testing framework to give away!
If you feel that you just don't know where to start; or that you can't see how testing even applies to databases - come along to the session, give me just 1-hour of your time. I guarantee, I will change your mind.
The session is accessible - even if you've never written a single test before. The simplest tests are simple one-liners that could save you hours of pain in the future. The session goes well beyond the concepts and shows tried-and-trusted techniques used every day by the session's presenter.
SQL Server 2005 and 2008 both introduced many language elements that simplify complex queries. In this session we will look at a number of less frequently used, but very powerful T-SQL features like PIVOT, ROW_NUMBER() and MERGE.
Aren’t end-users a pain? You provide them with a beautifully crafted cube in which all their business needs have been analyzed, dimensionalized, and sanitized and all they seem to do is complain that they can’t get the information they need. What are they on about?
In this presentation I will discuss how report building imposes sometimes complex data modelling requirements that are likely to be lost in the traditional interview and analysis process and how capturing these requirements can lead to happier end-users. I will show some approaches to solving problems such as:
1. Handling of flow, balance and average data types
2. Providing commonly used time based aggregations (YTD, QTD, Rolling 12 months etc.)
3. Scaling of data to provide reports in Millions, Thousands and Units
4. Providing periodic and cumulative views of results
5. Allowing back-dating of reports so that the user can get correct results for prior reporting periods
6. Flexible handling of Scenarios and versions
7. And for those of you who get stuck with financial applications how to handle Credits, Debits and normal balance conversion
The solution shows how addressing these reporting needs will lead to additional dimensions in the data model and the need to handle complex inter-dimensional relationships.
Data Mining is now a fully paid up member of your SQL Server arsenal. Data mining allows you to look into your data and discover more value than you could by simple querying. Iam going to try to dispel some of the unfounded myths around Data Mining during the session.
In the first part of this session I am going to take you through some of the things you can do with Data mining. In the second part I will look at some of the new stuff that is coming along in SQL Server 2008.
This session is for your average .NET Developer who does SQL rather than those who are dedicated SQL Developers (that's not to say you won't pick up interesting tips).
This is some of the stuff that I have learned over the years which you might find useful. This session is completely from a .NET developers perspective and the things that have helped me out when working with SQL Server. We will be looking at T-SQL, parts of the SQL Server Toolset, SQL Server commands etc.
Hopefully by the end of this session you will come away with some useful stuff that you will be able to use in your day to day life. I will try and skip theory and best practice, and go with loose observations as much as possible.
Slowly Changing Dimensions (SCDs) in their various forms are a standard part of most BI implementations and yet they still manage to confound many developers when they come to implement them. Drawing from experience gained in the field, this session will briefly describe what an SCD is, what it does, how it can best be modelled in both the relational and OLAP layers and which of the various options available in SQL Server 2008 is the best way of loading it. Topics covered may include:
- "There are 6 types of SCD? WHAT?!?";
- "To Snowflake or not to Snowflake?";
- "The SCD Wizard is brilliant and should always be used"; *
- "CHECKSUM(): the solution to all my problems"; *
- "MERGE - the obligatory SQL Server 2008 bit":
It may also try and present an adaptable and performant strategy for loading SCDs that actually works.
* - May not be true
This session will explain the basic principles of good data vizualisation techniques which are in use on a daily basis to improve the reporting from SQL Server.
It will show examples from the "Hall of Shame" and the "Hall of Fame" and explain the good, bad and ugly referencing some of the new learnings from industry luminaries such as Edward Tufte, Stephen Few and Rolf Hichert.
We will build a simple sample dashboard from scratch linked to SQL Server and provide directions to set of reference material.
As a DBA for hire I'm mostly thrown into the deep with dozens if not hundreds of SQL Servers and SA rights. This is usually ok, I do have some DBA skills through my years of experience and I'm not one of those cowboy developers :-)
Sometimes though I get myself, and occasionally also the customer, in trouble by doing something silly.
This session is not about best practices.
I'll show you what can go wrong during day to day DBA stuff and how to fix it.
This session is designed to follow on from the PerformancePoint server introductory session and it will cover the following:
1. MS BI - "the art of the possible": there are a lot of tools in the Microsoft BI kit bag, end users aren't interested in tools, so this part of the session is designed to showcase how content can be surfaced in Sharepoint in an integrated and hopefully seamless manner.
2. MOPPS - "My First Dashboard": this will go through the process of creating and publishing a simple dashboard. Starting from nothing and ending up with a common parameter driven interactive dashboard (including PPS, MSRS, Excel Services, and ProClarity content).
3. MS BI front ends - "tips & tricks": from how to de-brand or skin ProClarity to how to build an Analytic workbench application in Sharepoint.
There will be absolute minimum Powerpoint and as much hands on demo as possible - although for those that already know MOPPS, there won't be much new in there for you, that's why it's level 200.
SQL Server 2008 has some great new features for developers which on the surface may look small but will change your development experience for good.
In this session we will cover the debugger, intellisense, intialising, incrementors, MERGE, table valued functions and more.
The ratio of DBA’s to the size of data being managed is increasing all the time as is the burden of compliance regulation ,so what can be done to square this circle? This session will show that many of the answers to these issues are included in the new management features in SQL Server 2008:
• Policy Management allows fine grain control and audit of the state of all of the servers in the organisation.
• Configuration Servers allows policies and SQL to be evaluated against a group of servers.
• Resource Governor in Enterprise edition dynamically mediates workloads when the server is under pressure.
• The Performance Data Warehouse allows historical statistics to be analysed.
• Change Data Capture and audit provide a rich source of compliance data without adversely impacting performance.
• Replication is much simpler to configure and maintain.
• PowerShell support allows repetitive tasks to be scripted and integrated into other administrative tasks in Windows Server 2008.