 TechNet Magazine March 2007

TechNet Magazine March 2007

SQL Server: New Tools to Diagnose Index Health

To get the best performance out of SQL Server, you need to oversee and maintain healthy indexes. New dynamic management views and functions give you a window into SQL Server to help you better understand how your indexes are functioning and discover potential performance issues.

Randy Dyess SQL Server: Achieve High Availability for SQL Server

SQL Server 2005 offers a number of options to improve database availability, from replication to clustering to log shipping. We’ll take a closer look at the various options for configuring SQL Server and help you find out which ones are right for your environment.

Zach Nichter SQL Server: Simplify Database Maintenance with Table Partitions

In the past, working with partitions and very large tables was a burdensome and tedious task, but with SQL Server 2005, table partitioning has gotten much simpler. Now you can easily create multiple partitions, move those partitions around, drop old partitions, and even change the way data is partitioned. Here’s how.

Noah Gomez SQL Server: Top Tips for SQL Server Clustering

A server cluster provides failover capabilities, ensuring more uptime for your critical operations. But for a cluster to be effective, it must be implemented properly. Here are some key tips that will help you get your cluster up faster and keep it functioning reliably.

Tom Moreau, PhD Windows Administration: Inside the Windows Vista Kernel: Part 2

This month we continue our in-depth discussion about what’s new in the Windows Vista kernel. In this issue, we review some advancements in how Windows Vista manages memory and explore the areas of system startup, shutdown, and power management.

Mark Russinovich From the Editor: Trophy Shelf

Joshua Trupin Toolbox: New Products for IT Pros

Greg Steen Utility Spotlight: SyncToy

Face it, most of the users you support just don't back up there data. SyncToy provides an unintrusive way to ensure your users' files are always backed up and ready to be easily restored.

Jay Munro SQL Q&A: Daylight Saving T ime, Server Memory, and More

Edited by Nancy Michell The Cable Guy: The DHCPv6 Protocol

With an IPv6 network, you don't actually need DHCP to configure addresses, but there can be good reasons to use it. Find out why, and learn how to use DHCPv6 in your environment.

Joseph Davies Windows PowerShell: The Power of Variables

In Windows PowerShell, variables are mapped to underlying classes in the .NET Framework, providing more powerful capabilities than are possible in previous scripting languages. Explore the inner-workings of how these variables work, and learn how to use them.

Don Jones Hey, Scripting Guy!: Answering Your OU Questions…for Just 5 Cents

Ever wish you could display a dialog box that lets select an OU from Active Directory and connect to it using a script? The Scripting Guys have just the script you're looking for.

The Microsoft Scripting Guys Exchange Queue & A: Transaction Logs, Cmdlets, Remote Management, and More

Find out how and why transaction logs have changed in Exchange Server 2007, discover new ways to manage your Exchange servers remotely, get a crash course in using cmdlets to manage Exchange from a command prompt, and more.

KC Lemson and Nino Bilic Security Watch: Deploying EFS: Part 2

Last month, we looked at the back-end portion of an EFS deployment, discussing the elements that a system administrator will deal with. In this issue, we focus on EFS from the end-users' perspective, exploring enhancements to Windows Explorer and the task of choosing file system locations to be encrypted.

John Morello The Desktop Files: PsTools Primer

PsTools provides a gaggle of useful utilities for system administration. Explore all the tools this free suite has to offer and learn how you can use them to do everything from changing a user account password and viewing a user's SID to gathering system information and managing processes.

Wes Miller Inside Microsoft.com: Getting Started with Database Mirroring

Database failure can be detrimental, causing loss of data and loss of productivity. Fortunately, SQL Server 2005 SP1 introduced a database mirroring feature that can prevent such a disaster. Here's what you need to know to create a hot standby database that you can switch to in case your production server ever goes offline.

Saleem Hakani Field Notes: The Doctor Is In

At a party, being an IT pro is sort of like being a doctor. Everybody has a question about a problem they've been having. But sometimes, even the experts overlook the simplest solutions.

Matt Hester Windows Confidential: Looking Smart by Being Stupid

Why did Windows forget your autoplay settings? It didn't. Sometimes the safest action is to take no action.

Raymond Chen


 SQL Server: New Tools to Diagnose Index Health
SQL Server Randy Dyess

New Tools to Diagnose Index Health
At a Glance:
  • Index fragmentation
  • Index usage
  • Index operational activity
Download the code for this article: DyessSQLIndex2007_03.exe (151KB)

One of the top items in any SQL Server performance-tuning checklist is tweaking the indexes in a database. The ability of the SQL Server query optimizer to make proper use of indexes during the execution of a query depends not only on the creation of effective indexes but
also on the health of the indexes. A series of dynamic management views (DMVs) and functions (DMFs), introduced in SQL Server™ 2005, can help database administrators determine the effectiveness of their indexes and discover any performance problems.
DMVs and DMFs allow you to look into a server and return information from the server state that helps you monitor the server instance's health and performance and diagnose problems. Database administrators familiar with previous versions of SQL Server will find that these DMVs and DMFs will replace using DBCC commands, executing certain system stored procedures, querying many system tables, and capturing events with SQL Profiler.
Three key functions and views-namely, sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats, and sys.dm_db_index_operational_stats-are available to help you understand whether your indexes are working as expected. They let you review the I/O and locking patterns of your indexes, and understand whether your indexes are being utilized by the query optimizer in a manner that does not lead to unnecessary contention in the database.

Index Fragmentation
The sys.dm_db_index_physical_stats DMF has been created as a replacement to DBCC SHOWCONTIG and will show an index's fragmentation. However, unlike DBCC SHOWCONTIG, which places a shared lock (S) on the table containing the index, sys.dm_db_index_physical_stats only places an intent shared lock (IS), which greatly reduces blocking of the table during the execution of the function.
To determine the fragmentation of an index when using sys.dm_db_index_physical_stats, you examine a combination of three columns in the output of the function. Logical fragmentation of indexes (extent fragmentation of heaps) can be determined by looking at the value returned in the avg_fragmentation_in_percent column. Logical fragmentation is the percentage of pages that are out of order in the leaf level of an index, while extent fragmentation is the percentage of out-of-order extents in the leaf level of an index. Logical and extent fragmentation can affect the performance of an index by requiring additional I/O and disk head movement because the disk head must skip around to read pages in order. You should strive to keep both logical and extent fragmentation as close to zero as possible.
Internal fragmentation of an index is the percentage of page fullness. Of course, you want the index page as full as possible, but you also need to balance fullness against the number of inserts into index pages in order to keep the number of page splits to the absolute minimum.
The avg_page_space_used_in_percent argument of sys.dm_db_index_physical_stats should be examined to determine index page fullness. To properly configure how close this number is to 100 percent, adjust the fillfactor of an index while watching the number of page splits that are occurring. At some point, the number of page splits will start increasing dramatically, indicating that you have set the fillfactor of the index higher than it should be. Adjusting an index's fillfactor takes time and testing and should not be done without proper planning. (Indexes that do not have random inserts into an index can have their fillfactors set to 100 without worrying about increased page splits.)
To determine the fragmentation levels of all indexes in the AdventureWorks.HumanResources.Employee table, you could use a statement like this:
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL
-- NULL to view all indexes;
-- otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') -- We want all information
Make use of this DMF to automatically determine which indexes need to be rebuilt, which need to be reorganized, and which do not need maintenance. Examining the values of the avg_page_space_used_in_percent and avg_fragmentation_in_percent columns of this DMF for index fragmentation that falls outside of accepted logical and density threshold can help you determine which operation to perform on the index.
Depending on the state of your indexes, the example shown in Figure 1 may not return data in your copy of the AdventureWorks sample database, but it can easily be adapted to other databases.
Figure 1 Examining page fullness and fragmentation (Close Figure 1)
--Reorganize the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 10
AND avg_fragmentation_in_percent < 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 75
AND avg_page_space_used_in_percent > 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes
It would be easy to store the results of the queries into a table variable and then loop through the table variable to build a dynamic string for the correct ALTER INDEX statement (see Figure 2).
Figure 2 Building a dynamic ALTER INDEX string (Close Figure 2)
--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO

--Table to hold results
DECLARE @tablevar TABLE(lngid INT IDENTITY(1,1), objectid INT,
index_id INT)

INSERT INTO @tablevar (objectid, index_id)
SELECT [object_id],index_id
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes

SELECT 'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.'
+ OBJECT_NAME(objectid) + ' REBUILD'
FROM @tablevar tv
INNER JOIN sys.indexes ind
ON tv.objectid = ind.[object_id]
AND tv.index_id = ind.index_id
INNER JOIN sys.objects ob
ON tv.objectid = ob.[object_id]
INNER JOIN sys.schemas sc
ON sc.schema_id = ob.schema_id

Index Usage
While sys.dm_db_index_physical_stats is a powerful replacement for DBCC SHOWCONTIG and can help reveal the health of an index, you will often face the more complex issue of determining which indexes are useful for the queries executed against a table. Often database developers or administrators build indexes on a table that they think will be used by the query optimizer during the execution of a query. In previous releases of SQL Server, knowing if these indexes were actually being used was far more difficult. One either had to drop the index and see if the performance of queries was affected or capture the execution plans of the queries and scan for the usage of the index.
There's a new dynamic management view-sys.dm_db_index_usage_stats-that makes it easy to understand how indexes are being utilized by the query optimizer and the queries executed against a table. This view can be examined to determine the usefulness of an index, allowing you to drop any indexes that are not being used by the query optimizer. No longer do you need to worry that an index is simply wasting storage space or that the maintenance of unused indexes is degrading database performance.
By examining the output of this DMV for indexes with zero seeks and scans, you can determine whether an index has been used since the last time SQL Server was started. Keep in mind, though, that many DMVs and DMFs are not persisted and will reset themselves back to zero once SQL Server is restarted. Take this into account when using a DMV or DMF to determine the usage of an index. The index may simply not have been needed since the last time the service was restarted but is needed by week-end, month-end, or quarterly report queries.
To view all indexes in an instance that have not been used since the last restart of the SQL Server service, you can use the following statement:
SELECT DB_NAME(database_id),OBJECT_NAME([object_id])
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND system_seeks = 0
AND system_scans = 0
AND system_lookups = 0

Index Operational Activity
If you want to understand the operational activity of your indexes, the sys.dm_db_index_operational_stats DMF will be very useful. You can use it to view the I/O, locking, latching, and access method activity on each index in a database, which can help you understand how your indexes are being used and diagnose index locking issues due to extensive I/O activity or the existence of a "hot spot" in the index.
Use the latch wait columns of this DMF to help establish the amount of time that READ and WRITE operations take to acquire access to an index's resources. This can help you determine whether the disk subsystem used to store the index is adequate for the index's I/O activity. It can also indicate whether the design and usage of the index have introduced a hot spot where extensive activity in one or more pages in an index causes contention for the data contained in those pages. Such contention often leads to excessive blocking of operations trying to READ or WRITE to this area.
Figure 3 shows how to determine the locking and I/O patterns for all indexes in the AdventureWorks.HumanResources.Employee table.
Figure 3 Determining locking and I/O patterns (Close Figure 3)
SELECT page_latch_wait_count --page latch counts
,page_latch_wait_in_ms --page latch wait times
,row_lock_wait_in_ms --row lock wait times
,page_lock_wait_in_ms --page lock wait times
,row_lock_count --row lock counts
,page_lock_count --page lock counts
,page_io_latch_wait_count --I/O wait counts
,page_io_latch_wait_in_ms --I/O wait times
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
)

Find Out More
The DMVs and DMFs introduced in this article have many uses besides the ones described here. Take the time to review the SQL Server Books Online articles describing these functions and views pointed to in the "Additional Resources" sidebar in order to understand the wide range of information that can be returned and examined using them.
To learn about some additional index DMFs and DMVs that are not discussed in this article, see the blog posting by the SQL Server query optimization team at blogs.msdn.com/queryoptteam/570176.aspx.
Additional Resources
  • sys.dm_db_index_physical_stats
  • sys.dm_db_index_usage_stats
  • sys.dm_db_index_operational_stats

Randy Dyess is a mentor with Solid Quality Learning where he specializes in SQL Server OLTP systems. Randy is the author of numerous books and articles about SQL Server. He is the founder and principle author of www.TransactSQL.Com and www.Database-Security.Info.
© 2007 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.


 SQL Server: Achieve High Availability for SQL Server
SQL Server Zach Nichter

Achieve High Availability for SQL Server
At a Glance:
  • Mirroring
  • Database snapshots
  • Log shipping
  • Clustering
  • Replication
Download the code for this article: NichterHA2007_03.exe (151KB)

High availability is a concept that every database administrator should understand. It refers to the responsiveness and accessibility of a system. Sometimes high availability means a response time of seconds while other situations require response times in

fractions of a second. I once consulted at a company where Web servers required SQL queries to have round-trip response times in milliseconds; if the response exceeded that limit, the database system was considered down and the Web server would reconnect to the next available database server.
With users demanding faster and faster applications, knowing how to achieve high availability and quick response times will help you plan your data-dependent applications wisely.
Fortunately SQL Server™ 2005 has a number of options to improve availability, including replication, clustering, database mirroring, database snapshots, and database log shipping. I'll take a look at these features and provide some insight into how you can decide which are right for your environment. Let's start with Figure 1, which describes availability options in SQL Server 2005.
Figure 1 High-availibility options (Close Figure 1)

Technology Attributes | Replication | Database Mirroring | Clustering | Database Snapshots | Log Shipping
High-availability option |  |  |  |  |
Tolerate high transaction requirements |  |  |  |  |
Real-time data availability |  |  |  |  |
Data image is read-only |  |  |  |  |
Unique hardware configuration |  |  |  |  |
Low cost |  |  |  |  |
Provides data recovery |  |  |  |  |
Automated failover |  |  |  |  |
Potentially complex implementation/management |  |  |  |  |
Possible performance considerations |  |  |  |  |

Defining High Availability
One of your first objectives when planning a high-availability application is defining what it means in your particular environment. To some organizations, high availability means that there must be redundant hardware equal to the production hardware, requiring both the data and the hardware to have an up-time and availability of 99.995 percent or higher. Other organizations might require only the data itself to be highly available, with less concern for production-level performance should a failover be necessary. Defining high availability is important in determining the right solution for your situation.
You also need to identify the types of outages you may experience and indicate how they affect your Service Level Agreements (SLA). Outages that can affect your availability include planned, unplanned, and degraded performance.
A planned outage is usually a scheduled maintenance window about which the users of the systems are informed in advance. An unplanned outage is generally the result of a hardware or software failure that causes your data to become inaccessible. Performance degradation can also cause outages and is often measured in end-user response time, which is typically agreed upon in advance by the business as well as the IT organization in some form of SLA.
In addition to identifying potential sources of outages, you must also determine the activity level of your data and whether it must always be online or can be near-line or off-line at times. You must also decide if the availability option is going to be in the same geographic location or a remote location. Budget limitations will likely play a role in your decision as well. Now let's look at each availability option.

Database Mirroring
Before I dig into database mirroring, I should define the terminology.
The principal is the primary production server housing the database that is sending its transaction logs continuously to the mirror server and database.
The mirror is the secondary server that houses the backup copy of the database. The mirror copy is consistently synchronized with the principal database.
The role indicates the purpose of the particular server-whether it serves as the principal or mirror.
The witness is the instance that monitors the principal and mirror servers as they perform their duties and can initiate an automatic failover.
A partner can be either the principal server or the mirror server.
In a typical environment, the principal database will be backed up on the principal instance and the backup will be restored on the mirror instance (see Figure 2). Once the database is restored, mirroring must be set up on the principal server either using the properties window of the principal database in the SQL Server Management Studio (SMSS) or by using T-SQL scripts.
Figure 2 Database mirroring architecture
Figure 2 Database mirroring architecture
Once mirroring is configured and the mirroring session has been established, the principal and mirrored databases will synchronize. The principal database will then send its transaction log of events that occurred since the time the last backup was applied on the mirror. The mirror will receive the log and attempt to apply it as quickly as possible. If you're using SQL Server 2005 Enterprise Edition, this process is multithreaded; otherwise it is a single-threaded operation. As soon as these logs are applied to the mirror, the databases are considered synchronized and will remain synchronized until the mirrored session is broken.
As clients execute new transactions, the principal server executes the transactions against the principal database and while doing so ships the transaction log records from the principal database to the mirrored database's redo log, or log queues, where it is picked up and applied to the mirrored database. Once the transaction is applied and committed on the mirror database, a reply is sent to the principal announcing that the transaction has been committed on the mirror. The principal will not confirm any new transactions that might come into the system until the acknowledgment is received from the mirror.
In the case of a failure, the mirror can initiate an auto-failure and the witness supports the process by determining whether the principal database is available. Once a failure occurs, the problem on what is now the mirror partner must be resolved before it can again become the principal partner. After the issues are resolved on the mirror partner, the move back to the mirror partner server is initiated and the databases synchronize again. Once they are in sync, the mirroring session can begin again.
This particular mirroring mode is a high-safety mode that provides synchronous transactional operations by turning on transactional safety, but it does not require a witness because it does not take advantage of automated failover; all failovers are manually initiated. There is another type of mirroring mode that provides synchronous transactional operations-the high-availability mode. It requires not only that transactional safety be turned on, but also that a witness be used for automated failover in the case of a failure.
The third and final mode available in mirroring is high-performance. It requires that transactional safety be turned off allowing for asynchronous operational support which, in turn, allows the transactions on the principal partner to commit without having to wait for the transaction record to be written to the mirror. High-performance mode does not require a witness in the configuration.
Note that mirroring requires the same edition of SQL Server on principal and mirror but not on the witness, which can be SQL Server Express Edition. In addition, it is important that the principal database be in full recovery mode.
ADO.NET 2.0 is integrated with SQL Server 2005 and includes the ability to support database mirroring and provide a transparent failover for the application to the mirrored environment. This gives your ADO.NET application a way to automatically fail over without any additional coding or configuration if a connection to the principal database cannot be made. The configuration is as easy as specifying a common user between the two environments and the failover partner in the connection string. The following is an example of an ADO.NET connection string identifying the failover partner for the mirrored database environment:
"Provider=SQLNCLI.1;Data Source=MirrorDB;Failover Partner=SQL03;
 Initial Catalog=AdventureWorks;
Persist Security Info=True;User ID=TestUser; Password=TestPswd; Pooling=True;
Connect Timeout=5;Application Name=ADOMirrorTest"
Database mirroring might be a good option for your company depending on your application and data requirements, but there are a number of things you need to consider for optimal performance. For instance, how high is the transaction rate of the system or the volume of data change? When you consider database mirroring, it is critical to determine whether bandwidth and network speed are sufficient to handle the volume of data and the transaction processing rate. Also, consider the saturation of the link. This is especially important if the mirror is in a different geographic location. Monitoring your system beforehand is crucial in determining if there are any environmental limitations that would prevent mirroring from operating efficiently.
Database mirroring can be a particularly good option if you are trying to keep costs down. In fact, the database mirroring architecture requires no shared disks and no advanced or specialized skill to run the environment. And unlike clustering, database mirroring does not require that both of the partners have the same hardware. Furthermore, it's simple to implement mirroring using the setup wizard found on the mirroring tab of the database properties window (see Figure 3). I also recommend reading the whitepaper "Database Mirroring Best Practices and Performance Considerations" at go.microsoft.com/fwlink/?LinkId=80897 for more helpful information.
Figure 3 Mirroring setup wizard
Figure 3 Mirroring setup wizard

Database Snapshots
Database snapshots are a new technology offered in SQL Server 2005 Enterprise Edition, but are not considered a high-availability option. Database snapshots should be used as a recovery or a viable reporting option when used with other technologies. A snapshot is simply a read-only view of a database at a particular point in time.
A snapshot is created by using a CREATE DATABASE command like so:
CREATE DATABASE SnapDB_20061028_2030 ON
(NAME = SnapDB_Data, FILENAME =
    'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SnapDB_20061028_2030.snp')
