A Complete Guide to SQL 2008 App-V Database Migration

Hi everyone, John Behneman here. The purpose of this article is to outline the steps necessary to migrate or move a Microsoft Application Virtualization (App-V) Database from one SQL server 200X to SQL server 2008 R2. This article assumes that the App-V database name stays the same on the source and destination database servers although if the database name is changed this procedure will still work. This article also assumes a basic understanding of SQL 2008 administration concepts.

-Quick Overview-

To properly migrate an App-V or SoftGrid database from an existing SQL 200X Server to SQL 2008 Server or 2008 R2 requires the following steps:

1. Backup the App-V database on the source SQL server; it can be a SQL 2000, SQL 2005 or SQL 2008 SQL server.

2. Move the Backup to the destination SQL server & restore the database to the destination SQL server.

3. Configure the relevant Global and App-V Database Security Logins and Roles on the Destination server to match the original database server.

4. On the App-V Management Server reconfigure the SFTMgmt.udl file to point to the new database server.

5. Update the dbo.DATA_SOURCE table with the new hostname and service_name

6. On the App-V Management Server verify and/or modify the SQLServerPort & SQLServerName registry keys to reflect the new configuration.

7. Run the Alert_Jobs.SQL script to create the 4 App-V SQL Jobs and the Data_Messages.SQL script to add the user-defined App-V Error messages 50001-500073 to the Master database sys.messages table & run the sp_SFTcleanupusage_update.sql script to allow the App-V Check Usage History job to run properly on a SQL 2008 or SQL 2008 R2 server.

· Create a blank database on the new server with the same name as the database on the old server or a new name if you want to rename the database. e.g.: APPVIRT.It is not necessary to pre-create an empty database, it just happens to the preferred method of the author. If you check the outlined steps on the TechNet article referenced at the end of this article it does not pre-create the database. This procedure can be used to rename the database during the migration.

· Restore the database backup to the new server, on the Options section select "Overwrite the existing database".

· Restore to the Blank database APPVIRT created in the previous step.

If you are using a *.bak file to restore your App-V Database you will need to select From Device: -> Click on the … button-> Backup media: -> File -> Add -> navigate to the *.bak backup file and add it. Make sure the Restore check box is checked otherwise it will not allow you to restore the backup. Leave all the other settings to default and click OK to restore the Database. You should see the following screen after a successful restore:

Step 3 - Configure the relevant Global and App-V Database Security -

· Verify the APPVIRTDatabase Security settings both Global and APPVIRTDatabase match that of the Old server.

