Article: 282603

Title: How to Upgrade from MSDE to SQL Server 2005 Express

This article applies to: MIP Fund Accounting, version 10.1 and greater.

Question

How do I upgrade my existing instance of MIP Fund Accounting so it uses Microsoft SQL Server 2005 Express instead of MSDE?

Answer

Starting with version 9.0, the installation CD for MIP Fund Accounting contains Microsoft SQL Server 2005 Express instead of the older SQL Server 2000 Desktop Engine (MSDE). Existing users will most likely be using MSDE unless they own full SQL Server 2000 or SQL Server 2005 Standard Edition. In order to take advantage of the newer SQL technology it will be necessary to backup your organization database(s) and the system database (npssqlsys), remove the existing MIP Server and MSDE instance, then install SQL 2005 Express and the MIP Server from the v10.1 (or greater) CD. SQL Server 2005 Express offers increased maximum database size (4GB), a graphical user interface (SQL Server 2005 Management Studio Express), and it does not have the MSDE workload governor.

IMPORTANT!

· The first step is to backup all organization database(s) and the system database (npssqlsys). The uninstall process will not remove files which were not installed by MIP or Microsoft, but it is the best practice to have current backups when performing a significant operation.

· If you plan on making a copy of MIP Share to protect custom formats or other files, please move the copy of MIP Share to another computer or suitable backup media before you reinstall MIP. Copies of MIP Share may confuse the installation routine and cause problems.

1. On the computer where MIP Fund Accounting Server is installed, go into Control Panel and open Add or Remove Programs. Uninstall all components related to MIP Fund Accounting in the following order. (If you have multiple instances of SQL on the server and need to identify which one MIP is using, go into the registry – HKLM>Software>MIPServer and look at the ServerName value).

i. MIP Fund Accounting Workstation – (if it exists)

ii. Desktop – (if it exists)

iii. MIP Fund Accounting Server

iv. MIP Fund Accounting Sample Organization – (if it exists)

v. Microsoft SQL Server Desktop Engine (MIP_Engine) – Note: MIP_Engine is the default name; your instance could be different.

vi. Microsoft Merge Modules for MIP Fund Accounting

NOTE: The example above is for version 8.x. If you’re using v10.x, simply navigate to \MIP Share\Setup and run the Uninstall.exe file. That will remove all of the workstation components automatically. You would then remove the MIP Fund Accounting Server and the Microsoft SQL Server Desktop Engine to complete the uninstall process on the MIP Server computer.

2. The files and folders left behind will be your organization database(s) and your backup files. Do not delete any files or folders at this point. If MSDE was installed using the default path (C:\Program Files\Microsoft SQL Server\MSSQL$MIP_ENGINE\Data), the Data folder will contain your organization database. You may leave it here for the moment. In this example, MyDatabase is the live production database I had been using before uninstalling MIP.

3. All backups made by the MIP backup utility (*.NPS) will be in the Backup folder right above the Data folder unless the location was changed. These locations will be relevant after MIP Fund Accounting has been reinstalled.

4. Please locate the MIP Share folder if you have more than one drive or partition on the server. Custom formats will remain behind in the <DriveLetter>:\MIP Share\Formats folder, for example, and you will want to reinstall MIP Share to the same location so those files will be available. If MIP Share was removed during the uninstall process, that means all files were installed by MIP and will be reinstalled from the CD.

5. Please verify that Microsoft .NET Framework 2.0 is installed before you try to install the MIP Server. Go to Control Panel and open Add or Remove Programs to determine if Microsoft .NET Framework 2.0 is installed. (Note: Microsoft .NET Framework 2.0 is considered part of the operating system for Microsoft Windows Server 2008 and Microsoft Windows Vista.)

6. Insert the v10.1 or greater CD to install MIP Fund Accounting and choose the radio button for SQL Express (To be installed from the CD).

7. Name your SQL instance and provide a password for the SA user.

(NOTE: In this example, the previous SQL instance name was MIP_Engine. To prevent confusion about which version of SQL Server you are using with MIP, it would help if you named the instance something other than MIP_Engine. However, changing the SQL instance name would require you uninstall and reinstall all MIP Fund Accounting Workstations on the network so they would have the correct registry entries. You may use the previous instance name, if desired. In this example, the instance name would be MIP_ENGINE instead of SQLExpress.)

8. Follow the installation wizard until MIP Fund Accounting Server and Workstation are installed.

9. Move the backups you made of your organization database(s) and system database (npssqlsys) from the old Backup folder under C:\Program Files\Microsoft SQL Server\MSSQL$MIP_Engine\Backup to the Backup folder created by the SQL 2005 Express installation – C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.

10. Go into the Administration Module using the Admin user and restore npssqlsys from the Backup folder for SQL 2005 Express to restore MIP users. Finally, restore your organization database(s) – DO NOT restore an organization database using npssqlsys from the drop-down menu. New installations require that you type in the organization database name in the Restore to Database field; once restored, the database name will be in the drop-down for future use.

11. At this point MIP Fund Accounting has been successfully migrated from MDSE to SQL Server 2005 Express. You may delete the MSSQL$MIP_ENGINE folder at this time as it is no longer used by MIP Fund Accounting.