AS SNAPSHOT OF SnapDB;
GO
When the database snapshot is created, it uses one or multiple files called sparse files rather than data files as a typical database would. These sparse files are essentially virtual holding places that initially consume no data. They are used to hold data only if the data it is changed or deleted in the source database. Data is written to the sparse files one data page at a time and the actual database snapshot only displays data that has been changed since the snapshot was taken. The rest of the data comes from the source database's data pages.
The database snapshot files are allocated at the size of the database when the snapshot was taken. The allocated size does not tell you how much data they actually hold. To get that information, run a T-SQL statement like this one:
SELECT *
FROM fn_virtualfilestats(DB_ID(N'SnapDB_20061028_
    2030'), 1);
GO

Because of the way data is stored in sparse files and the source database, when a database snapshot is accessed, the data pages in the original database data files and data pages from the sparse files of the snapshot are retrieved. Snapshots can only live on the server with the source database that the snapshot was taken from because of the need to share data pages. Because this architecture does not alleviate I/O on the source database, snapshots are not a valid reporting option to pursue as they will not represent the true state of the database.
Consider a scenario where database mirroring is used along with snapshots. This allows for the reporting data, the mirror, and snapshot databases to be separated physically from the principal database. The database snapshot is scheduled using the SQL Server agent and a custom script to provide refreshed snapshots at regular intervals. The example script in Figure 4 shows the stored procedure used to accomplish this. It is designed to be used inside of a job to manage creating and dropping snapshots for a particular database in your environment. This would allow for an acceptable reporting solution because the reporting data would be isolated from production data.
Figure 4 Stored procedure for scheduling snapshots (Close Figure 4)
use msdb;
GO
set nocount on
GO

CREATE PROCEDURE usp_snaprefresh
     @database    sysname = NULL    --name of the database to snapshot
    ,@keepsnap    int        = 24   --# of hours to keep a snapshot
                                    --after it was created
                                    --use a value of '0' to keep all
                                    --existing snapshots
    ,@fileloc    sysname            --location for snapshot
        = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'
AS

DECLARE
     @dt            datetime        ,@cnt          int
    ,@databaseid    int             ,@snap         sysname
    ,@sql           nvarchar(1000)  ,@yy           varchar(4)
    ,@mm            varchar(2)      ,@dd           varchar(2)
    ,@h             varchar(2)      ,@m            varchar(2)
    ,@lname         sysname         ,@pname        sysname
    ,@file          sysname         ,@pos1         int
    ,@pos2          int

-- initialize variables
SELECT
     @dt = getdate()
    ,@cnt = 0, @pos1 = 0, @pos2 = 0
    ,@databaseid = db_id(@database)

-- check if valid database was provided
IF @databaseid IS NULL
BEGIN
    RAISERROR ('Missing database name. Rerun the procedure specifying a
       valid database name.',16,1)
    RETURN (0)
END

--determine if snapshots should be kept
IF @keepsnap <> 0
BEGIN
    -- determine if other snapshots exist for this server older than @
    -- keepsnap value hrs
    SELECT ROW_NUMBER() OVER(ORDER BY name DESC) AS [RowNum],
        name INTO #t1
    FROM master.sys.databases
    WHERE source_database_id = @databaseid
        AND create_date < dateadd(hh,-(@keepsnap),getdate())

    IF (SELECT max(RowNum) FROM #t1 where name is not null) > 0
    BEGIN
        WHILE @cnt <= (SELECT max(RowNum) FROM #t1)
        BEGIN
            SELECT @snap = name FROM #t1 WHERE RowNum = @cnt

            PRINT 'Dropping snapshot ''' + @snap + ''''

            SET @sql = 'DROP DATABASE ' + @snap

            EXEC(@sql)
            SELECT @cnt = @cnt + 1
        END
    END
END

-- break apart point in time date time information for file name
SELECT @yy = convert(varchar(4),year(@dt)),@mm = convert(varchar(2),
   month(@dt))
    ,@dd = convert(varchar(2), day(@dt)),@h = convert(varchar(2),
         datepart(hh,@dt))
    ,@m = convert(varchar(2), datepart(mi,@dt))

-- piece together the database snapshot name and the file name
SELECT @file = @database + '_' + @yy + @mm + @dd + '_' + @h + @m

-- identify logical file name of primary data file
SET @sql = 'SELECT name INTO tempdb..t1
FROM ' + @database + '.sys.database_files
WHERE file_id = 1'

EXEC(@sql)

--setting logical filename for the snap
SELECT @lname = name FROM tempdb..t1

-- making sure the file location ends with '\'
IF substring(@fileloc, len(@fileloc), 1) <> '\'
BEGIN
    SET @fileloc = @fileloc + '\'
END

-- build sql statement to be run
SET @sql = N'CREATE DATABASE ' + @file + ' ON
(NAME = ' + @lname + ', FILENAME = ''' + @fileloc + @file + '.snp'')
AS SNAPSHOT OF ' + @database

EXEC(@sql)

-- cleanup
DROP TABLE tempdb..t1;
GO
Keep in mind, though, that database snapshots are considered temporary because snapshots cannot be backed up and cannot exist without their source database. If a sparse file runs out of space, the snapshot will be considered corrupt and must be dropped.
In addition, with database snapshots system performance can be degraded during data modification operations on the source database because the data pages are being written to the sparse file for each snapshot from the source database data files. This in turn multiplies the number of writes by the number of snapshots a database has.
Read permissions are defined by the source database at the point of the snapshot and cannot be changed. The snapshot must reside in the same instance as the source database because they share data pages as well as the same buffer cache.
You should consider using snapshots as a reporting solution only if you are also using mirroring; otherwise there is no performance gain for your environment. Database snapshots are possibly the fastest way to preserve data before a questionable operation is run on a system. A database can be reverted back to the state of the snapshot or the data can be pulled out of the snapshot to replace data in the source database.
You'll need to determine a naming standard for the snapshot databases. The standard that I use is originaldatabasename_date_time.snp. It specifies the source database first, then the day and the time (in 24-hour format) that the snapshot was taken.

Log Shipping
Log shipping is a limited high-availability option that utilizes backup and recovery to establish a very inexpensive solution. Log shipping takes advantage of transaction log backups on a scheduled interval to keep a secondary database up to date.
Log shipping in SQL Server 2005 uses wizards to walk through the process of setup, scheduling, initialization, and monitoring (see Figure 5). The process is simple and can be completed in just minutes.
Figure 5 Log shipping setup wizard
Figure 5 Log shipping setup wizard

After the primary database is identified, a schedule is created and the file age determined for backup files. Next, the file shares for the backup files must be established. After the file shares are set up, the secondary database file location needs to be established and the database must be initialized by completing a restore of the primary database. Finally the schedules must be set for the backup file copies and the restores of the transaction log backups along with any alerts or delays needed for each step.
Once setup is complete, log shipping backs up the transaction log of a database on a scheduled basis to a shared network location. After the files are sent to the share, the backup is applied to the secondary database on a set schedule.
Log shipping works well in many situations because of its simplicity. It is a good option for high availability that is inexpensive and can handle a high transaction system. The secondary database that is employed in log shipping can be used in read-only mode, which makes it handy for a reporting database. Log shipping requires minimal overhead but does necessitate an alert policy in case of handling failures.
A couple of things to consider if you're thinking about using log shipping in your environment are its simple setup and management and the fact that it requires little to no specialized skill. Log shipping is not a real-time high availability solution, however, though it can be combined with database mirroring for this purpose. It is limited by schedules as well as the operations such as backups, file copies, and restores. It also requires manual failover. For these reasons, log shipping provides a simple solution for the environment that is not quite as sensitive to time requirements.

SQL Server Clustering
Server clustering works at the OS level and involves duplicate hardware as well as shared disk resources for the cluster to access. Clustering is perhaps the least intrusive to end users, but it is also likely to be the most costly. It requires at least twice the amount of hardware that's needed to run a non-clustered instance.
The topic of clustering is quite involved, so rather than exploring all the details here, I'll give you a general overview. Clustering requires two or more servers, all of which must be installed with the same version of Windows® 2000 Advanced or Datacenter editions, or Windows Server® 2003 Enterprise or Datacenter editions. It also requires the installation of Microsoft® Cluster Services (MSCS) which handles ownership of shared resources between servers and manages IP addresses, shared disks, and network names. Clustering also requires a shared disk resource, usually in the form of a Storage Area Network (SAN) or SCSI attached storage.
A SQL Server instance is also considered a resource and both Standard and Enterprise editions of SQL Server 2005 can be installed in a cluster configuration. See the "Feature Comparison Chart for SQL Server 2005" (microsoft.com/sql/prodinfo/features/compare-features.mspx)for a list of features supported by both editions of SQL Server 2005.
Once the resources are established on the cluster, the secondary node of the cluster keeps in regular communication with the primary node of the cluster through a heartbeat established on a private network between the two nodes of the cluster. The heartbeat is an interval checkpoint that is taken to determine if the primary has failed.
In the case of a failure of the primary, the resources are moved to the secondary node while keeping the state of the logical server in place. This allows the clients to continue to work with only a pause in interaction. The entire failover process can take anywhere from 5 seconds or less to 30 seconds or more in some cases depending on the hardware, software, and networking components involved in the cluster.
Clustering can be an expensive, complex technology requiring specialized skill to resolve failures on the system; however, it provides the smoothest failover for end users of any of the automated failover options. Each application is different and some may not be cluster-aware or compatible, which in a worst-case scenario requires the application to reconnect.

Replication
SQL Server 2005 replication can also be used in high-availability architectures. It offers four types of replication: snapshot, transactional, peer-to-peer, and merge. Peer-to-peer is actually just a form of transactional replication so I won't discuss it here.
With replication you get the option of having a secondary site and database for high availability that is every bit as functional as the primary database. This can be achieved using merge replication, which takes transactions from both the primary and the secondary databases and merges the changes from both into one another. As you can guess, a conflict resolution procedure is needed in this configuration.
Transactional replication is very similar in logical design to database mirroring. Transactions that are applied to the primary database are shipped to the secondary database to ensure that the environment remains consistent. Once the transactions arrive they are applied to the secondary database, which then waits for the next transaction to be applied on the system.
Snapshot replication is very similar to log shipping in that they both run in scheduled intervals and update the secondary database in mass change rather than applying each transaction to both systems as they are committed. Both technologies are utilized in much the same manner.
Replication requires specialized knowledge, which is a big concern for environments that don't have a dedicated DBA. Replication can be somewhat complex to troubleshoot and it does require a more involved design if it's to be used as a high-availability option.
Replication can appropriately fill the requirements for a high-availability solution. This technology does what database mirroring can do at a record level using transactional replication but without the option for an automated failover. Assuming you have sufficient resources and a bit of creativity, scripting a solution for an automated failover should not be out of reach.
Unlike database mirroring, both the source and the target databases are fully accessible to client applications. Replication provides the same functionality as log shipping with the use of snapshot replication.
Something to consider, however, is that the replication technology is battle-proven and well documented. Using replication for a high-availability solution has some drawbacks and performance can be a concern, but only as much as database mirroring is. Any high-availability solution you design with replication will most likely have a more complicated architecture to manage; not more advanced necessarily, but definitely more involved. In addition, one of the largest hurdles to consider is that if your database table structure changes or if you want to add a table to be replicated, you must break and redefine the publication for the changes to end up in both databases.

Wrapping It All Up
You can see now how creating a high availability solution for your environment requires a touch of creativity. Each SQL Server 2005 high-availability technology has its strengths and weaknesses and each lends itself to different situations.
Log shipping, snapshot replication and even database mirroring in high-performance mode would be good when geographically separating a primary Web database from the secondary database environments (particularly if they don't require that the secondary data is available in real-time.)
If, on the other hand, there is a secondary database requirement of real-time data, then transactional replication or database mirroring can carry the load if the transaction rates on the primary server are low and the link between the two environment sites is fast and not saturated.
Also consider your comfort level with these technologies. If you have working experience in some of the technologies already, you'll probably be fine. If you are a manager with no dedicated DBA, try to steer clear of the more complex technologies like replication where there are a lot of moving parts and troubleshooting can be complex. You might also consider contracting an experienced SQL Server consultant to help you design, implement and possibly train your staff to manage a new high availability suitable for your environment.
If high-availability in your organization requires only that the data is available a high percentage of the time and data downtime is taken very seriously, then clustering might be the option for you.
The point of all this is that SQL Server 2005 has provided a number of new options for implementing high availability that are tailored to serve different types of environments. A single availability option might fit your needs, or you may choose to take advantage of a combination of technologies, but as you now know, there are a variety of choices that are available to you.

Zach Nichter is a SQL Server professional with over 10 years of experience. He has held a number SQL Server support roles including DBA, team lead, manager, and consultant. Currently Zach is employed by Levi Strauss & Co. as the DBA Architect, focusing on SQL Server performance, monitoring, architecture, and other strategic initiatives.


 SQL Server: Simplify Database Maintenance with Table Partitions
SQL Server Noah Gomez

Simplify Database Maintenance with Table Partitions
At a Glance:
  • Creating partitioned tables
  • Adding and merging partitions
  • Creating and managing partitioned indexes
Download the code for this article: GomezPartitioning2007_03.exe (156KB)

In the past, database administrators managing tables containing millions of rows of data were forced to create multiple tables. Once those tables were partitioned, the administrator had to tie
the tables back together during the execution of many of their queries. Tying partitions together involved creating a partitioned view or a wrapper stored procedure that figured out where the data lived and executed another stored procedure to hit only the partitions needed to return the dataset.
While these methods worked, they were burdensome. The administration of multiple tables and their indexes, as well as the methods used for tying the tables back together often caused administration and maintenance issues. In addition, creating multiple tables to partition data led to a degree of inflexibility, as the stored procedures, maintenance jobs, Data Transformation Services (DTS) jobs, application code, and other processes had to understand the nature of the partitioning. So to allow you to add or drop these quasi partitions without changing your code, these elements were typically created in a non-dynamic manner, and as a result they were inefficient.
The Enterprise and Developer editions of SQL Server™ 2005 let you partition large amounts of data contained in a single table into multiple smaller partitions that can be managed and maintained more effectively. The ability to create data segments that are accessed through a single point-of-entry reduces many of the administration issues that came with the old way of doing things. Using a single point of entry (table name or index name) hides the multiple data segments from the application code and allows the administrator or developer to change the partitions as necessary without having to adjust the code base.
In short, you can create multiple partitions, move those partitions around, drop old partitions, and even change the way data is partitioned without ever having to adjust the code in your application. Your application code simply continues to call the same base table or index name. Meanwhile, you can reduce the amount of data that individual indexes contain, in turn decreasing maintenance times for those indexes, and you can increase data load speed by loading into empty partitions.
Technically, every SQL Server 2005 table is partitioned-every table has at least one partition. What SQL Server 2005 does is allow database administrators to create additional partitions on each table. Table and index partitions are hard-defined, row-level partitions (partitioning by columns is not allowed) that allow a single point of entry (table name or index name) without the application code needing to know the number of partitions behind the point of entry. Partitions can exist on the base table as well as the indexes associated with the table.

Creating Partitioned Tables
You use partition functions and partition schemes to create a table that has the ability to grow beyond the default single partition. These objects are what allow you to divide the data into specific segments and control where those data segments are located in your storage design. You can, for example, spread data out over multiple drive arrays based on the age of the data or using other common differentiators. Note that a table can be partitioned based on one column of the table and each partition must contain data that cannot be placed into the other partitions.
Partition Functions When partitioning a table, the first decision is how you want to divide the data into different segments. A partition function is used to map the individual rows of data into the different partitions. Those individual rows of data can be mapped by any type of column except for text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or common language runtime (CLR) user-defined data types. However, the partitioning function must be able to place a row of data into only one table partition-it cannot allow a row of data to belong in multiple partitions at the same time.
In order to partition a table, you must create the partitioning column in the targeted table. This partitioning column can exist in the table schema when the table is first created or you can alter the table and add the column at a later date. The column can accept NULL values but all rows containing NULL values will by default be placed in the left-most partition of the table. You can avoid this by specifying that NULL values be placed in the right-most partition of the table when you create the partitioning function. The use of the left or right partition will be an important design decision as you modify your partitioning schema and add more partitions or delete existing partitions.
When creating a partitioning function, you can choose a LEFT or RIGHT partition function. The difference between LEFT and RIGHT partitions is where the boundary values will be placed in the partitioning scheme. LEFT partitions (which is the default) include the boundary value in the partition while RIGHT partitions place the boundary value in the next partition.
To understand this concept a little better, let's look at simple LEFT and RIGHT partitions:
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)

CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT
FOR VALUES (1,10,100)
In the first function (Left_Partition), the values 1, 10, and 100 are placed in the first, second, and third partitions, respectively. In the second function (Right_Partition), the values are placed in the second, third, and fourth partitions.
When creating a partitioned table, it's important to get the partitions as even as possible. This will help you to understand the space needed for a partition. The use of LEFT and RIGHT will determine where data is placed and this, in turn, will determine the size of the partition and the size of any indexes created on that partition.
You can determine the partition number in which a data value will be placed by using the $PARTITION function, as shown here:
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)
In the first SELECT statement, the result will be 2. The second SELECT statement will return 3.
Partition Schemes After you create the function and decide how your data will be divided, you must decide where the individual partitions will be created on your disk subsystem. You use partition schemes to create this disk layout. Partition schemes manage the disk storage of individual partitions by utilizing filegroups to place each partition onto a location on the disk subsystem. You can configure partition schemes to have all partitions placed in a single filegroup, all partitions placed into different filegroups, or multiple partitions share filegroups. This latter method allows the database administrator a large amount of flexibility in spreading out the disk I/O.
Figure 1 shows some of the ways in which you can assign a filegroup or multiple filegroups to a partition scheme. You should be aware that the filegroups used by your partition scheme must already exist in the database before you create your partitioning scheme.
Figure 1 Assigning filegroups to a partition scheme (Close Figure 1)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
      ALL TO ([PRIMARY])

--Place all partitions into the different filegroups
CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
      TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)
--Filegroups must currently exist in database

--Place multiple partitions into the different filegroups
CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
      TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)
--Filegroups must currently exist in database
If you create the sample partition functions as shown in Figure 1 and utilize the partition scheme to create a table, you can then determine where individual data rows are placed in your newly partitioned tables. Then you can view the distribution of those rows of data after they are inserted into your partitioned table. The code for doing all this will look something like that shown in Figure 2.
Figure 2 Placing rows of data and viewing the distribution (Close Figure 2)
--Prepare database
IF OBJECT_ID('Partitioned_Table') IS NOT NULL
DROP TABLE Partitioned_Table
GO

IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO

IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO

--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)

--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
      ALL TO ([PRIMARY])

CREATE TABLE Partitioned_Table
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)

--Determine where values will be placed (this is not required)
--You should try to do this before executing the code
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (2)
SELECT $PARTITION.Left_Partition (3)
SELECT $PARTITION.Left_Partition (4)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (11)
SELECT $PARTITION.Left_Partition (12)
SELECT $PARTITION.Left_Partition (13)
SELECT $PARTITION.Left_Partition (14)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
SELECT $PARTITION.Left_Partition (102)
SELECT $PARTITION.Left_Partition (103)
SELECT $PARTITION.Left_Partition (104)

--Insert data into partitioned table
INSERT INTO Partitioned_Table VALUES (1,'Description')
INSERT INTO Partitioned_Table VALUES (2,'Description')
INSERT INTO Partitioned_Table VALUES (3,'Description')
INSERT INTO Partitioned_Table VALUES (4,'Description')
INSERT INTO Partitioned_Table VALUES (10,'Description')
INSERT INTO Partitioned_Table VALUES (11,'Description')
INSERT INTO Partitioned_Table VALUES (12,'Description')
INSERT INTO Partitioned_Table VALUES (13,'Description')
INSERT INTO Partitioned_Table VALUES (14,'Description')
INSERT INTO Partitioned_Table VALUES (100,'Description')
INSERT INTO Partitioned_Table VALUES (101,'Description')
INSERT INTO Partitioned_Table VALUES (102,'Description')
INSERT INTO Partitioned_Table VALUES (103,'Description')
INSERT INTO Partitioned_Table VALUES (104,'Description')

--View the distribution of data in the partitions
SELECT ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('Partitioned_Table')

Modifying Partitioned Tables
Despite careful upfront planning, sometimes you'll need to adjust your partitioned tables after they've been created and populated. Your partition scheme may work as intended, but you may, for example, need to add new partitions as new data is accumulated, or perhaps you may need to drop large amounts of partitioned data at one time. Fortunately, partitioned tables and the underlying partitioning structures allow for changes to be made after the table has gone live and been filled with data.
Add Partitions Many partitioning plans include the ability to add a new partition at a future time. This point in time can be a particular date or it can be dependent on a value in an incremental identity column. However, if you haven't planned for this up front, you can still come in at a later date and add new partitions to a partitioned table. Consider the table created in Figure 2. You can add a new partition to this table to contain values greater than 500, like this:
--Determine where values live before new partition
SELECT $PARTITION.Left_Partition (501)  --should return a value of 4

--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(500)

--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (501)  --should return a value of 5
The ability to add partitions offers great flexibility. Figure 3 shows how you can add a partition to the left side of the function. In this case, you need to tell the partitioning scheme where to put the new partition since you have used up all of your filegroups created when you first built the partitioning scheme. Even though you are using the PRIMARY filegroup for all your partitions, you must still tell the partitioning scheme to reuse the PRIMARY filegroup for the new partition.
Figure 3 Adding a partition to the left side of the function (Close Figure 3)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5)   --should return a value of 2
SELECT $PARTITION.Left_Partition (1)   --should return a value of 1
SELECT $PARTITION.Left_Partition (10)  --should return a value of 2

--Add new filegroups to the partitioning scheme
ALTER PARTITION SCHEME Primary_Left_Scheme
NEXT USED [PRIMARY]

--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(5)

--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5)   --should return a value of 2
SELECT $PARTITION.Left_Partition (1)   --should return a value of 1
SELECT $PARTITION.Left_Partition (10)  --should return a value of 3
Merge Two Partitions SQL Server allows you to drop single partitions from a table while keeping the data. This can be used to merge older active data into archived data, or to reduce the number of partitions you have, easing your administration of the partitioned table. You can also use this to merge partitions, moving data from one filegroup to another to free up disk space on certain drive arrays. The code in Figure 4 shows how you can move data from one partition into another partition on the same filegroup.
Figure 4 Moving data from one partition into another (Close Figure 4)
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO

IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO

IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO

--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)

--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
      ALL TO ([PRIMARY])

CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)

INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')

--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)

--Merge two partitions
ALTER PARTITION FUNCTION Left_Partition()
MERGE RANGE (10)

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')

--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
Move a Single Partition Table into a Partitioned Table During load routines, large amounts of data often must be loaded into the database and then modified or aggregated before it is moved into the actual data table. SQL Server 2005 partitioning lets you move a single partition table into a table with multiple partitions. This means you can load data into a single load table, modify that data, and then move the entire table into an existing table without the overhead of moving each individual row of data. This layer of partitioning does not involve altering the underlying partitioning structures. It involves modifying the partitioned table. The code in Figure 5 shows how you can achieve this.
Figure 5 Moving an entire table into an existing table (Close Figure 5)
--Prepare database
IF OBJECT_ID(‘multiple_partition’) IS NOT NULL
DROP TABLE multiple_partition
GO

IF OBJECT_ID(‘single_partition’) IS NOT NULL
DROP TABLE single_partition
GO

IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = ‘Primary_Left_Scheme’)
DROP PARTITION SCHEME Primary_Left_Scheme
GO

IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = ‘Left_Partition’)
DROP PARTITION FUNCTION Left_Partition
GO

--Create single partition table
CREATE TABLE single_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)

--Table must have a CHECK Constraint
ALTER TABLE single_partition
WITH CHECK
ADD CONSTRAINT CK_single_partition
    CHECK (col1 > 100)

INSERT INTO single_partition VALUES (101,’Description’)
INSERT INTO single_partition VALUES (102,’Description’)
INSERT INTO single_partition VALUES (103,’Description’)
INSERT INTO single_partition VALUES (104,’Description’)

--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)

--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
      ALL TO ([PRIMARY])

CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)

INSERT INTO multiple_partition VALUES (1,’Description’)
INSERT INTO multiple_partition VALUES (2,’Description’)
INSERT INTO multiple_partition VALUES (3,’Description’)
INSERT INTO multiple_partition VALUES (4,’Description’)
INSERT INTO multiple_partition VALUES (10,’Description’)
INSERT INTO multiple_partition VALUES (11,’Description’)
INSERT INTO multiple_partition VALUES (12,’Description’)
INSERT INTO multiple_partition VALUES (13,’Description’)
INSERT INTO multiple_partition VALUES (14,’Description’)
INSERT INTO multiple_partition VALUES (100,’Description’)

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_
      ID(‘single_partition’))

--Move the single table into the partitioned table
ALTER TABLE single_partition SWITCH TO multiple_partition PARTITION 4

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_ID(‘single_partition’))
Move a Partition from One Table to Another A common administrative task is to move older data into separate archive tables. The archiving process usually involves a series of statements that can create additional resource usage in your transaction logs. Switching the ownership of a partition from one table to another, however, is a simple way to archive large amounts of data without the transaction log overhead. This feature allows the database administrator to move segments of older data from their active tables to archived tables. But since the data is not actually moved, the amount of time it takes can be dramatically less than when moving individual rows of data. Figure 6 shows how you can do this.
Figure 6 Moving older data to archived tables (Close Figure 6)
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO

IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO

IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO

IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO

IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Archive_Scheme')
DROP PARTITION SCHEME Archive_Scheme
GO

IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Archive_Partition')
DROP PARTITION FUNCTION Archive_Partition
GO

--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)

--Create archive function
CREATE PARTITION FUNCTION Archive_Partition (int) AS RANGE LEFT
FOR VALUES (100,200,300)

--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
      ALL TO ([PRIMARY])

--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Archive_Scheme
AS PARTITION Archive_Partition
--Partition must currently exist in database
      ALL TO ([PRIMARY])

CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)

CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Archive_Scheme (col1)

INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')

INSERT INTO archive_data VALUES (200,'Description')
INSERT INTO archive_data VALUES (300,'Description')
INSERT INTO archive_data VALUES (400,'Description')

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
      data'))

--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data PARTITION 1

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
      data'))
Use a Single Partition to Create a New Table You can move a single partition from an existing partitioned table into an empty non-partitioned table. By doing so, a database administrator can perform index maintenance on the single partition or easily drop large amounts of data without having that delete process logged. The sample in Figure 7 shows how to move a partition into an empty table and then use that new table to drop the data.
Figure 7 Moving and dropping data (Close Figure 7)
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO

IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO

IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO

IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO

--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)

--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
      ALL TO ([PRIMARY])

CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)

CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)

INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
      data'))

--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
      data'))

--Drop all archive data without logging
DROP TABLE archive_data
GO

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
      data'))

Partitioned Indexes
With the ability to partition the data of a table comes the ability to create partitioned indexes. This allows the database administrator to design the index structure based on the divided data rather than on the data of the entire table. Creating partitioned indexes results in individual B-trees on the partitioned indexes. The division of the indexes has the effect of creating smaller indexes that are more easily maintained by the storage engine during data modification, addition, and deletion. These smaller indexes can also be maintained individually by the database administrator, allowing for better index maintenance on large datasets.
Creating Partitioned Indexes When creating partitioned indexes, you can either create aligned indexes or non-aligned indexes. For aligned indexes, you create the index with a direct relationship to the partitioned data. (For non-aligned indexes, you choose a different partitioning schema.)
Aligned is the preferred method and will be done automatically if you create the partition table and then create the indexes without specifying a different partitioning scheme. Using aligned indexes gives you the flexibility to create additional partitions on the table and the ability to switch the ownership of a partition to another table. These capabilities are often the reason database administrators create partitioned tables in the first place, and simply using the partitioning schema of the table for your indexes will likely achieve your partitioning goals.
You can create indexes against tables where the data in the index is not aligned to the data in the table. If the data is in a partitioned table, this will allow you to join the data in different ways (partitioned data can be efficiently joined with other partitioned data by the query optimizer). Alternatively, you can do this with a non-partitioned table, allowing you to create a partitioned index (against the single partition table) so you can ease index maintenance.
The code in Figure 8 will create a partitioned, non-clustered index on a partitioned table. The non-clustered index will be aligned with the table and will utilize the partitioning column of the table as the non-clustered index key.
Figure 8 Partitioned, non-clustered index on partitioned table (Close Figure 8)
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO

IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO

IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO

--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)

--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
    ALL TO ([PRIMARY])

CREATE TABLE multiple_partition
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)

--Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
      partition(col1)

INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')

--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')

--Verify index partitions
SELECT partition_id, index_id FROM sys.partitions pt
WHERE pt.[object_id] = OBJECT_ID('multiple_partition')
The code in Figure 9 will create a non-aligned, non-clustered index on a partitioned table. This non-clustered index will use different columns for its index key, which can be used in collated joins against other partitioned tables.
Figure 9 Non-aligned, non-clustered index on partitioned table (Close Figure 9)
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO

IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO

IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Index_primary_Left_Scheme')
DROP PARTITION SCHEME Index_primary_Left_Scheme
GO

IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO

IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Index_Left_Partition')
DROP PARTITION FUNCTION Index_Left_Partition
GO

--Create partitioned index function
CREATE PARTITION FUNCTION Index_Left_Partition (int) AS RANGE LEFT
FOR VALUES (10,50,100)

--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)

--Place all index partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition
--Partition must currently exist in database
      ALL TO ([PRIMARY])

--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
      ALL TO ([PRIMARY])

CREATE TABLE multiple_partition
(
col1 INT
,col2 INT
) ON Primary_Left_Scheme (col1)

--Create non-aligned partitioned nonclustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
     partition(col2)
ON Index_primary_Left_Scheme (col2)

INSERT INTO multiple_partition VALUES (1,10)
INSERT INTO multiple_partition VALUES (2,10)
INSERT INTO multiple_partition VALUES (3,10)
INSERT INTO multiple_partition VALUES (4,10)
INSERT INTO multiple_partition VALUES (10,50)
INSERT INTO multiple_partition VALUES (11,50)
INSERT INTO multiple_partition VALUES (12,50)
INSERT INTO multiple_partition VALUES (13,50)
INSERT INTO multiple_partition VALUES (14,50)
INSERT INTO multiple_partition VALUES (100,100)
INSERT INTO multiple_partition VALUES (101,100)
INSERT INTO multiple_partition VALUES (102,100)
INSERT INTO multiple_partition VALUES (103,100)
INSERT INTO multiple_partition VALUES (104,100)

