You Can’t Specify the Outbound IP Address in ISA Server 2006

I just spent several hours attempting to set my outbound SMTP traffic from Exchange 2007 to use a specific IP on my ISA Server 2006 external NIC of the 5 available.  I wanted to isolate mail traffic to a specific IP address.  However, it turns out that you cannot send traffic over a specific IP in ISA 2006 for any protocol, unless you install another NIC with the IP address that you want to send on.  Here is a blurb that was posted by someone on Experts-Exchange after they spoke with Microsoft support on this issue (Not linking since you won’t be able to see the link):

'No version of ISA can, to date, select the IP address that will be used for outbound NAT addressing - only inbound as I have mentioned before. This facility WILL be introduced in ISA 2008 either in the beta release or in the full version. Yes, you can use an additional NIC with a public IP and route mail through that connector but long term, this is not a scaleable option'.

Virtual PC 2007 Laptop Install Mouse Hesitation Fix

If you are running Virtual PC 2007 on a laptop and are experience mouse stutter or hesitation (no matter how much memory or how powerful your laptop), there is a fix.  Here is a link to the explanation, but the short version is find the options.xml file on your C:\ drive (in my case it is in c:\Users\Josh\AppData\Roaming\Microsoft\).  Add the following section immediately after the last </virtual_network> tag:

   <virtual_machines>
    <enable_idle_thread type="boolean">true</enable_idle_thread>
  </virtual_machines>

Links to Setting up Kerberos for SharePoint 2007

Posting this for my own future reference: http://bimvp.com/blogs/bsm/archive/2008/07/01/configuring-kerberos-for-sharepoint-2007.aspx

Useful Visio stencil set for SQL Server 2005

I am putting this here for my own future reference.  There is a useful set of Visio stencils for SQL Server 2005 at Visio Toolbox.  Here is a link to the page that they are on, and a direct link to the .zip file.  These stencils appear to have actually been created by Microsoft directly, since the page is on a Microsoft website.

There is also a page on the site that shows how to work with Visio and SQL Server.  There are several videos and a couple of downloads.

Post SR1 Rollup Package 1 for Data Dude available

If you are using Visual Studio 2005 Team Edition for Database Professionals (Data Dude), you might be surprised to know that there is a post-SR1 Rollup Package 1 update available.  It can be download here.  The list of fixes can be found here, and I have pasted it in below:

Inconsistent revalidation of database object definitions when you close and then reopen a Visual Studio database project

A database project cannot be deployed if the project contains interdependent inline functions

If you use a four-part name to reference a table, you may receive an incorrect syntax exception

Database Professional Interpreter does not handle the "sp_executesql" parameter correctly

In a Visual Studio 2005 database, you cannot import a schema that was created by using the SQL Server 2000 compatibility mode

Error message when you use Design-Time Extensibility (DTE) objects: "Do you want to reload"

Cross-database references cause slow performance when you load a project

Schema Compare crashes the Visual Studio IDE when databases contain different versions of assemblies

Schema Compare does not correctly generate the update script for a stored procedure

The schema script is not generated when triggers or views that have the same name exist

Error message when you build a deployment script: "MSB4018: The 'SqlBuildTask' task failed unexpectedly"

After Schema Compare obtains the latest project files from Source Control, Schema Compare indicates that databases are different

How to fix Hyper-V error “Failed to add device Microsoft Synthetic Ethernet Port”

Hyper-V Beta, and RC0 worked great.  I recently upgraded to RC1, and in the process of both trying to move a VHD from another server and trying to create a new VM from scratch I repeated received the following error:

Failed to add device ‘Microsoft Synthetic Ethernet Port’

Here’s what I figured out: The Microsoft Forefront Client Security that was running on the host machine was interfering.  Once I excluded both .XML and .VHD files in Forefront, everything started working again.  That was an easy fix.

SQL Server 2008 RC0 is available for public download

Surprisingly, I haven’t heard much buzz around this, but SQL Server 2008 RC0 is now available for public download at this link.  I haven’t had a chance to look at it yet, but supposedly this is the final release before RTM later this year.

Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

