App-V 5.0 SP3 and remote SQL

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;

image

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

11 thoughts on “App-V 5.0 SP3 and remote SQL

  1. Nozuka

    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?

    Reply
    1. nickekallen Post author

      Yes, however as always I would test that in a lab before proceeding. I haven’t upgraded the Report DB

      Reply
    1. nickekallen Post author

      Hello,
      This is what the setup script does – I guess Microsoft would be a more wiser on this

      Reply
      1. Michael Loke

        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

        Reply
  2. Marcel Prins

    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

    Reply
  3. Michael Loke

    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]

    Reply
  4. Richard Hussain

    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

    Reply
    1. nickekallen Post author

      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.

      Reply
    2. Michael Loke

      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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *