Monday, September 19, 2011

Problem Provisioning PWA Site after SP1

** UPDATE2:  OK so I didn't reboot before that previous note.  This has now resolved the issue.  Reboot WAS tried prior to June CU

* UPDATE:  After installing June CU2011 Server Rollups (SPF & SPS+PS) I am still seeing the same issues as before.  More research to do.....

Since my rig had Project Server 2010 SP1 packages installed I have seen PWA provisioning fail when creating a brand new PWA Site with clean DB names etc.

Note:  This is NOT a restore scenario:

Firstly I see the following, which is what I would expect:

09/19/2011 15:45:59.32 OWSTIMER.EXE (0x0C30)                   0x05C4 Project Server                 Provisioning                   6974 Information Provisioning 'zzzzzzzzzzzzzzzzz': Server versions: Primary 10.50.1600.1, Reporting 10.50.1600.1. 47871661-d3d4-46fa-9c73-f5faf328a37d

09/19/2011 15:45:59.35 OWSTIMER.EXE (0x0C30)                   0x05C4 Project Server                 Provisioning                   6975 Information Provisioning 'zzzzzzzzzzzzzzzzz': Databases specified for site 'ProjectServer' are as follows: Published: Name = 'zzzzzzzzzzzzzzzzz_Published', State = NotCreated Draft: Name = 'zzzzzzzzzzzzzzzzz_Draft', State = NotCreated Archive: Name = 'zzzzzzzzzzzzzzzzz_Archive', State = NotCreated Reporting: Name = 'zzzzzzzzzzzzzzzzz_Reporting', State = NotCreated 47871661-d3d4-46fa-9c73-f5faf328a37d

Then a little while later prior to the above job completing I see a second PWA Provisioning job is initiated, that reuses the PWA Site url configured by me and then shows the following in ULS:

09/19/2011 15:49:59.61 OWSTIMER.EXE (0x18CC)                   0x2238 Project Server                 Provisioning                   6974 Information Provisioning 'zzzzzzzzzzzzzzzzz': Server versions: Primary 10.50.1600.1, Reporting 10.50.1600.1. 27bbee07-9442-48ce-af3e-eb7dabaf80ea

09/19/2011 15:49:59.95 OWSTIMER.EXE (0x18CC)                   0x2238 Project Server                 Provisioning                   6975 Information Provisioning 'zzzzzzzzzzzzzzzzz': Databases specified for site 'ProjectServer' are as follows: Published: Name = 'zzzzzzzzzzzzzzzzz_Published', State = NotEmpty Draft: Name = 'zzzzzzzzzzzzzzzzz_Draft', State = Empty Archive: Name = 'zzzzzzzzzzzzzzzzz_Archive', State = Empty Reporting: Name = 'zzzzzzzzzzzzzzzzz_Reporting', State = Empty 27bbee07-9442-48ce-af3e-eb7dabaf80ea

Shortly after this, guess what:

09/19/2011 15:49:59.95 OWSTIMER.EXE (0x18CC)                   0x2238 Project Server                 Provisioning                   7070 Critical Provisioning 'zzzzzzzzzzzzzzzzz': One or more of the databases already contains schema. When editing or creating a Project Server instance, you may specify: * Four databases that do not exist * Four existing, blank databases * Four existing Project Server databases of the same version from the same installation. Combinations of blank, new, and existing databases are not allowed. 27bbee07-9442-48ce-af3e-eb7dabaf80ea
Subtle but important difference here... this second provisioning job that appears to have been initiated part way through the initial DB provisioning job now sees that the Published database = NotEmpty state.

Hypothesis here is that the first correct provision job has begun to build the  DB schema, and then been kicked out by the erroneous second job, which finds a mismatch set of databases and bombs out.


So far it appears that the June CU resolves this issue, but I am retesting this now.  be back after the break

Friday, September 9, 2011


Sometime over the last couple of weeks - whilst running some BI-focused EPM Demos - I have come to realise that sometimes no manner of demo will get people interested when they just want to jump to the good stuff, the charts and graphs, the decomposition tree, indicators and traffic lights and KPI's etc etc

Sometime's they just want the shiny-shiny

So with this in mind I've given my blog a spring clean and a spruce up.  Time is pushed at the moment but I intend to attempt to post as much as I can over the coming months.  As ever, there will be some trouble shooting and some how-to's that I hope people can get benefit from.

My SharePoint_Config Database fall over go bang

