Comparing timesheet and project assignments actual data

From to time to time you may need to check out that your Project Server’s timesheet and assignments actual data are in sync. In order to achieve that you need to build a SQL query that connects and compares the timesheet and assignments data. The final report can be a cube, excel file or whatever report you may need.

In the example below I am going to create 3 SQL views based on the reporting database of Project Server 2007:

• The _Tasks_All view which combines assignment actual data from different tables in a human readable form
• The _Timesheet_All view which combines timesheet actual data from different tables in a human readable form
• The _Timesheet_Diff view which compares the other two custom views and calculates the differences

Connect to your database server where you are hosting your Project Server databases and expand the Project Server reporting database. Right click the views folder and select new view. On the add table popup dialog click “close” and paste the query below in the view builder

SELECT dbo.MSP_EpmAssignment.AssignmentUID,dbo.MSP_EpmResource.ResourceUID,dbo.MSP_EpmResource.ResourceName,dbo.MSP_EpmAssignmentByDay.TimeByDay,
dbo.MSP_EpmAssignmentByDay.AssignmentActualWork - dbo.MSP_EpmAssignmentByDay.AssignmentActualOvertimeWork AS 'AssignmentActualRegularWork',
dbo.MSP_EpmAssignmentByDay.AssignmentActualOvertimeWork,
dbo.MSP_EpmProject.ProjectUID,dbo.MSP_EpmProject.ProjectName,
dbo.MSP_EpmTask.TaskUID,dbo.MSP_EpmTask.TaskName,dbo.MSP_EpmTask.TaskOutlineNumber
FROM dbo.MSP_EpmAssignmentByDay INNER JOIN
dbo.MSP_EpmTask ON dbo.MSP_EpmAssignmentByDay.TaskUID = dbo.MSP_EpmTask.TaskUID INNER JOIN
dbo.MSP_EpmProject ON dbo.MSP_EpmAssignmentByDay.ProjectUID = dbo.MSP_EpmProject.ProjectUID INNER JOIN
dbo.MSP_EpmAssignment ON dbo.MSP_EpmAssignmentByDay.AssignmentUID = dbo.MSP_EpmAssignment.AssignmentUID INNER JOIN
dbo.MSP_EpmResource ON dbo.MSP_EpmAssignment.ResourceUID = dbo.MSP_EpmResource.ResourceUID

Save the view as _Tasks_All and close the builder.

Repeat the preceding procedure to create the _Timesheet_All view

SELECT dbo.MSP_TimesheetResource.ResourceUID, dbo.MSP_TimesheetResource.ResourceName,dbo.MSP_TimesheetLine.AssignmentUID,
dbo.MSP_TimesheetStatus.Description,dbo.MSP_TimesheetTask.TaskUID,
dbo.MSP_TimesheetTask.TaskName, dbo.MSP_TimesheetProject.ProjectUID,dbo.MSP_TimesheetProject.ProjectName,
dbo.MSP_TimesheetActual.TimeByDay,dbo.MSP_TimesheetActual.ActualWorkBillable,
dbo.MSP_TimesheetActual.ActualWorkNonBillable,
dbo.MSP_TimesheetActual.ActualOvertimeWorkBillable,
dbo.MSP_TimesheetActual.ActualOvertimeWorkNonBillable, dbo.MSP_TimesheetActual.PlannedWork
FROM dbo.MSP_Timesheet INNER JOIN
dbo.MSP_TimesheetResource ON dbo.MSP_Timesheet.OwnerResourceNameUID = dbo.MSP_TimesheetResource.ResourceNameUID INNER JOIN
dbo.MSP_TimesheetLine ON dbo.MSP_TimesheetLine.TimesheetUID = dbo.MSP_Timesheet.TimesheetUID INNER JOIN
dbo.MSP_TimesheetPeriod ON dbo.MSP_Timesheet.PeriodUID = dbo.MSP_TimesheetPeriod.PeriodUID INNER JOIN
dbo.MSP_TimesheetStatus ON dbo.MSP_Timesheet.TimesheetStatusID = dbo.MSP_TimesheetStatus.TimesheetStatusID INNER JOIN
dbo.MSP_TimesheetTask ON dbo.MSP_TimesheetLine.TaskNameUID = dbo.MSP_TimesheetTask.TaskNameUID INNER JOIN
dbo.MSP_TimesheetProject ON dbo.MSP_TimesheetLine.ProjectNameUID = dbo.MSP_TimesheetProject.ProjectNameUID INNER JOIN
dbo.MSP_TimesheetActual ON dbo.MSP_TimesheetResource.ResourceNameUID = dbo.MSP_TimesheetActual.LastChangedResourceNameUID AND
dbo.MSP_TimesheetLine.TimesheetLineUID = dbo.MSP_TimesheetActual.TimesheetLineUID INNER JOIN
dbo.MSP_TimesheetPeriodStatus ON dbo.MSP_TimesheetPeriod.PeriodStatusID = dbo.MSP_TimesheetPeriodStatus.PeriodStatusID INNER JOIN
dbo.MSP_TimesheetClass ON dbo.MSP_TimesheetLine.ClassUID = dbo.MSP_TimesheetClass.ClassUID