My buddy Wayne just brought to my attention a white paper that came out just a year ago from Microsoft that describes how to design SSAS 2005 cubes for use with Excel 2007.  I'm posting it here for my own future reference.  Here is the description:

Overview

Microsoft Office Excel 2007 takes advantage of most of the features in Microsoft SQL Server 2005 Analysis Services. To take full advantage of these features, it is important to keep in mind the end-user experience in Office Excel 2007 when you are designing cubes.
This document outlines how you can create a good end-user experience by optimizing the cube design for Office Excel 2007 PivotTable dynamic views.

Proper Way to Redirect OWA Users via ISA Server 2006

Oops, an update to my last post about redirecting OWA users when deploying Exchange with ISA.  Here is a blog detailing the "correct" way to do it, and also how to redirect http to https, etc.

Redirecting OWA URL with ISA Server 2006 (Quick and Dirty way)

In setting up Exchange 2007 OWA through ISA Server 2006, I realized that it would be a lot easier to tell users to access it via http://owa.domain.com as opposed to http://owa.domain.com/owa (which is repetitive if nothing else).

Problem is, there is no obvious way to do this in ISA or Exchange.  Fortunately I am not the first one to have this problem, and it has already been solved.  The short version of the fix is to set OWA up 100% according to whatever book, article, white paper you are already following.  THEN, add another web access rule point to the same site, except this time set it to Deny.  You don't have to get too fancy on the various settings, authentication, etc. and just use the same listener that you created for OWA.

Once the rule is created, make sure it is the one just above the main OWA access rule (I named the new one "Redirect OWA" so I would know what it was), and then go back in and on the deny page, set it to Redirect to your specific OWA subdirectory (http://owa.domain.com/owa).  It works great!

Here are the two resources that I found to be most helpful:

Thomas Shinder's Blog

Blog du Tristank (look in the comments)

**UPDATE** I have since found a blog that tells the correct way to do this (but the quick and dirty listed above still works fine).  Here is a link to my blog post about the correct way.

SSIS Performance Counters don't work on x64 (out of the box)

If you want to spend some time tuning performance of your SSIS solution, it is likely that at some point you will leverage the SSIS performance counters found in the SQL Server:SSIS Pipeline object.  However, if you are using 64-bit SQL, you will not find the performance counters listed in PerfMon.  That is because here is a known bug in SQL Server 2005 x64, which you can read about in KB 941154.

What is the problem?  The short version is: When SQL is installed, it accidentally points to the \Program Files (x86)\ directory for the SSIS PerfMon counters, except that they are actually in the \Program Files\ directory.

How do I fix this?  Its easy, but requires a reboot.  Here are the instructions, straight from the KB linked above:

To work around this problem, follow these steps:

1. Click Start, click Run, type regedit, and then click OK.

2. In Registry Editor, locate the following registry entry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\DTSPipeline\Performance\Library

3.Double-click the Library registry entry.

4.In the Edit String dialog box, type the following string value in the Value data box:

DriveLetter:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSPipelinePerf.dll

5.Restart the computer.

UPDATE: This fix does not work as described in the KB

Even though there is a DTSPipelinePerf.dll file at the path given in KB 941154, simply pointing to it in the registry setting and rebooting does not make it visible.  I was able to get it to work using Plan B: change the path back to \Program Files (x86)\ and use the 32-bit version of PerfMon.

There are two ways to use the 32-bit version of PerfMon:

1.) Start | Run | mmc.exe /32 perfmon.msc

2.) Execute c:\windows\sysWow64\Perfmon.exe

Programmatically executing SSIS packages

For a good summary of many different ways to run an SSIS package programmatically, as well as advantages and drawbacks of each, look here at Michael Entin's blog.  There are some good things in the comments as well.  This will come in handy at some point, I am sure.

Setup and Administration FAQ's for TFS 2008

I found a good resource for FAQ's for TFS 2008 on the MSDN forums and am posting it here for future reference.  Here is the link

Upgrading Existing SSIS Packages to TableDifference Component 2.0

I am an avid user of Alberto Ferrari's SQL Server Integration Services "Table Diff" component, which you can download from http://www.sqlbi.eu.  He recently released the new, improved version 2.0 of the component (the .dll still says that it is 1.0 though?) and I am upgrading a mid-size solution that makes heavy use of the prior version of TableDiff (1.x) to use the new component.  Here are my observations of both the upgrade and the new capabilities of the component.