--Verify row count on partitioned data
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id = 0

--Verify row count on partitioned index
--Row counts will not match those found in the data partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id <> 0
Maintain Partitioned Indexes In the past, performing index maintenance against large tables containing millions or even billions of rows of data often took longer than database administrators had time for. This maintenance was frequently left unperformed due to the data being locked while the index was being rebuilt. With SQL Server 2005, the database administrator can perform index maintenance online without locking the underlying table for a long period of time. But even this approach (which has you performing index maintenance while users are accessing the data) can still slow down your system due to resource usage. A better approach is to partition indexes into smaller segments and then perform index maintenance against those smaller partitions. For instance, to perform index maintenance against one index partition, you could simply append the code snippet below to the end of the code shown in Figure 8.
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
Note that index maintenance against single index partitions must be performed offline and can cause locking of the table during the index maintenance. To prevent this, you can move the single partition into a separate partition, perform the index maintenance, and then move the partition back into the main table. This process will cause some performance issues as the partition is being moved back into the table and the clustered index is being updated, but this is less problematic than the locking of the entire table and requires fewer system resources.

Summary
As you can see, SQL Server 2005 table partitioning provides much improved flexibility for the storage and maintenance of data in large tables, without having to rework application code or SQL Server processes. With these abilities, SQL Server proves itself as a capable platform for enterprise-level, critical databases.

Noah Gomez is a Senior SQL Server Development DBA for Verizon who specializes in VLDBs and large-scale applications. He is a member of the Professional Association for SQL Server (PASS) and was on the Verizon DBA team that worked on the multi-terabyte VLDBs that won Winter Corp. Top Ten Grand Prize awards in 2003.


 SQL Server: Top Tips for SQL Server Clustering
SQL Server Tom Moreau, PhD

Top Tips for SQL Server Clustering
At a Glance:
  • Running SQL Server on a cluster
  • Hardware and software requirements
  • Clustering one node
  • Cost-effective options

A server cluster allows you to connect a number of physical servers—or nodes—that act as failover partners for each other. The redundancy a cluster provides spells more uptime for your critical
operations. I've implemented lots of clusters in my 13 years working with SQL Server™, and each had its own set of issues. That experience allowed me to gather a number of tips that can help make your clustering efforts easy and successful.
Server clusters take advantage of the built-in clustering capabilities of the Enterprise Editions of the Windows Server® family. In fact, for clustering purposes, Windows Server 2003 is significantly better than Windows 2000 Advanced Server. To maximize the benefits you'll gain from clustering, you need the right hardware-and that involves some expense. It's not sufficient to slap a couple of servers together with a shared disk, and you can't rely on the fact that individual hardware components may be in the Windows® Catalog (formerly known as the Hardware Compatibility List). The system as a whole must be in the Windows Catalog. But don't worry-there are some approved, lower-cost cluster solutions available. Figure 1 shows a typical cluster configuration.
Figure 1 A typical cluster
Figure 1 A typical cluster
Of course, there's more to clustering than hardware-you also need to choose the right edition of SQL Server 2005. The Enterprise Edition enables clustering, as well as other useful features, such as the ability to leverage more CPUs, distributed and updateable partitioned views, built-in log shipping, automatic use of indexed views. If you already have an Enterprise Edition license, you should consider clustering, whether or not you have the two to eight servers necessary to form a traditional cluster (we'll talk about one-node clusters in a minute). If you have SQL Server 2005 Standard Edition, you can install a two-node cluster.
Windows Server 2003 Enterprise and Datacenter Editions come with clustering built in. All you have to do is run Cluster Administrator to set up your cluster. You can add all nodes at once or one at a time. Similarly, when you install SQL Server, you can elect to install on an individual, non-clustered server, or you can install a virtual instance on a cluster. If you elect to install a virtual instance, you can install on all nodes of the cluster, just some nodes, or even only one node.
Finally, to achieve the true aim of clustering-high availability-you need qualified people and well-rehearsed procedures to follow if bad things happen. Although clustering is good insurance against hardware failure, it doesn't prevent user errors, such as the dropping of a critical table by a sleep-deprived DBA in the middle of the night.

One-Node Clusters
Even if all you need at the moment is a single server, consider creating a one-node cluster. This gives you the option of upgrading to a cluster later, thus avoiding a rebuild. Just be sure that the hardware you choose is on the cluster portion of the Windows Catalog.
It's not merely for high availability that you'd want the option to add a node at a later date. Consider what happens if you find that your server just doesn't have the necessary capacity. That translates to a migration-and that takes time and effort. If you have a one-node cluster, migration is easier with far less downtime. You add the new node to the cluster, add the SQL Server binaries and service packs to the new node, and then failover to the new node. Then you add any post-service pack updates and, finally, evict the old node. The downtime is only the time it takes to failover and add the updates (if any).

Adding Nodes
Since all nodes in a cluster must be the same, you'll want to act sooner, rather than later, to get that extra node. If you wait too long, the node may go out of production. On one project, I had to rebuild a node in a SQL Server 2000 cluster. I had the OS/network admin handle the basic machine build, then I jumped in to add it back to the cluster and prepare it for service as a SQL Server node. All went well until I failed over to the new node. Much to my dismay, it failed right back. To make a long story short, although I had prepared a detailed document on building a new cluster, including adding the cluster service and SQL Server service accounts to both nodes, the document wasn't followed explicitly. The admin didn't add those service accounts to the rebuilt node, so the privileges they had before the rebuild no longer existed.
It took me a long time to track that one down. One day it occurred to me to look at local group membership. Once I added the two accounts, failover went smoothly. Then I got to thinking. Rebuilding a node is something you don't do frequently and, if you do, it's an emergency. Although I had a document in place, it wasn't used. We could have automated the security part by simply writing a brief script to add those two accounts and make any other necessary customizations. Things have improved in SQL Server 2005, though. The installer requires you to set domain-level groups for the SQL Server service accounts.
Of course, this got me thinking even more. You can create scripts that invoke CLUSTER.EXE to add the node to your Microsoft® Cluster Server (MSCS) cluster. All you have to do is feed the script the name of the node and it can handle the rest. In an emergency, automation is really your friend.

N+1 Clusters
Sometimes, the reason for adding a node to a cluster isn't that you're replacing a node. You could be adding more SQL Server instances to your cluster and each instance needs separate disk resources. Though multiple instances can run on a single node, they would be sharing CPU and RAM-and that could spell poor performance. Ideally, only a single instance should run on a single node. How do you ensure that when you fail over? Simple: the answer is that one node has no services running on it, while the other nodes each run one SQL Server instance. In fact, that's the definition of an N+1 cluster: N instances running on N+1 nodes. The extra node is the backup.

