Are there any pitfalls when upgrading SQL Server Express to SQL Server Standard 2008 R2 that serves a Microsoft Sharepoint 2007 (MOSS 2077) implementation?
We recently completed an update of a team foundation sql server by installing the Standard Edition of SQL over the top of the existing Express edition with no issue. So we plan to upgrade the Sharepoint server the same way.
Are there any pitfalls or hints we should be aware of? I am concerned that the data and customizations done in Sharepoint are not lost or damaged.
We will as a matter of course be taking backups prior to any change.
Question: Are there any pitfalls when upgrading SQL Server Express to SQL Server Standard 2008 R2 that serves a Microsoft Sharepoint 2007 (MOSS 2077) implementation?
There are no real pitfalls, but definitely some items to put on your migration checklist. You need to convert the whole instance from Express to a 2008 R2 "real database solution". This is a good move to make.
Given that you have an Express edition that came with the Sharepoint installation, it is mostly likely not a SQL Server 2008 or SQL Server 2008 R2 edition, and most likely have not SSMS tools installed. You will need the Standard Edition installation media and the product key. That is a given. Run the setup program from the standard edition media directly. Do NOT try to run setup from the installed SQL Server and use Edition upgrade option.
Verify the installation using SQLCMD once you have the Standard Edition in place. Run setup from installation media again and install the desired shared components such as SSMS, Integration Services, Books Online, etc. The Sharepoint setup installed SQL Agent installed with SQL Express by the Sharepoint installation may be disabled and set to run as "Network Service" and not have the SysAdmin SQL Server rights.
Change the service account for the Agent to have the sysadmin rights. The SQL Agent will then start. Check database options, file size, auto grow settings, and other maintenance items for the sharepoint databases. You probably want to move the Sharepoint databases to a better location than the default folders in Program Files --> Common Files. Good luck as you move off of the Express Edition.