locked
How to update AppV 5.0 SP1 Management Database to SP3? RRS feed

  • Question

  • I'am upgrading to SP3.

    But since I don't have admin access to the Management Datasebase, I need a sql-script to update the DB from SP1 to SP3.

    Thursday, December 11, 2014 10:45 AM

Answers

  • I have heard back from them, and I'm fully up and running.  They are supporting databases upgraded by the SQL scripts, but there appear to be some errors in it.

    If you're following the directions and you extract then apply the SQL scripts, they should apply successfully except permissions.  If you choose to continue on and upgrade you're servers, your warning of a failed update will be a message indicating that you need to update your database and you may have reduced functionality until you do so. 

    Here are the fixes to the database scripts that I received. 

    In InsertVersionInfo.sql change the line:

        INSERT INTO dbo.SchemaChanges VALUES(@dbversion, @minserviceversion)

    to:

        INSERT INTO dbo.SchemaVersion VALUES(2)

    In Permissions.sql change the line:

        GRANT SELECT ON dbo.SchemaChanges TO PUBLIC

    to:

        GRANT SELECT ON dbo.SchemaVersion TO PUBLIC

    And here is the order that I was told to run the scripts in:

    Upgrade:

    1. CreateTables.sql
    2. CreateStoredProcs.sql
    3. InsertVersionInfo.sql
    4. UpdateTables.sql
    5. Permissions.sql (if needed)

    New Database:

    1. CreateTables.sql
    2. CreateStoredProcs.sql
    3. InsertVersionInfo.sql
    4. Permissions.sql
    5. UpdateTables.sql

    If this doesn't fix it for you, use the message I posted above and add the new columns to dbo.packagegroupmembers and verify that you have the correct a 2 for the version in dbo.SchemaVersion.  If that still doesn't work, re-install your publishing service after verifying your database is upgraded correctly.

    Monday, December 22, 2014 1:19 PM

