In Part 1, we covered the overview of setup steps, requirements and prerequisites. In this part, we will cover the Database setup.
To proceed you will need to have your SQL Cluster with the Always On Group configured. You will need to use the SQL scripts which come with the App-V install.
When doing the actual install of your SQL instance, to follow my steps you only need the Database Engine feature. Once you have your SQL install with your AOG, proceed with these steps:
In the master database on the SQL Servers in the Always On Group run a new query using the Database.sql script:
This query creates the AppVManagement database.
Next, run a query was to run the CreateTables.sql script on the AppVManagement DB which created the tables.
Then execute a query running the CreateStoredProc.sql to create some stored procedures.
Then run a script UpdatedTables to drop the SchemaChanges table and add the SchemaVersion table.
Then run a scrip InsertVersionInfo.sql which inserts the value 2 into the SchemaVersion table. This is the schema version for App-V 5.0 SP3 and App-V 5.1.
Note: the line INSET INTO dbo.SchemaVersion VALUES(2) requires a manual fix, more info can be found HERE.
Other than this small change all of the previous scripts were run as provided by Microsoft as part of the App-V 5.1 install. The last script to run is permissions.sql, which requires further customization.
/* Grants access on SchemaVersion table to Public role */
GRANT SELECT ON dbo.SchemaVersion TO PUBLIC
GO
RAISERROR(‘Setting up server login and db login for read-access to SchemaChanges table’, 0, 1) WITH NOWAIT
GO
/* Setting up server login and db login for read-access to SchemaChanges table */
EXEC dbo.spSetupLogin 0x152116135304092897917314280799371187350, N’homelab\ AppVAdmins‘, 0
GO
RAISERROR(‘Setting up server login, db login and db roles for write-access to AppVManagement database’, 0, 1) WITH NOWAIT
GO
/* Setting up server login, db login and db roles for write-access to AppVManagement database */
EXEC dbo.spSetupLogin 0x15216135304092897917314280799371187351, N’homelab\AppVServers‘, 1
GO
The above script needs to be executed to create logins for the two AD Groups. The Admin Group provides access to the App-V Management Console. The App-V Servers groups provides permissions to write to the database.
The long identifiers starting with 0X152 are the AD Group SIDs. These SIDs cab be retrieved by running a PowerShell Script:
$account = “homelab.com\AppVServers”
(New-Object system.security.principal.NtAccount($Account)).translate([system.security.principal.securityidentifier]).value
The script returns the SID in the format:
S-1-5-21-1613530409-289791731-4280799371-187351
You need to modify for the script by dropping the S, prefixing with 0X and then removing the – between the numbers.
You should now have your AppVManagement database setup in your SQL Cluster.
The database should be setup as part of an AOG.
Ensure you setup database backups.
The AppVAdmins and AppVServers groups should also have been added as logins with permissions set accordingly.
To continue the setup, continue on to part 3.
