04 Oct Migrating from MSDE to SQL2005

Posted at 11:30h in Uncategorized by
0 Likes

<black sheep>

This blog is a move away from the scenario based blogs on PS2007 that I’ve been writing, instead it’s an overview of a situation that I think will become more common as organisations upgrade their project server databases (and WSS2.0) to SQL2005 (either from SQL2000, or in some cases there is a requirment to do it from MSDE).  Normally this is done by the IT guys, but of course it’s not as straight forward as they first think.

This blog comes about from direct customer experience, and I don’t necessarily have all the answers to hand, but it describes the approach I took and the pitfalls along the way.  Also, most of it is written from memory so use this as a guide only!

Existing environment – the customer had a single server installation of PS2003 SP2a which had been running for about a year.  The installation used MSDE and WMSDE on the production box, both installed by during the initial installation.  MSDE is used for the Project Server databases, and WMSDE is used by WSS2.0 (the main differences between the two databases is that WMSDE removes the 4GB size restriction)

Desired environment – a two server installation, leaving the existing application server alone, but moving the databases to a dedicated SQL2005 database server.

Installation steps

Step 1 – install SQL on the database server

The first job was to install SQL2005 SP1 on the new server.  This was just a quesiton of following the instructions.  The SQL2005 setup is  different from SQL2000 so if you are not used to it then try it first.  SP2 had just been released, but we decided it was too risky to include this in the equation, so we stuck with SP1.   The SQL2005 install includes Analysis Services, so everything is nicely tied up in one installation and one SP install.  There aren’t any instructions for installing SQL2005 for use with ProjectServer 2003, so I’ve given you a few screen shots to help.

At the end of setup UI, SQL is ready to install and it gives a brief summary.

Once the database is installed, it needs to be managed.  You need to install the workstation components in order to install the SQL Server Management Studio.

Once everything is installed, it’s time to install SP1.

Step 2 – create a new PS database.

My customer wanted a test environment creating in addition to the live environment, so I created a new DB and then ran the setupdb.cmd command to create the PS tables etc.  This has the added advantage of create the correct roles for ProjectServer.  The standard websps.sql script that setupdb calls need to be modified as the SQL statements between 2000 and 2005 are different, and websps.sql needs to be modified as below. 

  •  
    • Line 8781: select WASSN_ID from MSP_WEB_ASSIGNMENTS WAT2 WITH (index…
    • Line 19456: select WASSN_ID from MSP_WEB_ASSIGNMENTS WA WITH (index …
    • Line 26315: select WASSN_ID from MSP_WEB_ASSIGNMENTS WAT2 WITH (index…

The full details of these modifications can be found in the references section

Once the database is created, you’ll need to upgrade it to SP2a.  Again, you’ll need to modify websps.sql in the SP2a installation directory as above.

This should give a configured database, and allows us to test the connectivity from our application server to the db server.

If your existing system isn’t on SP2a, then I would perform an SP2a upgrade prior to moving any databases.

Step 3 – restore the existing MSDE database

Take the existing database from MSDE and restore it onto the db server.  For this I simply stopped the MSDE service, copied over the database files, and attached them an empty database on the db server.  Very simple indeed.  I’ve seen posts that say that the database needs to be run in SQL2000 compatibility mode, but this wasn’t an issue for me.

Step 4 – use Editsite to configure the ProjectServer database info.

I restarted MSDE on the app server, and then ran editsite.  Just to be sure, I always run editsite to check everything is okay.  It’s also a good way to verify database names and role names etc.  I made sure using Editsite and SQL Management Studio that the roles were correct etc, and then edited the existing ProjectServer instance in Editsite to reflect the new db server.  This part of the installation involved lots of checking and tweaking of the roles etc before getting it working.

Step 5 – migration of WSS databases

This should have been simple, as there are various KB articles (referenced) on how to move WSS2.0 databases.  However, I was unsuccesful in being able to move the databases and retain the integrity of the system, I think in part because when you do a WSS2.0 install onto WMSDE lots of decisons are taken for you, and I couldn’t access the configuration database once I’d moved it.  So the upshot was to export the WSS data, run wsswiz to remove the ProjectServer templates (I’m not sure if this is exactly necessary but I did it anyway) then remove WSS2.0 from the app server, remove WMSDE, reinstall WSS2.0 and point the installation to the new db server, run wsswiz to reconfigure WSS and PS to talk to each other, install the WSS SP2a upgrade (from ProjectServer SP2a), run PSCOMplus to set the identity for WSS, link PS to the new WSS instance (via the ProjectServer Admin page), and import the WSS data (sites, docus etc) and then link the WSS sites back to the projects.  All in all there is a lot to remember here, and not too much is documented.   How I love WSS!

This gives us a system that is now does exactly what we had before, but on a new database server.

Step 6 – Analysis Services

ProjectServer 2003 needs the following hotfix applied before it will connect to analysis services – KB 905386.  Note the scripts detailed will need to be run on each projectserver database.  There is much to download and update here, so expect it to take a while.  Plus MS can take 4+ hours to get the hotfix to you, so get it a few days beforehand.

Step 7 – Test environment

Using editsite, a test environment was created that used the original database I created using the setupdb.cmd command.  Remember that this database needs updating to SP2a, plus have the analysis services hotfix scripts run against it.  I had big issues here, the existing migrated database would connect, but not the new one, and I kept getting the following error “Cannot connect to <database> on <server> server” .  It came down to resetting the passwords for the SQL users (MSProjectServerRole etc).  Once this was fixed everything worked okay, but it stumped me for a while as I checked and rechecked everything.  Plus the error came back immediatley, and I didn’t at first think it was a permissions problem. 

References

SQL statement modifications – http://blogs.lv0.net/mpatest/archive/2005/11/18/182.aspx

Moving WSS databases – http://www.microsoft.com/technet/windowsserver/sharepoint/v2/movewsdb.mspx; KB 905386 

Analysis Services hotfix – KB 905386; http://support.microsoft.com/default.aspx/kb/921116/

SQL2005 and ProjectServer 2003 issue – kb917401

Credits – thanks to Brian Smith, Bill Raymond and Kate Simpson and my customer – they are all unwitting collaborators.

The end

As is usual, 5% of the work above took 90% of the effort, it’s always just one or two snags that take the time!

enjoy….  Ben

Tags: