As many of you may know, or recall, D6 does not support SQL Server 2000 anymore. This is fine and good. Most larger installations moved to Oracle years ago. However, there are a large number of Microsoft shops out there, a SQL Server is a quick to standup database for development, POC, and demo environments. So I thought I would share some tidbits on two different upgrades of SQL Server 2005. Neither were in a clustered environment, but that will happen soon and I’ll share then.

Why Upgrade?

There are several reasons. One is to prepare for D6. It is required and you may as well get on the newer third-party software while you wait for D6 sp1 to come out.

The second reason is performance. EMC did a benchmark comparison between SQL Server 2000 and 2005. On identical hardware environments, they setup 5.3 sp4 and ran tests. They measured approximately a 5-fold improvement in the performance of Documentum. When you look at the details, it appears that search was an order-of-magnitude better. Even with a conservative 3-fold improvement, that is a nice performance gain. The benchmark is available on Powerlink, so go read it for yourself.

[Edit: Look here for it and other related benchmarks. It is titled EMC Documentum 5.3 SP4 with Microsoft SQL Server 2000 and SQL Server 2005.]

If you are running 5.3 sp4 or higher, I would go ahead and plan an upgrade of SQL Server if at all possible. 2005 is on its second service pack and is in decent shape. It also performs better than 2000, which shouldn’t require too much imagination to believe. Even if you aren’t planning to move to D6 soon, you may want to proceed.

Upgrading the Enterprise Edition

I thought I would start with the easy upgrade. We had in one of our client’s development environment 5.3 sp5 running against SQL Server 2000 Enterprise. Being a development environment, we don’t have a large system or any high availability configurations. Armed with an experienced DBA by our side, we went forward.

1. First we shutdown the Content Server and the dependent Documentum services. We then backed the database up. We didn’t bother backing up the system tables, which we will do in our clustered environments, as we could rebuild the system fairly quickly.
2. We ran the installation package for SQL Server 2005 Enterprise. We selected every option. The installation went smoothly and it detected and upgraded our existing system after asking for permission. We then applied Service Pack 2. This was a no brainer for me and the DBA confirmed.
3. Set the compatibility mode to 90. Here is were the DBA helped us. Each database has a compatibility mode setting. For upgraded SQL Server 2000 databases, the setting is 80 as SQL 2K is technically version 8.0. The upgrade process does not change this automatically. This allows applications to continue to work as before. However, it does limit some of the enhancements in SQL Server 2005. Since 5.3 sp5 is certified against SQL 2K5, we changed the setting. This can either be done in the properties of the database using the new SQL Server Management Studio. There is also a stored procedure that can do this, but the Studio tool is much easier to use.
4. Turned everything back on. Everything worked.

One thing I will do next time is run the Upgrade Advisor tool. This will analyze the database and make recommendations prior to upgrading. Our DBA stressed the importance of this using this tool for any environment.

Upgrading My Personal Installation

Now, like most good Documentum tinkers, I have a system, 5.3 sp4/sp5 depending on the component, installed on my laptop. This lets me tinker, develop, demo, and otherwise put the product through its paces. I figure that if I can get it to work on XP, then I can get it to work anywhere. There were some complications though.

Microsoft offers a version of SQL Server 2005 called Express for free. I figured that would be a good version for my laptop. It has limitations on database size and on memory usage, but then, so does my laptop. My problem was simple. I have the Developer edition of SQL Server 2000. That can only be upgraded to SQL Server 2005 Developer. Not wanting to pay for it, and not having easy access to it, I decided to migrate my database instead of upgrade. (For comparisons of the different editions and features, go here.)

This database migration approach should work for most SQL 2000 to SQL 2005 migrations. However, you probably want to have EMC support aware of what you are doing before you do it in a Production environment. You also probably want a SQL Server DBA around as well to help with all the details that I may not list.

1. As before, I shutdown Documentum. Create a backup of the repository database. Grant full rights to the backup file to Everyone and move it to an accessible location. You could probably get away with read access for the Local System account, but as this is temporary, don’t sweat it. After the backup, shutdown SQL Server.
2. Install SQL Server Express. There is no Service Pack as the download incorporate SP2. I installed the advanced version. This includes the Management Studio (a vast improvement over the old Enterprise Manager).
3. From the Management Studio, create a new login. Give it the same username and password as your Documentum dbo account in SQL 2000.
4. Connect as the new user and create a new database. Be sure to set the proper sort order. One thing I LOVE about SQL Server 2005 is that you can that after you create the database. I almost always forget to set the sort order during creation and end up creating it twice. No more.
5. Restore the backup into the database. Be sure to select the option to replace the existing database. Once complete, set the compatibility level to 90.
6. Delete the backup file and start Documentum.

Once again, it worked. Both of my systems are too small to notice any real gains in performance. I think they are faster, but that could be my imagination. However, we are going to be doing performance testing down the road and I expect to get some definitive data.