I'm back.

I have resurfaced from the underground. My new blog is at http://www.sharepointmadscientist.com

SharePoint Customization Best Practices

At the SharePoint Best Practices Conference, there was quite a bit of interest in customization, specifically best practices for customization in SharePoint. The current bible on that topic was written by my buddy, Sean Livingston, a PM in the SharePoint PG. The doc can be found here: http://go.microsoft.com/fwlink/?LinkId=92311&clcid=0x409

SharePoint and Virtualization - Better Together!

I'm really excited about virtualization and think it's the best way to streamline deployments and get the most bang for buck out of your hardware. However, there are some important differences between virtual and physical environments that you need to be mindful of. I was hoping to get my virtualization guidance out in conjunction with MSFT's updated guidance, but I'm really under water right now and haven't had time to do the needed testing. Still, if you plan to deploy a production farm on virtual servers, make sure you follow the principles outlined in the following article: http://technet.microsoft.com/en-us/library/cc816955.aspx

Something else of note is that HW vendors are beginning to optimize their hardware for virtualization. Just check out this fact sheet about HP's BL 495c: http://www.hp.com/hpinfo/newsroom/press_kits/2008/virtualization/docs/fs_proliant.pdf

The SharePoint Best Practices Resource Center is live!

Check it out.

http://technet.microsoft.com/en-us/office/sharepointserver/bb736746.aspx

Improving SharePoint with SQL Server 2008

You might have noticed that SharePoint SP1 now supports SQL Server 2008 :-)

 http://blogs.msdn.com/sharepoint/archive/2008/08/15/sql-server-2008-support-for-sharepoint-products-and-technologies.aspx

http://blogs.msdn.com/mikewat/archive/2008/08/19/sql-server-2008-is-now-officially-supported.aspx

http://technet.microsoft.com/en-us/library/cc288751.aspx 

http://technet.microsoft.com/en-us/library/cc262485.aspx 

There is a vast array of new features in SQL Server 2008. While I would love to explore all of them, many are not relevant to the SharePoint admin since we are not allowed to modify the underlying schema. Also, keep in mind that while I did minimum testing to understand these features, no one (that I’m aware of) has any large scale perspective with SQL Server 2008 and SharePoint yet. This is not meant to be definitive guidance. That said, I will focus on the big wins for SharePoint and how to leverage them in your environment.

Intellisense

One of my favorite features is Intellisense in SQL Management Studio 2008. No more moving back in forth from query window to the object explorer trying to figure out what that column was called. No more select * queries to see the table. Those familiar with the Visual Studio IDE experience will be right at home. It’s awesome! Here’s a screenshot of Intellisense at work:

Compression, Compression, Compression.

Much has been made of SQL Server’s new compression features. There are actually three different compression scenarios in SQL now:

Database/Log Backup Compression

SQL Server 2008 Enterprise edition gives you the ability to compress backups. (though any SQL 2008 server can restore a compressed backup) This is probably the most usable compression feature of the lot, not just for backups, but for log shipping as well. Set the Compression flag in your backup script or set the server to compress by default and suddenly the size of your backups and shipped logs will be reduced. How much? Well, as with most technologies, it depends. SharePoint, due to its storage of blob data, is not the best compression candidate. That said, you can probably see up to 30% on your blob-laden content databases and perhaps 90 -95% on your other databases. Below is a screenshot of two database backups, one with compression and one without.

Not only will backups require less space, but backup duration will likely be reduced as well. This is because smaller backups require less IO’s and less time to write.

Backup Compression is quite simple to implement. The easiest way is to navigate to your SQL server’s server properties in management studio > Select Database Settings > and choose Compress Backup as seen here:

What’s great about setting the backup compression via server properties is that all backups including those made with the SharePoint UI or with STSADM will be compressed. (site backups excluded since they don’t actually backup databases)

The other way to backup using compression is to add the compression clause to your backup script. For example:

BACKUP DATABASE [<DATABASE_NAME>]

