The Official Microsoft App-V Team Blog

Your official source for all the latest news and tech tips for Microsoft Application Virtualization (App-V).

How to install the App-V 5.0 Database and Management Server using SQL scripts on the DB

How to install the App-V 5.0 Database and Management Server using SQL scripts on the DB

  • Comments 8
  • Likes

Ryan Cobb | Senior Premier Field Engineer

GearsHi everyone, my name is Ryan Cobb and here are the steps required to install the App-V 5 Database and Management Server using the SQL scripts on the DB instead of the installation routine.

=====

Background

Environment: Windows Server 2008 R2 domain

Computers:

  • AppVDC : Our Windows Server 2008R2 Domain Controller.
  • AppvServer : A Windows Server 2008R2 computer running SQL Server 2008 R2.
  • AppvServer2 : A Windows Server 2012 computer that is a member server in the domain. All App-V server pre-reqs are met.

Users and groups : AppVAdmin, member of AppV5Admins, Domain Users, Domain Admins.

Tasks:

  • Install App-V 5 Management and Reporting databases on AppVServer using the scripts provided by Microsoft.
  • Install App-V Management and Reporting services on AppVServer2 using the pre-installed databases.

 

Installation

First, the installation files need to be extracted from appv_server_setup.exe. This is done by executing the following command in an elevated CMD prompt:

appv_server_setup.exe /layout /layoutdir=c:\extractedinstallationfiles

We will work with the ManagementDatabase first. Inside your c:\extractedinstallationfiles directory is the DatabaseScripts folder, and in here you will find two folders; one for ManagementDatabase and one for ReportingDatabase.

Inside the ManagementDatabase folder there are a couple .sql files that will need to be modified as needed for your environment. The first file is the Database.sql file. The image below is the file as it was extracted.

clip_image002

The next image will show the differences after modification. Because my SQL Server already has both AppVManagement and AppVReporting databases for a different test environment, I need to change the names in the script to something else. For my example here, I will append a 5 to the database names (e.g. AppVManagement5 and AppVReporting5) as show below:

clip_image004

Next up we need to modify the permissions.sql file. It needs to be modified to ensure that the correct permissions are setup for the database. Mainly it adds the name of the App-V admins group, of which a member of that group is used to install the App-V server components on the AppVServer2 computer. In my example, my App-V admins group is named AppV5Admins and the name of the account being used is ManagementDbPublicAccessAccount. The other account that is needed is the ManagementDbWriteAccessAccount account and it is the account of the server used to install the App-V Management components and would be the machine account for AppVServer2.

But only if it was so simple. You see, SQL doesn't particularly like friendly names for user groups or computers so we will need to provide the SIDs for each entity instead.

Using PowerShell and the friendly names of both the group and machine we can retrieve the SIDs. The image below shows the retrieval of the SID for the AppV5Admins group and the AppVServer2 machine account:

clip_image006

The syntax for the above image is:

$Account=”domain\account”
(New-Object system.security.principal.NtAccount($Account)).translate([system.security.principal.securityidentifier]).value

Now that we have the SIDs we can plug those into our permissions.sql file as needed.

Before:

clip_image008

After. Notice how the [ ] brackets were removed. The SIDs as pasted also had the ‘S’s and ‘-‘s (dashes) removed.

clip_image010

I then copied the ManagementDatabase folder to my SQL Server computer which is named AppVServer.

Next, I opened SQL Management Studio, and per the directions from the readme I executed each of the .SQL files in their proper order.

I verified through SQL Management Studio that the commands completed as the DB was added, tables created, etc.

The final test is to install the App-V Management Server service on AppVServer2, which, when given the correct information as far as DB Servername, Instance name and DB Name, all was well in the world!

For the reporting database install, simply repeat the procedure above.

Ryan Cobb | Senior Premier Field Engineer | Microsoft

Get the latest System Center news on Facebook and Twitter:

clip_image001 clip_image002

System Center All Up: http://blogs.technet.com/b/systemcenter/
System Center – Configuration Manager Support Team blog: http://blogs.technet.com/configurationmgr/
System Center – Data Protection Manager Team blog: http://blogs.technet.com/dpm/
System Center – Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/
System Center – Operations Manager Team blog: http://blogs.technet.com/momteam/
System Center – Service Manager Team blog: http://blogs.technet.com/b/servicemanager
System Center – Virtual Machine Manager Team blog: http://blogs.technet.com/scvmm

Windows Intune: http://blogs.technet.com/b/windowsintune/
WSUS Support Team blog: http://blogs.technet.com/sus/
The AD RMS blog: http://blogs.technet.com/b/rmssupp/

App-V Team blog: http://blogs.technet.com/appv/
MED-V Team blog: http://blogs.technet.com/medv/
Server App-V Team blog: http://blogs.technet.com/b/serverappv

The Forefront Endpoint Protection blog : http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: http://blogs.technet.com/b/isablog/
The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/

Comments
  • Thanks

  • Thanks for the information, it helped in my implementation.  There was much value in seeing the pre and post changes to the scripts, as thats what I was unsure of.

    One tip -- while it is displayed in your powershell clip, it isn't explicitly said in the article.  When you're getting the SID of the computer account, you need to add a $ to the name or you'll get a translation error.

    You can also get the sid this way:

    (get-adcomputer $computeraccount).sid.value

    (get-adgroup $groupname).sid.value

    Thanks again!

  • $computeraccount=”domain\account$”

    (get-adcomputer $computeraccount).sid.value

    When i run it,it shows error.but the following Command is OK.

    $objUser = New-Object System.Security.Principal.NTAccount("domain", "UserAccount")

    $strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])

    $strSID.Value

  • Get computer account sid:

    Get-ADComputer -Filter "name -eq 'ComputerName'" -Properties sid | select name, sid

    Get ADGroup account sid:

    $objUser = New-Object System.Security.Principal.NTAccount("domain", "UserAccount")

    $strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier])

    $strSID.Value

  • We want to install 2 App-V Servers (Management/Publishing/Reporting roles on each) in NLB cluster with using a shared database in Remote SQL Cluster.

    Which server should be specified as ManagementDbWriteAccessAccount in Permissions.sql ?

  • The first one for the script. Then you will need to manually add in the second one using SQL management Studio (what I would use - you could probably script this as well.)

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment