A Complete Guide to SQL 2008 App-V Database Migration

Article
02/08/2012

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.

8. Verify that the Data_Messages.SQL script properly populated the Master Database sys.message table.

9. Verify that the four App-V Jobs are configured correctly and can be run manually without error.

-Assets that will need to be downloaded-

SQL script that creates the SQL 4 Jobs that are required by the App-V DB : https://gallery.technet.microsoft.com/scriptcenter/SQL-script-that-creates-b6345446

SQL script to allow App-V Check Usage History job to run on SQL 2008 : https://gallery.technet.microsoft.com/scriptcenter/SQL-script-to-allow-App-V-959bc1d4

SQL script that adds the App-V Data Messages to sys.messages Master DB table : https://gallery.technet.microsoft.com/scriptcenter/SQL-script-that-adds-the-f4da5092

Step 1 -Backup the Database section-

· Open SQL Server Management Studio on the Original App-V database server (Source) and find the APPVIRT Database Node

· Highlight and right click the APPVIRT (Default Name) go to Tasks

· Select Task ->Backup

· On the backup screen make sure the Backup type is Full choose Options and choose Overwrite all the existing backup sets

· Click Ok which will start the backup

Step 2 -Restore and Configure the new server-

· Move the database backup file to the new SQL 2008 server.

· Open SQL Server Management Studio on the Destination App-V SQL 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 APPVIRT Database Security settings both Global and APPVIRT Database 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.

Please see the App-V 4.5 SP2 Release Notes for more information about this file : https://technet.microsoft.com/en-us/library/ff699130.aspx

· Navigate to C:\Program Files (x86)\Microsoft System Center App Virt Management Server\App Virt Management Service (default location).

· 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.

How do I open the firewall port for SQL Server on Windows Server 2008? : https://support.microsoft.com/kb/968872
Configuring the Windows Firewall to Allow SQL Server Access : https://technet.microsoft.com/en-us/library/cc646023.aspx

· 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\SQLDatabaseName
Change 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:

Error 268480357 starting an App-V Management Server with dynamic SQL ports : https://support.microsoft.com/kb/2633835

· 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.

Steps 7 - Adding the 4 SQL Jobs and the user-defined App-V Error messages 50001-500073 to the Master database sys.messages table –

· DownloadAlert_Jobs.SQL scriptand follow the instructions found at this URL:

SQL script that creates the SQL 4 Jobs that are required by the App-V DB : https://gallery.technet.microsoft.com/scriptcenter/SQL-script-that-creates-b6345446

· Download sp_SFTcleanupusage_update.sql script and follow the instructions found at this URL:

SQL script to allow App-V Check Usage History job to run on SQL 2008 : https://gallery.technet.microsoft.com/scriptcenter/SQL-script-to-allow-App-V-959bc1d4

· Download Data_Messages.SQL script and follow the instructions found at this URL:

SQL script that adds the App-V Data Messages to sys.messages Master DB table : https://gallery.technet.microsoft.com/scriptcenter/SQL-script-that-adds-the-f4da5092

Steps 8 - Verify that the Data_Messages.SQL script properly populated the Master Database sys.message table

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 Orphaned Sessions 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 Orphaned Sessions 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:

https://technet.microsoft.com/en-us/library/ee675779.aspx

More Information

How to Migrate the App-V SQL Database to a Different SQL Server: https://technet.microsoft.com/en-us/library/gg252515.aspx

App-V 4.5 Remote Console Configuration Guide: https://blogs.technet.com/b/virtualworld/archive/2009/04/21/app-v-4-5-remote-console-configuration-guide.aspx

Related articles:

2633835 - App-V 4.6: Error 268480357 starting Management Server with Dynamic SQL Ports : https://support.microsoft.com/default.aspx?scid=kb;EN-US;2633835

314546 - How to move databases between computers that are running SQL Server : https://support.microsoft.com/kb/314546

Quick note on Microsoft Application Virtualization 4.5 OS and SQL Server requirements : https://blogs.technet.com/b/appv/archive/2015/06/08/quick-note-on-microsoft-application-virtualization-4-5-os-and-sql-server-requirements.aspx

John Behneman | System Center Support Engineer

Get the latest System Center news on Facebook and Twitter :

The Forefront Server Protection blog: https://blogs.technet.com/b/fss/
The Forefront Identity Manager blog : https://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: https://blogs.technet.com/b/isablog/
The Forefront UAG blog: https://blogs.technet.com/b/edgeaccessblog/

Comments

Anonymous
January 01, 2003
Something to consider: 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:

The user that is logged into the App-V Management Server Console must be a member of the App-V Admins domain global group.
The sftmgmt.udl file needs to configured to "Use Windows NT Integrated security".
Windows Authentication Mode must be enabled on the SQL server hosting the App-V Database.
Everything must be configured properly according to this Blog post.

Anonymous
March 15, 2012
Is SQL 2008 R2 actually "officially supported" for App-V? The latest documentation I can find makes no mention of R2. Thanks
Anonymous
July 27, 2012
The comment has been removed
Anonymous
July 12, 2016
Thanks for step by step solutions

Share via

A Complete Guide to SQL 2008 App-V Database Migration

Comments

Additional resources