 TechNet Magazine April 2008

TechNet Magazine April 2008

SQL Server 2008: What's New

The new version of SQL Server offers a host of new features and enhancements that promise to improve performance, increase security, and make life better for database administrators. Here's an overview of the key changes and what you can expect from SQL Server 2008.

Randy Dyess SQL Server 2008: Security

Security continues to be an area of deliberate improvement for SQL Server. Explore some of the most important security-related changes you'll find in SQL Server 2008, from encryption and authentication enhancements to Policy-Based Management and the new auditing system that will help with regulatory requirements.

Rick Byham SQL Server 2008: New Data Types

SQL Server 2008 offers seven new built-in data types that let you work with more complex data and simplify the management of structured, semi-structured, and even unstructured data. Get an in-depth look at these new data types and find out how you can use them to improve data management in your infrastructure.

Kelly Wilson SQL Server 2008: Data Warehouse Query Performance

SQL Server 2008 offers more powerful relational data warehousing capabilities. Take a close look at key performancerelated data warehousing features and find out how you can use all this new technology to optimize query performance.

S. Agarwal, T. Grabs, and J. Hammer SQL Server: Minimize Blocking in SQL Server

While locking is essential to support concurrent read and write activities on a database, blocking can adversely affect system performance. Learn how to optimize your SQL Server database to minimize blocking, and see how you can monitor the system to better understand how locking impacts performance.

Cherié Warren Microsoft Office: Integrating Access Databases with SharePoint

Many businesses still rely on Microsoft Access. Fortunately, Microsoft Office SharePoint Server 2007 allows you to integrate legacy Access data. Explore how you can use these tools to build new solutions for your organization.

Peter Serzo From the Editor: To Friends, Both Old and New

Joshua Hoffman Letters: Readers Speak Out

Toolbox: New Products for IT Pros

Greg Steen SQL Q&A: Grow Databases, Use IFilters, and Connect Remotely

Find out how to grow databases programmatically, learn how to use IFilters, and explore firewall settings that support remote SQL Server connections.

Edited by Matthew Graven Utility Spotlight: Web Capacity Analysis Tool

Web logs and traffic analysis can give you a good idea of the kind of traffic you can expect over time. But how can you determine what your infrastructure can handle? The free Web Capacity Analysis Tool can help you to see how your systems will perform under stress.

Greg Steen The Cable Guy: Troubleshooting NAP Enforcement

Troubleshooting enforcement behaviors in the Network Access Protection platform can be challenging. The Cable Guy explains how NAP health policy evaluation works and how you can troubleshoot the most common issues.

Joseph Davies Windows PowerShell: Sign Here, Please

The AllSigned execution policy provides the most secure Windows PowerShell environment. So how do you sign your Windows PowerShell scripts? Don Jones shows you how.

Don Jones Hey, Scripting Guy!: The Return of the Database Script

An overview of signing your scripts for improved security.

The Microsoft Scripting Guys Security Watch: Where Is My PII?

Before you can effectively protect your Personally Identifiable Information, you must know where it's stored on your PC. Here's a look at how to write scripts that will locate personal data stored on your computer. You just might be surprised at what these scripts uncover.

Frank Simorjay The Desktop Files: Deploying Windows XP with the WAIK

The Windows Automated Installation Kit was designed primarily for Windows Vista and Windows Server 2008. But did you know that it can also help you deploy Windows XP and Windows Server 2003? Here's what you need to know to get started.

Wes Miller Field Notes: A MARvellous Initiative

See how the new Microsoft Authorized Refurbisher (MAR) program can help companies deal with confusing software licensing issues when refurbishing old PCs.

Neil Fawcett Windows Confidential: Share and Share Alike

"Are you sure you want to remove this shared file?" Raymond Chen explains why an uninstaller may ask this question when you remove a program.

Raymond Chen


 SQL Server 2008: What's New
SQL Server 2008 Randy Dyess

What's New
At a Glance:
  • More powerful management
  • Improved performance and scalability
  • Better security and availability
  • Changes for developers

Once again, Microsoft has released a new version of SQL Server that promises to improve life for database administrators. The latest version is SQL Server 2008, and it offers a large variety of new
features and functionality that will make the administration a whole lot better.
Database administrators who have been using SQL Server® 2005 for any length of time will find many of the same features that they use in their day-to-day jobs, but they'll also soon discover that these familiar tools have been enhanced in SQL Server 2008. The new functionality built on the existing features goes a long way in reducing the number of workarounds or customizations once needed for using various features in complex database environments.
New features in SQL Server 2008 involve a large range of database job roles, making it difficult to categorize them. As I classify features under different topics, I realize that some readers will be wondering why I placed Feature X under Category Y when it clearly belongs under Category Z. It's a matter of perspective, and it's affected by how your company does business.
I do understand that database people often find themselves doing many different job roles, but I will try to break down the new features so they fit into the following generic categories: Management, Scalability, Performance, High Availability, Security, Development, and Business Intelligence.

What's New for Management?
For database administrators (like me), the additional management functionality makes SQL Server 2008 a very exciting new product. The new policy management, multiple server query capability, configuration servers, and data collector/management warehouse offer powerful new abilities for database administrators who are often responsible for managing large and complex database environments with hundreds or thousands of databases on dozens or even hundreds of servers.
The SQL Server 2008 Policy Management feature, which was actually called Declarative Management Framework in the Community Technology Previews (CTPs), allows you to create and execute configuration policies against one or more database servers. With these policies, you can ensure that standard configuration settings are applied and maintained on each of the targeted servers and databases. You can see an example of this feature in Figure 1.
Figure 1 The Data and Log File Location Best Practice policy
Figure 1 The Data and Log File Location Best Practice policy
Policies are created from a predefined set of facets. Each facet contains a subgroup of SQL Server 2008 configuration settings and other events that you can control. You pair these facets with conditions in order to create a policy. Conditions are the values that are allowed for the properties of a facet, the configuration settings, or other events contained within that facet.
Conditions are also values used for policy filters. Say you want the policy to be executed only against a certain database. In this case, you can create a condition that contains the name of the database and then add this condition to the policy. Now the policy will only apply to that one database. Trust me on this—SQL Server 2008 Policy Management may sound complex, but once you try it you'll realize it's pretty intuitive.
The new Multiple Server Interaction and Configuration Servers capabilities come in handy when you need to execute queries against multiple servers at the same time. You can register servers in your Management Studio and then place those servers together under a grouping. When you need to execute a policy or query against all the servers in the grouping, you simply right-click on the grouping and do so.
As an added benefit, you can configure this feature to return one resultset per server or merge all the resultsets together into one big resultset. You can also specify whether you want the server and database names as part of the results so you can separate the individual results from each server. Being able to store the registered servers on the configuration server rather than in each individual's Management Studio is a big benefit.
Another nice new management feature is the Data Collector. Database administrators often need to collect management data from a large number of servers, and many of these DBAs have created their own custom solution for doing so. The Data Collector is a built-in mechanism that eases the task of collecting management-related data. It allows you to use the SQL Server Agent and SQL Server Integration Services (SSIS) to create a framework that collects and stores your data while providing error handling, auditing, and collection history.
Unlike third-party tools and custom jobs, the Data Collector will be easily understood by most database administrators since it uses SQL Server Agent and SSIS to create a set of jobs and packages to handle the connections, collection, and storage of data (as you can see in Figure 2). Once this data is stored in a central location, referred to as the Management Warehouse, it can be viewed and organized through a set of T-SQL statements and SQL Server 2008 Reporting Services reports. This central data store makes it much easier to analyze and view the overall management metrics of the database environment.
Figure 2 The Data Collector Disk Usage Log file
Figure 2 The Data Collector Disk Usage Log file

What's New for Scalability?
Over the years, SQL Server database administrators have found their database environments becoming increasingly large. As the size of your database environment increases, you need new methods and tools to achieve the scalability that most enterprises require. SQL Server 2008 has introduced several new features that will help.
SQL Server 2008 has built-in compression that allows you to compress the database files and the transaction log files associated with the compressed database. SQL Server 2005 introduced the ability to compress data on a read-only file or filegroup, but this form of compression simply used the compression ability of Windows® NTFS. With SQL Server 2008, you now get both row-level and page-level compression, offering benefits you don't get with compression at the data file level.
Compression at the row and page levels reduces the amount of data space needed, plus it reduces the amount of memory needed since the data remains compressed while in memory. Compressed data in memory results in increased memory utilization, which benefits the scalability of many systems.
SQL Server 2008 also introduces compression at the backup level. While database backups only back up the active portion of the database, this still represents as many as hundreds of gigabytes or even dozens of terabytes. In database environments that have more than one copy of a multi-terabyte backup file, these backups often take up valuable storage space that could be used more effectively. By allowing database administrators to compress their backup files, SQL Server 2008 frees up some of this space, so it can be used for live data.
Then there's the Resource Governor. This new feature lets you define the amounts of resources that individual or groupings of workloads are allowed to use during execution. With Resource Governor, you can create an environment in which many different workloads coexist on one server without the fear of one or more of those workloads overwhelming the server and reducing the performance of the other workloads.
The benefit of this feature is that you can more effectively use the total amount of resources that are available on your database servers. Figure 3 shows an example of using the Resource Governor to limit activity on a server.
Figure 3 Limit activity with the Resource Governor (Close Figure 3)
USE master
go

--Drop function
IF OBJECT_ID('rgclassifier_demo','Function') IS NOT NULL
DROP FUNCTION rgclassifier_demo
go

--Create a classifier function for report group
CREATE FUNCTION rgclassifier_demo() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @group_name AS SYSNAME
  IF (USER_NAME() LIKE '%Launch_Demo%')
         SET @group_name = 'demogroup'
    RETURN @group_name
END
GO

--Drop workload group for anything coming from Management Studio
IF EXISTS (SELECT name FROM sys.resource_governor_workload_groups
  WHERE name = 'demogroup')
BEGIN
  DROP WORKLOAD GROUP demogroup
END
GO

--Create workload group
CREATE WORKLOAD GROUP demogroup
GO

--Register the classifier function with
--Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_demo)
GO

--Alter the dbogroup workload group to only
--allow 10% of CPU for each workload request
ALTER WORKLOAD GROUP demogroup
WITH (REQUEST_MAX_CPU_TIME_SEC = 10)
GO

--Create a new resource pool and set a maximum CPU limit for all workloads.
IF EXISTS (SELECT name FROM sys.resource_governor_resource_pools
  WHERE name = 'pooldemo')
DROP RESOURCE POOL pooldemo
GO

CREATE RESOURCE POOL pooldemo
WITH (MAX_CPU_PERCENT = 40)
GO

--Configure the workload group so it uses the
--new resource pool.
ALTER WORKLOAD GROUP demogroup
USING pooldemo
GO

--Apply the changes to the Resource Governor
--in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

What's New for Performance?
The general performance of databases improves with SQL Server 2008. Thanks to several new features found in SQL Server 2008, you can control and monitor the performance of your databases and the applications that execute against them.
When you have large numbers of transactions performed every second, the locking that normally occurs during these transactions can have a negative impact on the performance of your database applications. SQL Server is designed to reduce the total number of locks a process holds by escalating locks from the smaller row-level and page-level locks to large table-level locks. But it's important to understand that this escalation of locks can cause problems. For example, a single transaction can lock an entire table and prevent other transactions from working with that table.
SQL Server 2008 works with the table partitioning mechanism (which was introduced in SQL Server 2005) to allow the SQL Server engine to escalate locks to the partition level before the table level. This intermediary level of locking can dramatically reduce the effects of lock escalation on systems that have to process hundreds and thousands of transactions per second.
SQL Server 2008 offers several new query processor improvements for when the query interacts with partitioned tables. The query optimizer can now perform query seeks against partitions as it would against individual indexes by only working with the partition ID and not the partitioning mechanism at the table level.

What's New for High Availability?
As database environments become more complex and databases grow in size, the ability to ensure the availability of those databases becomes increasingly difficult. The familiar mechanisms you have used in the past to achieve high availability are still present in SQL Server 2008. But some of these features have been enhanced in SQL Server 2008 and some new ones have been added.
With SQL Server 2005, many administrators started implementing database mirroring to achieve high availability. SQL Server 2008 offers many improvements for the practice of database mirroring. For instance, in the past, database mirroring occasionally had performance issues related to moving transaction log data from the principal to the mirrored databases. In response, SQL Server 2008 now reduces the amount of information that is moved across the network from the principal's transaction log to the mirror's transaction log by compressing the information before sending it to the mirror's transaction log for hardening.
You now have the ability to repair corrupted data pages on the principal. If a principal database suffers corrupt data pages due to errors 823 and 824, the principal can request a fresh copy of those data pages from the mirrored servers. This request of good data pages is an automated process that is transparent to any users who are currently accessing the principal databases.
Another new feature, Hot Add CPU, lets you add additional CPUs to a database server without affecting the availability of the databases residing on that server. However, you should know that Hot Add CPU does have some limitations, as it is only useful when running the 64-bit Itanium-based Windows Server® 2008 Enterprise Edition or Datacenter Edition, and it requires the Enterprise Edition of SQL Server 2008.

What's New for Security?
SQL Server 2005 introduced data security in the form of data encryption. With SQL Server 2008, encryption is greatly enhanced with the introduction of two features: Extensible Key Management and Transparent Data Encryption.
Extensible Key Management allows for an enhanced structure to safely store the keys used in the encryption infrastructure—not only in the database itself but also outside the database in third-party software modules or with a Hardware Security Module.
Transparent Data Encryption offers improved flexibility for encrypting data by allowing encryption to be a property of the database and not just the result of functions in a line of code. The result is that administrators do not have to perform the large number of changes that are required for their database structure and application code when they perform encryption at the data level. The code in Figure 4 shows how you can encrypt a database with Transparent Data Encryption.
Figure 4 Using Transparent Data Encryption (Close Figure 4)
USE master;
GO

--Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YouWillNeedToCreateAStrongPassword';
GO

--Create a certificate to use with TDE
CREATE CERTIFICATE TDECERT WITH SUBJECT = 'TDECert'
GO

--Change to the database to encrypt
USE AdventureWorks
GO

--Create your database master key
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128 --Use a strong algorithm
ENCRYPTION BY SERVER CERTIFICATE TDECERT
GO

--Alter the database to encrypt it with the
--master database key
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON
GO

What's in Store for Developers?
Database administrators are not the only people that will benefit directly from the changes in SQL Server 2008. There are a number of new features that are designed to help database developers. These range from several new T-SQL enhancements to new components that can help developers create and utilize database queries.
Many database developers are responsible for creating the queries that are needed for returning the data required for their applications. You're probably familiar with the LINQ (Language Integrated Query) tool that enables database developers to issue queries against a database using a Microsoft® .NET-based programming language instead of the normal T-SQL statements. Well, SQL Server 2008 enhances LINQ by providing a new LINQ to SQL provider that allows developers to issue LINQ commands directly against SQL Server tables and columns. This will reduce the amount of time it takes to create new data queries.
When developing against databases, developers use higher-level objects that they map to individual database tables and columns. These objects, also known as entities, represent the data needed for database applications and, therefore, the developer doesn't need to understand the actual storage structure of the data and schema of the database. The new ADO.NET Entity Framework now allows developers to create database queries using these entities. The abstracting of the underlying database structure allows developers to be more productive.
SQL Server 2008 offers many different enhancements to T-SQL that allow database developers to be more efficient. One example is the new MERGE statement, which allows the developer to check for the existence of data before trying to insert the data. This check prior to performing the INSERT statement allows the data to be updated. No longer is it necessary to create complex joins in order to update data that exists and to insert data that does not already exist, all during a single statement.
In addition, separating time and date data from the combined date/time data type has been made easier. SQL Server 2008 introduces two separate data types to handle date and time data. Different data types will translate to improved performance for many queries since there will no longer be a need to perform an operation on the data before it can be used in the query.
When creating newer database structures, database developers often find themselves stretching the structure of databases in order to implement mapping applications. SQL Server 2008 helps to address this issue with new spatial data types. The two spatial data types, GEOGRAPHY and GEOMETRY, allow developers to store location-specific data directly into the database without having to break those data elements down into formats that fit other standard data types. The code in Figure 5 is an example of a simple spatial table.
Figure 5 A simple spatial table (Close Figure 5)
IF OBJECT_ID ( 'Demo_SpatialTable', 'Table' ) IS NOT NULL
    DROP TABLE Demo_SpatialTable
GO

--Create table to hold spatial data
CREATE TABLE Demo_SpatialTable
    ( SpatialID int IDENTITY (1,1),
    SpatialInputCol geography,
    SpatialOutputCol AS SpatialInputCol.STAsText() )
GO

