How to migrate the Microsoft SoftGrid database from one computer that is running Microsoft SQL Server to another computer that is running SQL Server

This article discusses how to migrate the database that is used by Microsoft SoftGrid from one computer that is running Microsoft SQL Server to another computer that is running SQL Server.

Prerequisites

In this article, the computer that is running SQL Server and for which the SoftGrid Server is currently configured will be known as the "source server." The computer that is running SQL Server to which the database will be migrated will be known as the "target server."

We recommend that you follow these steps during a scheduled downtime.

Additionally, we recommend that you stop the SoftGrid Virtual Application Server service before you start the migration. If the SoftGrid Virtual Application Server service is not available to the SoftGrid Clients, the SoftGrid Clients will enter Minimal Disconnected Operation (MDO) mode. In this mode, the SoftGrid Clients will continue to stream applications that are completely loaded in cache. However, if the SoftGrid Virtual Application Server service is available but the migration is incomplete, the SoftGrid Clients may be unable to enter MDO. Therefore, the SoftGrid Clients will receive a "launch failure" notice.

Stopping the SoftGrid Virtual Application Server service

1. On the SoftGrid Virtual Application Server, click Start, point to All Programs, point to Administrative Tools, and then click Services.
2. Right-click the SoftGrid Virtual Application Server service. Then click Stop.

Backing up the SQL Server Desktop Engine database

1. Start SQL Server Enterprise Manager.
2. Connect to the source database server as the sa user by using password that was specified during the original installation of the SoftGrid Server.
3. Expand sql_server_name, and then click Databases.
4. Make a complete backup of the SOFTRICITY database

Documenting the SOFTRICITY database Users and Roles

1. Start SQL Server Enterprise Manager.
2. Connect to the source database server as the sa user by using the password that was specified during the original installation of the SoftGrid Server.
3. Expand sql_server_name, expand Databases, expand SOFTRICITY, and then click Users.
   
   Note In this step, sql_server_name represents the actual name of the computer that is running SQL Server.
4. Document the Name value, the Login Name value, and the Database Role Memberships values for each user. For example, document the information that is in the following table.

A typical installation has three users. The Database Role Membership value can be located in the Properties dialog box for each user.

Restoring the source database to the target SQL Server

1. Start SQL Server Enterprise Manager.
2. Connect to the computer that is running SQL Server.
3. Expand sql_server_name. Then click Databases.
4. Create a database that is named "SOFTRICITY."
5. Restore the previously backed-up SOFTRICITY database to the newly created SOFTRICITY database

Registering Softricity Database Messages

1. Log on to the computer that is running SQL Server.
2. Open a Command Prompt window.
3. Type osql.exe –S [server] –d [user database name] –U [login id] –P [password]
   
   For example, type the following command:
   
   osql.exe –S SGSQL –d SOFTRICITY –U sa –P password
4. Type UPDATE db_messages. Then press ENTER.
5. Type SET severity = severity. Then press ENTER.
6. Type GO. Then press ENTER.
7. Type exit. Then press ENTER.

Re-creating the SQL Server users and assigning the Database Role Memberships

1. Start SQL Server Enterprise Manager.
2. Connect to the computer that is running SQL Server.
3. Expand sql_server_name, expand Databases, expand SOFTRICITY, and then click Users.
   
   Note In this step, sql_server_name represents the actual name of the server.
4. Delete the domain_name\SoftGrid administrators group user.
   
   Note In this step, domain_name\SoftGrid administrators group represents the domain name and group name.
5. Delete the Softricity user.
6. Add a database user. To do this, follow these steps:
   1. Specify the following settings:
      • Login name: <new>
      • Name: click the ellipsis button (...) to select and add the group name
   2. Click OK.
   3. Locate and then select the domain_name, add the SoftGrid administrators group from step 4, and then click OK.
   4. In the Database list, click SOFTRICITY. Then click OK.
   5. Specify the following settings:
      • The login 'domain_name\SoftGrid administrators group' has not been granted access to their default database: 'SOFTRICITY' and therefore will be unable to gain access to the default database. Continue?: Yes
      • Login name: domain_name\SoftGrid administrators group
   6. In the Database role membership window area, click to select the public check box and the SFTadmin check box in the Permit in Database Role column.
   7. Click OK