TO DISK = ‘E:\<DATABASE_NAME>.bak’

WITH COMPRESSION;

That’s it. Simple huh? Unfortunately, compression is not completely without thought. First, compression has no knobs. It’s on or it’s off. Great for simplicity, but for those used to third-party compression products and their infinite options might not be impressed. Talking with Kevin Farlee from the SQL PG, he mentioned that their testing of different compression levels showed higher and higher CPU utilization with very little benefit so it was decided that on/off with the best setting was the better approach. I recommend those using third-party compression tools with elaborate compression schemes, especially those who tune the size of the IO specifically for their environments, to continue using those products, but for a lot of folks “free” will be hard to pass up.

Lastly, compression requires CPU resources and in many environments CPU is a precious commodity, even during backup windows. How much CPU really depends on what is being compressed. Kevin Farlee mentioned that they observed 2% CPU usage in customer environments, but I doubt those environments included SharePoint. I’ve seen other compression tools taking far more CPU than that. My advice is to test this feature during a weekend backup window to see what affect it will have on your system before implementing across all backups. Straight from the horse’s mouth: (http://technet.microsoft.com/en-us/library/ms190954.aspx )

<By default, backing up using backup compression significantly increases CPU usage, and the additional CPU consumed by the compression process can adversely impact concurrent operations.>

The blurb goes on to say:

Therefore, you might want to create a low-priority compressed backup in a session whose CPU usage is limited by Resource Governor when CPU contention occurs. For more information, see How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).>

Intrigued? So am I, but for continuity sake, I’ll talk about resource governor later. There are still two more compression scenarios to discuss. There’s a really good blog post on backup compression here: http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

Database Mirroring Log Compression (Log Stream Compression)

One of the major performance limiting factors of database mirroring is network throughput between the principal and mirror servers. With 2008, SQL has introduced a feature to compress the log stream (the inserts/updates/deletes being transferred between servers), which should help maximize the network throughput, especially in environments where network conditions are poor. You should expect to see the same compression effectiveness as backup compression and possibly the same CPU overhead. Unlike backup compression; however, you can’t easily control this potential CPU hog. It’s on by default as soon as you start a mirroring session. There is one saving grace. You can disable log stream compression with traceflag 1462.

My advice to shops using database mirroring for SharePoint is to test before enabling log stream compression. You don’t want to be in a situation where your SharePoint environment tanks because SQL is pegged. If your SQL server ventures into the greater than 50% CPU utilization territory on a regular basis it would be safe to assume that log stream compression is not your friend although all is not lost. Simply add more CPU. (i.e. Scale up or out) The SQL CAT team has published an excellent blog entry on log stream compression: http://sqlcat.com/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx

Data Compression

SQL Server 2008 also introduces ways to reduce the size of your actual databases. These ways are called row compression and page compression. Unfortunately, these options are NOT SUPPORTED as they would require modification of SharePoint’s schema so I will not discuss them. For those interested, you can find out more here: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/11/12/types-of-data-compression-in-sql-server-2008.aspx

Transparent Data Encryption (TDE)

TDE takes SQL Server 2005’s encryption capabilities to the next level. In SQL Server 2008 Enterprise, TDE allows you to encrypt an entire database(s) and as its name suggests, TDE requires no application awareness or modification which means game on for SharePoint. Basically, the way it works is that data is encrypted as it’s written to disk and decrypted as it’s read from disk and it’s very simple to implement. Just 4 steps:

--Step 1. Create an encryption key.

USE MASTER;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘p@$$W0rd’

--Note: Password must meet complexity requirements if complexity is enforced.

--Step 2. Create a certificate

CREATE CERTIFICATE MYENCRYPTCERT WITH SUBJECT = ‘TDE’;

--Note: You should backup the cert with key and save to safe place.

--Step 3. Set you database(s) to use the certificate for encryption

USE <ContentDB>;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256 – There are a number of different algorithms to choose.

