facility9

SQL Server

Scripting DBA Actions - Dan Jones (Microsoft SQL Server team member) has put together a great overview of his tips and techniques for automating DBA tasks. This comes as a direct result of sitting in on the panel discussion on automation from PASS.

Summer Camp for DBA’s (#SQLPASS) is over for 2008 - TJay Belt summarizes what he got out of PASS and how we were all able to use twitter to keep up to date on conference happenings. TJay is right on the money about twitter and I would expect to see him writing and doing some pretty cool things with twitter sometime between now and the next PASS Summit.

PASS Session on Analytics by Donald Farmer (#sqlpass) - Brent summarizes what was, without a doubt, my favorite session at PASS. The new things that are coming in analysis and business intelligence make this sector very compelling.

Those kids won’t eat anything! - A practical example of the analysis tools that Donald Farmer spoke about. Jamie used the Predication Calculator to determine what foods his exceptionally picky kids will eat. There are some good examples that will be hilarious to anyone who has ever tried to get a picky kid to eat.

Thursday

After the Thursday’s keynote, I spent some time talking with Brent and meeting various vendors and conference attendees. It’s been good to have a little bit of time to relax, I’ve found.

After lunch, I attended Paul Nielsen’s and Louis Davidson’s presentation on smart database design. If they present on this topic, I definitely suggest that you attend it. In addition to being a hilarious presentation given by two exceptionally smart men, I took a lot away from it. One of the biggest things is the universal answer to almost any question: ‘It depends.’ There isn’t a magic solution. Throughout the presentation, Louis and Paul provided a lot of tools and rationale to use to make the decisions when a hard and fast answer isn’t immediately available. This was the best luck I had with sessions all day.

In the afternoon, I attended a SQLCAT session on Large Scale Relational Data Warehouse Learnings. There was some great material presented. Unfortunately, the bulk of it went over my head and I left the session about halfway through it.

In the day’s final session, I attended a Session on Integrated Business Intelligence Solutions. I learned a bit about building cubes with BIDS, but for the most part this was an introduction to report builder. I left early and was greeted by a book from Louis Davidson. I went to the SQL Server Heroes Unite event, but I left early because, frankly, I was exhausted from all of the learning, networking, and socializing.

Friday

Friday morning’s keynote by David Dewitt was amazing. He clearly explained the technology behind Gemini will work on a more technical level. Normally this would be a difficult subject to comprehend, but David made great use of his slides to clearly explain the core concepts in a clear manner.

I, once again, missed the morning session while I spent time socializing and getting to know my friends.

When the conference started, I had originally planned to spend my time on the Application Development track. However, after the first day I realized that the most important thing that I would get out of the summit was to expand my knowledge and get out of my comfort area and learn more about different aspects of SQL Server. With this in mind, I attended Donald Farmer’s session on Integrating Predictive Analysis Throughout the Data Lifecycle. Brent Ozar did a great job of summarizing Donald’s presentation. One of the most interesting things that Donald showed was how to use an existing corpus of data to perform form validation based on trends in the existing data to determine if data is valid within statistical norms.

Later in the afternoon, while loitering around in the hallway with a few others, Donald Farmer came along and said hello to us. Within a few minutes we were interactively mining his sample data on Titanic survivors. This emphasizes how easy it is to use these new tools to mine and analyze the data on the fly to produce a better, or different, understanding of the body of data.

The final session of the data was Louis Davidson’s talk on Why Normalization Matters. Louis is just as engaging of a presenter on his own as he was with Paul Nielsen. While there was some review here, the important thing wasn’t the material in the presentation. The important thing was the understanding and the explanation that Louis brought to the material. Ultimately, the main point that Louis made was that if you think about table design carefully and intelligently, it’s very easy to design tables and the relationships between them. The main reason that many databases are not in third or fourth normal form isn’t that it’s difficult it’s that people try to take shortcuts designing or extending a database. Of course, the only way to do things right is to practice.

Everything else

The 2008 PASS Summit was my first conference and was a great experience for me. I got to meet a lot of great people, volunteer and provide help for an organization that I enjoy being a part of, and learn more than I ever thought I would learn in a week about parts of SQL Server that I never thought I would have a chance to look into.

Social networking made this all easier. By using twitter, I was able to keep in touch with all of my friends, get tips on sessions to attend, and coordinate attending sessions together. TJay Belt posted a great blog summarizing how we were able to use twitter during the conference to keep in touch, network, coordinate, help each other out, and socialize.

PASS was a great experience, it gave me an additional dimension to my understanding of my own skills and of SQL Server as an entire family of products and something more than just a relational database engine.

Liveblogging the PASS Summit 2008 final day keynote. Refresh this page for news. Or, better yet, visit Brent Ozar’s coverage for additional info.

10:04 Parallel optimization is hard. Very hard. There’s a lot going on when the data is distributed across multiple nodes. Gray Systems Lab is working with DATAllegro to solve these problems. There are a great number of challenges that are up ahead. Big things are coming (har har har).

10:02 Partition skew is a concern when fragments/nodes don’t end up containing the same number of rows. How does this get solved? You can use range partitioning or you can change the hash function you’re using to partition the table.

9:57 Table repartitioning makes it possible to shuffle rows around so that all of Bob’s order rows are on the same server as Bob’s customer data rows. Joins can happen locally once you do this, even though you have a giant lump of data spread across a huge number of nodes.

9:50 This is very interesting stuff and I would encourage anyone interested to get a hold of the video of the keynote. I’m trying to keep up with all of this and blogging is getting in the way. Blogging will resume when the subject changes.

9:35 He’s now explaining how this would all work in a real system, not just in theory. The magic is that the software makes this all transparent outside of the database. There are no indexes, sadly, but queries take less time because they are distributed. Brent Ozar has a good overview of what’s going on from an engine perspective. Check it out.

9:30 Hash partitioning explained now. This is great… he’s explaining how it works and what’s wrong with it.

9:23 Horizontal partitioning is up now. This is some really really cool stuff… Round Robin partitioning is up now, also very cool. The problem is that you can’t tell where a row lives.

He’s showing all of this with animated slides. There’s very little to try to comprehend - he’s just showing it.

9:20 There’s a picture of a cluster of VAX machines. Oh, VAX.

9:16 Shared Memory (everything is shared in one machine) doesn’t scale up very well, the hardware doesn’t scale up very well up at all.

Share Disk is where nodes of commodity hardware uses local storage. There’s still limited scaleability here, too.

Shared Nothing is where you have commodity hardware with dedicated disk and memory. Everything is connected via commodity hardware. This can scale as long as you have money to buy commodity hardware. This is how the big boys do it.

9:15 Apparently, eBay has two 2 petabyte systems and one 5 petabyte systems. That’s a lot of data! He’s describing how the basic forms of scaling work.

9:10 The reason we need to know about this is because this is the theory behind the new DATAllegro products that are coming out next year. The point of doing linear speed up and linear scale up is to add hardware resources incrementally (10% more data? 10% more resources)

9:06 David Dewitt, a technical fellow with Microsoft and Ph.D. holder, is coming on stage now for the last keynote. He gets Alice Cooper as welcome music. He’s going to be talking about parallel DBs for scaleability.

9:00 Patrick is continuing to show different hardware that could be used and why you’d want to use it to meet your needs. This is a review of a white paper that’s available through Dell. Basically, add more servers to the query layer to meet load and distribute the data from the processing layer. Once you get more load in the processing layer… add more servers.

8:55 The first speaker is Patrick Otriz - Solutions Architect with Dell. What Dell doesn’t do is application development - Patrick’s job is to drive consistency around what Dell does - meet Service Level Agreements and establish business continuity plans. He’s describing the full stack and the problems that people will be facing at the hardware level.

8:48 SQL Heroes Contest winners are going to be announced. This was to create a project on codeplex using SQL Server. There has been an effort to get community sample applications that run along side the Microsoft sample databases - the SQL Heroes Contest. 60% of the submissions were from outside of the United States. Didn’t have time to type all of them out before the list was off the screen, hopefully the list will be published somewhere. (thanks to Adam Machanic, they’re Extended Events Manager, SSISUnit, CDC Helper, and QPee tools by Jason Massie!)

8:44 Bill Graziano came out riding on a tricycle. Early bird discount is $995 if you register before December 31st, act now! The summit will be in Seattle, Nov 3-6 in 2009.

PASS is looking for content either through videocasts (PASSTips) showing off new features or through technical articles.

Three new board members have been elected:

• Douglas McDowell
• Lynda Rab
• Andy Warren

10:06 SQL Server 2008 is going to increase productivity, once these new features are available we’ll be able to leverage additional tools both as data people and as consumers and users of data. Office and Excel are going to be huge in the future, combined with SharePoint and PerformancePoint. These new tools, the new vision is going to be amazing!

10:04 The users are able to serve themselves. This is very very important to the users. It’s very important to data folks. It makes our jobs easier by offloading the development time. This is all built on SQL Server 2008!

10:00 Analysis Services now has a section in the Share Point administration page, cool. Showing SharePoint as a way to view key IT decision points - query response times got bad, more memory was added, query response times got good. This is way cool. “Social network for data… it’s when data suddenly becomes significant.”

9:57 More Donald Farmer on stage.

9:56 Gemini is about end-user empowerment and making it visible and trackable. It’s managed self-service.

9:52 BI today - you can do embedded BI today. What’s in the future? More solutions building - adding more of these capabilities to the end users. Users will find a way to solve their own problems, so we need to be on board with how to help them and support them.

9:50 BI is becoming part of the standard interface. It’s the only way to help it grow to every user. Embedded reports again. Embedded KPIs. With the same tools, it’s a lot easier to move from OLTP to BI. We’re putting BI into the context of the applications we all use today. Context is king. Give data context and meaning and it suddenly becomes information

9:45 BI is very very important for the future of SQL Server. Bigger deployments, bigger volumes of data, better UX. Reports are being embedded inside of mainstream applications, you can even include a Report Builder in your apps!

9:42 Overall there are a lot of great features available in Report Builder 2.0. Looks like it’s a lot more powerful that it’s ever been, there’s a lot of potential.

9:40 Okay, these new features are very, very cool to the business people, however I’m not sure that the SQL Server nerd brigade really wants to see how their boss’s boss’s boss can build reports that help them downsize the DBA team.

9:37 In under two minutes, we have a slick report with a nice theme, formatting, a data source, and now we’re running it! TA DA!

9:35 Report Builder 2.0 can be downloaded right now. Go get it! Go! Go! Go!
New UI in Report Builder - it looks like Office. Very smooth integration. Report Builder has many more powerful features available - users can write MDX or use those smelly old reporting cubes.

9:33 Carolyn Chau is taking the stage - Lead Program Manager for Report Services.

9:31 Managed self-service building - give the users more power with Report Builder 2.0

9:30 “SQL Server is a data platform that works in a heterogeneous environment.” Ain’t that the truth! New data providers have been brought out for Oracle, TeraData, others.

9:27 Tom is talking about scaling in SQL Server - out and up! There was a big focus in SQL Server 2008, too, on bringing together the end-to-end experience - moving data into SQL Server through SSIS, OLTP, SSAS.

9:25 Tom is back on stage talking about moving away from BI as a specialty and moving towards building collaboration that turns data into information and helps people analyze their data in Excel. It brings BI to the primary data consumers, really.

9:20 Excel, as a front end to PerformancePoint data, can be used to adjust forecasts. Changes in the Excel spreadsheet happen in real time with PerformancePoint and then it’s possible to post an announcement to your team with SharePoint’s built in collaboration tools.

9:17 Back to the dashboard - task driven work instead of app drive work. Showing off the chat integration in SharePoint. Also showing off some of the custom searching you can do in SharePoint based on data stored in SQL Server.

9:14 Demonstrating scorecards, plan vs actual performance using SharePoint as the interface, office docs and Outlook tasks as the home page. There are some great tooltips popping up giving additional metadata. Live Maps integration showing running oil rigs with weather overlays, additional contextual information for each of the oil rigs when you hover.

9:12 Bruno Aziza, Business Architect in Enterprise Marketing, is up on stage to demonstrate a real-life example of how pervasive BI can be used with SharePoint, PerformancePoint, and SQL Server.

9:10 Microsoft’s goal is to bring value to all users in an organization, not just specialists. The user experience should be familiar, not confined to special tools. It shouldn’t be a quantum leap to go from being an OLTP person to a BI person (I agree with this! It’s been very tough for me to get my head around what they’re going on that side of the house).

9:07 BI is definitely a huge part of what Microsoft is doing - Pervasive BI is the theme of the keynote. How can we catalyze BI adoption in our organizations?

9:05 Tom Casey - GM for Microsoft Business Intelligence at Microsoft is giving the keynote.

9:02 All of the volunteers and bloggers have been recognized for the help they give to the community. PASSion Award for being an amazing volunteer: Kathi Kellenberger.

9:00 Hosted Trial Program of SQL Server 2008

8:57 PASSPort is going live today. This is a social component of the sqlpass.org website that allows PASS members to participate, learn, and be recognized within the community. We’re getting a demo now.

Create a profile by Dec 31, 2008 and you might just win a red Dell XPS laptop!

8:53 Technology Update - sqlpass.org has left beta. Thanks to MaximumASP and Dell for hosting and hardware. Key feature updates:

• Technical Articles
• PASS Tips
• PASS Blog
• Over 300 hours of recorded content
• Community Events Calendar - user groups, etc - will be coming soon
• Chapter website integration

I’ll be providing content for the AppDev SIG once the conference is done.

8:50 PASS Financials - PASS is committed to openness and transparency in both the financials and board minutes. The conference is responsible for 86% of the organization. Come to the Summit, help support PASS!

8:49 SQL Server Heroes Unite event from 6-9 PM in the main hall.

8:47 Overview of today’s events: Women in Technology Luncheon (don’t have to be a woman to attend). PASS Summit Expo Hall open from 11 to 5. VOTE IN THE ELECTION BY 3PM TODAY!

8:45 Nice plug for idera and their vespa

8:42 More Steppenwolf this morning. Rushabh Mehta is up on stage on a Vespa right now. There’s some great self-mockery of his “well-shampooed” hair going on.

After today’s keynote, I spent some time as an ambassador for PASS. Being an ambassador means that you stand in the hallway wearing a red vest before and after sessions. You help direct people to their sessions, answer questions about various sessions, and smile to help people feel more comfortable in a completely overwhelming environment. Helping out was very very rewarding. I had a blast being a PASS ambassador, and I’m looking foward to being an ambassador on Friday morning at breakfast.

Interpreting Perfmon and Profiler Results with Cloud-Based BI

Brent Ozar was asked to present at the last minute, but that didn’t detract from the presentation. After this morning’s keynote talking about the cloud opportunities, I knew that I would definitely need to get my brain around how to use the cloud effectively in my job so that I could advise business users on what they can do with the cloud.

Brent gave a great overview of how to use the Microsoft cloud services in Excel to analyze and slice & dice perfmon and profiler information to get a solid understanding of what’s actually going on inside the database. One of the biggest points he stressed was how you need to be intelligent in how you slice and dice and use the analysis tools available because if you work with garbage and apply meaningless analysis, you’ll only be making decisions based on garbage.

Brent blogs about the cloud and SQL Server at brentozar.com.

Additional resources are available at:

• SQL Server Pedia
• CloudEnterprise.info This also includes cloud outage information
• SQL Server Data Mining This is where you can get the excel add-ins for the cloud.

Database Unit Test Why-How-Now

Jamie Laflen - team lead from Microsoft for this product - presented on how unit testing is integrated in Visual Studio Team System or Team Dev edition. I took a lot of notes in this session about the nitty gritty details, but it boils down to this: VS now has the ability to generate unit tests from your database and run them through team build or msbuild.

Jamie did a great job of covering all of the features. There is a lot of flexibility - multiple connections can be used to tear down and set up to use a privileged account to set up data and an unprivileged account (web server, anyone?) to execute the procedures, test data can be deployed, schema changes can be deployed, the tests can be configured to run against a target server. This is an impressive addition to the tool box of any DBA or database developer.

The options for test development are opened up, too. It’s possible to write unit tests in T-SQL or any .NET language - DBAs, database developers, and application developers can write unit tests. It’s also possible to create custom data generators and custom test conditions as well as code analysis rules.

Oh, this will work against SQL Server 2000, 2005, or 2008. That’s right, you can use this without upgrading your database, you just need Visual Studio.

Partition Alignment

Jimmy May presented on partition alignment. I knew a little bit about this problem going in, but he really made it make sense to me. I know my limitations and I know that I couldn’t adequately explain this topic, so I’ll defer to Brent Ozar’s article: Jimmy May explains Partition Alignment.

VOTE!

You should have received an email about how to vote for the SQL Server board. If you’re attending the conference and you haven’t, get down to the PASS booth and mention that you haven’t received your instructions on how to vote. It’s very important that you watch the candidate videos and get out there and vote. It’s fun AND it’s free!

Other recaps

Brent Ozar has beaten me to the punch with recap of today’s activities