Tips and tricks for troubleshooting App-V and SQL

Artykuł
03/21/2011

This guide is intended to provide some general troubleshooting tips and tricks in regards to issues you might experience with App-V Management Servers 4.5 and SQL 2005/2008.

When the App-V Management Server notices issues towards the SQL Datastore, in certain scenarios we can see problems such as the “Application Virtualization Management Server” Service (AppVirtServer) failing to start, for example App-V Management Server upgrade failures etc.

When you are experiencing issues, you could use the following tips and troubleshooting tricks to pinpoint the problem.

If you suspect issues regarding SQL connectivity or between the management server and the SQL data store, we have identified some common troubleshooting tips that we use in assisting customers;

1. First, start to look at the App-V Server log (sft-server.log) and check for errors.

When we have a serious issue, this log will point towards SQL but it might not reveal much info. You might see an entry like:

41494 "Failed to establish a connection to the data source ()."

Note that in the context of the App-V server starting up, 41494 messages can be expected and are benign. Only when you see 41494 messages in the context of trying to launch applications can it be considered part of a serious issue.

2. Verify that the App-V Administrators group has a login in SQL. This should be configured during the installation of the App-V Management Server, but in case of a migration, it should be created manually. Make sure this login has SFTadmin and SFTeveryone roles added.

3. Verify that a SQL Security Login is present for the App-V Management Server machine account.

This login is usually created during the installation of the App-V Management Server against the database, but if it is not present, it should be created. If the SQL GUI does not allow the creation of a login for a machine account, use the below SQL statement:

CREATE LOGIN [Domain\machine_name$] FROM WINDOWS
Go

Then verify the newly created login:

SELECT * FROM sys.server_principals WHERE name LIKE 'Domain\machine_name%'
Go

4. Use SQL Server Profiler to find a cause for the failure. A SQL Profiler trace can tell us a lot about what’s going on between the Management Server and the datastore, but if we select too many events, we will be looking for a needle in a haystack. In most situations, the following settings will be useful:

i. Start SQL Server Profiler

ii. Decide whether you want to run the trace or save it as a *.trc. You can also save the trace to a table on one of the existing databases so that you can query the trace output.

iii. On the ‘Events Selection’ tab, check the boxes “Show all events” and “Show all columns”.

iv. Browse to “Security Audit”, right-click and select “Select Event Category”.

v. Browse to “Errors and Warnings”, right-click and select “Select Event Category”.

vi. If necessary, you can also select “Stored Procedures” and “TSQL”, but this will cause your trace to grow large.

This would be sufficient in most cases for connection troubleshooting. When you are troubleshooting on a SQL server that has a lot of activity, you can filter the trace for ApplicationName, “Microsoft Application Virtualization”, or LoginName, domain name\machine name$. You can use SQL Profiler to read a trace, or you can then import it to a SQL table so that you can query it easily.

5. Test that the port is clear all the way through the firewalls using the following steps:

Use port query on the App-V Management Server:

https://www.microsoft.com/downloads/details.aspx?FamilyID=8355e537-1ea6-4569-aabb-f248f4bd91d0&displaylang=en

Run this on the App-V Management Server and use the same DNS name for your SQL server. The SQL Server name can be found on the App-V Management Server at:

HKLM\Software\Microsoft\SoftGrid\4.5\Server\SQLServerName

The App-V database name can be found on the App-V Management Server at:

HKLM\Software\Microsoft\SoftGrid\4.5\Server\SQLDatabaseName

Test to see if port 1433 is listening.

If the tool returns anything else other than listening means we are being blocked somewhere.

If the above works, it means the traffic can get to SQL.

6. If your App-V Service fails to start, test if it does run under a Service Account. This way you can exclude certain aspects and you have a workaround for the time being.

i. Create a service account in AD Users and Computers.

ii. Add this account to the administrator group used for the App-V Administrators.

iii. Run Services.msc, then right-click on “Application Virtualization Management Server” and select Properties.

iv. Click on the Log on tab.

v. Change the NT Authority\Network Service to the service account created in Step 1 and enter the password. Click OK.

vi. Click OK to acknowledge the Services dialog box indicating that the new logon will not be in effect until the service is restarted.

vii. Right-click on Application Virtualization Management Server and select Restart.

viii. Assuming the Support Tools are installed on the App-V Management Server, go to Start, All Programs, Windows Support Tools, Command Prompt.

ix. Enter the following 2 commands:

setspn -A SoftGrid/<FQDN of your machine> <YourDOMAIN>\<YourServiceAccountName>
setspn –A SoftGrid/<NetBIOS name of your machine> <YourDOMAIN>\<YourServiceAccountName>

x. Grant access to the Content share to the Service Account you created.

xi. Add the Service Account to Logon as A Service rights in the Local Security Policy.

xii. Give the Service Account Modify permissions to the Microsoft System Center App Virt Streaming Server\Logs directory.

How to configure the App-V Management Server Service to run as a Service Account

https://blogs.technet.com/b/appv/archive/2008/08/21/how-to-configure-the-app-v-management-server-service-to-run-as-a-service-account.aspx

7. Make sure that when you are migrating and upgrading, that you use the correct order. When you are running SoftGrid against a SQL 2005 database, you should first upgrade to App-V 4.5 before you migrate your database to SQL 2008. SoftGrid 4.1 is not supported for use with SQL 2008.

For more info, please see:

Exploring the SoftGrid Platform v4.1

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

Here we can see the following:

“SoftGrid supports MS SQL 2000 with Service Pack 4 and MS SQL 2005.”

8. Determining the port the App-V database is running on. You can use the SQL Log File Viewer to check the SQL Server log to determine on what port the App-V database listens on. You can do this using the following steps:

i. Open SQL Server Management Studio.

ii. Browse to “Management” and then “SQL Server Logs”.

iii. Right-click the “Current” log and choose “View SQL Server Log”