ENCRYPTION BY SERVER CERTIFICATE MYENCRYPTCERT;

--Step 4. Turn encryption on

ALTER DATABASE <ContentDB>

SET ENCRYPTION ON;

Though encryption is very simple to set up there are definitely performance ramifications of doing so. Encryption is a CPU intensive operation and in order to guarantee security, encryption has to be scoped to not only the encrypted database, but also the log, temp database, and all backups of the encrypted database as well. This means that on a busy system a lot of CPU will be used no matter how few databases you encrypt since the temp DB will also be encrypted. My Advice: Use encryption carefully. Only implement where it’s absolutely necessary after thoroughly testing the performance and operational ramifications. Since the tempDB will be encrypted I would recommend you build out a secure SQL server just for subsets of databases where security is super important and scale is not a factor. I would then charge your tenants big money for the privilege J

Other factors to consider when using TDE:

· Backup compression will be less effective on encrypted databases.

· TDE might not take advantage of multiple processors.

· TDE affects restore operations and disaster recovery since you will need the certificate to restore a database. No certificate means no restore!

· TDE affects database mirroring and log shipping. You need to install the certificate on the partner servers in order to mirror or log ship.

· FileStream data will not be encrypted so those thinking about external blob storage will have more to consider.

Learn more about TDE here: http://msdn.microsoft.com/en-us/library/bb934049.aspx

Better Mirroring

Outside of the log stream compression feature we discussed above, there are other database mirroring enhancements in SQL Server 2008. These enhancements should increase the reliability and performance of mirroring. I won’t go into the details of each enhancement, but you can read about them here: http://msdn.microsoft.com/en-us/library/cc645581.aspx

Resource Governor

Resource Governor is exciting! SQL Server 2008 gives you the ability to granularly control how your SQL Server resources (CPU and memory) are allocated. Resource Governor applies thresholds to incoming connections based on criteria identified by the administrator. For example, if you want to prevent your reporting application running on a separate server from consuming too much memory, you can create a policy that identifies that application by appname, hostname, or username. You can apply Resource Governor to any attribute that makes an incoming request unique. Now I know what you’re thinking. Before you get too excited allow me to step on my soapbox.

<soapbox>Resource governor should not be used to control SharePoint’s usage of SQL. If your Index server is really hitting SQL hard, that’s a capacity planning problem that needs to be addressed with additional capacity, not by artificially restricting critical services. Restricting SharePoint services, no matter how unimportant the service(s) may seem, could have serious implications on the health and supportability of your environment. If you need more capacity buy and build more capacity. </soapbox>

So if you shouldn’t use Resource Governor to ratchet down SharePoint, where should you use it? Maintenance and administration. I can’t tell you how many times I’ve seen maintenance impacting server performance and health. I’ve seen backups pegging CPU and running outside of maintenance windows and DBCC’s running unmonitored doing the same. I’ve seen administrators tank SQL with bad queries during the day. Resource Governor to the rescue.

For example, let’s say that you want to ensure that your operations folks don’t tank your SQL server with ad-hoc queries using Management Studio or Query Analyzer. To do so, let’s prevent ad-hoc queries from consuming more than 25% CPU.

Step 1. Create a resource pool to limit CPU usage to 25%.

CREATE RESOURCE POOL poolAdhoc

WITH (MAX_CPU_PERCENT = 25);

Step 2. Create a workload group for ad-hoc queries and register it with the new resource pool

CREATE WORKLOAD GROUP groupAdhoc

USING poolAdhoc;

Step 3. Create a function that classifies Management Studio and Query Analyzer as members of the ad-hoc group.

CREATE FUNCTION adhocQueryClassifier() RETURNS SYSNAME 

WITH SCHEMABINDING

AS

BEGIN

    DECLARE @grp_name AS SYSNAME

      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')

          OR (APP_NAME() LIKE '%QUERY ANALYZER%')

          SET @grp_name = 'groupAdhoc'

    RETURN @grp_name

END

GO