and _Timesheet_Diff view

SELECT dbo._Timesheet_All.Description as 'Approval',dbo._Timesheet_All.ProjectName, dbo._Timesheet_All.TaskName,ISNULL (dbo._Tasks_All.TaskOutlineNumber,'N/A'),
dbo._Timesheet_All.TimeByDay,dbo._Timesheet_All.ResourceName,
dbo._Tasks_All.AssignmentActualRegularWork AS 'TaskRegularWork',
dbo._Timesheet_All.ActualWorkBillable AS 'TSRegularWork',
dbo._Tasks_All.AssignmentActualOvertimeWork AS 'TaskOvertimeWork',
dbo._Timesheet_All.ActualOvertimeWorkBillable AS 'TSOvertimeWork',
dbo._Timesheet_All.ActualWorkBillable – ISNULL(dbo._Tasks_All.AssignmentActualRegularWork,0) AS 'RegularDiff',
dbo._Timesheet_All.ActualOvertimeWorkBillable - ISNULL (dbo._Tasks_All.AssignmentActualOvertimeWork,0) AS 'OvertimeDiff'
FROM dbo._Timesheet_All LEFT OUTER JOIN
dbo._Tasks_All ON
dbo._Timesheet_All.AssignmentUID = dbo._Tasks_All.AssignmentUID AND
dbo._Timesheet_All.TimeByDay = dbo._Tasks_All.TimeByDay
WHERE dbo._Timesheet_All.ProjectName <> 'Administrative' AND(dbo._Timesheet_All.Description <> 'Rejected')

What we’ve done so far? We have created the _Tasks_all view which reports back:

• AssignmentUID
• ResourceUID
• ProjectUID
• TaskUID
• ProjectName
• ResourceName
• TaskName
• TaskOutlineNumber
• TimeByDay
• AssignmentActualRegularWork
• AssignmentActualOvertimeWork

The _Timesheet_All view which reports back:

• AssignmentUID
• ResourceUID
• ProjectUID
• TaskUID
• ProjectName
• ResourceName
• TaskName
• TimeByDay
• ActualWorkBillable
• ActualWorkNonBillable
• ActualWorkOvertimeBillable
• ActualWorkOvertimeNonBillable
• Description (approval status)
  

And finally the comparison view _Timesheet_Diff which repots back:

• Approval
• ProjectName
• TaskName
• OutlineNumber
• TimeByDay
• ResourceName
• TaskRegularWork
• TSRegularWork
• TaskOvertimeWork
• TSOvertimeWork
• RegularDiff (ActualWorkBillable – AssignmentActualRegularWork)
• OvertimeDiff (ActualOvertimeWorkBillable – AssignmentActualOvertimeWork)

The _Timesheet_Diff view joins the _Timesheet_All and _Tasks_All views on AssignmentUID and TimeByDay fields. I have used a LEFT OUTER JOIN instead of INNER JOIN because I’ve seen that some timesheets cannot bind back to assignments, due to Project changes after timesheets creation by users.

Finally, an easy way to produce a report is to create an excel document with a pivot table based on the _Timesheet_Diff view. A more robust way is to create a Analysis Services Cube or a Reporting Services Report under SharePoint server.