7. Add a new database user. To do this, follow these steps:
   1. Specify the following settings:
      • Login name: <new>
      • Name: softricity
      • Authentication: SQL Server Authentication
      • Password: the password that was specified during the original installation of the SoftGrid Server
      • Database: SOFTRICITY
   2. Click OK.
   3. Specify the following settings:
      • The login 'domain_name\SoftGrid administrators group' has not been granted access to their default database: 'SOFTRICITY' and therefore will be unable to gain access to the default database. Continue?: Yes
      • Confirm new password: the password that was specified during the original installation of the SoftGrid Server
   4. Click OK.
   5. Specify the following setting:
      • Login name: softricity
   6. In the Database role membership window area: click to select the public check box, the SFTadmin check box, and the SFTuser check box. Then click OK.

Re-creating the Softricity SQL Server agent alerts and jobs

1. Start SQL Server Enterprise Manager.
2. Connect to the computer that is running SQL Server.
3. Expand sql_server_name, expand Management, expand SQL Server Agent, and then click Jobs.
4. Create a job. To do this, follow these steps:
   1. Specify the following settings:
      • Name: Softricity Database (SOFTRICITY): Check Usage History
      • Category: Database Maintenance
      • Owner: sa
      • Description: This job calls a stored procedure in the associated SoftGrid database to cleanup usage data on a monthly basis.
        
        Note In this value, "cleanup" is spelled as one word.
   2. Click the Steps tab.
   3. Click New.
   4. Specify the following settings:
      • Step name: Check Usage History
      • Type: Transact-SQL Script (TSQL)
      • Database: SOFTRICITY
      • Command: type EXEC sp_SFTcheckusagehistory
   5. Click the Advanced tab.
   6. Specify the following settings:
      • On success action: Quit the job reporting success
      • Retry attempts: 4
      • Retry interval (minutes): 1440
      • Run as user: dbo.
   7. Click OK.
   8. Click the Schedules tab.
   9. Click New Schedule.
   10. Specify the following settings:
       • Name: Monthly Schedule
       • Schedule Type: Recurring
   11. Click Change.
   12. Specify the following settings:
       • Occurs: Monthly
       • Monthly: Day
       • Day: 1
       • Of every month(s): 1
       • Daily frequencies: Occurs once at
       • Occurs once at: 2:00:00 AM
       • Start date: today's date
       • Duration end date select No end date then select OK
   13. Click OK two times.
5. Create a job. To do this, follow these steps:
   1. Specify the following settings:
      • Name: Softricity Database (SOFTRICITY): Enforce Size Limit
      • Category: Database Maintenance
      • For Owner: sa
      • Description: 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.
        
        Note In this value, "cleanup" is spelled as one word.
   2. Click the Steps tab.
   3. Click New.
   4. Specify the following settings:
      • Step name: Enforce Size Limit
      • Type: Transact-SQL Script (TSQL)
      • Database: SOFTRICITY
      • Command: EXEC sp_SFTenforcesizelimit
   5. Click the Advanced tab.
   6. Specify the following setting:
      • On success action: Quit the job reporting success
      • Run as user: dbo
   7. Click OK.
   8. Click the Schedules tab.
   9. Click New Alert.
   10. Specify the following settings:
       • Name: Softricity Database (SOFTRICITY): Size Limit Reached
       • Type: SQL Server performance condition alert
       • Object: SQLServer:Databases
       • Counter: Data File(s) Size (KB)
       • Instance: SOFTRICITY
       • Alert if counter: rises above
       • Value: 849254
   11. Click the Response tab.
   12. Clear the E-mail and Net send check box. Click OK.
   13. Click the Notifications tab.
   14. Specify the following setting:
       • Write to Windows application event log: Whenever the job completes
   15. Click OK.