Step 4. Register the new function with the Resource Governor

ALTER RESOURCE GOVERNOR 

WITH (CLASSIFIER_FUNCTION= dbo.adhocQueryClassifier);

Step 5. Restart Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;

That’s it! Now queries originating from SQL Management Studio or Query Analyzer will be allocated to poolAdhoc and if CPU becomes constrained, those queries will be restricted to using no more than 25% CPU. When CPU is not constrained, Resource Governor is smart enough to allow the query to consume needed resources without restriction. Resource Governor can also be used to allocate minimum resources too. To learn more about Resource Governor visit http://msdn.microsoft.com/en-us/library/bb933866.aspx.

More Insight

If you are like me, analyzing SQL health and performance can be a dizzying experience. There’s so much to know and so many different tools to use. That’s why I was super excited about SQL Server 2005 SP2 Performance dashboard reports and now really excited about the upgrade to the 2008 Performance Studio.

Performance Studio takes the dashboard reports to the next level. It’s powerful stuff. Luckily, Rob Carrol, a UK based PFE, has already done the hard part for us and posted a really informative blog on the Performance Studio. Check it out here: http://sqlblogcasts.com/blogs/thepremiers/archive/2008/06/20/sql-server-2008-performance-studio.aspx

In addition to Performance Studio, there was also a number of new performance counters added which should help you better analyze your niche and not so niche scenarios. There are way too many to list, but I do want to call out for those using database mirroring there are some really good additions that should help you better understand what mirroring is doing. Paul Randle has a great post on these enhancements at http://www.sqlskills.com/blogs/paul/2007/10/01/SQLServer2008NewPerformanceCountersForDatabaseMirroring.aspx

Summary

All in all, SQL Server 2008 is a major improvement over SQL Server 2005 and includes many compelling reasons to upgrade. While not all new features can be used in SharePoint, the ones that can will definitely improve SharePoint’s operability, survivability, and security.

Giving Credit Where Credit is Due

This has been the most collaborative blog post I’ve ever written. I had a lot of help from folks from all over Microsoft and the blogosphere to ensure you got the best information. I wanted thank the following people for their contributions:

Gabe Bratton – SharePoint Supportability

Simon Skaria – SharePoint CAT

Lindsey Allen – SQL CAT

Kevin Farlee – SQL PG

SQL Server 2008 is now officially supported!

Microsoft has announced SharePoint Products and Technologies 2007 (SP1) support for SQL Server 2008. See http://technet.microsoft.com/en-us/library/cc262485.aspx for MOSS and http://technet.microsoft.com/en-us/library/cc288751.aspx for WSS. (look towards the bottom)

 See my posts on upgrade http://blogs.msdn.com/mikewat/archive/2008/08/11/upgrading-sharepoint-sql-servers-to-sql-server-2008.aspx and new features http://blogs.msdn.com/mikewat/archive/2008/08/19/improving-sharepoint-with-sql-server-2008.aspx

I’m Speaking at the SharePoint Best Practices Conference

It’s official. I’ll be at the SharePoint Best Practices Conference September 15^th – 17^th in Washington DC. This will be a great conference. There are a lot of great speakers and sessions and the focus on best practices will cut out all the fluff. If you are an ITPro focused on SharePoint/SQL/Storage design, availability, performance, or administration come check out my sessions on:

Capacity Planning and Performance Tuning - Mike Watson

(ITP433, CIO433, PM433)

The topics of capacity planning and performance monitoring will be presented together because much of the foundation is the same. Performance counters that are used for testing a farm's capacity are also used to monitor the performance of the farm. In fact, some of the capacity planning metrics will help define the baseline used for performance monitoring. For example, we will show you how to test the capacity of your farm for Web requests using performance counters. We will then use those performance tests results to define the baseline for monitoring those servers for adequate performance. You will be presented with best practices for Hardware and software boundaries, Planning using System Center Capacity Planner 2007, Testing using Visual Studio 2008, and Monitoring using native and 3rd party tools.

