Availability of the Microsoft IT Site Delete Capture 1.0 Feature for SharePoint Products and Technologies

I’m pleased to announce the availability of the Microsoft IT Site Delete Capture 1.0 feature for SharePoint Products and Technologies.


The Microsoft IT Site Delete Capture feature 1.0 is a shared component library (DLL); by registering the Microsoft IT Site Delete Capture feature 1.0 shared component library in the Global assembly cache, SharePoint Products and Technologies administrators can intercept both site/web delete requests and archive the site/web to a resource local to the web front-end computer or UNC path before the site/web is removed the configuration and content databases. The Microsoft IT Site Delete Capture feature 1.0 also exposes functionality allowing SharePoint Products and Technologies administrators to send e-mail notification to the end-user indicating the site has been archived and deleted, additionally any failure in the event receiver will generate an e-mail message to the end-user indicating that the site/web has not been deleted. The message format, text, and language are stored in a flexible, culture-independent extensible markup language configuration file to support any localization requirement.


The Microsoft IT Site Delete Capture 1.0 will be a community driven effort and managed under CodePlex source control.  Discussion boards and bug management will be also provided by the CodePlex workspace.


To learn more about the Microsoft IT Site Delete Capture 1.0 feature visit http://www.codeplex.com/governance.


The SharePoint Governance workspace is intended provide governance and manageability samples and tools designed to help IT Professionals management SharePoint Products and Technologies deployments.  Upcoming tools include an implementation of site/web lifecycle management based on the Site Delete and Confirmation feature, the Microsoft IT Site Delete Capture 1.0 feature, and additional out-of-the-box functionality.  A sample auditing configuration solution deployment package will be introduced in the September-October timeframe that provides guidance on auditing and the management of content types across site collections.

SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007 – Part 1 (Introduction, Overview, and basics)

This will be the first of a multi-part series covering SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007.  This post will cover an introduction to SQL Server 2005 Database Mirroring, an overview, and the basics to include considerations and integration with SharePoint Products and Technologies. Part 2 will cover implementing SQL Server 2005 Database Mirroring with SharePoint Products and Technologies using NTLM authentication and dedicated (DAS) storage and failover examples.


SQL Server 2005 Database Mirroring has increased in popularity since its introduction and among the possible applications there is a growing demand to implement SQL Server 2005 Database mirroring for SharePoint Products and Technologies.  This article outlines the considerations and implications of designing SQL Server 2005 Database Mirroring into your SharePoint Products and Technologies database architecture.

Understanding Basic Database Mirroring Concepts

SQL Server 2005 Database Mirroring provides high-availability and rapid failover by continuously sending a databases’s transaction logs from an originating SQL server instance (principal) to a destination SQL server instance (mirror).  Since the granularity of failover is at the database level unlike the server level failover in Microsoft Cluster Server, SQL Server 2005 Database Mirroring failover can provide an increase in failover performance and is more seamless and transparent to the application.  A complete overview of Database Mirroring in SQL Server 2005 is available at http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx.

Considerations and Implications

