Microsoft KB Archive/932128

= How to migrate a SoftGrid Server database from MSDE to SQL Server 2000 =

Article ID: 932128

Article Last Modified on 8/15/2007

-

APPLIES TO


 * Microsoft SoftGrid for Terminal Services
 * Microsoft SoftGrid for Windows Desktops

-



Important This article contains information about how to modify the registry. Make sure that you back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows registry



SUMMARY
This article describes how to migrate a SoftGrid Server database from Microsoft SQL Server Desktop Engine (MSDE) to Microsoft SQL Server 2000.



MORE INFORMATION
The default database for the SoftGrid Server installation is MSDE. You can back up and restore the MSDE database to a computer that is running SQL Server 2000. To do this, follow these steps.

Note These steps assume that a computer that is running SQL Server is currently available.

Back up the MSDE database

 * 1) Start SQL Server Enterprise Manager.
 * 2) Connect to the MSDE database server. Use sa as the login name and use the password that was specified during the original installation of the SoftGrid server.
 * 3) Expand , and then expand Databases.
 * 4) Perform a complete backup of the SOFTRICITY database.

Document the SOFTRICITY database users and roles

 * 1) Start SQL Server Enterprise Manager.
 * 2) Connect to the MSDE database server. Use sa as the login name and use the password that was specified during the original installation of the SoftGrid server.
 * 3) Expand , expand Databases, expand SOFTRICITY, and then click Users.
 * 4) Document each user's name, login name, and database role memberships.

The following table includes some examples of user names, login names, and database role memberships.

A typical installation will have three users. To determine the database role membership, view the properties for each user.

Restore the MSDE database to the computer that is running SQL Server

 * 1) Start SQL Server Enterprise Manager.
 * 2) Connect to the computer that is running SQL Server.
 * 3) Expand , and then expand Databases.
 * 4) Create a new database that is named SOFTRICITY.
 * 5) Restore the previously backed up SOFTRICITY database to the newly created SOFTRICITY database.

Register Softricity database messages

 * 1) Log on to the computer that is running SQL Server.
 * 2) Click Start, click Run, type cmd.exe, and then click OK.
 * 3) At a command prompt, type osql.exe –S [server] –d [user database name] –U [login id] –P [password] . For example, type osql.exe –S SGSQL –d SOFTRICITY –U sa –P password.
 * 4) Type UPDATE db_messages, and then press ENTER.
 * 5) Type SET severity = severity, and then press ENTER.
 * 6) Type GO, and then press ENTER.
 * 7) Type quit, and then press ENTER.

Re-create the SQL Server users and assign the database role memberships
 Start SQL Server Enterprise Manager. Connect to the computer that is running SQL Server. Expand, expand Databases, expand SOFTRICITY, and then click Users. Delete the  user. Delete the softricity user. Add a new database user:  Right-click Users, and then click New Database User.</li> In the Login name box, click  .</li> In the Name box, click Browse.</li> Locate, click the user and the groups that were documented in step 4 of the &quot;Document the SOFTRICITY database users and roles&quot; section, click Add, and then click OK.</li> In the Database box, click SOFTRICITY, and then click OK.</li> In the '''The login ' ' has not been granted access to their default database: 'SOFTRICITY' and therefore will not be able to gain access to the default database. Continue message box, click Yes'''.</li> In the Login name box, click \ .</li> In the Database role membership box, click to select the Public check box and the SFTadmin check box, and then click OK.</li></ol> </li> Add a new database user: <ol style="list-style-type: lower-alpha;"> Right-click Users, and then click New Database User.</li> In the Login name box, click .</li> In the Name box, type Softricity .</li> Under Authentication, click SQL Server Authentication.</li> In the Password box, type the password that was specified during the original installation of the SoftGrid server.</li> In the Database box, click SOFTRICITY, and then click OK.</li> In the Confirm new password box, type the password that was specified during the original installation of the SoftGrid server, and then click OK.</li> <li>In the '''The login ' \ ' has not been granted access to their default database: 'SOFTRICITY' and therefore will not be able to gain access to the default database. Continue message box, click Yes'''.</li> <li>In the Login name box, click softricity.</li> <li>In the Database role membership box, click to select the following check boxes, and then click OK: <ul> <li>Public</li> <li>SFTadmin</li> <li>SFTuser</li></ul> </li> <li>In the Database role membership box, click to select each role that was documented in step 4 of the &quot;Document the SOFTRICITY database users and roles&quot; section, and then click OK.</li></ol> </li></ol>