Best Practices for Data Protection, Recovery, and Availability - Mike Watson

(ITP432, CIO432, PM432)

If Server 2007 is a critical component in an organization's infrastructure, it is imperative to properly plan, design, implement, and maintain a data protection, recovery, and availability plan. Simply backing up and restoring via Central Administration is insufficient to fully restore data in all but the simplest implementations. Because SharePoint Server 2007 usually consists of multiple, interconnected systems and dependencies, we will focus on the planning and designing processes first, and then focus on the individual components that make up a SharePoint Server 2007 server farm. The following topics will be presented: Planning for recovery and availability, Designing for high availability, Backup and restore strategies, and Recovering from disasters.

Intranet, Extranet, and Internet Best Practices - Ben Curry/Mike Watson

(ITP431, CIO431, PM431)

Web application best practices are the foundation for any Intranet, Extranet, or Internet SharePoint Server 2007 implementation. Many best practices, including content database, Internet Information Services, Web application policies, authentication, client integration, and farm topologies, will be discussed along with some possible scenarios to begin and refine your design. In addition, we will discuss best practices for sharing information over the Internet for collaboration, as well as Internet-facing sites for anonymous users.

IT PRO Experts Panel - Bob Fox/Mike Watson/Daniel Webster/Tom Wisnowski

Good luck trying to find a problem that this team can not fix.  Bring your trickiest problems and let these top experts work with you on them.

Upgrading SharePoint SQL Servers to SQL Server 2008.

SQL Server 2008 is now released and is supported by WSS 3.0 and MOSS 2007 SP1 and above although we probably won't see a supportability statement anytime soon due to resource constraints with the content folks. I will let you know when the official supportability statement is released.

[Update] - Support has been annouced. See http://technet.microsoft.com/en-us/library/cc262485.aspx for MOSS and http://technet.microsoft.com/en-us/library/cc288751.aspx for WSS. (look towards the bottom)

Upgrade from 2005 to 2008 is a pretty simple process. Here are some things to be aware of when using Upgrade Advisor. If you run upgrade advisor (recommended for SQL servers not dedicated to SharePoint 2007) you will see the following warnings in the report. These warnings can safely be ignored:

·         Full-Text Search has changed in SQL Server 2008 – SharePoint 2007 no longer uses Full Text Search and will not be affected by these changes.

·         Column aliases in ORDER BY clause cannot be prefixed by table alias – This is flagged on each instance of the proc_GetTpPageMetaData stored procedure in each content database. Though this issue suggests that this stored procedure will not work correctly in SQL 2005 and SQL 2008, it apparently does and can be ignored.

·         Other Database Engine upgrade issues – The upgrade advisor doesn’t check for all possible upgrade and compatibility issues. This can be ignored for SharePoint 2007 databases.

 Here are some additional things to be aware of when building out a new farm or provisioning new services on SQL server 2008.

·         SSRS and MOSS Report Center – On servers where MOSS is installed on top of SQL server 2008 with Reporting Services the potential for a URL conflict exists since they can both end up with the same url. The workaround would be to use a non-default web site for hosting MOSS. You can distinguish the sites using and IP address, Host Header, or Port.

·         Least Privilege Deployments and WSS 3.0 – There is at least one known issue where provisioning a new web application will fail if content access account is running without sufficient permissions to the new database. I haven’t reproduced this or tested a workaround, but I imagine that if you temporarily give the content access account sys admin perms on the SQL Server you will avoid this error.

You will need to install .Net 3.5 SP1 and hotfix KB942288-v4 (Windows Installer 4.5) - Update services will be stopped and started during the install. These installs will likely require a reboot. One will wait for the other to complete before prompting to reboot. (two installs - one reboot)