All replies

  • Haven't checked that, but running the server's setup.exe with the /layout c:\temp parameter extracts the installer's individual files, including .sql scripts. I could imagine that on of these performs the upgrade.


    Falko

    Twitter @kirk_tn   |   Blog kirxblog   |   Web kirx.org   |   Fireside appvbook.com

    • Proposed as answer by RalJans Friday, December 12, 2014 12:18 PM
    Thursday, December 11, 2014 12:10 PM
    Moderator
  • I am working on installing sp3 from scratch and I see there are some problems with the install scripts. Particularly the Management Update Tables scrip. It deletes the DBO.SchemaChanges and then the insertversion script trys to write to the non existing table.

    Be carefull and test it in a QA environment first. I am opening a ticket with microsoft today on the script issue.

    Thursday, December 11, 2014 12:56 PM
  • I spoke with Microsoft about some upgrade issues yesterday. I was informed that the read me has the wrong directions regarding the order to apply databases.  This is what I was told:

    The correct upgrade script order is:

     

    1. CreateTables.sql
    2. CreateStoredProcs.sql
    3. InsertVersionInfo.sql
    4. UpdateTables.sql
    5. Permissions.sql (if needed)

     

    Note that the readme has UpdateTables.sql and InsertVersionInfo.sql incorrectly swapped.

     

    The order for creating a new DB is:

     

    1. CreateTables.sql
    2. CreateStoredProcs.sql
    3. InsertVersionInfo.sql
    4. Permissions.sql
    5. UpdateTables.sql

    I've applied the databases scripts without error (except permissions since my machines are upgrades and it is optional).  However, after upgrading I'm unable to do a publishing refresh from an 8.1 client running SP3.  That same client can do a publishing refresh to a server that hasn't been upgraded yet.


    Friday, December 12, 2014 2:03 PM
  • Joe.Robinson, after db scripts - have you runned this http500 error at publishing service port (82) ?
    • Edited by J.Saarinen Sunday, December 14, 2014 6:59 AM
    Sunday, December 14, 2014 6:58 AM
  • Yes; after applying the SQL scripts to a remote server, then updating to SP3 --  my clients can't do a publishing refresh.  In troubleshooting, I found that the clients can't get publishing information from the publishing server.  In IE, it returns an HTTP 500 error instead of XML.  However, if you go into IE options and turn off friendly error messages, you'll see that it's actually giving you an error message that is more helpful:

    <PublishingServiceError>
    <Message>Publishing metadata not ready</Message>
    <Details>
    <Message>
    Object reference not set to an instance of an object.
    </Message>
    </Details>

    </PublishingServiceError>

    This is coming from a failed database upgrade that doesn't report as a failure.  Tables are getting dropped while they are still needed, so the version information doesn't seem to make it into the correct table.

    I still don't have a fix, but I expect a call back from Microsoft today.  I will share any information that I get.

    Monday, December 15, 2014 1:29 PM
  • I've done some more digging, and here is what I've found.  But first:

    ***** WARNING: be very careful editing your database.  You could break things and not be able to repair them.  

    The following support statements are important, as I bet everyone having problems has a SQL database that they didn't run the setup.exe on.

    Microsoft supports HA clustering and suggests that you deploy to the cluster using the database scripts as described in Planning for High Availability.  This describes using the setup to extract the database scripts using /layout and layoutdir.

    Microsoft does not support deploying SP3 to a database using the database scripts, as described in App-V 5.0 SP3 Supported Configurations.  

    • Remote deployment of the management server database or the reporting database. You must run the installer directly on the computer that is running Microsoft SQL Server.

    I stood up a SQL server and installed the management database directly on it using pre-SP1 installation media.  I documented the database schema.  I then upgraded to SP3, running the installation media locally and documented the changes.  I found the following changes:

    dbo.PacakgeGroupMembers2 new columns were added

    Column: PackageOptional
    Data Type: bit
    Allow Nulls: False

    Column: VersionOptional
    Data Type: bit
    Allow Nulls: False

    dbo.SchemaChangesWhile the database scripts put data into this table, it's dropped before the last script is run.

    dbo.SchemaVersion: New table, one column, with a default value of 2

    Column: Version
    Data Type: int
    Allow Nulls: False

    Version = 2

    Running the Management Database scripts against my SQL cluster appeared to do all that except set the version to a value of 2.  In my case, the version was actually null, which I believe was producing a null exception and preventing me from getting publishing information.

    I added the value of 2 to test, and ... it did not work.

    However -- I added the value of 2, re-installed the management service and things are looking better.  I'm now able to get publishing information by browsing to my publishing port, and my clients are able to sync.

    I am still waiting to hear back from Microsoft on this, but I wanted to share.  It seems that something doesn't happen correctly when you don't run the EXE on the server.  

    Be careful manually editing your database!



    • Edited by Joe.Robinson Tuesday, December 16, 2014 10:45 PM
    Tuesday, December 16, 2014 10:41 PM
  • "I am still waiting to hear back from Microsoft on this, but I wanted to share.  It seems that something doesn't happen correctly when you don't run the EXE on the server. "

    I just wanted to ask - have you heard something from MS ?

    Monday, December 22, 2014 1:11 PM
  • I have heard back from them, and I'm fully up and running.  They are supporting databases upgraded by the SQL scripts, but there appear to be some errors in it.

    If you're following the directions and you extract then apply the SQL scripts, they should apply successfully except permissions.  If you choose to continue on and upgrade you're servers, your warning of a failed update will be a message indicating that you need to update your database and you may have reduced functionality until you do so. 

    Here are the fixes to the database scripts that I received. 

    In InsertVersionInfo.sql change the line:

        INSERT INTO dbo.SchemaChanges VALUES(@dbversion, @minserviceversion)

    to:

        INSERT INTO dbo.SchemaVersion VALUES(2)

    In Permissions.sql change the line:

        GRANT SELECT ON dbo.SchemaChanges TO PUBLIC

    to:

        GRANT SELECT ON dbo.SchemaVersion TO PUBLIC

    And here is the order that I was told to run the scripts in:

    Upgrade:

    1. CreateTables.sql
    2. CreateStoredProcs.sql
    3. InsertVersionInfo.sql
    4. UpdateTables.sql
    5. Permissions.sql (if needed)

    New Database:

    1. CreateTables.sql
    2. CreateStoredProcs.sql
    3. InsertVersionInfo.sql
    4. Permissions.sql
    5. UpdateTables.sql

    If this doesn't fix it for you, use the message I posted above and add the new columns to dbo.packagegroupmembers and verify that you have the correct a 2 for the version in dbo.SchemaVersion.  If that still doesn't work, re-install your publishing service after verifying your database is upgraded correctly.

    Monday, December 22, 2014 1:19 PM
  • Here is an official KB addressing the issue: http://support.microsoft.com/kb/3031340

    Thursday, January 22, 2015 10:05 PM
    Moderator