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.

MS Project Server Queuing Service Fails to Start

Symptom:
After upgrading Project server 2007 to SP1, MS Office Project Server Event Service and MS Office project Server Queue service cannot start with the following error: The service did not respond to the start or control request in a timely fashion.

Cause:
After using SysInternals tools, I’ve noticed that both processes were trying to connect to http://crl.microsoft.com/. Since my project server machine is behind a firewall, the outgoing HTTP requests were dropped, and the long delay caused the CRL check to time out. Therefore the service control manager reported that the services were taking too long to start.

Solution:
Temporarily allow outgoing HTTP requests for the Project Server Machine.

How to create enterprise calculated filed in MS Project Server 2007

There are many times when we want to create custom calculated fields and share it with the rest of our organization in a MS Project Server environment; but how can we accomplish that?


First, we have to open MS Project Professional 2007 and go to Tools -> Customize -> Fields. In the custom fields dialog box, we need to select the field category (task, resource or project) and field type (cost, date, text, etc).




For the sake of this tutorial, we are going to create an enterprise task (text) field named El. Duration, which will calculate for us the elapsed days between the start and finish dates of a task.


Press the “Formula…” button to open the formula dialog box. From there you are able to create a formula, capable to calculate the elapsed days between the start and finish date of a task.


In the formula dialog box by pressing the function button, we can see the list of all available functions wecan use, in order to build our formula. Four our example we will choose the DateDiff function, which can support five entries: interval, date1, date2, firstdayofweek and firstweekofyear. We want to be able to measure the elapsed days in the day unit, so we set the first option (interval) to “d” which stands for days and our formula now looks like DateDiff(“d”;date1;date2;firstdayofweek;firstdayofmonth).

In order to figure out the naming conversion for the fields we want to use in our formula, we are pressing the “fields” button and we are now able to see all the available fields we can use in our formula. For our example we want the [start] and [finish] fields to be chosen, in order our formula to become like DateDiff(“d”;[start];[finish]) and to be able to calculate the elapsed days between start and finish dates of a task. For cosmetic purposes I am transforming the formula to DateDiff(“d”;[start];[finish]) & “d” to include the “d” character in future views.

The next step is to open the PWA page, with a project server administrative account, and go to Server Settings -> Enterprise Custom Field Definition. In the “New Custom Field” web page enter a name for the custom field (El. Duration for our example) and select the “formula” radio button. Copy and paste the formula created in project pro 2007 into the text box area and replace “;” character with “,” (see picture). In the “entity and type” section select as entity the “task” option and as type the “text” option and save the custom field definition.