Run setup.exe again after reboot and choose installation > upgrade from SQL Server 2000 or SQL Server 2005 and follow the prompts. I imagine a resource constrained or large SQL instance will take some time to upgrade. I did not notice any loss of availability with my databases during the upgrade which is good since my upgrade took almost 4 hours per instance, not including the prerequisites install. I have a feeling it took so long because of the lack of memory on my virtual server. (2GB) There was lots of paging occurring during the upgrade. The upgrade process itself seems lightweight, never consuming more than a few percent of the proc and about 30MB of memory.

If using database mirroring, upgrade the mirror and witness instances first. If running a mirroring split (principal databases on both instances) fail over to one node or the other. By upgrading the mirror and witness first you will ensure mirroring continues to work during the upgrade and you will minimize downtime due to the mandatory reboot. Make sure to upgrade the witness server and mirror server before attempting a failover. Else, the failover will fail and you will end up with unprotected databases and worse, you will need to break mirroring to bring your principal databases online.

All in all, the upgrade from SQL 2005 to SQL 2008 is a straightforward process. While I recommend you test the upgrade process I doubt you will find any surprises on a dedicated SharePoint backend. I hope that IT shops fast track this upgrade so we can focus on and take advantage of the new feature set in 2008. I plan to talk about how we SharePoint folks can leverage those new features in a post in the near future. [Update] See http://blogs.msdn.com/mikewat/archive/2008/08/19/improving-sharepoint-with-sql-server-2008.aspx for information on new features.

SQL Mirroring Setup Made Easy

--

-- Step 1 -- Run this command on the principal, mirror, and witness

--

-- Create the endpoint 

CREATE ENDPOINT Endpoint_Mirroring -- name

STATE=STARTED -- Endpoint will be started and ready

AS TCP(LISTENER_PORT=5022 -- Endpoint will use port 5022

, LISTENER_IP=ALL) -- Endpoint will listen on all IP addresses

FOR DATABASE_MIRRORING -- Specifies mirroring as the endpoint purpose

(AUTHENTICATION = WINDOWS[Negotiate] -- Will negotiate NTLM or Kerberos for authentication

, ENCRYPTION = SUPPORTED, -- Mirroring traffic will be encrypted

ROLE=ALL); -- This endpoint can be principal, mirror, or a witness



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

-- Step 2 -- Run on Principal

--

-- Set all the databases to use full recovery.

-- Caution - Full recovery requires additional logging space

-- Run this on the principal BEFORE backup

-- Select the output of this query, paste, and run in the query window to execute.

-- this query will set all databases to use full recovery except system databases (master, temp, msdb, and model)

Select 'USE MASTER; ALTER DATABASE [' + name + '] SET RECOVERY FULL;' from sys.databases where database_id > 4



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

-- Step 3 -- Run on principal

--

-- Backup all databases

-- Select the output of this query, paste, and run in the query window to execute.

-- This will backup all databases on the server except the system databases (master, temp, msdb, and model)

-- Make sure all databases are in full recovery mode before backup

-- Replace <BackupLocation> with the appropriate local backup path. Format C:\dir\

SELECT 'BACKUP DATABASE [' + name + '] TO DISK = ''<BackupLocation>' + name + '.bak'' WITH FORMAT;' from sys.databases where database_id > 4



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

-- Step 4 -- Run on principal

--

-- Backup logs

-- Select the output of this query, paste, and run in the query window to execute.

-- This will backup the log for all databases on the server except the system databases (master, temp, msdb, and model)

-- Replace <BackupLocation> with the appropriate local backup path. Format C:\dir\ 

SELECT 'BACKUP LOG [' + name + '] TO DISK = ''<BackupLocation>log_' + name + '.bak'' WITH FORMAT;' from sys.databases where database_id > 4



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

-- Step 5 -- Run on the query generator on the principal

-- Run the actual queries on the mirror server

-- Make sure to copy the backups to the mirror before restoring.

--Restore Databases

-- Select the output of this query, paste, and run in the query window to execute.

-- This will restore all databases from the principal.

-- Replace <BackupLocation> with the appropriate local backup path. Format C:\dir\ 

