Thursday, May 31, 2007

HOL SOA21 - WCF Contracts

As mentioned earlier, I am in the process of QA testing some of the Hands On Labs for TechEd 2007. I am currently working on SOA21, which is a very good Windows Communication Foundation (WCF) lab. Unfortunately, it expects the attendee to have a good understanding of .NET 3.0 concepts as well as a good grasp of C#, so it may be somewhat tough for people to get through.

In exercise 2 Task 1, you are asked to rebuild the IMath interface, and you need to ensure that you use the new data contract that was constructed. The complete syntax for this is:

public class MathService : MathTypes.IMath
public MathTypes.MathResponse Add(MathTypes.MathRequest req)
return new MathTypes.MathResponse(req.x + req.y);

HOL SOA19 - Workflow Enabled Services - Sample Code

I am spending this week doing some QA on the Hands on Labs for TechEd. I will be posting additional info about the labs as I go through them. One thing I have noticed is every once in awhile the sample code is actually missing from the lab. For example, SOA19 has you create a rather lengthy class to assist the lab. Here is the missing sample code:

public static string NumToStr(decimal inputValue)
string[] magnitudeNames = { "", "Thousand", "Million" };
string returnValue = string.Empty;

if (inputValue > Decimal.MaxValue )
throw new ArgumentOutOfRangeException(
"inputValue", "The input value is too large.");

inputValue = Math.Abs(inputValue);
long dollars = System.Convert.ToInt64(Math.Floor(inputValue));
int cents = System.Convert.ToInt32((inputValue - dollars) * 100);

if (dollars > 0)
int tempDollars = 0;
int groupIndex = 0;
tempDollars = System.Convert.ToInt32(dollars % 1000);
dollars = System.Convert.ToInt64(
(Math.Floor((decimal)(dollars / 1000))));

