MSDE and SSE

Today I learned of a client that was having some problem with their MSDE database and Oracle told them to upgrade it to SSE. I don't know what was wrong or how Oracle thought it would fix this unknown problem, but using an SSE database on your deployment server is just a good idea anyway.

If you're installing 9.0 or something, you're going to install SSE or the Oracle database so you won't have to worry about upgrading. If you're on some ancient release like 8.12 or before then it's possible you're still running on MSDE.

It's really easy to upgrade to SSE, the problem is finding the freaking documentation. It's linked in the special instructions for the 8.12 planner ESU. Take a look on support.oracle.com and find Doc ID 756629.1, scroll about halfway down to the link for Special Instructions for upgrading to SQL Server 2005 Express Edition, click that, find the link on the new page that says the same thing and click that, then click the Here link to go to the actual instructions. Can they make that any more complicated? Ugh.

I guess I should have said this before telling you about upgrading to SSE - make sure you back up your MSDE databases. There is no way to downgrade to MSDE from SSE. You have to uninstall SSE, reinstall MSDE and reattach your original databases. If you forget to back that stuff up, then you are screwed. You have to go with SSE no matter what.

Anyway, let's say you followed the instructions and you jacked it up. You can always reinstall SSE if you want. This'll be your only option if you didn't backup the MSDE databases. To reinstall SSE just follow the directions in Doc ID 661133.1, that'll fix everything right up.

A side note about the SSE database: If you install Visual Studio .NET and don't deselect everything except C++, it will install its own SSE database and place certain restrictions on it that will prevent the E1 SSE database from working correctly. If you install SQL server tools on the deployment server, it will also install an SSE database that will interfere with E1. On the first option, you're almost screwed. You have to completely uninstall Visual Studio and reinstall it the right way. On the second option you can simply stop the other SSE service and the E1 SSE will work fine.

I digress. Now you have SSE installed and what-not, but for some reason the planner DBs aren't populating or maybe you can't build ESUs or packages. One thing I've seen is that the JDE user isn't created or isn't assigned any privileges on the planner databases. This is easy to fix: Connect to the JDESSELocal database via SQL Server Management Studio and add/fix the JDE user. Simple.

If it all goes to hell and doesn't come back, you can always revert to the old MSDE. Uninstall SSE, reinstall MSDE (from the E1 installation), and reattach your old planner databases. You did back those up, right?