Introduction

Mr. Netz first introduced the concept of the Unified Dimensional Model (UDM). This conceptual model ties together the many ways to look at data (from the highly-normalized database to the data warehouse) to be able to present the data to a multitude of reporting tools. It looks like this should be a session by itself. He then reviewed how the various server "abilities" (manageability, scalability, availability, serviceability, security, and programmability) have been greatly changed in SQL 2005. These plus changes in the predictive analysis tools (with eight different data mining algorithms) help build a much data warehouse.

Most of his session, however, consisted of a demonstration of cube-building and querying - the heart of Analysis Services. The first step is to define a data source view (DSV) - basically a subset of tables in a relational database. The data source view designer now has the ability to include related tables as well, which drew an interesting comment from two attendees behind me: "That assumes a lot", said one. "Yeah," said his counterpart. "It assumes you have foreign keys in your tables, which we don't…"

One interesting point is that calculated columns can be added in your data source view, as well as other changes that can help get around a DBA that is reluctant to create the views you need in your DB. This drew some chuckles from non-DBA types in the audience. Also, DSV's are stored as XML and can be version-controlled - again highlighting the high degree of integration across all of these tools.

Cube construction was also demonstrated. The wizard appears to be pretty competent, such as being able to flag what it thinks should be fact tables and what should be dimensions. Because the cube and dimension designer is one tool, developer productivity is greatly enhanced; with the current version, you have to go back & forth between the cube editor and dimension editor when you need to make changes.

Not until you actually build (deploy) the cube is the server actually affected. Up to that point you are only in design mode. This should make moving the databases around a lot easier. Processing is done in parallel so it should be a lot faster as well.

One warning he made is that you must be careful about the number of elements you bring in from your relational database. SQL 2005 has the ability to over-specify members, making a lot of excess dimensions in your cube - which could be disastrous for performance when it comes time to build the cube.

Unfortunately, at this point, he began to get into the querying side of Analysis Services, which I don't have a lot of familiarity with. It was, frankly, hard for me to follow the presentation at this point. One comment did stand out, though, in that MDX now supports a CASE statement in query building, instead of the older chained IF statements. The audience seemed to welcome this quite a bit. In fact, I should make note here of the fact that the room was packed for all three presentations. This marathon session seemed to tie together a lot of what was being presented regarding Business Intelligence, and there was a very high degree of interest from the attendees.

Brian Welcker presented the last session on the changes to Reporting Services, with some help from a couple other members of the MS design team. Unfortunately a high-priority phone call kept me away from the first portion of his demo, but what I saw was pretty impressive. This included designing user-interactive reports (drill-down and drill-through reports, as well as imbedded bookmarks and hyperlinks), adding code to reports to do special tasks (like changing background colors under specific circumstances), and a change to the Query Designer that prevents it from changing query code that you may paste in from an outside source (you might have had to experience that pain to appreciate it). He also mentioned the high integration of VSS into Reporting Services, and finished with a sample application that used a web service to communicate back to the data source and run the reports - tying everything together from the previous sessions.

There was, frankly, a LOT of information presented in this giant demo of the MS BI tool set, and by the end of it my brain was full. It is going to take some time to digest all of this, but it was very interesting to see the whole process from integration to final output. I'm looking forward to trying this out for my self.

At the Trade Show

Today was the last day of the trade show, so I made a point of visiting with as many vendors as I could. There were some interesting demos going on, including one by Unisys involving a gigantic 50-million member cube.

One of the show sponsors is Idera, a company from my home town of Houston that markets enterprise-level SQL management tools. I spent some time talking with Lisa Benston, one of their marketing reps, and Rick Pleczko, their CEO, about their tool suite and history. Given the number of people they had at the show, I was frankly surprised to find out that, as a company, Idera is less than a year old, but already a major player in the enterprise management tool market. In fact, several other vendors made a point of mentioning that they work with SQLSafe, Idera's new backup and recovery package, which tells me that the company has some solid stuff available.

Mr. Pleczko mentioned that after he retired from Net-IQ, he didn't want to really get out of the software business. He and some others from Net-IQ (with whom they have a licensing and marketing agreement) formed Idera with the view of building the best enterprise management tools available. It seems to be working, as they have over 350 major corporations using their software.

Idera's tool offerings are impressive. SQLSafe has some awesome numbers, and a demo I watched of DTx (their data transformation and migration tool, and one of their first products) was pretty interesting. Mr. Pleczko wasn't specific when I asked what they had in the works for future products, but did mention that he felt that SQL 2005 would give them many opportunities, as DBA's will be hard-pressed to get up to speed with all of the changes in the platform.

