Ryan Cobb | Senior Premier Field Engineer
Hi 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.
Environment: Windows Server 2008 R2 domain
Users and groups : AppVAdmin, member of AppV5Admins, Domain Users, Domain Admins.
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.
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:
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:
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.
After. Notice how the [ ] brackets were removed. The SIDs as pasted also had the ‘S’s and ‘-‘s (dashes) removed.
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:
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/
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/
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:
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])
Get computer account sid:
Get-ADComputer -Filter "name -eq 'ComputerName'" -Properties sid | select name, sid
Get ADGroup account sid:
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.)