Technorati Tags: From,Project,Server,_Tasks_All,_Timesheet_All,_Timesheet_Diff,Connect,SELECT,MSP_EpmAssignment,AssignmentUID,MSP_EpmResource,ResourceUID,ResourceName,AssignmentActualWork,AssignmentActualOvertimeWork,AssignmentActualRegularWork,MSP_EpmProject,ProjectUID,ProjectName,MSP_EpmTask,TaskUID,TaskName,TaskOutlineNumber,INNER,JOIN,Save,Repeat,MSP_TimesheetResource,MSP_TimesheetLine,MSP_TimesheetStatus,Description,MSP_TimesheetTask,MSP_TimesheetProject,MSP_TimesheetActual,ActualWorkBillable,ActualWorkNonBillable,ActualOvertimeWorkBillable,ActualOvertimeWorkNonBillable,PlannedWork,MSP_Timesheet,OwnerResourceNameUID,ResourceNameUID,TimesheetUID,MSP_TimesheetPeriod,PeriodUID,TimesheetStatusID,TaskNameUID,ProjectNameUID,LastChangedResourceNameUID,TimesheetLineUID,MSP_TimesheetPeriodStatus,PeriodStatusID,MSP_TimesheetClass,ClassUID,Approval,ISNULL,TaskRegularWork,TSRegularWork,TaskOvertimeWork,TSOvertimeWork,RegularDiff,OvertimeDiff,LEFT,OUTER,WHERE,Administrative,ActualWorkOvertimeBillable,ActualWorkOvertimeNonBillable,OutlineNumber,Analysis,Services,Cube,Report,SharePoint,Technorati,Tags,Timesheet,Tasks,Assignments,LiveJournal,actual,data,time,query,compares,excel,create,views,database,view,combines,tables,human,readable,form,click,table,builder,reports,back,comparison,timesheets,compare

Windows Live Tags: From,Project,Server,_Tasks_All,_Timesheet_All,_Timesheet_Diff,Connect,SELECT,MSP_EpmAssignment,AssignmentUID,MSP_EpmResource,ResourceUID,ResourceName,AssignmentActualWork,AssignmentActualOvertimeWork,AssignmentActualRegularWork,MSP_EpmProject,ProjectUID,ProjectName,MSP_EpmTask,TaskUID,TaskName,TaskOutlineNumber,INNER,JOIN,Save,Repeat,MSP_TimesheetResource,MSP_TimesheetLine,MSP_TimesheetStatus,Description,MSP_TimesheetTask,MSP_TimesheetProject,MSP_TimesheetActual,ActualWorkBillable,ActualWorkNonBillable,ActualOvertimeWorkBillable,ActualOvertimeWorkNonBillable,PlannedWork,MSP_Timesheet,OwnerResourceNameUID,ResourceNameUID,TimesheetUID,MSP_TimesheetPeriod,PeriodUID,TimesheetStatusID,TaskNameUID,ProjectNameUID,LastChangedResourceNameUID,TimesheetLineUID,MSP_TimesheetPeriodStatus,PeriodStatusID,MSP_TimesheetClass,ClassUID,Approval,ISNULL,TaskRegularWork,TSRegularWork,TaskOvertimeWork,TSOvertimeWork,RegularDiff,OvertimeDiff,LEFT,OUTER,WHERE,Administrative,ActualWorkOvertimeBillable,ActualWorkOvertimeNonBillable,OutlineNumber,Analysis,Services,Cube,Report,SharePoint,Technorati,Tags,Timesheet,Tasks,Assignments,LiveJournal,actual,data,time,query,compares,excel,create,views,database,view,combines,tables,human,readable,form,click,table,builder,reports,back,comparison,timesheets,compare

WordPress Tags: From,Project,Server,_Tasks_All,_Timesheet_All,_Timesheet_Diff,Connect,SELECT,MSP_EpmAssignment,AssignmentUID,MSP_EpmResource,ResourceUID,ResourceName,AssignmentActualWork,AssignmentActualOvertimeWork,AssignmentActualRegularWork,MSP_EpmProject,ProjectUID,ProjectName,MSP_EpmTask,TaskUID,TaskName,TaskOutlineNumber,INNER,JOIN,Save,Repeat,MSP_TimesheetResource,MSP_TimesheetLine,MSP_TimesheetStatus,Description,MSP_TimesheetTask,MSP_TimesheetProject,MSP_TimesheetActual,ActualWorkBillable,ActualWorkNonBillable,ActualOvertimeWorkBillable,ActualOvertimeWorkNonBillable,PlannedWork,MSP_Timesheet,OwnerResourceNameUID,ResourceNameUID,TimesheetUID,MSP_TimesheetPeriod,PeriodUID,TimesheetStatusID,TaskNameUID,ProjectNameUID,LastChangedResourceNameUID,TimesheetLineUID,MSP_TimesheetPeriodStatus,PeriodStatusID,MSP_TimesheetClass,ClassUID,Approval,ISNULL,TaskRegularWork,TSRegularWork,TaskOvertimeWork,TSOvertimeWork,RegularDiff,OvertimeDiff,LEFT,OUTER,WHERE,Administrative,ActualWorkOvertimeBillable,ActualWorkOvertimeNonBillable,OutlineNumber,Analysis,Services,Cube,Report,SharePoint,Technorati,Tags,Timesheet,Tasks,Assignments,LiveJournal,actual,data,time,query,compares,excel,create,views,database,view,combines,tables,human,readable,form,click,table,builder,reports,back,comparison,timesheets,compare