Re-create the Softricity SQL Server Agent alerts and jobs
<ol> <li>Start SQL Server Enterprise Manager.</li> <li>Connect to the computer that is running SQL Server.</li> <li>Expand, expand Management, expand SQL Server Agent, and then click Jobs.</li> <li>Create a new job: <ol style="list-style-type: lower-alpha;"> <li>Right-click Jobs, and then click New Job.</li> <li>In the Name box, type Softricity Database (SOFTRICITY): Check Usage History .</li> <li>In the Category box, click Database Maintenance.</li> <li>In the Owner box, click sa.</li> <li>In the Description box, type This job calls a stored procedure in the associated SoftGrid database to cleanup usage data on a monthly basis .</li> <li>Click the Steps tab.</li> <li>Click New.</li> <li>In the Step name box, type Check Usage History .</li> <li>In the Type box, click Transact-SQL Script (TSQL).</li> <li>In the Database box, click SOFTRICITY.</li> <li>In the Command box, type EXEC sp_SFTcheckusagehistory .</li> <li>Click the Advanced tab.</li> <li>In the On success action box, click Quit the job reporting success.</li> <li>In the Retry attempts box, type 4 .</li> <li>In the Retry interval (minutes) box, type 1440 .</li> <li>In the Run as user box, click dbo, and then click OK.</li> <li>Click the Schedules tab.</li> <li>Click New Schedule.</li> <li>In the Name box, type Monthly Schedule .</li> <li>In the Schedule Type box, click Recurring.</li> <li>Click Change.</li> <li>Under Occurs, click Monthly.</li> <li>Under Monthly, click Day.</li> <li>In the Day box, type 1.</li> <li>In the of every month(s) box, type 1 .</li> <li>Under Daily frequency, click Occurs once at.</li> <li>In the Occurs once at box, type 2:00:00 AM .</li> <li>In the Start date box, click today's date .</li> <li>In the End date box, click No end date, and then click OK.</li> <li>Click OK two times.</li></ol> </li> <li>Create a new job: <ol style="list-style-type: lower-alpha;"> <li>Right-click Jobs, and then click New Job.</li> <li>In the Name box, type Softricity Database (SOFTRICITY): Enforce Size Limit .</li> <li>In the Category box, click Database Maintenance.</li> <li>In the Owner box, click sa.</li> <li>In the Description box, type This job calls a stored procedure in the associated SoftGrid database to cleanup usage data. An alert triggered by an increase in the database data file(s) size calls this job .</li> <li>Click the Steps tab, and then click New.</li> <li>In the Step name box, type Enforce Size Limit .</li> <li>In the Type box, click Transact-SQL Script (TSQL).</li> <li>In the Database box, click SOFTRICITY.</li> <li>In the Command box, type EXEC sp_SFTenforcesizelimit .</li> <li>Click the Advanced tab.</li> <li>In the On success action box, click Quit the job reporting success.</li> <li>In the Run as user box, click dbo, and then click OK.</li> <li>Click the Schedules tab, and then click New Alert.</li> <li>In the Name box, type Softricity Database (SOFTRICITY): Size Limit Reached .</li> <li>In the Type box, click SQL Server performance condition alert.</li> <li>In the Object box, click SQLServer:Databases.</li> <li>In the Counter box, click Data File(s) Size (KB).</li> <li>In the Instance box, click SOFTRICITY.</li> <li>In the Alert if counter box, click rises above.</li> <li>In the Value box, type 849254 .</li> <li>Click the Response tab.</li> <li>Click to clear the E-mail check box, click to clear the Net send check box, and then click OK.</li> <li>Click the Notifications tab.</li> <li>In the Write to Windows application event log box, click Whenever the job completes, and then click OK.</li></ol> </li> <li>Create a new job: <ol style="list-style-type: lower-alpha;"> <li>Right-click Jobs, and then click New Job.</li> <li>In the Name box, type Softricity Database (SOFTRICITY): Monitor Alert/Job Status .</li> <li>In the Category box, click Database Maintenance.</li> <li>In the Owner box, click sa.</li> <li>In the Description box, type This job refreshes the alerts and other jobs associated with the SoftGrid database based on configuration data stored in the database. The job is run on a daily basis .</li> <li>Click the Steps tab, and then click New.</li> <li>In the Step name box, type Refresh Database Jobs .</li> <li>In the Type box, click Transact-SQL Script (TSQL).</li> <li>In the Database box, click SOFTRICITY.</li> <li>In the Command box, type EXEC sp_SFTrefreshjobs N'REFRESH', and then click OK.</li> <li>Click New.</li> <li>In the Step name box, type Refresh Database Alerts .</li> <li>In the Type box, click Transact-SQL Script (TSQL).</li> <li>In the Database box, click SOFTRICITY.</li> <li>In the Command box, type EXEC sp_SFTrefreshalerts N'REFRESH' .</li> <li>Click the Advanced tab.</li> <li>In the On success action box, click Quit the job reporting success, and then click OK.</li> <li>Click the Schedules tab, and then click New Schedule.</li> <li>In the Name box, type Daily Schedule .</li> <li>Under Schedule Type, click Recurring.</li> <li>Click Change.</li> <li>Under Occurs, click Daily.</li> <li>In the Every Day(s) box, type 1 .</li> <li>Under Daily frequency, click Occurs once at.</li> <li>In the Occurs once at box, type 1:00:00 AM .</li> <li>In the Start date box, click .</li> <li>In the Duration end date box, click No end date, and then click OK.</li> <li>Click OK two times.</li></ol> </li></ol>