Upgrading SQL Server
Upgrading a clustered instance of SQL Server is not for the faint of heart: it's clustered for a reason-you need uptime. But SQL Server 2005 offers a number of enhancements you want to take advantage of, so if and when you've got to do it, you can proceed without a lot of downtime.
What are your choices? Let's look at the most expensive solution first: creating a whole new cluster, which means new servers and perhaps a new storage area network (SAN). You can probably keep the existing network switches, but that's about all. Obviously, this approach isn't cheap but it has advantages. New hardware generally performs much better than old, with disk capacity and speed increasing at an ever-growing rate. Thus, you'll get a performance boost with new hardware alone. You may even want to lease your equipment just to stay ahead of the curve.
Once you have the hardware in place, you can create your new virtual SQL Server on this setup, copy your production databases over, and then put the new system through its paces, leaving plenty of time to shake out the bugs before cutover day. Just be sure to script out the logins from your existing server. (Check out support.microsoft.com/kb/246133. It's also a good idea to update your login build script in case of catastrophic failure.)
To minimize downtime, you'll likely have to use log shipping, unless your databases are quite small and you have a period of time in which no users are connected. You can log-ship right up to just before cutover. Then, kick the users out, cut and ship the final log, then point the app at the new instance. (Check out the database mirroring section below for an interesting alternative to log shipping.) If you use DNS aliases, you probably won't even need to point the apps to the new instance. Just update the DNS alias instead. This approach has the advantage that if you get part way through the migration and have to revert back to the original, at least you have the original.
You can take a less expensive route, but it requires more advance planning. A cluster can support more than one SQL Server instance, but each instance must have its own disk resources. So when you're carving up your SAN, set one LUN aside for a future upgrade. To perform the upgrade, install SQL Server binaries on this disk resource. You can exercise the system and, when you're ready, shut down the current SQL Server, move the disk resources from the old SQL Server group, update the dependencies, and bring the new SQL Server instance online. Attach the databases from the old instance, and you're up and running. (You did back everything up ahead of time, right?)
That's the less-expensive approach-and it carries with it some risk. If something goes bad, you can't detach the databases from the new instance and put them back. You're reduced to restoring from backups-and that can mean some serious downtime.
An alternative is putting two instances of SQL Server on your SAN, assuming you have enough space. You restore production backups (and log ship) to the new instance, and proceed much as I described earlier. However, now you have a fallback. Once you've done the migration, you can free up the SAN resources from the old instance. It'll cost you only the price of the extra disks.

Load Balancing
Let's start by debunking a common misconception. You use MSCS clustering for high availability, not for load balancing. Also, SQL Server does not have any built-in, automatic load-balancing capability. You have to load balance through your application's physical design. What does that mean?
As a table grows, you can expect to see some degradation in performance, particularly when table scans are involved. When you get into the millions or billions of rows, the traditional solution has been to use partitioned views, which are made up of tables with identical schemas hooked together with UNION ALL's. Also, CHECK constraints are put in place to differentiate the member tables, and this prevents data duplication across the partitioned view. If the column that is used in the CHECK constraint is also part of the primary key, the view is updatable.
If the member tables are on their own filegroups, you may get better disk performance if the files in those filegroups are on separate physical drives. The tables can even be in separate databases. In SQL Server 2005, however, as long as all of the data is in the same database, you can use table partitioning, which is far easier to implement.
But let's say you've tried your best with table partitioning or (local) partitioned views and still things are slow. If you have SQL Server 2000 or SQL Server 2005, you can use distributed partitioned views. The major difference is that the member tables can reside on different instances of SQL Server and those instances can be installed on an N+1 cluster. Why is this a good idea? If any one member table goes offline in a partitioned view, the entire view goes offline. Making those members part of a cluster then gives you the reliability you need to support performance and provide load balancing.

Do You Really Need a Cluster?
Perhaps you have some spare servers lying around, but they aren't in the Windows Catalog for clusters. It's a shame to have to go out and buy new ones just to support a cluster when those servers are available.
Database mirroring may be an attractive alternative to clustering. Mirroring involves three elements: an instance that houses the mirrored database is known as the principal; the backup server is known as the mirror; and, if you want automatic failover, a third server-known as the witness-is required. Briefly, a transaction in a database on the principal gets run again in the mirror. If the principal goes down, the database can fail over to the mirror, automatically if you have a witness. You have to set up mirroring for each of your application databases and you can't mirror system databases.
The mirror is a separate instance of SQL Server, unlike in a cluster, and can be located thousands of miles away. Its caches get populated by the update activity that occurs as a result of the transactions duplicated from the principal. Assume, of course, that there is no activity on the mirror other than receiving the mirrored transactions from the principal. Failover is generally quicker than in a cluster since SQL Server is already running on the mirror. Because the caches are at least partially primed, the initial performance is not as sluggish as it might be in the clustered scenario. And note that when a mirrored database fails over, the role of principal and mirror are reversed.
The downside of database mirroring is the need for double the total disk capacity than with a cluster. You'll also need more CPU power-if you go for synchronous mode with no data loss. As I said, high availability isn't cheap.

A Combined Approach
Since a mirror can be located quite remotely from the principal, it's a good choice for Disaster Recovery (DR) plans. Your cluster can be your first line of defense, but what happens if you employ both clustering and mirroring? In a cluster failover, if you have a witness as part of your mirroring configuration, the mirror will become the principal while the clustered SQL Server is coming online. However, note that failover from the new principal back to the (clustered) new mirror is not automatic. Consequently, it's better not to enable automatic failover for your mirrored databases when used in conjunction with a cluster.
DR isn't the only reason you'd use mirroring; it's also useful if you have to apply a service pack or hotfix to your principal, in which case you can manually failover to your mirror. While applying the service pack or hotfix, the old principal server is temporarily offline and the committed transactions occurring at the new principal are queued up, waiting to be sent back to the new mirror (old principal). Once the service pack or hotfix installation is complete, synchronization will take place and eventually the two servers will be completely in sync. Now, you can switch the roles of principal and mirror. Downtime was the couple of seconds to failover and back. You can use this approach to migrate your SQL Server to another box. Just don't fail back.

Virtual Server Adds Flexibility
Virtualization allows you to run one or more operating systems concurrently on a single physical server. Virtualization software adds another layer of capabilities to the cluster concept because you can cluster the software. Consequently, if the server on which the host is running fails, then it-and its guest OSs-failover to a backup node. This could be a very easy way to migrate a guest server. Plus, the guest OS does not have to be cluster-capable. Thus, you could run SQL Server Workgroup Edition inside a guest Windows Server 2003, running on Microsoft Virtual Server 2005 on a cluster. Indirectly, you have essentially clustered Workgroup Edition (see Figure 2).
Figure 2 Using a virtual server
Figure 2 Using a virtual server

In Control
If you're in charge of a SQL Server implementation, you need to know that your server is always available. Server clustering helps to ensure that's always the case. This article provides some hard-earned tips to help you get started, and you'll find more useful information in the "Clustering Resources" sidebar.
Clustering Resources
For more information on the methods used here and the various products you need to set up your SQL Server cluster, see the following:
  • SQL Server 2005 Failover Clustering
  • SQL Server 2000 Failover Clustering
  • Windows Server 2003 R2 Enterprise Edition–Cluster Server
  • Microsoft Cluster Server Administrator’s Guide
  • Microsoft Virtual Server 2005 R2

Tom Moreau, PhD, BSc, PhD, MCSE, MCDBA, is an independent consultant specializing in SQL Server database administration, design, and implementation, and is based in the Toronto area. Tom’s been using SQL Server since 1993 and has been an MVP since 2001. He’s written over 100 articles and coauthored a book on SQL Server. Thanks to SQL Server MVP Geoff Hiten for his useful input.


 Windows Administration: Inside the Windows Vista Kernel: Part 2
This article is the second part of a three part series. Click here to read Part One. Click here to read Part Three.

Windows Administration Mark Russinovich

Inside the Windows Vista Kernel: Part 2
At a Glance:
  • Memory management
  • Startup and shutdown
  • Power management

Last month, in the first installment of this three-part series, I looked at Windows Vista kernel enhancements in the areas of processes and I/O.
This time I'll cover advances in the way Windows Vista manages memory, as well as major improvements to system startup, shutdown, and power management (Part 1).
Every release of Windows® improves scalability and performance, and Windows Vista™ is no different. The Windows Vista Memory Manager includes numerous enhancements, like more extensive use of lock-free synchronization techniques, finer-grained locking, tighter data-structure packing, larger paging I/Os, support for modern GPU memory architectures, and more efficient use of the hardware Translation Lookaside Buffer. Plus, Windows Vista memory management now offers dynamic address space allocation for the requirements of different workloads.
Four performance-enhancing features that use new technologies make their operating system debut on Windows Vista: SuperFetch, ReadyBoost, ReadyBoot, and ReadyDrive. I'll discuss them in detail later in this article.

Dynamic Kernel Address Space
Windows and the applications that run on it have bumped their heads on the address space limits of 32-bit processors. The Windows kernel is constrained by default to 2GB, or half the total 32-bit virtual address space, with the other half reserved for use by the process whose thread is currently running on the CPU. Inside its half, the kernel has to map itself, device drivers, the file system cache, kernel stacks, per-session code data structures, and both non-paged (locked-in physical memory) and paged buffers allocated by device drivers. Prior to Windows Vista, the Memory Manager determined at boot time how much of the address space to assign to these different purposes, but this inflexibility sometimes led to situations where one of the regions became full while others still had plenty of available space. The exhaustion of an area can lead to application failures and prevent device drivers from completing I/O operations.
In 32-bit Windows Vista, the Memory Manager dynamically manages the kernel's address space, allocating and deallocating space to various uses as the demands of the workload require. Thus, the amount of virtual memory used to store paged buffers can grow when device drivers ask for more, and it can shrink when the drivers release it. Windows Vista will therefore be able to handle a wider variety of workloads and likewise the 32-bit version of the forthcoming Windows Server® code-named "Longhorn," will scale to handle more concurrent Terminal Server users.
Of course, on 64-bit Windows Vista systems, address space constraints are not currently a practical limitation and therefore require no special treatment as they are configured to their maximums.

Memory Priorities
Just as Windows Vista adds I/O priorities (as I discussed in the last installment), it also implements memory priorities. Understanding how Windows uses memory priorities requires grasping how the Memory Manager implements its memory cache, called the Standby List. On all versions of Windows prior to Windows Vista, when a physical page (which is typically 4KB in size) that's owned by a process was reclaimed by the system, the Memory Manager typically placed the page at the end of the Standby List. If the process wanted to access the page again, the Memory Manager took the page from the Standby List and reassigned it to the process. When a process wanted to use a new page of physical memory and no free memory was available, the Memory Manager gave it the page at the front the Standby List. This scheme treated all pages on the standby essentially as equals, using only the time they were placed on the list to sort them.
On Windows Vista, every page of memory has a priority in the range of 0 to 7, and so the Memory Manager divides the Standby List into eight lists that each store pages of a particular priority. When the Memory Manager wants to take a page from the Standby List, it takes pages from low-priority lists first. A page's priority usually reflects that of the thread that first causes its allocation. (If the page is shared, it reflects the highest of memory priorities of the sharing threads.) A thread inherits its page-priority value from the process to which it belongs. The Memory Manager uses low priorities for pages it reads from disk speculatively when anticipating a process's memory accesses.
By default, processes have a page-priority value of 5, but functions allow applications and the system to change process and thread page-priority values. The real power of memory priorities is realized only when the relative priorities of pages are understood at a macro-level, which is the role of SuperFetch.

SuperFetch
A significant change to the Memory Manager is in the way that it manages physical memory. The Standby List management used by previous versions of Windows has two limitations. First, the prioritization of pages relies only on the recent past behavior of processes and does not anticipate their future memory requirements. Second, the data used for prioritization is limited to the list of pages owned by a process at any given point in time. These shortcomings can result in scenarios like the "after lunch syndrome," where you leave your computer for a while and a memory-intensive system application runs (such as an antivirus scan or disk defragmentation). This application forces the code and data that your active applications had cached in memory to be overwritten by the memory-intensive activities. When you return, you experience sluggish performance as applications have to request their data and code from disk.
Windows XP introduced prefetching support that improved boot and application startup performance by performing large disk I/Os to preload memory with code and file system data that it expected, based on previous boots and application launches. Windows Vista goes a big step further with SuperFetch, a memory management scheme that enhances the least-recently accessed approach with historical information and proactive memory management.
SuperFetch is implemented in %SystemRoot%\System32\Sysmain.dll as a Windows service that runs inside a Service Host process (%SystemRoot%\System32\Svchost.exe). The scheme relies on support from the Memory Manager so that it can retrieve page usage histories as well as direct the Memory Manager to preload data and code from files on disk or from a paging file into the Standby List and assign priorities to pages. The SuperFetch service essentially extends page-tracking to data and code that was once in memory, but that the Memory Manager has reused to make room for new data and code. It stores this information in scenario files with a .db extension in the %SystemRoot%\Prefetch directory alongside standard prefetch files used to optimize application launch. Using this deep knowledge of memory usage, SuperFetch can preload data and code when physical memory becomes available.
Whenever memory becomes free-for example, when an application exits or releases memory-SuperFetch asks the Memory Manager to fetch data and code that was recently evicted. This is done at a rate of a few pages per second with Very Low priority I/Os so that the preloading does not impact the user or other active applications. Therefore, if you leave your computer to go to lunch and a memory-intensive background task causes the code and data from your active applications to be evicted from memory while you're gone, SuperFetch can often bring all or most of it back into memory before you return. SuperFetch also includes specific scenario support for hibernation, standby, Fast User Switching (FUS), and application launch. When the system hibernates, for example, SuperFetch stores data and code in the hibernation file that it expects (based on previous hibernations) will be accessed during the subsequent resume. In contrast, when you resume Windows XP, previously cached data must be reread from the disk when it is referenced.
See the sidebar "Watching SuperFetch" for a glimpse of how SuperFetch impacts available memory.
Watching SuperFetch
After you’ve used a Windows Vista system a while, you’ll see a low number for the Free Physical Memory counter on Task Manager’s Performance page. That’s because SuperFetch and standard Windows caching make use of all available physical memory to cache disk data. For example, when you first boot, if you immediately run Task Manager you should notice the Free Memory value decreasing as Cached Memory number rises. Or, if you run a memory-hungry program and then exit it (any of the freeware “RAM optimizers” that allocate large amounts of memory and then release the memory will work), or just copy a very large file, the Free number will rise and the Physical Memory Usage graph will drop as the system reclaims the deallocated memory. Over time, however, SuperFetch repopulates the cache with the data that was forced out of memory, so the Cached number will rise and the Free number will decline.
Watching memory

ReadyBoost
The speed of CPUs and memory are fast outpacing that of hard disks, so disks are a common system performance bottleneck. Random disk I/O is especially expensive because disk head seek times are on the order of 10 milliseconds-an eternity for today's 3GHz processors. While RAM is ideal for caching disk data, it is relatively expensive. Flash memory, however, is generally cheaper and can service random reads up to 10 times faster than a typical hard disk. Windows Vista, therefore, includes a feature called ReadyBoost to take advantage of flash memory storage devices by creating an intermediate caching layer on them that logically sits between memory and disks.
ReadyBoost consists of a service implemented in %SystemRoot%\System32\Emdmgmt.dll that runs in a Service Host process, and a volume filter driver, %SystemRoot%\System32\Drivers\Ecache.sys. (Emd is short for External Memory Device, the working name for ReadyBoost during its development.) When you insert a flash device like a USB key into a system, the ReadyBoost service looks at the device to determine its performance characteristics and stores the results of its test in HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\Currentversion\Emdmgmt, seen in Figure 1.
Figure 1 ReadyBoost device test results in the registry
Figure 1 ReadyBoost device test results in the registry
If you aren't already using a device for caching, and the new device is between 256MB and 32GB in size, has a transfer rate of 2.5MB/s or higher for random 4KB reads, and has a transfer rate of 1.75MB/s or higher for random 512KB writes, then ReadyBoost will ask if you'd like to dedicate up to 4GB of the storage for disk caching. (Although ReadyBoost can use NTFS, it limits the maximum cache size to 4GB to accommodate FAT32 limitations.) If you agree, then the service creates a caching file named ReadyBoost.sfcache in the root of the device and asks SuperFetch to prepopulate the cache in the background.
After the ReadyBoost service initializes caching, the Ecache.sys device driver intercepts all reads and writes to local hard disk volumes (C:\, for example), and copies any data being written into the caching file that the service created. Ecache.sys compresses data and typically achieves a 2:1 compression ratio so a 4GB cache file will usually contain 8GB of data. The driver encrypts each block it writes using Advanced Encryption Standard (AES) encryption with a randomly generated per-boot session key in order to guarantee the privacy of the data in the cache if the device is removed from the system.
When ReadyBoost sees random reads that can be satisfied from the cache, it services them from there, but because hard disks have better sequential read access than flash memory, it lets reads that are part of sequential access patterns go directly to the disk even if the data is in the cache.

ReadyBoot
Windows Vista uses the same boot-time prefetching as Windows XP did if the system has less than 512MB of memory, but if the system has 700MB or more of RAM, it uses an in-RAM cache to optimize the boot process. The size of the cache depends on the total RAM available, but is large enough to create a reasonable cache and yet allow the system the memory it needs to boot smoothly.
After every boot, the ReadyBoost service (the same service that implements the ReadyBoost feature just described) uses idle CPU time to calculate a boot-time caching plan for the next boot. It analyzes file trace information from the five previous boots and identifies which files were accessed and where they are located on disk. It stores the processed traces in %SystemRoot%\Prefetch\Readyboot as .fx files and saves the caching plan under HKLM\System\CurrentControlSet\Services\Ecache\Parameters in REG_BINARY values named for internal disk volumes they refer to.
The cache is implemented by the same device driver that implements ReadyBoost caching (Ecache.sys), but the cache's population is guided by the ReadyBoost service as the system boots. While the boot cache is compressed like the ReadyBoost cache, another difference between ReadyBoost and ReadyBoot cache management is that while in ReadyBoot mode, other than the ReadyBoost service's updates, the cache doesn't change to reflect data that's read or written during the boot. The ReadyBoost service deletes the cache 90 seconds after the start of the boot, or if other memory demands warrant it, and records the cache's statistics in HKLM\System\CurrentControlSet\Services\Ecache\Parameters\ReadyBootStats, as shown in Figure 2. Microsoft performance tests show that ReadyBoot provides performance improvements of about 20 percent over the legacy Windows XP prefetcher.
Figure 2 ReadyBoot Performance statistics
Figure 2 ReadyBoot Performance statistics

ReadyDrive
ReadyDrive is a Windows Vista feature that takes advantage of new hybrid hard disk drives called H-HDDs. An H-HDD is a disk with embedded nonvolatile flash memory (also known as NVRAM). Typical H-HDDs include between 50MB and 512MB of cache, but the Windows Vista cache limit is 2TB.
Windows Vista uses ATA-8 commands to define the disk data to be held in the flash memory. For example, Windows Vista will save boot data to the cache when the system shuts down, allowing for faster restarting. It also stores portions of hibernation file data in the cache when the system hibernates so that the subsequent resume is faster. Because the cache is enabled even when the disk is spun down, Windows can use the flash memory as a disk-write cache, which avoids spinning up the disk when the system is running on battery power. Keeping the disk spindle turned off can save much of the power consumed by the disk drive under normal usage.

Boot Configuration Database
Windows Vista has enhanced several aspects of startup and shutdown. Startup has improved with the introduction of the Boot Configuration Database (BCD) for storing system and OS startup configuration, a new flow and organization of system startup processes, new logon architecture, and support for delayed-autostart services. Windows Vista shutdown changes include pre-shutdown notification for Windows services, Windows services shutdown ordering, and a significant change to the way the OS manages power state transitions.
One of the most visible changes to the startup process is the absence of Boot.ini from the root of the system volume. That's because the boot configuration, which on previous versions of Windows was stored in the Boot.ini text file, is now stored in the BCD. One of the reasons Windows Vista uses the BCD is that it unifies the two current boot architectures supported by Windows: Master Boot Record (MBR) and Extensible Firmware Interface (EFI). MBR is generally used by x86 and x64 desktop systems, while EFI is used by Itanium-based systems (though desktop PCs are likely to ship with EFI support in the near future). The BCD abstracts the firmware and has other advantages over Boot.ini, like its support for Unicode strings and alternate pre-boot executables.
The BCD is actually stored on disk in a registry hive that loads into the Windows registry for access via registry APIs. On PCs, Windows stores it in \Boot\Bcd on the system volume. On EFI systems, it's on the EFI system partition. When the hive is loaded, it appears under HKLM\Bcd00000000, but its internal format is undocumented so editing it requires the use of a tool like %SystemRoot%\System32\Bcdedit.exe. Interfaces for manipulating the BCD are also made available for scripts and custom editors through Windows Management Instrumentation (WMI) and you can use the Windows System Configuration Utility (%SystemRoot%\System32\Msconfig.exe) to edit or add basic parameters, like kernel debugging options.
The BCD divides platform-wide boot settings, like the default OS selection and the boot menu timeout, from OS-specific settings such as OS boot options and the path to the OS boot loader. For example, Figure 3 shows that when you run Bcdedit with no command-line options, it displays platform settings in the Windows Boot Manager section at the top of the output, followed by OS-specific settings in the Windows Boot Loader section.
Figure 3 Settings displayed in BCDEdit
Figure 3 Settings displayed in BCDEdit
When you boot a Windows Vista installation, this new scheme divides the tasks that were handled by the operating system loader (Ntldr) on previous versions of Windows into two different executables: \BootMgr and %SystemRoot%\System32\Winload.exe. Bootmgr reads the BCD and displays the OS boot menu, while Winload.exe handles operating-system loading. If you're performing a clean boot, Winload.exe loads boot-start device drivers and core operating system files, including Ntoskrnl.exe, and transfers control to the operating system; if the system is resuming from hibernation, then it executes %SystemRoot%\System32\Winresume.exe to load the hibernation data into memory and resume the OS.
Bootmgr also includes support for additional pre-boot executables. Windows Vista comes with the Windows Memory Diagnostic (\Boot\Memtest.exe) pre-configured as an option for checking the health of RAM, but third parties can add their own pre-boot executables as options that will display in Bootmgr's boot menu.

Startup Processes
In previous versions of Windows, the relationship between various system processes was unintuitive. For example, as the system boots, the interactive logon manager (%SystemRoot%\System32\Winlogon.exe) launches the Local Security Authority Subsystem Service (Lsass.exe) and the Service Control Manager (Services.exe). Further, Windows uses a namespace container called a Session to isolate processes running in different logon sessions. But prior to Windows Vista, the user logged into the console shared Session 0, the session used by system processes, which created potential security issues. One such issue was introduced, for example, when a poorly written Windows service running in Session 0 displayed a user interface on the interactive console, allowing malware to attack the window through techniques like shatter attacks and possibly gain administrative privileges.
To address these problems, several system processes were re-architected for Windows Vista. Session Manager (Smss.exe) is the first user-mode process created during the boot as in previous versions of Windows, but on Windows Vista the Session Manager launches a second instance of itself to configure Session 0, which is dedicated solely to system processes. The Session Manager process for Session 0 launches the Windows Startup Application (Wininit.exe), a Windows subsystem process (Csrss.exe) for Session 0, and then it exits. The Windows Startup Application continues by starting the Service Control Manager, the Local Security Authority Subsystem, and a new process, Local Session Manager (Lsm.exe), which manages terminal server connections for the machine.
When a user logs onto the system, the initial Session Manager creates a new instance of itself to configure the new session. The new Smss.exe process starts a Windows subsystem process and Winlogon process for the new session. Having the primary Session Manager use copies of itself to initialize new sessions doesn't offer any advantages on a client system, but on Windows Server "Longhorn" systems acting as terminal servers, multiple copies can run concurrently to allow for faster logon of multiple users.
With this new architecture, system processes, including Windows services, are isolated in Session 0. If a Windows service, which runs in Session 0, displays a user interface, the Interactive Services Detection service (%SystemRoot%\System32\UI0Detect.exe) notifies any logged-on administrator by launching an instance of itself in the user's security context and displaying the message shown in Figure 4. If the user selects the "Show me the message" button, the service switches the desktop to the Windows service desktop, where the user can interact with the service's user interface and then switch back to their own desktop. For more on what happens at startup, see the sidebar "Viewing Startup Process Relationships."
Figure 4 Service has displayed a window
Figure 4 Service has displayed a window
Viewing Startup Process Relationships
You can use Process Explorer from Sysinternals (microsoft.com/technet/sysinternals) to see the process startup tree of Windows Vista.
The screenshot includes the Session column, which you can add through Process Explorer’s column dialog. The highlighted process is the initial Smss.exe. Below it is the Session 0 Csrss.exe and Wininit.exe, which are left-justified because their parent process, the instance of Smss.exe that configured Session 0, has exited. Wininit’s three children are Services.exe, Lsass.exe, and Lsm.exe.
Process Explorer identifies a set of processes as running in Session 1 and that’s the session I’m logged into through a Remote Desktop connection. Process Explorer displays processes running in the same account as itself with a blue highlight color. Finally, Session 2 was initialized to prepare for a user logging into the console and creating a new logon session. It’s in that session that Winlogon is running and using LogonUI to ask a new console user to “Press Ctrl+Alt+DELETE to Log on”, and in which Logonui.exe will ask the user for his credentials.
Startup process and session information
Startup process and session information

Credential Providers
Even the logon architecture is changed on Windows Vista. On previous versions of Windows, the Winlogon process loaded the Graphical Identification and Authentication (GINA) DLL specified in the registry to display a logon UI that asked users for their credentials. Unfortunately, the GINA model suffers from several limitations, including the fact that only one GINA can be configured, writing a complete GINA is difficult for third parties, and custom GINAs that have non-standard user interfaces change the Windows user experience.
Instead of a GINA, Windows Vista uses the new Credential Provider architecture. Winlogon launches a separate process, the Logon User Interface Host (Logonui.exe), that loads credential providers that are configured in HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\Currentversion\Authentication\Credential Providers. Logonui can host multiple credential providers concurrently; in fact, Windows Vista ships with interactive (Authui.dll) and smartcard (Smart-cardcredentialprovider.dll) providers. To ensure a uniform user experience, LogonUI manages the user interface that is displayed to end users, but it also allows credential providers to specify custom elements like text, icons, and edit controls.

Delayed-Autostart Services
If you've ever logged onto a Windows system immediately after it starts, you've probably experienced delays before your desktop is fully configured and you can interact with the shell and any applications you launch. While you're logging on, the Service Control Manager is starting the many Windows services that are configured as automatic start services and therefore activate at boot time. Many services perform CPU and disk-intensive initializations that compete with your logon activities. To accommodate this, Windows Vista introduces a new service start type called delayed automatic start, which services can use if they don't have to be active immediately after Windows boots.
The Service Control Manager starts services configured for delayed automatic start after the automatic-start services have finished starting and it sets the priority of their initial thread to THREAD_PRIORITY_LOWEST. This priority level causes all the disk I/O the thread performs to be Very Low I/O priority. After a service finishes initializing, the Service Control Manager sets its priority to normal. The combination of the delayed start, low CPU and memory priority, and background disk priority greatly reduce interference with a user's logon. Many Windows services, including Background Intelligent Transfer, Windows Update Client, and Windows Media® Center, use the new start type to help improve the performance of logons after a boot.

Shutdown
A problem that's plagued Windows service writers is that during a Windows shutdown they have, by default, a maximum of twenty seconds to perform cleanup. Versions of Windows prior to Windows Vista haven't supported a clean shutdown that waits for all services to exit because a buggy service can hold up a shutdown indefinitely. Some services, like those that have network-related shutdown operations or have to save large amounts of data to disk, might require more time and so Windows Vista allows a service to request pre-shutdown notification.
When Windows Vista shuts down, the Service Control Manager first notifies those services asking for pre-shutdown notification. It will wait indefinitely for these services to exit, but if they have a bug and don't respond to queries, the Service Control Manager gives up and moves on after three minutes. Once all those services have exited or the timeout has expired, the Service Control Manager proceeds with legacy-style services shutdown for the rest of the services. The Group Policy and Windows Update services register pre-shutdown notification in a fresh Windows Vista installation.
The Group Policy and Windows Update services also use another Windows Vista services feature: shutdown ordering. Services have always been able to specify startup dependencies that the Service Control Manager honors to start services in an order that satisfies them, but until Windows Vista they have been unable to specify shutdown dependencies. Now services that register for pre-shutdown notification can also insert themselves into the list stored at HKLM\System\CurrentControlSet\Control\PreshutdownOrder and the Service Control Manager will shut them down according to their order. See the sidebar "Identifying a Delayed-Autostart and Pre-Shutdown Service" for more on these services.

Power Management
Sleep and hibernate are other forms of shutdown, and buggy power management in drivers and applications has been the curse of road warriors since Windows 2000 introduced power management to the Windows NT®-based line of Windows operating systems. Many users have expected their laptop system to suspend or hibernate when they closed the lid before embarking on a trip, only to arrive at their destination with a hot carrying case, a dead battery, and lost data. That's because Windows has always asked device drivers and applications for their consent to change power state and a single unresponsive driver or application could prevent a transition.
In Windows Vista, the kernel's Power Manager still informs drivers and applications of power-state changes so that they can prepare for them, but it no longer asks for permission. In addition, the Power Manager waits, at most, 20 seconds for applications to respond to change notifications, rather than the two minutes it waited on previous versions of Windows. As a result, Windows Vista users can be more confident that their systems are honoring hibernations and suspends.

Next Up
As mentioned earlier, this is the second installment in a three-part series. The first part covered Windows Vista kernel improvements in the areas of I/O and processes. This time, I looked at Windows Vista enhancements in memory management, startup, and shutdown. Next time, I'll conclude the series by describing changes to the kernel in the areas of reliability and security.
Identifying a Delayed-Autostart and Pre-Shutdown Service
The built-in SC command is updated in Windows Vista to show services configured as delayed autostart services:
Using SC to display start type
Using SC to display start type
Unfortunately, the SC command does not report services that have requested pre-shutdown notification, but you can use the PsService utility from Sysinternals to see that a service accepts pre-shutdown notification:
Viewing pre-shutdown status
Viewing pre-shutdown status

Mark Russinovich is a Technical Fellow at Microsoft in the Platform and Services Division. He is a coauthor of Microsoft Windows Internals (Microsoft Press, 2004) and a frequent speaker at IT and developer conferences. He joined Microsoft with the recent acquisition of the company he cofounded, Winternals Software. He also created Sysinternals, where he published the Process Explorer, Filemon, and Regmon utilities.


 From the Editor: Trophy Shelf
From The Editor
Trophy Shelf
Joshua Trupin

I'VE GONE ABOUT THREE DECADES of my life without winning anything. My instant lottery tickets always end up with two cherries and a frowny face. I open up candy wrappers and they say "Sorry, try again." I pick New York teams in playoff football pools.
Considering this poor track record, it's no wonder I get a thrill out of actually winning something. TechNet Magazine just collected four awards from the Society for Technical Communication (STC). We put a lot of work into every issue, so it's wonderful to have that work recognized. I want to take a moment to acknowledge our staff and authors for their contributions.
Our March/April 2006 issue brought you a clear and accessible guide to Active Directory in the form of a jigsaw puzzle poster. It was wildly popular-in fact, we're still getting daily requests for copies! The STC gave our poster an award of Excellence for Software Guides.
Two of our articles also received high marks. Both the August 2006 "Playing with Exchange in a Sandbox," by Greg Taylor and Simon Shepherd, and the May/June 2006 "Deconstructing Common Security Myths," by Jesper Johansson and Steve Riley, received awards of Merit from the STC.
TechNet Magazine itself won an Award of Excellence for Scholarly & Professional Journals. I couldn't be prouder of the recognition, and the same goes for everyone who works on our team.
Don't worry, we're not going to rest on our laurels. In this issue, Mark Russinovich explores more of the kernel changes in Windows Vista-key information of lasting value for any IT pro involved in desktop deployment or support services. We're also bringing you in-depth coverage of SQL Server this month, which runs the gamut from using index metadata to table partitioning and high-availability options to tips for clustering.
We never take a break here at One TechNet Magazine Plaza. (Deadlines will do that to you.) However, we do enjoy an occasional change of venue. We'll be at Microsoft TechoEd in Orlando from June 4-8, 2007 (see microsoft.com/teched). Come by to meet some of the award-winning editors and authors of TechNet Magazine. We've got some giveaways and fun stuff planned, so you never know-we could make you a winner too. -JOSHUA TRUPIN
Thank you to the following Microsoft technical experts: Vijay Bharadwaj, Paul Bowden, Elden Christensen, Manish Karla, Michael Murgolo, Rajeev Nagar, John Peng, Mark Russinovich, Abraham Samuel, Mark Scurrell, Jim Truher, George Walters, and Buck Woody.

Joshua Trupin


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

Manage Synchronization of the Network
ViceVersa Pro
www.tgrmn.com

Unless your infrastructure consists of a single box, you probably have to move files around your environments all the time. From log files to application releases to database backups, data is always moving across the network. And it is important to know what you need to copy to keep different environments in sync. For most IT professionals, moving those files among environments manually is not an option—not to mention that it's generally a bad idea because it can lead to bad syncs.
TGRMN Software's ViceVersa Pro lets you easily automate and schedule those tasks. The application provides four different copy modes. Basic Synchronization gives you source-to-destination synchronization and the Advanced Synchronization mode offers bidirectional file and directory synchronization with file tracking. The other two modes are Backup (which will mirror your source) and Consolidation.
While the Backup mode will mirror the source to the target, adding and deleting files from that target, you can also choose file replication, which will only copy files to the target. This could prove very useful if you have users who delete things unexpectedly. Bidirectional synchronization performs as the name suggests, with the addition of conflict and deletion tracking. If a file changes in both the source and the target before the next synchronization, a conflict is noted and you can reconcile the problem manually. The Consolidation method is similar to bidirectional synchronization, but it doesn't keep track of conflicts or deletions.
Protect against copy errors and data loss
Protect against copy errors and data loss
ViceVersa Pro can help with log shipping tasks using the integrated file compression and file encryption features in Backup mode. The app compresses your source files to the standard gzip format, which can then be password-protected.
The app comes with a good integrated file comparison too, but if you prefer, you can also set ViceVersa Pro to use an external file comparison program. In order to support a global environment, ViceVersa can handle Unicode characters and paths longer than 256 characters. The application also has built-in support for Internet time synchronization, so you can set it to point to your local NTP box or any outside NTP server if you allow outbound requests from the environment in which you will be using the application.
At times bandwidth utilization can become a concern, especially if your traffic is flowing across a VPN or WAN. In that case, ViceVersa lets you throttle the amount of bandwidth the application uses on each synchronization task. This can also help ensure production-level response from the servers that you are copying to or from. Or, if you choose, you can set a maximum CPU utilization threshold to also help ensure availability on your server when the application is running.
ViceVersa Pro protects against copy errors and data loss by using a safe copy algorithm. The app also performs verification cyclic redundancy checks to ensure data integrity. You can schedule synchronization tasks to run at almost any interval including real-time mode (this will synch files as they are added or deleted to the synchronization folders).
Another cool feature of ViceVersa Pro is that the UI lets you preview all operations before executing them. This helps to ensure that you are copying what you mean to copy; there's nothing like ending up with a couple gigs of log files in the system directory.
All in all, if you are looking for a low-price, feature-full synchronization tool, ViceVersa Pro may suit your needs.
Price: $59.95 per single user.

Administer Your System's Disks
Partition Manager 8.0 Professional Edition
www.partition-manager.com/corporate/professional
If you are looking for an application to help you administer your system's disks, Paragon Software Group's Partition Manager 8.0 Professional Edition is definitely worth considering.
As its core functionality, the Windows®-based app gives you an intuitive wizard-driven interface that lets you easily create, delete, change the visibility, and format hard disk partitions, whether they be FAT16/32, NTFS, Ext2/3 or HPFS format. You can also resize existing partitions or move partitions from one disk to another.
Taking it a step further, you can convert between file system types such as NTFS from FAT16/32 or Ext2 to Ext3 and back. Copying a partition across disks will automatically resize the partition to the size of the disk, which can be handy for extending the data drive on your system without having to copy all of the files manually. One useful feature of version 8.0 is the ability to do a "Hot Resize," which allows you to increase the size of an active NTFS partition without rebooting Windows.
Increase the size of an active NTFS partition — without rebooting
Increase the size of an active NTFS partition — without rebooting
Another helpful feature of the Pro Edition is that it lets you convert dynamic disks to basic disks on Windows 2000, Windows XP, and Windows Server® 2003 clients. Partition Manager supports disks up to at least 500GB (Paragon's testing limit, not a real physical limit) as well as FireWire and USB-connected drives. Partition Manager will support PATA (IDE), SATA, and SCSI hard disks.
Partition Manager lets you create a Recovery CD (or a DVD or even a floppy disk) to help you restore a system after a catastrophic disk failure. Or, if you mistakenly delete a partition but the system is still active, you can undelete the partition via the interface. You can even change the cluster size of your disk to help fine-tune performance. The application also gives you a "virtual pre-execution" view, allowing you to evaluate your disk changes before you commit them physically to disk.
You can also upgrade a hard drive on your system with Partition Manager 8.0. The app allows you to copy a partition or whole hard disk image to a new disk, adapting its size to target partition.
Finally, Partition manager also contains an embedded disk defragmenter that can defragment drives that are up to 99 percent full, though hopefully, you will be able to take action before a drive reaches that state.
Price: $99.95 for Professional Edition.

Add Auto-Completion to your SQL Editor
SQL Prompt: IntelliSense for SQL
www.red-gate.com/products/SQL_Prompt/index.htm

It's tempting to think all databases are created and structured with logical naming conventions in a normalized form, but sadly, we all know that's not the case. When playing the role of system administrator or database administrator, I find myself having to refer back to the table structure frequently when creating or running queries, and even more so when acting as a developer.
Though SQL Server™ Management Studio for SQL Server 2005 has made life much easier by integrating an object browser into the tree view section of the interface, I still find myself wanting that feature I have become all too used to in the Visual Studio® IDE: IntelliSense®. Fortunately, Red-Gate Software offers SQL Prompt, which gives you autocompletion for Microsoft SQL Server editors.
The application runs in your task bar and it detects when you start working in an editor. First, SQL Prompt will ask you for the connection information for that database so that it can reach out and cache all the object data. This speeds performance and reduces overhead, but if you are constantly modifying objects on the database instance, you will need to remember to clear the cache (which is a simple two-click action) to see your changes on the prompt list.
In addition to the Visual Studio IDEs, Enterprise Manager, Query Analyzer, and SQL Server Management Studio, SQL Prompt also directly integrates with UltraEdit and EditPlus 2. On the Options tab of the Settings UI you can indicate what SQL key words you would want to trigger the candidate list (such as FROM, JOIN, EXEC, and so on). Triggering SQL Prompt on SQL JOINS will even give you your ON statement (assuming you have reference set or the fields are named the same). Here too you can set text shortcuts for code snippet insertion. For example, the application sets "ssf" followed by the Tab key to spell out "SELECT * FROM". You can also have SQL Prompt automatically change SQL key words to uppercase as you type. This and the code snippets are time-savers for sure.
On the Candidates tab of the settings UI, you can set whether you want SQL Prompt to use fully qualified column names, prefix with the owner/schema information, surround your names with square brackets, and even set which types of SQL functions you want to show up in the candidate list. If you use SQL, you may want to add this tool to your bag of tricks.
SQL Prompt will remain free until the release of version 3.0, which, at the time of this writing, was in final beta. Pricing for version 3.0 has yet to be released
Price: Free.

Calculate Page Size
Atomic Web Page Size Calculator
www.info-pack.com/pagesize
End-user bandwidth may be getting fatter and new technologies are helping to deliver Web content faster to those end users, but page size is still important. All those bytes can add up to a big bandwidth cost for a high-traffic site.
The Atomic Page Size Calculator utility from Info-pack.com provides a low-cost, simple way to check the page weight of your Web site pages. All you need to do is type in the URL of the page of questionable size and the program grabs the page, calculating the weight of all the elements including any externally referenced images—items that may not show up in your Web logs for your primary domain.
Keep an eye on bandwidth cost
Keep an eye on bandwidth cost
The calculator supports page, image, and JavaScript files, so you should be able to get data on most of the resources on your page. The app will also notify you when it encounters a 404 "broken" resource. Once you get the weight information for your URL, click the Report button to see the size breakdown and estimations of the time it will take to download that URL at various connection speeds.
It would be great if the app could handle multiple URLs simultaneously, but perhaps that will come in a future release. If you are looking for a quick way to check the page size of your development team’s pages, this app may be for you.
Price: $19.95 direct.

Write to Toolbox
If you have a favorite tool or utility you would like to see featured here, please write to the author at tntools@microsoft.com.
Greg Steen is a technology professional, entrepreneur, and enthusiast. He is always on the hunt for new tools and methods to help make operations and development easier for IT professionals.


 Utility Spotlight: SyncToy
Utility Spotlight
SyncToy
Jay Munro
Download the code for this article: SynctToy (971KB)

A HEADACHE THAT plagues IT pros, network admins, and Help Desk staff alike is the call from a user whose computer went south without a backup. Getting even the savviest folks to save their work to the network rather than just locally can be difficult. SyncToy can help.
Originally designed to synchronize digital photographs between camera, computer, and external drives, SyncToy is a flexible and reliable way to backup almost any kind of file. Running on Windows Vista™, as well as Windows® XP and Windows Server® 2003, SyncToy works with local, external, or USB drives, as well as network or workgroup shares.
SyncToy uses a paired-folder architecture where you specify a left and a right directory. You can create as many folder pairs as you want and synchronize one or all with a single button click. To create a folder pair, you walk through a quick four-step wizard where you type or paste a path, or browse to a location for the left and right folders. SyncToy understands UNC file naming (such as \\MyServer\Joe_smith\backup\) as well as local drive mappings and paths (E:\doc_backup). Once paths are entered, you choose one of five actions that SyncToy will perform when comparing the folders.
Sync Copies new and updated files in both directions. If you rename or delete a file in one folder, that action is replicated in the other.
Echo Copies new and updated files, and performs deletes and renames only from the left folder to the right.
Subscribe Copies updated files from right to left, and only if the file already exists on the left. Changes made on the left will not be replicated to the right.
Contribute Copies new and updated files on the left to the right, while ignoring deletions.
Combine Keeps multiple machines in lockstep by copying files that exist on one side but not the other. Files that are deleted or renamed on either side are not affected or replicated.
Once you select the action, pick a friendly name for the folder pair, and you're finished. SyncToy currently does not let you edit paths or change the name once you create a folder pair, so if you make a mistake, select the folder pair, click delete, and create it again. You can set options including which files and subdirectories to include and whether to save overwritten files to the Recycle Bin (on by default).
Synchronizing files with SyncToy
Synchronizing files with SyncToy

The main SyncToy dialog box displays folder pairs on the left, along with the paths, options, and actions for the selected pair. The All Folder Pairs option lists all your folder pairs, and you can do a Preview or Run All on the checked pairs. As you'd expect, Preview goes through the motions as if actually performing the sync, but makes no changes. This is definitely worth doing if you're going to schedule unattended syncs. Preview displays a report, which tells you what operations it would have done. If all looks good, click Run.
Scheduling is not built-in, though the Help file has a fairly good walk-through for both Windows Vista and Windows XP schedulers. Non-technical users might need help setting this up, but you only have to do it once, as all changes to folder pairs, actions, and options are done in SyncToy itself.
Like all Power Toys, SyncToy, while written to the same standards as regular shipping software, is not supported by Microsoft, but rather through community forums and documentation. So you're on your own. The Help file is written for all use levels and covers the features quite extensively. The UI is intuitive enough that you get the basics the first time around.
So download SyncToy and show your users how to back up files. You'll all sleep better.

Jay Munro is a writer on the Windows User Assistance team at Microsoft, specializing in Internet Explorer. Previously, he was a project leader with PC Magazine labs.


 SQL Q&amp;A: Daylight Saving T ime, Server Memory, and More
SQL Q&A
Daylight Saving T ime, Server Memory, and More
Edited by Nancy Michell

Daylight Saving Time
Q Given the upcoming changes to Daylight Saving Time (DST) in the U.S. to comply with the Energy Policy Act of 2005, should I update SQL Server™?
A No. At this time there are no SQL Server-specific updates required to support the changes to DST. SQL Server relies on the underlying operating system for time-related data, which means if the OS is reporting the correct dates and times, SQL Server will report and use the same values. To comply with the upcoming changes to DST, you will be required to update your copies of Windows® as outlined at support.microsoft.com/kb/928388. This will be required on all Windows operating systems prior to Windows Vista™ (which already includes the changes) to comply with the DST changes, including those running SQL Server. (Australia is also experiencing some changes. See support.microsoft.com/kb/912475.)

Connecting with Windows Vista
Q I've installed Windows Vista and now I can't connect to SQL Server 2005 on my system. I'm a local administrator, and I used to be able to make this connection without any problem. What has happened?
A This is expected behavior in Windows Vista and SQL Server 2005 without Service Pack 2 (SP2). Windows Vista has a new security model (User Account Control) that in effect traps your membership in the local administrator's group and asks you to verify operations requiring administrative privileges. If you click the Allow button, your credentials, which include your administrator token, are sent along to the application. In the case of the SQL Server Management Studio (SSMS), you're not shown a dialog because just running the tool doesn't require administrative permissions. The problem is that by default the SQL Server 2005 system administrator role includes the local administrators group from the operating system, and that is what your account used in the past to access SQL Server. Since Windows Vista isn't sending that permission along, you don't gain access.
It should be noted that running SQL Server 2005 without SP2 isn't supported on Windows Vista, and that SP2 has a tool that will add your account for you. If you're still waiting for SP2, the fix is pretty simple. Your individual Windows user account must be added to SQL Server, in your case to the system administrator's role. To do this, just right-click the SQL Server Management Studio and select Run as Administrator. Connect to your SQL Server and add your Windows account to the system administrator's role. See SQL Server Books Online for more information on how that works.

Transactional Replication with Views
Q If I have a published view and I update it, I know that transaction will be replicated. If I update the base table of this view, however, will that transaction be replicated? Also, if I have a published table and create a view of it, and I update the view instead of the base table, will that transaction replicate?
A Assuming the base table is configured as an article in a publication (that is, you configured the base table for replication as well), then any update to the base table would be replicated.
When you replicate a view, by default all that is replicated as part of a view is the schema portion of the view, or the code behind the view, not the data that underlies it (unless you have an indexed view). Then, even without replication, any time you update a view-in this case that means running a Data Manipulation Language (DML) statement with the view as the target-you are really just updating data in the underlying table, not the view. A view is just a logical storage of a query statement with no physical storage attached to it (again, unless you are using indexed views).

Max Server Memory
Q I have a box running Windows Server® 2003 and SQL Server 2000, with 5GB of RAM. Let's say I use the /3GB switch to increase my user mode virtual address space, the /PAE switch to load the Physical Address Extension (PAE) version of the Windows kernel, and set Address Windowing Extensions (AWE) Enabled to 1 (and enable lock pages in memory). With AWE enabled, will the max server memory option configure just the size of the data cache or the size of all buffer caches (data, processes, sessions, and so on)? Since only the data cache can make use of AWE-mapped memory, if I configure the max server memory to 4GB, will the data cache use only 1GB (the portion mapped by AWE), or can it use this extra 1GB and continue to use or compete with all other memory consumers in the standard address space?
A Max server memory will always limit the entire buffer pool size; however, the only consumer that can make use of AWE-mapped memory is the data cache, nothing else.
So for your first question, even with AWE enabled, max server memory still limits the entire buffer pool, but non-data-cache consumers won't ever use any of the AWE-mapped memory.
For the second question, the data cache will use AWE-mapped memory plus any other memory in the buffer pool that SQL Server determines appropriate; it won't be limited to only AWE memory, it just happens to be the only consumer that can make use of AWE memory. If you're not sure what the /3GB switch does, see Raymond Chen's blog.

Profiling and Performance
Q I have SQL Server 2005 mirrored in production. When I start SQL Server Profiler on the database machine and write the trace data to a file, I see a drastic drop in performance. Why?
A The reason for the performance drop depends on where you're running Profiler. If you're running it interactively on the server machine, then the Profiler UI is consuming both memory and CPU on the server, which will impact performance.
If you're running Profiler interactively on a workstation, then you're moving the event information across the network. This impacts throughput. If this is the same network you're using for mirroring, you'll see an impact there as well. Also, if you're storing the profiler output on a network share, you're moving data across the network and adversely affecting performance.
Probably the best way to mitigate all of this is to run Profiler non-interactively on the server that is running the instance to be profiled and then pipe the output to a local file. You'll still be consuming server resources but this approach generally has the lowest impact. This works much better than the (in-memory) Profiler trace. File trace uses the system memory more efficiently; it has larger buffers and flushes them to disk more effectively. It also doesn't depend on external processes (like SQL Server Profiler).
Finally, trace data is written to a disk file while Profiler is still profiling. The trace file is shared so that other people can see real-time profile data remotely. If you're calling the trace file interactively, that means you've manually invoked Profiler and are viewing output on the screen. Traces can be created programmatically with no visual output, which is why you should run things non-interactively.
You can create a share on top of a local directory and other people can access the files found there-that's typically fine. As mentioned before, you don't want to send trace output to a file on a remote share, especially one that is accessed through the same network pipe used for mirroring.
You should also select just the minimum set of events needed for your investigation. For the trace file location, you should choose the fastest drive on your system (preferably different from the SQL Server database and transaction log drives). If you still experience significant performance degradation, split the events into two or more traces each targeting a different hard drive. Even if the traces target the same hard drive, you'll still benefit from a split due to the fact that each trace has its own set of buffers. See sp_trace_create and all of its relatives in SQL Server Books Online for more.

Clustering Issues
Q I am trying to install SQL Server on a cluster running Windows Server 2003. Every time I try this, I get an error stating "Setup failed to perform required operations on the cluster nodes." The sqlstp.log simply states:
Script file copied to '\\SERVER01\ADMIN$\SERVER01_MSSQLSERVER.iss' successfully.
Installing remote service (SERVER01)...
An error occured in the service create (SERVER01): 1069...
What could be happening?
A There are several possible reasons for this failure. Setup installs Windows NT® Service on all selected nodes to remotely manage the setup process on individual machines. Therefore, you have to be aware of a number of snags you may encounter.
First, your domain account users may have a group policy that denies "log on as service" permission. (Remember domain policy overrides your local machine policy.) Make sure you're using an account without these restrictions.
Second, the logged-on account of the machine you're executing setup from should have admin access on all nodes because of the following: the Master setup process requires remote registry access to all machines; setup copies cnvsvc.exe to the remote machine's Windows directory; or setup uses standard Windows APIs that use only the logged on account's permission to access remote machines. For these reasons you should be logged on as an admin on all modes by default.

Disaster Recovery Plan
Q I am considering whether to use database mirroring (async mode) or log shipping to implement my disaster recovery (DR) strategy for my SAP databases. My production and DR sites will have a 100Mb broadband connection that is not dedicated to the mirroring session. The connection will be shared with different mirroring sessions or even with other DR servers.
If there is a network issue preventing the log record from being sent to the mirror database, will there be a retry?
When the mirroring session is suspended, is there any retention period? And, besides the system views, is there any logging information I can use to monitor the mirroring traffic and the log records transmission?
A Let's start by answering the question: what is the retry logic of database mirroring? You can look at this in two ways: First, if there is a transient network issue, the mirroring session state is disconnected. There is a default network timeout value of 10 seconds, which means a log record can't be sent from the principal database to the mirror database. In such cases, the principal will continue running "exposed" and the transaction will commit on the client side. Once the network issue is resolved, the mirroring session will retry automatically without any user intervention. It will attempt to catch up using the log records; the partners will synchronize first and once they catch up, the state will be synchronized.
Second, if there is a redo issue, the mirroring session state is suspended. A redo issue means that the mirror database cannot commit the log records on its database. Redo issues are mainly caused by a physical file not being found or by insufficient disk space. In such cases, the principal will continue running exposed and so the transaction will commit on the client side. After you fix the redo issue manually on the mirror server, the mirror session requires intervention:
ALTER DATABASE <db> SET PARTNER RESUME;
Concerning retention periods, the answer is that regardless of whether the mirror session is disconnected or suspended, the log records will be kept until the session is restored and all records from the time the session was suspended until the time it was resumed are hardened on the mirror. Basically, while the mirroring session is disconnected or suspended, the log on the principal cannot be truncated, because that would break the log redo chain. This means that the principal's log will grow unbounded until the session is restored. Therefore, there is in fact no retention limit on the mirroring session; the only real constraint is the disk space the principal server has for storing the log since the log cannot be truncated.
Finally, there is no specific log file you can use to monitor mirroring. SQL Server 2005 provides a GUI tool called the Database Mirroring Monitor for this purpose, which lets system administrators view and update status and configure warning thresholds on several key performance metrics. From this, you can get an alert when mirroring is not performing well. The main performance concern with database mirroring is whether the log records are processed in a timely manner. For more information about monitoring database mirroring, you can take a look at the article Monitoring Mirroring Status at msdn2.microsoft.com/fr-fr/library/ms365781.aspx.

Thanks to the following Microsoft IT Pros for their technical expertise: Chad Boyd, Sandu Chirica, Alan Doby, Kaloian Manassiev, Luciano Moreira, Ivan Penkov, Sivagaminathan Rajarethinam, Deborah To, Patrick Woodward, Buck Woody, Stanley Yau, and Warren Young.


 The Cable Guy: The DHCPv6 Protocol
The Cable Guy
The DHCPv6 Protocol
Joseph Davies

The Dynamic Host Configuration Protocol (DHCP) was designed to take care of assigning IP addresses and other networking information to computers so they can communicate on the network automatically. With an IPv6 network, you don't actually need DHCP to configure addresses, but there can be good reasons to use it. DHCP for IPv6 (DHCPv6) can
provide stateful address configuration or stateless configuration settings to IPv6 hosts. IPv6 hosts can use several methods to configure addresses:
Stateless Address Autoconfiguration is used to configure both link-local addresses and additional non-link-local addresses by exchanging Router Solicitation and Router Advertisement messages with neighboring routers.
Stateful Address Autoconfiguration is used to configure non-link-local addresses through the use of a configuration protocol such as DHCP.
An IPv6 host performs stateless address autoconfiguration automatically and uses a configuration protocol such as DHCPv6 based on the following flags in the Router Advertisement message sent by a neighboring router:
Managed Address Configuration Flag, which is also known as the M flag. When set to 1, this flag instructs the host to use a configuration protocol to obtain stateful addresses.
Other Stateful Configuration Flag , which is also known as the O flag. When set to 1, this flag instructs the host to use a configuration protocol to obtain other configuration settings.
Combining the values of the M and O flags can yield the following:
Both M and O Flags are Set to 0. This combination corresponds to a network without a DHCPv6 infrastructure. Hosts use router advertisements for non-link-local addresses and other methods (such as manual configuration) to configure other settings.
Both M and O Flags are Set to 1. DHCPv6 is used for both addresses and other configuration settings. This combination is known as DHCPv6 stateful, in which DHCPv6 is assigning stateful addresses to IPv6 hosts.
The M Flag is Set to 0 and the O Flag is Set to 1. DHCPv6 is not used to assign addresses, only to assign other configuration settings. Neighboring routers are configured to advertise non-link-local address prefixes from which IPv6 hosts derive stateless addresses. This combination is known as DHCPv6 stateless: DHCPv6 is not assigning stateful addresses to IPv6 hosts, but stateless configuration settings.
The M Flag is Set to 1 and the O Flag is Set to 0. In this combination, DHCPv6 is used for address configuration but not for other settings. Because IPv6 hosts typically need to be configured with other settings, such as the IPv6 addresses of Domain Name System (DNS) servers, this is an unlikely combination.
Like DHCP for IPv4, the components of a DHCPv6 infrastructure consist of DHCPv6 clients that request configuration, DHCPv6 servers that provide configuration, and DHCPv6 relay agents that convey messages between clients and servers when clients are on subnets that do not have a DHCPv6 server.

DHCPv6 Messages
As with DHCP for IPv4, DHCPv6 uses User Datagram Protocol (UDP) messages. DHCPv6 clients listen for DHCP messages on UDP port 546. DHCPv6 servers and relay agents listen for DHCPv6 messages on UDP port 547. The structure for DHCPv6 messages is much simpler than for DHCP for IPv4, which had its origins in the BOOTP protocol to support diskless workstations. Figure 1 shows the structure of DHCPv6 messages sent between client and server.
Figure 1 DHCPv6 messages between client and server
Figure 1 DHCPv6 messages between client and server
The 1-byte Msg-Type field indicates the type of DHCPv6 message. The 3-byte Transaction-ID field is determined by a client and used to group the messages of a DHCPv6 message exchange together. Following the Transaction-ID field, DHCPv6 options are used to indicate client and server identification, addresses, and other configuration settings. For the list of defined DHCPv6 options, see RFC 3315, as referenced in the "DHCPv6 RFC Resources" sidebar.
DHCPv6 options are formatted with the type-length-value (TLV) format. Figure 2 shows the structure of DHCPv6 options.
The 2-byte Option-Code field indicates a specific option. The 2-byte Option-Len field indicates the length of the Option-Data field in bytes. The Option-Data field contains the data for the option.
There is a separate message structure for the messages exchanged between relay agents and servers to record additional information. Figure 3 shows the structure of these kinds of messages.
Figure 2 Structure of DHCPv6 options
Figure 2 Structure of DHCPv6 options
The 1-byte Hop-Count field indicates the number of relay agents that have received the message. A receiving relay agent can discard the message if it exceeds a configured maximum hop count. The 16-byte Link-Address field contains a non-link-local address that is assigned to an interface connected to the subnet on which the client is located. From the Link-Address field, the server can determine the correct address scope from which to assign an address. The 16-byte Peer-Address field contains the IPv6 address of the client that originally sent the message or the previous relay agent that relayed the message. Beyond the Peer-Address field are DHCPv6 options that include the Relay Message option, which contains the message being relayed and other options. The Relay Message option provides an encapsulation of the messages being exchanged between the client and the server.
There are no broadcast addresses defined for IPv6. Therefore, the use of the limited broadcast address for some DHCPv4 messages has been replaced with the use of the All_DHCP_Relay_Agents_and_Servers address of FF02::1:2 for DHCPv6. For example, a DHCPv6 client attempting to discover the location of the DHCPv6 server on the network sends a Solicit message from its link-local address to FF02::1:2. If there is a DHCPv6 server on the host's subnet, it receives the Solicit message and sends an appropriate reply. More typically, a DHCPv6 relay agent on the host's subnet receives the Solicit message and forwards it to a DHCPv6 server.
Figure 3 Structure of messages between relay and server
Figure 3 Structure of messages between relay and server

Stateful Message Exchange
A DHCPv6 stateful message exchange to obtain IPv6 addresses and configuration settings-when both M and O flags in a received router advertisement are set to 1-typically consists of the following messages:
  • A Solicit message sent by the client to locate the servers.
  • An Advertise message sent by a server to indicate that it can provide addresses and configuration settings.
  • A Request message sent by the client to request addresses and configuration settings from a specific server.
  • A Reply message sent by the requested server that contains addresses and configuration settings.
If there is a relay agent between the client and the server, the relay agent sends the server Relay-Forward messages containing the encapsulated Solicit and Request messages from the client. The server sends the relay agent Relay-Reply messages containing the encapsulated Advertise and Reply messages for the client. For a complete list of DHCPv6 messages, see Figure 4.
Figure 4 DHCPv6 messages (Close Figure 4)

DHCPv6 Message | Description | Equivalent DHCP for IPv4 Message
Solicit | Sent by a client to locate servers. | DHCPDiscover
Advertise | Sent by a server in response to a Solicit message to indicate availability. | DHCPOffer
Request | Sent by a client to request addresses or configuration settings from a specific server. | DHCPRequest
Confirm | Sent by a client to all servers to determine if a client's configuration is valid for the connected link. | DHCPRequest
Renew | Sent by a client to a specific server to extend the lifetime of assigned addresses and obtain updated configuration settings. | DHCPRequest
Rebind | Sent by a client to any server when a response to the Renew message is not received. | DHCPRequest
Reply | Sent by a server to a specific client in response to a Solicit, Request, Renew, Rebind, Information-Request, Confirm, Release, or Decline message. | DHCPAck
Release | Sent by a client to indicate that the client is no longer using an assigned address. | DHCPRelease
Decline | Sent by a client to a specific server to indicate that the assigned address is already in use. | DHCPDecline
Reconfigure | Sent by a server to a client to indicate that the server has new or updated configuration settings. The client then sends either a Renew or Information-Request message. | N/A
Information-Request | Sent by a client to request configuration settings (but not addresses). | DHCPInform
Relay-Forward | Sent by a relay agent to forward a message to a server. Relay-Forward contains a client message encapsulated as the DHCPv6 Relay-Message option. | N/A
Relay-Reply | Sent by a server to send a message to a client through a relay agent. Relay-Reply contains a server message encapsulated as the DHCPv6 Relay-Message option. | N/A

Stateless Message Exchange
A DHCPv6 stateless message exchange to obtain only configuration settings-when the M flag is set to 0 and the O flag is set to 1 in a received router advertisement-typically consists of the following messages: an Information-Request message sent by the DHCPv6 client to request configuration settings from a server and a Reply message sent by a server containing the requested config settings.
For an IPv6 network that has routers configured to assign stateless address prefixes to IPv6 hosts, the two-message DHCPv6 exchange can be used to assign DNS servers, DNS domain names, and other configuration settings that are not included in the router advertisement message.

DHCPv6 Support in Windows
Windows Vista™ and the next version of Windows Server®, code-named "Longhorn," include a DHCPv6 client. The DHCPv6 client attempts DHCPv6-based configuration depending on the values of the M and O flags in received router advertisement messages. Therefore, to use DHCPv6, you must configure DHCPv6 servers and relay agents to service each IPv6 subnet and then configure your IPv6 routers to set these two flags to their appropriate values. If there are multiple advertising routers for a given subnet, they should be configured to advertise the same stateless address prefixes and values of the M and O flags. IPv6 hosts running Windows® XP or Windows Server 2003 do not include a DHCPv6 client and therefore ignore the values of the M and O flags in received router advertisements.
You can configure an IPv6 router that is running Windows Vista or Windows Server "Longhorn" to set the M flag to 1 in router advertisements with the "netsh interface ipv6 set interface InterfaceName managedaddress=enabled" command. Similarly, you can set the O flag to 1 in router advertisements with the "netsh interface ipv6 set interface InterfaceName otherstateful=enabled" command.
The Beta 2 release of Windows Server "Longhorn" supports a DHCPv6 relay agent and DHCPv6 stateless configuration with the DHCP Server service. You can configure the DHCP Server service for DHCPv6 options to be distributed to all DHCPv6 clients in the two-message DHCPv6 message exchange previously described. Microsoft has plans to include support for DHCPv6 stateful operation, including the ability to create IPv6 address scopes in the DHCP snap-in, in the Beta 3 release of Windows Server "Longhorn.".
DHCPv6 RFC Resource
Selected RFCs relating to DHCPv6:
  • RFC 3315 Dynamic Host Configuration Protocol for IPv6 (DHCPv6)
  • RFC 2462 IPv6 Stateless Address Autoconfiguration
  • RFC 3633 IPv6 Prefix Options for DHCP version 6
  • RFC 3646 DNS Configuration Options for DHCPv6
  • RFC 3736 Stateless DHCP Service for IPv6

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


 Windows PowerShell: The Power of Variables
Windows Powershell
The Power of Variables
Don Jones

If you work with a Windows-based scripting language, such as VBScript or KiXtart, you're accustomed to variables being nothing more than a sort of storage mechanism for data. Windows PowerShell has variables, too, but they're much more powerful than the variables in older scripting languages. The Windows
PowerShell variables are actually mapped to underlying classes in the Microsoft® .NET Framework. And in the Framework, variables are objects, meaning they can store data and also manipulate it in many ways. In fact, the robust capabilities of variables in Windows PowerShell™ are the reason why the Windows PowerShell scripting language doesn't contain any intrinsic data manipulation functions. It doesn't need these functions since the variables themselves already provide the functionality.

Declaring Variables
Although the New-Variable cmdlet in Windows PowerShell does allow you to declare a variable and assign an initial value to it, you don't have to use the cmdlet. Instead, you can create a new variable on the fly simply by assigning a value to it:
$var = "Hello"

In Windows PowerShell, variable names always start with a dollar sign ($) and can contain a mix of letters, numbers, symbols, or even spaces (though if you use spaces, you need to enclose the variable in braces, such as ${My Variable} = "Hello"). This example created a new variable named $var and assigned it the initial value of "Hello". Because in this case the value is a string of characters, Windows PowerShell will use the String data type to store the value. In .NET Framework terms, that's the System.String class, which has perhaps the most built-in functionality of any variable type. If, say, I want to see an all-lowercase version of the value in $var, I can do this:
PS C:\> $var.ToLower()
hello
PS C:\>
The ToLower method is built into the System.String class, and it produces an all-lowercase representation of the string's value. It doesn't change the actual contents of the variable $var, though. To see a complete list of everything the System.String class is capable of doing, pipe a string variable to the Get-Member cmdlet, like this:
$var | get-member
Figure 1 shows the output, which includes a couple dozen methods used for manipulating strings. Nearly all the functionality provided by VBScript string-manipulation functions is provided instead by methods of the string variable in Windows PowerShell.
Figure 1 A look at the System.String class output
Figure 1 A look at the System.String class output

Many of the string variable's capabilities are more useful in an administrative context than the string functions in a language like VBScript. Suppose you've written a script that reads in Universal Naming Convention (UNC) paths from a file and does something with them. You'd want to verify that each path read in is a UNC before attempting to use the path. The StartsWith method lets you confirm that a string's value starts with the necessary backslash characters a UNC requires:
PS C:\> $path = "\\Server\Share"
PS C:\> $path.StartsWith("\\")
True
PS C:\>
Because StartsWith returns a True or False value, you can use it in logical constructs:
if ($path.StartsWith("\\")) {
 # code goes here
}
Windows PowerShell even provides a form of autocompletion for variables' methods, reducing the amount of typing you have to do. If $var contains a string, you can type
$var.
and then press Tab, which pops up the first method name for the $var variable. Pressing Tab again moves to the next method, and pressing Shift+Tab brings up the prior method. In this fashion, you can cycle through all the available methods until you find the one you want.

Variable Confusion
So far, my examples have allowed Windows PowerShell to determine the data type of my variables. Assigning a string to a variable essentially forces the variable to be of the System.String class. Assigning a number to a variable, on the other hand, usually results in the variable becoming an Integer (or, more specifically, an Int32, which can store a specific range of values). Consider this, for example:
PS C:\> $int = 5
PS C:\> $int | get-member

  TypeName: System.Int32
This truncated output shows that Windows PowerShell is treating $int as an Int32, which has its own set of methods and properties. And, in fact, its set of methods and properties are far fewer than those of the String type.
$int was made an Int32 because the value wasn't enclosed in quotes and because the value was composed solely of digits. Had it been in quotes, it would have been interpreted as a System.String.
Allowing Windows PowerShell to decide what data type to use won't always produce the results you want. Suppose you're reading values out of a file and you always want the values to be treated as strings. Some of the values, however, might contain only digits, raising the possibility that Windows PowerShell would treat them as Int32 or another numeric type. That may cause problems for your script. If Windows PowerShell doesn't recognize the value as a string, then all the methods of the System.String class aren't available (and your script might rely on one of these unavailable methods).
To work around this, you can force Windows PowerShell to treat a variable as a certain type by declaring that type when you first use the variable. Here's an example:
[string]$var = 5
Normally, $var would have been an Int32, but here I've forced Windows PowerShell to make $var a String, ensuring I can use all the methods associated with the System.String class. The variable type declaration also provides a handy kind of self-documentation to the code, since it's now obvious what type of data is expected to go into $var. In fact, I've gotten into the habit of always declaring a specific type for all of my variables, completely taking the decision away from Windows PowerShell. This makes the behavior of my scripts more predictable and in several instances has saved me quite a bit of debugging time.
As you'd expect, forcibly declaring variables does have repercussions, though they're not necessarily bad. Take this example, which doesn't declare the variable's type:
PS C:\> $me = 5
PS C:\> $me = "Don"
The $me variable was initially an Int32, but Windows PowerShell changed it to a String when the value "Don" was added. Windows PowerShell can change the type of a variable as needed, provided the variable hasn't already been explicitly set to a specific type.
In this example, I forced $me to be an Int32 using the [int] type name:
PS C:\> [int]$me = 5
PS C:\> $me = "Don"
Cannot convert value "Don" to type
"System.Int32". Error: "Input string
was not in a correct format."
At line:1 char:4
+ $me <<<< = "Don"
Then, when I tried to assign a string value to it, an error message was displayed. Since I'd explicitly forced $me to be an Int32, Windows PowerShell expected to convert the string "Don" into an integer value. It was unable to do this, nor was it able to change the type of $me to String.

So Many Types
There are many types available within Windows PowerShell. In fact, you can use any .NET Framework type (and the available types number in the hundreds). However, Windows PowerShell defines several shortcuts for common types of data. Figure 2 is just a partial list, showing 10 of the most commonly used type shortcuts. For a complete list, refer to the Windows PowerShell documentation.
Figure 2 Common type shortcuts (Close Figure 2)

Shortcut | Data Type
[datetime] | Date or time
[string] | String of characters
[char] | Single character
[double] | Double-precision floating number
[single] | Single-precision floating number
[int] | 32-bit integer
[wmi] | Windows Management Instrumentation (WMI) instance or collection
[adsi] | Active Directory Services object
[wmiclass] | WMI class
[Boolean] | True or False value
You can also declare a variable's type using the full .NET Framework class name, like so:
[System.Int32]$int = 5
This technique allows you to use types that are in the .NET Framework, but that don't have a specific shortcut in Windows PowerShell.

Extending Types
Perhaps the coolest thing about Windows PowerShell is its ability to extend the capabilities of these variable types. In the Windows PowerShell installation folder (normally in %systemroot\system32\windowspowershell\v1.0, though you'll find that the path is somewhat different on 64-bit systems), you'll find a file called types.ps1xml. You can edit this file in Notepad or in an XML editor, such as PrimalScript. By default, the System.String class isn't listed, although many other types are. However, I can add a new capability to the System.String type by adding this to the types.ps1xml file.
After making the change, I need to close and reopen Windows PowerShell. I also need to make sure that the local script execution policy allows unsigned scripts to execute since I haven't signed types.ps1xml:
Set-executionpolicy remotesigned
Now, once I restart Windows PowerShell, I can use the new functionality, like this:
PS C:\> [string]$comp = "localhost"
PS C:\> $comp.canping
True
PS C:\>
As you can see, I've added a CanPing property to the System.String class. This returns True or False to indicate whether the local computer is able to ping the address contained in the string. In Figure 3, you'll notice that the WMI query utilizes a special variable, $this. The $this variable represents the current value contained in the string and is how the content of the string variable is passed into the WMI query.
Figure 3 Extend System.String type (Close Figure 3)
<Type>
  <Name>System.String</Name>
  <Members>
    <ScriptProperty>
      <Name>CanPing</Name>
      <GetScriptBlock>
      $wmi = get-wmiobject -query "SELECT *
FROM Win32_PingStatus WHERE Address = '$this'"
      if ($wmi.StatusCode -eq 0) {
        $true
      } else {
        $false
      }
      </GetScriptBlock>
    </ScriptProperty>
  </Members>
</Type>

Let a Variable Do the Work
Windows PowerShell provides flexible, feature-filled variable types and an equally flexible system for extending the functionality of types. These capabilities make it very easy to build a great deal of power into your scripts. In fact, variables can become a major building block in complex scripts, in many cases taking on the advanced capabilities you'd normally find in a more complex function.
Learn More Online
Join me for this series of webcasts, held on the second Tuesday of each month, where we'll continue to dive into the rich scripting functionality provided by Windows PowerShell. Visit microsoft.com/events/series/donjonesscripting.mspx to sign up today.

February 20, 2007 Windows PowerShell: The Scripting Crash Course
Join this webcast to learn about the variables, language constructs, scripts, and functions in Windows PowerShell. In this one-hour crash course, we demonstrate how the simple but effective scripting language in Windows PowerShell makes Windows® administrative automation faster. We also explore several third-party tools that make Windows PowerShell scripting as painless as possible.

March 20, 2007 Windows PowerShell: Functions, Filters, and Efficiency
Learn to create effective, modularized code in Windows PowerShell functions and filters. We explore the robust scoping rules in Windows PowerShell that enable you to fully encapsulate functions and filters and explain how you can easily reuse these functions and filters across projects. We also demonstrate how you can add your own custom functions into the global scope in Windows PowerShell, which makes it easy to assemble a library of useful utility functions at your fingertips.

April 17, 2007 Windows PowerShell and Windows Management Instrumentation
Windows PowerShell can access all the power and capability of Windows Management Instrumentation, or WMI. We describe not only how to use Windows PowerShell to access WMI, but also how to pass WMI objects and collections through the Windows PowerShell pipeline. We explore how to utilize WMI properties and methods in Windows PowerShell scripts and illustrate the underlying security and configuration features in WMI.

May 22, 2007 Windows PowerShell: Converting from VBScript
Want to convert your scripts—or even just your skills—from VBScript to Windows PowerShell? Join this webcast to learn how. We explore how Windows PowerShell includes all of the major constructs and features of VBScript, making it easy to translate your VBScript skills to this new administrative environment. We illustrate how to convert tools in VBScript into the native scripting language in Windows PowerShell. We also examine the unique structure in Windows PowerShell and show you how to start using the Windows PowerShell scripting language so that your efforts become more efficient and effective.

June 19, 2007 Windows PowerShell: Under-the-Hood Extensions
In this webcast, we explore how Windows PowerShell makes use of the powerful and flexible .NET Framework to handle data, giving you hundreds of built-in functions for managing strings, dates, and other types of data. But did you know that you can extend these functions using Windows PowerShell scripts? We show you how to make string variables that contain not only a computer name but can also tell you if that computer is up and running. Learn how to create date and time variables that can automatically format their data without the use of external functions. Join this session to see how to build all kinds of new functionality into Windows PowerShell in just minutes, making Windows administration faster and easier.

Don Jones is the Director of Projects and Services for SAPIEN Technologies, and coauthor of Windows PowerShell: TFM (SAPIEN Press). Contact Don at www.ScriptingAnswers.com.


 Hey, Scripting Guy!: Answering Your OU Questions...for Just 5 Cents
Hey, Scripting Guy!
Answering Your OU Questions...for Just 5 Cents
The Microsoft Scripting Guys
Download the code for this article: HeyScriptingGuy2007_03.exe (151KB)

Q How can I display a dialog box that enables me to select an OU from Active Directory?

YOU KNOW, if we Scripting Guys had a nickel for every time we've been asked that question, we'd have -- well, actually, it would only come out to $37.15, wouldn't it? OK, but if we had a nickel for every person who wanted to ask us that question, but just never got around to actually asking, we'd have -- well, the amount doesn't matter. After all, we didn't become Scripting Guys for the money. We became Scripting Guys for the satisfaction that comes from helping people perform their system administration tasks quicker and easier.
Note. OK, so technically we did become Scripting Guys for the money. But then our manager told us, "You know, it is possible for you guys to do well and make a lot of money, but only if you're willing to work really hard and produce nothing but high-quality stuff." That was the moment when we decided money wasn't everything.
No matter. The point is, a lot of people would like to know how to display a dialog box that would enable them to select an OU and then, by extension, connect to that OU using a script. "You've shown us how to display a dialog box that lets us choose folders," says one recent e-mail, "and you've shown us how to display a dialog box that lets us choose files. Why haven't you ever shown us how to display a dialog box that lets us select an OU from Active Directory®?"
Well, to begin with, yes, we really did show people how to display a dialog box that enables them to select a folder and you can find an example of that right here. We also showed everyone how to display a dialog box that allows them to select files; check out microsoft.com/technet/scriptcenter/resources/qanda/jan05/hey0128.mspx if you don't believe us. Both of those articles were well received, and we know that people are employing those techniques in their scripts. So then why haven't we shown people how to display a dialog box that enables them to select OUs from Active Directory? Is this some sort of Microsoft conspiracy or something?
Of course not. (Say, wait a minute: that's exactly what someone involved in a conspiracy would say, isn't it?) The truth is, there's actually a good reason why we haven't shown you how to display a dialog box that enables you to select OUs from Active Directory: no such dialog box exists. As they say in baseball, you can't hit what you can't see. And as we say in scripting, you can't display what doesn't exist.
So, we guess that's it for this month's column. See you next time.
No such luck! According to the editors of TechNet Magazine, that isn't it for this month. "So what if a dialog box like that doesn't exist?" they said. "You're the Scripting Guys, for Pete's sake. Can't you just create a dialog box to take care of this? We thought you guys could do anything when it comes to scripting."
Needless to say, the editors at TechNet Magazine have been grossly misinformed. Do anything when it comes to scripting? Not exactly. The truth is, we Scripting Guys know how to do only one thing when it comes to scripting.
Fortunately, though, that one thing is creating a dialog box that enables you to select an OU from Active Directory. Take a look at Figure 1.
Yes, this is kind of a long script, isn't it? But you know what they say: you can't make an omelet without breaking a few eggs. Likewise, you can't build a custom dialog box without also breaking a few eggs (we got hungry halfway through this column and had to stop for a snack). Now that our appetites are sated, we'll see if we can explain how this all works.
What we're doing with the script is using the Internet Explorer® object model to build a pseudo-dialog box. We're going to create an instance of Internet Explorer, grab a collection of OUs from Active Directory, then display those OUs in a listbox in an Internet Explorer window. We'll wait until the user selects an OU, then grab the ADsPath of the selected item, dismiss the "dialog box," and then go on from there.
Note. Is this going to be the fanciest and prettiest dialog ever created? Yes, if by "fanciest and prettiest" you mean a bare-bones affair that doesn't even have an OK or a Cancel button. But, in our defense, the idea here is to simply give you the basic framework. If you want to pretty up or modify the dialog box, you're on your own.
(Boy, if we had a nickel for every time we said "Sorry, you're on your own," we really would be rich. Send your donations to The Scripting Guys c/o TechNet Magazine.)
In order to fit the explanation of our technique into the space allotted to us, we'll have to skim over a few things. For example, all we'll say about the following block of code is that it creates an instance of Internet Explorer that is 350 pixels by 400 pixels, and, in doing so, hides such things as the address bar and the status bar:
Set objExplorer = CreateObject( _
    "InternetExplorer.Application")

objExplorer.Navigate "about:blank"
objExplorer.ToolBar = 0
objExplorer.StatusBar = 0
objExplorer.Width= 350
objExplorer.Height = 400
objExplorer.Left = 400
objExplorer.Top = 400
objExplorer.Visible = 1
Of course, all this does is give us a blank Internet Explorer window. In order to get the list of OUs in our domain, we need to do a search of Active Directory. We can't explain the ins and outs of Active Directory searching today; for information on that, take a look at our two-part series in the Script Center (microsoft.com/technet/scriptcenter/resources/tales/sg0405.mspx). What we can do is tell you that this is the code we use to retrieve the Name and ADsPath for each OU in the domain fabrikam.com:
objCommand.CommandText = _
    "SELECT Name, ADsPath FROM 'LDAP://DC=fabrikam,DC=com' WHERE objectCategory=
    'organizationalUnit' ORDER BY Name"
Oh, and just to make things easier for you, we've also sorted the OUs alphabetically by name. That's what the ORDER BY Name clause is for. (No problem; after all, we live to serve.)
When we call the Execute method, we get back a recordset consisting of all the OUs found in fabrikam.com. That means our next step is to display each of those OUs in a listbox.
So how do we do that? Well, first we need to construct the entire listbox in memory. For starters, that means using this line of code to store the <SELECT> tag in a variable named strHTML:
strHTML = "<select size = '20' name='OUList' style='width:300px'>"
What are we doing with this line of code? Well, we're simply constructing a standard HTML listbox (something you do using the <SELECT> tag). Along the way, we give the listbox the Name OUList and configure it to display 20 items at a time and to be 300 pixels (300px) wide. If you know anything about HTML, the previous code should look pretty familiar.
Next, we need to add some items (that is, some OUs) to the listbox. By amazing coincidence, our recordset happens to consist of information about each of the OUs in our domain. Because of that, we can set up a Do Until loop that loops through the entire recordset, grabs the information, then adds each OU to the listbox. Within that loop, we use the following code (again, standard HTML for adding items to a listbox) to add each OU, configuring the item so that the OU Name is displayed in the list and, if you click on that OU in the listbox, the ADsPath is passed to the script:
strHTML = strHTML & "<option value= " & _
    Chr(34) & objRecordSet.Fields
    ("AdsPath").Value & Chr(34)
strHTML = strHTML & ">" & objRecordSet.
    Fields("Name").Value
Why pass the ADsPath to the script? That's easy. Suppose we passed the OU Name instead. If the Name is, say, Finance, it's going to be difficult to bind to that OU; after all, "Finance" by itself isn't a valid ADSI binding string. However, if we get back the ADsPath (LDAP://ou=Finance,dc=fabrikam,dc=com), then binding is a piece of cake. That's because the ADsPath is exactly what we need to locate and connect to an object in Active Directory.
Incidentally, we're displaying the Name in the listbox because we're assuming your OUs have unique names. That might not be the case; for example, the OUs North America\Research and Europe\Research have the same name (Research). If your OUs have duplicate names, you might want to use a different property-say, ADsPath or distinguishedName-in the listbox instead. That's something you'll have to decide for yourself. (Ka-ching! There's another nickel!)
By the way, notice that we're still constructing the listbox in memory: each item added to the box is being added to the variable strHTML. That's all part of the plan: we save the entire code for the listbox to the variable strHTML, then use the value of that variable to actually add the listbox to our instance of Internet Explorer.
After all the OUs have been added, we use this line of code to signify the end of the listbox:
strHTML = strHTML & "</select>"
At this point, strHTML contains all the HTML tagging required to create a listbox, and-best of all-a listbox in which each item represents an OU in Active Directory. That means we can now assign the value of strHTML to the InnerHTML property of our Internet Explorer document:
objExplorer.Document.Body.InnerHTML = strHTML
In turn, that gives us a listbox that displays all the OUs in a domain.
Wait, wait, don't go; we aren't quite finished yet. For example, we still need some code that enables us to pause our script until the user has selected an OU in the dialog box; without it, the listbox would be displayed but the script would continue to run, regardless of whether the user makes a selection. Yikes! To keep that scenario from happening, we use this block of code:
Do While objExplorer.Document.Body.All.OUList.Value = ""
    Wscript.Sleep 300
Loop
What we're doing here is repeatedly checking the Value of our listbox (which, as you might recall, we named OUList, the same name one of the Scripting Guys gave his daughter). If the Value is an empty string (meaning no item has been selected), we simply pause the script for 300 milliseconds, then loop around, and check again. This continues: a) forever; b) until the user selects an item in the listbox; or c) until the user closes the Internet Explorer window.
Let's assume that the user does select an OU in the listbox. In that case, we take the Value (which, as you recall, is the ADsPath of the OU) and assign it to a variable named strTargetOU:
strTargetOU = objExplorer.Document.Body.All.OUList.Value
We then use the Quit method to dismiss our instance of Internet Explorer as shown below:
objExplorer.Quit
At this point, we're almost finished. We first check to see if strTargetOU is equal to an empty string:
If strTargetOU = "" Then
    Wscript.Quit
End If
If it is, that means the user closed Internet Explorer without selecting an OU; in turn, we use the Wscript.Quit method to terminate the script. (We're making the assumption that if the user didn't pick an OU, then he or she isn't really interested in running the script.) If strTargetOU is not equal to an empty string, however, then we echo back the value of the variable:
Wscript.Echo strTargetOU
Of course, in a real script you'd probably go ahead and bind to that OU in Active Directory instead of simply echoing back the ADsPath. But you know what they say -- or at least what we say: for that you're on your own.
As we mentioned, and as you can see in Figure 2, it's not the fanciest dialog box ever created, but it works, and it's way easier than having to type in an ADsPath (assuming you even know the ADsPath). Plus, it gives you the chance to customize the code-and the dialog box-to fit your needs.
Figure 2 A simple script dialog
Figure 2 A simple script dialog

Now, if only we had a nickel for every column we've ever written about displaying a dialog box that enables you to select an OU from Active Directory. After all, that would give us 5 cents more than TechNet Magazine usually pays us.
Mind you, we're not complaining.

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.


 Exchange Queue &amp; A: Transaction Logs, Cmdlets, Remote Management, and More
Exchange Queue & A
Transaction Logs, Cmdlets, Remote Management, and More
KC Lemson and Nino Bilic

Q Why did you guys lower the transaction log size to 1MB in Microsoft® Exchange Server 2007?
A Well, in Exchange 2007 we have this cool thing called log shipping, which is the technology behind a couple of new features called local continuous replication (LCR) and cluster continuous replication (CCR). When this is enabled on a Storage Group (SG) level, the SG transaction logs get copied from the running production SG to the copy SG. The logs on the copy SG are replayed into the database to keep it up to date. This is obviously a simplified explanation of what is going on, but you get the picture.
The catch is that transaction logs can't be sent to the copy SG unless the log is closed on the production SG. In other words, the e00 log (the current log in the first storage group) can't be sent until it is full, closed, and renamed. Additionally, there are settings to control how many logs behind you can be in this process, as there is a trade-off between availability and performance. (These settings are available by running the Set-MailboxServer cmdlet in the Exchange Management Shell. The help file provides details about the exact parameters needed.)
To make a long story short, we decreased the transaction log size to minimize the amount of data that can be out of sync between two copies of the Storage Group. This comes into play when, let's say, the production database hard drive crashes and you then need to take action to start the SG from the copy. Smaller transaction logs will give you a better chance of less data loss.
This is not to say that we can guarantee you will lose only 1MB of data. Seeing as though these are transaction logs, it is possible that the lost 1MB log file could contain, for instance, a COMMIT transaction for 5MB of data. In that case, you would be missing those 5MBs of data on the copy-even though you might have received five of the logs with data itself, the last log with a little bit of data and that needed COMMIT transaction is essential.
Ultimately, it is up to you to gauge whether this is an acceptable risk given your business needs. A small amount of data loss with a recovery time in only minutes might be perfectly acceptable when compared to the alternative of hours of restore work. We'll be sure to cover more on this subject in the future!

Q Now that the transaction logs are shorter, will we "run out" of transaction logs faster?
A When talking about Exchange, "running out" refers to transaction log names. In Exchange Server 2003, transaction logs are named like this:
E nn fffff.log
The nn is the prefix that changes from SG to SG. Since nn will remain the same during the life of the SG, the number of uniquely named transaction logs that can be created for the storage group is essentially the "fffff" value, which is roughly one million logs (note to self: put pinky to corner of mouth and laugh maniacally). Some of our customers have run into this limit on busy mail servers.
Starting with Exchange Server 2000, we provided a hotfix to display an event in the application log when this limit is approached. (See support.microsoft.com/kb/896001 for more information on this.)
OK, with that out of the way, what happens in Exchange 2007? We just said that the transaction logs went from 5MB (the size in previous versions of Exchange) to 1MB (the size in Exchange 2007). Does that mean it takes just one-fifth of the data to run out of unique transaction log names?
The answer is: no. In Exchange 2007, the name of the transaction log is in this format:
E nn ffffffff.log
Notice that we have "ffffffff" logs to work with, as opposed to just "fffff" in previous versions of Exchange. Because of the internal Extensible Storage Engine (ESE) math, Exchange 2007 cannot create all "ffffffff" logs. Instead it creates "7fffffec" logs, which is still a pretty huge number (convert this from hex to decimal if you don't believe us!)
What does this mean in real life? In real life, it means that with Exchange 2007 you can create about 2048 times more logs than in previous versions of Exchange. Now let's factor in logs being one-fifth the size:
2048 / 5 = 409.6
As you can see, the new log-naming scheme can handle 409 times more data than the old one! And remember, that is per SG, not per server, because each storage group will have a different log file prefix (such as e00, e01, and so on). Did we mention that you can create 50 SGs per server on the Enterprise version of Exchange 2007? We don't think there is too much chance that someone will run out of transaction logs anytime soon!

Q How can I manage my Exchange 2007 servers remotely?
A There are a few options for managing Exchange 2007 servers remotely. First off, you can install the Exchange 2007 management tools onto a separate machine from your Exchange server, as long as that machine is running either the 32-bit or 64-bit version of Windows Server® 2003 Service Pack 1 (SP1) or later, Windows Server 2003 R2, or Windows XP SP2 or later. Note that installing any of the server roles (client access, mailbox, edge, and so on) on 32-bit hardware is not supported in production environments, but is supported for installing the management tools on a 32-bit machine. Also note that as of the initial release of Exchange Server 2007, the management tools are not supported on Windows Vista™.
In some organizations, the IT department creates a single server to be the management server, installing all the necessary management tools for various products on this server. Then the administrators who need to use those tools access the management server via Terminal Server so they can perform remote administration. In other settings, users install the management tools on their own desktops.
From the console or shell you can perform operations on the servers in your organization. In the console, the servers are visible so you can perform operations on any of them remotely. In the shell, many of the tasks support a filtering flag like -server if you want to scope an operation to a single server. There are a few exceptions, however, for commands that must be run locally, such as the Get-NetworkConnectionInfo command.

Q In your last column, you told us how much RAM is recommended for an Exchange 2007 server. What about the page file size?
A In order to prevent potential out-of-virtual-memory errors on Exchange 2007 servers, we recommend the following guidelines for determining the size of your system page file.
If the server has less than 8GB of RAM, then the minimum size of the page file should be the amount of RAM plus 10MB. So if the server has 4GB of RAM, the minimum size would be 4GB plus 10MB. The recommended size of the page file, however, is the amount of RAM multiplied by 1.5. So for the 4GB server, this would be 6GB.
Furthermore, the minimum size for the page file is calculated the same for a server with more than 8GB of RAM-that is, the amount of RAM plus 10MB. However, the recommended size is determined differently. Basically, on a server with more than 8GB of RAM, you should only go to 1.5 times the amount of RAM if you get out-of-virtual-memory errors with smaller page files.
Note that the errors mentioned in this case have nothing to do with virtual memory fragmentation (a problem with Exchange 2000 and Exchange 2003 servers). These are actual pop-up errors logged by the system that say the page file size is being increased or the page file is too small.

Q I would like to learn how to use the Exchange Management Shell, but I have never used a command line to administer my systems before. Where should I start?
A First, let's start with some basics. Each cmdlet in the Exchange Management Shell is comprised of a single verb and a single noun. There are only a handful of verbs that the Exchange Management Shell uses: "Get" is used to retrieve properties of objects, "Set" to change properties on an object, "New" to create new objects, and so on. The nouns of the Exchange Management Shell include many concepts that all Exchange administrators are already familiar with (for example, Mailbox, Contact, Queue, and OWAVirtualDirectory). There are also concepts new to Exchange Server 2007 (such as TransportRule, StorageGroupCopy, and UMAutoAttendant). So in practice, a basic cmdlet will look something like New-Mailbox (which will create a mailbox) or Get-Mailbox (which will retrieve a list of mailboxes in the organization).
Most cmdlets, however, will need more specifics about what exactly you're trying to accomplish. If you want to change a property on a mailbox, for example, you'll need to specify which mailbox and which property. This is done via parameters. If New-Sandwich were a cmdlet, then the parameters might be -NumberOfTomatoSlices 4, -Bread Wheat, and a variety of other similar options that specify in more detail what form you want the sandwich to take (-FlufferNutter $true-ahhhh yum).
Once you understand the verb-noun split, you can use the command-line exposure in the Exchange Management Console (EMC) wizard to familiarize yourself with which cmdlets perform which operations. In the EMC, run through the wizard to create a new user. At the end of the wizard, after the operation completes, you'll see a copy of the command that the wizard ran under the hood (see Figure 1). Pressing Ctrl+C will copy the contents of that dialog so you can paste the cmdlet into Notepad and put together your own simple batch files.
Figure 1 Command-line exposure for creating a new user
Figure 1 Command-line exposure for creating a new user
If you want to practice using cmdlets (but don't want to cause any damage), a nice trick is to put -WhatIf at the end of the command. When you do this, the system will tell you what it would do by running the command without actually making any changes to the system.
Once you're familiar with the basic cmdlets like *-Mailbox, you will probably want to try out some others. To find more cmdlets, use the Get-ExCommand cmdlet. This will display a list of all the Exchange management cmdlets. (You must do this while running the Exchange Management Shell. Just opening the Microsoft Command Shell or Windows PowerShell™ will not let you perform this or any other Exchange management tasks.) The list is huge, so use wildcard matching to find what you're looking for. For example, Get-ExCommand *database* will return a list of all Exchange cmdlets that have "database" in the name-such as Dismount-Database, Move-DatabasePath, and New-MailboxDatabase.
If you think you know part of the noun you're looking for, you can cycle through the options. For example, you can type Get-Mailbox and then press the Tab key to cycle through all cmdlets with mailbox in the noun of the task name. You can even combine wildcard matching and tab completion. For instance, you can type Get-*Mailbox* and tab through the possible options.
Another simple trick is to use prompting. Each cmdlet has a set of parameters that are required by default-the basic parameters necessary to perform the operation. "Get" cmdlets, such as Get-Mailbox, don't require any parameters. By default, Get-Mailbox will return a list of all mailboxes in the entire organization. (The list will max out at 1000 by default. To change this maximum, you can type Get-Mailbox -ResultSize 2000, for example.) Cmdlets that create or modify objects (like Set or New) will prompt you for the necessary information as to which object you want to create or modify. If you run New-Mailbox, for example, the shell will prompt you one by one for the password, database, name, and so on.
To find out more about the possible parameters on a given cmdlet, use the help system: Help Move-DatabasePath. (You can also just type Move-DatabasePath, Move-DatabasePath -?, and so on-we try to have the bases covered.) The help will give you a description of the cmdlet and a list of the parameters along with the purpose of each. Help can also be accessed with the Get-Help cmdlet. (Doesn't it feel good to just type Get-Help? Don't you wish some people you know could run that cmdlet in the real world?)
Another way to discover possible parameters on a cmdlet is to use tab completion on the parameter names. Type Get-Mailbox -S and press Tab, and you'll cycle through the possible parameters that start with S.
And then, of course, there's the extensive help system. If you don't find what you're looking for offline, don't forget to check the regularly updated help content at go.microsoft.com/fwlink/?linkid=79421 and the quick reference guide at go.microsoft.com/fwlink/?linkid=64647.
All of this will get you started with the Exchange Management Console, but this information barely scratches the surface of the command line's power and flexibility. We'll cover more in future columns.

Q In the Exchange Server 2003 ESM (Exchange System Manager), we could easily see the mailbox size of all users. That feature seems to be gone in Exchange 2007. Is there a way to do this in Exchange 2007?
A In the new Exchange 2007 console, you can get the size of a mailbox on the general property page of the mailbox itself. If you want to see this for multiple users, you have to use the Exchange Management Shell.
If you run Get-MailboxStatistics, you get the data across an entire server (not just a specific database). By default, the size is not shown-you have to run the cmdlet with Format-List to get the size:
get-mailboxstatistics | format-list
It might be even better to choose what you want to see and pipe it to a table. In this example, we get the display name of the mailbox, the total item size, the total deleted item size, and the database the mailbox is on:
Get-MailboxStatistics | Select-Object
displayname,totalitemsize,
totaldeleteditemsize,databasename
| Format-Table
Depending on the information you are looking for, you can use different formatting for the output. In this example, Format-Table happens to work better than Format-List

KC Lemson is a Lead Program Manager on the Exchange Server team. Her ATM PIN number is 0000—don’t spend it all in one place.
Nino Bilic, a Technical Lead, spends his free time collecting $1,000 checks from Bill in thanks for forwarding on his e-mail.


 Security Watch: Deploying EFS: Part 2
Security Watch
Deploying EFS: Part 2
John Morello

You can think of any Encrypting File System (EFS) deployment as having essentially two parts: the back-end design portion focusing on certificate management and recovery agents, and the user-facing EFS deployment portion. Last month I covered the back-end portion and discussed options for data and key recovery
as well as options for provisioning clients with certificates. Here, I'll focus on the aspects of the deployment that your end users will see, such as enhancements to Windows® Explorer, choosing what file system locations to encrypt, and how to manage encryption options with Group Policy.

Determining if EFS Is Already in Use
Once you've decided to deploy EFS, the first step is to determine the current state of EFS usage within your organization. Recall that EFS can be used in standalone mode, in which the end user is solely responsible for the creation and backup of his encryption keys. You may have advanced users in your organization who have already used EFS in this fashion, so it's important to determine who these users are in order to ensure a smooth transition to a more managed deployment.
The first time EFS is employed by any given user on a computer, Windows creates the following registry entry in HKEY_CURRENT_USER:
HKCU\Software\Microsoft\Windows NT\CurrentVersion\EFS\CurrentKeys\CertificateHash
Microsoft® Systems Management Server (SMS), Active Directory logon scripts, or other tools can be used to check for the presence of this entry on machines across your enterprise. If a user has this registry value present, he has used EFS at some point. However, this value does not necessarily indicate that someone is currently using EFS or that any of his data is encrypted. Thus, when a machine is found with this registry key, it should be more closely examined to determine if any files are currently encrypted.
Using cipher.exe and passing the /U and /N switches will instruct cipher to report the encryption state of files and directories on a computer. For example, if you wanted to determine if any data on a system was encrypted, you could run the following command:
cipher /U /N
The registry check and cipher report could be combined into a single script to check for the presence of the registry value and, if present, to generate a report of currently encrypted files on the computer. Once these reports are generated, you'll know which users have which files encrypted and will be ready to migrate them to a centrally managed design.
To control the application of Group Policy during the deployment, it's useful to create two security groups in your domain to separate those computers where EFS is in use from those where it is not. For example, "SG-ComputersUsingEFS" would contain the computer accounts of those computers where EFS is already in use, while "SG-ComputersNotUsingEFS" would contain all other machines where EFS is not currently in use.

Controlling EFS Usage with Group Policy
Once you know who is and is not using EFS, the next step in your client deployment is to disable standalone EFS usage for anyone not currently using it. This is an important step in the deployment process because it's easier to enable and configure EFS properly the first time than it is to migrate users who are using self-signed certificates. You should only perform this step after determining which users are already using EFS because by disabling EFS through Group Policy, users will not be able to access any currently encrypted files. You can find this policy in the Group Policy Object Editor under Compu-ter Configuration\Windows Settings\Security Settings\Public Key Policies\Encrypting File System.
As mentioned earlier, this value should only be enabled on computers where EFS is not currently in use, so as not to disrupt the work of anyone who is already using EFS. Thus, in my example, the access control list (ACL) on the Group Policy Object (GPO) that disables EFS allows only the "SG-ComputersNotUsingEFS" group to apply the Group Policy.

To Encrypt or Not to Encrypt
Once you've determined what systems are currently using EFS and you've disabled EFS on all others until your managed deployment, the next step is to determine what will be encrypted in that managed deployment. Depending on how your client systems are managed, this process can range from simplistic to quite intricate.
The first thing to consider when thinking about what files to encrypt is whether or not your users are administrators on their systems. If your users are local administrators, you're really only able to educate and encourage them to encrypt sensitive data, but you're not able to truly control it in a managed way. The reason for this is simple: a local administrator can create files and directories anywhere on the file system. Thus, while you may encrypt the common locations, such as My Documents, if the user creates a new directory in another location and saves sensitive information there, it is quite difficult to ensure that this data is also protected. In addition to all of the other security benefits achieved when end users are not local administrators, this also makes EFS far more manageable and secure.
The next critical piece of information to keep in mind about EFS is that it is per-user encryption. In other words, anything that is encrypted by a given user can only be decrypted by that same user and no other, including the SYSTEM account (the exception, of course, being the Data Recovery Agent or DRA.) This means that if an executable or DLL is encrypted with a user's key and another principal on the system tries to access it, the other principal will receive an access denied error. Thus, in general you should not encrypt files or directories that include executable code that might need to be accessed by multiple principals on a system. Windows will prevent encryption of files in %SystemRoot% or files bearing the SYSTEM attribute, but it's not uncommon for software vendors to install binaries into %ProgramFiles% for services that may be run by the SYSTEM account. For example, this is a common approach for laptop vendors to use when installing power and device management software that runs as a service.
With these two facts in mind, what are the best practices for EFS encryption targets? The answer goes back to how your users' systems are normally configured. In a well-managed environment with a standardized operating system image, you can likely automate the encryption process for users based on the predetermined data storage locations from your image. For example, if users can only save data in %userprofile%\My Documents, you may only need to encrypt that directory. (Note: be careful when encrypting My Documents if you use folder redirection. If My Documents is redirected to a server, the server will need to be Trusted for Delegation and have access to the user's profile. In such cases, it's generally easier to just encrypt the Offline Files cache, which we'll discuss later in this column.)
In a less-managed environment (where users may write to many locations on the file system), you may want to take the approach of automating the encryption of a few common storage locations and then providing education to users on how to encrypt other locations on their own. Regardless of your environment, you should always encrypt at the directory level rather than at the file level. Doing so ensures that all files created in that directory, including temporary ones, are always encrypted.

Recommended Encryption Locations
As a general rule, the locations you should normally encrypt are: %userprofile%\My Documents, %userprofile%\Application Data\Microsoft\Outlook (assuming you're using Microsoft Office Outlook® as your messaging client), and %userprofile%\Desktop. Encrypting My Documents protects the default location for saved files in Windows XP. Protecting the Outlook directory ensures that sensitive e-mail that's cached locally is also encrypted; this is particularly important for users running in the Outlook 2003 default of Cached Mode. Finally, the desktop is often used as a sort of temporary directory where users may place documents they're currently working on or presentations they frequently deliver. Of course, in your organization, you may have modified the default locations of some of these directories, so your EFS encryption choices should reflect whatever locations are normally used in your organization.
In addition to these general best practices, you should also determine whether you have applications that may save sensitive information in alternative locations. For example, some applications save user information in %ProgramFiles%\AppName rather than the user's profile. It's important to determine whether you have any such applications on your client systems so that you can properly protect whatever data they utilize. In the best-case scenario, the application could be configured to save changes in another user-profile-specific path (such as My Documents), in which no further EFS changes would be required. In a worst-case scenario, if the application requires that data be stored in its Program Files directory, EFS could be used to encrypt only that specific subdirectory of Program Files.
Finally, use caution if you plan to encrypt the temporary directory (%TMP% and %TEMP%). Many application installers use this directory for expansion of installation package contents and then perform file move operations on the expanded files to place them in the proper path in %ProgramFiles%. Because a moved file on the same volume retains its original attributes, the file will continue to be encrypted after it's been placed in %ProgramFiles%. If a user other than the one running the installer then attempts to use this file, he will be denied access to it. Often, such problems do not manifest themselves in clear application error messages that are indicative of the root cause. Because of this, I generally recommend that %TMP% be encrypted only in well-managed environments where end users do not install applications on their own.

Protecting Offline Files
Offline Files is a feature in Windows 2000 and later that allows users to make a local copy of data stored on a file server. Offline Files then allows the user to work on this data while disconnected from the server and to synchronize the changes back to the server when next connected. Offline Files, however, is not a simple directory containing copies of files from the server. Instead, it's a database that is shared systemwide and runs in the context of the SYSTEM account. In Windows XP, this database can be protected with EFS. Consider what this means in light of the earlier discussion about EFS being per-user. How could EFS, which is user specific, encrypt a database that is used by multiple security principals?
When a user running Windows XP chooses the option in Windows Explorer to encrypt offline files, the encryption routine utilized is different from that used for the other encryption operations the user may initiate. Rather than utilize the user's personal EFS keypair for the process (which would prevent SYSTEM from accessing the files), the SYSTEM account self-generates a keypair for use with EFS and uses that keypair to encrypt the client-side cache. This has a critical security implication in that if someone can subsequently get code to execute as SYSTEM, he can decrypt the data in the cache. If a laptop is stolen, it's trivial for an attacker to reset the administrator password and log on as administrator. Once running as administrator, it's similarly simplistic to force code to run as SYSTEM and then access the cache. (Note that this behavior has changed in Windows Vista™. Windows Vista now uses the individual user's key to encrypt user-specific Offline Files.)
To protect against this type of attack, the laptop can be configured to store the encryption key for the Security Accounts Management (SAM) database offline, either as a password input at boot time (mode 2) or on a floppy disk during the boot process (mode 3). Both approaches can be cumbersome since they require non-automatable interaction with the computer during the boot process, creating potential problems with automated systems management. However, if EFS is to be used on a computer with Offline Files, it is critical that one of these options be used secure the data in the cache. I normally recommend you use the boot-time password (see Figure 1). This is because a user who has a laptop bag stolen will most likely either have the disk in the laptop already or, if not, then almost certainly in the bag along with the laptop.
Figure 1 Automate EFS operations
Figure 1 Automate EFS operations

Enabling EFS
Once you've determined what to encrypt on your client, the next step is to perform the actual encryption operation. This process can be achieved through a logon script or other process that will execute a script on the client that calls cipher.exe to perform the encryption operation. If you have existing standalone EFS users in your environment, as discussed in the earlier example, I recommend migrating them first.
To migrate these standalone users, the first step is to provision the client systems with managed certificates, as discussed in the February 2007 installment of this column (microsoft.com/technet/technetmag/issues/2007/02/SecurityWatch). Once all systems have enrolled for these managed certificates, cipher should be run with the /U switch to update all existing files with the new encryption and recovery agent keys. At this point, all systems in the SG-ComputersUsingEFS group would be utilizing EFS in a managed fashion, including centrally issued (and potentially archived) keys, and centrally managed recovery agents.
To deploy EFS for the users not currently using EFS, you must first remove the Group Policy preventing EFS usage. Once that is completed and users have enrolled for managed certificates, a logon script can be run to encrypt the key paths discussed earlier. Here is a simplistic script sample:
cipher /e /s /a "%userprofile%\My Documents"

cipher /e /s /a "%userprofile%\Application Data\Microsoft\Outlook"

cipher /e /s /a "%userprofile%\Desktop"
After you've enabled EFS on the desired directories, you should consider setting two additional options to increase the security of your EFS deployment, both related to data paged from memory to disk. Note that for each of these scenarios, the amount of data that could be potentially recovered by a malicious party is limited to that which was loaded in memory or paged to disk during legitimate use. In other words, if a user has sensitive EFS-protected data on the machine that they have not viewed (and, hence, not loaded into memory), this data is not at risk from these attack vectors.
The page file is used by Windows as virtual memory (often referred to as swap space in other operating systems) and contains raw pieces of in-memory data written in clear text to the hard drive. This can put sensitive data at risk on an EFS-protected system because if an attacker is able to recover the page file, they can potentially utilize forensics tools to extract usable data from this file. Since the page file itself cannot be encrypted in Windows XP (note that it can be encrypted in Windows Vista) the best option is to force Windows to clear it at shutdown. This can be accomplished via Group Policy, as illustrated in Figure 2.
Figure 2 Setting group policy
Figure 2 Setting group policy
The downside to this setting is the significant increase in time it takes to shut down a machine, particularly one with large amounts of memory. (By default, the size of the page file is directly related to the amount of physical memory in the computer.) This is because to clear the page file, Windows must physically write to every page on the disk prior to shutdown.
The hibernation file in Windows contains a dump of the machine's physical memory when the power management mode is set to enable hibernation. When hibernation occurs, Windows writes the complete contents of physical memory to disk in the hibernation file, allowing the computer to be restored to exactly the same state when the user is ready to continue working. Like the page file, the hibernation file cannot be encrypted in Windows XP. However, unlike the page file, hibernation cannot be disabled directly from a GPO. Rather, you should use a script to call powercfg.exe with the /HIBERNATE switch to disable (or re-enable) hibernation.
After completing your initial encryption and configuration tasks, you should consider wiping the slack space on disks in your deployment. This process can be very time intensive and disruptive, but has a security benefit in high-security environments. Slack space is the area of a disk that does not hold data currently active on the system but which, because of the way hard disks operate, may contain pieces of sensitive information from previously used files. In other words, because a file delete operation doesn't actually overwrite all the sectors a file occupies on a disk (it only removes the pointer from the file table), the data that was deleted may continue to exist on the disk. In these cases, the data may be recoverable by forensics tools that can read this slack space and reassemble files from it. To securely wipe this residual data, run cipher with the /W switch. This forces cipher to repeatedly overwrite all sectors marked as available on the disk. As long as all future file operations on sensitive data occur in EFS-protected directories, it won't be necessary to run this command on a regular basis.

Enhancing Windows Explorer for EFS
By default, encryption and decryption options for files and directories are somewhat buried in the advanced properties menus in Windows Explorer. You may choose to enhance the end-user experience with EFS by placing "Encrypt" and "Decrypt" on the Windows Explorer context menu (exposed with a right-click). Additionally, Windows Explorer can be configured to show EFS-protected files and directories in a different color (green) than other files. This can help users know whether a file or directory is encrypted without having to drill down into the advanced properties menu. Finally, you may want to add an option to define which users have access to the file or directory. To enable these options, make the
following changes in the registry via a script calling reg.exe:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Advanced]

"EncryptionContextMenu"=dword:00000001

"ShowCompColor"=dword:00000001

[HKEY_CLASSES_ROOT\*\Shell\Encrypt To User...\Command]
@="rundll32 efsadu.dll,AddUserToObject %1"

EFS in Windows Vista
EFS has received a number of important enhancements in Windows Vista that make it easier to deploy and manage, as well as increase its security. Primary among these enhancements is full support for storing EFS keys on smart cards. This is a particularly valuable feature for organizations that are already utilizing smart card logon, because that same card can also be used to store users' EFS keys. This support for smart cards also extends to recovery agent certificates, such that the sensitive DRA certificates can also be stored on cards.
Windows Vista also supports encryption of items previously either impossible or not easily accomplished in Windows XP. Windows Vista can encrypt the page file, eliminating the need to set the "clear virtual memory pagefile" option. The entire design of Offline Files has been reworked in Windows Vista. In addition to much better performance and stability (as well as a generally more user-friendly interface), client-side caching is now per user, meaning it is possible to securely encrypt the cache without the use of SYSKEY mode 2 or 3. These two improvements eliminate major deployment hurdles for today's Windows XP-based environments.
Finally, Windows Vista allows administrators to configure encryption of the Documents folder directly through Group Policy, without having to utilize a separate script. Figure 3 illustrates the new EFS properties available via GPO in Windows Vista.
Figure 3 EFS properties in Windows Vista
Figure 3 EFS properties in Windows Vista

Wrap Up
EFS provides Windows administrators with a highly secure option for protecting mobile data. EFS is scalable, manageable, and provides flexible data recovery mechanisms. In Windows Vista, EFS is further improved with enhanced capabilities for easier management and deployment, as well as support for smart card-based key storage. For organizations that want to protect data, even when a machine has been physically lost, EFS provides a strong solution that's already a part of your existing Windows platform.
Where to Go for More
For more information on the topics discussed in this column, see the following resources:
  • The February 2007 Security Watch column
  • Encrypting File System in Windows XP and Windows Server 2003
  • How to Use the SysKey Utility to Secure the Windows Security Accounts Manager Database

John Morello graduated summa cum laude from LSU and has been with Microsoft for six years in a variety of roles. As a Senior Consultant, he has designed security solutions for Fortune 100 enterprises and Federal civilian and defense clients. He’s currently a Program Manager in the Windows Server group working on security and remote access technologies.


 The Desktop Files: PsTools Primer
The Desktop Files
PsTools Primer
Wes Miller
Download the code for this article: MillerTheDesktopFiles2007_03.exe (150KB)

Until last year, I worked at Winternals Software, which was acquired by Microsoft in July 2006. Winternals sold commercial software, but the company founders, Mark Russinovich and Bryce Cogswell, also owned a separate company called Sysinternals that distributed many tools as freeware. A few of these freeware apps had
even evolved to become components in the Winternals Administrator's Pak. Mark and Bryce also licensed these tools to companies seeking to use them outside of the typical freeware license terms.
One of my favorite tools from the Sysinternals library-OK, it's a whole gaggle of tools-is the PsTools suite now found at microsoft.com/technet/sysinternals/utilities/pstools.mspx. Mark named the tools after the UNIX process listing utility, ps. As mentioned on the download site for the tools, there are built-in Windows® command-line utilities that can perform some of the same functions as these tools. However, in addition to significant unique functionality, a major difference is that the PsTools suite can be used locally or remotely (provided the remote systems are configured correctly, of course). Even better, the process for running them remotely is nearly as simple as running them locally.
The PsTools suite includes several individual applications, listed in Figure 1. As with the rest of the Sysinternals utilities, there is no installer, only applications. Just put them in a folder on your path and go!
Figure 1 PsTools utilities (Close Figure 1)

Tool | Description
PsExec | Allows you to execute processes in any user context.
PsFile | Shows the files opened remotely on a system.
PsGetSid | Displays the Windows Security Identifier (SID) of a computer or a user.
PsInfo | Lists information about a Windows system.
PsKill | Kills processes by name or process ID.
PsList | Lists detailed information about running processes.
PsLoggedOn | Shows all locally logged on users as well as all remote resource-sharing users.
PsLogList | Dumps Windows Event Log records.
PsPasswd | Changes user account passwords.
PsService | Views and controls Windows services.
PsShutdown | Shuts down, reboots, hibernates, or suspends a computer.
PsSuspend | Suspends running processes.
Now, you may notice that some of this functionality (especially PsLogList) could be accomplished via Windows Management Instrumentation (WMI) and some complex scripting. But the point of the PsTools suite is that each utility was designed to accomplish a specific task, and do so simply and easily.
I'm going to walk you through each tool and give you a simple example of why and how you might use it. But first, I'll offer a brief introduction into how the tools themselves work and what kind of prerequisites you must have in place in order for them to work.

How Do the Tools Work?
The tools all generally work in the same manner. On the host system, you launch the desired PsTool. Though many can be run locally, you'll find that these tools typically provide their utmost utility when run against a remote system or several systems at once. (PsExec is a specific exception that comes to mind, as you will see in my example below.) Using the command-line argument you provide, the utility copies itself to the remote system's administrative share, which is the same as %SystemRoot% (Windows directory) on the remote system.
Once copied into the remote directory, the application launches itself and installs a Windows service. This Windows service performs the task you requested, using the credentials you provided at launch time. If an end-user UI on the client system is required by the specific tool, this service then launches an additional binary in the user's context. (Remember that services can't throw direct interactive UIs.) The service communicates any necessary information back to the console that launched it. And finally, the service uninstalls itself.
As you can see, the tools are powerful and self-contained. Though they involve multiple binaries and multiple processes, you as a user don't have to worry about it-you simply run the desired tool and it works.
In order to use the tools, you must have the following prerequisites: either Windows NT®, Windows 2000, Windows XP, or Windows Server® 2003 (x86 or x64 versions of Windows are supported, but Itanium versions are not); the default administrative network share enabled on the remote system you are running the tools on (admin$); and the ports for File and Printer Sharing opened on the remote system (whether you are using Windows Firewall, Internet Connection Firewall, or another firewall product).

PsExec
PsExec is my personal favorite among the tools-and probably not for the reason you might expect. For the better part of two years, I have been running as "non-admin" on my systems-I run as a User, not as an Administrator. Instead, I have a short script called run.vbs that I use to run apps as local Administrator (they could just as easily run as any user). Figure 2 shows the content of the script (which is also available on the TechNet Magazine site at technetmagazine.com/code07.aspx). The script assumes the PsTools are in the Path system variable.
Figure 2 Run.vbs (Close Figure 2)
SET WshShell = WScript.CreateObject("WScript.Shell")
SET WshSysEnv = WshShell.Environment("SYSTEM")
SET FSO = CreateObject("Scripting.FileSystemObject")
IF WScript.Arguments.Count <> 0 Then
    FOR EACH arg IN WScript.Arguments
        iArgCount = iArgCount + 1
        strCmdArg = (arg)
        strCmdArray = Split(strCmdArg, " ", 2, 1)
        IF iArgCount = 1 THEN
        strExe = strCmdArray(0)
        ELSEIF iArgCount = 2 THEN
        strRun = strCmdArray(0)
        ELSE
        strParams = strParams&" "&strCmdArray(0)
        END IF
    NEXT
END IF
'/t:0A && title ***** Admin *****
        strExt = LCase(Right(strExe, 3))

IF strExt <> "exe" AND strExt <> "bat" AND strExt <> "cmd" THEN
WshShell.Run "psexec.exe -d -i -e -u COMPUTERNAME\USER -p PASSWORD  cmd /c start "&strExe&"
    "&strRun&" "&strParams, 0, FALSE
ELSE
WshShell.Run "psexec.exe -d -i -e -u COMPUTERNAME\USER -p PASSWORD "&strExe&" "&strRun&"
    "&strParams, 0, FALSE
END IF

    SET WshShell = NOTHING
    SET WshSysEnv = NOTHING
    SET FSO = NOTHING
This simple script is one example of using PsExec with functionality that Mark added as we worked on our Protection Manager product at Winternals: the ability for a limited user to provide more privileged credentials to launch PsExec locally. The more common use of PsExec, of course, is to run it remotely-for example, to run a utility that doesn't provide its own remoting, scripting, or automation framework. A couple of key features of PsExec include the ability to copy a binary to remote systems that is to be executed, as well as the ability to dictate processor affinity. One other handy feature of PsExec is the ability to script it against many machines simultaneously by providing a script file.
Here is simplified usage for PsExec:
PsExec \\computer -u username -p password command
You can expand on this using the switches shown in Figure 3.
Figure 3 PsExec switches (Close Figure 3)

Switch | Description
-a | Specifies processor affinity.
-c | Indicates whether the application should be copied to the remote system.
-d | Specifies not to wait for the process to finish before exiting (it won't wait for a success or failure message).
-e | Loads using the specified account's profile.
-f | Copies the application even if it exists on the remote system. Ensures that you are running the current (or a specific) version.
-i | Runs the application interactively on the remote system (required if you want an end user to see any user interface from the application).
-l | Runs the application as a limited user. Removes the Administrators group token and strips process privileges to the level they would be for a member of the Users group. Handy to run high-risk Internet-facing applications with minimal privileges.
-n | Specifies the timeout to wait before failing to connect to remote computers.
-priority | Runs the process at the specified priority. That is, specifies how Windows should set the process priority for a given task (-low, -belownormal, -abovenormal, -high or -realtime ).
-s | Runs the process using the System account (handy to run Regedit to access privileged registry keys).
-v | Copies the application only if the one being copied is newer.
-x | Displays UI on the Winlogon secure desktop-only works on the local system.
@file | Runs the command on all computers specified in the text file.

PsFile
PsFile is a more narrowly focused tool than PsExec. Specifically, it is used to check what files are open, and by whom, on the remote system. This can be useful if you want to track which files are open before rebooting a server. If you need to find out which user has locked the file, perhaps preventing editing or deletion, you can notify them manually, or close it via PsFile. Usage for PsFile is below:
PsFile \\computer -u username -p password
This simply returns a list of the open file handles on the remote system. Beyond the standard usage, you can also add an Id parameter to the statement, specifying the name of the file you want to either find information about or close. You can also include the -c switch, which closes the file you identified with the Id parameter.

PsGetSid
PsGetSid is also a very focused tool. It will tell you the Security Identifier (SID) of a computer, user, or group. This can be useful if you want to ensure that the systems in your domain don't have duplicate SIDs, or you simply want to verify the SIDs of all users or groups on a system. Usage of PsGetSid is as follows:
PsGetSid \\computer -u username -p password account
Note that account can specify a user, computer, or group. While PSGetSid's syntax is simple, a couple of additional parameters are also available. The SID parameter specifies the account (group or computer) given a specified SID, and @file runs the command on all computers listed in the specified text file.

PsInfo
PsInfo is used to provide detailed system information. This obviously would be the most useful when scripted against multiple remote systems, with the output sent to a file (such as an Excel® and database-friendly CSV file). Note that this utility operates via an important concept that is different from most of the other tools: if you don't provide a computer name, PsInfo will, by default, attempt to query all computers in the domain. PsInfo's usage is as follows:
PsInfo  \\computer -u username -p password
While the syntax is simple, additional switches and parameters are also available as shown in Figure 4
Figure 4 PsInfo Switches (Close Figure 4)

Switch | Description
-h | Shows all Windows updates.
-s | Shows all applications installed on the system.
-d | Shows disk/volume information for the system.
-c | Outputs the results in CSV format.
-t | Specifies another delimiter for the CSV file other than the default value of \t.
Filter | Shows only data that matches a given filter.
@file | Runs the command on all computers listed in the specified text file.

PsKill
PsKill is a great utility for terminating processes on either a local or remote system. The usage is very simple, and it allows you to rapidly kill a process along with all of its child processes. PsKill's usage is much like the other tools I've discussed:
PsKill -t \\computer -u username -p password process id | process name
Note that you can specify either the process id or the process name you want to kill.

PsList
I don't use PsList much because I'm such a Process Explorer fan (see microsoft.com/technet/sysinternals/ProcessesAndThreads/ProcessExplorer.mspx for more information.) But since there isn't a remotable version of Process Explorer, PsList becomes a reasonable facsimile for some of the key functionality provided by Process Explorer. In effect, PsList is a remotable list of one or more processes running on a remote system.
Usage for PsList is pretty simple:
PsList \\computer -u username -p password account
As with the other tools, the account parameter can specify a user, computer, or group. Additional parameters are shown in Figure 5.
Figure 5 PsList Switches (Close Figure 5)

Switch | Description
-t | Shows information at the thread level.
-m | Provides detailed memory information for each process.
-x | Shows process, memory, and thread information.
-t | Shows information as a process tree (think Process Explorer tree view).
-s [n] | Runs in task manager mode for the specified number of seconds. This allows for a relatively real-time display of remote process information.
-r [n] | Specifies how frequently (in seconds) to refresh when running in task manager mode.
name | Shows information about processes that begin with the name specified.
-e | Matches the process name exactly.
pid | Displays information about a specific process.
PsList is powerful, yet easy to use-and without using Remote Desktop allows you to see the remote processes in enormous detail. With this information you could move on to PsKill or PsSuspend if, for example, you had a process that had to be reined in.

PsLoggedOn
The premise behind PsLoggedOn is self-explanatory. The utility shows you who is logged onto a system. This can be either local (interactive) logons or network share logons. Similar to PsFile, this capability can be useful to determine who is using a server that you may need to perform maintenance on. Usage is very simple:
PsLoggedOn \\computer
PsLoggedOn has only two optional parameters: -l only shows local logons (not network logons), and -x excludes the logon time.
While basic, PsLoggedOn allows you to easily see information for which you would generally have to dig in a couple of Windows locations.

PsLogList
One of the most generally useful utilities, PsLogList, is a tool for dumping the Windows event logs into a text format that can then be used by log readers, databases, or whatever else your heart desires. The main advantage is that it converts the event log entries from their binary format into something that can be easily transformed or automated. (Of course in Windows Vista™, event logs are natively available as XML, but that's another topic for another day.)
Usage of PsLogList is:
PsLogList \\computer -u username -p password
A number of additional switches and parameters are also available as shown in Figure 6.
Figure 6 PsLogList switches (Close Figure 6)

Switch | Description
-a | Dumps only events after the specified date.
-b | Dumps only events before the specified date.
-c | Clears the event log permanently after dumping the desired output.
-d | Displays only information for the specified number of days.
-e | Excludes events with the specified event ID (up to 10 can be specified).
-f | Filters event types (warning, error, and so on).
-g | Exports the event log out as an EVT file-requires the -c switch.
-h | Displays only information for the specified number of hours.
-l | Dumps from the specified EVT file.
-m | Displays only information for the specified number of minutes.
-n | Displays only the specified number of most recent events.
-o | Shows only events from a given source (-o SRService for the Windows System Restore Service, for example).
-q | Excludes events from a given source (opposite of -o).
-r | Reverses order-dump log events from least recent to most recent.
-s | Lists records on each line in delimited fields.
-t | Specifies something other than a comma as a delimiter for the -s output.
-w | Waits for new events to be populated and dump in real-time. This is only applicable on the local system.
-x | Dumps extended data from event logs.
eventlog | Specifies which event log to dump. System is the default.
@file | Runs the command on all computers specified in the text file.
Windows event logs can be an incredibly powerful diagnostic tool, but the trick is getting the data out to the point it can be manipulated or consolidated. PsLogList performs that trick.

PsPasswd
PsPasswd is a simple yet powerful utility designed with some of the same power as Net.exe, but with remotability and automation in mind.
PsPasswd  \\computer -u username -p password Username Newpassword
You can specify a computer, no computer (the local system), or if you specify the computer as \\* the utility will run against all computers in the current domain. PsPasswd has only one additional parameter available: @file runs the command on all computers specified in the text file. Again, it's a very basic utility that provides enormous multi-system power.

PsService
PsService allows you to view and edit Service Control Manager (SCM) information on one or more systems on your network, and you can even search for a given service name on systems on your network.
PsService \\computer -u username -p password command options
When you run PsService, command represents one of the commands listed in Figure 7, and options specifies an attribute to apply to a given command. If no additional command switches are provided, PsService will provide a full dump of SCM information.
Figure 7 PsService commands (Close Figure 7)

Command | Description
query | Queries a given service.
config | Queries the configuration information for a given service.
setconfig | Sets the configuration information for a given service.
start | Starts a service.
stop | Stops a service.
restart | Restarts (stops and starts) a service.
pause | Pauses a service.
cont | Resumes a paused service.
depend | Shows which services depend on the given service.
find | Looks in your network for a given instance of the service.
security | Provides the security information for a given service.
You can thus think of PsService as a powerful, remotable command-line version of Services.msc-but one that also allows you to look across the network for services (perhaps potentially dangerous ones) and set configuration information.

PsShutdown
PsShutdown is another one of my favorites. While some would say that Microsoft includes a tool that handles this task (shutdown.exe), it's really not the same. The key difference is in the power of PsShutdown: namely, it can shut down or restart a remote system. In addition, it has the ability to put a system into either standby or hibernate mode (not just shutdown or restart), lock a system, and restart a system after displaying a message to the user for a given amount of time.
PsShutdown \\computer -u username -p password
A number of additional parameters are also available for use with PsShutdown, as shown in Figure 8.
Figure 8 PsShutdown switches (Close Figure 8)

Switch | Description
-a | Aborts an already in-progress shutdown initiated by PsShutdown.
-c | Allows an interactive user to abort the shutdown.
-d | Suspends the computer (standby).
-e | Shutdown reason codes. I recommend looking on the Microsoft Web site for assistance with these, as they can be problematic to use with both PsShutdown and the built-in shutdown.exe. See msdn2.microsoft.com/en-us/library/aa376885.aspx for more information.
-f | Forces all running applications to terminate.
-h | Hibernates the computer.
-k | Powers off the computer (reboots if power off isn't supported).
-l | Locks the computer.
-m | Specifies the message to display to any interactive users.
-n | Specifies the timeout to wait before failing to connect to remote computers.
-o | Logs off the console user.
-r | Reboots the computer.
-s | Shuts down, but doesn't power off.
-t | Time to count down before running shutdown command or the specific time to power off.
-v | Displays the message to the users until shutdown.
@file | Runs the command on all computers specified in the text file.
You can see that PsShutdown is a very powerful, highly configurable system power control utility that is still relatively easy to use.

PsSuspend
PsSuspend is a handy utility if you want to get a rogue process under control temporarily, or if you simply want to get a high-CPU usage process spun down temporarily without losing any data or application context.
PsSuspend \\computer -u username -p password process ID
You can get the process ID using PsList. Once you've suspended a process, you can use the -r switch to resume it. Note that the concept of suspending and resuming a process is also implemented by Process Explorer, though that works only locally. Obviously, PsSuspend can suspend and resume processes remotely as well as locally, giving it the edge over the built-in implementation in Process Explorer.

Conclusion
I find the PsTools suite to be an invaluable set of utilities. Along with the rest of the Sysinternals utilities (namely Regmon, Filemon, Process Explorer, and now Process Monitor), PsTools are the first programs I put in my path when I reinstall Windows on any of my systems. I hope this column familiarized you with the PsTools if you weren't before, and if you were, I hope you have gleaned some additional knowledge about the tools. Many thanks to Mark Russinovich, of course, for providing these tools for free for all these years and for assisting me with the content for this column.

Wes Miller is a Development Manager at Pluck (www.pluck.com) in Austin, Texas. Previously, Wes worked at Winternals Software in Austin, and at Microsoft as a Program Manager and Product Manager for Windows. Wes can be reached at technet@getwired.com.


 Inside Microsoft.com: Getting Started with Database Mirroring
Inside Microsoft.com
Getting Started with Database Mirroring
Saleem Hakani

If your database goes offline, you’re probably up a creek, right? If you’re using SQL Server 2005 with SP1, however, the database mirroring feature can prevent disaster. This new high-availability technology allows you to maintain a hot standby of your database for use when your production database server becomes
unavailable for any reason. The database mirroring feature works by transferring transaction log records for a database from the primary server to the secondary server, which serves as the hot standby. With database mirroring, data changes are recorded in the transaction log before any changes to actual data pages are made, just as SQL Server™ updates always work. The log records are first placed in the principal database's log buffer in memory and then persisted to disk. Those transaction logs are copied to and replayed on the mirror server's database. This causes the principal's database changes to be duplicated on the mirror database. Note that only the principal database is accessible to client connections. When the principal database receives changes requested by clients, the principal server sends those active changes to the mirror server; the mirror does not make any of these decisions. When database mirroring is enabled and the principal database fails, the mirrored database becomes available.

The Mechanics of Database Mirroring
Database mirroring works with all the standard hardware that supports SQL Server 2005 and it ensures no data loss in the event of a database failure. The mirror database will always be updated with the current transaction that's being processed on the primary database server. Figure 1 illustrates the flow of data.
If your principal server does go down, you can rest assured that the mirror server has an exact point-in-time copy of the principal database as of the last committed transaction. Thus, the mirror is always ready to take over the principal server role.
Figure 1 Data replication to the mirror
Figure 1 Data replication to the mirror
In the database mirroring topology, you'll need a third server called the witness if you want to enable automatic failover from principal to mirror server and vice-versa. A witness server can be any computer that can support SQL Server 2005.

Operating Modes
The database mirroring topology you'll use will depend on the transaction safety and operating modes you've chosen. The operating modes that are supported by database mirroring include high-safety (with or without automatic failover) and high-performance.
High-Safety (with automatic failover) This mode supports maximum database availability with synchronous data transfer and automatic failover to the mirror database. This operating mode is best used when you have fast and very reliable communication between the principal and the mirror servers and you require automatic failover for a single database. In this scheme, the principal database waits to commit a transaction until it receives a message from the mirror server that the mirror server has hardened the transaction's log to the disk.
High-Safety (without automatic failover) This mode supports maximum database availability with synchronous data transfer but without automatic failover to the mirror database. In this mode, if the mirror server instance becomes unavailable, the principal server instance continues to function but will not be able to mirror the data. If the principal server goes down, database mirroring is suspended but you can manually force the service to fail over.
High-Performance In this operating mode, the transfer of data is asynchronous. The principal server does not wait for an acknowledgment from the mirror as it does in the above two modes. The mirror server does its best to keep up with the principal, but it is not guaranteed at any point that all the most recent transactions from the principal will be hardened in the mirror server's transaction log. If the principal server goes down, database mirroring is suspended but you can manually force the service to fail over.

Laying the Groundwork
Setting up database mirroring is a simple process if you establish a strong foundation using best practices:
Server Edition Double-check that both principal and mirror servers are running on the same edition of SQL Server 2005; you can use either the Standard or Enterprise edition.
Witness Server Availability If you are planning to use high-safety with automatic failover, then make sure that the witness server is available and that SQL Server 2005 (any edition) is installed. The witness server can run on any reliable computer system that can support SQL Server 2005.
Mirror Image Make sure that the mirror server instance has the identical jobs, logins, SQL Server Integration Service (SSIS) packages, disk partitions, file locations, and server configuration as the principal server instance. Configuring the mirror server exactly as the principal server will enable the mirror server to function the same way as the principal server does.
Full Recovery It's important that all the databases participating in database mirroring are set to FULL recovery model.
Master and TempDB Verify that all the server instances in a mirroring topology use the same Master and TempDB collation and code-page. Having different collation and code-pages can cause problems during database mirroring setup.
Back Up If the database to be mirrored is large, you must perform a full backup of the database first and then restore it on the mirror server instance using NORECOVERY option.
Plan Ahead Determine all the server names, port numbers, security accounts, and locations where databases would reside and document them. See the "Database Mirroring Best Practices" sidebar for a checklist.
Once you have taken care of the groundwork, you're ready to tackle setting up database mirroring in your environment.
Database Mirroring Best Practice
  1. Use partner servers that have identical CPU, memory, storage, and network capacity.
  2. Make sure both the partners have the same SQL Server and OS editions, service packs, and updates.
  3. Install SQL Server on an identical directory and drive structure on both principal and mirror server instances.
  4. If performance becomes a concern, consider a dedicated network interface card to separate the load.
  5. Like the server partners, make sure that both the principal and mirror server instances are identical in terms of CPU, memory, storage and network capacity. Ensuring that both servers have the same directory structure, same disk partitioning scheme, and SQL Server configuration eliminates the need for changes to these during or after the failover to the mirror partner.
  6. Make sure that all of your applications can connect and execute all necessary actions and that all active SQL Server logins (and their permissions) on the principal server instance are also present on the mirror server instance. You can use the Transfer Logins task for SQL Server 2005 Integration Services to accomplish this.
  7. Copy SQL Server agent jobs, alerts, SSIS packages, support databases, linked server definitions, backup devices, maintenance plans, database mail profiles, and so on from the principal server to the mirror server.
  8. Establish a procedure so that whenever you make any modifications on the principal (such as changes to hardware, software, SQL Server settings, or any database objects), you’ll automatically repeat or replicate and transfer those changes on the mirror server instance.
  9. Perform multiple test failovers before going live.

Setting It Up
Let's set up database mirroring using the high-safety operating mode with automatic failover. (As noted, that means a witness server instance is required). For my example here I'll use the server and database names shown in Figure 2, which also specifies each server's role.
Keep in mind that because configuration can impact performance as the pending transaction log is copied from the principal server to the mirror server, you might want to perform the initial configuration of database mirroring during off-peak hours.
Setting up the mirror consists of three steps: creating endpoints on the participating servers, performing backup and restore of your principal database, and enabling mirroring sessions on all participating servers.
Before establishing a database mirroring session, you must establish the communication mechanism between all the servers participating in database mirroring. To accomplish this, create endpoints on all the servers by running this statement on both ServerA and ServerB:
Create Endpoint Mirroring_Endpoint
State= Started as TCP (Listener_Port=5001)
For Database_Mirroring (Role=Partner);
For ServerC (which will be acting as a witness), change the (Role=Partner) to (Role=Witness) and run the statement. This controls the TCP port that each instance listens on.
For the next step, you should perform a full database backup followed by a log backup of the DBM_Demo database from the principal server and then restore it on the mirror server instance using the NORECOVERY option. (Using NORECOVERY assures that the mirror database will be in the restoring state so that the transaction logs can be applied.)
Here's the T-SQL statement to perform a full database backup of DBM_Demo database from ServerA (the principal server instance):
Backup Database DBM_Demo to DISK='E:\MSSQL\Bak\DBM_Demo_FULL.bak';
If there are any changes to the database after you perform the full database backup, you may have to perform a log backup of the database; otherwise it may not be required.
If necessary, you can use the following T-SQL statement to perform a log backup of the DBM_Demo database from ServerA:
Backup Log DBM_Demo to Disk='E:\MSSQL\Bak\DBM_Demo_Log.bak';
Once you have all the backups performed, move the backup files to ServerB or to a shared location so you can restore these backups on ServerB. After you have done so, you should also restore any transaction-log backups you had performed since the last full database backup from ServerA.
You can use the following T-SQL statement to restore the full and log backups on ServerB using the NORECOVERY option:
--Restore full database backup on the mirror --server instance
Restore Database DBM_Demo from Disk='E:\MSSQL\Bak\DBM_Demo_FULL.bak' with NORECOVERY;
Finally, use the following T-SQL statement to restore the log backup on the mirror server using the NORECOVERY option:
Restore Log DBM_Demo from Disk='E:\MSSQL\Bak\DBM_Demo_Log.bak' with NORECOVERY;
Once you have finished restoring all the backups, you are ready to perform the final step-enabling your database mirroring session on all the participating servers.
Setting up a database mirroring session requires a server network address for each of the server instances. This address must identify the instance by providing a system address and the port number on which the instance is listening. The syntax for a server network address looks like this:
TCP://<System-address>:<port>
<System-address>: is a fully qualified domain name or an IP address; you can get this information by executing IPCONFIG on the local machine from a command prompt.
You established the <Port> when you created the endpoints.
You can start the database mirroring session on ServerB like so:
Alter Database DBM_Demo
Set Partner= 'TCP://ServerA.com:5001';
Then run the following T-SQL to start the session on ServerA:
Alter Database DBM_Demo
Set Partner='TCP://ServerB.com:5001';
Next, enable the mirroring session on ServerC (the witness server) like so:
Alter Database DBM_Demo
Set Witness='TCP://ServerC.com:5001';
Database mirroring is now ready to run in your environment. Any database objects that have been added or modified on the DBM_Demo database will be transferred to the ServerB copy. However, if ServerA's database becomes unavailable, a failover can occur, changing the mirrored database's role to the principal role.
Now that you have database mirroring up and running, you'll always have a hot standby ready if your production database ever goes down.

Saleem Hakani is a Senior Database Engineer at Microsoft with more than 14 years of experience with database systems. He founded and leads the Microsoft SQL Community and is responsible for delivering SQL Server standards and automation throughout the Windows Live organization. Saleem holds MCTS, MCDBA, and MCSA certifications. You can contact him at Saleem@sqlcommunity.net.


 Field Notes: The Doctor Is In
Field Notes
The Doctor Is In
Matt Hester

NOT LONG AGO it occurred to me that being an IT pro is somewhat similar to being a doctor. This revelation hit me when I was first using Windows Vista™. At the time I was having networking connectivity issues. After hours of ping, ipconfig, and even a few reboots, I saw a new word that triggered the natural curiosity that we IT pros all share: "diagnose." Typically, when working on an operating system, I see words and phrases like "repair," "status," "task manager," and "send error report" that help me navigate the byways of the operating system. But seeing the word "diagnose" made me think about the similarities in the two professions.
In the first place, both doctors and IT pros have qualifying exams and certifications that allow us to put certain acronyms after our names. These acronyms identify our specializations and hopefully look impressive on the wall or on business cards.
Like doctors, IT pros also have a great many resources that we use to solve problems. Doctors consult with their peers and search for information in professional journals and on the Internet. We IT pros also confer with our peers and do research on the Internet and in technical publications (particularly TechNet Magazine of course!) to help us resolve problems.
Furthermore, doctors and IT pros both rely on trusty tools. Doctors have tried-and-true instruments like the stethoscope; we have ping. They both allow us to listen to what is going on.
The last commonality is social in nature. When doctors and IT pros attend social events, we often find it critical to hide our professional identities. Alas, at a recent party, people somehow found out what I did for a living and suddenly I became extremely popular. But that popularity came at a price: I also lost my identity as a charming human being. I simply became the "PC guy" and, by extension, the unwilling onsite support specialist for all the party guests: "Hey PC guy, why did my computer crash this morning?" "Hey PC guy, where is the Any key?", "Hey PC guy, can you fix my Mac?", The worst part was that I saw a doctor across the room grinning at me with a broad, knowing smile as he continued to dance, uninterrupted.
But back to my connectivity problem: after about three hours of unsuccessful attempts to connect, I clicked the newly discovered Diagnose and Repair button. Imagine my shock and embarrassment when I saw the reply screen shown below:
With my mouth agape and my face burning, I looked around the room to make sure no one was watching. Then I reached around my PC and plugged in the cable. Since that happy day, I have worked further with Windows Vista and used the diagnose capability extensively. It has saved me a huge amount of time in finding the root cause of many issues. It also integrates very well with the Problem Reports and Solutions control panel, which allows you to search for additional information online. Check it out, prior to pulling your hair out.
Later that night as I drifted off to sleep, happy that I had successfully identified and solved my network connectivity problem, I wondered: does this mean I get to play more golf and drive a fancy new sports car?

Matt Hester is a TechNet Presenter on the Microsoft Across America team. Learn more about the presentations he delivers at technetevents.com/mhester. You can also visit his blog at blogs.technet.com/matthewms.


 Windows Confidential: Looking Smart by Being Stupid
Windows Confidential
Looking Smart by Being Stupid
Raymond Chen

WHEN YOU insert a CD into your computer, Windows® might ask you what it should do-even though you told it what to do last time. Why does Windows forget your autoplay settings? Well, it doesn't; it's just double-checking with you.
There were two problems with the way Windows XP handled CD autoplay. First, many users found that after installing a program that supported CD autoplay, they didn't know how to make that program their new default CD autoplay program. If they had previously selected a program and ticked "Always perform this action," there was no easily discoverable way to undo the always flag to cause the CD autoplay dialog to reappear and give the user an opportunity to select the new program as the default.
Second, certain poorly behaved programs, upon installation, secretly hacked the undocumented CD autoplay settings in order to set themselves as the default CD autoplay handler-thus, overriding the user's choice.
Simply put, for users the two problems were: "I just installed this program and I want it to be my CD autoplay program," and its converse "I just installed this program and I don't want it to be the CD autoplay program."
Windows Vista treats autoplay settings globally
Windows Vista treats autoplay settings globally
Windows XP Service Pack 2 (SP2) introduced new CD autoplay behavior in an attempt to address these problems. When it sees that a new CD autoplay handler is available, Windows now shows you the CD autoplay dialog one more time. This gives you a chance to pick that new program you just installed or un-pick that program you just installed if it was rude enough to set itself as the new default player.
You also get the CD autoplay dialog the first time you insert a CD after upgrading to Windows XP SP2. This is a "better late than never" dialog, meant to address any handlers that were installed before you upgraded to Windows XP SP2.
In the old days, the OS only had to concern itself with helping developers interface with a feature. But, in the current software landscape, it also has to worry about stopping programmers who are trying to abuse that interface.
There's another case where it may appear that Windows has forgotten your autoplay settings. When you insert a removable storage device, such as a USB drive, you may get prompted even though you've already selected an option and ticked "Always perform this action." This is not because Windows has forgotten your settings, but rather that it remembered them all too well.
When you select an option for a removable storage device, Windows remembers the setting for that device. If you plug in a different device, you get a new prompt since you didn't specify how you wanted this different device to behave. The intent of this design is so you can say that your digital camera should default to "View pictures" whereas your music player should default to "Play music." (If the device doesn't have a serial number, then plugging it into a different USB port causes it to be treated as a different device.)
In response to the confusion created by the Windows XP design, the behavior has changed in Windows Vista™. The autoplay settings are now treated as global rather than per-device. If you say "Take no action," then Windows Vista will take no action regardless of which device you insert or where you plug it in. Sometimes the way to make the operating system look smarter is to make it more stupid.

Raymond Chen's Web site, The Old New Thing (blogs.msdn.com/oldnewthing), and identically titled book (Addison-Wesley, 2007) deal with Windows history and Win32 programming. In his spare time, he has been known to score 7th grade essays.