Auto-naming custom list definition

There are so many times where companies want to standardize their file naming process across document libraries. In order to achieve that, in a Sharepoint document library, you can use a couple of ways, workflow or custom list definition with event receivers.

I have created and uploaded a custom list definition, which saves files by using the following naming rule: 3 first uppercase letters of content type + REF + DOCID. For example a document of content type Correspondence with ID=33 will be named CORREF33.

The event receivers are fired up on the following events: ItemCreated, ItemUpdated, ItemCheckedIn. In all cases, if the content type is or inherited by the content type “folder”, the naming conversion is not taking place. If you have enabled the “Require documents to be checked out before they can be edited” option, the ItemCreated and ItemUpdated event receivers are not evaluated in order to avoid conflicts and items are named after you checked them in.

In order to install the custom list definition, extract the archive somewhere in your Sharepoint Server and execute “setup.bat /i /weburl http://mysite/myweb”. You should now be able to select the “Autonaming Document Library” when you try to create a new list or document library.

http://rapidshare.com/files/204782843/AutonamingListDef.zip.html

Altering the default containers for creating users and computers in AD

In the AD structure of the MS windows server product line, the default containers for creating the security accounts for users and computers are of object class CN instead of OU which is more desirable. OUs have significant advantages over CN since they can support Group Policy assignments directly to the OU level, delegation, protection against accidental deletion and better recovery scenarios.

Creating the OU

In order to gain those advantages of the OU structure you need to create one first. To create an OU you simply have to:

1. Open Active directory users and computers (DSA.MSC)
2. Right click on the domain name (Note that OUs can be nested)
3. Select New –> Organizational Unit
4. Enter a name for the OU and press OK.

In the simplest scenario you could create an OU with your company’s name (like MyCompany) and inside that OU one called Computers and one called Users. Now you are able to move the users and the computers accounts to the respective OUs to gain the advantages mentioned previously.

Altering the default container

To complete the procedure, you need to redirect the default containers for creating users and computers. This will save you time and frustration when creating user and computer objects through command line utilities like net user, net computer, netdom add, etc and the domain join user interface in widows operating systems.

Note, that in order to redirect the default users and computers containers your domain must be at least at Windows 2003 functional level.

To alter the default container for your users account (redirusr.exe) to an OU called Users inside an OU called MyCompany to the Mydomain.local domain, type:

c:\windows\system32\redirusr ou=Users,ou=MyCompany,dc=Mydomain,dc=local

To alter the default container for your computers account (redircmp.exe) to an OU called Computers inside an OU called MyCompany to the Mydomain.local domain, type:

c:\windows\system32\redircmp ou=Computers,ou=MyCompany,dc=Mydomain,dc=local

Selling IT Services (Part 1)

Preface

In the modern commercial world selling your product and services is challenging at most of the times. I have seen many colleagues struggling to sell their services, in many cases, without success. I have watched many situations in which numerous sales people are trying to sell products to companies they don’t actually need. They don’t analyze needs, wants and criteria and they don’t take into account the company’s budget.

I am not a salesman. Actually, I am a systems engineer and solution architect. But since I am a free lancer I have to sell my services on my own. I am going to share a summary of a process that I studied in a few sales books and works pretty well for me.

Prepare

One thing that I am sure about is that preparing and meeting the customers’ needs, wants and criteria makes the selling process much easier. Usually, I am following the following preparation steps before I meet a potential customer:

• I gather information about the company: Products, services, organizational charts, stakeholders, offices, etc.
• I make sure that I know pretty well about my products and services.
• I make sure that I know the major competitors’ advantages, disadvantages and prices.
• I plan for Frequently Asked Questions that may arise
• I Plan for any presentation and visual aids I might need
• I mentally rehearsal the sales process and my presentations

It is not wise to go unprepared to a customer’s meeting. By gathering information about the client I can prepare my hooks (I will talk about it later), and potential opportunities that may fit to their organization.

By knowing my products and services I can respond to any questions and concerns that may arise and by knowing the competition I am prepared to deal with potential alternatives.

FAQ planning is very useful to inoculate against customer’s concerns smoothly.

First Impression, hooks and rapport

You have only one opportunity to make a first impression and that’s why it is so important. Most of the times is as simple as smiling, maintaining a good eye contact, offering a firm handshake and properly introducing yourself. Many people are messing that up!

The purpose of hooks is to get people’s attention and make them focus on you. Hooks are supposed to:

• Answer the question: Why should I listen to you?
• Make them to ask: What is it?
  

A hook example for a MS SharePoint solution I often use:

Mr. Doe, You know how difficult is to manage and organize your electronic documents, which makes complex to efficiently exchange corporate information across employees and customers? One of the many benefits of the solution we offer is that it will make the management of the electronic documents an easy task for your company, which means that information exchange will be adequate; leading to better corporate performance and customer satisfaction.

Rapport is one of the most important features or characteristics of unconscious human interaction. It is commonality of perspective, being in "sync", being on the same "wavelength" as the person with whom you are talking. Techniques of gaining rapport include the following:

• Matching your body language
• Using same phrases
• Speech pace
• Breathing rate
• Matching voice tone
• Maintaining eye contact
• Analyzing needs and criteria

Analyzing needs and criteria

It is a very important step in the selling process. Nobody cares about your products or services if they don’t need or want them. So, it is wise to understand what customer wants, needs and what it is important for him in your products or services. Needs analysis is as simple as asking questions and listening to the answers:

• What problems are you experience in information exchange currently in your organization?
• How long does it usually take to find out all the relative information about a project, a subject or a customer? Is this important to you?
• What else would be nice to be delivered in your solution?

Another important element to analyze is the customers’ criteria in selecting products or services. Some criteria examples are service availability, product stability, ease of use, helpdesk support, etc. Understanding customers’ criteria should be stated later in the sales presentation in order to increase the possibility of success.

Understanding customer’s budget

It won’t be in my advantage if a customer expects a solution of 5.000€ and I propose a solution of 25.000€, isn’t that right? I prefer to find out about the customer’s budget before presenting my solution. This saves a lot of time for me and the customer. Asking the question “Have you setup a budget for dealing with those problems?” can lead to three possible answers: No, yes XXX€, yes but I am not willing to share it.

If I get the answer yes XXX€, I have easily overcome my solution’s pricing pitfall. In the other two cases I have to take a different approach in order to find out something about their budget. I ask: “Ok that’s common. Let’s take a look at some round numbers. I have a 5,000€ solution that will solve some of your problems and a 25,000€ solution which will cover most of them. Which approach would you like to follow?”.

In most cases the customer will come up with a number, usually in the low budget range. This can be a strategic move to lower the price. In order to overcome such cases I can respond: “Hmm… We won’t go first class with that budget. Is it possible to trim some of the features?”. This will make them understand:

• that I know about my services and I am not willing to waste my time
• that a cheap solution will not solve all their major problems
  

Finally, I am trying to understand the decision making process. I need to know who and how are going to evaluate my proposal. I need to take into account the concerns of all the decision makers or decision influencers. Later, in my solution presentation, I should also deal with the concerns of those people in order to maximize my possibilities of success.

Summarizing

At this point I have analyzed customer’s needs, wants, criteria and budget. In order to go ahead with my proposal or sales presentation I am using a summarizing bridge:

Mr Doe, can I summarize what we have discussed so far? We talked about the problems of managing your company’s electronic documents and how this affects the exchange of information across your organization and your customers. You said that this is a major problem for your company since misunderstandings are very common due to outdated and invalid data exchange, is that right? We also discussed that a smart search feature across your electronic documents will save many hours of work and frustration. It is important for you and your company to implement a nice user interface, an easy to use search feature and to be possible to brand it with your corporate logo. We also discussed that the available budget to address those problems is around 15,000€ and that you prefer to pay 30% upon proposal acceptance and the rest 70% to span it on the delivery of each phase of the project, correct? Shall we close an appointment for the next week in order to discuss about the final proposal?