There are several important factors to consider before implementing SQL Server 2005 Database Mirroring in your SharePoint Products and Technologies infrastructure.  Prior to implementing SQL Server 2005 Database Mirroring, you should understand what problems you are trying to solve whether they are performance, availability, or geographic replication related.


  • Determine the SQL Server 2005 Database Mirroring implementation mode – High Performance vs. High Availability vs. High Protection

    • High performance SQL Server 2005 Database Mirroring is also known as asynchronous mirroring where the transaction safety level is OFF.  In high performance mirroring the transaction is committed as soon as the principal server writes the log record to the local log and sends the log record to the mirror.  The principal does not wait for acknowledge and if needed queues the logs, a failover at this point may result in data loss.
    • High availability SQL Server 2005 Database Mirroring is also known as synchronous mirroring where the transaction safety level is FULL.  Each transaction committed on the principal database is also committed on the mirror server synchronously.  The principal server will only commit a transaction after receiving acknowledgement from the mirror server indicating the transaction log has been hardened.  This process of acknowledgement and receipt results in slower performance versus asynchronous mirroring.
    • High protection SQL Server 2005 Database Mirroring is also known as synchronous mirroring where the transaction safety level is FULL and is similar to high availability with primary difference being that there is no implementation of a witness server which requires manual failover.
    • SQL Server 2005 Standard Edition allows only the FULL transaction safety level.

  • Understand and plan for database limitations

    • master, model, temp, or msdb cannot be mirrored.
    • SQL Server 2005 Database Mirroring requires that the databases use the FULL recovery model, the SIMPLE and BULK-logged recovery models cannot be used.
    • Transactions are played out on two servers, limiting the number of databases will reduce the performance cost.
    • Fewer databases will result in less overhead, by limiting the number of databases in a mirroring session you can maintain optimum performance levels.  This may require the repartioning of smaller content databases to achieve fewer and larger content databases.  Larger databases can leverage multiple data files spanning one or more drives to optimize performance and management.
    • Mirror databases that require redundancy and/or high value databases – content databases typically fall into this category.

  • Determine the appropriate hardware requirements – Can a single SQL server support your infrastructure?

    • Automatic failover requires a witness (polling) server.  The witness server’s role is to enable automatic failover – if the mirror server has confirmation from the witness, it can automatically take on the role of principal and make its database available.
    • SQL Server 2005 Database Mirroring requires two unique SQL server instances, one on the principal server and one on the mirror server.

  • Determine the deployment methodology

    • Decide on a localized or geographically disperse deployment.  A geographically disperse deployment provides a disaster solution, but comes with performance implications as the result of network performance, latency, and type LAN vs. MAN vs. WAN.

  • Security

    • SQL Server 2005 Database Mirroring supports both Windows (NTLM/Kerberos) and Certificate based SQL authentication.
    • SQL Server Database Mirroring supports both AES and RC4 encryption algorithms for transmission encryption.

  • Decide on shared vs. dedicated storage

    • Storage is duplicated in mirroring 1TB of storage on the principal server requires 1TB of storage on the mirror server.  Since each server in a mirroring partnership is a unique SQL server instance, the resources are not shared.

  • Plan for capacity

    • SQL Server 2005 Database Mirroring requires duplication of storage, before designing mirroring into your database architecture you should understand the capacity requirements of the current server farm and future scale.  1TB of storage on an MSCS cluster with shared storage requires 2TB of storage in mirroring, 1TB on the principal and 1TB on the mirror.

  • Change – understand the existing SharePoint Products and Technologies infrastructure’s rate of change (churn).  The performance of the principal server is affected by the transfer of log records to the mirror.

    • Long running and/or intensive transactions can impact performance and failover times and can include creating and/or rebuilding an index on a large table or bulk loading a large amount of data (Search).
    • Log bound workload performance under database mirroring is highly dependent on network performance and log I/O.

  • Handling role changes – understand the implications resulting of the loss of the principal or mirror server on SharePoint Products and Technologies.

    • Understand the manual failover steps to support SharePoint Products and Technologies.
    • Script an automatic failover mechanism to handle client-redirects.

  • Managing failover – SQL Server 2005 Database Mirroring works with a single database at a time. You need to take this into account when designing SQL Server 2005 Database Mirroring into your database architecture.  *See Failover Handling
  • Planning and implementation

    • To provide the best initial performance when implementing SQL Server 2005 Database Mirroring, consider taking an online backup of the principal database(s), copy the database(s) to the mirror server and restore the database(s) with the option to apply further transaction logs.  This will reduce the length of time associated with initial synchronization and minimize the performance implications of the initial synchronization.
    • Ensure the implication of mirroring role changes is understood and planned for and the technology has been thoroughly tested before designing into your database architecture.
    • A one to one mapping of principal to mirror server is recommended to maximize compatibility with SharePoint Products and Technologies.
    • In high availability SQL Server 2005 Database Mirroring the process of determining a failover is based on the network connection. If there is a problem with the network, mirroring will fail over or deny access to the database because of the quorum requirement.  Understand the network implications on database mirroring.
    • As with any product or technology understand the support parameters, limitations of the product or technology, and ensure it has been thoroughly tested prior to a production implementation.

Failover Handling

The introduction of a witness server in your SQL Server 2005 Database Mirroring architecture provides a mechanism for automatic failover; however, since mirroring granularity is at the database level, it is important to consider how to handle both single, multiple database and/or server failure.  Since the principal and mirror server SQL server instances are unique, SharePoint Products and Technologies will need to be made aware of the database server hosting its content.  The following sections details the STSADM operations that can be run to create this awareness in Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007.

If an individual database fails you can set the database server using the SharePoint 3.0 Central Administration user interface or STSADM.

Content Database Failover

To change the principal server for a content database using STSADM run:

stsadm -o deletecontentdb -url “<http:// webapplicationurl>” -databasename “<contentdatabase>” -databaseserver “<failedprincipal>”
stsadm -o addcontentdb -url “<http:// webapplication>” -databasename ” <contentdatabase>” -databaseserver “<newprincial>”

