database

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