Here is a typical Type 1 style update package (in this case an import table, not a dimension) that uses TableDifference at its core to compare the new and old data flows and direct output accordingly.  Behind the scenes, I overwrote the TableDifference.dll file with the new one in both the .NET Assembly Cache and the \PipelineComponents directory.  When I opened the package, everything is working except the Update and Insert components have red X's.  These are easily fixed by simply double-clicking on each to open properties, accepting the new automatically matched column references and saving the package.  That's it, there is nothing else required to update your pacakages from TableDifference 1.x to the newly released 2.0.

Upgrading from TFS 2005 to 2008

A couple of things about upgrading from Team Foundation Server 2005 to 2008 that I wanted to highlight:

1.) The new version of TFS Explorer does not work with VS 2005.  However, you can use the TFS 2005 Explorer to connect to TFS 2008 Server and do 95% of what you need to do.  Just install the VS 2008 version on at least one machine to get the extra features if needed, and it will work fine in a side-by-side deployment.  If you want the TFS 2008 Explorer, you can download the stand-alone install here.

2.) If you are not doing an in-place upgrade and want to move your source code items and work items (with history), there is a tool on Codeplex that will let you do that (even between TFS 2005 and TFS 2008).  You can find it here.

3.) You can download TFS Web Access for 2008 and it presents a very nice UI for interacting with TFS.  The Visual Studio interaction is still a little scattered in my opinion.

4.) If you were using Changeset Comments in 2005, you will need to add the TFS 2008 Powertools to get that feature back.

5.) You MIGHT have a problem with adding items to 2008 if you already had 2005 on the same dev machine.  I was having problems with phantom workspaces after I deleted them, connections to the 2005 server that wouldn't disappear no matter how many times I deleted them, Can Not Connect To Server errors when I tried to add items to the new 2008 server, etc.  HERE IS THE BRUTE FORCE ANSWER: run devenv.exe /resetuserdata and everything will work.  *Warning* Your personal Visual Studio settings will be erased if you do this, but that is not the worst thing in the world.

Team Foundation Server 2008 RTM and 64-bit

Bad news - Microsoft is pushing 64-bit (as they should), but one of their newest enterprise products does not yet support it.  Team Foundation Server 2008 only supports 32-bit.  They have said that 64-bit support will be in the next version of TFS, "Rosario".

At least they support 64-bit SQL and SSAS, but definitely not MOSS 64-bit.  We have deployed 64-bit MOSS 2007 and it turns out that it doesn't even support 64-bit SharePoint when SharePoint is running on a different server from TFS, it doesn't even recognize that MOSS is installed:

SQL Server Default Ports

It seems like it is always hard to track down the ports that SQL Server uses, so thought that I would post them here for when I need them.  These are the SQL Server default ports.

SQL Component	TCP Port
SQL Service	1433
SQL Browser Service	1434
SQL Monitoring	1444
SQL Server Analysis Services Redirector	2382
SQL Server Analysis Service	2383
SQL Server Reporting Services	80

Excel 2003 Add-in for Analysis Services

If your company (like most companies) is still using Excel 2003 and you are implementing a data warehouse which contains SSAS cubes, you might be interested in the Microsoft Excel 2003 Add-in for SQL Server Analysis Services.  I have not used this tool extensively, but it does bring Excel 2003 closer to Excel 2007 in terms of OLAP capabilities.  Here is the blurb from the download page, I will post more as I spend time with the tool.  This is not a new find, nor is it likely news to anybody, but worth noting so I am putting it here.

The Excel Add-in for Analysis Services enables users to access and analyze data from multiple Analysis Services Cubes, and to create rich, customized reports directly in Microsoft Office Excel 2003 or Microsoft Excel 2002. This download can improve data analysis, shorten reporting cycles, and enhance your company's ability to respond to customers.
This download not only reduces the time and resources required to train users, but it also eliminates the need for organization’s to support specialized reporting systems and tools. The key business benefits of Excel Add-in for Analysis Services include:

• Visibility in Excel into business trends
• Increased speed and quality of decision-making
• Streamlined data analysis that shortens reporting cycles and saves resources
• Increased reporting flexibility through rich, highly customized, and refreshable reports
• Access to relevant information from multiple data sources

About the Excel Add-in for Analysis Services
With the Excel Add-in for Analysis Services, individual users can manage the reporting cycle from beginning to end and eliminate the need to cut and paste data from multiple systems.

• Access: Easily create and maintain live data connections to multiple Analysis Services cubes, ensuring data consistency and integrity, and combine data from multiple sources into a single report.
• Analyze: Conduct detailed analysis using native Excel capabilities. Extend the richness of analysis through “what if” and drill through capabilities.
• Author: Easily personalize and refresh report layouts. Minimize end user training and reduce reliance on IT due to Excel’s popularity and ease of use.

The Data Warehouse Lifecycle Toolkit 2nd Edition is now available

The first one is a great reference, but was published in 1998.  Since then the art and science of building the data warehouse has matured significantly.  I don't think that the word BI really existed in 1998. . . Actually I don't know since I didn't start working in the DW/BI world until 1999 with Brio, then Business Objects.

The original had a lot of great material for organizing the pro cess around preparing for, implementing and maintaining the data warehouse, including a nice set of documents.  Here is a link to the new book on Amazon.

Installing Exchange 2007 on Windows 2008 (aka UAC is a pain)

Over the last couple of days I have been setting up Exchange 2007 SP1 on Windows Server 2008 RTM.  No matter what I tried, I was stuck in a Catch-22: If I logged in as Local Admin on the box, I didn't have permission to make the necessary changes to the domain; if I logged in as Domain Admin, I didn't have permission to write to the local log file on the C:\ drive of the Exchange server.  I kept getting errors like the one at right from the GUI, or this one from the command line:

Failed to initialize the log file: Access to the path 'C:ExchangeSetupLogs\ExchangeSetup.log' is denied.

Setup will not continue.

Those are NOT the most descriptive errors.  If I logged in as local admin, those errors went away, but instead I received errors that the local admin account didn't have the rights to make massive changes to the domain (shocker).

I posted on the Microsoft Technet Exchange forums, and hardly anyone read my post, much less answered it.  One person responded that they had the same problem, so I knew it wasn't just me.  I noticed that I couldn't run the basic commands that install Windows 2008 components, like PowerShell without getting similar errors.  It didn't matter if it was on a Hyper-V VM, or a physical server, I could not resolve the problem.

Then I had an idea.  I knew that Windows 2008 and Vista are the same underneath, and I remember that the first thing I did when I installed Vista was to disable UAC. . . hmm. . . yep, that was the problem.  Here is where you do it on Windows 2008, just like in Vista:

Then go here:

I hope that anyone else that is about to go crazy from Installing Exchange 2007 on Exchange 2008 finds this blog entry and simply disables UAC.

Is the Nintendo Wii just a gimmick?

I was reading a post on Kotaku about the new Wii 3.2 system software update, and I noticed that about 40% of the comments say something along the lines of "I haven't turned my Wii on since the last system update", or "I only play my 360 or PS3".

I have a 360, PS3 and Wii, and that is the order that I would rate them right now.  The Wii is cool, and Wii Sports is the best game ever for when you have friends over. . . but I haven't really seen anything else that is impressive enough to hold the attention of anyone over the age of 25.

I know that they are selling many more Wii's than they are 360's or PS3's, but I bet 80% of them are collecting dust after the first couple of weeks.  Microsoft has a crazy high attach rate, something like 8 games for every system sold.  I'm guessing the Wii has about 2 games for every system sold.  I hope that they come out with a killer app, or at least Wii Sports 2 in the near future.

New Bluetooth Headset - BlueAnt Z9

My old bluetooth headset died (Plantronics Voyager 510, best basic bluetooth headset you can get for $45).  This seems to happen every year in February.  I am very picky about my bluetooth headset, so always try a couple until I get the perfect one.  This year I tried 2: the Aliph Jawbone and the BlueAnt Z9. 