In the next part: Writing and presenting the proposal, closing the sale, following up.

Archiving Exchange 2007 mailboxes. The cheap way...

Did you ever come up with the situation to export your Exchange mailboxes to PST files? From time to time, an Exchange administrator needs to export some or all of his organization’s mailboxes out of the exchange database.

In previous versions of exchange sever a tool called ExMerge.exe was able to assist in such cases. In the 2007 version of Exchange server this tool has been replaced by the export-mailbox cmdlet. The export-mailbox cmdlet can be used to export or move the contents of a mailbox, between mailboxes or to a PST file (SP1+). Although export-mailbox is an Exchange server 2007 tool, the source and target mailboxes can be on one of the following:

• Exchange 2007 server
• Exchange Server 2003 SP2 or later
• Exchange Server 2000 SP3 or later

To export data to a .pst file, you must execute the export-mailbox cmdlet from a 32-bit computer that has the 32-bit version of Exchange management tools installed, along with MS Outlook 2003 SP2+ or MS Outlook 2007. Note that export-mailbox cmdlet cannot export data between mailboxes in different forests.

Since export-mailbox is a powershell cmdlet, the output of other cmdlets like get-recipient or get-mailbox can be piped into the tool to streamline functionality. The export-mailbox cmdlet can filter against:

• Subject Keywords (-SubjectKeywords)
• Content keywords (-ContentKeywords)
• Keywords found in subject, body or attachments (-AllContentKeywords)
• Filenames of attachments. You can also include wildcard characters like *.pdf or Article*.txt (-AttachmentFilenames)
• Sender and/or recipient keywords (-SenderKeywords & -RecipientKeywords)
• Specified timeframe (–StartDate & –EndDate)
• Messages locale (-Locale)
• Included or excluded mailbox folders (-IncludeFolders & -ExcludeFolders)

Also, you can choose if you want to just export the contents of a mailbox or move them and delete the exported data from the source (-DeleteContent).

A very common example is an organizational policy which dictates that all employee mailboxes should be exported in an annual basis to offline media for archiving purposes. In order to achieve that goal an exchange administrator should be able to move organizational mailbox data from source mailboxes to PST files using a date filter:

Get-mailbox export-mailbox -EndDate “01/01/2007” -ExcludeFolders “\Contacts”,”\Journal”,” \Junk E-Mail”,”\Deleted Items”,”\Calendar”,”\Sync Issues”,”\Drafts”,”\Notes”,”\Tasks”,”\Outbox” –PstFolderPath c:\MyPSTs –DeleteContent –confirm:$false

If we analyze the above command:

• The Get-Mailbox command returns all mailboxes in the organization
• The Export-Mailbox cmdlet gets its input from the get-mailbox command
• The EndDate switch filters out all items after 01/01/2007
• The ExcludeFolder switch excludes all folders we don’t want to include in our archiving procedure, such as Tasks and Contacts
• The PstFolderPath switch directs the PST files folder location to c:\MyPSTs
• The DeleteContent switch deletes exported items from source mailboxes
• The Confirm:$False global switch disables the confirmation dialog during the mailbox export procedure

The above command can be used with variations in order to include more advance filters like exporting mailboxes with specific subject keywords from a single organizational unit:

Get-Mailbox –OrganizationalUnit “ou=Accounting,ou=Structure,dc=mydomain,dc=local” export-mailbox –SubjectKeywords “Payroll” –PstFolderPath c:\MyPSTs –DeleteContent –confirm:$false

Note that under the –PstFolderPath location multiple files will be created, in the form of accountname.pst, for each respective user defined by the export command.

Auto Configuring Proxy Settings

Did you ever wondered what is this “automatically detect settings” option in LAN settings under connection tab in Internet Explorer? By setting this option you enable the web proxy auto discovery (WPAD) protocol functionality of the web browser. Using this protocol you are directing your web browser to use a special configuration file to automatically set its proxy settings. The benefit from the use of WPAD is the ability to instruct all web browsers in an organization to use the same policy, without configuring each of them manually.

Where is the configuration file?

