As App-V 5.0 SP3 has been released it is not only the client that gotten an update, but the App-V Server components has gotten a fresh new installer for the first time since App-V 5.0 SP1.
If one is working in a locked down environment where the roles of server and databases are seperated, and databases might be located on a shared SQL-hotel that no ordinary administrator is allowed access to – the question that rises is how does one get the database up 2 speed?
According to the notes the only supported scenario is to run from the installer provided by App-V 5.0 SP3, and what that is can be debated, which no SQL admin would dream about.
If executing the appv_server_setup.exe with the switch /layout there will be a folder extracted named database scripts.
Looking at the contents (in databasescripts) in App-V 5.0 SP3 and comparing that to the contents of App-V 5.0 SP1 – the files look fairly much the same;
When comparing the contents of the files the following differences emerge;
Createtables.sql
CREATE TABLE dbo.PackageGroupMembers ( Id int PRIMARY KEY IDENTITY, PackageGroupId int NOT NULL, PackageVersionId int NOT NULL, LoadOrder int NOT NULL, <em>PackageOptional bit NOT NULL DEFAULT 0, VersionOptional bit NOT NULL DEFAULT 0</em> ) CREATE TABLE dbo.SchemaVersion ( Version int NOT NULL )
InsertVersionInfo.sql
SELECT @minserviceversion = N'5.0.10107.0' SELECT @dbversion = N'5.0.10107.0'
And finally, looking at updates.sql – you can notice that there are some major changes. For example;
to begin with;
-- Replace SchemaChanges table with SchemaVersion table
RAISERROR('Removing SchemaChanges table', 0, 1) WITH NOWAIT
GO
IF (EXISTS (SELECT * FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] = 'SchemaChanges'))
BEGIN
DROP TABLE [SchemaChanges]
INSERT INTO [SchemaVersion] VALUES (1)
END
GO
and continue with;
-- Add PackageOptional and VersionOptional columns to PackageGroupMemebers table ALTER TABLE PackageGroupMembers ADD PackageOptional bit NOT NULL DEFAULT 0, VersionOptional bit NOT NULL DEFAULT 0
and
-- Erase the current schema version from the SchemaVersion table (the new current version will be written during the install)
RAISERROR('Removing current schema version', 0, 1) WITH NOWAIT
GO
DELETE FROM [SchemaVersion]
GO
It seems that all the changes to the database are actually contained in updates.sql and createtables.sql.
It seems that the below commands are suffice. All of this is just gathered information from the Microsoft provided scripts on howto setup a clean database.
CREATE TABLE dbo.SchemaVersion ( Version int NOT NULL ) INSERT INTO [SchemaVersion] VALUES (1) -- Add PackageOptional and VersionOptional columns to PackageGroupMemebers table ALTER TABLE PackageGroupMembers ADD PackageOptional bit NOT NULL DEFAULT 0, VersionOptional bit NOT NULL DEFAULT 0 -- Update SchemaVersion table to version 2 DELETE FROM [SchemaVersion] INSERT INTO [SchemaVersion] VALUES (2) DELETE FROM [SchemaVersion]
Once this is completed, head on over to your App-V Management Server. install the .NET Framework 4.5.1
Then install the new App-V 5.0 SP3 bits – first the Management Server. Then the Publishing server. After that you are all done!
You can verify that your servers are operational by simply accessing their respective website. if anything odd shows up – check the event logs under Microsoft\App-V
Thank you for this!
So i just have to run your short complilation script at the end of your post and that is all?
And is this only applied to the Management DB and the Report DB does not need to be touched?
Yes, however as always I would test that in a lab before proceeding. I haven’t upgraded the Report DB
The last Delete from [Schemaversion] . You insert a value and then you delete it. Why ?
Hello,
This is what the setup script does – I guess Microsoft would be a more wiser on this
The commenting in the code states that this is done so it could be replaced by the installer. I wonder what it becomes in a standalone (rather than upgrade/update) installation.
-- Erase the current schema version from the SchemaVersion table (the new current version will be written during the install)
RAISERROR('Removing current schema version', 0, 1) WITH NOWAIT
GO
DELETE FROM [SchemaVersion]
GO
This would be enough for updating the database:
CREATE TABLE dbo.SchemaVersion (Version int NOT NULL )
INSERT INTO [SchemaVersion] VALUES (2)
— Add PackageOptional and VersionOptional columns to PackageGroupMembers table
ALTER TABLE PackageGroupMembers ADD PackageOptional bit NOT NULL DEFAULT 0, VersionOptional bit NOT NULL DEFAULT 0
I think the drop table should also be included if you are applying SP3 on top of an existing installation as the comments indicate SchemaChanges table will no longer be used in the future:
-- Replace SchemaChanges table with SchemaVersion table
DROP TABLE [SchemaChanges]
I am hoping someone is reading this. I have updated my DB for SP3 but now I get “There was a problem interacting with the database on the server. The error was: Invalid object name ‘dbo.RoleAssignment’
Any ideas what went wrong? Even with a fresh install, the problem is still there.
Thanks in advance
Hello,
So you ran the above scripts with both an updated scenario and a fresh scenario? Sounds really odd – with a fresh install you must run the entire script.
I would suggest you explain what did you – the App-V forums is a great place todo so in.
We saw this as well. Not mentioned is that the installer removes the registry entry that points to your database, restore the MANAGEMENT_DB_NAME value at HKLMSOFTWAREMicrosoftAppVServerManagementService
Update HA App-V 5.0 SP1 with remote SQL to App-V 5.0 SP3 here