The other day I was tibnkering on my new demo rig (I love SSD's) and the whole OS Drive locked up on my, causing me to hard-crash the machine.

Uh-ho i thought... my VM was running at crash point... better check it over.

Lo and behold, my rig was dead on reboot.  Upon review my config_db was suspect - go figure - and I had to try and get it back (as I was going to lose quite a lot of work if i didn't).

So after a little trawl around I found the following.  I hope it helps someone else in the future:

Run this in SQL:

               alter database “Sharepoint_config” set emergency

Set DB to single user mode 

Run this in SQL to find the SPID for the connection to the Config_DB


disconnect from SQL
ensure SQL Browser service started
open SQL Mgt but dont connect to the server
New Query

               Kill SPID# from SP_WHO2 (where # is the SPID noted previously)

use Sharepoint_config
dbcc checkdb ("sharepoint_config", repair_allow_data_loss)

Run the following SQL

               SET MULTI_USER

Your DB should now be back and happy

Timesheet solutions in project server 2010 - #1 Security black holes and foibles
This is my first outing into a full blown stand-alone timesheet solution.  Yes, I’ve done the full bottom-up and top-down configurations before but never JUST timesheets.
The aim of this solution is to
- minimise the administrative overheads associated to running an EPM solution
- minimise “Project Owner” involvement in the process and thus minimise MS Project Pro licence needs
- Capture historic data (actual work and cost)
- A fixed list of “activities” per project, duplicated for work performed pre project and during the project
- Allow anyone to work on any project within their particular group/team/dept
- provide some insightful BI and reports off the back
The entry point in for this solution is the [magical] new option of automated publishing via rules, delivered as part of SP1, without which we would not be moving forward with this.
Voyage of discovery:
During the first two days of testing, I have come across the following challenges:
1 - Data Security on the Insert Row functions in timesheets (“Insert Task” and “Add Yourself to a Task)
2 - Deactivating unwanted Timesheet Add Line functions (Insert Row | Create New Task)
3 - Limitations of creating projects from templates via PWA (Project.CreateProjectFromTemplate method)
4 - curious behaviour of the auto-publish function when calculating Actual Cost  
What follows in this post is a deep-ish dive into the world of data security on Timesheet Insert Row options

1 - Data Security on the insert row functions - ”Insert Task” and “Add Yourself to a Task
I started this process as I wanted to ensure that the menu options for these functions was scalable enough to be able to take the number of projects I wanted to throw at it, but also to confirm that I could get projects OUT of the list again at the appropriate time, retaining control throughout the project lifecycle.
These options are really important to me in this scenario as I need team members from certain departments/teams to be able to add tasks from the dept/team projects into their timesheets and begin to assign time, as we will not be individually resourcing tasks.  We will also not be scheduling tasks as such so the ability to re-add a task (insert task function) is also key.
My starting assumption here was that the projects from which you can select tasks would be the projects you have permission to see (with appropriate Category permissions for the functions) via My Tasks so to prove this I temporarily elevated the My Tasks category to see ALL projects.  I then created some dummy tasks in a handful or projects.
At this point - if the rule is true - I would be able to add any of these tasks to my timesheet via “Add yourself to an existing task” function.  Of course on testing this is not the case.  
How “Add yourself to an existing Task” works:
It turns out that the list of available projects form which you can pull tasks onto the timesheet is governed solely by the projects that include you in their Project Team, NOT by the application security model (which of course includes the switch “The user is on the project team” but we wont mention that).  
Although I understand the logic here, as the ability to perform this task is a category permission, it seems strange that this is not governed by category data access rules.  My working assumption is that this would also require Build Team from Enterprise permissions over the projects in order to allow the resource to assign themselves to the project team which all sounds logical and achievable within the constrains of application security in my head, but I dont develop software so…..
the agreed workaround for this is that each dept/team resource is assigned to the project team as needed, and then the team members can pick whichever tasks they need.
Note : Tied functions 
As far as I can tell so far, this function is tied to the Create new task function by category feature “Create new task or assignment” so you get either or both, which I REALLY REALLY REALLY don’t want as I need the task list is fixed and controlled across all projects (thats engineering for you) and we will be auto-approving task reassignment changes via rules.  As far as I can tell so far, my only option is to deactivate this through custom code which I am currently experimenting with.  More to follow (I hope) 
Note:  Removing Projects and tasks from the list:-
This permission/option DOES conform to the Security driven approach for locking down/closing projects.  However it appears that it DOES NOT take account of projects that have no remaining tasks open for update.  The Project and Summary Tasks will still appear in the list of available projects and tasks, but there will be no low-level tasks if these are all closed for update. T
How “Add Task” works
The Add Task function allows you access to existing Task Assignments to re-add them to your timesheet when they have “dropped off”.  This in itself is fine and dandy for me but how does it handle closed tasks and projects as if it has all my assignments in there the project list is going to get big QUICK (as I am very busy)
During testing of the project closedown procedure I notice that using [again] Category level security to attempt to close a project (using Deny) does not impact the list of projects and tasks on display.  This can only be done using the “Close Tasks to Update” function as far as I can see so far (and the project will only be hidden from view once there are no further tasks to be updated)/
A brief note on Closing Tasks and Projects:
In order to stop users accessing closed tasks during the lifespan of the project, you should use the Close Tasks to Update function as tasks are closed.  
However you are also recommended to have a strong regime for closing projects down with a DENY category once everything has been completed, to ensure that the project is removed from Timesheet Inset Row functions.