Home > SI5 Documentation > User Guide > Support Solutions > Upgrade > MSDE to SQL EXPRESS UPGRADE

MSDE to SQL EXPRESS UPGRADE

Upgrading from MSDE (SI4) to SQL EXPRESS 2005

 

Reason to Upgrade:

 

Desktop Engine (MSDE) has several limitations, including a maximum file storage size of 2GB and a maximum of 5 concurrent connections locally (25 for websites). Over time you may reach these limits and will lose the ability to storage projects on the server (you will still be able to create them locally). SQL Express increases that storage limit to 4GB.  The steps below will show you how to upgrade your existing MSDE database to SQL Express to allow room for growth.

 

Before you begin make a backup and download the following files:

 Backup:

Ø  C:\Program Files\D-Tools\SI 4 ß Backup the entire SI4 folder

Ø  C:\Program Files\Microsoft SQL Server\MSSQL$DTOOLSMSDE\ ß Backup the entire Data folder

 

 Download:

1. Download SQL EXPRESS 2005 - Click Here

2. Download SQL Server Management Studio Express – Click Here

 

If your MSDE database only has one instance running (DTOOLSMSDE) choose Option 1.  If you have more than one instance running, scroll down to Option 2.

Option 1: Upgrade the existing MSDE install using SqlServer 2005 Express (with DToolsMSDE as the only instance)

 

 1.    Install SQL Server 2005 OVER the existing instance of DTOOLSMSDE

 

  1. Uncheck “Hide advanced configuration options”

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image001.png

 


  1. Choose Default instance. The Default Instance option will upgrade an existing default MSDE instance to SQL Express (in this case our DToolsMSDE instance will upgrade to SQL Express)

 File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image002.png

 

Note: If you have more than one instance, you will need to select “Named Instance and enter the proper instance name”

 

There is no need to change the .INI files or anything as the instance name, username and password are the same.

Option 2: Install SQL Server 2005 Express side-by-side with the existing MSDE.

 

1.  Install SQL Server 2005 with a new instance

2.  Uncheck “Hide advanced configuration options” as pictured below

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image001.png

 


3.  Choose Mixed Mode authentication and set a password (this password needs to also be the same in the .ini files for LANSync and Business Manager)

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image003.png

 


  1. In the Installation process Add the user to the Administrator role

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image004.png

 


  1. Detach the following database from DTOOLSMSDE instance.

a.    DToolsMastertable

b.    DToolsProject40

c.    DToolsUsers

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image005.png

 

  1. Move the below files from “C:\Program Files\Microsoft SQL Server\MSSQL$DTOOLSMSDE\Data” to “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data”

d.    DToolsMasterTable

e.    DToolsMasterTable_log

f.     DToolsProjects40

g.    DToolsProjects40_log

h.    DToolsUsers

i.      DToolsUsers_log

 

  1. Attach the above database files to the new instance (default is usually YOUR_SERVERNAME\MSSQLSERVER)
    1. To re-attach Database files simply reverse the previous step. Righ-click on the server instance and select ATTACH.  You will need to do one at a time

  2. Add user “d-toolsprojects” to the new instance with Sql Server authentication

j.     Login Name: d-toolsprojects

k.    Password: d-toolsprojectsadmin

 

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image006.png

 

 

  1. Assign sysadmin server role

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image007.png

 

 

Note: Under “Default Database: select DToolsUser

 

a.    You will need to run this query on each of the following Database files as pictured below (DToolsMasterTable,  DToolsProjects40, DToolsUsers)

 

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image008.png

 

  1. Type the following line of code verbatim, select all the text and click [Execute]: sp_change_users_login “AUTO_FIX’,'DToolsData”

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image009.png

  

 

Ø  You will see the following confirmation that one orphaned user was fixed

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image010.png

 


  1. Change the following ini files found in “C:\Program Files\D-Tools\SI 4” for all clients

 

a.    D-ToolsBusinessManager.ini

b.    D-ToolsLanSyncV4.ini

 

  1. Changes to be made are as follows

c.    Change the Datasource name to the new instance (sometimes this format works YOUR_SERVER_NAME\)

d.    Add UserName “sa” and password = password set in step 3

 

File:User:JoseM/MSDE_to_SQL_EXPRESS_UPGRADE/image011.png

 

 

 

 

Last modified
17:01, 26 Nov 2013

Tags

Classifications

This page has no classifications.