The configuration’s file location can be published by using two alternative methods: DNS or DHCP. A web browser configured for WPAD, before fetching its first page sends a DHCPINFORM query to its local DHCP server in order to get the URL of the configuration file in the DHCP reply. If DHCP does not provide the desired information, the web browser will try to fetch the configuration file by using DNS resolution. For example if the FQDN of the client computer is computer.subdomain.domain.local, the web browser will try to fetch the configuration file from the following locations:

1. http://wpad.subdomain.domain.local/wpad.dat
2. http://wpad.domain.local/wpad.dat
3. http://wpad.com/wpad.dat (in incorrect implementations)

Hosting the wpad.dat

Since the web browser is trying to fetch the configuration file (wpad.dat) by using the HTTP protocol, the hosting server should be able to do so. The hosting web server must be also set to serve .dat files as “application/x-ns-proxy-autoconfig” mime types and the wpad.dat file should be located at the web site’s root directory. For example in an IIS configuration, you should do the following:

• Go to Start --> settings --> control panel --> administrative tools --> Internet Information Services (IIS) Manager
• Right click the web site node in which you are going to host the wpad.dat file (for example Default Web Site) and select properties
• Select the HTTP Headers tab and press MIME Types button
• In the “MIME Types” dialog box press NEW, type .dat in the extension field and application/x-ns-proxy-autoconfig in the MIME Type field, and press OK.
• Return back to IIS Manager and right click the web site node in which you are going to host the wpad.dat file (for example Default Web Site) and select explore.
• Right click somewhere in the right pane of the IIS snap-in and select new --> text document.
• Rename the document to wpad.dat.
  

Editing the wpad.dat file

The wpad.dat file you have created in a previous step should be populated with a javascript in order to instruct the web browser how to configure its proxy settings. A sample configuration is illustrated below:

function FindProxyForURL(url, host) {
// our local URLs from the domains below mydomain.com don't need a proxy:
if (shExpMatch(url,"*.mydomain.com/*")) {return "DIRECT";}
if (shExpMatch(url, "*.mydomain.com:*/*")) {return "DIRECT";}

// Client computers within this network are accessed through
// port 8080 on proxy1.mydomain.local:
if (isInNet(MyIPAdress(), "192.168.0.0", "255.255.255.0"))
{return "PROXY proxy1.mydomain.local:8080";
}
// All other requests go through port 8080 of proxy2.mydomain.local.
// should that fail to respond, go directly to the WWW:
return "PROXY proxy2.mydomain.local:8080; DIRECT";
}

In the example above, you are directing the web browser to use proxy1.mydomain.local on port 8080 in case the client computer belongs to 192.168.0.0/24 network (script marked with red). In case the client does not belong to the 192.168.0.0/24 network, all web traffic will go through proxy2.mydomain.local and if proxy2 fails to respond, it will try to go directly (script marked with orange). Finally, we instruct the web browser to bypass proxies in case the URL contains the .mydomain.com string (script marked with green). Note that you can add more rules by just adding lines to your configuration file.

Publishing the file location

To publish the file location you need to either setup a DHCP option or setup a DNS record. To setup the DHCP option in a windows DHCP server you need to do the following:

• Go to Start --> settings --> control panel --> administrative tools --> DHCP
• Right click the DHCP server name and select “set predefined options”
• In the Predefined options dialog box press “add”
• In the option type dialog box set the following values:
  Name: WPAD
  Data Type: String
  Code: 252
  Description: WPAD Auto Config Key
