August 2009
Business Intelligence: Planning Your First Microsoft BI Solution
Even BI novices can learn what goes into creating a BI solution, from planning and designing a data mart to preparing data. An Adventure Works example illustrates the first steps a company can take to ensure that its BI solution supports the strategic decisions it needs to make. Stacia Misner
Business Intelligence: Building a Data Foundation for a BI Solution
See how you can design and build a data mart that uses SQL Server 2008 Integration Services to perform the extract, transform and load (ETL) processes for the Adventure Works BI solution introduced in "Planning Your First Microsoft BI Solution." Derek Comingore
Web Infrastructure: ASP.NET Web Cache Spurs Performance and Scalability
By reducing the number of times that certain Web pages execute, Web-caching solutions can dramatically reduce performance and scalability bottlenecks. This article offers a comprehensive look at ASP.NET Web Cache key features, geographical cache distribution, Web-caching clusters and more. Iqbal Khan
Microsoft Dynamics: Troubleshooting Microsoft Dynamics CRM
Fixing server-side performance issues starts with an understanding of the components comprising Microsoft Dynamics CRM and how those fit in your overall system architecture. Here’s a look at the Microsoft Dynamics CRM solution, five steps to follow for troubleshooting and tools for helping you diagnose the problems. Aaron Elder
Commerce Server 2009: Getting Started with Commerce Server 2009
Commerce Server 2009 leverages the strengths of a mature platform, addresses many common deployment pain points and provides a solid e-commerce framework. Learn about CS 2009’s enhanced features, optional components and the new functionality created by its integration with SharePoint Server 2007. Alan Maddison
Columns
From the Editor: A Friendly Push in the Right Direction: | Toolbox: New Products for IT Professionals: Greg Steen highlights new tools and utilities for the IT Professional.Greg Steen | SQL Q&A: Checksum Problems, Choosing the Correct Recovery Model and More: Paul S. Randal answers your SQL questions: How can I tell when there’s a checksum problem without waiting until the monthly maintenance plan? Is it true that using a GUID as the clustered index key can lead to performance issues with indexes? How can I choose the correct recovery model? And more!Paul S. Randal
Utility Spotlight: SDelete: This free utilitly from Microsoft permanently wipes single files, directories or multiple objects, and purges unallocated clusters of a disk.Lance Whitney | Inside SharePoint: Improving SharePoint Performance: Pav Cherney presents an overview of key SharePoint architecture components, describes how they can lead to common performance issues, and discusses how to resolve and troubleshoot problems.Pav Cherny | Windows PowerShell: Stay Seated!: Windows PowerShell v2 aims to fix the server management shortcomings of Windows Management Instrumentation. Don Jones shows how the new remoting features in Windows PowerShell v2 are a powerful and simple way to run any commands on remote computers.Don Jones
Geek of all Trades: Microsoft's New Certifications: What They Are, Why They Matter: With the Microsoft Certified Systems Engineer credential being retired for all iterations but Windows Server 2003, a new certification program has been created. Greg Shields explains how this new certification path helps to better identify where your skills lie.Greg Shields | Windows Confidential: Windows 'Prettified' Filenames: Why case-sensitivity doesn’t matter to Windows, just to a user’s sense of aesthetics.Raymond Chen


Business Intelligence
Planning Your First Microsoft BI Solution
Stacia Misner
At a Glance:
  • Introduction to BI
  • Avoiding common problems with corporate databases
  • A walk through the Microsoft BI stack
  • Building a simple solution

Contents
What Is BI?
What's Wrong with Querying Your Corporate Databases?
Support for Decision Making
BI in Action
The Microsoft BI Stack
An Approach to Solution Development
Getting Started
Next Steps
Most database administrators (DBAs) have encountered some form of business intelligence (BI) while managing their organization’s data and Microsoft SQL Server implementation. Many other IT professionals, who don't have DBA responsibilities, have likely heard of BI but don't have firsthand experience with it or really even know what it is. In this article, you'll find out what all the fuss is about. Once you understand what BI technologies can really do and see how BI solutions are constructed on the Microsoft BI platform, you'll discover why BI isn't just for DBAs. If you're savvy about BI, you'll be in a better position to support your organization's BI initiatives when they come up (and they will) and you'll also realize how you can use BI to track and analyze data relevant to your role, making your job easier and deepening your understanding of that data.
This article is the first in a series of articles that introduce the Microsoft BI stack. In this initial article, I define BI and describe the high-level architecture of a BI solution in general terms. I also provide some insight into the process of building a BI solution. To delve into the SQL Server BI technologies mentioned in this article, you can read the companion articles written by Derek Comingore in this issue (see "Building a Data Foundation for a BI Solution") and by Scot Reagin and me in upcoming issues.

What Is BI?
Several years ago, while I was coauthoring Business Intelligence: Making Better Decisions Faster (Microsoft Press, 2002), I was surprised by how strongly my peers disagreed about which technologies should be considered within the domain of BI and thus within the scope of the book. It was an enlightening experience to hear such divergent opinions among BI professionals about which tools they considered BI tools and which they excluded. This difference of opinion still exists within the IT industry, and many continue to debate the definition of BI. To me, BI is as much about business process as it is about technology, so I start defining BI from that perspective.
As a business process, BI is a series of activities you perform to gather and analyze data so that you can make better decisions and improve your business by sharing the results of your analysis with others. Whether you need information to decide how to make your daily routine more efficient or to support long-range planning, such as next year's budget, the steps you take to find, transfer, format and study the data are all part of BI. In addition, BI includes the processes you use to make your results available for later reference so you and others can measure the impact of the decisions you made after studying the data. Typically, BI is an iterative process. You analyze the data to see what has happened, you take action to ensure good things keep happening and bad things stop, and you then analyze the data at a later point to determine whether your actions made things better or worse and whether external factors helped or hindered your efforts.
Given this broad definition of BI, you're using BI even when you're jotting down bits of information or creating lists to help you make decisions throughout the day. Introducing technology into some or all of the business processes you use to gather, analyze and share information can make these processes more efficient. Organizations tend to start small when adopting BI technology, often using it at first to solve specific problems. Over time, the use of BI technology grows incrementally as the emphasis shifts to disseminating information efficiently across the organization. As its BI strategy continues to mature, the organization usually acquires more sophisticated tools to enable greater interaction with and exploration of data.

What's Wrong with Querying Your Corporate Databases?
In any definition of BI, data is always the focal point. You might be wondering why you need to bother to create a BI solution when you can simply query one or more of your organization's databases to get the data you need. If you're the sole consumer of the data that you're gathering, if you have the tools, skills and security privileges to access the corporate databases, and if all the data you need is in those sources, you probably don't need a formal BI solution. By contrast, if you regularly need to share information with others whose technical skills and security privileges vary, you need to create a BI solution that is simple to use and maintain. You also need to consider that once your colleagues hear about this neat BI solution, they'll want to use it too. To anticipate this demand, your solution should be scalable from the beginning.
On the surface, allowing everyone in the company to run reports to get data from the corporate databases might seem like a good idea, but this approach probably won't be very popular with the DBAs. As they will tell you, querying the corporate databases directly can come with more than a few problems. Here are a few of the more common ones:
  • If the organization's data is stored in different platforms in different formats, consolidating the data into a common format that is useful for analysis can be challenging. Data can't be copied directly from source tables into a common target table but will have to be manipulated in some way before it's stored.
  • Data definitions might not be consistent across multiple databases, and reconciling data that appears to be similar can be difficult. For example, the revenue in a sales database might be calculated according to one set of business rules but be subject to an entirely different set of business rules in a general ledger database.
  • Each database is probably structured and optimized for inserting data or for performing lookups. Even if you want to query just one database, running analytical queries usually requires the summarization of large quantities of data, which is an expensive operation in terms of database resources. Consequently, your queries can take a long time to execute and can create contention for resources with other applications performing insert, update or lookup operations.
  • Historical data is often archived rather than maintained indefinitely in a corporate database. If you want to look at trends over time—a common BI activity—your solution might need to be a repository for data that doesn't persist in the corporate database.
  • Some data required for analysis might not be available in the corporate database. It might be in flat files, spreadsheets, or unstructured data formats like Microsoft Word documents. More problematic is getting to the information people have stored on their local machine—or in their head.
  • Even if data is available, quality issues sometimes mean you can't use it directly from the source. You might need to download the data and clean it up before you can analyze it. Unless the data can be cleaned within the source, you'll need to cleanse the data manually each time you access it—making sure you apply the same rules every time. Furthermore, you can't be sure that everyone else using the same data is following the same rules for cleaning it.
To address these problems with data access, a BI solution often includes a database created exclusively to house the data used for analysis. Having such a database means you can avoid any resource contention problems between activities generating data and activities consuming that data. Going a step further, you can restructure the data so that queries summarizing data can run much faster. When you need to consolidate data from multiple data sources, you can centralize it and apply business logic to put the data into a common format with consistent meaning. You can also incorporate data that didn't come from a database, such as an XML document or a spreadsheet, into this central location. Another benefit of creating a separate database is that you can preserve historical data for as long as necessary after that data is purged from the source databases. Finally, you can automate the process of cleansing and enhancing the data for analysis, ensuring that the same rules are applied each time the data is accessed.

Support for Decision Making
A BI solution should do more than give you better access to data. It should specifically support your decision-making efforts. In general, a BI solution should help you assess and respond to business conditions, whether you require an all-encompassing view of the entire organization or a narrow perspective of a department, workgroup, or even a team of one. In fact, the ability to move quickly from a summarized view to a detailed view of data is an important capability in BI.
The goal of a BI solution is to let you spend your time analyzing the data and finding answers to questions rather than tracking down, consolidating, reformatting and reconciling the data itself. When you have enough quality time to analyze the data, you can usually spot problems early and take steps to stop negative trends from continuing. You can also use BI to discover correlations between seemingly unrelated data points and then adapt your strategies to turn your insights into dollars saved or dollars earned. Every decision you make each day as you do your job, whether you're solving a problem or planning for the future, translates directly or indirectly into a cost or a profit for your company.

BI in Action
Understanding BI on a theoretical level is good, but seeing it in action really helps you understand its benefits. To show you how BI works, the three other articles in this series describe the development of a BI solution for a fictional company called Adventure Works. At the end of this article, I'll explain how to obtain the sample database for Adventure Works, which is a fictional bicycle manufacturer that sells its products worldwide. It maintains a sales staff to sell its products wholesale to resellers such as small specialty bicycle shops or large warehouse-style shopping outlets. Adventure Works also sells products directly to individual customers through the Internet. The Adventure Works data provides lots of analysis opportunities that fit well into a BI solution.
Remember that a BI solution is intended to support decision making. With this in mind, let's consider the types of questions that Adventure Works needs to answer before making key decisions:
  • Which sales channel is more profitable? Adventure Works must decide whether to invest in adding more sales staff to develop relationships with more resellers or to expand its Internet sales presence. To help guide this decision, analysts need the ability to compare the sales performance over time between resellers and its Internet site. The sales performance data points (called measures) that analysts need to compare include sales dollars, order quantity and profitability. A positive trend in profitability is the most important measure because high sales measured in dollars or number of units sold don't benefit the company if these sales result in a net loss.
  • Is demand for certain products growing or declining? Adventure Works must match production levels to sales demand. If demand for some products is growing, Adventure Works must adjust its manufacturing processes to ensure more of these products are available for sale and thus increase sales. If demand for other products is declining, Adventure Works needs to reduce their production or possibly eliminate product lines to avoid an oversupply situation in which these products must be sold at a loss.
Even if you don't analyze sales data in your job, I encourage you to follow along as we build a BI solution for Adventure Works. You can apply the same design and development principles described in this series to your own data.

The Microsoft BI Stack
Now let's take a closer look at the technology architecture of BI. The Microsoft BI stack provides all the tools you need to build, manage and use a BI solution. SQL Server 2008 is the foundation of the stack as the data platform hosting the data mart or data warehouse. A data mart is a subject-specific data store. A data warehouse is an enterprise-wide collection of data containing multiple subjects. The line between data marts and data warehouses is blurry, but you don't need to worry about the distinctions. In this series, I use the term data mart. (Although this series of articles refers specifically to SQL Server 2008, you can also build a similar BI solution by using SQL Server 2005 and its BI components with little or no modification of the instructions provided.)
SQL Server 2008 includes three BI components: Integration Services (SSIS), Analysis Services (SSAS), and Reporting Services (SSRS). These components extend the data platform with data integration functionality, multidimensional database support and a data presentation layer, respectively. Figure 1 illustrates the relationship of these components to one another in a BI solution.
Figure 1 SQL Server 2008 Components in a BI Solution
After you design the physical structures of your data mart, you use SSIS to populate it with the data you extract from other data sources. SSIS provides the tools necessary to automate the processes for cleansing data, consolidating data from multiple sources and transforming the data into a structure well suited for analysis. You can schedule the periodic execution of these processes using SQL Server Agent. In his article on SSIS on p. 31, Derek Comingore explains how to develop extract, transform and load (ETL) processes for a BI solution.
Adding an SSAS database to your BI solution allows you to support more sophisticated, high-performing interactive queries. You use SSAS to copy your relational data into a multidimensional database structure called a cube. A well-designed cube optimizes data for ad hoc queries by adding indexes and the functional equivalent of summary tables (known as aggregations) to return query results that can be exponentially faster than a comparable query to a relational database. You can also embed complex calculation logic in the cube to simplify queries that would otherwise require hundreds of lines of Transact-SQL code to replicate when using a relational data source. Many front-end tools (called cube browsers) let you query a cube without writing a single line of code. In next month's issue, Scot Reagin will show you how to develop a cube as part of a BI solution.
Whether you're storing your data in a SQL Server data mart or an SSAS cube, you can add SSRS to your solution architecture to make the data available to users. SSRS is a reporting platform that includes tools to develop reports, to secure and manage published reports using a centralized administrative infrastructure and to support user access to reports. You can use an SSRS Web application or Microsoft Office SharePoint Server 2007 (MOSS) to view reports, use the subscription feature to receive reports via e-mail, or call the SSRS Web service in your own application to display your reports. The default view of a report displays in HTML format, but you can also export a report to other file types, such as PDF or Excel. Next month, I'll explain more about using SSRS in the data presentation layer of your BI solution.
The Microsoft BI stack also includes several Microsoft Office technologies that expand your options for the data presentation layer. Excel 2007 is a popular choice for supporting data analysis in BI solutions. You can access your SQL Server data mart or browse an SSAS cube directly from Excel (as shown in Figure 2) and explore data much more freely than you can when viewing an SSRS report.
Figure 2 Using Excel 2007 to Browse a Cube
In addition to using Excel to explore both relational and multidimensional data, you can use its data mining algorithms to uncover hidden patterns of information in your data or to detect anomalies in the data (which means you can fix problems before populating your data mart). A great way to get started with data mining is to download a free add-in for Excel from Microsoft and then use it to analyze data that you import into Excel from any source or to view the output of a data mining model created and stored on the SSAS server. An add-in for Visio 2007 is also available for sharing annotated data mining models. You can learn more about downloading and using these add-ins at "Data Mining Add-Ins for Office 2007."
An increasingly popular choice for sharing data is Excel Services, which is available in MOSS. Using Excel 2007, you first create a workbook containing a pivot table that uses an SSAS cube as its data source and you then publish the data connection and your workbook to Excel Services. Although you can publish workbooks that contain data from other types of data sources, one of the advantages of using SSAS, pivot tables and Excel Services together is the ability to use much of the same cube browsing functionality within a pure HTML interface that preserves the familiarity of Excel. Another advantage is the ability to centralize the administration and access of Excel workbooks. For more information about Excel Services, refer to "Excel Services Technical Overview."
Yet another advantage of using Excel Services for cube browsing is that you can embed pivot tables and pivot charts into a MOSS dashboard page using the Excel Web Access Web Part. A dashboard is a special SharePoint content type that allows you to present data from multiple sources on a single page using a variety of Web Parts. You can even add a filter to the dashboard page and connect it to all or some of the Web Parts to dynamically change the content on the page based on the filter selection. Figure 3 shows a sample MOSS dashboard. MOSS also includes a Web Part to display SSRS reports that are stored on the MOSS server (an optional SSRS configuration known as integrated mode), or you can use Web Parts that ship with SSRS to display reports stored on the report server (the default configuration known as native mode).
Figure 3 Using a MOSS Dashboard Page to Display Workbooks and Reports
For dashboards with even more functionality, you can use Microsoft Office PerformancePoint Server 2007 (PPS). You use PPS to develop scorecards and dashboards that you can deploy to MOSS. A scorecard is a report that compares actual performance to a defined target and shows the results using color-coded icons. You can display a PPS scorecard using a special type of Web Part in a standard MOSS dashboard or as part of a PPS dashboard. In the dashboard, you can add PPS reports to dashboard zones to present different views of the same data (as shown in Figure 4) or to show related data from different sources.
Figure 4 Using a PPS Dashboard to Display Data
As you can see, the Microsoft BI stack provides numerous options for the development, administration and implementation of your BI solution, but it by no means prevents you from using other options. The Microsoft BI stack from top to bottom is an extensible architecture into which you can plug your own custom applications or third-party applications when you need to support specific requirements.

An Approach to Solution Development
The best way to start learning about the Microsoft BI stack is to build a simple solution. The companion articles in this series show you how to build a solution by using SSIS, SSAS and SSRS. When you've finished the series, you'll have a general idea of how each of these components works. Don't feel compelled, however, to use each component in the stack for every BI solution you build.
As you put together your BI solution, plan to spend most of your time performing data preparation tasks, such as restructuring and cleansing. In BI terms, this set of tasks is called the extract, transform and load (ETL) process.
Before you start ETL development, you must carefully plan the design of your BI solution. In my experience, the development of a BI solution goes more smoothly when you have a specific business problem to solve and approach the design by first considering how people need to interact with information. By taking a user-centric approach, you can work your way backward through the applicable business processes to design a solution that properly retrieves and structures the data to support your business need. This recommendation might seem to be an obvious approach, but I've seen many people attempt to drive the solution development from the data available out to the users and wind up with a solution that never gets used.
After you come up with the initial design, you're ready to start developing. If your BI solution uses SSIS, SSAS and SSRS, you begin by creating and populating the BI solution's data structures using SSIS. Once the data is in place, you continue to the next step by building a cube. After your development is complete, you process the cube to load it with data. You then use SSRS to develop a report that queries the cube and displays the query results in a report. You'll see how to step through this process over the next three articles in this series. For your own projects, you should approach this process incrementally and iteratively to make sure the results of one step work satisfactorily in subsequent steps.