The reason that I bring this up is not that I get anything from Idera (well, I did get a t-shirt), but to illustrate the opportunity attendees have at conferences like this to meet vendors and talk about their products. It's one thing to read advertisements in the trade mags; it is entirely different to see demos and talk to the salescritters live and in person. Plus, there's usually a ton of swag available; vendors will try some entertaining things to get you to stop and talk. The Idera folks had a giant pchinko board set up, giving away t-shirts, stress balls, fold-up flying disks, and a grand prize that, in my infinite wisdom, I forgot to make note of.

• Log Growths
• Percent Log Used
• Transactions/Sec.

• Latches
• Average Latch Wait Time
• Locks
• Average Wait Time
• Lock Waits/Sec
• Number of Deadlocks/sec.

• General statistics
• User Connections
• Memory Manager
• Memory Grants Pending
• User Settable Query

In the performance monitor you can use also the Alerting. Alert are defined events that raise a notification of some kind. The Alert can by defined in performance monitor. Alert will create error in the event viewer. You can also create alert in SQL Server 2005. You can use Microsoft SQL Server Management Studio to create Alert. If you need to monitor many servers you can use the Agent to set alert for more then one servers. To do that you can go in SQL Server Management Studio in Agent, properties Advance to set the alerts.

Kevin also shows us many places where you can get tools to monitor SQL Server Database. This session was good to show the entire different thing you can do to be proactive in performance tuning.

by Daniel Leclair

09:45 - Server and Data Recovery – Internals and Troubleshooting - Bob Ward

My DBA skills not being where they should be, this seemed like a good session to get familiar with some of the problems people encounter, and how they can be diagnosed and fixed. Bob Ward of Microsoft made this session all that and more – about a half-hour more, actually, in front of a packed room for the entire presentation!

Mr. Ward led off with an overview of related Microsoft Product Support Services (PSS) topics at the conference (unfortunately most had already been presented), comments about the PSS newsgroup, tools that are available (including Read80Trace, Ostress and PSSDiag), and the hands-on lab available at the conference (more later). Then came several disclaimers: many of the diagnosis and recovery procedures he would discuss were undocumented. He termed these “Break Glass In Case of Emergency” tools, and while tested, backwards compatibility was not guaranteed and some were potentially dangerous! And, while he would end with a discussion of improvements for SQL 2005, most of the session revolved around SQL 2000.

Next, he discussed some of the various reasons why SQL Server might fail (most sounded like user- or developer-induced issues or hardware problems, rather than bugs in the server software itself). These included unauthorized or unscheduled server shutdowns, Windows exceptions, unhandled C++ exceptions, runtime library errors, forced DLL exits, and more. Most of these would be recorded in either the server error log, event log, or userdump, and he went over what to look for in these logs.

SQL Agent includes an option to auto restart if it stops unexpectedly, and if this is set, it could make debugging the problem more difficult. The upside to this is that an auto-restart will write to the log. Lastly, Windbg – the Windows debugger - may need to be attached to find out what is going on if answer is not in the logs (and he went into the way to do this).