• Go back to DHCP snap-in and right click either your scope or server options.
• Select “Configure Options…”
• In the scope options dialog box select the 252 option and in the string value type your wpad.dat file location (like http://wpad.mydomain.local/wpad.dat) and press OK.

Finally, to configure your DNS server, you need to add a WPAD A or CNAME record for the server hosting your wpad.dat file (for example wpad.mydomain.local).

Moving MOSS & Project Server 2007 Databases to a new server

Many system administrators are dealing with the situation of moving their MOSS and Project Server databases to a new SQL server. The reason for that kind of change can vary from hardware expansion requirements to the migration of a test environment to a production one. Unfortunately, that kind of transition is not as simple as detaching the databases from the old server and attaching to the new one, due to the complexity and the relations between MOSS web applications and the database server.

The easiest way to succeed in that kind of transition; is to backup and restore your environment using the tools provided by Sharepoint Services 3 central administration site. Fortunately, Microsoft included in the restore procedure an option to reinstate your entire farm in a different database server. It lets you even modify the accounts used by the web applications or to modify the web applications themselves.

But let’s go and dig in into our solution. In the following example we have the SS-LAB1-W643KST server hosting the MOSS 2007 web applications services, search service, and Project Application service, while SS-S003-W643KST acts as the database backend of our environment. Due to a scheduled hardware replacement of SS-S003-W643KST, all databases must be moved to a named instance (EPM) of SS-S007-W643KST server.

In order to begin with our transition we need to backup the entire farm:

1. Open Sharepoint Central administration web site
2. Open the operations tab and select Perform A Backup
3. Select the checkbox next to Farm option, in order to select the entire farm, and Click the continue to backup options at the top of the page
   
   
4. Leave the type of backup to full and enter a UNC path for your backup location. Finally press the OK button to proceed to the backup progress page.

After the backup job submission, a timer job is created named “backup / restore” with schedule type set to “one time”. If you check the “timer job status” you should be able to see the “backup / restore” job set to initialized. The progress of your backup job is always available to the “backup and restore job status” page which is opening by default after step 4 of the previous backup sequence, or by selecting the “backup and restore job status” under the “backup and restore” section of the operations tab in Sharepoint Central Administration Website.

Before we begin with the restore procedure we need to disconnect the server from the farm and create a new one. Note, that since “configuration database” and “administration content database” cannot be migrated, all farm wide configuration settings must be reconfigured as a post migration step.

1. Run the Sharepoint Products and Technologies Configuration wizard and press next
2. Select the Disconnect from this server farm radio button and press next
3. Press YES to the warning message informing you about the consequences of the farm disconnection. Note that in case you had more than one MOSS front-end web servers in the same farm, you should have run the wizard on each of them, leaving the one hosting the Sharepoint Central administration web site last
4. Wait for the wizard to complete and press Finish
   
   
   
   

To create a new farm:

1. Run the Sharepoint Products and Technologies Configuration wizard and press next
2. Select the option to Create a new farm and press next to proceed to the Specify Configuration Database Settings dialog
3. Enter the new database server hostname, the new database name of the farm configuration database, and the username and password of the database access account and press next
   



4. In the Configure Sharepoint Central Administration Web Application dialog, enter a port number for your Central administration web application, along with the authentication provider you want this web application to us, and press next



5. Wait for the wizard to complete and press Finish.

At this point, we have managed to create a new MOSS 2007 farm with no web applications and content databases associated with it, beside the ones related with the Central Administration Web Site. Before we begin with the restore procedure we need to configure the MOSS 2007 search service, in order to be able to successfully restore the SSP search index later in the process.

1. Open Sharepoint Central administration web site
2. Open the operations tab and select Services On Server
3. In the Services On Server web page, select the Search Indexing radio button and press the start link next to Office Sharepoint Server Search
4. In the Office Sharepoint Server Search configuration dialog, fulfill the email address for the indexing administrator, the username and password for the search service account and the default indexing file location, and press start
   
   

We are now ready to begin the final migration step; the restore procedure. During the restore procedure the following events will take place:

• The appropriate web applications will be created and provisioned as dictated by the settings provided in the restore configuration page
• The content databases will be restored and associated with the appropriate web applications
• The shared service provider will be restored along with the configuration database, content database, Project Server Applications, User Profiles, search indexes, etc

In order to begin with the restore procedure:

1. Open Sharepoint Central administration web site
2. Open the operations tab and select Restore from backup
3. Enter the UNC path of your backup location and press the OK button
4. Select the checkbox next to Farm option in order to select the entire farm and Click the continue to restore process at the top of the page
5. In the type of restore select the new configuration option in order to be able to change the database locations. Fulfill the appropriate web application accounts and edit the New Database Server Name sections with the name of the new database server; in this scenario: SS-S007-W643KST\EPM. Finally press OK to begin the restore procedure.




6. After the restore completion, open a command prompt and execute the iisreset command to finalize the web applications propagation.

Note, that the progress of your restore job is always available to the “backup and restore job status” page which is opening by default after step 5 of the previous restore sequence, or by selecting the “backup and restore job status” under the “backup and restore” section of the operations tab in Sharepoint Central Administration Website.

As a post migration step, you should start the appropriate farm services (like “project application service”), perform a full search crawl and configure farm wide settings.