To change the principal server for a content database using the SharePoint 3.0 Central Administration user interface:



  1. In SharePoint 3.0 Central Administration select Application Management, then click Content Databases.
  2. From the Manage Content Databases page, select Remove content database for the failed content database.
  3. From the Manage Content Databases page, select Add a content database to reinstate the content database on the new principal server.

Configuration Database and Administration Content Database Failover


The configuration and administration content database must reside on the same SQL database server, in the event that either of these databases fails, both must be failed over to the new database server.  To failover the configuration and administration content database, run the following STSADM from a web front-end computer:

stsadm.exe -o renameserver -oldservername <failedprincipal> -newservername <newprincipal>


Restart Internet Information Services to commit the change.


Search Database Failover

The following STSADM operation should be run from one web front-end computer for each failed search database.

stsadm –o editssp –title <searchname> –ssplogin <username> –ssppassword <password> -searchdatabaseserver <newprincipal>

Shared Services Database Failover

The following STSADM operation should be run from one web front-end computer for each failed SSP database.


stsadm –o editssp –title <SSPName> –ssplogin <username> –ssppassword <password> -sspdatabaseserver <newprincipal>


The information provided above details the steps necessary to instantiate a manual failover of the various components of a SharePoint Products and Technologies server farm, for additional information on scripting automatic client-side redirect in the event of failover see Alerting on Database Mirroring Events and Database Mirroring in SQL Server 2005 under Resources and Recommended Reading.  While it is possible to mirror the configuration and other databases associated with a SharePoint Products and Technologies server farm through the proper implementation of STSADM operations; support of SQL Server 2005 Database Mirroring for SharePoint Products and Technologies is limited to the content databases.


Resources and Recommended Reading


Database Mirroring in SQL Server 2005


SQL Server: Database Mirroring Best Practices and Performance Considerations


SQL Server 2005 Database Mirroring FAQ


Alerting on Database Mirroring Events


Using Database Mirroring with Office SharePoint Server and Windows SharePoint Services


SQL Server Performance Test Results

Code Snippet – How to Locate Host Header-based Site Collections in Windows SharePoint Services 3.0

One of the great features in Windows SharePoint Services 3.0 is the ability to co-host both path-based and host header-based site collections within the same server farm and content databases. Now that you’ve introduced host header-based site collections to your server farm, how do you quickly identify those site collections within a server farm or content database that also hosts your path-based site collections? In this scenario the object model is extremely useful in reporting on those site collections, the Web application, and content database where they reside.


The following code example provides a basic object model approach to locating and returning those site collection via a Command Line application.


Before using the code example add references to the following in your Microsoft Visual Studio project:



  • Microsoft.SharePoint

In the code example each SPSite object (site collection), is represented within an SPSiteCollection object that consists of the collection of all site collections in the Web application, by exposing the HostHeaderIsSiteName member, host header-based site collections can be enumerated through the object model.

            //Let’s get the set of Web applications installed with this service
SPWebApplicationCollection WebApps = SPWebService.ContentService.WebApplications;
//SPWebApplication Class represents an IIS load-balanced Web application installed on the farm
//Step through the Web applications
foreach (SPWebApplication WebApp in WebApps)
{
//Let’s check the Web application status before we begin and write each available Web application to the console
if (WebApp.Status.ToString() == “Online”)
Console.WriteLine(WebApp.DisplayName);
{
SPSiteCollection SiteCollections = WebApp.Sites;
//Step through the site collectons
foreach (SPSite SiteCollection in SiteCollections)
{
//Let’s check the site collection and determine whether a host header URL is the site name,
//if TRUE we’ll write the output to the console
if (SiteCollection.HostHeaderIsSiteName.Equals(true))
{
Console.WriteLine(“Site Url=” + “”” + SiteCollection.Url.ToString() + “”” + ” ContentDatabase=” + “”” + SiteCollection.ContentDatabase.Name.ToString() + “””);
}
}
}
}

This code snippet is provided under the Microsoft Permissive License.

Understanding the Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System Rule File

Since my original post Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System Available! , I’ve received several comments and requests for information on how to create custom rule definitions that can be used with the Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System. The article below references the basic steps required to create and use custom rule definitions.


The default rule definitions for the Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System are contained within sharepointbpa.config.xml.  sharepointbpa.config.xml is available in the directory specified during the installation.



 