Getting Started
To complete the entire BI solution described in the remaining articles in this series, you need to install SQL Server 2008, including SSIS, SSAS and SSRS. If you have access to SQL Server, an SSAS server and a report server on your network, you need only install the development tools on your computer. For product installation instructions, see "How to: Install SQL Server 2008 (Setup) ." You can download the sample databases used to build the solution for Adventure Works from CodePlex. The first database, AdventureWorks2008, is representative of an online transactional processing (OLTP) database, which captures the transactions generated by business operations in the sales, production and human resources departments. You'll use this database as a source for the data mart that you build using SSIS. The second database, AdventureWorksDW2008, is a sample of data representing best practices in data warehouse design. You can use this database as a source for your SSAS cube if you decide to skip learning about SSIS and jump directly into cube development.

Next Steps
After you get familiar with the SQL Server BI components, you should find a simple project of your own to continue building your knowledge. You don't need a full-fledged data mart to get started, but you should try to structure your data using the principles described in the next article in this series. Once you start using BI, you'll probably never look at data quite the same way again.

Stacia Misner is a BI consultant, educator and author, as well as the founder and principal of Data Inspirations. She has 25 years of experience in the IT industry, with nine years focused on the Microsoft BI stack. Stacia has written several books about BI and SQL Server. Her latest book, Microsoft SQL Server 2008 Reporting Services Step by Step (Microsoft Press, 2009), was released earlier this year. She can be reached at smisner@datainspirations.com.


Business Intelligence
Building a Data Foundation for a BI Solution
Derek Comingore
Code download available at: SampleCode2009_08.exe (151 KB)
At a Glance:
  • Following the dimensional model to build a data mart
  • Developing ETL processes and data maps
  • Using Integration Services for ETL
  • Creating an Integration Services project in BIDS

Contents
Understanding Requirements
Using the Dimensional Model
Creating a Bus Matrix
Creating a Data Map
Building the Data Mart
Deploying the Data Mart
Developing ETL Processes
Creating an SSIS Project in BIDS
Creating Common Data Connections
Developing the ETL for Dimensions
Defining a Data Flow to Extract and Load
Developing Additional Packages
Developing the Internet Sales Fact Table Package
Final Steps
Data is the most important part of any business intelligence (BI) solution. As explained in Stacia Misner's, "Planning Your First Microsoft BI Solution," getting data into your BI solution and maintaining it once it's there involves several steps. BI professionals refer to these steps as extract, transform and load ( ETL) processes. Even if you don't plan to pursue a career focused on BI, you can still take advantage of ETL techniques and tools to manage the data you need to inform the day-to-day decisions you make in your job. In this article, I'll show you how to design and build a simple data mart to illustrate how you can use SQL Server 2008 Integration Services (SSIS) to perform ETL for your own BI solution.

Understanding Requirements
As with any IT project, the best way to start an ETL project is to understand the general requirements of the BI solution you want to build and then work your way back to the data to determine how best to support those requirements. The first article in the series set the stage for the BI solution the fictional company Adventure Works needs by describing the analytical requirements as several questions the company needs to answer. These questions highlight the need for Adventure Works to understand its product sales from different perspectives: profitability by distribution channel (resellers or the Internet), the change in demand for products over time, and the difference between actual sales and sales forecasts by product, salesperson, geographic region and reseller type. The answers to these questions will help Adventure Works decide which distribution channel the company should focus on to increase profits, how to adapt its manufacturing processes to best meet demand, and how changes in sales strategy might help the company meet its sales goals. You'll see how the data supports answering these business questions when you add SQL Server Reporting Services (SSRS) to the BI solution.
Before I start designing the data mart for Adventure Works with these requirements in mind, I want to model the information needs from a business perspective. In other words, a data mart's design should be based on the way the users will ask their questions rather than the way the data comes from the data sources.
You'll need the SQL Server 2008 Adventure Works OLTP sample database for the code samples found in this article.

Using the Dimensional Model
A data mart is typically built using a dimensional model design, which is a database schema well suited for analysis. (A great resource for learning about dimensional modeling is kimballgroup.com.) A dimensional model presents data in a way that's familiar to users, and it helps you build data structures that are optimized for querying high volumes of data. You can achieve this optimization by denormalizing the data, which helps the database engine rapidly select and efficiently aggregate a vast amount of data at query time. In the denormalized schema for the Adventure Works solution, I'll include two types of tables: dimension and fact. A dimension table stores information about business entities and objects, such as resellers or products. A fact table, which I'll use for the numerical sales data I need to aggregate, contains numerical measures and keys relating facts to the dimension tables. I'll explain more about fact tables later in this article.
You can implement the tables of a dimensional model in two types of schemas: star and snowflake. In simple terms, a star schema uses one table for each dimension, which a query in turn relates to a fact table with a single join. A snowflake schema uses two or more tables for each dimension and consequently requires more joins in a query to see all the data. This collection of cascading joins means that queries often run more slowly in a snowflake schema than in a star schema. For the purposes of this article, I'll keep the design simple and use a star schema.

Creating a Bus Matrix
As part of the dimensional modeling process, I create a bus matrix to help identify the dimensions associated with sales, the focus of the BI solution for Adventure Works. Remember that Adventure Works has two sales channels: wholesale sales to resellers and individual sales through the Internet. I also use the bus matrix to identify the relationship of each dimension to one or both types of sales. Figure 1 shows my sample bus matrix for Adventure Works Sales.
Figure 1 Bus Matrix for Adventure Works Sales
Adventure Works Sales Bus Matrix | Date | Product | Customer | Promotion | Geography | Reseller | Sales Territory | Employee | Currency
Internet Sales | X | X | X | X |  |  | X |  | X
Reseller Sales | X | X |  | X |  | X | X | X | X
My next step is to determine the measures for the solution. Measures are the numerical values that are required for analysis. They can come directly from the source, such as sales dollars or product costs, or be derived through a calculation, such as multiplying a quantity by a dollar amount for an extended sales amount. I also need to decide which attributes to include with each dimension. Attributes are the individual elements in a dimension (corresponding to columns in a table) that you can use to group or filter data for analysis, such as Country in the Sales Territory dimension or Year in the Date dimension. I won't detail every identified measure or dimensional attribute in this article—just realize that the identification process needs to take place.