SELECT 'RESTORE DATABASE [' + name + '] FROM DISK = ''<BackupLocation>' + name + '.bak'' WITH NORECOVERY;' from sys.databases where database_id > 4



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

-- Step 6 -- Run on the mirror server

-- Make sure to copy the log backups to the mirror before restoring.

--Restore logs

-- Select the output of this query, paste, and run in the query window to execute.

-- This will restore all logs from the principal.

-- Replace <BackupLocation> with the appropriate local backup path. Format C:\dir\ 

SELECT 'RESTORE LOG [' + name + '] FROM DISK = ''<BackupLocation>log_' + name + '.bak'' WITH NORECOVERY;' from sys.databases where database_id > 4



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

-- Step 7 -- Run on the mirror server FIRST!

--

-- Replace <principalserverfqdn> with the fully qualified domain name of the principal server

-- Select the output of this query, paste, and run in the query window to execute.

SELECT 'ALTER DATABASE [' + name + '] SET PARTNER = 'TCP://<principalserverfqdn>:5022';' from sys.databases where database_id > 4



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

-- Step 8 -- Run on the principal server. Run After Step 7!

--

-- Replace <mirrorserverfqdn> with the fully qualified domain name of the mirror server

-- Select the output of this query, paste, and run in the query window to execute.

SELECT 'ALTER DATABASE [' + name + '] SET PARTNER = 'TCP://<mirrorserverfqdn>:5022';' from sys.databases where database_id > 4



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--

-- Step 9 -- Run on the principal server, Run After Step 8!

--

-- Replace <witnessserverfqdn> with the fully qualified domain name of the witness server

-- Select the output of this query, paste, and run in the query window to execute.

SELECT 'ALTER DATABASE [' + name + '] SET WITNESS = 'TCP://<witnessserverfqdn>:5022';' from sys.databases where database_id > 4

-- That's it!

-- High Availability mirroring is now setup.

SharePoint 2007 Infrastructure Update Released

The SharePoint 2007 Infrastructure Update is a must have install including numerous performance and stability hotfixes. Make sure to install this one. For more info see the SharePoint team blog

Content Deployment Rollup has been Released

If you are using content deployment (aka prime) you will be happy to know that the rollup has been released as part of the May 2008 Hotfix package. Find out more here.

WSS: http://support.microsoft.com/kb/952698

MOSS: http://support.microsoft.com/kb/952704/

Quick Blurb on SharePoint Deployment Best Practices

SharePointJoel, Doron Bar Caspi, and I were interviewed by TechTarget after our *dizzying* session on Geo-distributed SharePoint Deployments at TechEd 08. I'm not sure where Joel's and Doron's audio ended up, but they included portions of my interview in their Windows Week In Review News From Microsoft TechEd 2008.

You can access the audio directly at http://windowsnews.blogs.techtarget.com/podpress_trac/web/92/0/Windows_Week_in_Review_2008_061308.mp3 

Not much meat there, but I figured this might be helpful for someone. (maybe)

SharePoint Reporting - What's Up?

Joel and I had a lot of fun doing this video for TechEd Online about the state of SharePoint reporting.

 SharePoint Reporting—What's Up? 

Key Points about SharePoint 2007 reporting.

• Project and team based site collection administrators will find out of the box reporting adequate in most situations.
• Search administrators may be surprised by the out of the box search reporting. A. It exists. B. It has some good stuff.
• Portal administrators who are used to very robust metrics may not find the droids they are looking for and may want to create or purchase something.
• Farm administrators won't find anything interesting out of the box and will need to create or purchase something to get the info they need.
• For farm administrators, there's the MSIT reporting framework available on codeplex. http://blogs.technet.com/corybu/archive/2007/10/09/msit-sharepoint-reporting-framework-2-0-released.aspx
• Quest Software has a capacity management tool with some rich reports. http://www.quest.com/capacity-manager-for-sharepoint/
• Quest also has the Site Administrator for SharePoint which in addition to it's administrative capabilities provides lots of awesome reports. http://www.quest.com/site-administrator-for-sharepoint/
• Nintex has released its Nintex Reporting 2008 suite which contains some informative and downright eye-pleasing reports targeted at specific audiences. http://www.nintex.com/NProducts/Reporting.aspx 
• The new kid on the block (at least new to SharePoint) is Idera with their Point Admin Toolset (beta). (Can we work on that name?) http://www.idera.com/Products/SharePointToolset/Default.aspx 
• Others? Let me know and I will check you out and update this post.