6. Create a job. To do this, follow these steps:
   1. Specify the following settings:
      • Name: Softricity Database (SOFTRICITY): Monitor Alert/Job Status
      • Category: Database Maintenance
      • Owner: sa
      • Description: 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.
   2. Click the Steps tab.
   3. Click New.
   4. Specify the following settings:
      • Step name: Refresh Database Jobs
      • Type: Transact-SQL Script (TSQL)
      • Database: SOFTRICITY
      • Command: EXEC sp_SFTrefreshjobs N'REFRESH'
   5. Click OK.
   6. Click New.
   7. Specify the following settings:
      • Step name: Refresh Database Alerts
      • Type: Transact-SQL Script (TSQL)
      • Database: SOFTRICITY
      • Command: EXEC sp_SFTrefreshalerts N'REFRESH'
   8. Click the Advanced tab.
   9. Specify the following setting:
      • On success action: Quit the job reporting success
   10. Click OK.
   11. Click the Schedules tab.
   12. Click New Schedule.
   13. Specify the following settings:
       • Name: Daily Schedule
       • Schedule Type: Recurring
   14. Click Change. Then specify the following settings:
       • Occurs: Daily
       • Every Day(s): 1
       • Daily frequencies: Occurs once at
       • Occurs once at: 1:00:00 AM
       • Start date: today's date
       • Duration end date: No end date
   15. Click OK three times.

Enabling the SQL Server Agent service

1. On the computer that is running SQL Server, click Start, point to All Programs, point to Administrative Tools, and then click Services.
2. Right-click the SQLSERVERAGENT service, and then click Properties.
3. In the Startup type list, click Automatic. Then click OK.
4. Right-click the SQLSERVERAGENT service. Then click Start.

Reconfiguring the SoftGrid communication configuration files

Reconfigure the SoftGrid Virtual Application Server

1. On the SoftGrid Virtual Application Server, right-click Start, and then click Explore.
2. Locate the following folder:
   C:\Program Files\Softricity\SoftGrid Server\conf
3. Locate the Server.conf file.
4. Use a text editor to change the host name that is between the <location> tag and the </location> tag to the host name of the computer that is running SQL Server.
5. Save the change, and then exit Windows Explorer.

Reconfigure the SoftGrid Management Server (if it is installed)

1. On the SoftGrid Management Server, right-click Start, and then click Explore.
2. Locate the following folder:
   C:\Program Files\Softricity\SoftGrid Server\conf
3. Locate the Manager.conf file.
4. Use a text editor to change the host name that is between the <location> tag and the </location> tag to the host name of the computer that is running SQL Server.
5. Save the change, and then exit Windows Explorer.

Reconfigure the SoftGrid Management Web Service

1. On the SoftGrid Management Web Service server, right-click Start, and then click Explore.
2. Locate the following folder:
   C:\Program Files\Softricity\SoftGrid Management Service
3. Locate the SftMgmt.udl file.
4. Right-click the file, and then click Properties.
5. Click the Connection tab.
6. In the Select or enter a server name box, type the name of the computer that is running SQL Server. Then click OK.
7. On the SoftGrid Management Web Service server, click Start, point to All Programs, point to Administrative Tools, and then click Data Sources (ODBC).
8. Click the System DSN tab.
9. Click SoftGridReporting System Data Source, and then click Configure.
10. In the Server box, type the host name of the computer that is running SQL Server.
11. Click Finish.
12. In the window that appears and that contains the message, "A new ODBC data source will be created with the following configuration," click OK.

Reset the SoftGrid Management Web Service

1. On the SoftGrid Management Web Service server, click Start, point to All Programs, point to Administrative Tools, and then click Services.
2. Stop and then start the World Wide Web Publishing service.

Reconfigure the database logging

1. Start the SoftGrid Management Console, and then connect to the SoftGrid Management Web Service.
2. Expand SoftGrid Systems, expand server_name, and then click Server Groups.
3. Right-click the name of the default server group, and then click Properties.
4. Click the Logging tab.
5. Modify the existing SQL database type by changing the name that is in the DNS Host Name box to the host name of the computer that is running SQL Server.
6. Click OK two times.
7. In the window that appears and that contains the message, "You must restart the SoftGrid Virtual Application Server services in this group for them to reflect the changes made," click OK.

Repeat steps 1 through 7 of this procedure for each database logging that is currently configured to log to the source database server.

Resetting the SoftGrid services

1. Restart the SoftGrid Virtual Application Server.
   
   Note If the SoftGrid Management Server is the same server as the SoftGrid Virtual Application Server that was previously restarted, you can skip this step.
2. On the SoftGrid Virtual Application Server, click Start, point to All Programs, point to Administrative Tools, and then click Services.
3. Stop and then start the following services:
   • SNMP Brass Management Multiplexer Agent
   • SNMP EMANATE Master Agent
   • SoftGrid Management Server
   • SoftGrid SNMP Monitor
   • SoftGrid Management Server