--Insert data into table
INSERT INTO Demo_SpatialTable (SpatialInputCol)
VALUES (geography::STGeomFromText('LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326));

INSERT INTO Demo_SpatialTable (SpatialInputCol)
VALUES (geography::STGeomFromText('POLYGON((47.653 -122.358, 47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))', 4326));
GO

--View data to see that data has been converted and stored in col2
SELECT * FROM Demo_SpatialTable
A very common issue for database developers in the past was how to store and utilize large binary objects such as documents and media files. The method typically used was to store the files outside of the database and just store a pointer in the database to the external file. With this method, however, when you move the file, you must also remember to update the pointer.
SQL Server 2008 handles this issue with the new FILESTREAM data type. With this data type, files can still be stored outside of the database, but the data is considered part of the database for transactional consistency. This allows for the use of common file operations while still maintaining the performance and security benefits of the database.

What about Business Intelligence?
Increased use of SQL Server over the last few years has been driven in large part by the adoption of business intelligence strategies. Business intelligence capabilities are not new to SQL Server, but SQL Server 2008 does bring some new features to the table.
For example, when data is stored in data warehouses, space is often wasted due to NULL values. Columns that store NULL values take up the space of the largest allowed data size defined in the column. This means that a column with thousands of NULL values can actually consume many MB of space without actually storing any data.
SQL Server 2008 introduces sparse columns, which allows NULL values to be stored without taking up any physical space on the disk. Because sparse columns do not consume actual space, tables that contain sparse columns can actually exceed the 1,024 column limit.
SQL Server 2008 also introduces a new mechanism, Change Data Capture, for managing incremental changes that need to be loaded into the data warehouse. This captures and places changed data into a set of change tables. Capturing updated, deleted, and inserted data in an easily consumed storage schema allows for the incremental loading of data warehouses from those tables—as opposed to having to build custom insert statements that try to figure out the changes made to existing rows of data before updating the data warehouse.

Wrapping Up
This is just a quick overview of what SQL Server 2008 has in store. It will bring a broad set of new features and updates to existing features that will improve life for both database administrators and database developers. Ultimately, it will offer much improved performance and scalability for today's ever-demanding databases.

Randy Dyess is a mentor with Solid Quality Mentors. He specializes in SQL Server OLTP systems. Randy is the author of TransactSQL Language Reference Guide, coauthor of MCTS Self-Paced Training Kit: Microsoft SQL Server 2005 Implementation and Maintenance (Exam 70-431), and numerous magazine and newsletter articles. Randy is also the Director of Programs for the North Texas SQL Server Users Group and is a SQL Server MVP. Visit his blog at blogs.solidq.com/EN/rdyess/default.aspx.
© 2007 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.


 SQL Server 2008: Security
SQL Server 2008 Rick Byham

Security
At a Glance:
  • Enhancements to encryption
  • Enhancements to authentication
  • Security auditing
  • Policy-based management

SQL Server 2008 delivers many enhancements and new features that are designed to improve the overall security of your database environment. It adds key encryption and authentication capabilities and introduces a new auditing system to
help you report on user behavior and meet your regulatory requirements.
In this article, I'll give you an overview of the most important changes you'll find in store for SQL Server® 2008 security. One of the first things you might notice is that the SQL Server 2005 Surface Area Configuration tool has been discontinued. The protocol options that were exposed by the Surface Area Configuration tool are now available in the Configuration Manager tool. However, enabling and disabling features is now done using the new SQL Server 2008 Policy-Based Management framework.

Enhancements to Encryption
There are two major improvements in the area of encryption. First, SQL Server can now use encryption keys stored on an external third-party hardware security module. And second, data stored in SQL Server can be encrypted in a method that is transparent to the applications that connect to the database. This means database administrators can easily encrypt all of the data stored in an entire database without having to modify existing application code.
The first improvement is made possible by the new Extensible Key Management (EKM) feature, which is available in the Enterprise, Developer, and Evaluation editions of SQL Server 2008. EKM enables third-party vendors of enterprise key management and hardware security module (HSM) solutions to register their devices in SQL Server. Once these devices are registered, users can use the encryption keys stored on these modules.
These vendors can even expose advanced encryption features (such as key aging and key rotation) in these modules. In some configurations, this allows for data protection from database administrators who are not members of the sysadmin group. The T-SQL cryptographic statements can then encrypt and decrypt data using the keys stored on the external EKM device.
Another new feature, transparent data encryption, allows you to encrypt database files without having to alter any of your applications. It performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is secured with a certificate stored in the master database of the server. The diagram in Figure 1 illustrates the architecture that enables transparent data encryption.
Figure 1 Architecture of transparent data encryption
Figure 1 Architecture of transparent data encryption
This is useful for protecting data at rest. While you can take several precautions to help secure your database, such as encrypting confidential assets and placing a firewall around the database servers, the physical media on which the database is stored (even backup tapes) offers a different vulnerability. A malicious user could steal this media and potentially access the stored data.
Transparent data encryption, however, lets you encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This can help your organization comply with many laws, regulations, and industry guidelines regarding the appropriate protection of data.
Transparent data encryption allows software developers to encrypt data using Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) encryption algorithms. Encryption of the database file is performed at the page level, with pages being encrypted before they are written to disk and then later decrypted when read into memory. Backup files of databases that have transparent data encryption enabled are also encrypted by using the database encryption key.
To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. Without the certificate or asymmetric key, the database can't be restored. So be sure to retain any keys as long as you may need access to related backups.

Enhancements to Authentication
As you probably know, Kerberos is a network authentication protocol that is used to provide a highly secure means to mutually authenticate client and server entities (or security principals) on a network. Kerberos helps users mitigate security vulnerabilities, such as luring and man-in-the-middle attacks. Relative to Windows® NTLM authentication, Kerberos is more secure, more robust, and offers better performance.
To authenticate a connection mutually using Kerberos, the Service Principal Names (SPN) of a SQL Server instance must be registered in the Active Directory®, and a client driver must provide a registered SPN when connecting. In SQL Server 2008, Kerberos authentication has been expanded to all network protocols, including TCP, Named Pipe, Shared Memory, and Virtual Interface Adapter (VIA). By default, the client driver automatically infers a correct SPN for a SQL Server instance to which it connects. You can also explicitly specify an SPN in the connection string parameter for better security, control, and troubleshooting.
Internet Information Services (IIS) no longer provides access to ASP.NET, Report Manager, or the Report Server Web service. In SQL Server 2008, Reporting Services handles all authentication requests through a new authentication subsystem that supports Windows-based and custom authentication.
Reporting Services now hosts the Microsoft® .NET Framework and ASP.NET technologies built into the SQL Server common language runtime (CLR), and Reporting Services also uses the HTTP.SYS capabilities of the OS. The Report Server includes an HTTP listener that accepts requests that are directed to a URL and port that you define during server configuration. URL reservations and registration is now managed directly by the report server through HTTP.SYS.

Security Auditing
SQL Server Audit is a new feature that will let you create customized audits of database engine events. This feature uses extended events to record information for audits, and it provides the tools and processes you need to enable, store, and view audits on various server and database objects.
SQL Server Audit is also faster than SQL Server Trace, and SQL Server Management Studio makes it easy to create and monitor the audit logs. You can audit to a more granular level now, capturing SELECT, INSERT, UPDATE, DELETE, REFERENCES, and EXECUTE statements for individual users. Furthermore, SQL Server Audit is fully scriptable with T-SQL statements CREATE SERVER AUDIT and CREATE SERVER AUDIT SPECIFICATION and their related ALTER and DROP statements.
To set up auditing, you create an audit and specify the location where the audited events will be recorded. Audits can be saved to the Windows Security Log, the Windows Application Log, or to a file at a location that you specify. You name the audit and configure its characteristics, such as the path to the audit file and its maximum size. You can also select to have SQL Server shut down if auditing fails. And if you need to log the audited events to more than one location, you just create more than one audit.
The next step is to create one or more audit specifications. A server audit specification collects information about the instance of SQL Server and includes server scoped objects, such as logins and server role membership. It also includes database information that is managed in the master database, such as the right to access a database. When you define an audit specification, you indicate which audit will receive the monitored events. You can define multiple server audits and multiple server audit specifications, but each server audit can include only one enabled server audit specification at a time.
You can also create database audit specifications that monitor events in a single database. You can add multiple database audit specifications to an audit, but one server audit can enable only one database audit specification per database at a time.
The SQL Server audit action events used for server audit specifications are grouped into collections of related audit action events. These are exposed as audit action groups. When you add a group to the audit specification, you monitor all the events included in that group. For example, there is an audit action group called DBCC_GROUP that exposes the DBCC commands. The DBCC commands, however, are not available for auditing individually.
There are 35 audit action groups available for the server, some of which are closely related to one another. For example, there is a SUCCESSFUL_LOGIN_GROUP, a FAILED_LOGIN_GROUP, and a LOGOUT_GROUP. There is also an AUDIT_ CHANGE_GROUP Audit Action Type you can use to audit the auditing process.
Database audit specifications can also specify groups of audit action events collected into database-level audit action groups. In addition to the audit action groups, database audit specification can include individual audit action events to audit data manipulation language statements. These events can be configured to monitor the entire database or just specific database objects. The SELECT audit action, for instance, can be used to audit SELECT queries for a single table or an entire schema. These events can also be configured to monitor actions by specific users or roles—such as all db_writers.
You can use the SELECT audit action, for example, to audit SELECT queries for a single table by user Mary or the FINANCE_DEPT database role or the public database role. Clearly, this offers a lot of control and flexibility for creating the audits you need.

Dependency Reporting
Dependency reporting has been improved with a new catalog view and new system functions. If you use sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, and sys.dm_sql_referenced_entities, you can report on cross-server, cross-database, and database SQL dependencies for both schema-bound and non-schema-bound objects.

New Database Roles
There are changes to the database roles included in the msdb database. The db_dtsadmin role has been renamed to db_ssisadmin, the db_dtsltduser role has been renamed to db_ssisltduser, and the db_dtsoperator role has been renamed to db_ssisoperator. To support backward compatibility, the old roles are added as members of the new roles when servers are upgraded.
In addition to these changes, new database roles have been added to support new SQL Server 2008 features. In particular, the msdb database includes new roles for server groups (ServerGroupAdministratorRole and ServerGroupReaderRole), Policy-Based Management (PolicyAdministratorRole), and the data collector (dc_admin, dc_operator, and dc_proxy). And the management data warehouse database also includes new roles for the data collector (mdw_admin, mdw_writer, and mdw_reader).

FILESTREAM Security
SQL Server now includes support for FILESTREAM storage, which lets SQL Server applications store unstructured data, such as documents and images, on the file system. This, in turn, means that client applications can benefit from the streaming APIs and performance of the file system while still maintaining transactional consistency between the unstructured and corresponding structured data.
FILESTREAM data must be stored in FILESTREAM filegroups—this is a special filegroup that contains file system directories rather than the actual files. These directories, called data containers, provide the interface between database engine storage and file system storage.
In terms of security, FILESTREAM data is secured like any other data—permissions are granted at the table or column levels. The only account that is granted NTFS permissions to the FILESTREAM container is the account under which the SQL Server service account runs. When a database is opened, SQL Server restricts access to the FILESTREAM data containers, except when access is made using the T-SQL transactions and OpenSqlFilestream APIs.

Policy-Based Management
SQL Server 2008 Policy-Based Management provides a new system for managing SQL Server. You can create policies to test and report on many aspects of SQL Server, and policies can be applied on a single database, a single instance of SQL Server, or on all the SQL Servers that you manage.
With Policy-Based Management, you can test the SQL Server configuration options and many of the security settings. And for some security settings, you can create policies that detect database servers that are out of compliance and then take steps to force them into compliance.
In SQL Server 2008, a number of features that are not essential are disabled by default to minimize your exposure to a possible attack. You can use Policy-Based Management to selectively enable any additional features that you need. You can then evaluate your configuration on a scheduled basis, getting alerts if the configuration settings are found to not match the policy.
Policy-Based Management groups together related properties and exposes them in components called facets. For example, the Surface Area Configuration facet contains properties for Ad Hoc Remote Queries, CLR Integration, Database Mail, OLE Automation, Remote DAC, SQL Mail, Web Assistant, and xp_cmdshell. You can create a policy that enables CLR Integration but disables all the other features. Your policy can include complex condition statements, such as disabling Database Mail on all instances of SQL Server unless they are named Customer_Response.
Once you have created the policy, you can then evaluate the policy on all your servers to produce a report that tells you which servers do not comply. Press the Configure button and all the noncompliant instances will be configured with the policy settings. You should also schedule the policy to run periodically to monitor the status of your servers. Surface Area Configuration facets are provided for the Database Engine, Analysis Services, and Reporting Services.
Note, however, that this Policy-Based Management is not intended to be a security enforcement mechanism. In most cases, a user with sufficient privileges can either issue statements that violate a policy or bypass the policy and perform reconfiguration actions that might violate your security policy. SQL Server 2008 Policy-Based Management should be regarded simply as an aide in monitoring your SQL Server security settings.
Facets vary in their ability to enforce settings, depending on whether their related DDL statements can run in non-autocommit modes. Sometimes a facet can force a configuration setting onto an instance of the Database Engine, but an administrator can still reconfigure the settings. Some facets can be enforced by a server trigger—this can prevent low-privileged users from changing the setting and reduce the chance that an administrator will accidentally change the setting. In this case, the administrator would have to temporarily disable the policy before changing the setting. Other facets can only report on the status of a property but can't change it. This is the case with a policy that checks the key length of a symmetric or asymmetric key (as shown in Figure 2).
Figure 2 Facet for asymmetric keys
Figure 2 Facet for asymmetric keys
There are facets for most types of database objects, many of which have security uses. For example, the login facet can determine whether the password policy is enforced for each login, and the stored procedure facet can detect if all procedures are encrypted. Other facets test the properties of users, schemas, cryptographic providers, common criteria compliance, and C2 auditing.

Windows Server 2008
SQL Server 2008 is fully tested with Windows Server® 2008, which ships with the firewall turned on. Now is a good time to review how to configure the firewall settings. And Windows Server 2008 also provides User Access Control, which you may have experienced with Windows Vista®. This restricts the privileges you automatically receive as an administrative user. These features will affect all versions of SQL Server.

In Conclusion
Security continues to be an area of deliberate improvement for SQL Server. Encryption and authentication enhancements provide new capabilities, and the new auditing system and SQL Server 2008 Policy-Based Management give you new tools to monitor the status of security compliance.

Rick Byham joined Microsoft in 1995. He worked as a SQL Server Support Engineer at Customer Support Services and then joined the SQL Server team at Microsoft Learning. He moved to the SQL Server Books Online team as a technical writer in 2003 where he is currently responsible for security documentation. Rick can be reached at rick.byham@microsoft.com.


 SQL Server 2008: New Data Types
SQL Server 2008 Kelly Wilson

New Data Types
At a Glance:
  • New date and time data types
  • Representing position in a hierarchy
  • Two models for working with spatial data

Conducting business in this global economy increasingly requires companies to use new types of data, applications, and complex calculations. The seven new data types built into SQL Server 2008
provide the means for working with and simplifying the management of more complicated data.

Date and Time
With the old datetime data type, SQL Server® users did not have the ability to work with date and time information separately. Four of the new data types—date, time, datetime2, and datetimeoffset—change that, simplifying working with date and time data and providing for increased date range, fractional seconds precision, and time zone support. New database applications should use these new data types instead of the legacy datetime. Let's take a closer look at the new versions.
The date data type stores a date without a time component. The range is from January 1, 1000 though December 31, 9999 (0001-01-01 through 9999-12-31). Each date variable requires 3 bytes of storage and has a precision of 10 digits. The accuracy of the date type is limited to a single day.
Take a look at Figure 1, which shows how to create and initialize Date variables in T-SQL scripts. The variable @myDate1 is initialized by a string formatted as 'MM/DD/YYYY.' The variable @myDate2 is not initialized; it will have a value of NULL. The variable @myDate3 is initialized to the date on the local computer system. The value of variables can be changed at any time with the SELECT or SET statements, as the example illustrates by changing the value of @myDate2. Date columns can also be created in tables. Figure 2 shows how to create a table with three date columns.
Figure 2 Create a table with three date columns (Close Figure 2)
USE TempDB
GO

CREATE TABLE myTable
(
    myDate1 date,myDate2 date,myDate3 date
)
GO

INSERT INTO myTable
VALUES('01/22/2005',
       '2007-05-08 12:35:29.1234567 +12:15',
       GetDate())

SELECT * FROM myTable

--Results
--myDate1    myDate2    myDate3
------------ ---------- ----------
--2005-01-22 2007-05-08 2007-11-20
Figure 1 Create and initialize date variables in T-SQL scripts (Close Figure 1)
DECLARE @myDate1 date = '01/22/2005'
DECLARE @myDate2 date
DECLARE @myDate3 date = GetDate()

SELECT @myDate2 = '2007-05-08 12:35:29.1234567 +12:15'

SELECT @myDate1 AS '@myDate1',
       @myDate2 AS '@myDate2',
       @myDate3 AS '@myDate3'

--Results
--@myDate1   @myDate2   @myDate3
------------ ---------- ----------
--2005-01-22 2007-05-08 2007-11-20
The time data type stores the time of day without any date component. It is based on a 24-hour clock, so the supported range is 00:00:00.0000000 through 23:59:59.9999999 (hours, minutes, seconds, and fractional seconds). You can specify the fractional second's precision when the data type is created. The default precision is 7 digits; the accuracy is 100ns. Precision affects how much storage space is required, which can range from 3 bytes for up to 2 digits; 4 bytes for 3 or 4 digits; to 5 bytes for 5 to 7 digits.
The T-SQL script in Figure 3 shows how variable precision is affected by implicit conversions of a string initialization value. The T-SQL code first creates and initializes eight separate time variables to an identical value. The fractional precision of each variable is equivalent to its name. For example, @myTime3 has a fractional precision of three places. The results show that the precision of each time data type is equivalent to the fractional precision with which it is declared. Digits that fall outside the range are truncated.
Figure 3 Display time data type's variable precision (Close Figure 3)
DECLARE @myTime  time = '01:01:01.1234567 +01:01'
DECLARE @myTime1 time(1) = '01:01:01.1234567 +01:01'
DECLARE @myTime2 time(2) = '01:01:01.1234567 +01:01'
DECLARE @myTime3 time(3) = '01:01:01.1234567 +01:01'
DECLARE @myTime4 time(4) = '01:01:01.1234567 +01:01'
DECLARE @myTime5 time(5) = '01:01:01.1234567 +01:01'
DECLARE @myTime6 time(6) = '01:01:01.1234567 +01:01'
DECLARE @myTime7 time(7) = '01:01:01.1234567 +01:01'

SELECT @myTime  AS '@myTime',
       @myTime1 AS '@myTime1',
       @myTime2 AS '@myTime2',
       @myTime3 AS '@myTime3',
       @myTime4 AS '@myTime4',
       @myTime5 AS '@myTime5',
       @myTime6 AS '@myTime6',
       @myTime7 AS '@myTime7'

--Results
--@myTime          @myTime1   @myTime2    @myTime3     @myTime4
------------------ ---------- ----------- ------------ -------------
--01:01:01.1234567 01:01:01.1 01:01:01.12 01:01:01.123 01:01:01.1235
--
--@myTime5       @myTime6        @myTime7
---------------- --------------- ----------------
--01:01:01.12346 01:01:01.123457 01:01:01.1234567

DROP TABLE myTable
A time data type can be created as a column in a table. The DROP TABLE myTable T-SQL script in Figure 4 creates a table named myTable1 and adds three time columns to the table. A record is then inserted into the table and the contents of the table are displayed with a SELECT statement.
Figure 4 Create myTable1 (Close Figure 4)
USE TempDB
GO

CREATE TABLE myTable1
(
    myTime1 time(1),
    myTime2 time(2),
    myTime3 time(3)
)
GO

INSERT INTO myTable1
VALUES('01:30:01.1234567',
       '02:34:01.1234567',
       '03:01:59.1234567')

SELECT * from myTable1

--Results
--myTime1    myTime2     myTime3
------------ ----------- ------------
--01:30:01.1000000 02:34:15.1200000 03:01:59.1230000

DROP TABLE myTable1

Datetimeoffset and Datetime2
The datetimeoffset data type provides time-zone awareness. The time data type does not contain a time zone and so only works on local time. In the global economy, however, it is often necessary to know how a time in one area of the world relates to time in another area. The time zone offset is indicated as + or - hh:mm.
This code creates a datetimeoffset variable and initializes it to the time value 8:52 A.M. Pacific Standard Time:
DECLARE @date DATETIMEOFFSET = '2007-11-26T08:52:00.1234567-08:00'
PRINT @date
--Results
--2007-11-26 08:52:00.1234567 -08:00
The string that initializes the datetimeoffset variable (@date in the script) is specially formatted, arranged from the most significant element to the least. A single capital letter T separates the date and time elements. A minus sign separates the time elements from the time zone. There are no spaces between the minus sign and the time or time zone elements. This format is one of two ISO 8601 formats supported by the datetimeoffset data type. (ISO 8601 is an international standard for representation of date and time values.)
The precision of the time component is specified just as for the time data type, and it defaults to the same seven digits if unspecified. The supported range is the same.
The datetime2 data type is an extension of the original datetime type. It supports a larger date range and greater fractional-seconds precision, and it allows you to specify the precision. The date range of the datetime2 type is January 1, 0001 through December 31, 9999, compared with the January 1, 1753 through December 31, 9999 range of the original datetime. As with the time type, seven fractional seconds of precision are provided. The original datetime type provided three digits of precision and a time range of 00:00:00 through 23:59:59.999. Here's how to create a datetime2 variable and initialize it to the local server date and time:
DECLARE @datetime2 DATETIME2 = GetDate();
PRINT @datetime2

--Results
--2007-11-26 09:39:04.1370000
Next I'll look at the new hierarchyid data type. This data type works with the relationship among data elements in a table, rather than a specific date or time data.

The Hierarchyid Data Type
The hierarchyid data type allows you to construct relationships among data elements within a table, specifically to represent a position in a hierarchy. To explore this data type, let's start by creating the MyCompany database and filling it with employee data, using the script in Figure 5.
Figure 5 Create and populate the MyCompany database (Close Figure 5)
USE MASTER
GO

CREATE DATABASE MyCompany
GO
USE MyCompany
GO

--Create a table called employee that will store
--the data for the employees for MyCompany.

CREATE TABLE employee
(
    EmployeeID int NOT NULL,
    EmpName    varchar(20) NOT NULL,
    Title      varchar(20) NULL,
    Salary     decimal(18, 2) NOT NULL,
    hireDate   datetimeoffset(0) NOT NULL,
)
GO

--These statements will insert the data for the employees of MyCompany.

INSERT INTO employee
VALUES(6,   'David',  'CEO', 35900.00, '2000-05-23T08:30:00-08:00')

INSERT INTO employee
VALUES(46,  'Sariya', 'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(271, 'John',   'Specialist', 14000.00, '2002-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(119, 'Jill',   'Specialist', 14000.00, '2007-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(269, 'Wanida', 'Assistant', 8000.00, '2003-05-23T09:00:00-08:00')

INSERT INTO employee
VALUES(272, 'Mary',   'Assistant', 8000.00, '2004-05-23T09:00:00-08:00')
GO
--Results
--EmployeeID  EmpName Title      Salary   hireDate
------------- ------- ---------- -------- --------------------------
--6           David   CEO        35900.00 2000-05-23 08:30:00 -08:00
--46          Sariya  Specialist 14000.00 2002-05-23 09:00:00 -08:00
--271         John    Specialist 14000.00 2002-05-23 09:00:00 -08:00
--119         Jill    Specialist 14000.00 2007-05-23 09:00:00 -08:00
--269         Wanida  Assistant  8000.00  2003-05-23 09:00:00 -08:00
--272         Mary    Assistant  8000.00  2004-05-23 09:00:00 -08:00
Figure 6 shows the resulting simple database consisting of a single employee table. This employee table in the MyCompany database does not have any imposed structure. This is normal for a relational database, as structure is imposed dynamically by an application through its query and processing code.
Figure 6 MyCompany employee table
Figure 6 MyCompany employee table
Business data, though, typically has a type of inherent structure. For example, every business has a reporting structure, such as that shown for MyCompany in Figure 7. All employees of MyCompany report to David, the CEO. Some employees report directly, as in the case of Jill. Others, like Mary, report through an intermediary. In programming terms, the reporting structure for MyCompany is referred to as a tree because that's what its shape resembles. David, at the top, reports to no one; he is the parent or ancestor. The employees that report to David are beneath. Such nodes are referred to as children or descendants. David can have as many descendants as needed to represent his direct reports.
Figure 7 MyCompany's organizational structure
Figure 7 MyCompany's organizational structure
The script in Figure 8 rebuilds the MyCompany database using the hierarchyid data type, constructing a relationship that matches the reporting structure for MyCompany. The ALTER TABLE statement is used to first add a column of type hierarchyid. David's node is then inserted using hierarchyid's GetRoot method. David's directs are then added to the tree using the GetDescendant method.
Figure 8 Rebuild the database using hierarchyid (Close Figure 8)
DELETE employee
GO
ALTER TABLE employee ADD OrgNode hierarchyid NOT NULL
GO

DECLARE @child hierarchyid,
@Manager hierarchyid = hierarchyid::GetRoot()

--The first step is to add the node at the top of the
--tree. Since David is the CEO his node will be the
--root node.

INSERT INTO employee
VALUES(6,   'David',  'CEO', 35900.00,
       '2000-05-23T08:30:00-08:00', @Manager)

--The next step is to insert the records for
--the employees that report directly to David.

SELECT @child = @Manager.GetDescendant(NULL, NULL)

INSERT INTO employee
VALUES(46,  'Sariya', 'Specialist', 14000.00,
       '2002-05-23T09:00:00-08:00', @child)

SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(271, ‚John',   ‚Specialist', 14000.00,
       '2002-05-23T09:00:00-08:00', @child)

SELECT @child = @Manager.GetDescendant(@child, NULL)
INSERT INTO employee
VALUES(119, ‚Jill',   ‚Specialist', 14000.00,
       ‚2007-05-23T09:00:00-08:00', @child)

--We can now insert the employee that reports to
--Sariya.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 46

INSERT INTO employee
VALUES(269, ‚Wanida', ‚Assistant', 8000.00,
       ‚2003-05-23T09:00:00-08:00', @manager)

--Next insert the employee that report to John.
SELECT @manager = OrgNode.GetDescendant(NULL, NULL)
FROM employee WHERE EmployeeID = 271

INSERT INTO employee
VALUES(272, ‚Mary',   ‚Assistant', 8000.00,
       ‚2004-05-23T09:00:00-08:00', @manager)
GO
Once the database records are added and the hierarchy is constructed, the contents of the employee table can be displayed with a query like this:
SELECT EmpName, Title, Salary, OrgNode.ToString() AS OrgNode
FROM employee ORDER BY OrgNode
GO
--Results
--EmpName  Title      Salary    OrgNode
---------- ---------- --------- -------
--David    CEO        35900.00  /
--Sariya   Specialist 14000.00  /1/
--Wanida   Assistant  8000.00   /1/1/
--John     Specialist 14000.00  /2/
--Mary     Assistant  8000.00   /2/1/
--Jill     Specialist 14000.00  /3/
OrgNode is the hierarchyid column. Each slash / character in the result indicates a node in the hierarchy tree. David is at the root, which is shown with a single slash. Sariya, John, and Jill report to David and have two slash marks, indicating that they are the second node in the hierarchy. The numbers 1, 2, or 3 show the order of the respective child node. This system is very flexible. Child nodes can be removed, inserted, or added as needed. If we added an employee between John and Jill, for example, that employee would be listed in the resultset as: /2.1/.
To answer the question, for example, "Who reports to Sariya?" you can create a query as shown in the following T-SQL code:
DECLARE @Sariya hierarchyid

SELECT @Sariya = OrgNode
FROM employee WHERE EmployeeID = 46

SELECT EmpName, Title, Salary, OrgNode.ToString() AS 'OrgNode'
FROM employee
WHERE OrgNode.GetAncestor(1) = @Sariya
GO
--Results
--EmpName Title     Salary  OrgNode
--------- --------- ------- -------
--Wanida  Assistant 8000.00 /1/1/
The query uses the hierarchyid's GetAncestor method, which returns the parent of the current hierarchyid node. In the previous code, the variable @Sariya is set to the hierarchy node for Sariya. This is because Sariya is the direct ancestor of any employee that reports to her. Therefore, writing a query that returns the employees that report directly to Sariya consists of retrieving Sariya's node from the tree and then selecting all employees whose ancestor node is Sariya's node.
Hierarchyid columns tend to be very compact because the number of bits required to represent a node in a tree depends on the average number of children for the node (commonly referred to as the node's fanout). For example, a new node in an organizational hierarchy of 100,000 employees, with an average fanout of six levels, would take about five bytes of storage.
The hierarchyid data type provides several methods that facilitate working with hierarchical data. A summary of these methods is shown in Figure 9. Detailed information on all of the methods is available in SQL Server Books Online (msdn2.microsoft.com/ms130214).
Figure 9 Methods provided by the hierarchyid data type (Close Figure 9)

Method | Description
GetAncestor | Returns a hierarchyid that represents the nth ancestor of this hierarchyid node.
GetDescendant | Returns a child node of this hierarchyid node.
GetLevel | Returns an integer that represents the depth of this hierarchyid node in the overall hierarchy.
GetRoot | Returns the root hierarchyid node of this hierarchy tree. Static.
IsDescendant | Returns true if the passed-in child node is a descendant of this hierarchyid node.
Parse | Converts a string representation of a hierarchy to a hierarchyid value. Static.
Reparent | Moves a node of a hierarchy to a new location within the hierarchy.
ToString | Returns a string that contains the logical representation of this hierarchyid.

Spatial Data Types
Spatial data is data that identifies geographic locations and shapes, primarily on the Earth. These can be landmarks, roads, even the location of a business. SQL Server 2008 provides the geography and geometry data types for working with this type of data.
The geography data type works with round-earth information. The round-earth model factors the curved surface of the earth into account in its calculations. Position information is given in longitude and latitude. This model is well-suited to applications like transoceanic shipping, military planning, and short-range applications referenced to the earth's surface. This is the model to use if your data is stored in latitudes and longitudes.
The geometry data type works with the flat-earth or planar model. In this model, the earth is treated as a flat projection beginning at a known point. The flat-earth model does not take the curvature of the earth into account, so it is primarily used for describing short distances—for example in a database application that maps the interior of a building.
The geography and geometry types are constructed from vector objects, specified in Well-Known Text (WKT) or Well-Known Binary (WKB) format. These are transport formats for spatial data described by the Open Geospatial Consortium (OGC) Simple Features for SQL Specification. Figure 10 lists the seven types of vector objects supported by SQL Server 2008.
Figure 10 Vector objects supported by SQL Server 2008 (Close Figure 10)

Object | Descripton
Point | A location.
MultiPoint | A series of points.
LineString | A series of zero or more points connected by lines.
MultiLineString | A set of linestrings.
Polygon | A contiguous region described by a set of closed linestrings.
MultiPolygon | A set of polygons.
GeometryCollection | A collection of geometry types.
To construct a geography type with one or more vector objects, you first declare the geography type in your T-SQL script, as shown in Figure 11. You then call one of the methods listed in Figure 12 and pass in the character string for your vector object and the Spatial Reference ID (SRID). The SRID is the spatial reference identification system, defined by the European Petroleum Survey Group. It is part of a set of standards developed for cartography, surveying, and geodetic data storage. Each SRID identifies a specific type of ellipsoid to use in geography calculations. This is necessary because the earth is not a perfect sphere. SQL Server 2008 can only perform calculations on identical SRIDs.
Figure 12 Construct objects for geography and geometry (Close Figure 12)

Method | Description
STGeomFromText | Constructs any type of geography instance from input text.
STPointFromText | Constructs a geography Point instance from the input text.
STMPointFromText | Constructs a geography MultiPoint instance from input text.
STLineFromText | Constructs a geography LineString instance from input text.
STMLineFromText | Constructs a geography MultiLineString instance from input text.
STPolyFromText | Constructs a geography Polygon instance from input text.
STMPolyFromText | Constructs a geography MultiPolygon instance from input text.
STGeomCollFromText | Constructs a geography GeometryCollection instance from input text.
Figure 11 Create points, lines, and polygon geometry (Close Figure 11)
DECLARE @geo1 geometry
SELECT @geo1 = geometry::STGeomFromText('POINT (3 4)', 0)
PRINT @geo1.ToString()

DECLARE @geo2 geometry
SELECT @geo2 = geometry::Parse('POINT(3 4 7 2.5)')
PRINT @geo2.STX;
PRINT @geo2.STY;
PRINT @geo2.Z;
PRINT @geo2.M;

DECLARE @geo3 geography;
SELECT @geo3 = geography::STGeomFromText(
    'LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326);
SELECT @geo3.ToString();

--Results
--POINT (3 4)
--3
--4
--7
--2.5

DECLARE @gx geometry;
SET @gx = geometry::STPolyFromText(
    'POLYGON ((5 5, 10 5, 10 10, 5 5))', 0);
PRINT @gx.ToString();
--Results
--POLYGON ((5 5, 10 5, 10 10, 5 5))

Differences between Geography and Geometry
The geography and geometry data types are meant for working with different types of data, so there are some key differences to be aware of. With the geometry data type, distances and areas are given in the same unit of measurement as the coordinates of the instances. For example, the distance between the points (0,0) and (6,8) will always be 10 units. This is not the case with the geography type, which works with ellipsoidal coordinates that are expressed in degrees of latitude and longitude.
The GEOMETRY data type returns incoherent results when coordinates are expressed as latitude longitude pairs. The following T-SQL code calculates the distance between the POINTS (90 0) and (90 180). Both of these points refer to the North Pole, so the distance between them should be 0. In the GEOMETRY case the calculated distance is 180.
DECLARE @g1 GEOMETRY, @g2 GEOMETRY, @g3 GEOGRAPHY, @g4 GEOGRAPHY
SELECT @g1 = GEOMETRY::STGeomFromText('POINT (90 0)', 0)
SELECT @g2 = GEOMETRY::STGeomFromText('POINT (90 180)', 0)

SELECT @g3 = GEOGRAPHY::STGeomFromText('POINT (90 0)', 4326)
SELECT @g4 = GEOGRAPHY::STGeomFromText('POINT (90 180)', 4326)
SELECT @g2.STDistance(@g1) AS 'GEOMETRY',
       @g4.STDistance(@g3) AS 'GEOGRAPHY';

--Results
--GEOMETRY               GEOGRAPHY
------------------------ ----------------------
--180                    0
The orientation of spatial data is different for the two data types as well. In the planar system used by the geometry data type, orientation of the polygon is not an important factor. For example, a polygon with the coordinates ((0, 0), (10, 0), (0, 20), (0, 0)) is considered the same as the polygon ((0, 0), (0, 20), (10, 0), (0, 0)). But in the data model used by the geography data type, a polygon is unclear without a specified orientation. Consider, for example, a ring around the equator. Does the polygon described by this ring refer to the northern or southern hemisphere? The point here is that when working with geography data, the orientation and the location must be accurately described.
There are also some restrictions placed on the geography data type by SQL Server 2008. For example, each geography instance must fit inside a single hemisphere. Larger spatial objects are not allowed and will cause an ArgumentException to be thrown. Geography data types requiring two inputs will return NULL if the results from the methods do not fit inside a single hemisphere.
SQL Server provides a number of methods that allow operations to be performed on geography and geometry instances. Figure 13 shows some examples using methods that SQL Server 2008 provides to work with spatial data. Due to space constraints, I cannot go into more detail on this topic, but you can find full descriptions in SQL Server Books Online.
Figure 13 Working with spatial data (Close Figure 13)
DECLARE @gm geometry;
DECLARE @gg geography;
DECLARE @h geography;

SET @gm = geometry::STGeomFromText('POLYGON((0 0, 13 0, 3 3, 0 13, 0 0),(2 2, 2 1, 1 1, 1 2, 2 2))', 0);
SELECT @gm.STArea();

--Results
--38

SET @gg = geography::STGeomFromText('LINESTRING(0 0, 5 5)', 4326);
--Calculate the distance to a point slightly offset from the LINESTRING.
SET @h = geography::STGeomFromText('POINT(4 4)', 4326);
SELECT @gg.STDistance(@h);

--Results
-- 430.182777043046

--Calculate the distance to a point on the LINESTRING.
SET @h = geography::STGeomFromText('POINT(5 5)', 4326);
SELECT @gg.STDistance(@h);

--Results
-- 0

DECLARE @temp table ([name] varchar(10), [geom] geography);

INSERT INTO @temp values ('Point', geography::STGeomFromText('POINT(
5 10)', 4326));
INSERT INTO @temp values ('LineString', geography::STGeomFromText(
'LINESTRING(13 5, 50 25)', 4326));
--Calculate the distance to a point on the LINESTRING.
--Display the number of dimensions for a geography object stored in a --table variable.
INSERT INTO @temp values ('Polygon', geography::STGeomFromText(
'POLYGON((47.653 -122.358, 47.649 -122.348, 47.658 -122.348, 47.658 -122.358, 47.653 -122.358))', 4326));

SELECT [name], [geom].STDimension() as [dim]
FROM @temp;

--Results
--name       dim
------------ -----------
--Point      0
--LineString 1
--Polygon    2
I hope this information on the seven new data types in SQL Server 2008 has been helpful.

Kelly Wilson has been in software engineering for more than 20 years. Her credits include applications in SQL Server, 3D graphics, gaming, and color science. Kelly is currently a Programming Writer with the SQL Server group at Microsoft.


 SQL Server 2008: Data Warehouse Query Performance
SQL Server 2008 Sunil Agarwal and Torsten Grabs and Dr. Joachim Hammer

Data Warehouse Query Performance
At a Glance:
  • Star join query optimization
  • Partitioned table parallelism
  • ROW and PAGE compression
  • Partition-aligned indexed views

SQL Server 2008 will offer much more powerful relational data warehousing capabilities than its predecessor, but you may still be wondering how you can use all this new technology to build a well-performing data warehouse for decision support over billions of rows. Or you
may want to know which features are going to help you achieve the best query performance for your decision-support queries and reports, or what sort of performance improvements you can realistically expect with this new version of SQL Server®.
There are certainly a lot of questions that come up as we get closer to the actual release. We hope this deep-dive look into some of the most important performance-related data warehousing features in SQL Server 2008 can help you get ready.

Logical Database Design: Dimensional Modeling
Transactional line-of-business applications usually have a normalized database schema. Logical database schema design for relational data warehouses puts less emphasis on normalization. Many relational data warehouse designs today follow a dimensional modeling approach, which was made popular by Ralph Kimball and Margy Ross in their book, The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling.
If you spend a lot of time dealing with data warehousing, you're probably already familiar with common schema patterns for relational data warehouses (such as star and snowflake schemas). Dimensional modeling distinguishes dimension tables from fact tables. Your dimension tables are what holds your master data (such as products, customers, stores, or countries) while your fact table stores transactional data (such as sales, orders, purchases, or returns).
Dimension tables and fact tables are linked by primary key (PK)/foreign key (FK) relationships. You'll find that many data warehouses don't enforce the FK constraints as a way to keep storage requirements small. This saves the storage overhead of the underlying indexes and keeps the maintenance cost of the fact table low. Dimension tables in a data warehouse are usually fairly small—they typically hold thousands or up to several million rows. The fact table, on the other hand, can be very large, holding hundreds of millions to billions of rows. Thus, logical design really needs to pay close attention to storage requirements of the fact table.
This size factor also has implications in determining what key you should choose from a dimension table to maintain the fact table/dimension table relationships. Composite keys based on the business key of the dimension—meaning the real-world identifier of the entity represented by the dimension—commonly cover several columns. You should know that this is an issue for the corresponding foreign key in the fact table since the multi-column composite key will be repeated for each fact table row.
In response to this, a common practice is to use small surrogate keys to implement the relationships between a fact table and its dimensions. The surrogate key is an integer-typed identity column that serves as an artificial primary key of the dimension table. With the fact table referring to the smaller surrogate key, there is a significant reduction in storage requirements for large fact tables. Figure 1 illustrates a dimensionally modeled data warehouse schema using dimension and fact tables with surrogate keys.
Figure 1 Star schema example with a fact table and two dimension tables
Figure 1 Star schema example with a fact table and two dimension tables
The snowflake schema design spreads one or several dimensions over multiple levels (for instance, customer, country, and region for a customer dimension), thereby normalizing larger dimensions that may suffer from excessive redundancies in data. The levels are represented by separate tables, giving the schema the form of a snowflake. The star schema design, on the other hand, does not spread any of its dimensions over tables. A star schema has the form of a star where the dimension tables are grouped around the fact table in the center.
With dimensionally modeled star schemas or snowflake schemas, decision support queries follow a typical pattern: the query selects several measures of interest from the fact table, joins the fact rows with one or several dimensions along the surrogate keys, places filter predicates on the business columns of the dimension tables, groups by one or several business columns, and aggregates the measures retrieved from the fact table over a period of time. The following demonstrates this pattern, which is also sometimes referred to as a star join query:
select ProductAlternateKey,
CalendarYear,sum(SalesAmount)
from FactInternetSales Fact
     join DimTime
on Fact.OrderDateKey = TimeKey
     join DimProduct
on DimProduct.ProductKey =
   Fact.ProductKey
where CalendarYear between 2003 and 2004
      and ProductAlternateKey like 'BK%'
group by ProductAlternateKey,CalendarYear

Physical Design
Many SQL queries in your relational data warehouse will follow the structure of the star join query. Nevertheless, decision support queries usually vary over time because decision makers continuously attempt to better understand their fundamental business data in new ways. So that is why workloads for data warehouses tend to have a high ratio of ad hoc queries. This makes physical design for decision support queries and a dimensionally modeled data warehouse schema challenging.
With SQL Server, data warehouse designers usually start with a blueprint or physical design that they tune and refine over time as workloads evolve. You should feel free to adopt and vary this blueprint for your own data warehouse environment. If you do so, keep in mind, of course, the best practices for database physical design, such as the performance impact caused by index maintenance for updates and storage requirements for the indexes.

The Fact Table
The blueprint design aims to anticipate the typical star query shape and builds indexes over the fact table. The clustered index of the fact table uses several dimension surrogate key columns (the foreign key columns) as index keys. The most frequently used columns should occur in the list of index keys. You may want to take the time to verify that this indeed provides a good access path for the most frequently executed queries in your workload.
In addition, the blueprint creates a single-column non-clustered index for each dimension surrogate (foreign key) column in the fact table. This provides a highly efficient access path for queries that are very selective in one of their dimensions.
The goal of the clustered index is to provide good performance for the majority of the queries in your workload. The set of non-clustered indexes targets queries that retrieve fact table measures for a specific customer or product. These non-clustered indexes ensure that you do not, for example, have to scan the fact table in order to retrieve sales data for a single customer.

Dimension Tables
When you apply the blueprint design to dimension tables, you need to create indexes for each dimension table. These include a non-clustered primary key constraint index on the surrogate key column of the dimension and a clustered index over the columns of the business key of the dimension entity. For large dimension tables, you should also consider adding non-clustered indexes over columns that are frequently used in highly selective predicates.
The clustered index facilitates efficient Extract, Transform, and Load (ETL) during the maintenance window of the data warehouse, which is often a time-critical process. With slowly changing dimensions, for example, existing rows are updated in place while rows not yet present in the dimension are appended to the dimension table. To be successful, this access pattern requires well-performing lookup and updating for the dimension table at the time of ETL.
The blueprint design we outlined serves as a good starting point for physical designs in relational data warehouses built with SQL Server. Based on this typical relational data warehouse setup, we can explore key new features in SQL Server 2008.

Star Join Query Optimization
Processing the fact table is usually the biggest cost of executing a star join query in a dimensionally modeled relational data warehouse. This is easy to see because even highly selective queries retrieve an order of magnitude more rows from the fact table than from any dimension. Therefore, using the best access path into the fact table is essential for good query performance.
With SQL Server, the query optimizer automatically chooses the access path with the lowest estimated cost from a set of alternatives. In the context of data warehousing, the main objective is to make sure the query optimizer considers the compelling alternatives of access paths for the execution plan of the star join query. SQL Server includes various functionalities in its query optimizer to automatically provide well-performing star join query execution plans.
You can think of star join queries as being divided among three different classes, as depicted in Figure 2. These broad classes also help the SQL Server engine to identify proper plan choices for those queries. The main concept SQL Server relies on is the selectivity of those queries against the fact table. A query is considered more selective with the fewer rows it consumes from the fact table. The percentage of rows retrieved from the fact table is used to provide the intuition of those query classes. These percentages represent values from typical customer deployments, but they are not strict boundaries used to generate access path definitions.
Figure 2 Selectivity ranges for star join queries
Figure 2 Selectivity ranges for star join queries
The first class covers highly selective queries, which process up to 10 percent of the rows in the fact table. The second class, with medium selectivity, consists of queries that process more than 10 percent and up to 75 percent of the fact table rows. Queries in the third class, with low selectivity, require processing of more than 75 percent of the rows stored in the fact table. The boxes in the figure also highlight the basic query execution plan choices within each selectivity class.

Plan Choice Based on Selectivity
Since high-selectivity star queries usually retrieve not more than 10 percent of the fact table rows, those queries can afford random access into the fact table. Hence, query plans for that class rely heavily on nested loop joins in combination with (non-clustered) index seeks and bookmark lookups into the fact table. As these perform random I/O into the fact table, they are outperformed by sequential I/O once we need to retrieve larger portions of the fact table. This motivates different query plans as the number of rows from the fact table increases beyond a certain number.
Since medium-selectivity star queries process a significant fraction of the rows in the fact table, hash joins with fact table scans or fact table range scans are usually the preferred choice of access path into the fact table. SQL Server uses bitmap filters to improve the performance of these hash joins.
Figure 3 illustrates how SQL Server uses these bitmap filters to improve performance of joining during star join query execution. The figure shows a plan for a query against two dimension tables, Product and Time, that join with the fact table along their surrogate keys. The query uses filter predicates, such as WHERE clauses, against both dimension tables such that only one row qualifies for each dimension. This is indicated by the little red tables next to the two join operators.
Figure 3 Star join query plan with join reduction processing
Figure 3 Star join query plan with join reduction processing
The join implementation for each join is a hash join, which allows SQL Server to consume the information about qualifying rows from the dimension tables into what we call join reduction information for both dimension tables. The green boxes in the figure represent join reduction information data structures. Once populated from the underlying dimension tables, SQL Server moves these data structures automatically during query execution to the operator that processes the fact table, such as a table scan. This operator utilizes the information about dimension table rows to eliminate fact table rows that will not qualify for the join conditions against the dimensions.
SQL Server prunes these fact table rows very early during query processing—after retrieving the row from the fact table. This enables CPU savings and potential disk I/O savings since the pruned rows don't need to be processed within further operators of the query plan. SQL Server uses a bitmap representation to efficiently implement the join reduction information data structures at query execution time.

Star Join Optimization Pipeline
The optimization process employs standard heuristics for join query optimization to generate an initial set of query execution plan alternatives. Special purpose extensions are then invoked to generate additional query plan alternatives.
In the case of data warehousing, the extension detects star schemas, snowflake schemas, and the star query patterns, and it estimates the selectivity of the query against the fact table. If the schema and the query shape match the patterns, SQL Server automatically adds further query plans to the plan space, which is then covered by cost-based optimization to choose the most promising query plan for execution.
At query execution time, SQL Server also monitors actual selectivity of the join reduction at run time. If selectivity changes, SQL Server rearranges the join reduction information data structures dynamically so the most selective one is applied first.

Star Join Heuristics
Many physical designs for data warehouses follow the star schema but do not completely specify the relationships between the fact and dimension tables, as mentioned earlier for foreign key constraints, for instance. Without the foreign key constraints explicitly specified, SQL Server must depend on heuristics to detect star schema query patterns. The following heuristics are applied to detect star join query patterns:
  1. The largest of the tables participating in the n-ary join is considered the fact table. There are additional restrictions on the minimum size of the fact table. For instance, if even the largest table is not beyond a specific size, the n-ary join is not considered a star join.
  2. All join conditions of the binary joins in a star join query have to be single column equality predicates. The joins have to be inner joins. While this might sound restrictive, it covers the vast majority of joins between the fact table and dimension tables on the surrogate key in typical star schemas. If a join has a more complex join condition that doesn't fit the pattern described above, the join is excluded from the star join. A five-way join, for example, can lead to a three-way star join (with two additional joins later on), if two of the joins have more complex join predicates.
Note that these are heuristic rules. There are few real circumstances that will lead the heuristics to pick a dimension table as the fact table. This influences the plan choice but has no impact on the correctness of the selected plan. The binary joins involved in a star join are then ordered by decreasing selectivity. Join selectivity in this context is defined as a ratio of input cardinality of the fact table and result cardinality of the join—the join selectivity indicates how much a specific dimension reduces the cardinality of a fact table. As a general rule, we want to consider joins with higher selectivity first.
The query processor in SQL Server applies the optimization automatically to queries following the star join pattern and the aforementioned conditions when the resulting query plans have attractive estimated query costs. Thus, you do not need to make any changes to your application for it to benefit from this significant performance improvement. Note, though, that some of the star join optimizations such as join reduction are available only in SQL Server Enterprise Edition.

Star Join Performance Results
As part of the development effort for the star join optimization in SQL Server 2008, we performed a number of performance studies based on benchmark and real customer workloads. It's worth looking at the results from three of these workloads.
Microsoft Sales Organization Data Warehouse This workload tracks performance for a data warehouse that is used internally for decision support in the sales organization at Microsoft. We took a sample snapshot of the database with a size of about 750GB (including indexes). Queries in this workload are challenging for query processing, as many of them have more than 10 joins.
Retail Customer This series of experiments is based on a data warehousing customer in the retail business (with both a conventional store and online presence). The customer is characterized by a dimensionally modeled snowflake schema and canonical star join queries. We used about 100GB of raw data to populate a snapshot of the warehouse for our experiments.
Decision Support Workload This series of experiments investigates performance of a decision support workload on a 100GB dimensionally modeled database. Figure 4 shows the results for these three workloads. The figure plots normalized geometric means of query response time over all queries in the workload. This metric is a good indicator of what query performance is expected to be when running an arbitrary query from the workload. The bars in the figure compare the baseline performance (1.0) when not using the star join optimization against star join-optimized performance. All these runs were performed with SQL Server 2008.
Figure 4 Performance improvements with star join optimization
Figure 4 Performance improvements with star join optimization
As the figure shows, all workloads improve significantly, by 12 up to 30 percent. While your individual mileage will vary, we expect decision support workloads against the SQL Server Engine to improve by approximately 15–20 percent based on the extension of the star join-specific optimizations that are new in SQL Server 2008.

Partitioned Table Parallelism
To speed up query processing in large data warehouses, database administrators often partition large fact tables by date. This places the data onto different filegroups, reducing the amount of data that must be searched when processing rows within a specific data range and also making use of the concurrent performance of the underlying disk system when the filegroups are deployed over a large number of physical disks.
SQL Server 2005 introduced the ability to partition a large relation into smaller logical chunks to improve the administration and management of large tables. It has also been used successfully to improve query processing, especially when it comes to large decision-support applications.
Unfortunately, some customers using SQL Server 2005 have observed performance problems associated with queries on these partitioned tables—in particular, when running on parallel shared memory multiprocessor machines. When processing parallel queries on partitioned tables in SQL Server 2005, there may be instances when only a subset of the available threads is assigned to execute the query.
Think of a 64-core machine where queries could use up to 64 threads in parallel, and a query touches two partitions. With SQL Server 2005, it only receives 2 of the 64 threads and therefore might use only 2/64 (3.1 percent) of the CPU power of the machine. It has been reported that for some queries, performance in the partitioned case could be 10 or more times worse than when the same query is run on the same machine on a non-partitioned version of the same fact table.
We should note that SQL Server 2005 was specifically optimized for queries touching a single partition. In this case, the query processor will assign all available threads to perform the scan. This special optimization resulted in a significant performance boost for single-partition queries executing on multi-core machines, and it was only natural for customers to expect this behavior for queries touching multiple partitions.
The new partitioned table parallelism (PTP) feature in SQL Server 2008 improves query performance in the partitioned case by better utilizing the processing power of the existing hardware regardless of how many partitions a query touches or the relative size of individual partitions. In a typical data warehouse scenario with a partitioned fact table, users may see a significant improvement for queries executing on parallel plans, especially if the number of available processor cores is larger than the number of partitions affected by the query. And this new feature works out of the box without any tuning or configuration.
Say we have a fact table representing sales data organized by sales date across four partitions. The diagram in Figure 5 will help you visualize this example. Note that instead of a single clustered index for the entire date range, as in the non-partitioned case, there is typically a clustered index on the date column for each partition of the fact table. Now assume that query Q summarizes sales for the last seven days. As new sales data continuously enters the fact table through the last partition (labeled P4), the query will likely touch different partitions depending on when it is executed. This is illustrated in the first row of the diagram by how the Q1 query touches just a single partition while the Q2 query touches two partitions since the relevant data at the time of execution spans across the P3 and P4 partitions.
Figure 5 The new PTP feature at work
Figure 5 The new PTP feature at work
Now assume that there are eight threads available. Executing Q1 and Q2 on SQL Server 2005 may generate some unexpected behavior. SQL Server 2005 has an optimization by which if the optimizer knows at compile time that only one partition will be touched by the query, that partition will be treated like a single non-partitioned table and a plan will be generated that accesses the table with all available threads.
The result is that Q1 involving a single partition (P3) will result in a plan that is processed by eight threads (not shown). In the case of Q2, which touches two partitions, the executor assigns a single thread to each partition even if the underlying hardware has additional threads available. Thus, Q2 will only utilize a very small fraction of the available CPU power and most likely execute significantly slower than Q1.
Execution of Q1 and Q2 on SQL Server 2008 results in better utilization of the available hardware and better performance and more predictable behavior. In the case of Q1, the executor again assigns all eight available threads to process data in P2 (not shown). Q2, meanwhile, will result in a parallel plan in which the executor assigns all available threads to both P3 and P4 round-robin style, producing the effect illustrated in the bottom row of the diagram where each of the two partitions receive four threads. The CPU remains fully utilized and performance of Q1 and Q2 are comparable.
This round-robin allocation of threads enables queries to perform increasingly better with the more processor cores there are compared to the number of partitions that are accessed by the query. Unfortunately, though, there are cases in which the allocation of threads to partitions is not as straightforward as in this example.
The performance gains from SQL Server 2005 to SQL Server 2008 for the partitioned table scenario on a multicore processor machine are further illustrated in Figure 6. This anecdotal graph highlights scan performance for partitioned tables. For this particular test, which was conducted on a system with 64 cores and 256GB of RAM, we partitioned a single table of 121GB into 11 partitions of 11GB each. For the set of tests depicted in this figure, we used a heap file organization with both cold and warm buffer starts. All the queries perform simple scans against the data.
Figure 6 Scan performance for SQL Server with new PTP feature enabled
Figure 6 Scan performance for SQL Server with new PTP feature enabled
The y-axis shows response time (sec) and the x-axis indicates the degree of parallelism (DOP), which is analogous to the number of threads that are assigned to the query. As you can see, in both the cold and warm start cases, response times continue to decrease until DOP reaches 22. At this point, the I/O system becomes saturated for the cold start case. This is due to the fact that the query used in this example is I/O-bound. For more CPU-bound workloads, this limitation may not exist or may occur only at higher DOPs.
The curve representing the warm start case, however, continues to show decreases in response time as DOP levels increase. On SQL Server 2005, both curves would start to level off at around DOP 11 since the number of threads per partition would be limited to 1 when dealing with multiple partitions.
It is important to point out that, in practice, the gain in response times for increasing numbers of DOP is never linear. Rather, the expected behavior resembles more that of a step function—this reflects the reality that the query essentially waits on the slowest subpart. So, for example, simply adding one more thread to a scan is not going to improve the finish time of a query until all remaining scans have received additional threads allowing them to also finish faster.
We have performed additional experiments to test the new PTP behavior for various other hardware and file configurations. In doing so, we observed a similar behavior in terms of scaling of throughput as the DOP increases beyond one thread/partition.
Last, but not least, the new PTP feature in SQL Server 2008 also improves the readability of query plans and allows for better insight into the execution of certain workloads. For example, as part of the PTP feature, the way in which parallel and serial plans are represented in showplan XML has been improved and the partitioning information that is provided in both compile-time and runtime execution plans has been enhanced.

Data Compression
As businesses intelligence becomes increasingly common, businesses are pouring more and more data into their data warehouses for analysis. The result is an exponential growth in the size of the data being managed. In 1995, the first Winter Corporation survey of database size reported that the largest system in the world contained a terabyte of data. Ten years later, the biggest database was around 100 times larger. The more astounding fact is that the size of data warehouses is tripling every two years. This creates new challenges in terms of managing such large amounts of data and providing acceptable levels of performance for data warehouse queries. These queries are typically complex, involving many joins and aggregates, and they access large amounts of data. And it is not uncommon for many queries in the workload to be I/O-bound.
Native data compression aims to address this issue. SQL Server 2005 SP2 introduced a new variable length storage format, the vardecimal storage format, for decimal and numeric data. This new storage format can reduce the size of your databases significantly. The space-savings, in turn, can help improve the performance of I/O-bound queries in two ways. First, there are fewer pages to read, and second, since the data is kept as compressed in the buffer pool, it improves the page life expectancy (in other words, it improves the odds that the requested page will be found in the buffer). Of course, the space savings achieved from data compression do have a CPU cost due to the process of compressing and uncompressing data.
SQL Server 2008 builds on the vardecimal storage format, providing two kinds of compression: ROW compression and PAGE compression. ROW compression extends the vardecimal storage format by storing all fixed-length data types in a variable length storage format.
Some examples of fixed-length data types are integer, char, and float data types. Even though SQL Server stores these data types in variable length format, the semantics of the data types will remain unchanged (a data type is still the fixed-length data type from the perspective of the application). So this means that you can get the benefits of data compression without requiring any changes in your applications.
PAGE compression minimizes the data redundancy in columns in one or more rows on a given page. It uses a proprietary implementation of the LZ78 (Lempel-Ziv) algorithm, storing the redundant data only once on the page and then referencing it from the multiple columns. Note that when you use PAGE compression, ROW compression is actually also included.
The ROW and PAGE compression can be enabled on a table or an index or on one or more partitions for the partitioned tables and indexes. This gives you complete flexibility over choosing tables, indexes, and partitions for compression, allowing you to strike the right balance between space savings and CPU impact. Figure 7 illustrates this with a sales table that is partitioned in different ways with aligned indexes.
Figure 7 Partitioned table with different compression settings
Figure 7 Partitioned table with different compression settings
Each partition represents a quarter, with Oct-Dec being the latest quarter. Suppose the first two partitions aren't accessed frequently, the third partition is moderately active, and the last partition is the most active. In this case, one possible configuration is to enable PAGE compression on the first two partitions to get the maximum space savings with minimal impact on the workload performance, ROW compression on the third partition, and no compression on the last one.
You can enable compression, either online or offline, using Alter Table or Alter Index Data Definition Language (DDL) statements. SQL Server also provides a stored procedure to estimate the space savings. The space savings that you achieve will depend on the data distribution and the schema of the object being compressed.
Based on results seen from testing with many customer databases, it looks like most customers will be able to reduce the size of their database by 50–65 percent and improve the performance of their I/O-bound queries significantly. However, estimating the impact on the performance of CPU-bound queries is a bit trickier and depends on the complexity of the query. In SQL Server, the cost of decompression is only incurred when accessing the index or tables. If the relative CPU cost of scan operators is low compared to the overall CPU cost of the query, as is typically the case in the data warehouse scenario, you should see less than 20-30 percent impact on the CPU utilization.

Partition-Aligned Indexed Views
In SQL Server 2008, partition-aligned indexed views allow you to create and manage summary aggregates in your relational data warehouse more efficiently and use them in scenarios where you couldn't effectively use them before. This improves query performance. In a typical scenario, you have a fact table that is partitioned by date. Indexed views (or summary aggregates) are defined on this table to help speed up queries. When you switch in a new table partition, the matching partitions of the partition-aligned indexed views defined on the partitioned table switch, too, and do so automatically.
This is a significant improvement over SQL Server 2005, where you must drop any indexed views defined on a partitioned table before using the ALTER TABLE SWITCH operation to switch a partition in or out. The partition-aligned indexed views feature in SQL Server 2008 gives you the benefits of indexed views on large partitioned tables while avoiding the cost of rebuilding aggregates on an entire partitioned table. These benefits include automatic maintenance of aggregates and indexed view matching.

Partition-Level Lock Escalation
SQL Server supports range partitioning, which allows you to partition data for manageability or to group the data according to its usage pattern. So, for example, the sales data can be partitioned on a monthly or quarterly boundary. You can map a partition to its own filegroup and in turn map the filegroup to a group of files. This offers two key benefits. First, you can back up and restore a partition as an independent unit. Second, you can map a filegroup to either slow or fast I/O subsystem depending on the usage pattern or the query load.
An interesting point here is the access pattern of the data. The queries and DML operations may only need to access or manipulate a subset of partitions. So if you are, for instance, analyzing the sales data for 2004, you only need to access the relevant partitions and, ideally, you should not be affected, except for system resources by the queries concurrently accessing data in other partitions. In SQL Server 2005, the concurrent access of data in other partitions can lead to a table lock that can impact access to other partitions.
To minimize this interference, SQL Server 2008 introduces a table-level option to control lock escalation at partition or table level. By default, the lock escalation is enabled at the table level, as is the case in SQL Server 2005. However, you can override the lock escalation policy for the table. So, for example, you can set lock escalation as follows:
Alter table <mytable> set (LOCK_ESCALATION = AUTO)
This command instructs SQL Server to choose the lock escalation granularity that is right for the table schema. If the table is not partitioned, the lock escalation is at TABLE level. If the table is partitioned, then the lock escalation granularity is at the partition level. This option is also used as a hint by SQL Server to disfavor locking granularity at the table level.

Wrapping Up
This is just a brief overview of the enhanced features that are to be found in SQL Server 2008 that will help you to achieve better performance for decision support queries over relational data warehouses. But keep in mind that while competitive response times for your decision support queries is essential, there are other key requirements that are beyond the scope of this article.
Some additional functionality that is related to relational data warehousing includes the following:
  • Support for the MERGE syntax in T-SQL to update, delete, or insert (dimension) data with one statement and round-trip into the database.
  • Optimized logging performance of the SQL Server engine to allow for more efficient ETL.
  • Grouping sets to facilitate writing aggregate decision support queries in T-SQL.
  • Backup compression to reduce the I/O requirements for both full and incremental backups.
  • Resource governance to control system resource allocation to different workloads.
We encourage you to check out more detailed information about all those exciting features on the SQL Server Web page, available at microsoft.com/sql.
We'd like to thank Boris Baryshnikov, Prem Mehra, Peter Zabback, and Shin Zhang for providing their technical expertise.

Sunil Agarwal is a Senior Program Manager in the SQL Server Storage Engine Group at Microsoft. He is responsible for concurrency, indexes, tempdb, LOBS, supportability, and bulk import/export.
Torsten Grabs is the Senior Program Manager Lead for the Core Storage Engine in the Microsoft SQL Server team. He holds a PhD in database systems and has 10 years of experience working with SQL Server.
Dr. Joachim Hammer is a Program Manager in the Query Processing group at Microsoft. He specializes in query optimization for large-scale data warehouse applications as well as in distributed querying, ETL, and information integration.


 SQL Server: Minimize Blocking in SQL Server
SQL Server Cherié Warren

Minimize Blocking in SQL Server
At a Glance:
  • Why lock escalation occurs
  • Avoiding unnecessary blocking
  • Optimizing your queries
  • Monitoring the impact of locking on performance

Locking is necessary to support concurrent read and write activities on a database, but blocking can adversely affect system performance, sometimes in subtle ways. In this article, I'll take a look at how to optimize your SQL Server 2005 or SQL Server 2008 database in order to minimize
blocking, as well as how to monitor the system so you can better understand how locking impacts performance.

Locking and Escalation
SQL Server® chooses the most appropriate grain of lock, based on how many records are affected and what concurrent activity exists on the system. By default, SQL Server selects the smallest grain of lock it can, only choosing coarser-grained locks if it can more efficiently make use of the system memory. SQL Server will escalate a lock if the escalation will benefit overall system performance. As Figure 1 shows, escalations will occur when the number of locks in a particular scan exceeds 5,000 or when memory used for locks by the system exceeds the available capacity:
Figure 1 Conditions that cause lock escalation
Figure 1 Conditions that cause lock escalation
  • 24 percent of the non-address-windowing extensions (non-AWE) memory used by the database engine if the locks setting is 0
  • 40 percent of the non-AWE memory used by the database engine if the locks setting is not 0
If an escalation does happen to occur, it is always to a table lock.

Avoiding Unnecessary Blocking
Blocking can occur on any grain of lock, but blocking exposure is increased when escalations occur. Lock escalation may be a sign that your application is inefficiently designed, coded, or configured.
Adhering to database design fundamentals (such as using a normalized schema with narrow keys and avoiding bulk data operations on transactional systems) is important to avoiding blocking. If these principles aren't followed (such as separating out the reporting system from the transactional system or processing data feeds during non-business hours), it will be difficult to tune the system.
Indexing can be a key factor in how many locks are required to access the data. An index can reduce the number of records accessed by a query by reducing the number of internal lookups the database engine has to perform. For instance, when you select a single row from a table on a non-indexed column, each row in the table needs to be temporarily locked until the desired record is identified. In contrast, if that column were indexed, only a single lock would be needed.
SQL Server 2005 and SQL Server 2008 both contain dynamic management views (sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details) that reveal tables and columns that would benefit from indexes, based on accumulated usage statistics.
Fragmentation may also be implicated in performance issues, in that the database engine may need to access more pages than it might otherwise. Moreover, incorrect statistics can lead the query optimizer to choose a less efficient plan.
Keep in mind that while indexes speed data access, they can slow data modification because not only does the underlying data need to change, but the indexes need to update as well. The dynamic management view sys.dm_db_index_usage_stats highlights how frequently indexes are being used. A common example of inefficient indexing occurs with composite indexes, where the same column is indexed both in isolation and in combination. Since SQL Server accesses indexes from left to right, the index will be used as long as the left-most columns are helpful.
Partitioning tables can both optimize the system (so there is less block exposure) and divide the data into separate physical objects that can be separately contended for. While enabling row partitions is a more obvious way of separating the data, partitioning the data horizontally is another option to consider. You may intentionally choose to de-normalize by splitting a table into separate tables with the same number of rows and keys, but different columns, to reduce the chances that separate processes will want to exclusively access the data at the same time.
The more diverse the ways an application can access a particular row of data and the more columns that might be included in that row, the more a column-partitioning approach may be attractive. Application queuing and state tables can sometimes benefit from this approach. SQL Server 2008 adds the ability to disable lock escalations per partition (or per table when partitions are not enabled for the table).

Query Optimization
Query optimization plays an important role in enhancing performance. Here are three approaches you can take:
Shorten the Transaction One of the most important ways to reduce blocking, as well as improve overall performance, is to ensure that transactions are as small as possible. Any processing that is not critical to the integrity of the transaction (such as looking up related data, indexing, and scrubbing data) should be taken out to reduce its size.
SQL treats each statement as an implicit transaction. If the statement affects a large number of rows, a single statement can still constitute a large transaction, especially if there are many columns involved or if the columns contain a large data type. A single statement may also cause page splits if the fill factor is high or if an UPDATE statement is filling out a column with a wider value than had been allocated. In such circumstances, it can be helpful to split the transaction into groups of rows, processing them one at a time until they are complete. Batching should only be considered when the individual statement or groups of statements can be broken into smaller batches that can still be considered complete as a unit of work if they succeed or fail.
Sequence the Transaction Within the transaction, intentionally sequencing the statements can decrease the likelihood of blocking. There are two principles to keep in mind. First, access objects in the same order within all SQL code in your system. Without a consistent order, deadlocks may occur where two competing processes access data in a different order, causing a system error for one of the processes. Second, place frequently accessed or expensive-to-access objects at the end of the transaction. SQL waits to lock the objects until they are needed in the transaction. Delaying access to the "hot spots" allows these objects to hold locks for a smaller percentage of time.
Use Locking Hints Locking hints can be used at either the session level or the statement level for a specific table or view. A typical scenario for using a session-level hint would be for a batch processing in a data warehouse, where the developer knows the process will be the only one running at a given time on that set of data. By using a command such as SET ISOLATION LEVEL READ UNCOMMITTED at the beginning of the stored procedure, SQL Server will not reserve any read locks, thus reducing the overall locking overhead and increasing performance.
A typical scenario for using statement-level hints would be when the developer knows a dirty read can safely occur (such as when reading a single row from a table where other concurrent processes will never need that same row) or when all other performance-tuning efforts have failed (schema design, index design and maintenance, and query tuning) and the developer wants to force the compiler to use a specific kind of hint.
Row-lock hints may make sense if monitoring demonstrates that larger-grained locks have occurred where very few records are affected by the query, as this could reduce blocking. Table-lock hints may make sense if monitoring shows that smaller-grained locks are being held (and not escalated) when nearly all the records in the table are affected by the query, as this could reduce system resources needed to hold the locks. Note that specifying a locking hint does not guarantee the lock will not be escalated when the number of locks reaches the threshold for system memory. However, doing so will prevent all other escalations.

Adjust Your Configuration
As Figure 2 shows, there are a number of factors to consider when configuring your SQL Server system.
Figure 2 How SQL Server determines the amount of memory that can be used for locking
Figure 2 How SQL Server determines the amount of memory that can be used for locking
Memory Locks are always held in non-AWE memory, so increasing the size of the non-AWE memory will increase the capacity of the system to hold locks.
A 64-bit architecture should be the first choice when attempting to increase locking capacity, as 32-bit architecture is limited to 4GB of non-AWE memory whereas 64-bit is not limited at all.
On 32-bit systems, you can grab an additional gigabyte of memory from the operating system for SQL Server by adding the /3GB switch to the Boot.ini file.
SQL Server Configuration Settings A variety of settings can be adjusted via sp_configure that affect locking. The locks setting configures how many locks can be held by the system before throwing an error. By default, the setting is 0, which means the server will dynamically adjust locks reserved with other processes competing for memory. SQL will initially reserve 2,500 locks, and each lock consumes 96 bytes of memory. Paged memory is not used.
The min and max memory settings reserve the amount of memory used by SQL Server, thus configuring the server to statically hold the memory. Since lock escalation is related to available memory, reserving the amount of memory from competing processes can make a difference regarding whether or not escalations will occur.
Connection Settings By default, locks that are blocking don't time out, but you can use the @@LOCK_TIMEOUT setting, which causes an error to occur if the specified wait threshold for a lock to release is exceeded.
Trace Flags Two trace flags in particular relate to lock escalations. One is trace flag 1211, which disables lock escalations. If the number of locks consumed exceeds the available memory, an error is thrown. The other is trace flag 1224, which disables lock escalations for individual statements.

Watching Your System
Further Reading
  • Inside Microsoft SQL Server 2005: The Storage Engine by Kalen Delaney
  • How to Monitor Blocking in SQL Server 2005 and in SQL Server 2000
  • SQL Server 2005 Performance Statistics Script
  • How to Resolve Blocking Problems that Are Caused by Lock Escalation in SQL Server
  • Lock Escalation (Database Engine)

The impact that locking has on overall system performance can be monitored for blocks and locks by polling state data at given intervals (perhaps hourly) and capturing running statistics on the locks being held. Key information to capture is:
  • Affected object, grain, and lock type
  • Lock and block duration
  • SQL command being issued (stored procedure name, SQL statement within)
  • Information on the blocking chain, where relevant
  • How the system is consuming its available locking capacity
You can run a script like the one in Figure 3 to capture this information, writing it to a table with the relevant timestamp. And to further break down the ResourceId of the data being blocked, you could run a script like the one in Figure 4.
Figure 4 Learning more about blocked data (Close Figure 4)
DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

SET @SQL = N'
USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                                     END
                          FROM    sys.allocation_units
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL
                    END

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
        @SQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT
Figure 3 Capturing locking stats (Close Figure 3)
SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance,
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0
You can also monitor your system for escalations through SQL Profiler (Lock:Escalation event), the dm_db_index_operational_stats dynamic management view (index_lock_promotion_count), or regular polling of system locking information. The pertinent information to glean from escalation monitoring is whether the processing warrants an escalation; if not, the relevant stored procedures can pinpoint a root cause for performance problems. Tables with large amounts of data or high concurrent usage should be the primary focus of the evaluation.
After gathering data on locking, blocking, and escalations, the data can then be analyzed to determine the cumulative block and lock time (number of incidences multiplied by duration of incidences) per object. Typically, this can initiate an iterative cycle of performance tuning where changes are deployed, monitored, analyzed, and fixed. Sometimes all it takes is a simple change such as adding an index to make a significant performance improvement and will alter which area in the system constitutes the most painful performance bottleneck.
You can find more information about reducing blocking in SQL Server in the "Further Reading" sidebar. If careful attention is given to keeping transactions small throughout the design, coding, and stabilization phases, many blocking issues can be minimized. Appropriate hardware can significantly reduce the likelihood of unwanted escalations as well. Regardless, ongoing evaluation of blocking on the system can quickly identify performance problems at their root.

Cherié Warren is a Senior Development Lead for Microsoft IT. She is currently responsible for one of the largest transactional databases at Microsoft. Cherié also frequently consults on root cause and address performance problems related to blocking. She has been specializing in enterprise-level SQL Server databases for 10 years.


 Microsoft Office: Integrating Access Databases with SharePoint
Microsoft Office Peter Serzo

Integrating Access Databases with SharePoint
At a Glance:
  • Benefits of integrating Access and SharePoint
  • Migrating Access data to SharePoint
  • Extending Access functionality with SharePoint

When I talk to end users about Microsoft Office SharePoint Server (MOSS) 2007, I always ask them to show me the tools they use and how they use them. Without fail, someone somewhere in the
organization is using Microsoft® Office AccessTM to address essential business needs. Access is still a viable tool and an important asset at many companies.
MOSS 2007 provides a way to leverage applications built on Access and integrate them into SharePoint® to create a new solution with far greater reach. The focus of this article is to show how and where MOSS integrates with Access and how an end user can configure these two products to build a solution.
The underlying technology that MOSS brings to Access incorporates workflow, a common repository (SQL Server®), and a Web-based interface (the browser). SharePoint features include document management, workspaces, and the ability to set up custom lists and to utilize built-in workflows and out-of-the-box Web parts that cover everything from advertising events, to building a contacts list or an issues list, to managing user-created forms. Moreover, the ease of use and familiar Office technologies will enhance the end user's feeling of empowerment. As you will see, MOSS does not threaten the current solutions that are in place. Instead, MOSS facilitates efficiency since you do not have to rewrite applications, allowing end users to focus on resolving business issues rather than technical ones.
It's not only the end user who benefits. IT departments and administrators struggle with how to assist their end users in maintaining all the custom databases that spring up around a company. Many do not even know that Access is being used to solve mission-critical business problems until certain issues come up, such as storage limits, multiuser access, security, repairing corrupt databases, Web usage, incompatible Access versions, and deployment of changes across multiple desktops. SharePoint has an answer for each of these issues.
While Access has its limits, it is powerful and friendly, especially for those who don't write code for a living. In one spot you get configurable relational tables, simple queries (and complex ones), a drag-and-drop front-end form creator, reports based on the data, and the ability to configure multiple users. Building solutions based on Access and SharePoint makes sense both for organizations looking to pool resources and talent, and for individuals who want to take advantage of applications and skills built up over the years.

Getting Started
Access 2007 provides several points for integrating with MOSS 2007. As you can see in Figure 1, these include: importing data from a SharePoint List, publishing data to a SharePoint site, creating a SharePoint List, using SharePoint data as an external source, migrating an Access database to a new or existing SharePoint site, and offline accessing of SharePoint data.
Figure 1 Access 2007 provides easy entry points for integrating with SharePoint
Figure 1 Access 2007 provides easy entry points for integrating with SharePoint
Creating a SharePoint list from an Access table is a good place to begin. A SharePoint list is very similar to a table in Access. It is saved in the SQL Server content database, contains certain field attributes, and supports lookups.
Some limitations to keep in mind when creating a SharePoint list are: Microsoft recommends a limit of 2,000 items per view to obtain optimum performance; SharePoint doesn't enforce referential integrity; OLE Objects are not exported into SharePoint; and validation of data is limited.
To create a SharePoint list from an Access table, first select the External Data tab, then click on the enabled SharePoint List button under the Export group. Now you must specify the SharePoint site where this list will be active by filling out the Export - SharePoint Site dialog shown in Figure 2, or by right-clicking on your table and choosing Export to a SharePoint List. Note that if the table was a child in a relationship, all the parent tables would also be exported.
Figure 2 Access data will be exported to the SharePoint site you specify
Figure 2 Access data will be exported to the SharePoint site you specify
When the export process is finished, you are given the option of saving the export steps so you do not have to repeat them. At this point, you can go to the SharePoint site and see the data that was uploaded. This simple operation is useful for moving data into SharePoint and making it accessible to users. The benefits are increased security, as data is now being saved in a SQL Server database, and easier access, because information is available via the browser.
However, another operation—linking lists in SharePoint as tables in Access—turns out to be a much more effective way to integrate Access and SharePoint. To do this, click on the External Data tab in Access and choose SharePoint List under the Import group. This brings up the dialog box shown in Figure 3.
Figure 3 Linking Access to SharePoint
Figure 3 Linking Access to SharePoint
Choose the site that contains the SharePoint list that will be used as a linked data source, then select the list you want to link. When this operation is finished, the list will show up as a linked table in Access, and at the bottom right-hand corner of the application you'll see an "Online with SharePoint" indicator. There is now two-way synchronization between Access and SharePoint, supporting add, change, and delete operations from either SharePoint or Access. As you'll see, code can be written against the table to supply extra functionality.
When you link to SharePoint lists in Access, you'll find that another table, the Windows® SharePoint Services (WSS) Users Information List, is also imported and linked in by default. And an issues table is created called "Move to SharePoint Site Issues." Here you can review any conflicts with the data.
For more information on migrating data and how data types are handled during conversion, see "How Access Converts between Access and SharePoint Data Types" (office.microsoft.com/en-us/access/HP010477131033.aspx) and the section called Limitations of migration in "Move the Tables in an Access Database to a SharePoint Site and Link to Them" (office.microsoft.com/en-us/access/HA101314681033.aspx).
A third way to integrate tables and lists is initiated through SharePoint. Choose a list and, under the Actions tab, click on the "Open with Access" choice. A dialog box pops up that asks for the name of the database (new or existing). After entering the name of the database, you choose whether to simply link to the data on the SharePoint site or export the data outright into Access (see Figure 4).
Figure 4 Making SharePoint data reachable through Access
Figure 4 Making SharePoint data reachable through Access

Moving a Database to SharePoint
So far we have looked at importing a SharePoint list as a linked table and exporting tables to SharePoint as lists, but what if you need access to the whole database? Many organizations have mission-critical applications running in Access and need those applications to be available remotely so that individuals can run reports and queries and enter data. In such situations, it is not always feasible—economically or based on available skills—to rewrite the application. SharePoint and Access have this covered with the "Move to SharePoint" option, found under the Access SharePoint Lists tab. This is similar to the option to move to SQL Server using the Upsizing Wizard, which first appeared with Access 97.
The benefits of moving an entire Access database to SharePoint are that all the tables are converted to SharePoint lists; a backup is made of the Access database; lists automatically become linked tables in Access; you can move the whole database, not just tables; and the database works much as it did prior to the move.
When you click the Move to SharePoint button, the dialog box in Figure 5 appears. After specifying the site, you need to decide whether to choose the option to save the database. If you do, the whole database will be saved into a selected document library. This means that the entire database (including forms, reports, queries, and macros) is saved into SharePoint and is thus available on the server. When a user wants to access the database, he simply navigates to the library where it is, clicks on it, and it is automatically deployed to his desktop.
Figure 5 Specify the SharePoint site to which you want to move your database
Figure 5 Specify the SharePoint site to which you want to move your database
When you choose to move the whole database, a Publish Changes message bar appears below the ribbon. This allows you to keep the local changes to the design and data in sync with the copy on the server. If you don't select this option, only the tables will be converted into lists and linked into Access. In this case, the whole database is not available to the user community and is not backed up as an object in SharePoint.
After choosing one of these options, the database and its associated information will be available through the SharePoint site you chose. Again, a table is automatically created called "Move to SharePoint Site Issues." This is where any conflicts due to the migration are documented. You can review any issues that Access encountered while uploading the table on the SharePoint site.

Views, Forms, and Reports
After the database is moved, you'll want to test its functionality. Make sure the "Online with SharePoint" indicator is visible before starting. Note that Access queries become SharePoint views; a SharePoint view is very similar to an Access query.
When you move the whole database, the existing queries are copied into SharePoint as views. Access users understand that writing queries enables them to see a subset of data. Learning how to do this in a SharePoint View helps you understand how data is organized in a SharePoint list.
One of the most useful views you can create is an Access View. To do this, navigate to the list under which you will create the view, choose Views, then Create View. There you'll see the Access View option that lets you create forms and reports based on a list. When you click on this option and choose the Access database, a dialog like the one in Figure 6 pops up. As you can see, you now define the format (type of form, PivotChart, PivotTable, or Report), and when the process completes, there will be a linked form or report in the Access database.
Figure 6 Creating an Access View
Figure 6 Creating an Access View

Offline Access
In the real world, extending the reach of a mission-critical application translates into return on investment. One way to extend the reach of an integrated Access/SharePoint application is to realize that you do not have to be connected to SharePoint in order to use it. To facilitate such flexibility, Access 2007 introduced the ability to work offline. Information is cached in the local copy of the database until it is attached to the server. In order to take a database offline, click the Work Offline button on the External Data|SharePoint Lists group on the ribbon.
When you are ready to reconnect to SharePoint, you can choose to either Work Online or to Synchronize. Both options upload the data and present conflicts for review; with Synchronize, however, the data is uploaded but the application remains offline.
One other item to be aware of in the SharePoint Lists group is Discard Changes, which has two options. The first discards all changes but doesn't update the SharePoint data in Access. The second option discards all changes and updates the SharePoint data (linked lists).

Access Templates
Access 2007 has several prebuilt templates to assist users with rapid creation of business solutions, as you can see by clicking the SharePoint Lists button under the Create tab in the Tables group (see Figure 7). Choosing one of these automatically defines the schema for a table that is linked into SharePoint. Currently only Contacts, Tasks, Issues, and Events are supported. What this means is that if you export a table that contains one of these types of information, the data will end up as corresponding WSS content type (for example, Issues to Issues).
Figure 7 Choosing the type of list
Figure 7 Choosing the type of list
You can also create custom templates that can be used for SharePoint lists or create SharePoint Site templates with self-linking Access 2007 databases. To do this requires the Access 2007 Developer Extensions and setting the table's WSSTemplateID. This property tracks which tables have a SharePoint list template equivalent.

Access and Metadata
Businesses with entrenched Access applications might not always be able or willing to move all of these applications to SharePoint. In such cases, sometimes it makes sense to write code in Access to update SharePoint. With code, you can glue SharePoint and Access together.
A simple example is the case of the Northwind Trader's database, which stores information about products, suppliers, and customer orders. Each product typically has an associated product sheet supplied by the vendor as a .pdf file. The Access database tracks all the information contained on the product sheet, such as geographic location, product number, revision history, and company department code. This information needs to be available to suppliers and vendors via the SharePoint extranet.
A SharePoint site is set up and all the documents get moved to a linked document library, but the metadata fields are empty (see Figure 8) because the data is tracked in Access (and the person who monitors this is very comfortable with Access and not keen on changing).
Figure 8 Northwind document viewed in SharePoint, with metadata missing
Figure 8 Northwind document viewed in SharePoint, with metadata missing
Happily, as Figure 9 shows, you can add Visual Basic® for Applications (VBA) code to update the metadata via the Access application. The application doesn't have to be rewritten. The employee gets to continue using a tool that is comfortable while branching out onto the Web, and the vendors can access the extranet based on their permissions (so they see only what they should see), and there are no concurrency or database size issues.
Figure 9 Updating metadata using code
Figure 9 Updating metadata using code

Built-In Security
One of the greatest benefits of moving Access applications into SharePoint is being able to take advantage of the SharePoint security model, which has Active Directory® authentication built-in. That means no more worrying how to implement security in Access applications by creating schemas and tables and then adding users. With SharePoint lists, you can make use of security trimming, where users see only what they are allowed to see; this is innate in SharePoint. In addition, if there are components of the database you don't want to move onto SharePoint, you don't have to move them.
SharePoint provides a granular and role-based security model. When determining how and where to move a database, you should design the site and security before migration. It will be easier to set up and test permissions. Also, there will be fewer migration issues and the Access database will perform more efficiently.

Referential Integrity
I would be negligent if I didn't at least broach the subject of referential integrity. The fact is, SharePoint does not enforce referential integrity as Access does. Many installations are normalized at some level. This has to be accounted for in order for these mission-critical Access databases to work. Fortunately, there are a few workarounds that can actually be construed as enhancements to the existing system.
Earlier, I noted that when you move a database to SharePoint, if you moved child tables, the parent tables also move. In addition, lookup fields are used in SharePoint when it recognizes that a field choice is based on another table. For example, suppose a customer is chosen and an order is placed in the Northwind Traders database. If these tables were moved to SharePoint, the customer field would be a lookup in the Order list.
What about cascading updates/deletes and more intricate operations? This is where SharePoint and its workflows should be evaluated as a potential solution. Returning to the Northwind Traders database, suppose a customer calls and cancels an existing order, which then triggers several actions: inventory must be put back, suppliers need to be notified, the employee attached to the order must follow up with the customer, shipping must be canceled, and accounting needs to be updated. That's a lot of moving parts and a lot that must be coordinated.
Utilizing SharePoint Designer, a workflow can be created in SharePoint that accomplishes all of the above as soon as the status of the order changes. There are 22 out-of-the-box workflows that can be mixed and matched into hundreds of actions. SharePoint Designer is a great tool both for branding your site and creating workflows.
The best part is that using SharePoint Designer to create workflows involves skills that are familiar to users who know how to "program" Access. This means that users creating Access applications can transition and extend their scope of skill into workflow creation without too much effort. There is no code writing when it comes to utilizing SharePoint Designer for workflows, and deployment of these workflows is simple.

Peter Serzo is a Senior Engineer for CDW Berbee. He focuses on architecting SharePoint Enterprise solutions for companies around the globe.


 From the Editor: To Friends, Both Old and New
From the Editor
To Friends, Both Old and New

We must begin this month on a bittersweet note. Sadly, the time has come for us to wish a fond farewell to Joseph Davies, The Cable Guy, who concludes his TechNet Magazine column in this issue. We've been extremely fortunate to have an expert of Joseph's caliber to educate us on myriad Windows networking topics. Alas, Joseph has recently taken on a demanding new role at Microsoft, and so a monthly column is a task he must leave behind. All of us here at TechNet Magazine thank him for his terrific contributions over the years and wish him the very best of luck in his new endeavors.
As sad as we are to see The Cable Guy depart, the show must go on. So in our next issue we will debut a new column titled "Inside SharePoint." In the past year, we've received an overwhelming amount of feedback requesting more education and guidance on SharePoint. And since SharePoint is an exciting and fast-growing technology, we can't think of a topic more deserving of a monthly column. Pav Cherny will kick it off, starting back at the beginning with an architectural overview of SharePoint infrastructure. Over the coming months, we'll discuss security, availability, performance, troubleshooting, and much more. If you have any SharePoint topics you'd like to see us cover, please send them our way! E-mail tnmag@microsoft.com.
We must also extend an enormous debt of gratitude to two of our senior editors, Terrence Dorsey and Nancy Michell. Terry and Nancy have been with us since the beginning, together bringing their extensive talent and experience to bear on shaping TechNet Magazine into the publication it is today. Beginning with this issue, however, Terry and Nancy turn their full attention to our sister publication, MSDN Magazine. Two of our other extremely talented senior editors, Matthew Graven and Sharon Terdeman, will in turn say goodbye to their MSDN Magazine tasks to focus fully on TechNet Magazine. Fortunately, Terry and Nancy won't be going far, and we still have every intention of relying on their insights and expertise as TechNet Magazine continues to grow.
So with that housekeeping taken care of, I leave you to enjoy this month's issue. Largely devoted to the upcoming launch of SQL Server 2008, we discuss a variety of new features that will help you store, protect, manage, and leverage your data, which, given its ever-increasing quantity and expanding scope, is arguably your most critical business asset. —Joshua Hoffman


 Letters: Readers Speak Out
Letters
Readers Speak Out

Strong and Weak Hosts
The September 2007 The Cable Guy column (technetmagazine.com/issues/ 2007/09/CableGuy), "Strong and Weak Host Models," mentioned the ability to enable or disable the use of the weak host, but it did not specify how to do so. Is this merely enabling or disabling the firewall or setting a registry of parameter? Is there a registry parameter associated with this?
Also, local access configuration requires two pieces of information: the IP address and the IP mask. Why does Microsoft consider a configuration that does not contain the optional gateway parameter as invalid?
—Milt B.
You can configure strong or weak host behavior in Windows Vista® and Windows Server® 2008 with the commands listed in the figure (first published in the September 2007 issue). There are no corresponding registry entries. You can't configure strong or weak host behavior in Windows® XP and Windows Server 2003. IPv4 is weak host only; IPv6 is strong host only.
As for your second question, to our knowledge, IPv4 or IPv6 configuration on an interface does not require the configuration of a default gateway or router, either through the user interface or at the command line.


 Toolbox: New Products for IT Pros
Toolbox
New Products for IT Pros
Greg Steen

Manage Multiple Monitors
UltraMon
realtimesoft.com
With the price of LCD panels dropping, it seems most system administrators have at least two, if not more, monitors at their workstation to help them keep an eye on different administrative dashboards. (If you don't, I would highly recommend it—it makes life so much easier!) But with the addition of another monitor or two, you also start using your mouse more extensively, which can really slow you down. A good, easy-to-use display management utility with keyboard shortcuts can be a great help. One such tool worth considering is UltraMon from Realtime Soft, a utility that supports Windows® 98 up to Windows Vista®, including both 32-bit and 64-bit editions.
When you first start up UltraMon, you'll probably notice the two little icons in the corner of each of your application windows. The first icon quickly expands the application window across the monitors, and the second icon transports the application to the opposing monitor. You'll also notice that the taskbar is extended across each of your monitors and contains the applications active for that particular monitor. This means you don't have to go back to your primary monitor to expand an application, and you immediately know where that application will appear when it is maximized and minimized.
On each of your application shortcuts, a new UltraMon tab allows you to define on which monitor that application should start and at what position, so you can, for example, always have Microsoft® Outlook® show up on your left window and Internet Explorer® open up on the right. As an IT professional, you are most likely connecting to your workstation remotely from different locations such as a laptop or your home desktop; just know that each of these remote connections probably requires a slight change to the desktop and resolution of your display.
Fortunately, UltraMon also supports display profiles, which allow you to quickly change between customized views across your monitors. This is a great help if you are sharing your machine with others who need or want their own customized view or if you want to change your view based on your activities.
If you are running Windows 2000 or Windows XP, you can also enable a "mirroring" feature that is perfect for presentations, as it allows you to have your primary monitor run at a higher resolution than your presentation monitor but still mirror the application activities on both screens. And, if you haven't upgraded to Windows Vista yet, you can also have the application stretch your screen across multiple monitors (which Windows Vista does by default).
Additional display features—such as a shortcut to the Display Settings screen, the ability to quickly set and switch the primary monitor on your system, and switching to a different display profile—are a few of the quickly accessible repeated activities available with the UltraMon system tray icon. Here you can also launch the UltraMon Options window, which will let you customize your "Smart Taskbar," set up hotkeys to move and change windows, and bring your mouse to a specified focus point, among other things.

Price: Starts at $39.95 for a single license.
The options window in UltraMon lets you set up hotkeys and manage your Smart Taskbar
The options window in UltraMon lets you set up hotkeys and manage your Smart Taskbar

Book Review
Microsoft SQL Server 2005 Analysis Services
informit.com
Having extensive data about your customer base is one thing, but understanding and manipulating that data to derive useful and insightful statistics about those customers is quite another. To get to those calculated views and projections, you need a dynamic and powerful business intelligence solution. Part of the Microsoft answer to the business intelligence and online analytical processing (OLAP) arena is SQL Server® 2005 Analysis Services. And to help guide you through fully utilizing this solution is the book Microsoft SQL Server 2005 Analysis Services.
Whether you are considering a new data-mining deployment, upgrading to Analysis Services 2005 from Analysis Services 2000, or you are a database administrator interested in what these tools can do for you and your organization, this book by Py Bateman, Alexander Berger, Irina Gorbach, and Edward Melomed is for you. Instead of dwelling in the realm of the theoretical approaches to business intelligence, the book offers an in-depth, nuts-and-bolts view into using, developing for, deploying, and managing Analysis Services in the real world. Many of the examples it provides rely on Food Mart 2005, a sample database that comes with the software, so you can quickly start experimenting in your own test environment.
The introductory section of the book shows you what's new in version 2005 including the client-server architecture with Web service XML for Analysis (XMLA) communication, the Unified Dimensional Model, and improvements to scalability in the new version. You'll also get an introduction to the Food Mart sample database and the basics of multidimensional databases. Next you will jump into creating data models by learning about Data Definition Language (DDL) XML, dimension attributes and hierarchies, and cubes and multidimensional analysis. You will also get an introduction into the design and development workplace of the 2005 Analysis Services business intelligence professional with the Business Intelligence Development Studio.
Once you get those design concepts down, you move into data manipulation via multidimensional expressions (MDX). In addition to seeing how to query for data with MDX, you'll also learn how to use MDX functions and member and cell properties; deal with empty sets and nulls, Subselects, and Subcubes; and learn how to create and use cube-based MDX calculations and extend your queries with stored procedures. The book then delves into Key Performance Indicators (KPIs), drill-through, and scorecards, all of which can show insight into the various aspects of business growth, metrics, and targets.
The next chunk of the book will bring you through the extract, transform, and load (ETL) process of your data, showing you not only how to bring data into your system but also how to optimize your aggregation design and use proactive caching to increase performance. Here, too, you will work through the physical data model of your cubes and dimensions, and you'll see how the processing of each works with 2005 Analysis Services. In addition, you will learn how to scale your deployment out and up to meet your business needs, touching on network load balancing, OLAP farms, linked measure and dimension groups, and remote partition processing.
The book then moves into the real nitty-gritty of query execution to give you a better understanding of the underlying operations to help you fine-tune your application's performance. It explores accessing your data from a client perspective and the numerous options available for doing so such as XMLA, ADOMD.net, and the Analysis Management Objects (AMO). You have to break out the "coder-within" for this section to see how to utilize Visual Studio and the Business Intelligence IDEs to create your own custom data warehouse applications.
The last section delves into management of your Analysis Services installation including using the trace tool, flight recorder, and the SQL Server Profiler to monitor the performance and health of the system. In addition, you are shown various options for backup and restoration of your deployment with sections on using a DDL command to back up your database, automation through SQL Server Agent or SQL Server Integration Services (SSIS), and techniques to recover lost or damaged data. Overall, this guide to SQL Server 2005 Analysis Services is a thorough, broad-spectrum book.

Price: $59.99.

Diagnose Database Issues
ApexSQL Audit
apexsql.com/sql_tools_audit.asp
You probably know that the first step in troubleshooting an issue or tracking a potential breech is to gather the pertinent information surrounding the event that caused the problem. And if you are involved in end-user support, you're probably familiar with complaints along the lines of "the system lost the data." One tool that could help you quickly zero-in on issues with SQL Server databases is ApexSQL Audit from ApexSQL, which fully supports SQL Server 7.0 through SQL Server 2005.
As I'm guessing you've surmised from the name, this application tracks activity on the databases you specify and then allows you to create custom reports on the audit trail for analysis. Audit entries are added via SQL triggers and stored in an automatically generated schema. You can choose to either store the audit information in the same database as the data you are watching or in its own database for isolation.
Installation (and removal) of the audit schema (or architecture, as they call it) is a painless operation via the graphical UI. Setting up an audit plan for a database is relatively simple: you pick the database, select the tables and fields to watch, and then click the button to generate the triggers.
The application lets you modify the trigger templates for your project, and I really like that the template editor provides IntelliSense®-like capabilities for the audit object model, making it easy to customize both when the trigger will fire and what information will be stored. Another nice feature lets you easily reference look-up tables so the audit information will be understandable, as opposed to being just a bunch of opaque IDs. You can also map watched field names to audit field names if you want to map columns.
Once you are up and running, the UI can give you a quick view of the current audit triggers applied, and, with a click, you can enable and disable specific triggers if need be. You can also examine what audit coverage you have with the interface's handy table view.
As you know, audit information tends to be verbose, and some kind of information management plan needs to be put in place for archiving and trimming audit databases. ApexSQL Audit gives you a data management tool that lets you either delete all information with a click, specify a set of criteria and then delete, or schedule a criteria-based deletion.
Scheduled jobs are automatically created as Data Transformation Services (DTS) packages and scheduled through the SQL agent. Of course, having all of that audit information is one thing, but being able to use it is quite another. Fortunately, ApexSQL Audit has a strong reporting component that allows you to generate customized filtered views of both atomic audit data and aggregate views of that audit data. Once you generate your report, you can easily print it or export the output to HTML, Microsoft Excel, or a text file.

Price: Starts at $599.
The look-up tables in ApexSQL Audit provide extensive audit information
The look-up tables in ApexSQL Audit provide extensive audit information

Automate Password Expiration Reminders
Password Reminder PRO v1.4
sysoptools.com/password-reminder-pro.html
Have you noticed an influx of helpdesk calls when your password expiration policy rolls through a group in your organization? If so, you might want to check out Password Reminder PRO from SysOp Tools. This simple tool integrates with Active Directory® and notifies your end users that their accounts or passwords are expiring soon.
You can set up three different reminders, each with a distinct text-based or formatted HTML e-mail template. You can also configure a maximum password age to the same number of days as your Active Directory configuration and the hour at which reminders should be sent so you won't conflict with peak hours. The test mode lets you preview the effects without actually affecting your end users to ensure you have everything configured properly.
The utility also has a report console, which shows you a data-grid view of different user classes: accounts that are expiring, inactive users, new accounts, disabled accounts, and more. From this report console, you can export all the information to an XLS file for later review. For additional auditing of the e-mails that have been sent, Password Reminder PRO sends a daily report of which reminders were sent to which users.

Price: Starts at $249 for a 100-user license.
Password Reminder PRO previews configurations before deployment
Password Reminder PRO previews configurations before deployment

Remote Troubleshooting Simplified
UltraVnc SC
uvnc.com/pchelpware/sc
The easiest way to troubleshoot an issue or walk a user through a process is usually to be physically at the machine. But that's often not possible in today's geographically dispersed companies. The Windows built-in Remote Desktop Connection and Remote Assistance tools can help you connect to some of the machines in your environment. But to cover all the bases, you might want to check out the free UltraVNC SC, from the producers of the mainline project UltraVNC.
Like its older sibling, UltraVNC SC allows you to view and control a remote machine; however, this version is a tiny 166K UltraVNC server that you configure and send to your end user. It requires no registry entries, it doesn't run as a service, and it doesn't need an installer, so there is no setup or configuration for the end user. The user just double-clicks the executable to get the mini-VNC server running, and you can connect to the machine to support and administer, as needed.
Whereas the standard UltraVNC server supports file and directory transfers, currently UltraVNC SC only supports file transfer. In terms of security, communication is encrypted up to the level of your Microsoft Crypto API (either 40 or 128 bits). Also, the mini server doesn't allow incoming connections, it is only instantiated by the end user, and it can only connect to the machine specified in your configuration before sending it to your end user.
To create the custom viewer, tailor a few text configuration files and graphics and then run a batch file from the install of another project add-on called PCHelpware to generate the executable. The documentation isn't great, but it shows you how to go through the relatively simple process. Finally, the app is automatically uninstalled when the connection is closed after use, so it leaves no trace or security hole.

Price: Free.

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&amp;A: Grow Databases, Use IFilters, and Connect Remotely
SQL Q&A
Grow Databases, Use IFilters, and Connect Remotely
Edited by Matthew Graven

Tip: Clear Cache
Ever wonder why a stored procedure might work well in the test environment but work poorly when deployed on the production SQL Server? This may be a cache-related issue. Before you deploy stored procedures to your production environment, test the procedures in the test environment after having cleared cached query plans so that you know how your stored procedure will perform in a "cold" cache environment. Here are a couple handy things to keep in your bag of tricks.
To clear up the procedure cache on a SQL Server:
DBCC FREEPROCCACHE
Go
Query to list all cached plans:
Select * from sys.dm_exec_cached_plans
Go

Q I have a database that has heavy traffic during the day and I don't want to use autogrow because this could potentially cause timeouts if SQL Server®, decides to do this during peak hours. I would like to implement a periodic job to expand the database file by a specific percentage of the space used. How can I do this?
A File growth is an intensive disk I/O operation and if SQL Server has to wait for a data or log file to expand, you could certainly see this harm performance and response times. The default growth increment is 1MB for data files and 10 percent for log files, which can be insufficient growth increments for busy systems. Additionally, relying on autogrow can lead to disk fragmentation because the data or log files are not contiguous on disk—this means response times may be longer than necessary, as data is physically scattered on the disk.
The key to good performance is proactively allocating sufficient file space for data and log files up front. This often requires some trend analysis and growth forecast and will result in better performance because the files will be contiguous on disk and avoid the I/O cost required by autogrow during peak periods. Autogrow should generally stay enabled since a completely full data or log file will prevent access to the database altogether. But keep in mind that autogrow should be considered a safety net rather than a feature for managing databases.
Scheduling regular database file expansions should be avoided since this can also lead to noncontiguous files on disk and, in turn, reduced performance. Proactive monitoring can be achieved by regularly executing a script to determine the percent of free space for each database (executed by a SQL Agent job) and then taking an action (such as sending an e-mail alert via Database Mail). The code in the figure offers a sample script showing how to gather the percent of free space for the current database.
Once an alert has been generated, you can script one-time file growth with the ALTER DATABASE command, and you can use a SQL Agent job to schedule this action to take place outside of peak hours. Try to grow the file to a size that will be sufficient for the foreseeable future to avoid future small incremental file expansions. It's also a good idea to ensure autoshrink isn't enabled on any database—this could needlessly create database shrink or grow cycles.
—Justin Langford
Determining Free Space in a Database
-- Script to gather size, free space and
-- calculate % free space for current
-- USER database
DECLARE @size DEC(15,2)
DECLARE @free DEC(15,2)
DECLARE @result DEC(15,2)

SELECT @size = SUM(size)*1.0/128
FROM sys.database_files

SELECT @free =
(SUM(unallocated_extent_page_count)*1.0/128)
FROM sys.dm_db_file_space_usage

PRINT 'DB Size ' + CONVERT(VARCHAR(15), @size)
PRINT 'Free Space ' +
CONVERT(VARCHAR(15), @free)

SELECT @result = (@free/@size)*100

PRINT '% Free Space ' +
CONVERT(VARCHAR(15), @result)

Q My company stores different formats of files within our database using varbinary and image columns. I've heard that SQL Server has integrated functionality that will let me search these different file formats. How can I configure SQL Server to do this?
A This functionality is built into the full-text indexing service. The service provides the flexibility to use IFilter interfaces, making it possible to develop and load filters that can extract useful information from proprietary data. These IFilters are also used for other products, such as Microsoft® Office SharePoint® Server, to gather information about crawled files.
An IFilter is either provided by the creator of the file format or by third-party vendors. SQL Server already contains some IFilters that are loaded when the FulltextService (FTS) is installed—these include filters for HTML and DOC files. However, more IFilters can be added as required. For example, filters for Adobe PDF can be found on the Adobe Web site, and a new filter pack for 2007 Office system extensions was published at the end of 2007. Note that you must know which version of the IFilter is needed. For instance, an IFilter intended for 32-bit systems won't work with 64-bit installations of SQL Server.
After you run the installation package on the client, the IFilter will typically be registered in the ecosystem of the OS. With the bits registered in the OS, you need to perform a few steps to make FTS able to load the Filters. After starting your query execution tool, issue the following commands:
  • sp_fulltext_service 'load_os_resources',1. (This statement will enable FTS to load the registered bits for processing, including components like wordbreakers and stemmers.)
  • sp_fulltext_service 'verify_signature',0. (This will bypass SQL Server to check whether the used filters are signed, as many vendors do not sign their filters according to the standard.)
  • Restart the SQL Server instance and the instance of FTS.
  • Create your full-text index on the columns having the binary column as the content for the IFilter to crawl and the extension column (meaning the column with the extension type, such as DOCX) for SQL Server to choose the filter to which filter to redirect the content to.
More information can be found at go.microsoft.com/?linkid=7912971.
—Jens Suessmeyer

Q I am unable to connect to a remote SQL server. Do I need to configure the firewall on my client or server machine?
A Remote connections to SQL Server 2005 can fail for many reasons, but firewall configuration is one of the most common problems. The SQL Protocols blog (blogs.msdn.com/sql_protocols) is a great resource for information on making a SQL connection.
The default installation of SQL Server 2005 does not allow remote connections. From the machine running SQL, on the Start menu, select Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration. Here, go to Surface Area Configuration for Services and Connections, select Remote Connections, and select the "Using both TCP/IP and named pipes" radio button. You must then restart SQL for the change to take effect.
By default, SQL Server uses port 1433. To verify the port is open, use the following telnet command, replacing <ipaddress> with the actual IP address of the machine running SQL Server:
telnet <ipaddress> 1433
If you get a connection failed response, open the Windows® Firewall, go to the Exceptions tab, select Add Port, and add TCP port 1433. Telnet should now succeed. (Note that Telnet is not installed by default on Windows Vista®.)
—Rick Anderson
Thanks to the following SQL Server experts for contributing to this column:
Justin Langford works for Coeo Ltd., a Systems Integrator and Microsoft Certified Partner based in England. Jens Suessmeyer is a database consultant at Microsoft located in Germany. Rick Anderson works in Developer User Education at Microsoft. Saleem Hakani is a Senior Database Engineer and SQL Server Community Lead at Microsoft.


 Utility Spotlight: Web Capacity Analysis Tool
Utility Spotlight
Web Capacity Analysis Tool
Greg Steen

Web logs and historical traffic analysis may give you insight into the kind of traffic you can expect over time, but how can you tell what your infrastructure can really handle? To find out, you need a tool that can push your infrastructure and its application stack to the breaking point. One such tool is the free Web Capacity Analysis Tool (WCAT), a lightweight load-generation utility that plays back scripted HTTP requests against a Web server (or load-balanced server farm) while gathering performance statistics for later analysis. The latest version (6.3.1 as of this writing) is available for both x86 and x64 Windows systems (x86: iis.net/downloads/1466/ItemPermaLink.ashx; x64: iis.net/downloads/1467/ItemPermaLink.ashx) and has support for IPv6 networks. In addition, WCAT is multithreaded and supports multiple load-test clients controlled from a single source, so you can simulate thousands of concurrent users.
This utility lets you repurpose your old machines as test clients, and each of those test clients can spawn multiple virtual clients (the maximum number of which will depend on the network adapters and other hardware of the client machine). You can choose either HTTP 1.0 or HTTP 1.1 requests, with or without SSL. And, if your test scenario requires it, you can use scripted basic or NTLM authentication to gain access to those restricted portions of your site. (If your site uses cookie, form, or session-based authentication, you can create the correct GET or POST request to authenticate your test user.) WCAT also manages any cookies your site may set, so profile and session information will persist.
WCAT can help you test the limits of your Web site infrastructure
WCAT can help you test the limits of your Web site infrastructure
WCAT comes with a great user guide that helped me quickly create a Microsoft® .NET Framework console application that generated a test client script based on user traffic data from site traffic audit history, enabling me to simulate real-world traffic on a new codebase before deployment. And, if you have a C programmer on your staff, you can create custom functions and response handlers that can be called by your scenario files to tailor its functionality to your environment.
To use WCAT, you need to configure two files, a controller file that contains the general test settings such as the number of clients, virtual clients per client, performance counters to track, and the Web server you are testing against; and a scenario file that contains the test scenario, the default request parameters, and any references to custom functions and libraries.
The application logs the test results to an XML file to which you can apply the included XSL style sheet to get an organized tabulation of the results when viewed in Internet Explorer®. The report contains summary information such as the number of transactions executed, context switches that occurred, the percent of CPU utilization (of all CPUs), and the total number of errors encountered. You also get a section listing the results of the performance counters you configured in the settings file (see the screenshot for an example), a response-time analysis, a per-transaction statistics report, a per-client breakdown section, and information regarding how your test setup was configured. In addition, you'll see the hardware configuration of the remote Web server you are testing along with a listing of all the updates that have been installed. This is a great way to determine and track whether a patch will have any performance implications for your site.
All in all, if you manage a Web site infrastructure, the WCAT utility can help you gain insight into how your platform will perform under stress before that next big code release.

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.


 The Cable Guy: Troubleshooting NAP Enforcement
The Cable Guy
Troubleshooting NAP Enforcement
Joseph Davies

The new Network Access Protection (NAP) platform built into Windows Vista, Windows Server 2008, and Windows XP SP3 helps protect your private intranet by enforcing compliance with computer health requirements. Key components of NAP are NAP clients, NAP enforcement points, and NAP health policy servers.
A NAP client is a computer that can provide health status information for system health evaluation. A NAP enforcement point is a computer or network access device that uses NAP or can be used with NAP to require the evaluation of a NAP client's health state and provide restricted network access or communication. A NAP health policy server is a computer that is running Windows Server® 2008 and the Network Policy Server (NPS) service that stores health requirement policies and performs health evaluations of NAP clients. The NAP health policy server and NAP enforcement points exchange system health information and restricted access instructions with the Remote Authentication Dial-In User service (RADIUS) server and proxy messages.
In this column, I describe the components of a health requirement policy, how the NPS service processes incoming requests for NAP evaluation, and how to troubleshoot the most common issues with NAP enforcement.

Health Requirement Policies
How NAP Health Evaluation Works
The NPS service on the NAP health policy server uses the following process to perform a health evaluation:
  1. The NPS service compares the incoming request message to its configured set of connection request policies. For NAP, the request message should match a connection request policy that specifies that the NPS service perform the authentication and authorization locally.
    2. The connection request policy can also impose connection requirements. For example, for 802.1X and VPN enforcement, the connection request policy requires the use of a Protected Extensible Authentication Protocol (PEAP)-based authentication method. If the connecting client does not use PEAP, the connection request is rejected.
  2. The NPS service evaluates the health information in the request message, which consists of a system statement of health (SSoH) containing health status information. The NPS service passes the health status information to its installed SHVs, which return health evaluation information to the NPS service.
  3. The NPS service compares the request message and the health evaluation information from the SHVs to the appropriate set of network policies. The health evaluation information is compared to the Health Policy condition of NAP-based network policies. The Health Policy condition specifies the conditions for health compliance or noncompliance. The NPS service applies the first matching network policy to the request message.
  4. Based on the matching NAP-based network policy and its associated NAP settings, the NPS service creates a system statement of health response (SSoHR). This response includes the health evaluation information from the SHVs and indicates whether the NAP client has unlimited or restricted access and whether the NAP client should automatically attempt to remediate its health state.
  5. The NPS service sends a RADIUS response message with the SSoHR to the NAP enforcement point. If the client has been granted restricted access, the response message can also contain instructions that specify how the NAP client's access is restricted.
  6. The NAP enforcement point sends the SSoHR to the NAP client.

A health requirement policy is a combination of a connection request policy, one or more network policies, one or more health policies, and NAP settings for a NAP enforcement method. To create a health requirement policy, use the Configure NAP Wizard in the Network Policy Server snap-in. (For more information on the evaluation process, see the "How NAP Health Evaluation Works" sidebar.)
Connection Request Policies These are ordered sets of rules that allow the NPS service to determine whether an incoming connection request should be processed locally or forwarded to another RADIUS server. A NAP health policy server processes connection requests locally.
Incoming RADIUS requests from Windows®-based NAP enforcement points can contain a source tag that specifies the type of NAP enforcement point such as a Dynamic Host Configuration Protocol (DHCP) server or a virtual private network (VPN) server.
If the incoming request message contains a source tag, the NPS service on the NAP health policy server attempts to match the request against only those connection request policies with a matching source (all other connection request policies are ignored). If the incoming request message does not contain a source tag, the NPS service attempts to match the request against the connection request policies with an unspecified source (all other connection request policies that specify sources are ignored).
For example, incoming requests from a NAP-enabled DHCP server specify a source tag of DHCP. The NPS service attempts to match the requests from the DHCP server against connection request policies with the source of DHCP. Incoming requests from 802.1X-enabled switches and wireless access points do not specify a source tag. The NPS service attempts to match these requests against connection request policies without a specified source.
The connection request policy used for local or remote processing evaluation is the first matching connection request policy in the ordered list for the subset of the policies that apply to the incoming request. If the request does not match any connection request policies, the request is rejected.
Network Policies These are ordered sets of rules that specify the circumstances under which connection attempts corresponding to incoming request messages are either authorized or rejected. For each rule, there is an access permission that either grants or denies access, a set of conditions, a set of constraints, and network policy settings. If a connection is authorized, the network policy constraints and settings can specify a set of connection restrictions. For NAP, network policies can specify a health policy condition to check for health requirements and enforcement behavior settings.
Just like connection request policies, network policies use a source tag to determine which network policies to attempt to match to the incoming request. If the incoming request message contains a source tag, the NPS service attempts to match the request against only the network policies with a matching source (all other network policies are ignored). If the incoming request message does not contain a source tag, the NPS service attempts to match the request against the network policies with an unspecified source (all other network policies that specify sources are ignored).
The network policy used for authorization or health evaluation is the first matching network policy in the ordered list of subset for policies that apply to the connection attempt. If the attempt does not match any network policies, the request is rejected.
Health Policies These allow you to specify health requirements in terms of the installed system health validators (SHVs), which are used in health evaluation, and whether NAP clients must pass or fail any or all of the selected SHVs. For example, a health policy for compliant NAP clients can specify that the client must pass all health checks. A health policy for noncompliant NAP clients can specify that the client must fail at least one health check or all health checks.
Network Access Protection Settings These consist of the configuration of the SHVs installed on the NAP health policy server for health requirements and error conditions, and remediation server groups, which specify the sets of servers that are accessible to noncompliant NAP clients with restricted network access for the DHCP and VPN enforcement methods.

Scoping NAP Enforcement Problems
A logical approach is helpful when troubleshooting any problem. Some common questions to ask during troubleshooting include the following: What works? What does not work? How are the things that do and do not work related? Have the things that do not work ever worked? If so, what has changed since they last worked?
Obviously, these questions become more focused when dealing with NAP enforcement problems. Was the connection or connectivity working before the NAP enforcement method was deployed? For example, was the Internet Protocol security (IPsec) server or domain isolation working before deploying IPsec enforcement? Was 802.1X authentication working before deploying 802.1X enforcement? Was VPN remote access working before deploying VPN enforcement? Was DHCP working before deploying DHCP enforcement?
Furthermore, was a specific NAP enforcement method working before? If so, what has changed on the NAP client, NAP enforcement point, or NAP health policy server? The answers to these questions can indicate where to begin troubleshooting, possibly allowing you to isolate the component, layer, or configuration issue that is causing the problem.
For general troubleshooting of NAP enforcement problems, you need to determine the scope of the problem. The first step is to figure out whether the issue is, in fact, NAP enforcement. For IPsec enforcement, determine whether the NAP client has the correct set of IPsec policies for IPsec peer negotiation and data protection. For DHCP enforcement, find out whether the NAP client can exchange DHCP messages with a DHCP server. For 802.1X and VPN enforcement, determine if the NAP client can successfully authenticate. For example, if your VPN clients cannot perform authentication for the VPN connection, verify the authentication settings and credentials of the VPN clients.
If you have identified the problem as NAP enforcement, next determine its extent. Are all NAP clients for all NAP enforcement methods affected? Are all NAP clients for a specific enforcement method affected? Are all NAP clients for a specific enforcement method and NAP enforcement point affected? Are all NAP clients that are members of a specific group affected? Does the problem affect only a specific NAP client?
For example, if all of your NAP clients are experiencing problems for all types of NAP enforcement methods, there may be configuration issues on your NAP health policy servers. If all of your NAP clients are experiencing problems with a specific NAP enforcement method, configuration issues might exist for the Group Policy settings for NAP clients or with the health requirement policies for the specific NAP enforcement method on your NAP health policy servers. If only specific NAP clients are experiencing NAP enforcement problems, configuration issues for NAP enforcement might exist for those NAP clients.

Common NAP Enforcement Problems
NAP Resources
  • Introduction to Network Access Protection
  • Network Access Protection Platform Architecture
  • Network Access Protection Policies in Windows Server 2008
  • NPS Network Access Protection

Unlimited Access When a NAP client has unlimited access (it is not restricted), it may be because the NAP client has been evaluated as compliant by the NAP health policy server. This is exactly what you want. However, the unlimited access could also have been granted because the NAP client did not receive an SSoHR, which is what typically occurs when a health evaluation is not being performed for the NAP client. If there is no NAP health evaluation, there is no SSoHR sent back to the NAP client and the NAP client gets unlimited access.
For example, a NAP client configured for DHCP enforcement will send its SSoHR to its DHCP server. If the DHCP server running Windows Server 2008 is not configured for NAP, it won't send health evaluation request messages to a NAP health policy server. Moreover, a DHCP server running Windows Server 2008 configured for NAP that cannot reach a NAP health policy server assigns, by default, an address configuration for unlimited access.
Unlimited access may also result when the NAP health policy server does not perform a health evaluation because the incoming request matched a network policy that does not require NAP health evaluation. For information about determining the network policy that matched a NAP client's request, see the "Troubleshooting NAP Enforcement Step-by-Step" section in this column.
Restricted Access A NAP client that has restricted access has been evaluated as noncompliant by the NAP health policy server. If the NAP client has restricted access but should have unlimited access, verify the health state of the NAP client against the health policy settings corresponding to the network policy that matched the NAP client's request.
No Auto-remediation When properly configured, NAP clients can automatically remediate their health state. If NAP clients cannot auto-remediate, verify that the Enable Auto-Remediation of Client Computers checkbox is selected for the NAP Enforcement settings of the network policy that matched the NAP client's request.
Another problem that can affect auto-remediation is when the restricted NAP client cannot reach the remediation servers to download updates. For IPsec enforcement, verify that the remediation servers have the correct IPsec policy applied. For 802.1X enforcement, check the settings in the matching network policy that assign the access control list (ACL) or virtual local area network identifier (VLAN ID) and verify the configuration of the ACL or VLAN for your switches or wireless access points (APs). For VPN or DHCP enforcement, check the settings in the matching network policy for the remediation server group and ensure that all remediation servers are configured as members of the group. For DHCP enforcement, ensure that the DHCP scope options for NAP clients are configured correctly, such as the Default Gateway option value for the NAP user class.
Non-NAP-Capable Evaluation For any NAP enforcement method, verify that the corresponding enforcement client is enabled on the NAP client. For 802.1X and VPN enforcement, verify that the NAP client has system health checking for the PEAP authentication method enabled on both the connection request policy and on the NAP client (the Enable Quarantine Checks checkbox in the Protected EAP Properties dialog box is selected).
NAP Client Not Restricted but Can't Reach Intranet For IPsec enforcement, verify that the IPsec policy for compliant NAP clients and the IPsec policy of the intranet computers have a common set of negotiation and protection settings. For 802.1X enforcement, confirm that the network policy for compliant NAP clients is specifying the ACL or VLAN ID for the intranet, rather than the restricted network. Verify the configuration of the ACL or VLAN ID for the intranet on your switches or wireless APs. For VPN enforcement, make sure that there are no IP packet filters configured on the network policy for compliant NAP clients that restrict the traffic of VPN clients.

Troubleshooting NAP Enforcement Step-by-Step
One way to step your way through troubleshooting a problem with NAP enforcement for a specific NAP client is to start with the configuration of the NAP client, check the NAP client event log, and then go to the NAP health policy server to determine how the NPS service processed the request of the NAP client.
Step 1: Check the NAP Client Configuration NAP client configuration consists of Windows services, enforcement clients, and NAP enforcement-specific settings. To display NAP client status and configuration information, use the netsh nap client show state and netsh nap client show configuration commands.
Note that if NAP client settings are being provided by Group Policy, then all of your NAP client settings are specified by Group Policy and all of your local NAP client settings are ignored. In that case, use the netsh nap client show grouppolicy command to display the Group Policy-based NAP client configuration and make changes through Group Policy.
For IPsec enforcement on a computer running Windows Vista®, use the net start command to verify that the Network Access Protection Agent, IKE and AuthIP IPsec Keying Module, and IPsec Policy Agent services are started. Use the netsh nap client show configuration command to verify that the IPsec Relying Party enforcement client is enabled. If needed, use the NAP Client Configuration snap-in to enable the IPsec Relying Party enforcement client or use the netsh nap client set enforcement 79619 enabled command. Netsh nap client set enforcement commands should be run from a command prompt with elevated privileges.
For 802.1X enforcement on a computer running Windows Vista, use the net start command to verify that the Extensible Authentication Protocol, Network Access Protection Agent, Wired AutoConfig (for 802.1X enforcement on wired connections), and WLAN AutoConfig (for 802.1X enforcement on wireless connections) services are started.
Use the netsh nap client show configuration command again to verify that the EAP Quarantine enforcement client is enabled. If needed, use the NAP Client Configuration snap-in to enable the EAP Quarantine enforcement client or use the netsh nap client set enforcement 79623 enabled command. Verify that the Enable Quarantine Checks checkbox is selected for the properties of the Protected EAP authentication method for the wired or wireless connection.
For VPN enforcement on a computer running Windows Vista, use the net start command to verify that the Extensible Authentication Protocol, Network Access Protection Agent, and Remote Access Connection Manager services are started.
Use the display of the netsh nap client show configuration command to verify that the Remote Access Quarantine enforcement client is enabled. If needed, use the NAP Client Configuration snap-in to enable the Remote Access Quarantine enforcement client or use the netsh nap client set enforcement 79618 enabled command. Verify that the Enable Quarantine Checks checkbox is selected for the properties of the Protected EAP authentication method for the VPN connection.
For DHCP enforcement on a computer running Windows Vista, use the net start command to verify that the Network Access Protection Agent and DHCP Client services are started. If needed, use the Services snap-in or the Sc.exe tool to start these services and configure them for automatic startup.
Use the display of the netsh nap client show configuration command to verify that the DHCP Quarantine enforcement client is enabled. If needed, use the NAP Client Configuration snap-in to enable the DHCP Quarantine enforcement client or use the netsh nap client set enforcement 79617 enabled command.
Step 2: Check the NAP Client Event Log Use the Event Viewer snap-in to check the events in the event log created by the Network Access Protection Agent service. On computers running Windows Vista, use the Event Viewer snap-in to view events in Applications and Services Logs\Microsoft\Windows\Network Access Protection\Operational. On computers running Windows XP with SP3, use the Event Viewer snap-in to view NAP events in the System event log.
From the NAP client events, obtain the correlation ID for the NAP client health evaluation. You can use the correlation ID or the computer name of the NAP client to find the corresponding health evaluation event on the NAP health policy server. Figure 1 shows an example of an event for a non-compliant NAP client with the correlation ID.
Figure 1 Example of a Windows Vista NAP client event
Figure 1 Example of a Windows Vista NAP client event
Step 3: Check the NPS Event Log On the NAP health policy server, use the Event Viewer snap-in to view the events in the Windows Logs\Security log that are created by the NPS service. To view NPS events, in Event viewer, open Custom Views, then Server Roles, and then click Network Policy and Access Services. To view an online help topic associated with the event, click on the "Event Log Online Help" link on the General tab of the event.
Typical NAP health evaluation events have the event ID 6278 (for unlimited access) or 6276 (for restricted access). Find the corresponding NAP health policy server event based on the computer name of the NAP client (the Account Name field in the Client Machine section of the event description) or the correlation ID (the Session Identifier field in the Quarantine Information section of the event description).
NPS event log events 6278 and 6276 contain information about the NAP health evaluation, such as the name of the matching connection request policy (the Proxy Policy Name field in the Authentication Details section of the description of the event) and the matching network policy (the Network Policy Name field in the Authentication Details section of the description of the event). Figure 2 is an example of the Details tab of an event ID 6276, with the Prefix Policy Name, the Network Policy Name, and the correlation ID (named QuarantineSessionID). This is the NAP health policy event corresponding to the Windows Vista NAP client event in Figure 1.
Figure 2 Example of a NAP health policy server event
Figure 2 Example of a NAP health policy server event
If there is no event corresponding to the NAP client event, verify that the Network Policy Server service is started and that the NAP enforcement point is properly configured for NAP and to use the NAP health policy server as a RADIUS server. Verify that RADIUS messages can be sent between the NAP enforcement point and the NAP health policy server.
By following these three steps, you should be well on your way to figuring out why your NAP enforcement is not working as expected. For more information about NAP, see the "NAP Resources" sidebar.

Joseph Davies is a technical writer with Microsoft and has been teaching and writing about Windows networking topics since 1992. He has written eight books for Microsoft Press and is the author of the monthly online TechNet Cable Guy column.


 Windows PowerShell: Sign Here, Please
Windows PowerShell
Sign Here, Please
Don Jones

In the January 2008 Windows PowerShell column, I stressed the importance of digitally signing your Windows PowerShell scripts. And I described some scenarios where any execution policy other than AllSigned could create significant security vulnerabilities in your environment.
One thing I didn't cover in much detail is how to actually sign your scripts. You can take a look at that column at technetmagazine.com/issues/2008/01/PowerShell. Guess what I'm covering this month?

More than Just a Signature
The word signature is the correct technical term for what I'm talking about, though the word itself isn't really a good description of what is actually happening here. Signing a script doesn't mean you're approving or authorizing it, as you might do with a contract or a credit card slip. In the world of digital security, signing is the process of affixing your identity to something and ensuring that the state or condition of that "something" has not been modified in any way. It's all based on cryptography, and cryptography (at least in this case) starts with a pair of encryption keys.
These keys are referred to as asymmetric keys because they are different from one another. The pair consists of a private key, which is accessible only to you, and a public key, which is accessible to anyone. I'm simplifying this a bit, but basically anything encrypted with the private key can only be decrypted with the matching public key.
Here's how it works. Again, I'm using some simplification just to keep the discussion moving along. I have a Windows PowerShell® script that I want to sign and I have a certificate containing my private key that I want to use to sign the script. Windows PowerShell itself has a cmdlet, which I'll discuss in a moment, that can take the script and the certificate and perform the actual signing. When it does this, Windows PowerShell takes the script and encrypts it using my private key. That encrypted copy appears as gibberish text that is added to the bottom of the script as a series of comments (see Figure 1). My identity is also encoded, but not encrypted, into those comments—the identity itself can be read without resorting to cryptography.
Figure 1 Signature block stored at the bottom of your script
Figure 1 Signature block stored at the bottom of your script
Later, when Windows PowerShell looks at the signature, it decodes my identity and uses it to obtain my public key. Since only my public key can decrypt the rest of the signature, Windows PowerShell knows that if it's able to decrypt the signature, then I must have signed it. If someone else had signed it, my public key wouldn't be able to decrypt the signature. Once the signature is decrypted, Windows PowerShell compares the script to the copy that had been encrypted into the signature. If they match, then the signature is considered intact. If the two scripts differ, the signature is broken and considered invalid.
This is how a signature protects a clear-text script from being modified without someone noticing. Remember, while the script itself can be easily changed, the signature can't be changed to match the modified script without using my private key—and only I have my private key.
This is definitely a simplification of the underlying technical process. In reality, signatures can and do contain more information, such as a copy of my public key, information about the certificate authority (CA) that issued the keys, and so on. But my description here certainly outlines the important parts of the process and highlights the fact that a signature actually does protect your script from unauthorized modification.
A critical security factor is the absolute protection of your private key—you don't want this falling into someone else's hands. When you install your key in Windows, you should immediately password-protect it so that a malicious process can't use your key without your knowledge. Smart cards provide even better security since they contain your private key. With a smart card, your private key never leaves the card. Instead, the data that you want to encrypt is transmitted to the card through its reader hardware, and the circuitry of the card itself performs the encryption and sends the result back.

Getting a Certificate
Cmdlet of the Month: Export-CSV
The folks in the forums at PowerShellCommunity.org often ask if Windows PowerShell can export to Microsoft Excel®. Of course it can! The Export-CSV cmdlet does the trick, since Excel can natively open, edit, and save CSV files. For example, if you want to export a list of services, just run Get-Service | Export-CSV MyServices.csv. In fact, you can tack Export-CSV onto the end of any pipeline and all the objects in that pipeline will wind up in the CSV file you specify. By default, Export-CSV adds a header line to the top of the file, which it uses to specify the type of object that was exported. This line is commented so it won't prevent Excel from opening the file. If you don't want that type information exported, you can just add the -notype parameter to Export-CSV. In addition, you can add the -force parameter to force overwriting an existing CSV file of the same name without prompting (the default is to prompt), or you can add the aptly named -noClobber parameter to prevent overwriting an existing file.

To sign scripts, you will need a specific kind of certificate—a Class III Authenticode Code-Signing Certificate—and there are three main ways to get one. The first is to use your organization's internal Public Key Infrastructure, or PKI, if it has one. A well-implemented PKI will have its root CA trusted by all of your organization's computers—this is necessary for the certificates issued by the CA to be usable in the organization.
Windows Server® has shipped with its own Certificate Server software since Windows® 2000, and you can use that software to create your own PKI. A full-fledged PKI implementation requires a lot of planning, but if you just need a PKI for the sole purpose of issuing code-signing certificates, you don't need to do a ton of work. You can simply use Group Policy to push your CA's root certificate out to your computers so that they'll trust it.
A second option is to use a commercial CA. One benefit of using a commercial CA is that, if you select one of the major CAs, your organization's computers are probably already configured to trust certificates from that CA. (Note that Windows XP trusts a large number by default, while Windows Vista® trusts a far smaller number by default). One popular commercial CA is VeriSign (verisign.com). There are others worth investigating, also, such as CyberTrust (cybertrust.com) and Thawte (thawte.com).
The third option for obtaining a code-signing certificate is to make your own self-signed certificate using a tool like Makecert.exe. It ships with the Windows Platform SDK, it installs in some editions of Microsoft® Office, and it can be found in many other places. The upside of a self-signed certificate is that it's free and doesn't require any infrastructure. The downside, however, is that it's really only usable on your computer. But if you just need to enable script execution on your computer—for testing or general experimentation with code signing—Makecert.exe is a great option. Documentation about this tool is at go.microsoft.com/fwlink/?LinkId=108538. Just be aware that many versions of this tool have existed over the years, so it's possible that your computer is running an older version that doesn't work exactly as the documentation describes.
Once you have Makecert.exe, you can then create a self-signed certificate by running something like the following from the Windows PowerShell command line (and don't let me catch anyone using cmd.exe when you're reading this column):
makecert -n "CN=MyRoot" -a sha1 –eku
1.3.6.1.5.5.7.3.3 -r -sv root.pvk root.cer
–ss Root -sr localMachine
And then run the following:
makecert -pe -n "CN=MyCertificate" -ss MY
–a sh1 -eku 1.3.6.1.5.5.7.3.3 -iv root.pvk
–c root.cer
Makecert.exe will prompt you for a password to protect the key, and then it will create the certificate. You can verify the installation by running this code in Windows PowerShell:
gci cert:\CurrentUser\My -codesigning
This will display a list of all the items in the CERT: drive (that's your Windows certificate store) that are code-signing certificates. By the way, all of this is also documented in Windows PowerShell itself. To find that documentation, just run help About_Signing and read down a few screens.

Signing a Script
Now that you've got a certificate and you've got a script (for testing purposes, you can just make one up quickly, if you need to), you're ready to sign the script. It's shockingly easy with Windows PowerShell—just enter:
$cert = @(gci cert:\currentuser\my
-codesigning)[0]
Set-AuthenticodeSignature myscript.ps1 $cert
The first part retrieves the first installed code signing certificate (if you have multiple certificates installed and want to use one other than the first, just change the "0" to the appropriate number). The second part signs the file (of course, you'll need to change the filename to match yours). It's that simple! To be totally honest, though, I find the Set-AuthenticodeSignature cmdlet name to be a bit longer than necessary, so that's why I created an alias called Sign. Now, I can just run:
Sign myscript.ps1 $cert
Now open that script and you'll see the signature block inserted at the bottom. Try running the script with your execution policy set to AllSigned (Set-ExecutionPolicy AllSigned) and it should work fine. Now you'll want to modify the script and save it, but make sure you don't sign it again. Windows PowerShell should now simply refuse to run the modified version because the signature has been broken.

Worth the Effort
Isn't this really more hassle than it's worth? Is it asking too much to be able to script a few administrative tasks without having to drop $500 on a certificate or implement a whole new infrastructure element? Look, I'm an admin, too and I understand your concerns. But the simple answer is that this is definitely worth every bit of the effort it takes.
The fact is security almost always involves some level of hassle. Antivirus software can be a pain, but you aren't going to run without it. Firewall software is definitely a hassle, but as you know, it isn't safe to run without a firewall. And Windows Vista User Account Control (UAC) can also be a hassle, but it makes your computer safer. And isn't it this improved security that we're all trying to achieve?
Windows PowerShell is a powerful tool, and like any other tool, there is always the potential for it to be twisted into a security vulnerability by a malicious user. That is why it is important for you to take steps to head off these malicious users.
Code signing is the best step you can take, and it doesn't take much involvement. For example, I use a graphical script editor that automatically signs my scripts every time I press Save, so code signing is pretty much transparent for me.
You can make it more transparent even without a fancy editor. For example, you can create a Windows PowerShell profile for yourself (create the file Microsoft.PowerShell_profile.ps1 in a folder named WindowsPowerShell, located under your documents folder), and add this function to it:
function sign ($filename) {
  $cert = @(gci cert:\currentuser\my
-codesigning)[0]
Set-AuthenticodeSignature $filename $cert
}
Now you can sign your script by simply entering:
Sign c:\scripts\myscript.ps1
Of course, that profile script should be the first one you sign! The idea is to take a few steps to make script signing as convenient as possible, so that it's not a hassle.
Deploying a one-server PKI for the sole purpose of issuing a code-signing certificate isn't much work. (Keep in mind, though, that you really do need to research and plan your PKI, including protecting the root CA and providing disaster recovery, especially if it will be used for anything else.) Makecert.exe is always available if you're the only one who will need to run scripts in your environment. Note that the About_Signing Windows PowerShell help topic also includes information on securing the certificate produced by Makecert.exe.

Don Jones is the author of Windows PowerShell: TFM and VBScript, WMI, and ADSI Unleashed. You can contact him through the PowerShellCommunity.org Web site.


 Hey, Scripting Guy!: The Return of the Database Script
Hey, Scripting Guy!
The Return of the Database Script
The Microsoft Scripting Guys
Download the code for this article: HeyScriptingGuy2008_04.exe (151KB)

You know, if the Scripting Guys have a weakness (come on, we said if), it's this: we worry too much about the possible consequences of the actions we take, or don't take. Rather than act in our own best interests, we often hold back and, instead, do what we think will be in the best interests of everyone else. And we do that regardless of what those actions might mean for us.
Take articles about database scripting, for example. To be perfectly honest, articles about database scripting are difficult for the Scripting Guys to write. That's not because database scripting is hard; it's actually pretty easy. Instead, the problem is that the Scripting Guys don't have many chances to work with databases; because of that, any time we write about databases we have to stop and think about what we're doing. As you probably figured out a long time ago, thinking is definitely not a requirement for becoming a Scripting Guy.
As a matter of fact, if any of us had really thought about it in the first place, there probably wouldn't even be any Scripting Guys.
So if articles about database scripting are so hard for the Scripting Guys to write, why do we continue to write them? The answer to that is simple: we're concerned about what might happen to the rest of the world if we stop writing these articles. When Sir Arthur Conan Doyle decided to kill off Sherlock Holmes (by having him fall off a cliff in the story "The Final Problem"), he thought he was simply getting rid of a character he no longer wanted to write about. Instead, a huge outcry arose around the world. According to some reports, people in London actually wore mourning cloths on their hats and sleeves when they heard the news. "The Final Problem" was published in The Strand Magazine in 1893; in response, 20,000 people canceled their subscriptions.
Ouch.
The Scripting Guys are concerned that killing off our articles on database scripting would have a similar effect worldwide. We don't want to be the cause of so much heartache and despair, and we certainly don't want 20,000 of you to cancel your subscription to TechNet Magazine. Therefore, we'd like to announce that the Scripting Guys will continue to write articles about database scripting. When? Well, how about right now?

Adding Records to a Database
What we thought we'd do this month is show you a few nifty little tricks for working with databases. And yes, these are in addition to the tricks we showed you in our September 2007 column (technetmagazine.com/issues/2007/09/HeyScriptingGuy); even the Scripting Guys wouldn't stoop so low as to run the exact same article twice.
Well, not unless we thought we could get away with it.
Let's start out by showing you a simple way to add a record to a database. Assume we have a database named C:\Scripts\Inventory.mdb, and that this database includes a table named Computers. The Computers table includes the following fields:
  • ComputerName
  • SerialNumber
  • UserName
  • Department
Now, how do we add a new computer to that database? Take a look at the code in Figure 1.
Figure 1 Adding a record to the Computers table (Close Figure 1)
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "INSERT INTO Computers (ComputerName, SerialNumber, UserName, Department)" &  _
    "VALUES ('atl-ws-07', '11234', 'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, adLockOptimistic
We're not going to talk about the first lines of this script in any detail; if you need that sort of background information you might want to take a look at some of the resources we have in the Script Center at microsoft.com/technet/scriptcenter. Suffice it to say that we use the constants adOpenStatic and adLockOptimistic to control cursor type and record-locking for our recordset. (Yes, that sounds impressive, but it's really pretty simple stuff.) After we're done creating instances of the ADODB.Connection and ADODB.Recordset objects, we then use this command to open our database:
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"
By the way, that command opens a Microsoft® Access® 2003 database. To open an Access 2007 database, use this command instead:
objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = C:\Scripts\Inventory.accdb"
And, although we're beginning to stray a bit from the main topic, you might use a command similar to this to open a SQL Server® database:
objConnection.Open _
"Provider=SQLOLEDB;" & _
"Data Source=atl-sql-01;" & _
"Trusted_Connection=Yes;" & _ "InitialCatalog=Inventory;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"
Up to this point, everything we've shown you is boilerplate code; as you'll soon see, pretty much all your database scripts will start off with these same lines of code. The part we want to emphasize is actually this line:
objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName, Department) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, _
        adLockOptimistic
Here we're using an INSERT INTO query to, well, insert a new record into the Computers table. Notice that, following the table name, we include the names of all the fields for which we have values, separating those field names with commas and enclosing all the names in parentheses.
You might be asking: what do the Scripting Guys mean when they say "all the fields for which we have values"? Good question. Well, let's suppose this new computer has yet to be assigned a Department. Assuming the database allows us to put a Null value in the Department field, we can simply leave Department off the list and not assign a value to it. In other words, we could write a query like this:
objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer')", _
        objConnection, adOpenStatic, _
        adLockOptimistic
Speaking of assigning values, look what comes after the field names: the VALUES keyword followed by the values we want to assign to each field (which are also enclosed in parentheses). Keep in mind that when we list the field names, they can be in any order; for example, we put ComputerName first even though it might not be the first field in the database. That's fine for field names, but not so fine for values. Values must be listed in the exact same order as the fields. If our first listed field is ComputerName, then our first value must be the name of the computer. If it isn't, we're going to run into problems. (For example, we're going to end up assigning the user name or serial number to the ComputerName field.)
As you can see, assigning values isn't very complicated; just make sure that you format the values according to data type: dates and string values should be enclosed in single quote marks; numeric and Boolean values should not be enclosed in single quotes.
Oh, and if you have a value that actually has it's very own single quote mark (like the name O'Brien), then you need to "escape" that single quote by doubling it:
'O''Brien'
Crazy, but true.

Deleting Records from a Database
That was actually pretty cool: we can add a record to a database just by executing a single SQL query. Could there be anything better than that? Not that we know of.
Well, except maybe being able to delete multiple records from a database using a single query.
Suppose your company decides to eliminate its Human Resources department. (No, no jokes; we're usually in enough trouble with the Microsoft HR department as it is.) How can you delete all the Human Resources computers from your Inventory database? Figure 2 shows one way to do that.
Figure 2 Deleting multiple records (Close Figure 2)
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "DELETE * FROM Computers Where Department='Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic
As you can see, this particular script starts out exactly the same way as our script for adding a new record: by defining some constants, creating a couple of objects, then connecting to the Inventory.mdb database. After that we simply use a DELETE query to delete all the records (*) from the Computers table, or at least all the records where the Department field is equal to Human Resources:
objRecordSet.Open _
  "DELETE * FROM Computers " & _
   "Where Department='Human Resources'", _
    objConnection, adOpenStatic, _
    adLockOptimistic
It's as easy as falling off a cliff.
Um, no offense, Sherlock Holmes.

Updating Records in a Database
No doubt everyone agrees how much fun it would be to delete all traces of your Human Resources department. (Note to Microsoft HR department: we mean fun for other people and their HR department. Not us. We wouldn't enjoy it at all.) Nevertheless, deleting all the HR computers from the Inventory database might not be the best thing you could do. Why not? Well, unless your HR employees absconded with all their hardware (which sounds like something they would—well, never mind; we said no jokes) those computers still belong to your company; that means those computers should still be listed in the database. With that in mind, let's do this. Instead of deleting those records from the database, let's simply update each record; we'll just change the department name for each of these computers from Human Resources to None. In other words, we'll run the script shown in Figure 3.
Figure 3 Updating records (Close Figure 3)
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "UPDATE Computers Set Department = 'None' " & _
    "Where Department = 'Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic
Again, the only thing different about this script is the SQL query. In this case we're using an UPDATE query to update records in the Computers table. In addition to the UPDATE keyword, we need to add two parameters that tell the query how to update the records and which records to update:
  • Set Department = 'None'. This simply specifies the new value for the Department field. You can actually set more than one field value at a time by using syntax like this: Set Department = 'None', UserName = 'None'. As you can see, that will set both the Department and UserName fields to None.
  • Where Department = 'Human Resources'. This is a standard Where clause that indicates which records should be updated; in this case, that's all the records that have a Department field equal to Human Resources. We put this in for one simple reason: we don't want to modify all the records in the database, only the records (computers) that formerly belonged to the Human Resources department.
Here's a cool thing you can do with Update queries. Let's say your company has decided to give everyone a 10 percent cost-of-living raise. (The Scripting Guys are obviously like Sir Arthur Conan Doyle in at least one respect: in our writings we both deal with fictitious worlds.) Here's a script that opens a table named Employees and modifies the Salary field for each employee. And what does it set the Salary field to? The query sets this field to the employee's current salary times 1.1; in other words, it gives each employee a 10 percent raise. (If only it were that easy!) Here's the query:
objRecordSet.Open _
  "Update Employees " & _
  "Set Salary = (Salary * 1.1)", _
    objConnection, adOpenStatic, _
    adLockOptimistic
The cool thing? As you can see, you can do calculations in your Update queries. Have you decided to extend the contract for all your temporary employees another 60 days? In that case, a query similar to the following might come in handy:
objRecordSet.Open _
  "Update TempEmployees " & _
  "Set ContractExpirationDate = " & _
  "(ContractExpirationDate + 60)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

Cool Ways to Retrieve Data
Speaking of queries that might come in handy, let's wrap things up by looking at some cool ways to retrieve data from a database. For example, here's a simple yet very useful script. Suppose our Inventory database includes a field named Price, a field that—logically enough—represents the price of the computer. Want to know the five most expensive computers in your organization? The sample script in Figure 4 will tell you that.
Figure 4 Sorting the recordset (Close Figure 4)
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Test.mdb"

objRecordSet.Open "SELECT Top 5 * FROM Computers Order By Price", _
     objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("ComputerName")
    objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close
As you can see, in this SQL query we did two things: we sorted the recordset by Price (Order By Price), and we asked for only the top five (that is, the five most expensive) computers; that's what the SELECT Top 5 does. If we wanted a list of the 10 most expensive computers we'd use this query:
objRecordSet.Open _
  "SELECT Top 10 * FROM Computers " & _
  "Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic
Note. We also added some code that loops through the recordset and displays the name of each computer. We didn't need this code in our previous scripts because those scripts didn't return and display data.
Alternatively, we can get the top 10 percent by using this query:
objRecordSet.Open _
  "SELECT Top 10 PERCENT * " & _
  "FROM Computers Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic
Now, what if we wanted to get the cheapest computers? No problem; we simply use the same approach, except this time we sort the recordset in descending order (that is, from lowest price to highest price). In other words, we use this query, with DESC indicating that the recordset should be sorted in descending order:
objRecordSet.Open _
  "SELECT Top 5 * FROM Computers " & _
  "Order By Price DESC", _
    objConnection, adOpenStatic, _
    adLockOptimistic
See? We told you these were useful.
Here are two more you might find interesting. Let's suppose your database has a field named Budgeted that tracks the amount of money originally budgeted for the computer. Want to compare the actual cost to the budgeted amount? This query returns a list of computers that cost more than the budgeted amount:
objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "Where (Budgeted < Price)", _
    objConnection, adOpenStatic, _
    adLockOptimistic
Meanwhile, this query calculates the average price of all the computers (SELECT AVG(Price) FROM Computers), then returns a list of all the computers that cost less than the average price:
objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "WHERE Price < " & _
  "(SELECT AVG(Price) FROM Computers)", _
    objConnection, adOpenStatic, _
    adLockOptimistic
And yes, your right, we did use a SELECT query in our Where clause. How does that work? Well, that's another story for yet another day. But, as you can see, it opens the door to retrieving all sorts of cool information with your SQL queries.

The Moral of the Story
As we noted at the beginning of this month's column, we weren't sure we even wanted to write another article on database scripting. We're glad we did, however, partly because we think you'll find some of these queries useful, but also because of this: if we didn't write this column now we'd end up having to write it sooner or later. After all, even Sir Arthur Conan Doyle was forced to bring Sherlock Holmes back from the dead. Sir Arthur took the first tentative steps in that direction by penning a new Sherlock Holmes story that allegedly took place before the detective's death. After that, though, he simply gave in to the pressure from his readers, concocting a somewhat dubious tale explaining that Sherlock Holmes had faked his own death. Turns out that everyone, and therefore everything, was perfectly fine.
Interestingly enough, when that latest Sherlock Holmes story appeared, 30,000 new subscribers signed up for The Strand Magazine. Sir Arthur's grateful publishers in both Britain and the U.S. responded by making him the highest-paid writer in the world.
Hmmm, Sir Arthur Conan Doyle brought back a much-beloved character and became the highest-paid writer in the world; now the Scripting Guys have brought back a much-beloved topic—and without having to fake any deaths. Wonder if the good folks at TechNet Magazine have noticed?

Dr. Scripto's Scripting Perplexer
The monthly challenge that tests not only your puzzle-solving skills, but also your scripting skills.

April 2008: Pick a Letter
In this month's puzzle, you need to insert the letters A through O in the blue spaces to reveal the name of a VBScript function. Each letter (A through O) should be used only once, and the letters do not appear in alphabetical order. The inserted letter could be the beginning of the function name, the end of the function name, or anywhere in the middle of a function name. For example, in the following line you would insert the letter D to complete the function name IsDate:
Now you try it; insert the letters A through O to reveal VBScript function names in this grid:

Dr. Scripto's Scripting Perplexer
Answer: Pick a Letter, April 2008

The Microsoft Scripting Guys work for—well, are employed by—Microsoft. When not playing/coaching/watching baseball (and various other activities) they run the TechNet Script Center. Check it out at www.scriptingguys.com.


 Security Watch: Where Is My PII?
Security Watch
Where Is My PII?
Frank Simorjay

We all talk about PII (Personally Identifiable Information) being the most important information to protect. But before you can protect PII, you must thoroughly understand what PII you have collected on your PC. It's easy to say that everything on your computer is sensitive, but what do you really mean by everything?
To shed light on this, I started to look at the problem in a bit more detail, breaking down data types that may be sensitive and figuring out where the data may end up on your computer. First, just how sensitive information may be is often a personal judgment. For instance, some people feel threatened if their name shows up in a search result. Of course, unless you've been living under a rock, there is a good chance that someone has posted your name on the Internet in some form by now. To investigate, use your favorite search engine to search for your name online. Keep in mind that the more common your name, the harder it will be to find instances that refer specifically to you. And you might consider this a good thing.
If you are looking for yourself on the Internet, you might also want to check out some of the popular social networking sites, such as LinkedIn, Facebook, and YouTube. It is quite remarkable to see the Internet's ability to store and disperse private information that used to require diligent search efforts to uncover.
Knowing what information you need to protect is more of a science than it used to be. To help, I thought it would be interesting to see if your computer has potentially private information that you may not be aware of and that you may want to protect. While you might say that all personal information that can be used to steal your identity is sensitive, information can really be separated into two levels of detail. There is information that is readily available and information that is more private and generally considered critical to your personal identity.
Information that is readily available is not typically considered as PII. This includes your name and may also consist of your phone number, street address, e-mail address, gender, and in many cases your place of employment and some educational information. These items are readily available on the Internet and in public directories such as phone books. Disclosure of this information, such as accidentally allowing a spammer to pick up your e-mail address, can be annoying, but alone it would not lead to identity theft.
Sensitive information consists of more private data that provides a link to your identity. Data that you wouldn't want disclosed publicly includes your Social Security number (or other similar unique identifier provided by your government), bank account numbers, credit card numbers (particularly when accompanied by the expiration date and card member ID), your driver's license number, and your fingerprint (or other biometric-related information). When in the wrong hands, these items can be used in very damaging ways. It is important that you control where and how this information is recorded and stored, on the Internet and on your PC. To this end, I will now discuss a couple of simple methods to find any PII that may be stored on your system's hard drive.

Finding PII Data on Your Computer
PII information is scattered everywhere. In fact, if you were to go through your garbage, you would probably find some PII quite easily. Protecting this information requires diligence and a bit of care. I recommend that everyone invest in a good paper shredder and shred anything that has personal information on it.
But what about the PII lurking about on your PC? Finding this data can be as challenging as storing it securely. Windows Vista®, and several other desktop search tools, can help you find information on your system. But you need to know what information to look for.
To illustrate the problem, I'm using a couple of simple tools that will allow me to provide quick hands-on examples of what's at stake. I'm using scripts with Windows PowerShell®. Among the many things Windows PowerShell does, you'll find that it also provides excellent string-matching capabilities. For our purposes, I will be focusing on its ability to match regular expressions. Windows PowerShell (available at microsoft.com/powershell) is a powerful tool that has quickly become a standard for administrative tasks.
Additionally, I will use findstr.exe to provide a means to manage false positives, meaning the ability to ignore files that may contain strings that look interesting (due to the randomness of data strings in binary files) but are in fact of no interest here. In other words, non-text files can be ignored for this exercise.
I have selected two good PII data types: Social Security numbers and credit card information. This data should be easy to find if it is actually stored on your hard drive in clear text. The structure and pattern of both data types are unique enough to allow for a simple script to find the information. However, this data is also sensitive enough that I would ask why it needs to be stored on your PC. If you are inclined to store this information, you should ensure that it is protected. I'll cover ways to protect your PII in a moment. My discussion here is admittedly limited—there are other important PII data types that I haven't included here, such as user names and passwords.

Searching for a Social Security Number
Here is a simple string that will look for any information in files that consists of a standard U.S. Social Security number structured as XXX XX XXXX or XXX-XX-XXXX. Using Windows PowerShell, you can simply enter the following lines:
Get-ChildItem  -rec -exclude *.exe,*.dll |
select-string " [0-9]{3}[-| ][0-9]{2}[-| ]
[0-9]{4}"
Or you can use findstr.exe to ensure that binary files are not read for the search using this:
Get-ChildItem  -rec | ?{ findstr.exe
/mprc:. $_.FullName } | select-string
" [0-9]{3}[-| ][0-9]{2}[-| ][0-9]{4}"
In this sample, Get-ChildItem –rec conducts a recursive directory search of files that starts from the directory in which the command was executed. Findstr.exe searches for strings in files and Select-string is the Windows PowerShell string search function. (Findstr.exe provides similar functionality that I am not discussing here.) In addition, note that the leading space in the regular expression is deliberate. This helps to reduce false positives by eliminating unnecessary information, such as registry strings like HKLM\SOFTWARE\tool\XXX-XX-XXXX.
In my sample run, the search pattern returned a test sample file I put in a subdirectory, and it also found samples located in an XML file that outline file patterns for credit card and Social Security numbers (see Figure 1).
Figure 1 Results when searching for a number pattern
Figure 1 Results when searching for a number pattern
I use the exclude capability in the first example to drop all .exe and .dll files since they can generate unnecessary noise. You may discover other file types that also cause false positives. If you do, you can use exclude to fine-tune the search process.
If you are searching only for a specific Social Security number, you can do the following (replacing "123 45 6789" with your Social Security number):
Get-ChildItem  -rec | ?{ findstr.exe
/mprc:. $_.FullName } | select-string
"123 45 6789","123-45-6789"
The results of this search effort are shown in Figure 2.
Figure 2 Searching for a specific number
Figure 2 Searching for a specific number

Searching for Credit Card Information
Credit card information is a bit trickier since the formats vary. And I want to limit false positives (meaning results that look like a credit card number only by random chance). Nonetheless, the search will probably turn up some random sequences that are merely similar to credit card numbers.
I am using information that is provided in the essay "Anatomy of Credit Card Numbers" by Michael Gilleland as a reference when building these strings (see merriampark.com/anatomycc.htm). For instance, my search string specifies that the first number must be a 4, 5, or 6 since this is defined as the major industry identifier of the credit card.
Here I have constructed simple strings that will search for Discover, MasterCard, and Visa cards. In Windows PowerShell, my search string looks like this:
Get-CchildItem  -rec | ?{ findstr.exe
/mprc:. $_.FullName } | select-string
 "[456][0-9]{15}","[456][0-9]{3}[-| ][0-9]{4}
[-| ][0-9]{4}[-| ][0-9]{4}"
In the sample shown in Figure 3, I used the exclude function to eliminate noise from .rtf, .rbl, and .h file types. Additionally, the sample code looks for credit card strings that have no spaces or dashes. This, unfortunately, may overload your display. So the following is an alternative command for the same function, but this one will not catch non-spaced or non-dashed card numbers:
Figure 3 Using exclude to eliminate noise from the results
Figure 3 Using exclude to eliminate noise from the results
Get-ChildItem  -rec | ?{ findstr.exe
/mprc:. $_.FullName } | select-string
"[456][0-9]{3}[-| ][0-9]{4}[-| ][0-9]{4}
[-| ][0-9]{4}"
Since American Express cards are considerably different, I have created a modified search string to locate that card's pattern. In Windows PowerShell, the search string looks like this:
Get-ChildItem -rec | ?{ findstr.exe
/mprc:. $_.FullName } | select-string
"3[47][0-9]{13}","3[47][0-9]{2}[-| ][0-9]{6}
[-| ][0-9]{5}"
Overload of data may affect this result also. This alternative command is the same function but will not catch non-spaced or non-dashed card numbers:
Get-childitem -rec | ?{ findstr.exe
/mprc:. $_.FullName } | select-string
"3[47][0-9]{2}[-| ][0-9]{6}[-| ][0-9]{5}"
When writing this column, I ran these searches on my own system and I was quite surprised to find several instances of my Social Security number saved in places where it should not have been stored. It turns out the information was located in a note I wrote a while ago and then forgot about. This made me rethink what I should and should not write down!
If you find that you do want to store this information but only in a safe way, try using a tool such as Password Safe (available at passwordsafe.sourceforge.net). Or encrypt your hard drive with a tool such as BitLockerTM Drive Encryption. Finally, the Data Encryption Toolkit for Mobile PCs provides tested guidance on protecting data on a mobile PC. These solutions will at least make it a bit more difficult for someone who happens to be trolling your PC for personal information.

Wrapping Up
Finding PII information is fairly simple. Being aware of the information is the tricky part. But keep in mind that a piece of malware or a malicious user who has gained access to your system can use similar discovery techniques to find information on your system just as easily. Be careful about when and where you enter PII information, and if you are inclined to store the information, be sure that you encrypt it.
I'd like to thank Matt Hainje for helping to troubleshoot my Windows PowerShell scripts.

Frank Simorjay is a Technical Program Manager for the Microsoft Solution Accelerator–Security and Compliance group. He designs security solutions for Microsoft customers, speaks at events such as the Secure World Exposition (of which he is a founder), provides security education and training, and has contributed to a variety of papers and books on security. His most recent work is the "Malware Removal Starter Kit."


 The Desktop Files: Deploying Windows XP with the WAIK
The Desktop Files
Deploying Windows XP with the WAIK
Wes Miller

It's amazing (and daunting) just how different customers can be. In an ideal world, all customers would be in the same phase of deploying the same version of Windows all the time. It sure would make testing easier. But, of course, that isn't the case. While some of you are already in the throes of—or perhaps even done with—
your Windows Vista® deployments or are enthusiastically getting ready to deploy your first Windows Server® 2008 Read-Only Domain Controller (RODC) into production, there are just as many who remind me regularly (thanks) that you are also in the middle of long-planned deployments of Windows® XP and/or Windows Server 2003 R2.
The other day I received an e-mail message from a reader asking, "What about the Windows Automated Installation Kit (WAIK) and Windows XP? How do I use the WAIK when deploying Windows XP?" Well, I'm going to try and answer that.

Another Look at the WAIK
Just over a year ago, I wrote a column discussing the WAIK, the set of powerful tools that was designed to help you deploy Windows Vista (technetmagazine.com/issues/2007/01/DesktopFiles). The WAIK will now address Windows Server 2008 deployment as well, and those two operating systems have a new setup infrastructure—and the tools in the WAIK are largely meant to take advantage of them. And, for better or worse, versions of Windows prior to Windows Vista that use either unattended setup or Sysprep require you to use tools designed specifically for them. But I want to take another look at the WAIK to highlight the tools that you can use to help deploy Windows XP.
Windows PE 2.0 For some common ground, let's start by looking at my February 2008 column (technetmagazine.com/issues/2008/02/DesktopFiles) on dual booting with Windows PE 2.0 and Windows XP. Essentially, if Windows PE 2.0 works for you in that scenario, it will work here. The question to ask yourself is, "Will I be deploying Windows XP on any systems with less than 512MB of RAM or that don't support the Advanced Configuration and Power Interface (ACPI) with Windows Vista?" If the answer to either part is yes, you'll need Windows PE 1.6, and you'll have to verify that you have access to it as part of Software Assurance. Only Windows PE 2.0 and 2.1 are freely available today; versions 1.6 and earlier still require Software Assurance membership.
ImageX/WIM ImageX and the Windows Imaging Format (WIM) were both designed from the ground up to work with any and all versions of Windows beginning with Windows 2000, whether on NTFS or FAT volumes—so yes, absolutely you can use them in order to deploy Windows XP (or Windows Server 2003).
Windows Deployment Services Windows Deployment Services (WDS), which replaces Remote Installation Services (RIS), was originally shipped as an out-of-band (OOB) release in the WAIK 1.0 and was updated and integrated into Windows Server 2003 SP2. Now it's shipping with increased functionality in Windows Server 2008, but it is still very applicable to Windows XP deployment.
If you have a RIS server, or a WDS server running in legacy mode, this will not apply much to you. But if you've begun the transition to WDS running in mixed or native mode, then yes—you should look at WDS as potentially being a part of your Windows XP deployment scenario.
Windows System Image Manager (WSIM) WSIM is really only applicable to Windows Vista and Windows Server 2008 deployment. If you're deploying Windows Server 2003 or earlier, WSIM isn't going to be much help.

Windows XP Deployment Tools
Windows XP (like all versions of Windows from Windows NT® 4.0 through Windows Server 2003) can be deployed via either an unattend.txt file or an "image." For the purposes of this column, I'm going to gloss over unattended setup since it really is now a thing of the past. If you want to take advantage of the WAIK, specifically ImageX, you'll be doing image-based deployment. So instead of an unattend.txt, you need a Sysprep.inf—the answer file format for Sysprep.
When I use the term image here, I'm talking loosely about how to pick up the OS image. Historically, you'd most likely have used Ghost, PQDI, or other imaging tools. Before ImageX, Microsoft didn't provide any way to pick up the OS and applications you had Sysprep'd and copy them to one or more destination computers.
There are two important points to remember when you are building an image of Windows:
  • You can't change the Hardware Abstraction Layer (HAL) except when moving between uniprocessor and multiprocessor systems. As I've mentioned in previously published columns, you can't safely change an image when going between ACPI and non-ACPI architectures.
  • You can change mass-storage controllers. The idea that you can't is a common misconception. But in order to do this, you must use Sysprep to install any potential mass-storage controllers that target computers may need; after deployment, you use Sysprep to remove all but the driver that "stuck" on the target system. I'll get to that momentarily.
With these two issues in mind, you should be able to prep an image on one system and have it work on any target system that utilizes the same or a compatible HAL.

Tools of the Trade
Whenever you're working with Windows XP in an image-based deployment scenario, you will want to keep three items close at hand:
Ref.chm The unattended setup text file reference. Remember that the time to optimally configure optional components in any version of Windows prior to Windows Vista is before imaging. However, if you do need to install optional components after setup, you can do so by running sysocmgr.exe as outlined at support.microsoft.com/ kb/222444. If you're deploying Windows XP Tablet PC Edition, follow the steps at go.microsoft.com/fwlink/?LinkId=108589 to create a single image that installs the Tablet PC components on applicable systems.
Sysprep The Microsoft-supported way to make disk-duplicated systems. I still occasionally see some recommendations for third-party security identifier (SID) changers; as always, I recommend only using Sysprep, as the other tools tend to miss critical Windows SID locations (especially the ones that aren't out in the open).
Setup Manager The quickest and easiest way to create a sysprep.inf file. As always, make sure that you have the correct version handy—it should generally be the same as the version of Windows you are deploying (Windows XP SP2 with Windows XP SP2 deployment tools, for example).
You'll find all three of those items on the Windows XP CD. Updated versions are available at go.microsoft.com/fwlink/?LinkId=107541.
You'll also want to keep tap.exe handy. This utility is included with the Windows XP Embedded tools (go.microsoft.com/fwlink/?LinkId=108590), even in the free evaluation version. Under Windows PE, tap.exe will return information about all of the Plug and Play (PnP) devices Windows PE found; most interesting, it will tell you the HAL Windows PE chose for the device (see Figure 1). This is significant primarily because the logic Windows PE uses to select the HAL is the same logic a full Windows setup would use to determine which HAL to place—so tap.exe under Windows PE is a handy way to see what HAL Windows would recommend for a specific system.
Figure 1 Tap.exe utility can tell you which HAL Windows PE chose for a specific system
Figure 1 Tap.exe utility can tell you which HAL Windows PE chose for a specific system

Creating the Image
You can work through the following steps to get started with your own Windows XP image to be deployed using ImageX (yes, you could use another imaging tool—but you'll soon see why ImageX is the ideal tool for this particular workflow).
The first step is to gather all the necessary tools and components, including Sysprep, Setup Manager, ImageX, and Windows PE (version 2.0 or 1.6, depending on your needs and what you have access to. Remember that if you are using version 2.0 with ImageX, you will need to use bootsect.exe with the /nt52 switch when creating your partition to ensure your boot code is compatible with Windows XP.)
Of course, you'll also need a PC with Windows XP (any SKU) installed on it, as well as the latest updates for Windows and any other installed software. Ideally, this system should never have been joined to the domain before—this reduces the likelihood of domain/network problems later. The system should have only imaging-safe applications installed, nothing that privately stores the machine name, SID, domain, or user-specific information that Sysprep will miss or be unable to replace at SID-changing time. In addition, it should use the HAL you expect to deploy most frequently. On newer hardware, this will generally be the ACPI multiprocessor (MP) HAL, due to the prevalence of both ACPI and multicore (and previously hyperthreading, which also used an MP HAL).
Now configure your Windows XP system as you want it to appear to your end users. Install all the applications you want the majority of users to have (and any that can't be installed unattended). Install or remove any Windows-optional components so the system will be set up as you want for your end users. Then configure the desktop. Log in as the Administrator and make any modifications you want to the profile, including the desktop background, screensaver, Start Menu, and so forth. By default (starting with Windows XP SP2), Sysprep will copy the settings from the Administrator account over to the Default User account for you.
Next, run Setup Manager (see Figure 2), specifying that you want to create a new Sysprep unattend file and completely automate the setup. Note that as you're going through Setup Manager, you will need to enter a product key. If you don't have one handy or you want to script it later (and you don't have a Volume License key), you can specify the key provided in the default unattend.txt file on the Windows XP or Windows Server 2003 CD (which will allow setup to complete but will not allow activation).
Figure 2 Using Setup Manager to create a Sysprep answer file
Figure 2 Using Setup Manager to create a Sysprep answer file
You'll also need to provide a machine name. You may want to automate this later using SQL or some other mechanism, but for now just enter some value and then use scripting to replace the machine name before you run Sysprep, after the machine has had a WIM deployed to it.
Remember that if you provide a password for the Administrator account, it will only be applied if the existing Administrator account in the image does not have one. And note that the domain join section does not allow you to encrypt the domain join credentials. You should use the least privileged account possible to set up the machine account. Finally, I suggest using the Version String option in Setup Manager to track the "version" of the image you have just created.
Now take the Sysprep.inf file, place it in the C:\Sysprep directory with sysprep.exe and setupcl.exe, and add the following to the .inf file:
[Sysprep]
BuildMassStorageSection = Yes

[SysprepMassStorage]
Then run Sysprep –bmsd. This will modify your sysprep.inf and add all of the mass-storage IDs your installation of Windows is aware of, as shown in Figure 3. If you want to add in other devices, you can, or you can add them to your Windows installation and rerun sysprep –bmsd.
Figure 3 Adding mass storage IDs to sysprep.inf
Figure 3 Adding mass storage IDs to sysprep.inf
Next, copy your sysprep.inf file off to a share, then run sysprep.exe –factory and shut down your system. Reboot to Windows PE and connect to a UNC share (recommended) using this:
NET USE Y: \\myserver\myshare
/USER:DOMAIN\USER password
Now capture the image by using the following:
ImageX /capture C: Y:\NewImage.wim
"Factory Mode capture from 4/1/2008"
Then shut down the system.
You now have an image that is ready to be updated via factory mode. I'm not going to drill into the specifics of that here, but, in a nutshell, factory mode is the safest mode to keep your images in until they are ready to be imaged for deployment. For more information, consult the Windows XP deploy.cab documentation mentioned earlier.
When you are ready to prep the images for deployment—that is, once you're ready for a rollout—boot to Windows PE and use Diskpart to create the desired partition(s). Format the partitions using the format command, and use bootsect.exe as needed to apply the pre-Windows Vista boot code (/nt52). Now connect to a UNC share (or cd to wherever your image(s) are) using this:
NET USE Y: \\myserver\myshare
/USER:DOMAIN\USER password
Then apply the image as follows:
ImageX /apply Y:\NewImage.wim C: 1
Finally, reboot to Windows Factory Mode and make any necessary updates to your image (you will need to use a winbom.ini file here; see ref.chm in the deploy.cab for assistance). Winbom.ini should always contain the following lines, which tells it to reseal the image to be ready to run Mini-Setup on the next reboot:
[FACTORY]
ResealMode = Mini
When you're finished, shut down. Repeat the earlier steps you used to capture your image, but now modify the capture command to be:
ImageX /append C: Y:\NewImage.wim "Resealed
and ready for deployment – captured 4/4/2008"
By using /append, you will save considerable space. You've just combined your factory mode and resealed images together, so you can easily switch between them. You can also use /delete to drop any images along the way that you decide not to use. But remember that doing so won't save the space; it will just drop the reference to the specified volume image. You'll need to export all of the volume images you want to keep if you want to clean up the unused space.
Now you can see how the WAIK, although designed and supported primarily for Windows Vista and Windows Server 2008, can help you whether you are deploying the latest versions of Windows or earlier versions. Though you need to use a combination of Windows XP tools and the WAIK tools (primarily ImageX and possibly Windows PE 2.0), Microsoft now provides everything you need to get started rolling out any version of Windows.

Wes Miller is a Senior Technical Product Manager at CoreTrace (www.CoreTrace.com) in Austin, Texas. Previously, he worked at Winternals Software and as a Program Manager at Microsoft. Wes can be reached at technet@getwired.com.


 Field Notes: A MARvellous Initiative
Field Notes
A MARvellous Initiative
Neil Fawcett

Imagine you have 5,000 PCs in your basement that have been refurbished, and you've got customers already lined up to buy these machines. The hardware has a new lease of life, but the operating system ... well that's an entirely different problem. Is it a legal copy? Does each PC even have an OS? Can you resell it?
The new commercial Microsoft Authorized Refurbisher (MAR) program has a solution to all these problems. On the surface, this new scheme is a pretty simple concept: help commercial companies take old PCs, install a legal OS, and sell them.
But the MAR program has deeper goals. From a pragmatic perspective, it's a business opportunity for both Microsoft and commercial PC refurbishers. From a legal perspective, it helps avoid software piracy (both accidental and intentional). Environmentally, it keeps still-viable hardware out of landfills. And finally, it creates a conduit by which companies can better understand how licensing inside Microsoft works, especially when dealing with a PC that has passed from one owner to another.
This program is not only beneficial for Microsoft and PC refurbishers. It offers many significant benefits to customers as well: they get a PC that has been professionally data-wiped and preinstalled with a genuine version of Windows® XP Home Edition or Windows XP Professional; they have access to downloads, updates, and enhanced features; and they also have access to future releases of Windows. And all this is done on a computer that could so easily have become part of a landfill and yet another statistic in the fight against toxic dumping.
MAR is targeted at companies with a capacity to supply an average of 5,000 refurbished PCs per month, and they must demonstrate a history of refurbishing this number of PCs before they can join the program. MAR is a worldwide program, open to major OEM refurbishers as well as to other refurbishers that qualify and are headquartered in the U.S. or Canada. Three participating refurbishers are Redemtech, Techturn, and Apto Solutions.
The licensing of new software products can sometimes confuse both sellers and buyers, but the refurbished PC market brings a new level of complexity to what you can and can't do with an ageing copy of Windows. MAR slices through this complexity for refubishers. It provides a set of installation tools, including the Refurbisher Preinstallation Kit (RPK), which eases the preinstallation of Windows XP to a diverse base of refurbished PCs. Each license comes with a Certificate of Authenticity (COA) from Microsoft. If a PC has been refurbished, still has its COA, and the company has the original recovery media or hard disk-based recovery image, then a new Windows license isn't needed. However, MAR is designed to streamline the process if there is no recovery media or image for commercial refurbishers.
The reuse of computers—through donation, refurbishment, or resale—offers tremendous promise for increasing access to technology, especially for people underserved by technology. It also can help limit the growing environmental footprint of the technology industry. In fact, the market in used PCs—referred to as the "secondary PC market"—is already proving instrumental in the spread of technology in many developing economies.
The marketplace for refurbished PCs is growing and predictions indicate that millions of units can avoid landfills if corporations seek out alternatives. MAR performs not only a strategic role within Microsoft, but critically aids large firms in deploying a low-cost hardware solution that provides great benefit to others. With more and more focus on the environmental issues affecting the planet, the proper disposal of PCs has never been more important. Initiatives like the MAR program will add in revitalizing an ageing PC, helping it continue to benefit anyone looking for low-cost technology and keep it away from a toxic landfill. Simply by aiding in the clarification of software licensing, the program goes a long way towards making a refurbished PC more appealing. You can find more information about the MAR program at microsoft.com/oem/mar.

Neil Fawcett has been writing and editing in the IT sector for numerous publications for over 20 years. He has also spoken at events and published 13 books on IT-related subjects. Prior to his writing career, Neil worked in Silicon Valley as a software developer and hardware engineer.


 Windows Confidential: Share and Share Alike
Windows Confidential
Share and Share Alike
Raymond Chen

Have you ever wondered why it is that when you uninstall a program, Windows® asks whether it should delete a file that "may prevent other applications from running correctly"? First of all, that message (featured in the screenshot) is not coming from Windows—it's coming from the uninstaller. The correct question, therefore, is really, "Why does the application uninstaller display this dialog box?" This is an excellent example of asking a user a question that he can't answer. What led to this sad state of affairs?
To manage reference counts for files used by more than one application (such as the MFC runtime libraries, which are used by multiple unrelated programs), the installer creates an entry under the SharedDLLs registry key. This entry lists the file and its reference count. If there is already an entry for the file, the reference count is incremented. And if the entry doesn't yet exist, the reference count is set to one. Conversely, when the program is uninstalled, the reference count is decremented, and when it drops to 0, the file is deleted because there are no programs that are using it anymore. At least that was the theory.
Should I delete it or not?
Should I delete it or not?
A serious problem with this model is that it assumes everybody agrees to play by the rules. But the temptation to cheat is enormous, since the consequences of cheating typically do not penalize the cheater.
For example, say there is a program that uses the MFC runtime libraries, whose installer just copies these libraries into the system directory without updating the reference count. Then, when this program is uninstalled, it merely deletes the libraries. Now consider what happens when this program attempts to coexist with a program that follows the rules. Here is the basic turn of events:
  1. Install Good Program: the libraries are installed and the reference count is set to 1.
  2. Install Bad Program: the libraries are installed, but the reference count is unchanged.
  3. Uninstall Bad Program: the libraries are deleted.
The result of these events is the Good Program stops working because the libraries are now gone.
Here's another scenario:
  1. Install Good Program: the libraries are installed and the reference count is set to 1.
  2. Install Bad Program: the libraries are installed but the reference count is unchanged.
  3. Uninstall Good Program: the reference count drops to zero and the libraries are deleted.
The result of this scenario is the Bad Program stops working.
The people who wrote the Good Program figured, "Well, we can't stop the Bad Program from hosing us, but at least we can try to stop ourselves from hosing the Bad Program." Therefore, when they're about to delete a file, they stop and display a warning message saying, "Oh boy oh boy oh boy, I'm about to delete a file, OK? If there's a Bad Program out there that's still using it, then that Bad Program will be hosed. If you have a Bad Program, then you should click Don't delete." OK, that's not the exact phrasing, but you know what dialog I'm talking about. Even the legendary Jeffrey Richter recommended this approach in a 1996 Microsoft Systems Journal article.
Unfortunately, by doing this the user is presented with a question he has no chance of answering correctly.
Of course, all those control freaks who demand that Windows give them the option of overriding every last thing should be applauding this dialog box. The uninstaller programs of the world are giving you file-by-file control over the uninstall process. So be careful what you ask for—you just might get it.

Raymond Chen's Web site, The Old New Thing, and identically titled book deal with Windows history and Win32 programming. He wonders whether nuclear fusion will become viable in his lifetime.