If you want to roll your own reporting solution here are some things to keep in mind:

• SharePoint parses the IIS logs and rolls up the information into its own logs. That information is then stored in the content database.
• SharePoint won't report anything unless A. IIS is logging and B. Usage analysis is enabled and has enough time to process the logs.
• Search reporting must be enabled in the SSP settings.
• It's best not to touch the SharePoint databases directly. Modification is a huge no-no and will result in loss of support.
• It may be OK to read from the database in certain situations. Performing one time queries during off hours should be OK. Building a reporting solution that rolls information into a separate warehouse may also be OK to if done right; however, keep in mind that queries can affect your availability, performance, and the underlying schema may change at any time without warning. MSFT in no way supports direct database reads or writes. If you are not a SQL guru who can design lightweight queries stay out of the database!
• The content database contains some accessible information on storage, bandwidth, and discussion usage and some info on hits and visits.
• The content database also contains some rich information on each web, but this information is stored in binary form and there's no information available on how to read it. The best way to access that information is through the published APIs. Check out the SDK for more information. http://msdn.microsoft.com/en-us/library/ms441339.aspx
• Farm admins will find the data available through the OM very useful, but keep in mind that enumerating this information on a per site basis across a large farm will take a long time and may impact performance if done poorly.
• You can parse the IIS logs directly if needed. Log parser 2.2 rocks! http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
• It's not always easy to determine what users are doing from the IIS logs. Many operations are not logged in a meaningful way.

In summary, SharePoint 2007 out of the box reporting is big improvement over 2003. Portal and farm administrators may want more info and should look into third party solutions or rolling their own solution; however, when rolling your own be sure to do in a supportable way that doesn't impact farm performance.

The Canadians are Coming!

My new buddy, and PFE extrordinare from Canada, Roger Cormier is blogging now. I'm certain he will have some good stuff soon so bookmark this one.  

http://blogs.msdn.com/rcormier/

I Made the List.

I'm number 65!

 http://www.sharepointjoel.com/Lists/Posts/Post.aspx?ID=31

 Wow! That's super especially considering how neglected my blog has become since joining my new team. Regardless, I look forward to posting some good stuff in support of my upcoming sessions at TechEd. (see below) See you there!

OFC373 Planning and Implementing Global Microsoft Office SharePoint Server 2007 Deployments

    Wednesday, June 11 10:15 AM - 11:30 AM, S220 E 

Speaker(s): Doron Bar-Caspi, Mike Watson

Level: 300 - Advanced

Session Type: Breakout Session

Global deployments can be complex if you don't know what really matters. This session focuses on network performance, solutions, and partners with information from real global SharePoint Server 2007 deployments.

OFC374 High Availability and Disaster Recovery for Microsoft SharePoint Products and Technologies

    Wednesday, June 11 4:30 PM - 5:45 PM, S320 C 

Speaker(s): Mike Watson

Level: 300 - Advanced

Session Type: Breakout Session

 

OFC357 How to Plan for SharePoint Storage Management for Multi-Terabyte Deployment

    Friday, June 13 4:30 PM - 5:45 PM, S220 E 

Speaker(s): Doron Bar-Caspi, Todd Klindt, Mike Watson

Level: 300 - Advanced

Session Type: Breakout Session

This session covers key recommendations and best practices to help administrators of large SharePoint farms to plan and monitor their Microsoft SQL Server storage requirements to support optimal performance and operation of their server farms.