Enable the SQL Server Agent service

 * 1) On the computer that is running SQL Server, click Administrative Tools, and then click Services.
 * 2) Right-click SQLSERVERAGENT, and then click Properties.
 * 3) In the Startup type box, click Automatic.
 * 4) Click Start, and then click OK.

Reconfigure the SoftGrid communication configuration files
Follow these steps on the SoftGrid Virtual Application Server component:
 * 1) On the SoftGrid Virtual Application Server component, locate the C:\Program Files\Softricity\SoftGrid Server\conf folder.
 * 2) Open the Server.conf file by using a text editor.
 * 3) Change the host name that is between the tag and the tag to the SQL Server host name.
 * 4) Save the change, and then exit the text editor.

Follow these steps on the SoftGrid Management Server component if it is installed:
 * 1) On the SoftGrid Management Server component, locate the C:\Program Files\Softricity\SoftGrid Server\conf folder.
 * 2) Open the Manager.conf file by using a text editor.
 * 3) Change the host name that is between the tag and the tag to the SQL Server host name.
 * 4) Save the change, and then exit the text editor.

Follow these steps on the SoftGrid Management Web Service component:
 * 1) On the SoftGrid Management Web Service component, locate the C:\Program Files\Softricity\SoftGrid Management Service folder.
 * 2) Right-click the SftMgmt.udl file, and then click Properties.
 * 3) Click the Connection tab.
 * 4) In the Select or enter a server name box, type the name of the computer that is running SQL Server, and then click OK.
 * 5) On the SoftGrid Management Web Service component, click Administrative Tools, and then click Data Sources (ODBC).
 * 6) Click the System DSN tab.
 * 7) Click SoftGridReporting System Data Source, and then click Configure.
 * 8) In the Server box, type the SQL Server host name.
 * 9) Click Finish.
 * 10) In the A new ODBC data source will be created with the following configuration box, click OK.
 * 11) Click OK.

Remove the MSSQLSERVER service dependency from the SoftGrid Virtual Application Server service
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.
 * 1) On the SoftGrid Virtual Application Server, click Start, click Run, type regedit, and then click OK.
 * 2) Expand HKEY_LOCAL_MACHINE, expand SYSTEM, expand CurrentControlSet, expand Services, and then click SoftGridServer.
 * 3) Right-click DependOnService, and then click Modify.
 * 4) Clear the line that contains MSSQLSERVER, and then click OK.
 * 5) Exit Registry Editor

Reset the SoftGrid Management Web Service

 * 1) On the SoftGrid Management Web Service server, click Administrative Tools, and then click Services.
 * 2) Stop and then start the World Wide Web Publishing Service.

Reconfigure database logging

 * 1) Start the SoftGrid Management Console, and then connect to the SoftGrid Management Web Service.
 * 2) Expand SoftGrid Systems, expand , and then expand Server Groups.
 * 3) Click Default Server Group, and then click Properties.
 * 4) Click the Logging tab.
 * 5) Edit the existing SQL Database Type.
 * 6) Change the DNS Host Name to the SQL Server host name.
 * 7) Click OK two times.
 * 8) In the You must restart the SoftGrid Virtual Application Server services in this group for them to reflect the changes made box, click OK.
 * 9) Exit the SoftGrid Management Console.
 * 10) On the SoftGrid Virtual Application Server, click Administrative Tools, and then click Services.
 * 11) Stop and then start the SoftGrid Virtual Application Server service.

Repeat these steps for each database logging that is currently configured to log on to the MSDE database server and the SoftGrid server.

Reconfigure and reset the SoftGrid services
If the SQLSERVERAGENT service and the MSSQLSERVER service on the SoftGrid Virtual Application Server are being used for SoftGrid only, follow these steps:
 * 1) On the SoftGrid Virtual Application Server, click Administrative Tools, and then click Services.
 * 2) Right-click the SQLSERVERAGENT service, and then click Properties.
 * 3) In the Startup type box, click Manual.
 * 4) Click Stop, and then click OK.
 * 5) Right-click the MSSQLSERVER service, and then click Properties.
 * 6) In the Startup type box, click Manual.
 * 7) Click Stop, and then click OK.

Reboot the SoftGrid Virtual Application Server. On the SoftGrid Management Server, follow these steps.

Note If the SoftGrid Management Server is the same server as the SoftGrid Virtual Application Server that was previously rebooted, you can skip this step.
 * 1) On the SoftGrid Management Server, click Administrative Tools, and then click Services.
 * 2) Stop and then start the following services:
 * 3) * SNMP Brass Management Multiplexer Agent
 * 4) * SNMP EMANATE Master Agent
 * 5) * SoftGrid Management Server
 * 6) * SoftGrid SNMP Monitor

Keywords: kbexpertiseinter kbhowto KB932128

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.