SQL Server hangs are another issue with many causes. Many times these are blocking issues, but some cases may involve connection problems, CPU spins, or memory problems. Many of these are tough to figure out, and can vary by service pack installed. A discussion of a particular error (#17883) that seems to be related to anti-virus software was particularly noted by an attendee sitting next to me; she mentioned that it happens only on her production machines that are running Service Pack 3.

Mr. Ward mentioned that virtual memory problems may result in threads not being able to spawn. Each thread needs 512K of contiguous space to be created. A highly-fragmented hard drive, too many DLL’s being loaded, and other factors may mean that there is not enough contiguous space available.

Sometimes, SQL Server won’t start at all. This may be the result of startup parameters being invalid (i.e., someone might have tinkered with them). Startup parameters can be seen in the Enterprise Manager properties.

One of the recurring themes in the session was that the error log and userdump files very often have clues as to what is happening. For example, Mr. Ward said that PSSLooksAlive can be used to set breakpoints to trigger the dump when trying to find cluster failovers.

SQL I/O problems were the next topic. I/O problems can cause data corruption, sometimes down to individual bit level (also called bitflipping). Apparently, IDE drives are more prone to these issues than SCSI drives are, which I found interesting. Filter drivers (which intercept kernel calls) used by anti-virus and some backup software can also cause problems. Torn pages, stale read/writes, “scribblers” (data written on top of other data) and bitflipping were all discussed.

At this point, Mr. Ward asked what time was left. Oops – only fifteen minutes! He was a fast talker but seemed to shift into overdrive at this point. The session went a full half-hour later than advertised, but most of the attendees stayed until the absolute end. Somehow, he managed to cram discussions of system database recovery (make sure you shut down sqlagent before trying to restore msdb), backup and restore failures (slow backups can also be caused by low virtual memory), database offline problems (including setting SQL in emergency mode), log rebuilding (undocumented and extremely dangerous because log consistency is unknown – definitely a tool of last resort), and DBCC CheckDB errors into this 45 minutes, as well as a very small overview of what’s new in 2005.

Somewhere in all of this, he mentioned that there is a Service Pack 4 scheduled for beta in November, and talked about the lab downstairs that helps walk-thru some of the problems and how to solve them. Despite going overtime, on the whole this was a fascinating presentation; so much so that I decided to go downstairs and talk with him some more about it.

First, though, I caught up with the attendee that had mentioned the 17883 error. Candice Logue, DBA for McCain Foods in Florenceville, Canada, is responsible for 30 to 40 servers spread out between Canada and Europe. I asked her if she felt the session was helpful. “Oh, definitely,” she said. “The info is going to be really helpful. We seem to have particular trouble with the servers in Europe.” This was her first SQL conference. “I did the formal proposal to management and everything, telling them what sessions I wanted to go to and what I thought I’d get out of it. This has been great!” With two years of DBA experience behind her (“I was kind of thrown into it after the previous DBA quit suddenly.”), she said that most problems seem to be hardware related, or else caused by problem queries and deadlocks. “At least in our installation, we’ve seen few if any bugs in SQL itself.” Had she been down to the PSS lab? “Not yet, but if I don’t run out of time, I certainly will!”

SQL PASS 2004 PSS Service Center

by Daniel Leclair

After lunch, I decided to check out the lab, and chat more with Bob Ward. “Ken Henderson, who works for Microsoft as an Escalation Engineer, first approached me in 2003 about setting something up,” he said. “It lets attendees kind of get into our thought process when walking through a server problem. It was a big success, so we expanded it for this year.” I didn’t count the machines, but there looked to be twenty or more, about ¾ of which were in use. “We’ve been busy the whole conference,” he said. “Sometimes there were six or seven people waiting in line to try the labs!”

It did seem to be a big hit with the folks in the lab when I visited. In fact, one attendee said she paid the conference cost herself just be able to do the labs and check out the other PSS-related sessions!

Five different sections of the hands-on labs were available:

• Blocking and Deadlocking, including a demonstration of the PSS tools PSSDiag and RML Replay
• Performance, including practice with READ80TRACE and OSTRESS
• Memory issues and strategies to solve them
• Debugging tools that the PSS team uses
• Server and Data Recovery, including techniques to bring SQL Server or a database back online.

The labs from 2003 are available online (do a search for PSS Service Center on www.microsoft.com); this year’s exercises should be available in three or four weeks.

A Last-Minute Interview

by Daniel Leclair

On the way thru security at the Orlando airport, I happened to end up behind Donald Farmer, and he was gracious enough to answer a few questions about the changes in Integration Services.

First off, I asked him about why DTS was totally rewritten. Basically, DTS in SQL 2000 was nearly the same product it was when first brought out with 7, and no longer able to do to high-speed conversions that they wanted to build into the new product. So, starting from scratch, the Integration Services engine was rebuilt. That work, plus some small acquisitions along the way, has turned it into the product it is now.

Being ignorant of these things, I asked if he thought the improvements in Integration Services would drive some of the third-party tools out of the market. He thought not, as MS already had a large percentage of the SQL integration market – on the order of 80 to 90%. In addition, MS really wasn’t trying to compete with the multiple-platform integrators that specialize in a pure bytes-per-second basis – Integration Services is much better in the bytes-per-second-per-server category.

He also said that several third-party tool providers had contacted MS in order to leverage their products off of the greatly-improved DTS engine. And, it only made sense to work with them, as the API was openly published anyway.

Regarding the issue of non-templatable tasks, he said that it would be difficult to construct the base task templates (which are what they are anyway) in such a manner as to be able to contain generic-enough SQL scripts to be useful. Yes, he said, the method he should in his session (using the Clipboard Viewer) to create cut-and-paste task “pseudo-templates” was a hack, but it worked. There were no immediate plans to make things like the task objects sub-classable objects, but that was always an option for the future.

Wrap-Up

by Daniel Leclair

Without a doubt, this was one of the most interesting conferences I have been to in a long time. What is even more impressive is that it is put on by a non-profit, user-supported organization. All of the speakers, from the teams from Microsoft on down, were enthusiastic about their topics and more than happy to chat with folks regarding, well, just about anything. My only complaint is that it was impossible to see every session I wanted to! However, many were being recorded, and will be available at a later date for purchase.