· Verify that the service account running the Application Virtualization Management Server service (Default is Network Service (local) or AppV Management Server_ComputerName$ (Remote) has rights to the database. Usually it’s the name of the system (Domain\ComputerName$), if it is a non-default service account make sure the service account has rights to the database. This account should have the following roles:

-SFTeveryone -SFT user

· Make sure that the App-V Admin Group defined during the Management server setup has the following roles assigned:

-SFTadmin -SFTeveryone

NOTE: Please check the Global security and AppVirt Database Security settings of the Source and Destination SQL servers to verify that the App-V Logins/Users and Server Roles/User Roles match.

Step 4 - Reconfigure the SFTMgmt.udl file -

Locate the SFTMgmt.udl on the App-V Management server and back it up (copy it) then modify it to point to the new SQL server. If you receive an error message trying to open the SFTMgmt.udl file please verify the account you are logged in as has read and execute access permissions, you will need write and modify permissions to update it. The Application Virtualization Management Server service requires at least read access to this file, by default it runs under the Network Service account; if you using a domain service account for this service please give the account read permissions to this file.

· Double click on the SftMgmt.udl file and to the Connection tab change the Select or enter a server name: to the correct SQL Server and change the Select the database on the server: to the correct SQL Database. Then select the All tab and verify that all the parameters located here are correct for the new server. After all the settings are correct test the connection by clicking on the Test Connection button.

· If you receive the following error message during the Test Connection please configuration the appropriate inbound ports on the SQL Server, as a test you can temporarily disable the firewall on the SQL server to determine if this is the problem.

· Once the Firewall on the SQL server is configured correctly you should see the following message during the Test Connection test.

Step 5 -Update the dbo.DATA_SOURCE Table -

· In SQL Server Management Studio on the Destination App-V SQL server find the APPVIRT -> Tables -> System Tables ->dbo.DATA_SOURCE right click on this table and select Edit Top 200 Rows change the hostname to new server or server\instance depending on how it is configured and the service_name to the new database name.

Step 6 - Modify the SQLServerPort & SQLServerName registry keys -

· Configure the following Registry keys appropriately to access the new database location. Note: remove the Wow6432Node section of the address for X86 Management servers.

o HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\SoftGrid\4.5\Server\SQLDatabaseNameChange this to correct database name

o HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\SoftGrid\4.5\Server\SQLServerPort Change this to 0 so the server will query the SPN for this information or set this to the correct port. Change the Base setting to Decimal to set the port number correctly.

o HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\SoftGrid\4.5\Server\SQLServerName Change to ServerName\Instance or just servername depending on your configuration.

Please see KB 2633835 for more information regarding these registry keys:

· To verify App-V SQL server connectivity by launching the Application Virtualization Management Console, if you receive a 0000C80X go to %APPDATA%\Microsoft\mmc and rename the SftMMC file to SftMMC.old and try it again. If it still fails double check the all configuration items mentioned up to this point in the article.

• Verify that the App-V clients can successfully stream and refresh applications. Revisit the steps above if there is any problem.

Launch Microsoft SQL Server Management Studio on the SQL 2008 server hosting the App-V Database and navigate to Databases ->System Databases->Master->Views->System Views->sys.messsages. Highlight and right click on sys.messages and select the Top 1000 Rows on the Results window you should see message_id objects 50001 – 50073.

Steps 9 - Verify that the 4 App-V Jobs are configured correctly and can be run manually without error –

· Launch Microsoft SQL Server Management Studio and navigate to the SQL Server Agent/Jobs node in Object Explorer, right click each SoftGrid Database (APPVIRT) job and choose “Start Job at Step” on each Job if it is successfully should see the following screen for the first 3 jobs Check Usage History, Close OrphanedSessions and Enforce Size Limit. PLEASE NOTE: If you cannot drill down from the SQL Server Agent node, no [+], check to see if the Agent is running, right click on it and try to start it. You can also launch the Services MMC and check the SQL Server Agent (MSSQLSERVER) service, if it is not started please start it. When it is running you should see a green > indicator or a red down arrow if it is stopped.

· On the last Job, Monitor/Job Status you will see an addition screen listing Refresh Database Jobs and Refresh Database Alerts.

· Click on start and if it is successful you receive a screen similar to the first 3 jobs. If the job(s) fail you need to determine why it failed. If the Check Usage History, Close OrphanedSessions Job fails and it is running on SQL 2008 or above you will need to download and run the sp_SFTcleanupusage_update.sql, script this is not needed on SQL server 2000 and 2005. Otherwise check the SQL Error logs to find out why the job(s) failed.

· If the App-V jobs are failing to run manually check to see what Database the job is running under and the context in which the job is running, it should be running against the APPVIRT database under the dbo security account. To determine the database the job is running under, right click on the job and choose Properties -> Steps ->Edit -> and check the Database Parameter, it should be APPVIRT. To determine which account it is running under right click on the job and choose Properties -> Steps ->Edit ->On the Job Step Properties select -> Advanced and check the “Run as user” parameter, should be dbo, except for the Monitor Alert/Job Status job it will be blank. The Check Usage History job is unique in that it is the only job that has Retry attempts: set to 4 and Retry interval (minutes): set to 1440, which means if this Job fails to run you will not see the failure for 4 days. The first time you run this job manually you may wish to change the Retry attempts: to 0, then revert it back to 4 after a successfully test. To change the Retry attempts: value right click on the Check Usage History job and choose Properties -> Steps ->Edit -> Advanced and use the down arrow button to change the value to 0, reverse this operation to return the value to 4 after you run the manual test.

· In some cases depending on the specific SQL server security requirements, there may be addition steps required, there have been cases where SQL Admins define which SQL Client servers may access a specific database server and all others are denied access. Please check with your DBA to determine if they are using any enhanced security techniques to secure SQL servers.

· At this point you should have a successful App-V Database Migration.

NOTE If you have a distributed setup then you will need to set up constrained delegation as mentioned in the link below for the new database server that you migrated to:

We had a customer using this reference to migrate their App-V Database to a new SQL 2008 R2 server.

During the migration process they create a citrixadmin SQL account (non-domain joined account) with DBO rights to the App-V database. BUT, instead of selecting "Use Windows NT Integrated security" when configuring the sftMgmt.udl file during the SQL connection testing phase, they selected "Use a specific user name and password" and tested the connection with the citrixadmin account username and password which passes the connection test. When the sftMgmt.udl is configured this way it generates the following error message when attempting to login to the App-V Management Console from the local console of the App-V Management Server (not remotely):

Application Virtualization Management Console

Unable to log into the Application Virtualization system

The specified user is not authorized to administer this system

Error code: 0000C803

Checking the SQL error log on the SQL server, best place to start when troubleshooting these types of issues, we see the following error every time the App-V Admin user attempts to login to the Management Console:

"Login failed for user 'citrixadmin.' Reason: Password did not match that for the login provided.

Just changing the "Enter information to log on to the server:" back to "Use Windows NT Integrated security" alone did not fix this issue, it must be done in conjunction with a subsequent IISreset or optional reboot of the IIS server. One should always use this setting when configuring the Management Server to use a different database server. Using the citrixadmin SQL account passed the Test Connection test with flying colors suggesting to the customer that all is well. BUT this account is not a domain account and therefore cannot be a member of the App-V Admins AD group which is required for App-V Management Console access. To correct his situation the following things must be true:

1. The user that is logged into the App-V Management Server Console must be a member of the App-V Admins domain global group.