if (tempDollars != 0)
returnValue = String.Format("{0} {1} {2}",
while (dollars > 0);
returnValue = string.Format(
"{0} and {1}/100", returnValue.TrimEnd(), cents);
return returnValue;

private static string HandleGroup(int valueToConvert)
string[] onesNames =
{ "", "One", "Two", "Three", "Four", "Five",
"Six", "Seven", "Eight", "Nine", "Ten",
"Eleven", "Twelve", "Thirteen",
"Fourteen", "Fifteen", "Sixteen", "Seventeen",
"Eighteen", "Nineteen", "Twenty" };

string[] tensNames = { "", "", "Twenty",
"Thirty", "Forty", "Fifty", "Sixty",
"Seventy", "Eighty", "Ninety" };
string result = string.Empty;

int digit = valueToConvert / 100;
valueToConvert %= 100;
if (digit > 0)
result = onesNames[digit] + " Hundred";

int selectVal = valueToConvert;
if (((1 <= selectVal) && (selectVal <= 20)))
result += onesNames[valueToConvert];
else if (((21 <= selectVal) && (selectVal <= 99)))
digit = valueToConvert / 10;
valueToConvert %= 10;

if (digit > 0)
result = string.Format(
"{0} {1}", result, tensNames[digit]);

if (valueToConvert > 0)
result = string.Format(
"{0}-{1}", result, onesNames[valueToConvert]);
return result;

Saturday, May 26, 2007

Off to TechEd 2007!

Yes, the title and date are correct. I am off to TechEd a week early because I am privileged to work the show as a Technical Learning Guide (TLG) lead.

If you're going to be at TechEd, stop by the DEV pod in Hands on Labs (HOL) area and say hello..

Monday, May 21, 2007

Analysis Services 2005 Performance Tuning

Microsoft has published an update to the very well done Analysis Services 2000 performance tuning guide for Analysis Services 2005. This guide basically walks you through all of the things that you need to consider when working with large scale AS applications. The document is 90 pages, but well worth the read. Download it here.

Thursday, May 17, 2007

MDX Script Performance Analyzer

Chris Webb has recently posted a tool on Codeplex that should really help ANYONE who is trying to performance tune MDX queries. (Holy COW is that me right now!). The MDX Script Performance Analyzer can be found here.

In a nutshell, what this tool does is tell you what the most expensive calculations in your MDX script are.

This is a great tool!

Wednesday, May 16, 2007

SharePoint 2007 Performance

I have recently spent a lot of time working with Microsoft Office SharePoint Server (MOSS) 2007 and Excel Services. I can honestly say that MOSS is much easier to work with and much more feature rich than it's predecessor, but there is certainly a lot to think about with respect to capacity planning.

In my current project I am working exclusively with the Business Intelligence aspects of SharePoint and am relying on SharePoints capability to deploy dashboards and KPI lists that use a combination of Excel workbooks and SSRS reports that use connections to SSAS cubes. It turns out that Excel Services requires a fair amount of processing power, which, coupled with the base requirements for MOSS, means that we require significant hardware to deploy the solution even for a small number of users. The following diagram represents the logical layout of our single server deployment:

According to Microsoft, this configuration is not recommended for a production system. I can certainly see why, given the performance requirements.

One thing that we have run across on a regular basis is that MOSS tends to really fragment memory, and even on systems with a fair amount of available memory, SharePoint and Excel Services tend to require contiguous blocks of memory, which can quickly become constrained if access to Excel Services is requested by more than a one user. In order to alleviate this problem, the HeapDeCommitFreeBlockThreshold registry setting (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager) needs to be changed to 0x00040000 in order to ensure that the garbage collector keeps up with memory requests.

Fortunately for us, Microsoft has realized that MOSS performance and scalability is a concern for many organizations and have begun to publish very good information on MOSS deployment. I just wish that Microsoft Learning would realize that they need to publish some good MOSS deployment courses.

Take a look at the TechNet article on SharePoint capacity planning.

Tuesday, May 15, 2007

Microsoft BI Website

In case you've missed it, Microsoft now has a new "Business Intelligence" portal. Check out:

Shift Happens

Lynn Langit has posted an interesting presentation over on her blog. Check it out here.

Monday, May 14, 2007

Unit Testing Whitepaper published

Sachin Rekhi (Microsoft Team System for Database Professionals team) recently posted a very well done whitepaper on Database Unit testing using Team System for Database Professionals. Check it out here.

Data Dude Service Release

Microsoft has recently released Service Release 1 for Visual Studio Team System for Database Professionals. Read about it in KB 936202.

Gert Drapers has posted some information on exactly what it contains in his blog entry.

Wednesday, May 9, 2007

SQL Server 2005 Performance Reports

In their never-ending quest to make the life of a SQL DBA easier (please, don't laugh, it's really true!), Microsoft has recently released a series of custom reports that plug into SQL Server Management Studio (SSMS) that help to track down performance-related issues with SQL Server 2005 (You can download and read about them here). I recently had the opportunity to work with these reports first-hand in a production environment, and while I am normally very skeptical of such things, I have to admit that this toolset really impressed me.

Installing the Performance Reports

The installation of the Performance Reports package is relatively straightforward, although you do have to perform a couple of steps:

  1. Download the SQLServer2005_PerformanceDashboard.msi file and execute it. This will unpack a bunch of report RDL files and sql scripts into your SQL Server tools folder (default is C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard).
  2. Execute the setup.sql file on each server instance that you plan to monitor with these reports. (Note: You do not need the RDL files on each server)

Using the Performance Reports

Once the reports are loaded, you access them by right-clicking on a server in the object explorer inside SSMS and selecting Reports/Custom Reports. Then browse to the installation folder (Note: You may not see any files when you browse in management studio due to the fact that it might be looking for *.rdlc files instead of *.rdl – To solve this, simply type *.rdl in the filename box) and select "Performance_Dashboard_Main.rdl". This will open the main dashboard as shown below:

Each of the items in the dashboard is capable of drilling down into more detail. For example, in my case, when I selected the "Current Waiting Requests" graph, I was presented with the following:

Notice that the report shows the specific query that is causing the wait in this case. Clicking on the query brings up the execution plan as follows:

Note that the report shows that there are identified missing indexes that could help the performance of the query. Clicking on the "View Details" link brings up the following report:

Which can now be used to create the missing index that should help the performance of that particular query. (The reality is you want to perform a more complete index analysis using the tuning wizard and a captured workload, but this is a good start!)

Now, I know not everyone is the SQL Geek that I am, but come on, this stuff is COOL!

Thursday, May 3, 2007

SQL Server 2005 Incremental Servicing Model

Microsoft has recently announced a new methodology for releasing hotfixes for SQL Server called the "Incremental Servicing Model". Basically what it means is that hotfixes for SQL Server will now be delivered on a regular schedule, instead of as priority requires.

Under this new methodology, SQL Server "cumulative update" patches will be released every 2 months.

Read more about this here:

Wednesday, May 2, 2007

SharePoint and Forms Authentication

I have recently had the need to work with SharePoint (actually Windows SharePoint Services – WSS 3.0) 2007 and ASP.NET Forms authentication. For something that in the end turns out to be simple to configure, I had a heck of a time locating viable information through MSDN and the blogosphere.

So, with that in mind, I thought I would create a very simple step by step guide to help those who find themselves in the same boat (although I know nobody reads this blog anyway)

Configuring SharePoint 2007 / WSS 3.0 to use Forms Auth

One disclaimer that I'll give here is that I am going to give instructions for the simplest method of configuring MOSS/WSS to use forms auth. The end-result is a global configuration and it may not be the best solution for your particular environment, nor is it a particularly smart/secure idea. With that said, you can always expand upon the ideas presented here to customize the solution for your environment.

Step by step instructions are as follows:

  1. Install and configure MOSS/WSS using whatever configuration you deem fit. (In my case, I needed a bare-bones default installation of WSS, but have also tested these steps with SharePoint 2007 Enterprise Edition)
  2. From the Windows\Microsoft.Net\Framework\v2.0.50727 folder, execute aspnet_regsql
    1. Choose Configure SQL Server for Application Services
    2. Use the default database (which will create a database called aspnetdb on whatever instance you choose in the wizard)
    3. Once the wizard is complete, use SQL Server Mgmt Studio to grant access to the user that will be the security principal for the IIS Application Pool that WSS/MOSS will use. (By default it will be NT AUTHORITY\NETWORK SERVICE)
  3. Open the machine.config file from Windows\Microsoft.Net\Framework\v2.0.50727\CONFIG
    1. Locate the <connectionStrings> element
    2. Replace the connectionString attribute for "LocalSqlServer" with an appropriate string that points to the database you created in step 2
  4. In SharePoint Central Administration, create a new web application
    1. Use the default NTLM authentication
    2. Once done, ensure you restart IIS (use IISRESET /restart from a command prompt)
  5. Create a new Site Collection using the web application you created in step 4
    1. Ensure you assign a Windows account as the site administrator (You should test the site before changing authentication types, so you'll need an account that can access the site)
  6. Ensure the new site works by browsing to it
  7. Open SharePoint Central Administration Application Management
    1. Select Authentication Providers and ensure you select the correct web application (the one you created in step 4)
    2. Set the Authentication Type to "Forms"
    3. Set the Membership Provider to "AspNetSqlMembershipProvider" (It is imperative that you spell this correctly – you can cut/paste from machine.config <membership><providers> element if necessary)
    4. Once you save the configuration, restart IIS
  8. Test the new authentication type
    1. Open the site in the browser. If all is working correctly, you will be presented with SharePoint's default ASP.NET login screen
    2. Try to login with any user/password combination. It should fail and return you to the login screen
  9. Add users to the aspnetdb database
    1. The easiest way to do this is through Visual Studio's ASP.NET web configuration utility
      1. Create a new ASP.NET website project
      2. Don't change anything and build the project
      3. From the Website menu, choose "ASP.NET Configuration"
      4. Once the tool loads, choose "From The Internet" in the authentication column
      5. Add users
  10. Test the site again
    1. Choose a valid username/password combination
    2. You should be able to login, but not access the site (SharePoint will tell you that you don't have access)
  11. Open SharePoint Central Administration Application Management
    1. In the SharePoint Site Management section, add a user as a Primary Site Administrator (Choose a user you added in step 9)
    2. In the Application Security section, add any users to the Site Policy as necessary

Once these steps are followed, you should be able to enjoy WSS/MOSS with forms authentication.

I hope these steps have proven useful.