Creating a Data Map
Before creating physical tables of the data mart, I need to do some additional planning. Specifically, I need to craft a data map document to map each destination column in the data mart schema to the columns in the Adventure Works OLTP source system (the AdventureWorks2008 database that you can download and install as described in Stacia Misner's article on p. 31). You can use various applications to create a data map. The format isn't as important as the content. I like to develop data maps in Microsoft Office Excel. Figure 2 shows the DimProduct tab I created in my data map. I also created DimCustomer and FactInternetSales data maps. Each sheet in the workbook represents one of the tables in my data mart. On each sheet, I simply have two columns: one for the source column and one for the destination column.
Figure 2 DimProduct Data Mapping Tab
Each dimension table (except for the Date dimension) has a primary key known as a surrogate key (usually an identity column). One benefit of using surrogate keys is that you can combine data from multiple systems without the risk of duplicate keys.
The dimension tables also have an alternate key column. These alternate keys represent natural keys, sometimes called business keys. These are the source system's identifiers. For example, the CustomerAlternateKey column in the Customer dimension maps to the Adventure Works OLTP database field AccountNumber in the Sales.Customer table. By storing these keys in the dimension table, I have a way to match up records already in the dimension with records extracted from the source whenever I run an ETL process for each dimension.
Almost every data mart includes a Date dimension because business analysis often compares changes in measures by date, week, month, quarter or year. The Date dimension almost never comes from a source system, so the reasons for using a SQL Server IDENTITY–based key don't apply. Instead, I'll use what is called a smart key with a format of YYYYMMDD stored as a SQL Server Integer column. A smart key is a key that is generated from logic or scripts as opposed to being an auto-incrementing key such as an IDENTITY column in SQL Server.
Keep in mind that the Date dimension doesn't usually map to a source table. Instead, I'll use a script to generate the data to load records into the table.
Because the ETL processes required for my small schema are fairly simple, my data map is fine as is. In a real-world project, I would annotate the data map to point out when complex transformations are required.

Building the Data Mart
Now that logical modeling is complete, I need to create the physical tables that the ETL processes will load and a host database for these tables. I'll use a basic T-SQL script to create both my database and its associated dimension and fact tables. You can find the entire T-SQL script in the accompanying download for the sample BI solution at (2009 Code Downloads).
For the purposes of this article, I build only a subset of the entire Sales data mart schema so I can cover the entire ETL process in SSIS. In the smaller version of the schema, I include only the OrderQuantity and SalesAmount measures for the Internet Sales fact table. Additionally, my smaller schema includes a simplified version of the Customer, Product and Date dimension tables.

Deploying the Data Mart
To deploy the data mart, I simply need to execute the T-SQL I wrote earlier to instantiate the new tables on a SQL Server instance. To execute the T-SQL, I start SQL Server Management Studio (SSMS) by clicking Start\All Programs\Microsoft SQL Server 2008\SQL Server Management Studio. Once SSMS is open, I type the name of my designated SQL Server instance and then click Connect using Windows Authentication in the connection dialog box. Use SQL Server Management Studio to open the file TECHNET_AW2008SalesDataMart.sql and execute the script.

Developing ETL Processes
Designing and developing ETL processes is the next step in building a BI solution. To review, ETL includes all the technological processes by which data is extracted from data sources, transformed, and then loaded into a destination repository. Commonly, ETL processes within BI solutions extract data from flat files and OLTP operational databases, alter the data to fit a dimensional model (e.g., a star schema), and then load the resulting data into a data mart.

Creating an SSIS Project in BIDS
The first step in developing an ETL process is to create a new project in Business Intelligence Development Studio ( BIDS). BIDS comes with SQL Server 2008 and installs when you select the Workstation Components option during the installation process. BIDS includes project templates for SSIS, SSAS and SSRS. It also supports source control integration, just as Visual Studio does.
To start BIDS, go to Start\Programs\Microsoft SQL Server 2008\Business Intelligence Development Studio and select File\New Project. You should see the New Project template shown in Figure 3.
Figure 3 New Project Template in BIDS 2008
Select Integration Services Project in the Templates pane, type ssis_TECHNET_AW2008 in the Name text box, and then click OK. BIDS should now display an open SSIS project.

Creating Common Data Connections
Another nice feature in SSIS 2008 is the ability to create data source connections outside of individual packages. You can define a data source connection once and then reference it in one or more SSIS packages within the solution. To learn more about creating BIDS data sources, refer to "How to: Define a Data Source Using the Data Source Wizard (Analysis Services)".
Create two new data source connections: one for the TECHNET_AW2008SalesDataMart database and another for the AdventureWorks2008 OLTP database. Name the data source connections AW_DM.ds and AW_OLTP.ds, respectively.

Developing the ETL for Dimensions
The ETL to load the Product dimension is very simple. I need to extract the data from the Adventure Works Production.Product table and load the data into the TECHNET_AW2008SalesDataMart database. First, I need to rename the default package that BIDS created for my SSIS project. (A package is a container for all the steps in the workflow that SSIS will execute.) Right-click the default package in Solution Explorer, and select Rename. Type DIM_PRODUCT.dtsx and then press Enter.
Next, I need to create local package connection managers using the prebuilt data sources. Create two new connection managers referencing the data sources built earlier.

Defining a Data Flow to Extract and Load
SSIS includes a data flow task that encapsulates everything I need to implement the ETL for a simple dimension. I just drag a data flow task from the Toolbox onto the Control Flow designer's surface and rename the task to EL (for extract and load). Right-click the Data Flow task in the designer, and select Edit. BIDS now displays the Data Flow designer.
The extract portion of the Product dimension package needs to query the AdventureWorks2008 Production.Product table. To set up this task, I drag an OLE DB Source component from the Toolbox onto the Data Flow designer surface and then rename the OLE DB Source component to AW_OLTP.
Next, I define the load portion of my package to load into the data mart. I simply drag a new instance of the OLE DB Destination component onto the Data Flow designer surface and rename it AW_OLTP. I then click the OLE DB Source (AW_OLTP) component and drag the green arrow that appears on the OLE DB Source to the AW_DM OLE DB Destination component to connect the two components.
At this point, I've added the necessary components to the data flow, but I still need to configure each component so that SSIS knows how I want to extract and load the data. Right-click the AW_DM OLE DB Destination component, and select Edit. With the OLE DB Destination Editor open, I make sure AW_DM is selected as the OLE DB connection manager. Then I expand the Name of the table drop-down and select the dbo.DimProduct table. Finally, I click the Mappings tab to confirm that the mappings are correct. I click OK to confirm the mappings. This process is much easier when you have a data map ready for reference, especially if you're working with large tables. The Product dimension's ETL package is now complete.
You can execute the package inside BIDS easily. To test the Product dimension package, open the package and press F5.

Developing Additional Packages
I create the Customer Dimension package in the same way I did the Product package. I won't reiterate the steps you need to follow to create this new package. You should try to produce it on your own. Notice that this package uses an XML data-typed column ( Person.Person.Demographics) in the source, which requires you to parse out the individual demographic-related attributes. To parse individual values from a SQL Server XML data-typed column, you can leverage an XQuery with the XML data type's native Value ( ) method. Name the finished package DIM_CUSTOMER.dtsx.
Developing an SSIS package for the Date dimension is optional. Because this dimension doesn't usually have source data, the easiest way to load it is by using a basic T-SQL script. You can find the script I used in the completed solution.

Developing the Internet Sales Fact Table Package
The Internet Sales fact table package queries for all Internet Sales and returns those sales broken down by Product, Customer and Date (i.e., order date). Unlike a dimension package, a fact table package requires an additional step to look up the surrogate and smart keys in the corresponding dimension tables prior to loading the data into the fact table. You can create a new package and name it FACT_INTERNET_SALES.dtsx.
The extract portion of the package needs to query the AdventureWorks2008 OLTP database using the T-SQL code shown in Figure 4.
Figure 4 T-SQL Code for Internet Sales by Product, Date and Customer
SELECT
       P.ProductID
       ,CONVERT(INT,CONVERT( CHAR(4), YEAR(H.OrderDate) ) + RIGHT('0'+ CONVERT(VARCHAR(3),
            MONTH(H.OrderDate) ),2) + RIGHT('0'+ CONVERT( VARCHAR(3),
DAY(H.OrderDate) ),2)) AS OrderDateKey
       ,C.AccountNumber
       ,SUM(D.OrderQty) AS OrderQuantity
       ,SUM(D.LineTotal) AS SalesAmount
FROM
       [Sales].[SalesOrderDetail] D
INNER JOIN
       [Sales].[SalesOrderHeader] H ON (D.SalesOrderID = H.SalesOrderID)
INNER JOIN
       [Production].[Product] P ON (D.ProductID = P.ProductID)
INNER JOIN
       [Sales].[Customer] C ON (H.CustomerID = C.CustomerID)
WHERE
        H.OnlineOrderFlag = 1
GROUP BY
        P.ProductID
        ,H.OrderDate
        ,C.AccountNumber
Create a new data flow task in the package's control flow surface. Open the Data Flow designer, and create an OLE DB Source component. Name the component AW_OLTP, and use the query in Figure 4 as its source. This query produces an aggregation (sum) for the OrderQuantity and SalesAmount measures found in the Adventure Works Sales tables.
Now you need to configure a lookup transformation. Drag two new instances of the Lookup Transformation component from the Toolbox to the Data Flow designer surface and name them Product and Customer. Configure the first one ( Product) to look up the ProductKey in the Product dimension table by joining the AlternateKey of the dimension table to the incoming ProductID field from the AW_OLTP source query.
Configure the second one (Customer) to look up the CustomerKey in the Customer dimension table by joining the AlternateKey of the dimension table to the incoming AccountNumber field from the AW_OLTP source query.

Final Steps
The final step is to load the data into the FactInternetSales fact table, replacing the natural keys for each dimension with the surrogate keys the lookup transformation found. Drag over a new instance of the OLE DB Destination component, and name it AW_DM. Edit the OLE DB Destination component, and select the AW_DM connection manager. Next, select the dbo.FactInternetSales table and click the Mappings tab. Ensure that the mappings look like those in Figure 5. Click OK to complete the package's logic.
Figure 5 OLE DB Destination Mappings for the Fact Internet Sales Fact Table
To test the Internet Sales Fact package, open the package in BIDS and press F5.
You now understand the basics of dimensional modeling and building ETL-designed packages with SSIS. In the third article in this series, you learn how to use a populated data mart to create dimensions and cubes for an SSAS database. Once you've built a cube, you can then develop an SSIS package to update these objects continuously in the SSAS database each time new data is added to the data mart. SSIS can even prepare data for display in an SSRS report when the report requirements can't be met with a single query. As you can see, SSIS can do a lot more to help you manage your BI solution than just ETL processing.

Derek Comingore is a Senior Architect with ComFrame. As a SQL Server MVP and a certified professional in several Microsoft technologies, including SQL Server BI, he speaks at local and national SQL Server user groups and is a published author on SQL Server topics. Derek focuses on building and delivering enterprise-class data warehousing and BI solutions with SQL Server along with the other Microsoft BI software products. You can reach Derek at dcomingore@comframe.com.


Web Infrastructure
ASP.NET Web Cache Spurs Performance and Scalability
Iqbal Khan
At a Glance:
  • Dramatic bottleneck reduction
  • Static and dynamic Web caching
  • Must-have features: expirations, database dependency, PDF partial content, more
  • Special benefits for global organizations
  • Web-cache server clusters
  • Free and commercial solutions

The Problem: ASP.NET Bottlenecks
The Solution: ASP.NET Web Cache
Must-Have Features in Web Cache
Expirations
Reload Pages on Expirations
Partial-Page Caching
Database Dependency
File Dependency
PDF Partial Content
ViewState Caching
Gzip Compression
Scalable and Dynamic Web-Cache Cluster
Geographical Cache Distribution
Stay Away from the Database
Guidelines
Applications based on ASP.NET, Microsoft's Web-application framework, are making greater inroads into the enterprise. At the same time, bottlenecks resulting from growing numbers of users and transactions continue to prompt IT professional to call for improved performance and scalability.

The Problem: ASP.NET Bottlenecks
Bottlenecks can occur in ASP.NET applications for a variety of reasons. The most obvious: Data-storage technology isn't as scalable as Web-applications architecture. Any place in an ASP.NET application that deals with data storage or data access immediately becomes a logjam when you try to scale your application. Two areas where this happens are Session State storage and application data from a relational or mainframe database (see Figure 1).
Figure 1 Common areas of performance bottlenecks in ASP.NET applications.
Another bottleneck occurs if your ASP.NET application is making service- oriented architecture (SOA) calls to Web services. Here, the slowdown happens because the Web services have the same issues as your ASP.NET application (namely, in data storage and access). Chances are that a Web-services farm is being shared across multiple applications and, therefore, being stressed much more than any one ASP.NET application, creating the scalability bottleneck.
Bottlenecks can also occur between the user's browser and the ASP.NET Web farm. These clogs are related to the fact that ASP.NET pages have to be executed repeatedly at time involving intensive CPU processing. This process also involves sending heavy data elements (images, documents, etc.) to the user again and again.
In a previous TechNet Magazine article, I discussed ASP.NET performance and scalability issues, focusing on Session State and application data (see "Providing Scalability for ASP.NET Applications," June 2009). In that article, I covered how those problems occur, including the reasons that the ASP.NET Session State becomes a logjam as a Web farm grows. I discussed the fact that distributed in-memory cache is a superior alternative to Microsoft's existing storage option for ASP.NET Session State. I described how application data coming from a database can cause scalability bottlenecks. I also detailed how distributed caching resolves those ASP.NET Session State storage bottlenecks with the help of different caching topologies that each offer different features, but all address scalability and assure 100 percent uptime.
Finally, I generically profiled the different distributed caching options available in the market. Some options are free with limited capabilities, while others are more robust and feature-rich commercial products. However, for best performance and scalability, it's prudent to consider top-of-the-line commercial products.

The Solution: ASP.NET Web Cache
With that problem definition as a backdrop, this article's primary focus is on performance and scalability bottlenecks between the user's browser and the Web server or Web farm. Ideally, you want to reduce the number of times that the Web page itself executes. If the Web page is executed once, and its output isn't changing even briefly, then why execute it again? Why not just cache the output? Then the next time a user can just fetch that page's output directly from the cache, rather than re-executing it. Re-executing the page involves the CPU, consumes memory and uses other Web-server resources—and then, of course, there are the scalability bottleneck problems from data storage.
An ASP.NET page's output can be cached on the Web server itself; Microsoft provides the ASP.NET Output Cache mechanism to allow you to do that, and it works well. However, I do have two concerns about the ASP.NET Output Cache.
First, it requires you to change your ASP.NET code and, at the minimum, put tags on these pages to indicate that you want to cache their output. That's not always possible for an IT person who is managing both internally developed and externally purchased ASP.NET applications. Also, anytime you change your application code, you must go through quality-assurance efforts to make sure that nothing broke in the process. This increases the cost of incorporating caching.
Second, ASP.NET Output Cache caches page output local to each Web server and, in fact, inside each ASP.NET worker process. This creates major issues in terms of consistency and scalability if you have a Web garden (meaning multiple worker processes on a Web server) or a load-balanced Web farm. In these cases, you have multiple disconnected copies of the cache; for high traffic Web sites, this could become a management nightmare. In ASP.NET 4.0, Microsoft is promising an extensible caching framework that would allow you to keep the cache in a separate process or tier by incorporating a third-party distributed cache.
In light of this development, I prefer to cache page output not on the Web server, but in a separate Web-cache server that sits between users and your Web farm (kind of like a reverse proxy; see Figure 2). If a lot of user requests don't make it to the Web farm and are served from a cache in between instead, you're reducing the load on your Web farm and increasing the Web farm's scalability. Fetching output of a Web page from the cache and returning it to the user requires considerably fewer resources than actually executing that Web page. That's because the cache is a high-performance and scalable in-memory storage, unlike any disk storage that can have problems with performance and scalability.
Figure 2 Web-cache servers sitting between users and Web servers.
In this context, I'll discuss how caching the output of Web pages can resolve bottlenecks and considerably boost scalability and performance. A Web-cache server can play an important role in achieving these goals.
A Web-cache server intercepts http requests that users make for Web pages and checks to see whether those pages' output already exists in the cache. If it does, the Web-cache server returns that output to the user. The user request never even makes it to the Web servers. Because it's being fetched from the cache, the return is extremely fast. And because it's in memory and the page doesn't have to be re-executed, a lot of CPU processing is saved as well.
A Web-cache server can work with any back-end Web-technology platform. As long as they're all talking HTML and JavaScript, it doesn't matter whether the Web application is developed in Java, .NET, PHP or some other language. However, a Web-cache server can include some platform-specific features, so features differ from solution to solution.
There are two types of Web-caching solutions. One primarily caches fairly static data, meaning that every page being cached is presumed to be either totally static or to change at predictable intervals. The entire page is cached for a certain amount of time, after which it expires and is removed from the cache. The other type is for dynamic caching, which is more appropriate for dynamic Web sites or Web applications.
If your Web site or Web application is dynamic, where data changes frequently and unpredictably, then you should opt for dynamic caching. These days, most Web sites change at least some of their content frequently. You should be able to cache something for as little as 15 to 30 seconds, but normally for anywhere from a few minutes to a few hours—and in some cases, for days or even weeks.
Web caching offers an important benefit to large worldwide corporations: the ability to geographically disperse Web-caching servers. In a global organization, your main Web site application may be hosted in New York, but you have users all over the world—in San Francisco and London and Tokyo and Sydney and Dubai. Now it's relatively easy to have Web-caching servers geographically located in each of these areas. All requests coming from Europe will go through the European Web-cache server before they hit your Web site. That way, they don't have to cross the Atlantic Ocean and incur that 100-millisecond latency that every packet has to go through. They can basically get a copy of it from a nearby cache.
Let's take another example. You have a server in Dubai serving the Middle East and South Asia regions, and you've got hundreds of thousands of users hitting your Web site simultaneously. With Web caching, traffic to your main data center in New York will drop significantly. Not all the traffic is stopped because you don't cache everything, and whatever you are caching is also removed from the cache (that is, invalidated) at times. But depending on the nature of your application, you've probably reduced the traffic by 30 to 50 percent.

Must-Have Features in Web Cache
Over time, Web sites have transformed from only showing static content to becoming fully interactive Web applications with dynamic data that changes frequently. That means that, today, a Web cache must cater to all requirements of a dynamic Web site. The goal is to cache as much of the Web-site content as possible, while at the same time ensuring that the cached content is correct and not stale or out-of-sync with the underlying data in the database. To handle all that, the Web cache needs certain features to avoid data-integrity problems while boosting performance and scalability.
A typical Web-cache solution includes the following features.

Expirations
Expiration capability allows you to specify rules about which Web pages to cache and for how long. And it allows you to expire pages on either absolute or sliding time. Absolute time means you are going to expire something at a certain time, whether it's midnight today or 10 minutes from now. Sliding time depends on whether a certain page is constantly being accessed. If a page isn't being accessed—if it's not being used at all—you may want to expire it. An idle time would expire a page if it doesn't get used for more than a certain amount of time—for example, 10 or 20 minutes.
Absolute-time expiration is probably the most important time of expiration for Web pages. Web-caching products allow you to specify rules and choose which URL patterns (known as "URI") to cache. Web caching allows you to specify that certain URLs should never be cached. It allows you to specify certain types of URLs to cache for a certain lengths of time.
It should also allow you to specify when to expire various URLs based on information provided in the HTTP headers that comes with the URL output. Every page's HTTP header can contain information about how long it can be cached, when to expire it and when was it last changed. Those are the items Web cache can check to monitor whether a page's content has been changed and whether it needs to upload new content into the cache. Without this feature, you don't have a way to invalidate and remove stale data.

Reload Pages on Expirations
Another important feature is being able to automatically reload a page when it expires for any reason, whether due to absolute or idle-time expiration. For instance, you might say: "This page is good for the next 20 minutes." After 20 minutes, the Web cache automatically re-fetches that page so that it always has the latest copy.
This way, you don't wait for the next request from the user for that page. That's because when the request comes, you don't want the user to go through the delay for fetching that page again from the Web farm. Because you can fetch it in the background, it may take you 5 to 10 seconds to fetch it from the Web server by executing it first. But that's fine, because by the time the user gets it, you'll be able to return it with a sub-second response time, depending on how far away the user is.

Partial-Page Caching
Some of the pages that show up in a browser contain multiple items that each have their own URLs. So for a page to be rendered, the Web browser must call multiple URLs. This achieves the same result as a partial page—but it's not called "partial page" because on the server side, each URL represents a separate page and each page is cached independently and for a different duration, depending on the nature of its content.
In other situations, a single Web page is developed in such a way that its content is divided up into sections. Some sections are static and don't change; some portions are dynamic, with each portion changing after a different interval. In effect, partial-page caching caches page sections based on whether they're static or dynamic and for how long they should be cached, rather than just caching the entire page.
ASP.NET allows you to do partial-page caching through two means: control caching and post-cache substitution. Both require you to modify the particular ASP.NET pages where you want to do this caching. That may not be possible if you don't have your own development resources and you didn't develop this ASP.NET application in-house. Nonetheless, a description of each type of partial-page caching in ASP.NET follows.
You can do partial-page caching in ASP.NET by creating user controls to contain the cached content and then marking the user controls "cacheable." This allows you to cache some parts of the page as user controls so that when the page is re-executed, these portions are simply fetched from the cache and not re-executed. Only those parts of the page that aren't designated as user controls and are not marked "cacheable are re-executed."
With post-cache substitution, the page is cached, but some portions or fragments inside it are marked "dynamic" or "non-cacheable." Then, when this page is re-executed, only the dynamic or non-cacheable portions are actually executed. The rest of the page is obtained from the cache, and both cached and dynamic portions are combined to return the output of the page.
Interestingly, partial-page caching can only be performed on the Web server. For that reason, some programming is necessary. In the case of ASP.NET, partial-page caching is done by programming in the ASP.NET pages to determine what—and what not—to cache. But this caching is performed on the Web server itself and still executes the ASP.NET page, at least portions of it.
In ASP.NET, partial-page caching is implemented based on Microsoft's specification, but on the Java platform, a partial-page caching standard called Edge Service Includes (ESI) has emerged. ESI defines a simple XML-based markup language that defines cacheable and non-cacheable Web-page components that can be aggregated, assembled and delivered at the network edge, whether it's in a content-delivery network, an end user's browser or a Web-cache server acting as a reverse proxy between users' browsers and the Web server.
So, partial-page caching is highly platform-specific—and, of course, for ASP.NET, you must use Microsoft's approach.

Database Dependency
Another key feature is database dependency, which allows a page output to be invalidated from the cache when corresponding data in the database changes. The pages' output is often generated based on data in the database. The data they are depicting comes from one or more rows in one or more database tables. Therefore, if those rows change, then these pages must be removed from the cache. For that reason, database synchronization is an important feature. This means that when data changes in the database, these pages' output should be invalidated and should be removed from the cache. This capability allows you to automatically determine when certain pages should be removed from the cache.
One way to create this dependency is by specifying an SQL "select" statement (or a stored procedure call containing a select statement) that corresponds to the page in question, then mapping some of the page GET/POST parameters with parameters in the SQL statement. At run-time, the Web page parameters are used to execute the SQL statement and a SqlCacheDependency is created against a SQL Server 2005/2008 or Oracle 10g R2 or later database. Then when the corresponding row in the database changes, the database server fires a .NET event that's captured by the Web-cache server. Then the corresponding Web page output is removed from the cache (see Figure 3).
Figure 3 Page removed from Web cache if database row changes.

File Dependency
Another key feature is file dependency. You may associate a page output with a file in the system with the instructions: "If this file is ever updated or removed, please invalidate this page." Then the Web cache monitors that file, which is kept in a shared folder. If that file is ever updated or removed, the Web cache automatically invalidates the corresponding URL from the cache. This enables you to update that file from anywhere in your system, whether it involves another application or even a trigger in the database server based on when some related data changes. For example, if your data is stored on a mainframe rather than in SQL 2005/2008 or Oracle databases, you could use a file dependency to invalidate a cache page.
All this allows you to notify the cache when to invalidate if certain things happen in your data store or in your environment, which aren't directly accessible by the Web cache.

PDF Partial Content
Many people now access PDF files online. The common usage pattern is that they read one page at a time, going through the entire PDF file page by page. Few people download an entire PDF and then read it locally; most read it in their browsers. When they're viewing the information that way, they typically don't read the entire document; they abandon it after getting through some portion of it.
So serving the entire PDF is often a waste of resources; in fact could be the differentiating factor between acceptable and unacceptable performance during peak hours. For that reason, PDF Partial Content handling is an important feature in a Web cache. It reduces the bandwidth load on the Web server and improves overall scalability.

ViewState Caching
Among the most important features in ASP.NET is the ability to declare controls that run on the server and post-back to the same page. Prior to ASP.NET, in classic ASP, you ended up creating multiple pages to handle different operations that, logically, belonged to one page—for example, loading data, saving data or taking other actions. But in ASP.NET, you no longer have to do that. Form fields and other controls can now be declared to run on the server and the server simply posts the page back to itself.
To handle these post-backs, a ViewState is created that remembers the identity of the controls and any dynamic information assigned to these controls. In essence, ViewState preserves the state of a page's controls across multiple posts.
ViewState is important for ASP.NET applications. Essentially, it's information sent by the Web server to the browser so that the browser can send the same information back to the server the next time user does a post for the same page.
For example, you may load a customer on the customer.aspx page. Then, you make some changes in the customer data and click "Save." The Save button again calls customer.aspx, but also sends the ViewState so that the customer.aspx knows how to handle this post-back. The ViewState contains the information that customer.aspx had sent to the browser, and the post-back contains the new data that the user has perhaps modified. That allows customer.aspx to determine what changed and what to do about it. This is only a simple example; ViewState can also contain other dynamically created data for each control on the page.
Although ViewState is highly useful in ASP.NET, it also carries cost of transferring data back and forth from a Web server to the user's browser. This cost could have a performance and scalability impact during peak loads of the application and when the users are far from the Web server and across a slow Internet connection.
But the ViewState often doesn't have to travel all the way to the browser if it can be cached in the Web cache. In that case, only a tag or a unique ID could be sent to the browser so that if the browser comes back, the Web cache would re-insert a ViewState and give it back to the Web server based on the unique ID. All a Web server requires is that the next time the browser sends the request, it contains the ViewState from the previous time. It doesn't matter whether the ViewState reaches the browser because the browser never uses that information—only the Web server does.
In technical terms, a ViewState is used for post-back, so if you ever do post-backs, you'll need a ViewState, and a Web cache can cache a ViewState. That also saves considerable bandwidth, because a ViewState could be just a few kilobytes in size.

Gzip Compression
These days, most browsers can uncompress gzip-compressed content. But not all Web servers are configured to automatically compress Web pages' output. And even if compression is turned on, it consumes a lot of unnecessary CPU on each Web server, which is already being stressed by high traffic. However, if the compression is done by an intermediary proxy Web-cache server, it becomes more scalable.
It's easiest to compress static data that never changes. However, most pages in an ASP.NET application are dynamic and their content should only be compressed if the page output isn't changing every time. Otherwise, compression would put an unreasonable load on the CPU, perhaps dwarfing the gains in bandwidth usage. But because a Web cache is already intelligent about figuring out which dynamic pages output is cacheable (meaning that it doesn't change for at least a brief period of time), it's also able to automatically compress any cache pages. Then these cached pages are served to the clients multiple times in compressed form and considerably reduced bandwidth usage.
A typical gzip compression reduces the content by almost 80 percent, unless the content is already compressed (for example, PDF, JPEG, TIFF, etc.). A good Web cache should allow the user to specify which URLs to compress and which to ignore for compression.

Scalable and Dynamic Web-Cache Cluster
A Web cache is a server that sits in front of an ASP.NET Web farm (almost like an appliance). Today, ASP.NET Web farms can grow from two servers to 100-plus servers with a load balancer. Therefore, a single Web-cache server is unable to handle the load of a growing Web farm. A good rule of thumb is that for every five Web servers in the farm, you should have one Web cache server.
Having multiple Web-cache servers not only improves scalability, but also allows the Web cache to replicate so that if any one server goes down or is taken down, the cache isn't lost and there's no performance degradation (see Figure 4).
Figure 4 Dynamic Web-cache cluster (add or remove servers at runtime).
A good Web-cache server should be able to build a cluster of Web-caching servers to handle reliability through replication and scalability through multiple servers. However many Web caching-servers are involved, it's still considered one logical Web cache. Even if there are multiple copies of the cache in the cluster, they're always kept synchronized. In short, having a cluster of Web-caching servers allows you to scale while keeping the logical correctness of the cache.
Once you have a cache cluster, a good Web cache should allow you to specify different cache-storing options, also known as caching topologies. The main topologies include Replicated Cache, Partitioned Cache and Client Cache.
Replicated Cache means the entire cache is copied to each cache server in the cluster. As a result, each cache server has the entire cache. All "get" operations are always local to that box and, as a result, are very fast. However, updates are made to multiple cache servers, and if there are more than two such servers in the cluster, then the update cost grows.
On the other hand, Partitioned Cache breaks up (or partitions) the cache, storing each partition on a different cache server. The number of partitions equals the number of cache servers. This allows you to increase cache storage capacity by adding more cache servers, which you can't do in Replicated Cache. Partition Cache also allows you to create backups of each partition on a different cache server so that if any of those servers goes down, you don't lose the cache. Partition Cache is the most scalable caching topology for both storage and transactions per second.
Finally, Client Cache is a topology that is combined either with Partitioned or Replicated Cache (especially if the actual cache is hosted on a server other than the Web-cache server). Client Cache keeps a small subset of the cache within the client process so that the frequently used data is always available close by. What's kept in the Client Cache depends on what that client recently fetched. You can specify a maximum size for the Client Cache so when it reaches that size, it starts to evict (or remove) older items to make room for new ones. That way, the Client Cache never consumes more memory than what you have determined is feasible.
If you use multiple Web-cache servers without building a cache cluster, you'll end up with multiple disconnected copies of the cache. That outcome may be acceptable if your Web site is fairly static, but for dynamic Web sites and ASP.NET applications, that may cause data-integrity problems. Additionally, you'll end up sending the user request to the Web server even though another Web Cache server has the output of this page in the cache, thereby increasing the load on the ASP.NET Web farm.

Geographical Cache Distribution
Many ASP.NET application Web sites today have users worldwide even though the Web site itself is hosted in a single location. It's often impossible for the Web site to be located in multiple geographical locations because of the complexity and the cost of its infrastructure. For example, an ASP.NET Web farm also has to have a database server in the same location; database servers aren't usually ideal for geographical distribution when they involve highly transactional applications. As a result, end users experience slow performance because of wide area network (WAN) latency.
One way to remedy that problem is to place Web-cache servers in each geographical location, then route all the traffic from that area through the nearest Web-cache server (see Figure 5). Then if a Web-cache server doesn't have a page already cached, it directly sends the request to the main data center, where another set of Web-cache servers is located.
Figure 5 Web-cache server in geographically distributed environment.
So a good Web cache should have the ability to create a hierarchy of servers so that each Web cache knows where to route the request, even if it doesn't have the page cached.

Stay Away from the Database
As discussed in my June 2009 article, data doesn't change every time; it stays constant. Unfortunately, without distributed caching, you're going through unnecessary cycles to reproduce that same data over and over. But you don't have to go to the database all the time. It's the same data, so why go there? Why not take it from the cache?
Here is an example to highlight this issue. An airline's main web page isn't changing much; it can be cached for a long time. A customer visits to search for flights. The information is constantly shifting because as other users book seats, the information changes on the back end.
If the customer is searching for, say, a flight from New York to San Francisco, seat availability determines the results. Seat availability is based on the number of people already booked, with bookings happening constantly. A harried businessperson may complicate matters by inputting incorrect information or making multiple entries to assure a seat assignment.
The user receives results indicating that a particular flight is available, with that information changing in the cache every five minutes. But once the customer actually wants to reserve a seat, then a real-time check is performed in the database. That's because for every 1,000 people who check for flight availability, probably only about 10 actually make a reservation. It's fine to show flight availability to all those visitors, even though the information provided may not be entirely correct. In such situations, you can cache that page even if it's highly dynamic.
But you can cache it with the idea that it's acceptable to provide information with some risk that it's not completely accurate. However, on the reservations page in the database, it's critical to ensure that all information is accurate and up to date. The point here is that every application has common information that can be cached and a Web-site user doesn't have to be sent to the airline's database every time.
The easiest stage is when you want to cache all the images. You don't change your company logo or your president's picture or standard documents that you've got available for people to read. But then there are other portions that are more dynamic. That's where you can specify rules and say, for example, "I want to cache this page for this long."
With other pages, you might say, "I can't tell how long it's going to be cached, so I want to tie it to the database. If this row in this table changes, I want this page to be invalidated." That means removing it from the cache and reloading it, making a fresh copy. Every page category is different. As long as a Web cache allows you to determine how you want to cache pages, you're creating a situation in which the more you cache, the less you go to the Web farm.

Guidelines
In most cases, you can achieve significant benefits by deploying Web cache. If you've only got one Web server, chances are you don't have enough traffic to experience these types of problems. But if you got 1,000 or more users, you probably already have a load-balanced Web farm. In that case, you're a candidate for exploring ways to optimize performance and scalability.
Plan ahead. Don't wait for problems to occur because once they do, you'll be in panic mode. The best time is when you aren't facing any major issues—but at that point, you must be able to convince top management about why you require funding for such a project. One good way to accomplish that goal: Ask management what it might cost your business if your Web site provided painfully slow response times during peak hours (upward of 30 to 60 seconds per click). And what if your Web site went down for 30 to 60 minutes? Considering those questions can help convince your organization's executives about the need for a Web cache.
As previously noted, both free and commercially Web-caching options are available. With ASP.NET's growing popularity among developers, commercial Web-caching options that support .NET are now emerging. However, at this writing, most Web-caching products support Java and PHP. Most are software-based, but some hardware-appliance options are available as well.
As you consider your choices, stay focused on your organization and Web site. How dynamic is the site? How many users do you have? How much caching is going to really help you?
Then consider what caching features you need. As we've discussed, caching makes transactions blazingly fast and scalable. But it can sometimes give users outdated information. Keep that balance in mind as you consider various solutions.

Iqbal Khan is the President and Technology Evangelist of Alachisoft. Alachisoft provides NWebCache, which is a leading ASP.NET Web cache for boosting performance and scalability. Iqbal received an M.S. degree in Computer Science from Indiana University, Bloomington, in 1990. You can reach him at iqbal@alachisoft.com.


Microsoft Dynamics
Troubleshooting Microsoft Dynamics CRM
Aaron Elder
At a Glance:
  • Illustrating a Solution Architecture
  • Fundamental troubleshooting principles
  • Tools for diagnosing server issues
  • Steps to resolving CRM errors

Contents
The Stack
Ground Rules
Troubleshooting Server Issues
Play at Home Example
Wrapping Up
I’m back for a second article on Microsoft Dynamics CRM (see the first article, "Deploying Microsoft Dynamics CRM 4.0"), with a focus on something I am passionate about—that is, troubleshooting. Troubleshooting Microsoft CRM is not much different than troubleshooting any other n-tier Web application built on the Microsoft stack. As such, this article is not a how-to guide or a collection of "101 Tips & Tricks." Instead, I will discuss the basics of Dynamics CRM components and tools for isolating, understanding, and solving problems. For this article, I will focus only on troubleshooting the server-side aspects of Microsoft Dynamics CRM issues.

The Stack
With any complex system, be it the human body or an n-tiered Web application that leverages many equally complex subsystems and external systems, it is important to understand "the system stack." The stack is basically a blueprint of a system that allows you to understand all the components of that system and how they build and layer on each other. The stack could also be called the Solution Architecture Diagram, as it illustrates the components of the solution—in this case Microsoft Dynamics CRM. Once you understand the solution, you also need to understand how the solution has been deployed. For this, you need a System Architecture Diagram, which illustrates where each component of the solution sits in relation to the others in your deployment. Understanding all of this is critical to being able to isolate the problem.
Figure 1 shows a Solution Architecture Diagram of Microsoft Dynamics CRM 4.0, depicting all of its major components and their interdependencies. Note that each component could in turn have its own diagram of equal or greater complexity. Computer systems are all about abstraction, the process by which a system component makes available a set of features that another, dependent component can rely on, hiding the internal complexity of that component. Abstraction is the reason you need to isolate a problem when troubleshooting.
Figure 1 UML Package Diagram Depicting a Solution Architecture
To express the solution architecture, I have used a UML Package diagram. Arrows point in the direction of the "dependency." For example, the CRM Email Router "depends on" an SMTP Server and the CRM Platform's Web Services. A full diagram would be very complex, but this provides a basic model.
Now you can think about how Microsoft Dynamics CRM components are deployed within your enterprise. For the purposes of this article, we'll use a typical deployment architecture, as shown in Figure 2. Understanding how the system architecture relates to the solution architecture is vital when it comes to isolating problems. Without knowing where components are running, you could spend hours trying to find and fix a problem that isn't even happening on the machine you are trying to fix!
Figure 2 A Typical Deployment Architecture

Ground Rules
Before you start troubleshooting a problem with Dynamics CRM, you need to understand a few fundamental troubleshooting principles. First, let's walk through a basic workflow of troubleshooting and some guidelines for how you know when it is safe to proceed to the next step.

1. A problem or error is identified and reproduced.
  • Have you identified the problem and have you read the error message?
  • Is the error message generic? If so, have you taken steps to find the "real error"? Hint: If the error says "Something happened, contact your System Administrator," remember that you are probably that System Administrator and therefore need to do more digging to find the real error. Before you move to Step 3, you must be sure you are dealing with the actual error.

2. The problem needs to be understood.
  • Did you read and comprehend what the error message is saying?
  • Do you have a consistent set of steps to reproduce the issue reliably?

3. The problem needs to be isolated.
  • What systems in your System Architecture can you rule out as a cause of or influence on the problem?
  • What components of the Solution Architecture can you rule out as a cause of or influence on the problem?

4. The fix needs to be identified and understood.
  • Are you able to find support articles, blog posts, or newsgroup postings that suggest fixes that apply to your exact problem?
  • Before applying a fix, do you understand why it will correct the problem?

5. The fix needs to be applied and verified.
  • Does the applied fix resolve the issue? You will need to be able to reproduce the issue (Step 2) in order to be sure. Because you understand the fix, have you re-tested other areas of the system that might be affected?

Troubleshooting Server Issues
With an understanding of the troubleshooting process, we can now move on to the tools needed for diagnosing problems within Microsoft CRM.
DevErrors—When Microsoft Dynamics CRM submits data to the server, information is passed to ASP.NET and processed. Any errors are handled by a global exception handler at the ASP.NET layer. For usability and sometimes security reasons, the real error is hidden from the caller (that's you or your user) and a "pretty error" is displayed instead. Typically this error says something like "You do not have sufficient privileges" or "The requested record was not found." Unfortunately, these pretty errors come from a "white list." Of the hundreds of thousands of errors that could be thrown by CRM or any related component (SQL, SRS, .NET, Windows, and so on), only error codes that the CRM team thought might happen have a pretty string associated with them. The rest get handled by the dreaded catchall "An error has occurred, please contact your System Administrator." This, of course, is not of much use to you, the System Administrator.
Since one of our ground rules is to get the real error, you need to be able to tell when CRM is lying or at least not telling you the whole truth. The truth serum is to enable DevErrors via the web.config. This is done by modifying the [CRMWEB]\web.config file like so:
<add key="DevErrors" value="On"/>
Be sure to keep your System Architecture in mind when doing this. If you have two servers configured in a load-balanced environment, you will want to isolate the server where the error is happening or, alternatively, be sure to enable DevErrors on both servers. Once DevErrors is enabled, you will see errors that look something like the one in Figure 3.
Figure 3 A Microsoft CRM Error Report
Figure 3 shows several items on the left that provide different sets of information:
The Detailed Error—The first screen (the default) shows you the real error from Microsoft Dynamics CRM's point of view. This includes the Date, Time, and Server name where the error occurred, as well as the Error Description, Call Stack, Error Number (if available), the Source File and Line Number where the error occurred (if available), and the URL that was requested—all useful when trying to figure out what went wrong.
The ASP.NET Error—The next item is the real error from ASP.NET's perspective. This provides much the same information as the CRM error, but adds the options to "Show Detailed Compiler Output" and "Show Complete Complication Source."
Diagnostic Info—The third screen, shown in Figure 4, provides basic information about the server, where the error occurred and details about the client and user that made the request. This information includes server operating system, .NET runtime version, server name, and the path to where CRM is installed. Information on the specific CRM database used and settings in the web.config also is included. For the client, the screen shows the browser version, screen resolution, bit depth, and more. Information on the user making the request (at least from CRM's point of view) includes the user's domain and name, CRM user name, CRM User ID, Business Unit ID, and Organization ID.
Figure 4 The Diagnostic Info Screen
What the User Would Have Seen—The final item, shown in Figure 5, allows you to see from the perspective of the end user, as if DevErrors were turned off.
Figure 5 The Error Message the User Would Have Seen
Please note that DevErrors helps only with errors that happen during the processing of a Web application request and only those requests that involve a full page submit to the server. AJAX requests, such as with a publish of customizations, a workflow, or a grid action, do not support DevErrors. For these, you will have to use tracing.
TIP: If the information on the DevErrors page is cut off and you can't resize the window to see more, simply double-click anywhere on the page and a resizable window will open.
Tracing—If a CRM error happens anywhere other than from a direct Web request, the best way to get the real error is to use CRM tracing. Tracing can be enabled and configured by following the steps in the "How to enable tracing in Microsoft Dynamics CRM" article at support.microsoft.com/kb/907490. Or you can use a tool such as CrmDiagTool, available at box.net/shared/6oxfqi2ida.
TIP: In CRM 4.0, the "Trace Directory" is ignored.
Tracing can be scary for the novice, so don't get frustrated. In general, tracing should be used only when troubleshooting an issue. Depending on how you configure tracing, there can be a significant performance impact when it is running, and if you have verbose logging on, a heavily used system can easily create hundreds of megabytes of logs per hour. The article mentioned above provides a detailed explanation of all the ways to configure tracing and how to enable tracing for client and server.
TIP: When emailing out trace logs for help, be sure to compress them first. Log files are just text and usually compress by 90 percent or more.
Log File Structure—When tracing is enabled on the server, logs will be placed in the Trace folder, which is located where you installed CRM. Each service has its own log file and each file will by default grow to 10MB before a new file is started. Since the log files are actively being written to by the various CRM processes, you will not get the absolute latest trace information until the corresponding service (either IIS or Async Service) is stopped. When you open the folder you will see files such as
-CRMDEV-VPC-CrmAsyncService-bin-20090415-1.log
-CRMDEV-VPC-w3wp-CRMWeb-20090415-1.log
The naming convention is [MACHINE NAME] – [CRM PROCESS] – [YEAR MONTH DAY] – [SEQUENCE].LOG
The log file contains loads of information, with items written in chronological order. Note that the trace log writes the latest event at the bottom of the file, while within a call stack items are written in reverse chronological order (newest item first).
TIP: When looking for errors in the log, try searching for ": Error" (a colon followed by a space, then Error.
Event Log—The Windows Event Log is another place to look for errors that occur within Microsoft Dynamics CRM, its dependent components, or other areas of the system. Just like the Trace Log, the Event Log will generally provide more details about errors that occur within the system.
Microsoft CRM does not log all errors to the Event Log. For example, a disabled user trying to log in is logged while an attempt to update a record that no longer exists is not logged. Though this isn't documented, CRM logs errors to the Event Log from the following subsystems:
-MSCRMPerfCounters
-MSCRMPlatform
-MSCRMKeyArchiveManager
-MSCRMKeyGenerator
-MSCRMEmail
-MSCRMDeletionService
-MSCRMReporting
-MSCRMWebService
-MSCRMAsyncService
-ASP.NET 2.0
The ASP.NET 2.0 bucket acts as a "catch most" for Application layer errors. In addition, the Microsoft Dynamics CRM Email Router Service has its own Event Log (MSCRMEmailLog) that can be configured independently to log a wide range of information, warnings, and errors.
Since Event Logging does not need to be turned on, it is a good starting place to look for issues.
Reading a Call Stack—Call stacks come in all shapes and sizes and all too often are overlooked by nondeveloper troubleshooters. It is not uncommon for system engineers to simply "ignore the developer stuff" and research only the error message or code. I recommend that you not do this—even though the call stack looks like code, it is designed to be human readable and to tell what happened right up until the error occurred. Look at the following example:
[ReportServerException: The Report Server Windows service 'ReportServer' is not running.
   The service must be running to use Report Server. (rsReportServerServiceUnavailable)]
   at Microsoft.Reporting.WebForms.ServerReport.SetDataSourceCredentials(DataSourceCredentials[]credentials)
   at Microsoft.Crm.Web.Reporting.SrsReportViewer.SetExecutionCredentials(ServerReport report)

[CrmReportingException: The Report Server Windows service 'ReportServer' is not running.
The service must be running to use Report Server. (rsReportServerServiceUnavailable)]
   at Microsoft.Crm.Web.Reporting.SrsReportViewer.SetExecutionCredentials(ServerReport report)
   at Microsoft.Crm.Web.Reporting.SrsReportViewer.ConfigurePage()
   at Microsoft.Crm.Application.Controls.AppUIPage.OnPreRender(EventArgs e)
   at System.Web.UI.Control.PreRenderRecursiveInternal()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
What you see here is a list of all the things the system did (the calls) listed in reverse chronological order (the stack) up until the last thing it "tried" to do. In this case, the first thing that happened—the call at the bottom of the stack—was a call to System.Web.UI.Page.ProcessRequestMain().
When reading call stacks, it is important to read the name of each call. For each call listed, the words between the last period and the parenthesis are the method name. Words before the method name are the namespace, and the items within the parentheses, the parameters. In this case, the method ProcessRequestMain was called first; this method is in the System.Web.UI.Page namespace; and this method expects two Boolean (True/False) parameters. Right away, by the reading the namespace, we know that this call is not to any Microsoft Dynamics CRM code; this is actually calling code in the base .NET Framework (denoted by the System root namespace) and specifically to ASP.NET (denoted by the Web namespace). As we read "up" the stack, we see that ProcessRequestMain then called PreRenderRecursiveInternal, which then called OnPreRender. The OnPreRender method is the first method that is actually a part of the Microsoft Dynamics CRM code base, as denoted by the Microsoft.Crm namespace. As we continue up the call stack, we see that CRM actually makes a call into the SQL Reporting Services method SetDataSourceCredentials. This method then throws an exception of type ReportServerException with the error that the Report Server is not running. As you can see, by reading the call stack, you can tell that this error is not coming from CRM at all, but is instead coming from SQL Server Reporting Services (SSRS) and is then being "bubbled up" through CRM as a CrmReportingException. Based on your system architecture, you will have to determine where SSRS is running in order to know where to go start the service to resolve the issue.
Reading call stacks in this manner can help you isolate where an error is actually coming from. The final call in the stack might be something like YourCompany.Crm.Extensions.UpdateRecord(); this would tell you that the error appears to be coming from either code written by your developers or perhaps an ISV solution you purchased.
It is not uncommon for CRM errors to actually come from SQL Server in the case of referential integrity (RI) or other SQL level constraints, or from the .NET Framework itself.

Play at Home Example
Now let's give you a chance to play at home. Let's assume you have created a new user in CRM and that user is trying to use the system for the first time and is getting the error as shown in Figure 6.
Figure 6 A CRM Error Received By a First-Time User
What steps would you take to solve this problem? To resolve this issue, let's follow our basic troubleshooting workflow.

1. A problem or error is identified and reproduced.
You should ask the user what he was trying to do when he received the error, then attempt to reproduce the steps to see if you can recreate the error.

2. The problem needs to be understood.
Let's read the error message from the troubleshooter's point of view: "the logged-on user does not have the appropriate security permissions."
To understand the problem, you have to be able to answer two questions: Who is "the logged-on user"? What "security permission" does he "not have"?

3. The problem needs to be isolated.
In this case, you can answer both questions by using CRM tracing. You know that tracing is needed because this error page is in a dialog and does not provide the information that DevErrors would. CRM does not log privilege errors like this to the Event Log. Let's enable tracing and reproduce the issue using the same user. The Trace Log provides the following detailed error:
MSCRM Error Report:
Error: Exception has been thrown by the target of an invocation.
Error Number: 0x80040220
Error Message: SecLib::CrmCheckPrivilege failed. Returned hr = -2147220960 on UserId:
  e76c5f50-40b3-dc11-8797-0003ffb8057d and PrivilegeId: 7863e80f-0ab2-4d67-a641-37d9f342c7e3
Error Details: SecLib::CrmCheckPrivilege failed. Returned hr = -2147220960 on UserId:
  e76c5f50-40b3-dc11-8797-0003ffb8057d and PrivilegeId: 7863e80f-0ab2-4d67-a641-37d9f342c7e3
Source File: Not available
Line Number: Not available
Request URL: http://localhost:5555/AscentiumCrmDev/sfa/accts/edit.aspx?id={906C2F37-8D28-DE11-8D9F-0003FFB23445}
Stack Trace Info: [CrmSecurityException: SecLib::CrmCheckPrivilege failed. Returned hr = -2147220960 on UserId:
  e76c5f50-40b3-dc11-8797-0003ffb8057d and PrivilegeId: 7863e80f-0ab2-4d67-a641-37d9f342c7e3] at
  Microsoft.Crm.BusinessEntities.SecurityLibrary.CheckPrivilege(Guid user, Guid privilege, ExecutionContext context)
…
From this error and by reading the call stack details, you can see that the problem is caused by a CRM Check Privilege failure. You can see the GUID of the user who made the request as well as the GUID of the privilege he tried to use.
If you perform a Live Search on the Privilege 7863e80f-0ab2-4d67-a641-37d9f342c7e3, the first hit is to the Microsoft CRM SDK.
Following this link, you can see that the privilege the user needs is prvWriteAccount, which is the privilege that grants the user update rights on the Account entity. The same method would work for any of the hundreds of out-of-the-box privileges, as the GUIDs are all known. If you search for the Privilege ID and it was not found, the privilege might be on one of your custom entities, in which case you will need to query your local SQL Server to find out what privilege is being requested. The following script will yield the same information:
SELECT PrivilegeId, Name
FROM PrivilegeBase
WHERE PrivilegeId = '7863e80f-0ab2-4d67-a641-37d9f342c7e3'
Now that you know what privilege is needed, you just need to verify which user is missing the privilege. While you can sometimes assume that the calling end user is the one, you can't always be sure, such as when actions are performed via code, plug-ins, or custom extensions. In such cases, you may want to do a query to find the name of the user CRM thought was trying to use the privilege. The following script will handle this:
SELECT SystemUserId, DomainName
FROM SystemUserBase
WHERE SystemUserId = 'e76c5f50-40b3-dc11-8797-0003ffb8057d'
TIP: If the user GUID is all zeros (00000000-0000-0000-0000-000000000000), the user is probably the SYSTEM account and this means the calling user was probably an account such as Network Service. System accounts do not typically get CRM roles; instead they are granted elevated privileges via the PrivUserGroup in Active Directory.

4. The fix needs to be identified and understood.
You can now go into CRM and check to see what roles the user has, as shown in Figure 7.
Figure 7 User Roles in CRM
You can then drill down and see that the Salesperson role is indeed missing the Write on Account privilege as shown in Figure 8.
Figure 8 Core Records of the Salesperson Role shows it’s missing the Write privilege under Account.

5. The fix needs to be applied and verified.
To fix the issue, you simply need to grant the Write privilege to this role and save it. Be careful to make sure you understand which other users this will affect. Once you apply the fix, you can ask the user to try again to verify the problem has been resolved. You should then disable tracing and call the case closed.

Wrapping Up
Troubleshooting Microsoft Dynamics CRM means following basic ground rules and a methodology that includes identifying the real issue, narrowing scope, isolating the issue, and understanding the fix. You'll find the DevErrors, Event Logging, and Tracing tools in Microsoft Dynamics CRM critical in your troubleshooting efforts.

Aaron Elder (Microsoft Dynamics CRM MVP) works for Ascentium, a technology consulting and interactive marketing agency. Visit the Ascentium blog at ascentium.com/blog/crm.


Commerce Server 2009
Getting Started with Commerce Server 2009
Alan Maddison
At a Glance:
  • New features and functionality
  • Installation requirements
  • Integrating with SharePoint
  • Deploying the Default Site

Contents
Features and Functionality
Installation Prerequisites
Getting Started
With the recent release of Commerce Server 2009 (CS 2009), Microsoft has made some significant improvements to an already capable product. These improvements include something for everyone.
For developers, there's a simplified method of accessing Commerce Server functionality, while the new multi-channel support (Web, mobile, Live Services) can help broaden your target market and keep your management happy. CS 2009 also offers new functionality—for example, there's the Microsoft Office SharePoint Server (MOSS) 2007 integration, which includes an out-of-the-box commerce site based on SharePoint, and 31 Web parts to provide maximum customizability and ease of use. It all adds up a great new release.
In this article, we'll discuss some of Commerce Server 2009's most significant new features and functionality and take a step-by-step look at installation and configuration. CS 2009 supports highly distributed and scalable deployments, but we'll focus here on a small implementation that will highlight all the key installation and configuration requirements you'll encounter regardless of your production infrastructure.

Features and Functionality
One of the Commerce Server development team's core goals was to provide an e-commerce solution that would address the key pain points many organizations feel when deploying e-commerce solutions. If you've ever deployed and supported CS 2007, you're familiar with issues such as core-development complexity, time to deployment, difficulty in extending or customizing a solution, and targeting multiple end-user devices (for example, Web and mobile devices). With the release of CS 2009, Microsoft has simplified and enhanced the core capabilities of earlier versions.
The CS 2009 architecture retains the core subsystems (Catalog, Inventory, Marketing, Orders and Profile) of CS 2007, but with many enhancements.
Developer access to these core subsystems has been improved with the Commerce Foundation API, which presents a unified interface to all the subsystems. To anyone familiar with CS 2007, this improvement is most welcome; it should help reduce both the learning curve for those new to the product and the time to market. This new API also improves the ability to extend Commerce Server's functionality through the introduction of extensibility points: Commerce Entities, Operations and Providers.
In addition, the presentation and business-logic layers have been separated to allow for multi-channel support. By separating these layers, Commerce Server provides you with the ability to maintain common business logic regardless of how users access your Web site. Sales made via the Web will follow the same workflow process as sales made via mobile devices.
CS 2009 also delivers a rich user experience through its integration with SharePoint. That integration extends to the inclusion of 31 Web parts designed to offer access to functionality such as marketing, payment, shopping and profile management. In addition, your e-commerce sites can also use SharePoint functionality, such as searching, social networking, document lists, workflow and styling.
Finally, from the IT administrator's perspective, Commerce Server 2009 supports x64 hardware in a highly distributed and scalable architecture, integrates with System Center Operations Manager 2007 for monitoring and runs on either Windows 2003 or Windows 2008.

Installation Prerequisites
Before beginning a Commerce Server 2009 installation, you should note some important points. First, some key components of CS 2009 are actually the core components of CS 2007, so in a fresh install of CS 2009, you'll begin by installing CS 2007. (The install media should consist of two CDs—one for CS 2007 and the second for CS 2009). If you're upgrading to CS 2009, you must already be using CS 2007; upgrades from earlier versions such as Commerce Server 2002 aren't supported. CS 2007 requires Microsoft .NET 2.0, while CS 2009 requires the .NET 3.5 SP1 framework, which you need to download and install. In addition, CS 2007 requires the installation of Document Explorer 2005, regardless of which version of Windows Server you use. The required CAB file can be downloaded from the following locations:
  • x86 (EN): go.microsoft.com/fwlink/?Link ID=63706
  • x64 (EN): go.microsoft.com/fwlink/?Link ID=63730
You should extract DExplore.exe and launch the installer. If you don't install these files, the CS 2007 installer will generate a warning after you launch Setup and the prerequisites checker has run.
While it's possible to install CS 2009 without installing MOSS 2007 or Windows SharePoint Services (WSS) 3.0, some of CS 2009's key enhancements are built around SharePoint functionality. As such, the CS 2009 SharePoint Commerce Services component requires either WSS 3.0 or MOSS 2007. It's highly recommended that you also install Service Pack 2 for these products. (You can review the changes in SP2 as well as links to download the media for MOSS 2007 at support.microsoft.com/kb/953334/ and for WSS 3.0 at support.microsoft.com/kb/953338/.)
However, if you intend to use an existing installation of either WSS 3.0 or MOSS 2007 that's running SP1 and you don't intend to apply SP2 before the install of CS 2009, you need to download and install two cumulative updates. (The Windows SharePoint Services 3.0 update is available at support.microsoft.com/kb/961755/. The Microsoft Office SharePoint Server 2007 is available at support.microsoft.com/kb/961756/.) You must apply both update packages and run the SharePoint Products and Technologies Configuration wizard after each package has been installed.
If you intend to use WSS 3.0, you must perform a custom installation and create the databases in either SQL Server 2005 SP2 or SQL Server 2008 (Standard or Enterprise editions). That's because CS 2009 doesn't support SQL Server Express edition. In addition, the Commerce Server data warehouse functionality doesn't support installation of SQL Server Report Services and SQL Server Analysis Services in a named instance. In addition, if SQL Server is configured to use case-sensitive collations, you must pre-create the Commerce Server databases to modify them to support a non-case-sensitive collation before running Setup.
Commerce Server 2009 supports IIS 6.0 and IIS 7.0, but when installing the Web-server role (IIS 7.0) in Windows Server 2008, you must add supplementary role services. In Windows Server 2008, use the Add Roles Wizard, located in Server Manager Administration tool, to add IIS and associated services. All the required services are shown in Figure 1.
Figure 1 The required role services that must be added in Web server role (IIS 7.0) in Windows Server 2008.
Rose Service Category | Services
Common HTTP Features | Static Content Default Document Directory Browsing Http Errors
Application Development | ASP ASP.NET .NET Extensibility ISAPI Extensions ISAPI Filters
Health and Diagnostics | HTTP Logging Request Monitor
Security | Basic Authentication Windows Authentication Digest Authentication Request Filtering
Performance | Static Content Compression
Management Tools | IIS Management Console IIS Management Scripts and Tools Management Services IIS6 Management Compatibility
In Windows Server 2003, you add IIS server by selecting the Application Server option in the Windows Component Wizard, which is accessed through the Add or Remove Programs control panel. After selecting Application Server, click on Details; be sure to select the additional options of ASP.NET and Enable Network DTC (Distributed Transaction Coordinator) Access. Next, select IIS and click the Details button, then select World Wide Web Service and click the Details button. Once the screen loads, click Active Server Pages and WebDAV Publishing. Click OK three times, then click Next, followed by Finish to complete the process.
Commerce Server requires several different service (user) accounts for the various components. You should create these accounts before beginning installation. While it's possible to use a single account for all components, that's not the best practice for a production deployment. Using unique accounts for each Commerce Server component provides additional security through isolation of service credentials. Using a single account is acceptable in a development environment and, in fact, will simplify the configuration process—but you shouldn't take shortcuts in your production environment.
Figure 2 lists the types of user accounts you may need. The names used are only examples; use your own naming conventions. Giving the accounts meaningful names will make things easier for both administration and troubleshooting. Once you've created these accounts, you must add them as SQL Server logins, using SQL Server Management Studio.
Figure 2 Sample User-Account Names for Commerce Server 2009
Account name | >Service
svc_cscatalog | Catalog Web service
svc_csdirectmailer | Direct Mailer service
svc_csstaging | Staging service
svc_csmarketing | Marketing Web service
svc_csorders | Orders Web service
svc_csprofiles | Profiles Web service
svc_csiis | IIS account for accessing a Commerce Server site or application
svc_cshealthmonitor | Health Monitoring service (optional)
svc_biztalkadapers | BizTalk adapters (optional)
Finally, while development of applications and use of the BizTalk adapters are beyond the scope of this article, note that development requires Visual Studio 2008 and the BizTalk adapters require BizTalk 2006.

Getting Started
After launching Setup, you'll see the Commerce Server 2007 welcome screen. Enter your user and license-key information and accept the software license agreement to reach the component-selection screen. By default, the only selected component is Commerce Server 2007, which includes the core components, including the subsystems, documentation and management console. You may also select from up to five optional components, including Project Creation Wizard, Health Monitoring, Staging, Direct Mail and Volume Shadow Copy Writer Service.
The Project Creation Wizard may be grayed out because it requires Visual Studio (2005 for CS 2007; 2008 for CS 2009) to be installed. As a result, you shouldn't install that wizard on a production server; instead, install it on your developers' machines. The Health Monitoring Service, in conjunction with the Management Pack, allows you to monitor Commerce Server using System Center Operations Manager. The Staging Component, which is available only in Commerce Server's Enterprise version, allows you to publish or stage new content into production once the content has been reviewed and approved. Direct Mail is a bulk-mailer component that allows you to send personalized e-mail to your customers. The Volume Shadow Copy Service Writer component is self-explanatory; I recommend that you always install this component. Whether you choose to install the others depends on your organization's requirements. In this walk-through, however, we won't install any optional components.
After installation is complete; leave the check box selected and launch the Configuration Wizard. Once the wizard loads, click Next to move past the welcome screen to the Administration Database configuration screen. The SQL Server name will default to the local server name, so you should enter the correct name if SQL Server isn't installed locally. I recommend leaving the Database name at the default value (MSCS_Admin) unless there's a reason you need to change it. Immediately below the SQL Server and Database name, you can test your SQL credentials to confirm that you can connect to the SQL server. Microsoft recommends using Windows Authentication rather than SQL Authentication. After clicking the Test button and confirming a successful connection, click Next.
The next screen will depend on the options you chose to install. For example, if you selected to install the Staging and Direct Mailer options, you'll be asked to configure the SQL Server and Database name as well as the service account to use. Again, you'll need to modify the SQL Server name if you don't have a local installation; you should use the service or user account that you created earlier (see Figure 2).
After completing the database configuration options for your selected components, you'll come to the Summary screen where you have the option to save an XML file of the configuration. Click the Save button; this will save an XML file for later use. Click Next to begin the configuration process. When it's done, review the log file before clicking Finish to complete the process.
After running the configuration wizard, re-launch Setup and install the Business Management Applications. Accept all the defaults; you'll need all the applications installed. In a production environment, you should consider deploying a Business Management Server to host these tools. Once the tools are installed, you'll need to unpack and configure a Commerce Server site to install the Web services that the tools use. The site also includes the framework needed by developers looking to create applications. Because you're upgrading immediately to CS 2009, you can do this after you've installed and configured CS 2007. After launching Setup, click Next; accept the license agreement and click Next again. This immediately launches the installation process.
Once installation is complete, the Server Configuration Wizard should launch automatically. At that point, you can use the previously saved XML file to pre-populate the configuration fields. You'll need to re-enter the service-account passwords manually.
Click Next to move past the welcome screen. Because you used that XML file, you should see the correct information on the administration database screen. However, you'll need to re-enter the password. Click the Test button to confirm a successful connection to SQL Server. If the connection is successful, click Next to configure the optional components that you installed with CS 2007. After completing the configuration, you'll come to the Summary screen, where you'll be able to review the components to be configured. Click Next to start the configuration process. Once it's completed, you'll have a chance to review the log file. Click Finish.
The next step, which will launch automatically, is the Upgrade Wizard. Again, click Next to move past the welcome screen. At that point, you'll be asked to specify the log file path; the default path is acceptable. Because you haven't configured any sites, this process should go quickly. If you're performing an upgrade on a CS 2007 production system, however, the process will take longer to complete. Once the upgrade process is complete, click Finish.
At that point, you're ready to begin deploying Commerce Server sites. There are two sites included with CS 2009: the new Default Site, which is based on SharePoint, and the CSharp site. We'll focus on the Default Site.
To start deployment, launch the SharePoint Commerce Services Configuration Wizard. (If you're using Windows Server 2008, ensure that User Account Control is turned off before taking this step.) After launching, the wizard will complete a dependency check. Click Next, then decide whether to install the Commerce Web Parts or the SharePoint Default Web site and Web Parts. In this article, we'll install the Default Web site and Web Parts. Click Next to move on to the SharePoint application and site collection configuration screen. Then click the ellipsis to the right of the drop-down box; the wizard will walk you through creating a SharePoint Web application.
After you've selected the Web application to use, you should modify the Site Collection Title to something more meaningful than the default value of "Home." The next choice is identifying the Site Collection Administrator; by default, this will be the user you're currently logged on as. Then you should decide whether to change the Commerce Server site name and whether you want just the site or the site with sample data. If you're evaluating CS 2009, I suggest you use the sample data; otherwise, just unpack the site. Then change the database server name if you are not running SQL Server locally. After clicking Next, you will have a chance to review a summary of the configuration information.
Once you have read the Summary, click Next to begin the configuration of SharePoint. In most environments, this process should take only a few minutes. When that process is complete, you should see a results screen. Review the results to confirm that there are no errors, then click Finish. Note: If you click the View Site button, your new Commerce Server SharePoint site won't launch because there's more configuration to perform.
The first step is unpacking the Web services into the new site you just created. Navigate to <commerce server install directory>\Microsoft Commerce Server 2009\Site and find the site package called MicrosoftCommerceDefaultSiteWithSampleData.pup. Double-click to launch the Commerce Server Site Packager, which contains a sample catalog from Adventure Works. Once CS Site Packager has launched, choose Custom Unpack. Next choose Add an Application in the Package to an Existing Site, then click Next. Select DefaultSite and click Next. Leave all applications checked and click Next again. When the IIS Computers, Web Sites and Paths screen loads, select the Web site to use. The application names and associated virtual directory paths can be left at their default values.
As a general recommendation, don't use the SharePoint Web site that you just created as a target. Selecting the IIS Default Web Site is a good alternative in a testing situation. Click Next to begin the unpacking process; click Finish once complete.
To complete the process, you'll need to configure a series of permissions both on the Commerce Server and on the SQL Server. First, apply the NTFS permissions on the Commerce Server (see Figure 3).
Figure 3 NTFS Permissions on Commerce Server
Folder/File Name | Service Account to Add | Permission to Add
\Windows\Microsoft.NET\Framework(64)\v2.0.xxxxx\ Temporary ASP.NET | svc_cscatalog; svc_csmarketing; svc_csorders; svc_csprofiles; svc_csiis | Write
\Windows\Temp | svc_cscatalog; svc_csmarketing; svc_csorders; svc_csprofiles; svc_csiis | Write
CatalogAuthorizationStore.xml | svc_cscatalog | Write
Now create security groups to assign Administration roles within Commerce Server. CS 2009 uses Authorization Manager to assign these roles; you'll add all Administration users to these groups and then assign the Authorization Manager roles to the groups. These groups will allow full administration of CS 2009.
In production, you should assign more granular roles that more closely match user responsibilities to Commerce Server functionality. For example, the four Administration Groups and their relationship with Authorization Manager are shown in Figure 4.
Figure 4 Commerce Server Administration Groups, Authorization Stores and Roles
Group Name | Authorization Manager Store | Role
CS Catalog Admin | CatalogAuthorizationStore | Administrator
CS Marketing Admin | MarketingAuthorizationStore | Marketing Administrator
CS Orders Admin | OrdersAuthorizationStore | Orders Administrator
CS Profiles Admin | ProfilesAuthorizationStore | Profile Administrator
To launch Authorization Manager, you must launch the Microsoft Management Console (azman.msc) from Windows. Click Start, then Run. Once Authorization Manager launches, you'll need to add the Commerce Server Authorization stores. Right-click on Authorization Manager and select Open Authorization Store, making sure the selected store type is XML, then browse to the directory that holds the Web files for Commerce Server. For example, if you installed the catalog Web Services to the default Web site in a default install of IIS, the path would be c:\inetpub\wwwroot\defaultsite_catalogwebservice\CatalogAuthorizationStore.xml. As shown in Figure 4, you need to do this for all four Web services.
The next step is to grant database access to the Web service accounts (see Figure 5). These accounts were added as SQL logins when they were created. You'll need to add SQL permissions to all Commerce Server sites. In our example, this means the Default Site databases.
Figure 5 Grant database access to Web service accounts (which were added as SQL logins when they were created).
Database Account | Database | SQL Server Roles
svc_cscatalog | MSCS_Admin | admin_reader_role
 | MSCS_CatalogScratch | db_datareader, db_datawriter, db_ddladmin
 | DefaultSite_ProductCatalog | ctlg_CatalogWriterRole, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, Inventory_ReaderRole, Inventory_WriterRole
svc_csmarketing | MSCS_Admin | admin_reader_role
 | DefaultSite _Marketing | mktg_MarketingService_role, mktg_promoCodeGenerator_role
 | DefaultSite _MarketingLists | db_owner
 | DefaultSite _ProductCatalog | ctlg_catalogReaderRole
 | DefaultSite _Profiles | Profile_Reader, Profile_Schema_Reader
svc_csorders | MSCS_Admin | admin_reader_role
 | MSCS_CatalogScratch | db_datareader, db_datawriter, db_ddladmin
 | DefaultSite _Marketing | db_ddladmin, mktg_runtime_role
 | DefaultSite _ProductCatalog | ctlg_catalogReaderRole, Inventory_ReaderRole
 | DefaultSite _Profiles | Profile_Reader, Profile_Schema_Reader
 | DefaultSite _TransactionConfig | Orders_Management
 | DefaultSite _Transactions | Orders_Management, Orders_Runtime
svc_csprofiles | MSCS_Admin | admin_reader_role
 | DefaultSite _Profiles | Profile_Schema_Manager, Profile_Runtime
svc_csiis | MSCS_Admin | admin_reader_role
 | MSCS_CatalogScratch | db_datareader, db_datawriter, db_ddladmin
 | DefaultSite _Marketing | db_ddladmin, mktg_runtime_role
 | DefaultSite _MarketingLists | db_datareader
 | DefaultSite _ProductCatalog | ctlg_catalogReaderRole, Inventory_RuntimeRole
 | DefaultSite _Profiles | Profile_Schema_Reader, Profile_Runtime
 | DefaultSite _TransactionConfig | Orders_Runtime
 | DefaultSite _Transactions | Orders_Runtime
Now create application pools for the Commerce Server Web services, using IIS Manager. These pools will use the services accounts you created earlier and represent the final step in tying all the existing pieces together. You'll need to create four application pools, one for each Commerce Server Web service (Catalog, Marketing, Orders and Profiles). When you create the application pools, accept all the default values. After completing this step, you'll need to modify the identity of the user account used by the application pool. Each pool should be associated with the corresponding service account you created earlier.
Next, you need to add the service accounts you created to the IIS Worker process groups. In Windows 2008, this is a local group called IIS_IUSRS; in Windows 2003, the group is called IIS_WPG. Now you can assign the Commerce Server applications to the application pools you created using IIS Manager. In Windows 2008, this setting is modified under the Advanced Settings command in the Action Pane; in Windows 2003, you access the setting under the properties of the application or site. After you finish this step, restart IIS.
One final step remains: configuring the default Channel for your SharePoint commerce site. To do this, first launch the SharePoint site in your browser. Click Site Actions in the top left corner, then Site Settings, then List Templates in the Galleries Column. Now click Change Configuration under Lists in the Quick Launch section on the left of the page. Next, click Channel, then Edit Item. In the value box, type "Default," then click OK. Now click Default Catalog, then Edit Item. In the value box, type "Adventure Works." At this point, you've finished the configuration and you're ready to begin exploring Commerce Server 2009.

Alan Maddison is a Senior Consultant specializing in Microsoft technologies with Strategic Business Systems, a division of Brocade.


From the Editor A Friendly Push in the Right Direction
Howard Dierking

In opening this editor's note, I want to make one thing painfully clear. I am first, last and always a developer. I can write T-SQL, but know very little about the various techniques for building clusters and administering the system. I can use declarative and imperative options for configuring Code Access Security in the Microsoft .NET Framework, but am at quite a loss when trying to configure my Windows Home Server to require a certificate for remote access. So what could I possibly have to say of value to readers of TechNet Magazine?
Over the last two years, I've developed a health obsession with the SQL Server BI stack. And in building a few internal solutions with these technologies, I've come to a conclusion that I think is worth sharing here as it reveals very necessary synergies and changes that need to be realized between developers, IT operations and the business.
In reflecting back over both business applications that I've built and applications that I've reviewed, it seems clear that software development as a general activity is optimized around building transactional applications. Reporting capabilities are generally bolted on top of a highly normalized relational database that was never intended to provide any level of insight beyond the record of transactions. In other cases, the transactional database may be designed in an attempt to support both transactional and deeper reporting requirements—in my experience, such systems are not generally successful at either.
While it seems that this may be a developer problem, I bring it up here because in several of the conversations I have had with developers about this problem, the justification that I have been given time and time again has been that IT allows one—and only one—database. So my request to you is this—stop giving us that excuse. Whether it's working with development to plan a separate, isolated reporting store or whether it's upfront integration planning for a larger warehouse, I ask that you impress upon the development teams the notion that this separation of concerns is one worth doing. Remember, we developers tend to optimize around the transactional piece—so without a friendly push in the right direction, the path of least resistance is to ignore everything but this piece until it may be too late to do much about it without practically rewriting the application.
At the risk of making an overly broad generalization, you have a broader perspective when it comes to the business—after all, BI is generally considered an IT Pro activity. However, putting the pieces together is still very much a development activity. So use BI as a way to help broaden the perspectives of your developers. I'm sure they'll be grateful—and if they aren't, you can at least know that I'm grateful.
—Howard Dierking
Thanks to the following Microsoft technical experts: Steven Bergman, Ryan Donovan, Corey Hanson, Ken Jones, Denny Lee, Sanjeev Nair, Chad Rexin, Stefan Schackow and Kimberly Tripp.


Toolbox New Products for IT Professionals
Greg Steen

The opinions expressed in this column are solely those of the author and do not necessarily reflect the opinions of Microsoft. All prices were confirmed at the time of writing, and are subject to change.

Replace Your Text-File Editor
Notepad++
notepad-plus.sourceforge.net/uk/site.htm
With my experience thus far, I feel pretty safe with the following extreme generalization: Text-file editing is a necessity for all developers and systems administrators. (I know: not exactly ground-breaking.) Also, developers and sysadmins all have different needs when it comes to a text-file editor's ease of use and functionality.
So if you aren't happy with your current editor, one candidate for replacing it is the free and open source Notepad++. Notepad++ is more than just a standard text-file editor—it's more of a source-code editor. At its core, it's based on Scintilla, a free source-code editing component. It comes with handy features such as syntax highlighting and collapsible sectioning of known file types such as script and XML files. It includes basics such as line numbering and cursor and character positions and counts. You'll also receive line "bookmarking" to help you know where you are in the file and help you jump back to those key lines via the bookmarks. In addition, you can define your own custom highlight patterns.
Notepad++ lets you have multiple documents open simultaneously so that you can easily switch between files. You can also split the editor screen either horizontally or vertically to compare lines. The application has great search-and-replace functionality, including the ability to search and replace by regular expressions.
If you need to repeatedly carry out a task that search and replace won't cover, Notepad++ also offers full macro-recording and playback functionality to help you reduce the amount of manual repetition needed. Notepad++'s plug-in architecture means that the application is extensible; numerous plug-ins are already available from the product's Web site. As defaults, Notepad++ includes plug-ins such as a document monitor, a set of MIME tools and a robust spellchecker.
You'll also appreciate the richness of Notepad++'s TextFX extensions, which give you a bundle of text-modification tools. For example, you'll get one to convert text to hex, one to split lines and one to Base64-decode a file, among others.
We've just scratched the surface of Notepad++, but if you're fed up with your text editor, or if you just like to support open source projects, consider Notepad++ for your IT Toolbox.

Price: Free.
Figure 1 Notepad++
If you have a favorite tool or utility you would like to see featured here, please write to the author at tntools@microsoft.com.

Greg Steen is a technology professional, entrepreneur, and enthusiast. He is always on the hunt for new tools to help make operations, QA, and development easier for the IT professional.


SQL Q&A Checksum Problems, Choosing the Correct Recovery Model and More
Paul S. Randal

QI'm a SharePoint administrator and I've recently discovered that one of my Content databases became corrupt when my monthly consistency checks found errors. We traced it back to a faulty RAID controller and, after doing some research, we're turned on checksums on the database pages. My question is: How can I tell when there's a checksum problem without waiting until the monthly maintenance plan?
AThere are a few things you can do. First, you can add the WITH CHECKSUM option to your backups. For full and differential backups, this option will cause the backup operation to test any page checksums it sees and fail with an error if it finds a corruption. (I covered this in more detail in last month's article, "Understanding SQL Server Backups.")
Second, consider running your consistency checks more frequently than monthly. I recommend running some form of consistency check at least weekly, whether that's a DBCC CHECKDB on the database or maybe on a restored copy of the database. That, of course, is going to depend on your comfort level with your I/O subsystem.
Third, add some SQL Agent alerts. An alert can be set to fire on a variety of factors, such as a particular error number being raised by SQL Server, an error with a particular severity being raised or a performance counter crossing a threshold. This capability provides a really powerful mechanism to monitor for server problems.
When an alert fires, a message is sent to a predefined "Operator" using one or all of these options: a pager message, e-mail, or NET SEND. You can use the stored-procedure sp_add_notification to define an Operator.
As far as I/O subsystem problems are concerned, the errors you're interested in are 823, 824, and 825. The first two are raised when an I/O issue occurs (specifically, 824 is when a page checksum is found to be broken, and 825 is when SQL Server has to try a read operation multiple times before it completes). These are all problems that you want to know about as soon as possible in order to limit further damage to the database (and possibly the amount of downtime to recover).
823 and 824 are both severity-level 24 errors but 825 is only a severity-level 10 "informational" message (for more information, see my blog post, "A little-known sign of impending doom: error 825.") To alert for these errors, you should define an alert for all severity-level 24 errors, and one specifically for error 825 (in fact, it's a best practice to have an alert for each severity-level from 19 to 25).
To define the actual alerts, you can use T-SQL or Management Studio. Below is an example of the T-SQL code to add an alert for the 825 error.
USE msdb;
GO
EXEC msdb.dbo.sp_add_alert @name = N'825 - Read-Retry Required',
    @message_id = 825,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1,
    @category_name = N'IO Subsystem Error';
GO
You can find more information on defining and adding alerts, including a step-by-step walk-through on adding alerts using Management Studio, in my SQL Agent blog category "(SQL 2005 SP2 maintenance plan bug masking corruption)."
QI'm a developer-DBA who's responsible for some code and the database it runs in. I've been arguing with some other database developers about how to get a unique value to identify table rows. I'd like to use a GUID as the clustered index key, but the others are arguing that it can lead to performance issues with indexes. Is this true and, if so, can you explain why?
AYes, it's true—GUIDs are one of the leading causes of index fragmentation in SQL Server databases.
A GUID is a Globally Unique Identifier. In SQL Server, this is a 16-byte value that is either generated in SQL Server or somewhere else (such as through .NET in the client- or mid-tier). GUIDs generally have a random value unless generated with the NEWSEQUENTIALID function introduced in SQL Server 2005.
This function produces ranges of GUIDs, which can help to alleviate some of the problems I'll describe here. But it requires generating a server-side GUID, which doesn't work in many application environments because the unique identifier must be generated before the data is sent down to the data tier.
No matter where the non-sequential GUID is generated, having it as the leading key in an index means that because the key is essentially random, the insertion point of a new record in the index is also random—the key value of a record determines its placement in the index. It also means that the 16-byte GUID will be present in every row of each non-clustered index as part of the link that allows the Storage Engine to navigate from non-clustered index records to clustered index records to obtain column values for the query select list that aren't in the non-clustered index that was used (also commonly known as a bookmark lookup).
As more records are inserted into the index, the pages storing the records fill up. If a record is being inserted on a page that's already full (remember, the key value determines where the new record goes), then the page must split, with some records moving to a newly allocated page. A page split is an expensive operation to perform (a new page is allocated and linked into the index, and records are moved onto the new page), and it causes fragmentation.
Page splits cause logical fragmentation in the index (which affects range scan performance) by causing the physical and logical ordering of pages in the index to be different. It also causes poor page density (where there is unused space on pages), which leads to wasted space on pages and poor disk, I/O, and memory utilization.
For more information about index fragmentation and how to detect and remove it, see my August 2008 article, "Top Tips for Effective Database Maintenance." Picking a good clustered index key is beyond the scope of this article; I'll leave it to my wife, Kimberly L. Tripp, to explain. See her great blog post on the topic, which also goes into more details about GUIDs and clustered index structures "(GUIDs as PRIMARY KEYs and/or the clustering key.")
QOur high-availability strategy is to use log shipping to a couple of secondary servers. Our management team is pressuring me to make some use of the redundant servers to save on capital expenditures. My idea is to use the secondary servers to allow reporting queries to run, which would also have the benefit of offloading the report workload from the primary server. What problems might I run into doing this?
AThis kind of scenario is becoming much more common in the current economic climate, where companies don't like to have servers sitting around that appear to be idle (even though they're providing a redundant copy of the database).
As you probably already know, when you set up log shipping, you can define how the transaction log backups are restored on a secondary server—either WITH NORECOVERY or WITH STANDBY. The former doesn't allow any access to the database, whereas the latter allows read-only access to the database. This uses a special mode where recovery runs and the database is transactionally consistent, but the operations performed are stored in a separate undo file so that further transaction log backups can be restored (I'll discuss this in more detail next month in an article about using RESTORE).
To allow reporting on the secondary server, you're going to use WITH STANDBY so the reporting queries can connect to the database. Once you allow user connections, you immediately run up against some issues.
First, using the WITH STANDBY option can lead to transaction log backups seeming to take a long time to restore on a secondary server, as the contents of the undo file must be replayed before the next transaction log backup can be restored. This can be a problem if the undo file contains a large number of operations.
Second, the restore of a transaction log backup isn't an online operation. No one can be connected to the database for the duration of the restore. This means that all connections to your secondary reporting server must be dropped, then reconnected after the restore completes. Here you have a dilemma: When it's time to restore the next transaction log backup, do you terminate user connections or allow them to finish their queries? That's completely up to you.
One question to bear in mind if you decide not to terminate connections: How long do you allow the queries to continue before you forcibly terminate them? The longer you wait, the longer it's been since the last log backup was restored, and the further behind the primary database the secondary one gets. This could be a problem if you then have to fail over to the secondary, because there could be a queue of log backups waiting to be restored to bring the database as far up to date as possible, minimizing data loss.
You can find more information about these options, as well as on monitoring issues such as length of time since the last log backup restore on the secondary server, in the Books Online topic, "Using Secondary Servers for Query Processing."
QHow can I choose the correct recovery model? From what I've read, it seems like I should be using bulk-logged to cut down on my transaction log size, but it seems like my log size still keeps growing. Is it possible to use a mode that doesn't use the transaction log at all and completely avoid the whole issue?
Ane of the requests I've heard several times is for a non-logged database, when no transaction log records are generated at all—especially for tempdb, which people view as a scratch database because it's recreated on server startup.
As far as I know, this will never happen for SQL Server. The best you'll get is the BULK_LOGGED mode, which drastically cuts down the amount of transaction log generated for certain operations (such as index rebuilds and bulk loads). All databases, even tempdb, must have some level of logging to allow transactions to roll back (that is, to undo all the operations that were part of the transaction) in the event of a user canceling the operation or some error causing the operation to fail.
What's more important, for databases apart from tempdb, is that if a system crash occurs, the database must be able to recover without leaving transactionally inconsistent data or a structurally inconsistent (that is, corrupt) database. Imagine if there were no record of what had been changing in the database before the crash—how would SQL Server run recovery? You can read more about how logging and recovery work in my February 2009 article, "Understanding Logging and Recovery in SQL Server."
For choosing a recovery model, one overriding question will determine your choice: Do you want to be able to do "point-in-time" or "up-to-the-minute" recovery in the event of a disaster? If so, you're going to use the FULL recovery model (and possibly the BULK _LOGGED model on occasion). If you're not interested in doing this, then use the SIMPLE recovery model.
The reason for using SIMPLE instead of FULL if you don't want to be able to recover the database (without losing work since the last database or differential backup) is that with the SIMPLE recovery model, you don't have to take transaction log backups to manage the size of the transaction log.
Now, there may be other reasons why you have to use the FULL recovery model—for instance, if you want to use database mirroring (DBM only supports the FULL recovery model) or log shipping (log shipping supports both the BULK_LOGGED and FULL recovery models). In either case, you're going to have to make sure that you're taking transaction log backups so that the log doesn't grow too large (even if you just end up discarding them).
I mentioned that you may occasionally want to use the BULK_LOGGED recovery model, rather than constantly running in that mode. This is because there are some limitations around taking and restoring from log backups when there has been a minimally-logged operation in the BULK_LOGGED recovery model since the last transaction log backup. The details are too complex to explain in this column, but you can find out more on this, and about choosing a recovery model in general, in the Books Online topic, "Recovery Model Overview."

Paul S. Randal is the Managing Director of SQLskills.com, a Microsoft Regional Director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. Paul wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development. Paul is an expert on disaster recovery, high availability and database maintenance and is a regular presenter at conferences worldwide. He blogs at SQLskills.com/blogs/paul, and you can find him on Twitter at Twitter.com/PaulRandal.


Utility Spotlight SDelete
Lance Whitney

All good IT pros know that when you delete a file in Windows, even bypassing the Recycle Bin, the data isn't removed from disk.
Instead, the file's entry in the Master File Table is marked as deleted and its clusters are freed up to store new data. That process is efficient from a disk-management point of view. But it can leave deleted files vulnerable: Unless new data wipes out all of a deleted file's clusters, that file can still be recovered. The only way to ensure that a file can't be revived is to securely overwrite all its data. One way to achieve that is by using Microsoft's free utility SDelete.
Another great tool by Mark Russinovich, a technical fellow in Microsoft's Platform and Services Division, SDelete can permanently wipe single files or directories or multiple objects by using wild cards. In addition to securely removing existing files, SDelete will purge data in unallocated clusters of a disk—that is, files that have already been deleted.
SDelete can even wipe encrypted files. When you normally delete a file protected by Windows Encrypting File System, the unencrypted contents remain behind. Use SDelete to eliminate the file instead, and the unencrypted contents are removed as well. SDelete uses the Department of Defense standard DOD 5220.22-M, which overwrites all characters of a file with a series of random new values.
To use SDelete, first download a copy, then extract the file sdelete.zip. The extracted contents include an end-user license agreement text file and sdelete.exe. No need to install—just trigger the program from its executable.
SDelete is a command-line utility, so you'll need to run it from a command prompt. Open a command window and move to the folder in which you downloaded SDelete. Typing "sdelete" at the prompt displays a list of its options, as shown in Figure 1 and below:
sdelete [-p passes] [-s] [-q] <file or directory>
sdelete [-p passes] [-z|-c] [drive letter]
-c - Zero free space (good for virtual disk optimization) [Note: This option wipes free spaces with zero values.]
-p passes - Specifies the number of overwrite passes (default is 1)
-q - Don't print errors (Quiet)
-s - Recurse subdirectories [Note: The contents of all subdirectories are included in the deletion or disk wipe.]
-z - Cleans free space [Note: this is similar to the -c option except -z uses a random string of values to more securely wipe free disk space.]
Figure 1 These options should appear when “sdelete” is typed at the command line.
Of course, you can run SDelete on the PCs in your organization yourself. But you can also make the utility available to your users. Simply create a script or batch file with the appropriate command-line options and deploy that to your users to run within Windows.
Here's a great tip I found in the Sysinternals forum: Copy your SDelete script to the Windows SendTo folder on your users' PCs. Users can then shred any file by right-clicking it, moving to the Send To command, and then selecting your script.
SDelete is compatible with Windows XP and later and Windows Server 2003 and later.

Lance Whitney is an IT consultant, software trainer and technical writer. He has spent countless hours tweaking Windows workstations and servers. Originally a journalist, he took a blind leap into the IT world back in the early '90s.


Inside SharePoint Improving SharePoint Performance
Pav Cherny

Contents
Performance Criteria
Performance Interdependencies
Resolving Performance Issues
Troubleshooting Tools
Constant Monitoring
In an ideal world, you would account for SharePoint performance optimization in the planning and design stages with adequately sized and architected servers, support teams, and underlying infrastructure. But in the real world, you'll have trouble predicting user adoption rates. Your budget may be cut or staff downsized. You may inherit a poorly performing SharePoint environment. Even if your infrastructure at first meets performance expectations, growing numbers of documents, groups, lists, and sites may increase page load times and decrease satisfaction.
One of the biggest challenges you'll face in your efforts to optimize SharePoint performance will be navigating through the many configuration options that the underlying IIS, .NET, and SQL Server technologies provide during the planning and design stages, as well as in hands-on operation. The sheer number of options is daunting, not to mention trying to figure out which option is best suited to your needs. For example, SQL Server houses the vast majority of SharePoint configuration data and content, yet the search, content, configuration, and temp databases have very different read/write patterns that require appropriate disk throughput and RAM. To complicate the picture, you also can use caching in IIS or offload indexing to a front-end server to help increase disk throughput.
A second challenge revolves around determining the root causes of performance issues. SharePoint relies not only on the core SQL, IIS, and .NET components, but also on interdependencies such as Active Directory, the network, SharePoint architecture, and physical server hardware. This means a performance issue may have more than one root cause, and similarly require making multiple changes for problem resolution. Operational jobs, backup routines, and third-party tools add more possible root causes to performance issues.
In this column, I present an overview of key SharePoint architecture components, describe how they can lead to common performance issues, and discuss how to resolve and troubleshoot problems.

Performance Criteria
Before I go into the relationships between IIS and SQL Server design, configuration options, and impact on performance, let's establish the target of performance optimization. Put simply, it is improved user and administrator experience in terms of key indicators such as page load times, search, and crawling. If pages don't load fast for your users, then your effort to optimize performance by eliminating 10 round trips to the SQL Server databases doesn't matter.
When considering how quickly a page appears for a user, be sure to think about initial and subsequent load times. You might have instances in which users load a single page once but, generally, SharePoint use involves people accessing many sites and document libraries repeatedly. That's why focusing on opportunities that yield decreased load times for all page requests is so important. Keep in mind that because of browser caching, the first time a page loads the render time will be different than it is for subsequent page loads.

Performance Interdependencies
In my May 2008 column, "Building Your SharePoint Infrastructure", I covered the SharePoint architecture and explained at a basic level how IIS, SQL Server, and .NET work together to render requested pages. Now let's consider how to configure the core technologies to meet your performance needs. Figure 1 shows the key components that relate to optimization options.
Figure 1 SharePoint Architecture Components Affecting Performance
Resources
go.microsoft.com/fwlink/?LinkId=69752
SharePoint Products and Technologies Web site
microsoft.com/sharepoint
Windows SharePoint Services TechCenter
technet.microsoft.com/windowsserver/sharepoint
Windows SharePoint Services Developer Center
msdn2.microsoft.com/sharepoint
Microsoft SharePoint Products and Technologies Team Blog
blogs.msdn.com/sharepoint
In the basic request scenario, the ASP.NET page parser services the incoming request, once it's authenticated, and renders the result to the browser. The underlying content includes data from the file system and SQL Server content databases, such as list items, binary large objects (BLOBs), graphics, and text. Even submitting content to a blog or news page with a few Web parts requires verification of appropriate permissions, the page parser to compile the ASP.NET page, and multiple trips to the SQL Server for reading and writing to and from the temp, transaction logs, and content databases.
Performance issues can happen at any point in this process. For example, if the page contains many small artifacts, such as images, and your environment uses Windows NT LAN Manager and a remote domain controller (DC), then the full page will load slowly because HTTP GET requests require a round trip to the DC. The Windows NT LAN Manager authentication architecture is the constraint, not IIS or SQL Server operations. Similarly, a user might request a page with thousands of list items or import many items from a spreadsheet to a list, affecting the load times for all other users accessing sites housed on the same SQL Server.
Operational tasks and background processes, such as resource-intensive nightly backups, also can influence performance. Nightly backups can cause problems for business users in global environments that operate 24 hours every day. Background tasks impact performance because they strain system resources. For example, scheduled timer jobs, database cleanup tasks and, especially, indexing and crawling processes use large amounts of disk I/O, CPU, and RAM on front- and back-end servers.

Resolving Performance Issues
Regardless of the framework and methodology you use in trying to understand your SharePoint performance issues, isolate their root causes, and resolve the problems, you need baseline data that reflects acceptable performance against which you compare data that reflects poor performance. You can obtain a baseline set of performance data either right after your initial deployment, during periods of acceptable performance, or from best-practice recommendations. If you have no basis for comparison, then you can use Microsoft published performance recommendations for various measurements, available at technet.micro-soft.com/en-us/library/cc262787.aspx.
As I already mentioned, the most common performance issues happen as a result of interrelated components. This can be troublesome because the underlying indicators are often the same. For example, one common indicator of a performance issue is a spike in resource utilization, such as RAM, CPU, or disk I/O. To resolve the performance issue, you need to look at all available data, understand the sequence of events leading to the issue, and correlate events to determine the underlying root cause. Figure 2 shows common performance issues, possible root causes, and resolution options.
Figure 2 Issue, Cause and Resolution Summary
Issue | Possible Root Causes | Possible Resolutions | Additional Resources
High disk I/O activity on SQL Server | Large list operations, timer jobs, SQL maintenance tasks, backup, indexing, inadequate RAM, high I/O databases such as temp tlog, search, and content, placed on same disk or slow disks. | Separate temp and search databases in multiple files across high I/O disk volumes, increase RAM, use dedicated disks for transaction logs, defragment, and re-index databases weekly. | technet.microsoft.com/en-us/library/cc678870.aspx msdn.microsoft.com/en-us/library/ms175527.aspx technet.microsoft.com/en-us/library/cc263261.aspx technet.microsoft.com/en-us/library/cc262067(TechNet.10).aspx
SQL blocking/locking | NIC configuration, large list operations, indexing/crawling jobs. | Do not use SharePoint Team Services Administration (STSADM). Use SQL backup, DPM, Litespeed, or SQL 2008 with compression, ensure fill factor is set to 70% on content databases, enforce 100GB growth limit. | technet.microsoft.com/en-us/library/cc901593.aspx go.microsoft.com/fwlink/?LinkID=105623&clcid=0x409 support.microsoft.com/kb/271509 support.microsoft.com/kb/298475
Overall slow page loads | Compression not enabled. Caching not enabled or not configured. Large pages. Redundant SQL trips, underlying network issues. | Enable caching and compression, check page load times, and examine SQL queries and round trips, check NIC for Broadcom 5708 Chimney issues. | technet.microsoft.com/en-us/library/cc298550.aspx support.microsoft.com/kb/942861
Long time to load full page | Improper SharePoint object handling in custom code, slow links, SQL blocking, timer jobs, Web part caching not enabled. | Resolve back-end bandwidth and response issues, dispose of objects properly, use 64-bit hardware or configure memory pool limits, delay downloading core.js. | code.msdn.microsoft.com/SPDisposeCheck technet.microsoft.com/en-us/library/cc298550.aspx technet.microsoft.com/en-us/library/cc678870.aspx technet.microsoft.com/en-us/library/cc298466.aspx
Poor list performance | Large lists >2,000–3,000 items in a level. No indexing on lists. Underlying SQL Server issues. Too many columns. | Index on one or more columns, ensure SQL Server performance, keep fewer than 2,000–3,000 items in a level. | go.microsoft.com/fwlink/?LinkID=105580&clcid=0x409
Long crawl and index times or indexing causing sluggishness | Large data volumes require long index times, no dedicated index target. | Block with robots.txt, offload crawling/indexing to dedicated front-end server. | technet.microsoft.com/en-us/library/cc261810.aspx
LDAP operations (such as authentication and user operations) causing usage spikes | Low bandwidth, remote domain controller, large profile imports. | Increase bandwidth, use Kerberos, optimize profile importing. | support.microsoft.com/kb/827754
Backup taking too long | Using STSADM, other SQL conditions such as blocking. | Use Microsoft Data Protection Manager (DPM) or SQL 2008 with compression. | technet.microsoft.com/en-us/library/cc901593.aspx
IIS out of memory conditions | Application pool and worker process recycling, improper object handling, not enough RAM, poor load balancing architecture. | Use IIS overlapped recycling, use 64-bit hardware. | msdn.microsoft.com/en-us/library/aa720391(VS.71).aspx technet.microsoft.com/en-us/library/cc737679.aspx
As you narrow down possible causes for performance issues, keep in mind general operations best practices, such as the ones documented in the IT Showcase white paper, "SharePoint Performance Optimization." Applying the latest patches, service packs, and updates for SQL Server, IIS, SharePoint, and Windows Server is especially important. Microsoft has fixed many previous performance issues, such as tempdb allocation contention (see Concurrency enhancements for the tempdb database) and TokenAndPermUserStore cache (see Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005).

Troubleshooting Tools
You can rely on a diverse range of tools for digging down to the specifics of a SharePoint performance issue and gathering evidence that would help you make a diagnosis, determine the root causes, and resolve the problem.
The following tools are especially helpful in pinpointing causes of performance issues:
  • Fiddler PowerToy and neXpert add-on Used together, these tools provide a solid starting point for page load analysis. They allow you to review caching, compression, and overall HTTP performance. You can get more information about these tools at Fiddler PowerToy - Part 2: HTTP Performance and Microsoft neXpert Performance Analysis Plugin.
  • WireShark When you need to look into network issues, use WireShark. It works with many media, and you can capture packets from recreated TCP/IP conversations when you recreate issues. For more information, see wireshark.org.
  • Visual RoundTrip Analyzer (VRTA) You can use VRTA to examine the round-trip performance from request to response. VRTA examines the communications protocol, identifying the causes of excessive round trips, and recommending solutions. You can download it from Microsoft downloads, Visual Round Trip Analyzer.
  • SQL Profiler You can use this useful tool, installed with SQL Server, for monitoring an instance of SQL Server Database Engine or SQL Server Analysis Services. It enables you to discover issues with queries, deadlocks, timeouts, recompilations, and general errors and exceptions.
  • SQLDiag This tool, also installed with SQL Server, collects valuable information about the configuration of the computer running SQL Server, the operating system, and the information that is reported to the SQL Server error logs.
  • SQL Query Analyzer This is a low-level debug tool for analyzing query performance issues. It also is part of the SQL Server toolset.
  • SPtraceview This is one of my favorite tools because it provides a view of performance issues in real time. It's useful for monitoring diagnostic tracing when working with custom Web parts. For more information, see SPTraceView – Lightweight Tool for Monitoring the SharePoint Diagnostic Logging in Real-Time.
  • WSSDW.exe This is a performance-testing tool that populates data for testing deployments of Office SharePoint Server 2007. See Tools for performance and capacity planning (Office SharePoint Server) and SharePoint 2007 Test Data Population Tool for more information.
  • Custom tool for client-based URL ping This is one of the most useful tools because it enables the comparison of statistics before and after implementing configuration changes to the environment. See the appendix on the SharePoint Performance Optimization page.

Constant Monitoring
SharePoint performance tuning, like most things SharePoint, is complex. You need to understand the object model, details about the SharePoint architecture, and the interactions between IIS/.NET and SQL Server. You also need to know database administration and troubleshooting best practices. The good news is that if you lack this understanding, you can still do well at optimizing your SharePoint infrastructure by following established best practices, recommendations, and knowledge, and addressing the common issues pointed out here.

Pav Cherny is an IT expert and author specializing in Microsoft technologies for collaboration and unified communication. His publications include white papers, product manuals, and books with a focus on IT operations and system administration. Pav is president of Biblioso Corporation, a company that specializes in managed documentation and localization services.


Windows PowerShell Stay Seated!
Don Jones

Contents
The Solution
See It in Action
One-to-Many Remoting
Put It Out of Your Mind
It's Remote Management, New-School Style
I've always been frustrated by the dual message that sometimes comes out of Microsoft regarding server management: On the one hand, we're rightly told to install management tools on our own client computers and to use those tools to manage our servers. We're not supposed to go into the data center, and even using Remote Desktop is technically cheating because it's basically just going into the data center without taking the walk. On the other hand, however, there are many server-management tasks that can't be performed easily using existing remote tools—changing IP addresses or anything else with network configuration, for instance.
Windows PowerShell Q&A
Q Can a Windows PowerShell script be used as a logon script?
A Yes, but there might not be any point in doing so. You can't just drag the script into a Group Policy object (GPO) in the way that you can a VBScript file. Instead, you have to basically create a batch file the runs Powershell.exe, passing the command-line parameter that tells it what script to execute. It's awkward. You also have to install Windows PowerShell on every computer where that logon script will run. In the end, keep in mind that Windows PowerShell is a bit more self-contained than VBScript or the Cmd.exe shell. For example, mapping a drive using the New-PSDrive cmdlet won't affect Windows Explorer—you'd have to fall back on the Net Use command to actually map a drive in Windows, and if you're going to do that why not just use a traditional batch file, which is easier to include in a GPO?
Q Can Windows PowerShell scripts be scheduled?

A Absolutely. You'll actually schedule PowerShell.exe (which is located in your %systemroot% folder, under /WindowsPowerShell), and give it a command-line parameter specifying the script you want it to execute. Be sure that the scheduled task is set up to utilize a user account that has the necessary permissions to do whatever the script does, and make sure the shell execution policy is configured to allow script execution (I prefer the AllSigned policy myself, which means your script will need to be digitally signed; run "Help about_signing" in the shell for details on this).
Q Is there an easy way to work with file and folder permissions in Windows PowerShell?
A Sure. You've got the Get-ACL and Set-ACL commands, to start with—Get-ACL is great if all you need to do is report on permissions. Frankly, using these commands to actually change permissions is impractical—Windows' permissions are pretty complicated beasts, and the programming you have to do to set access control lists (ACLs) is equally complicated. But who says you have to use cmdlets? The shell is great at running more traditional command-line tools, and the various iterations of Cacls.exe (including ones like Dsacls for Active Directory) work great from within Windows PowerShell. I always use them when I need to modify or set file and folder permissions.
Setting up a new Server Core installation is another excellent example. Adding roles, configuring the network, even activating Windows must all be done from a local console window or via Remote Desktop, using command-line tools. What tools currently exist are either custom-written point solutions that handle a single task or solutions that require expertise with Windows Management Instrumentation (WMI). I like WMI, but frankly, it's too complex for most administrators to spend a lot of time learning, and so it often goes unused.
Windows PowerShell has promised to make all of this easier—by making WMI a bit easier to handle, but more importantly, by wrapping up administrative tasks into easier-to-use cmdlets that correspond roughly with the command-line utilities we've used for years. The shell's problem is that, aside from WMI, it's essentially a local shell. Many of its core operating system-configuration cmdlets don't provide any support for contacting a remote computer. The shell also doesn't solve one of WMI's more egregious problems: its use of Remote Procedure Calls (RPCs) for remote connectivity. RPCs are a hassle in environments that use local firewalls (and who doesn't these days?), often making WMI unusable for any kind of remote management.

The Solution
Microsoft has been aware of these shortcomings for quite a while, but it's taken some time for all the necessary fixes to line up in a single product. That product is Windows PowerShell v2, which includes a new version of Windows Remote Management (WRM). Both will ship for the first time in Windows 7 and Windows Server 2008 R2, and both will be pre-installed on those operating systems by default.
The technologies will also be made available for older versions of Windows, possibly as far back as Windows XP. But as of this writing, no official announcement has been made about exactly which older operating systems will be supported (the older the OS, the more difficult Microsoft's task, because older versions may lack some of the necessary core supporting technologies).
WinRM is really the key technology that makes all this possible. It's a Microsoft implementation of WS-MAN, or Web Services for Management, and as the name implies, it uses HTTP and HTTPS to communicate, which means that it's easy to get the traffic through a firewall. Unlike RPCs, which start on a single well-known point and then move their conversation to a randomly chosen port, HTTP and HTTPS utilize a single port—80 and 443 by default, but configurable if you don't like those. WinRM allows many different applications to "listen" for incoming management connections, and Windows PowerShell v2 is one application capable of doing so. (Personally, I think WMI might eventually migrate to use WinRM.)
Essentially, you sit on your client computer and ask it to make a shell connection to a remote computer. That activates WinRM on the remote computer, which in turn spins up an instance of Windows PowerShell v2 on the remote computer. That instance of the shell is capable of running your commands and delivering the results back to your computer.

See It in Action
The public beta releases of Windows 7 were the first time the world at large could try this out: Open a Windows PowerShell session on the server—or on multiple servers—and run Enable-PSRemoting to configure WinRM and start the WinRM service. Then, go to your client computer—also running Windows PowerShell v2—and run $session = New-PSSession computername, providing the name of the server you just configured for remoting. The New-PSSession cmdlet can also accept alternate credentials, if needed, and can be told to use non-standard ports if that's what you've configured.
If you see a lengthy error message like the one shown in Figure 1, it's because of the way WinRM authenticates. It uses Kerberos by default, but in a non-domain environment (like the one on the lab computer pictured), Kerberos isn't an option. When Kerberos isn't available, WinRM demands the use of the HTTPS transport, which requires that an SSL certificate be installed on the server computer. This is done to provide mutual authentication between you and the server so that you know you're connected to the machine you wanted. Even specifying Basic or Digest authentication in the –auth parameter won't help, because WinRM wants to use HTTPS to encrypt that traffic. The easiest solution? Be in an Active Directory domain!
Figure 1 A lengthy error message like this one may occur because of the way WinRM authenticates.
With your session activated, you're ready to begin using it. Enter-PSSession $session will connect you, live, to the remote server. Magically, you're typing in its instance of Windows PowerShell. Commands run interactively and you see the results immediately—not unlike SSH or similar technologies often used on UNIX computers.
Windows PowerShell even does some default encryption, so if you're not using HTTPS, you're still pretty safe from eavesdroppers. Run Exit-PSSession to detach from the remote console and return to your local console. The shell prompt even changes when you're connected to a remote computer, to remind you where you are.

One-to-Many Remoting
Being able to run commands on one computer is great, but how often do you need to do one thing on one server? More commonly, I find I need to run a command, or a set of commands, on a whole bunch of computers. What we've used so far in Windows PowerShell v1 is called one-to-one (1:1) remoting, meaning a single administrator managing a single remote computer. But the shell also offers one-to-many (1:n) remoting, where a single administrator can manage multiple computers. The trick is in the –computerName parameter of New-PSSession.
Let me repeat my earlier command, but this time I'll actually spell out the parameter rather than letting the shell assume it: New-PSSession –computerName computer. Because –computerName is a positional parameter, I didn't need to specify the actual parameter name earlier, but doing so now will make this trick easier to understand: $sessions = New-PSSession –computerName (Get-Content c:\names.txt). Assuming that the C:\names.txt file contains a list of computer names, one computer name per line, then the shell will create a remote session to each of them, storing the whole list of sessions in the $sessions variable.
By default, the sessions are created using whatever credentials Windows PowerShell itself is running under—so, either my logon account or an account I used with RunAs when starting the shell. A word of caution: If you have User Account Control (UAC) enabled, be sure to explicitly start the shell "as Administrator" by right-clicking its icon. Alternately, you can specify a different username for the sessions by using the command's –credential parameter.
Once you have this collection of commands, you can continue to work with them in the 1:1 fashion: Enter-PSSession $sessions[0] will connect you to the first computer's shell instance, for example. But the real power is in running a command against all of them at once: Invoke-Command –scriptblock { ipconfig } 0 –session $sessions. That runs the Ipconfig command on every computer to which you've connected a session, and brings the results back to your computer. It will actually connect to the computers in parallel, up to 32 at a time; you can change that parallel execution throttle by using the –throttlelimit parameter.

Put It Out of Your Mind
Of course, sometimes you'll fire off commands that take a while to run, and you might not want to sit around waiting for everything to complete. In such cases, why not let the shell keep working in the background? Just add the –AsJob parameter to Invoke-Command, and the shell will create a background job.
Remember that your copy of the shell isn't really doing that much work; the commands you've specified are being executed on remote instances of Windows PowerShell. Your shell is just waiting for them all to finish, and collecting the results they send back. Those results are stored as part of the job. Run Get-Job to see all current jobs and their status (whether they're still running, for example). Use Receive-Job to grab the results out of a completed job. So it all might look like this:
$sessions = New-PSSession –computerName (Get-Content c:\names.txt)
$job = Invoke-Command –scriptblock { your command(s) } –AsJob
Get-Job (to check the status)
$results = Receive-Job $job
You can then display $results, filter it, sort it, and so forth. Each result will have information attached to help you see which computer it came from. I'll discuss background jobs (which can even have sub-jobs that you have to deal with) in a future article.

It's Remote Management, New-School Style
Forget the old-school way of "remote" management that involved hiking to the data center or using a Remote Desktop "cheat." Windows PowerShell v2's remoting features are a powerful and simple way to run commands—any commands—on remote computers. Although my personal focus is on server management, these same techniques are great for desktop management tasks, as well, which is one reason why Windows PowerShell v2 is pre-installed on Windows 7 as well as Windows Server 2008 R2.

Don Jones is a co-founder of Concentrated Technology, where he blogs weekly about Windows PowerShell, SQL Server, App-V and other topics. Contact him through his Web site.


Geek of all Trades Microsoft's New Certifications: What They Are, Why They Matter
Greg Shields

Contents
New Paths for Certification
The Value of Certification
Extreme Certification
Aligning Technology with Business
I Summon the Vast Power of Certification!
You know this feeling. It’s the emotion you feel during what can be the longest wait in any IT professional’s career. Specifically, when you’re taking that certification exam, it’s the five-second eternity between clicking Finish and fi nding out your score. In those seconds, memories of weeks ormonths of late nights, studying, and hard work will probably flash through your mind as the Prometric servers calculate your test results—and your career's future.
Many questions flow through IT professionals' minds when they're taking certification exams: Do I really know the subject? Have I studied enough? Will I pass or fail? But these questions only relate to the exam at hand. Others will likely run through your mind as you go through the entire process: Will all this extra work benefit my career? Will I get a pay raise, a promotion, or a new job as my reward?
No one can definitively answer these questions. However, Microsoft's own certification program has been around for a long time, starting with its earliest exams on Windows, with literally millions of IT professionals certified in the following years. By passing Microsoft's series of exams, you prove to yourself, your employer and your peers that you deeply understand the underlying technology in your Windows environment. You also prove that you understand the job role that's been assigned to you as an IT professional.

New Paths for Certification
One important step on the path from zero to certification hero is understanding all the the steps required to meet your goals. This understanding is critical because of the sheer volume of exams available in Microsoft's testing portfolio. Taking the wrong exam wastes time and effort. Thus, your first challenge is mapping out the certification path that makes the most sense for your job role.
First, a little history: Microsoft's certification program has grown and expanded dramatically in the years since the first Microsoft Certified Professional (MCP) exams were offered. Tests are regularly released and later retired as the technologies involved come and go.
Even entire certifications have seen their lifecycle fully realized. Most notably, the venerable Microsoft Certified Systems Engineer (MCSE) credential is gone for all iterations but Windows Server 2003. Why eliminate the MCSE, long the goal of virtually every Microsoft cert-seeker? In many ways, the premiere certification met its end because of its name. The term "engineer" is a protected word in many states and countries, holding a specific meaning that wasn't part of Microsoft's testing goals. Its use caused a legal exposure for Microsoft and its testing program.
But the name wasn't the MCSE's only problem. The original MCSE was a first step in providing a premiere certification that demonstrated to employers what you knew. What it didn't do well was focus on specifics. Once you obtained the MCSE, that credential was bestowed regardless of which path you used to get there. Netting that MCSE required taking a series of specified exams along with an additional set of electives. Nowhere in your MCSE credential was it easy to see that, for instance, your experience focused more on Exchange than on clustering or on SQL than on IIS.
Replacing this monolithic MCSE is a new certification program that's designed to better identify where your skills lie. The new program also includes additional tiers designed to validate your maturity and experience with Microsoft's technology.
Microsoft's new certification path starts with the Microsoft Certified Technology Specialist (MCTS) certification, which is bestowed upon individuals who take one or more exams focusing on a specific Microsoft technology. These exams might relate to a particular product and version, such as Exchange Server 2007 or SQL Server 2008.
In most cases, taking an MCTS exam in one product/version combo nets you its MCTS certification. For example, if you pass the exam 70-642, "MCTS: Windows Server 2008, Networking Infrastructure, Configuring," the actual certification bestowed will be the MCTS: Windows Server 2008 Network Infrastructure Configuration. As you can see, with each MCTS exam you take, you also increase the number of MCTS certifications that you've achieved.
While industry newcomers might spend all their time accomplishing specific stated tasks directed by someone else, senior professionals find themselves moving toward the larger needs of solving business problems.
The Microsoft Certified IT Professional (MCITP) certification is designed for just those individuals. The MCITP consolidates a series of task-focused MCTS exams beneath a single job-role-focused MCITP exam. While not necessarily a capstone, this job-role exam tests whether an IT professional can analyze business requirements with an eye toward building an architecture that fulfills their needs. Passing the MCITP's job-role examination requires the knowledge and experience gained through a higher-level engineering of IT systems.
For Windows Server 2008, two MCITP credentials are currently available, with others still in progress. The MCITP: Server Administrator requires two MCTS exams and a single job-role exam (see Figure 1). It's designed as the core credential for senior professionals who primarily work with servers. Those who work in larger, more complex environments can further validate their knowledge and experience by obtaining the MCITP: Enterprise Administrator credential. This credential requires four MCTS exams as well as its own separate job-role exam (see Figure 2).
Figure 1 The MCITP: Server Administrator Roadmap
Figure 2 The MCITP: Enterprise Administrator Roadmap
Other as-yet-unreleased credentials are the MCITP: Enterprise Desktop Administrator and MCITP: Enterprise Desktop Support Technician, both scheduled to become available later this year. These credentials offer a specific focus for individuals whose job focuses less on an environment's server infrastructure and more on its desktops. Their required exams will validate an individual's understanding in topics such as deployment and desktop management as well as the ability to successfully resolve desktop issues. Another planned release is the MCITP: Virtualization Administrator, although details about this Hyper-V-focused exam remain unclear.
In looking through these certifications and their requirements, you'll notice that the new MCITP credentials specifically tailor your stated certification toward what you do in your daily job. As a result, the new certifications make it much easier for current and potential employers to understand exactly where your skills lie.
One critical—and, in some circles, controversial—new facet of Microsoft's certification program is the introduction of exam expiration. As mentioned earlier, MCTS exams test not only a technology, but a version of that technology as well. For that reason, all MCTS exams will eventually expire as the technology they test against goes out of its lifecycle, typically 7 to 10 years after the product's initial release. This means that the MCTS on Exchange Server 2007 that you take today will evaporate at some point in the product's future, taking your MCTS certification with it. In some cases, this will also affect your MCITP. Microsoft will provide an upgrade path to maintain your certification status when that time comes.

The Value of Certification
Despite all this information about certifications and exams, nagging questions remain about the ultimate benefit of all that work. Studying and sitting for the five exams for the MCITP: Enterprise Administrator is only worth the effort if you stand to gain from the experience.
Some organizations, particularly those in larger enterprises, have direct benefit programs in place for individuals who successfully obtain certifications. But in today's economic climate, these cash benefit programs are likely to keep shrinking. At the same time, however, a challenging economy provides the perfect opportunity to validate to your employer—or possible future employers—that you know the right stuff to do your job.
In December 2008, technology research giant IDC commissioned a study looking at the organizational performance of more than 2,000 teams of IT professionals. The study concluded, quite impressively, that: "Unequivocally, certification, as a measure of skill, showed a positive correlation to performance improvement." Researchers also stated that 75 percent of IT managers studied believe that certification is important to team performance and 66 percent believe that certification improves the level of service and support offered to IT customers.
While these results don't directly state that certification will help you get that promotion or pay raise, they do indicate that those who certify provide greater benefit to their businesses. In today's economy, that can be a significant competitive advantage.
One statistic relating to the tangible benefits resulting from a completed certification path comes from the 2007 MCP Customer Satisfaction Survey. In that survey, 71 percent of respondents said they believe that Microsoft certification helped them receive promotions or raises from their current employers. In a 2006 Redmond magazine survey of 1,280 IT professionals, 43 percent of respondents reported salary increases associated with obtaining Microsoft certifications. Forty percent cited certification as a factor in improving their ability to find or keep jobs and obtain promotions.
In addition, I've personally experienced the power of certification as a career-enhancing activity. Having sat for more than 30 exams over my nearly 15-year career, half of which were a part of the Microsoft program, I've seen how certification has advanced my own career in many ways. Though the return isn't always immediate, certification has provided a much-needed foot in the door in numerous situations. As a technical manager, I've also observed the higher level of professional maturity and skill brought to the table by certified individuals. In my opinion, certification works.

Extreme Certification
In creating a comprehensive certification program that shows exactly what you know, Microsoft has also created one with a measure of complexity. The MCTS and MCITP certifications discussed above are only a small fraction of those available today. MCITP credentials are also available with focuses on database administration, database development, business intelligence development, enterprise messaging and enterprise project management. Each is yet another manifestation of the technology-plus-job-role focus explained above for server and enterprise administrators. With all these options on the table, it's especially important to clearly understand each certification's requirements before you start.
For deeply experienced individuals in search of "extreme" levels of skills validation, Microsoft provides its Microsoft Certified Master (MCM) program. The MCM is designed to reflect individuals with skills and experience at a sort of "super-MCTS" level. These extremely highly skilled individuals have experience in niche topics associated with the particular technology that relates to their MCM certification. According to Microsoft Senior Certification Manager Jim Clark, MCMs are "the top 1 percent of the consultants out there. Out of 6.2 million MCPs in the world, only 400 of them are MCMs."
Like MCTS credentials, MCM certifications are both technology- and version-focused. Currently, only five MCM credentials are available: Exchange Server 2007, SQL Server 2008, Windows Server 2008: Directory, Office SharePoint Server 2007 and Office Communications Server 2007.
"Although we present our certifications as following each other, with the MCM, they don't necessarily. You don't exactly go from MCTS to MCITP to MCM," Clark explains. "Without the context of having experience in extremely niche situations [such as enterprise deployments with complex infrastructures], candidates won't do well in attempting the MCM."
One reason for this disconnect between the MCITP and the MCM has do to with certification requirements. Obtaining the MCM requires a dramatically larger investment than either the MCTS or MCITP. To obtain the MCM, candidates must attend three weeks of required learning sessions and complete in-class written and lab exams along with qualification lab exams. The requirements are intensive, as is the workload required of candidates. As a result, however, successful MCMs can expect the highest levels of professional respect from their businesses, their peers and, in the case of consultants, their clients.

Aligning Technology with Business
While it would be inappropriate to call it the highest tier of Microsoft's certification program, the Microsoft Certified Architect (MCA) could be called its final tier. The MCA program is designed as the process-oriented equivalent to the MCM's technology focus. Microsoft's MCA program is extremely exclusive, requiring the preparation of a competency document and a review board interview before a professional can even register for the program.
Unlike each of Microsoft's other certifications, the MCA isn't exam-centric. To obtain the credential, candidates undergo a three- to six-month process during which they prepare documentation that includes work history, an architectural solution case study, and information on instances where program competencies have been used in production. The MCA is a board-certified credential, with the "final exam" being a review-board interview. In those interviews, candidates are required to defend their documentation in much the same way board-certified physicians complete their training.
Functionally different in its goals than the MCM, the MCA is intended for those who focus on aligning technology with business processes. As Clark puts it: "MCAs talk about solving business goals at a very high level, without actually focusing on the technology itself. It's the job of the MCTSs and MCITPs to actually solve the problem. If you consider the MCMs as the 'super-MCTS,' then you could consider the MCA as the 'super-MCITP.'"

I Summon the Vast Power of Certification!
Most IT professionals will never elevate their knowledge and experience to the level of an MCA or even an MCM. But these highest-end credentials exist to provide a stepping stone for those who want to validate their experience at the highest levels. Most of us will fulfill our own career goals by maintaining our technology and job-role certifications at the levels that make sense for us.
In the end, that's what is primarily important when it comes to choosing the certification path: ensuring that your level of certification keeps pace with your individual job responsibilities as well as your overall career. Maintaining the correct level of skills validation is the key to guaranteeing the best return on your certification investment.

Greg Shields, MVP, is a partner at Concentrated Technology. Find more of Greg's tips and tricks at ConcentratedTech.com.


Windows Confidential Windows 'Prettified' Filenames
Raymond Chen

The introduction of long filenames and lowercase to the FAT file system back in Windows 95 also introduced a transition problem—all files on FAT drives created prior to that point conformed to the old 8.3 file-naming convention— which meant that they were all in uppercase. If Windows hadn't taken any special steps, then using Explorer to visit a folder filled with those old-style names would have resulted in a full-frontal assault by a screen full of uppercase letters.
Nothing screams "I'm an old computer from the 1950s" like filenames in uppercase. (Well, OK, we could also have put line numbers next to each file, too.) Instead of looking like a fresh, modern operating system, Windows 95 would have looked like an operating system written in FORTRAN on punch cards by a bunch of nerds, whereas in reality, Windows 95 was written in a mixture of C, C++, and assembly language on PCs by a bunch of nerds.
To smooth the transition to the exciting new world of lowercase letters, Explorer performed an operation known as "prettifying." If it encountered a filename that was all uppercase and conformed to the 8.3 naming convention, it said, "Oh, this must be one of those files created in the Before Time. Let me put some lipstick on it before introducing it to polite lowercase society." The first character was left uppercase and the remaining characters were converted to lowercase. A file whose name on disk was README.TXT was displayed on screen as Readme, or Readme.txt if extensions for known file types were being shown. Since the FAT file system is case-insensitive, the new name worked just as well for accessing the file, and it looked far less hideous. This was just one of many little touches added to the Windows 95 user interface to make it feel more comfortable and less computery.
This prettification process took place regardless of the underlying file system. Even if the file system supported lowercase natively, Explorer still checked whether the name was ugly. This was done because the all-uppercase name very well may have started out on a file system that supported only ugly names, but when the file was copied to a drive that supported pretty names, the name didn't get prettified. After all, the COPY command doesn't do anything with the filename; if you copy an ugly file, the copy is also ugly!
In Windows 95, prettification of file names was unconditional; there was no way to turn it off. If you had a file named FBI, it would get prettified to Fbi. There was a trick, though: If your file had an extension, you could convert the extension to lowercase while keeping the base name all uppercase. The presence of lowercase in the extension prevented it from being recognized as an old-school filename, so your file FBI.txt retained its original case. After extensions for known file types were hidden, the result was an all-uppercase FBI.
When the Explorer interface was ported to Windows NT 4.0, the prettification feature was preserved, but the Windows NT porting team added an option to disable the feature. The option is now interesting only for historical reasons, because prettification of filenames vanished entirely starting with Windows 2000. Network server names were also subject to prettification up to and including Windows XP.
Now that prettification of filenames has been turned off in all versions of Windows still in mainstream support, you can give your files all-uppercase names with impunity. And, if you still have files created from the days of ugly filenames, those files will be displayed with the original names in their full ugliness. Maybe that's not so bad—that wall of all-uppercase filenames may just bring on the nostalgia.

Raymond Chen's Web site, The Old New Thing, and identically titled book (Addison-Wesley, 2007) deal with Windows history and Win32 programming. He still has a lot of old files on floppy disks, but no longer has a working floppy drive that can read them.