Split existing project server 2003 into separate databases

[DaveNmo]

I have over 2300 projects in PS2003 and PWA is taking too long to refresh.
Over half of the projects are completed, so I plan to migrate them to a new
instance of Project Server (using the Site Editor tool).

I have the following questions:

1. Will I select "Single Database Configuration" since there is only one SQL
Server and one instance of the Project Server database?

2. Will I gain performance if choosing "Standard 2-way Deployment"? I
believe the goal is to create a separate instance of Project Server, a
separate PWA site with only active projects (and an archive PWA site for
closed projects), and a separate OLAP cube for each (unless one OLAP cube is
possible for both).

3. How do I migrate "completed" projects to the new database?

4. Will I need to manually re-create the enterprise resource pool in the
"archive" project server?

5. Are there any books or other reference materials on this topic? The

Thanks in advance for your reply.

David Nash

 

[Ben Howard]

Hi David,
There are various strategies for archiving projects, it's not exactly clear
to me which strategy you are choosing, as you mention editsite (which creates
new additional PS instances within an existing PS environment, rather than
being a migration tool), and then later mention new databases (so I assume
you are implementing a new separate PS environment).

So, here's my thoughts....

1. Have you tried creating an Enterprise Outline Code that holds the
project status (eg open or closed). Modify the PWA views so that it filters
out the closed projects. Does that improve perf? Assuming not then...
2. Rather than migrate your old projects, what data do you need from them?
Can you save the closed project as an MPP file, and put it on an archive WSS
share (perhaps on the Project Workspace). Then you could delete the project
from the database (note that the project and info will also be removed from
the next OLAP build, so it would be a good idea to build and save a "one-off"
cube before doing this".
3. What will you do re the project workspaces and associated docs...

4. Assuming 1&2 are no good to you, then you are into a migration scenario.
Splitting the PS environment across multiple tiers might speed things up,
but its impossible without understanding the current bottlenecks (network,
database etc). In your case, if everything else is performant except PWA,
then I don't think splitting the environment would help. Once your new PS
environment is set up, you could copy the existing DBs and install them on
the new archive env, and then delete the open projects... You'd still have
to contend with the setting up WSS too...

There's much to think about...

 

[DaveNmo]

Ben,

Thank you for your response. I've already tried your 1st idea and the
response time is still unacceptable to the users. The 2nd idea doesn't work
for us since we need to see the historical info for bidding on new projects.
I should have mentioned that we don't use WSS (not my decision..wish we did)
so we store our docs in a shared windows directory. We've monitored our
network and servers and found no bottlenecks (the delay is in pulling the PWA
views to the workstations.) We use a lot of custom fields and graphical
indicators in some of our views. So, my thinking is that if we decrease the
size of the database (we may even split the project database into 4 separate
departments) we would have less data to query.
I still have the outstanding questions related to using editsite.exe in my
original post.

 

[Ben Howard]

Hi David,
I understand, details below. From what you've given me I'm not convinced
this is the right way to go, historical data could be held in a separate
cube, and the project schedule etc would still be in an MPP file. However,
its not my system

1. You select single DB cofiguration if your SQL project database sits on a
single SQL server, rather than the DB being partitioned over 2 SQL servers.
Whether you have PS on a separate server is of no consequence for this radio
button.

2. Yes, you'll gain performance, but I would not split the database in a
"standard two way" deployment. I think you are confusing a partitioned SQL
database (views tables on one server, project tables on another server) with
a multi tier deployment (SQL on one server, Project Server on another). You
should not split the DB unless you have many thousands of projects and
resources.

3. You can't easily. Either, i) restore the DBs (this is a once only hit
but you'll have to have another method for newly closed projects, or save as
MPP and import them.

4. No, if you restore the DBs in the 1st instance.

5. Not really.

 

[DaveNmo]

Ben,

Thanks again for your rapid response. I appreciate your strategy for moving
projects to MPP files and saving the cube for historical purposes.

If I move projects off the server to an MPP file, how would I add future
projects to the "saved" cube for analyzing historical data? The other
problem I have related to this is that I don't know how to set up a separate
cube (any details available on this topic?)

Now I understand the difference between a "partitioned database" an a
multi-tiered deployment. Our database is only on one SQL server and the
Project Server is installed on another server (multi-tiered). I'd also like
to migrate to SQL 2005 but that's another topic. From what I understand,
this can be done with PS2003 but requires some registry editing.

I'm going to do some more thinking on the overall scheme, but you've
definitely helped answer my initial questions. If nothing else, I should be
able to create a sample company using editsite.exe, correct? We currently
don't have a test instance in place.

 

[Ben Howard]

Hi David,
moving to mpp will give you issues re the cubes, I would schedule to do this
on a regular basis and end up creating montly cubes or whatever.

Building a separate cube is as easy as changing the OLAP settings, the old
cubes will be left as they are. Any views will be configured to look at the
existing cube, so to look at a new cube will require the views to be
configured. Alternatively you can be connect to the cube via Excel.

You'll need some advanced tools to check several cubes at once, not
something I've tried.

You can easily create a new instance on PS using editsite for a test company.