They both claim to have excellent noise cancellation, but the Jawbone is very dramatic about this claim.  Back before I blew the engine on my Land Rover, noise was a real problem since that is probably the noisiest car on the road.  Since I don't have that car anymore, noise is less of an issue.

The bottom line is that for the money ($75), the Jawbone would have to be the best thing ever.  It turns out that it is not the best thing ever.  It is awkward  to put over your ear and too big to put in your pocket.  The BlueAnt is easy to put over your ear and fits in your pocket no problem, plus its only $60.  I've had it for a 2 weeks and it is great.  They seem to upgrade the firmware frequently, and I have already updated it to v3.4, it seems to be better than it was on v3.2 but it might be my imagination.  At any rate, it is a nice headset and I recommend it.

Connecting Excel Services to an External Data Source

I will be setting up a proper connection from Excel Services to an Analysis Services 2005 cube over the next couple of days and found this article that describes the necessary steps in detail.  The article is titled Plan external data connections for Excel Services, just what I was looking for.

Undoing Checkout for Another User in TFS 2005

It is not as easy as it should be to undo someone else's checkout in Team Foundation Server 2005.  In 2008, you can do it through a GUI, but only if you have the Power Tools installed.

Here is how to do it in TFS 2005, from the command line:

tf undo [/workspace:workspacename[;workspaceowner]] 
[/server:servername] [/recursive] itemspec [/noprompt]

I was also faced with having to add my login info, so the exact command I used was as follows:

tf undo /workspace:WORKSPACE-NAME;User $/myProject/CheckedOutFile.cs /login:myusername,mypassword

I also found out that you can delete an entire workspace for a developer who is no longer on your project and maybe had some files checked out.  That command is even easier, and will automatically undo any checkouts:

tf workspace /delete Workspace  (add "/login:name,password" to end of command if not using AD)

SQL Server 2005 SP2 Performance Reports

Just stumbled across a whole set of reports that can be deployed to SQL 2005 if you have SP2 installed.  Here is the link.  From what I have read, these are a pretty slick addition to the existing reports (you did know that there are existing reports, right?)

Here is the description from the download site:

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:

- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Reporting Services is not required to be installed to use the Performance Dashboard Reports.

Microsoft BI Demo VPC 5.1

You better have a monster machine on which to run this: Microsoft has released a new Business Intelligence sample VPC.  The .vmc is set to use 1GB when you first install it, I would recommend bumping to 1.5GB - I tried 2GB and it was a still sluggish on my laptop.  Might want to throw it on a spare server if you have one.

It is a big download, but definitely worth it if you want to have a VPC available for "playing around" with any of the Microsoft BI tools without having to go through the trouble of deploying MOSS, etc. on a VPC.

Download Part 1 (3.5GB)
Download Part 2 (1GB)

From the readme, here is the list of software that is installed and fully configured:

Microsoft Windows Server 2003 R2 Service Pack 2
Internet Explorer 7
Microsoft Office Enterprise 2007
Microsoft Office Visio 2007
Microsoft Office Project 2007
Microsoft Office SharePoint Designer 2007
Microsoft SharePoint Portal Server 2007
Microsoft Visual Studio 2005 w/ Service Pack 1
Microsoft SQL Server 2005 w/ Service Pack 2 (w/ SSIS, SSAS, SSRS)
Microsoft SQL Server 2005 Data Mining Add-ins
Microsoft SQL Server 2005 SharePoint Integration Add-in
ProClarity Analytics Server 6.3
ProClarity Dashboard Server 6.3
ProClarity Desktop Professional 6.3
ProClarity Web Professional 6.3
Microsoft PerformancePoint Planning Server 2007
Microsoft PerformancePoint Monitoring Server 2007
Microsoft PerformancePoint Dashboard Designer
Microsoft PerformancePoint Planning Add-In for Excel
Microsoft PerformancePoint Planning Business Modeler
Project REAL Reference Implementation
SoftArtisans OfficeWriter 3.8.1 and Designer
BI Demo Files
Microsoft SQL Server 2005 Samples
Microsoft SQL Server BI Metadata Samples
Performance Point Planning Samples/Labs
Visual Studio 2005 Tools for Office Second Edition