The Object element defines the object to be parsed using the Type element. In the example above, the configuration database connection string is gathered from the Registry key SOFTWAREMicrosoftShared ToolsWeb Server Extensions12.0SecureConfigDBdsn as CONFIG_DB_CONN. The connection string is stored in the dsn key as Data Source=;Initial Catalog=;Integrated Security=True;Enlist=False. CONFIG_DB_CONN is used for variable substitution when executing T-SQL statements against the configuration database. Variable substitution defines the server name through the command line switch -substitution SERVER_NAME. 


In the Incoming E-Mail test (below), the connection string is passed to the statement as %CONFIG_DB_CONN% and the subsequent statement executed as select count(*) AS WSS_INCOMING_EMAIL from dbo.objects where classid = ‘b0bde0e6-6fb0-4f14-a93f-93170dc5b3ed’. This statement returns a numeric value of TRUE = 1 or FALSE = 0. If the result of the statement is less than 1 using the Query attribute value of Windows SharePoint Services 3.0 and the 2007 Microsoft Office System rule file.



 
   
 

The example below is a custom rule definition that verifies the installation of the SQL Server 2005 Reporting Services Web Part Package.



 
 

In the above example, I elected to obtain a count of Web Part packages whose title is equal to rswebparts.cab, if the result was less than 1, the rule is instantiated and my custom error definition rendered in the output report file.


Rule definitions can also include Registry checks for specific key values and/or name sets. The example below was taken from the default rule file packaged with the Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System.



 
   
 

The Object Type attribute defines the object to be parsed, in this case the Registry. The Key1 attribute is a substitution value that specifies the server name (see above). The attribtute, Key3, defines the path to be checked in the definition. In this example the path SYSTEMCurrentControlSetServicesWSSArpiPerformance is parsed and the Query attribute matches($.,’1′) applied to determine the existence of the key value Disable Performance Counters. If the value is present the rule is instantiated and written to the output report file.



 
   
 

In the above example, I elected to return the Windows SharePoint Services installation type from the Registry. Using the path SOFTWAREMicrosoftShared ToolsWeb Server Extensions12.0WSSSetupType in the Key3 and Key1 attributes the Query attribute matches($.,’CLEAN_INSTALL’) is applied and returns TRUE a value matching the text CLEAN_INSTALL, optionally this can be configured to return a result where the text does not match CLEAN_INSTALL. This is a fairly basic example of using rule definitions, but illustrates the rules definition capabilities that can be applied in the rule definition file.


The Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System can also be used to run checks against XML objects as shown in the example below taken from the default rule file packed with the Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System.


 
   
  
    $. and $. > 0″ Error=”Warning” Sev=”2″ Title=”Memory Cache Threshold is too Small” Text=”Memory cache threshold is set at {1}%. This threshold, together with the Maximum Private Bytes, determines the amount of caching that Excel Calculation Service within SSP {2} is able to provide. If you set the threshold lower than 30% cache performance is limited. We recommend that you configure this threshold to be greater than 30%.” P1=”$.” S2=”%SSP_DB_INSTANCE_NAME%” />
   
   
 

Among other object types that can be checked using the Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System are FILE, GROUP and METABASE types, the examples below were taken from the default rule file packaged with the Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System – I’m still experimenting with WMI so stay tuned!


     
       
       
         
         
       
      </Object>

     
       
       
       
         
         
           
           
         
       
      </Object>
    </Object>

A Blog Related Blog-Post

One of the most common and discouraging issues related to maintaining a blog or any other system permitting user-driven comments and moderation is the persistent annoyance of spam and related content. Unfortunately measures such as Completely Automated Public Turing test to tell Computers and Humans Apart (CAPTCHA’s) have become increasingly susceptible to being bypassed or relegated to a best-available security measure in the prevention of spam partially due to the increasing capability of technology and problem solving investment by hackers more commonly through the addition of a human workforce, often compensated, for solving various CAPTCHA puzzles. Another popular method is reusing the session ID of a known image or leveraging a client side image for translation and submission to the server. Recently while having some discussion on the topic, I found over in Microsoft Research they are working on a system known as Animal Species Image Recognition for Restricting Access or Asirra.  Asirra differs from CAPTCHA’s being a human interactive proof (HIP) by asking people to identity images of cats and dogs (courtesy of PetFinder.com).  The task of identification is particularly difficult for a computer, but is easily solved by people and supports a good cause.  Asirra uses the PetFinder.com database, consisting of over 2 million images, adding to its overall security and complex challenge-response system.  The best part about Asirra is it’s FREE!  For more information on the Asirra project visit http://research.microsoft.com/sn/asirra/FAQ.aspx.