Kirx' Blog - kirxblog.wordpress.com

Remote Database Creation for App-V 5 SP3 | April 11, 2015


AppVDBThe new version of my PrepSQL tool now supports the changes that have been introduced with ServicePack 3 for App-V 5. You can silently create the App-V Management Database and Reporting Database on remote SQL servers, on SQL server clusters or in scenarios, where you can’t run App-V’s setup wizard on the SQL server machine. This new version also allows to upgrade the databases from a pre-SP3 version. It already includes the fix for a known error with Microsoft’s original scripts.

With PrepSQL for App-V 5 SP3 you can

  • Prepare the App-V Management database and the App-V Reporting database without running black-boxed executables on your SQL servers
  • Prepare the the App-V databases for using in clustered SQL server environments
  • Give your SQL Administrator the confidence to only make comprehensible changes to the SQL server
  • Fully automate the preparation and even execution of App-V’s .sql scripts
  • Leverage log files to identify potential issues while preparing the .sql scripts
  • Allow to use a name for the Reporting database that is not limited to a length of 20 characters
  • Update older App-V databases to the SP3 format
  • Skip the manual correction that causes MS’ scripts to fail

Download PrepSQL for App-V 5 SP3 from kirx.org

Download

Intro

This is an updated version of the PrepSQL script tool. It is designed for a Full Infrastructure deployment of App-V 5 SP3 and can also be used for App-V 5’s reporting capabilities (that work with every depolyment model).

It originally started with an descriptive article about how to prepare the very first App-V 5 Beta implementation within a remote database or clustered database environment. At that point-in-time I was hoping that Microsoft would impove the preparation process, but when the RTM release of App-V 5 came out, Microsoft just refered to an Technet article, describing what to do. Because following a guideline, manually replacing stuff in text-based files and finally running several scripts in a pre-defined order is boring and can cause error I created the first version of a script in Powertshell, adressing some of the pain-points. Based on community feedback the script evolved to a tool and got some features added (being wrapped in an .exe).

PrepSQL for APP-V 5 SP3 Features

  • Inserts the account/group name and pre-formatted SIDs into the .sql files for the following accounts:
    • Management Database Read-Only User
    • Management Database Read/Write User
    • Reporting Database Read-Only User
    • Reporting Database Read/Write User
  • Renames the various .sql files so that their name indicates the right execution order
  • Inserts a USE command into each original .sql file to ensure that the scripts are modifying the right database (and not the Master DB)
  • Extends the maximum length of the Reporting Database name from 24 to 60 characters
  • Creates batch files with OSQL commands that allow to import/run the scripts silently on the SQL server
  • Can be fully automated
  • Does not actively change anything on the SQL server
  • New in Version 2.1
    • Support for SP3
    • Correct the SchemaChanges / SchemaVersion issue that was introduced with App V 5.0 SP3 (see http://support.microsoft.com/en-us/kb/3031340)
    • Create the log file folder if it does not exist
    • Remove previously created destination sub folders

Requirements

PrepSQL has been designed for App-V 5 SP3.

If you need the script for App-V 5 RTM or App-V 5 SP1, please continue with Remote Database Creation for App-V 5 Revised. App-V 5 SP2 does not contain changes for the server infrastructure, so there is no script reuqired for that.

  • You need the original database scripts files that can be extracted from the App-V server setup wizard. They can be extracted on any machine using the following command (run as administrator):
APPV_SERVER_SETUP.EXE /layout c:\temp\appvextracted
  • Once you have the .sql scripts, you can use PrepSQL basically on every machine (including lab machines)
  • You need Powershell with the unrestriced execution policy
  • You need write permissions to the destination folder(s) that should contain the modified .sql scripts, log file and so on
  • To import the .sql files into SQL server, you need to be friendly to your SQL Administrator – or you do it yourself if you have sysadmin privileges on SQL server.
  • You need the PrepSQL.exe (and perhaps .cmd) from the archive that is linked in this article)

Quick Start

For more details read the Admin Guide inside the archive

Prepare the .SQL Scripts (App-V Expert)

  • Log in as an administrator to a machine that has Powershell’s Execution Policy set to unrestricted
  • Right-click -> Run as Administrator: APPV_SERVER_SETUP.EXE /layout c:\temp\appvextracted
  • Extract the content of the PrepSQL archive to c:\temp
  • Adjust the RunPrepSQL.cmd command file:
    • Remove unnecessary lines: lines starting with ‘Mgt’ are for the Management database, lines starting with ‘Rpt’ are for reporting. Other lines are for both
    • Adjust folder paths (C:\…)
    • Adjust database names (MS_Appv_5…)
    • Adjust user/group names (demo\…)
    • Make sure last line does not end with ^
  • Save, then right-click -> Run as Administrator RunPrepSQL.cmd
  • Check the files in the SqlDest folder that was specified in the .cmd
  • Hand over the all files from SqlDest to your SQL Server Admin

Run the .SQL Scripts (SQL Expert)

  • Validate the .sql scripts and optionally the _OSQL.cmd files for correctness
  • Run the .sql files manually in there numbered order against the SQL server or
  • Run the _OSQL.CMD file(s)
  • Validate that the databases exist, and that they contain data items like tables and stored procedures

Download

You can download PrepSQL from kirx.org

Download

References

Recommended Articles

Related and previous Articels

2012-04:  Use App-V 5 Beta Publishing and Management Server with a Remote Database

2012-11: Remote Database Preparation for App-V 5 (5.0 RTM and SP1)

2013-12: Remote Database Creation for App-V 5 Revised (5.0 RTM and SP1)

2015-04: Remote Database Creation for App-V 5 SP3 (this article)

Advertisements

5 Comments »

  1. […] 2015-04: Remote Database Creation for App-V 5 SP3  […]

    Pingback by Remote Database Creation for App-V 5 Revised | Kirx' Blog - kirxblog.wordpress.com — April 11, 2015 @ 18:19

  2. […] 2015-04: Remote Database Creation for App-V 5 SP3 (this article) […]

    Pingback by Remote Database Preparation for App-V 5 | Kirx' Blog - kirxblog.wordpress.com — April 11, 2015 @ 18:20

  3. […] 2015-04: Remote Database Creation for App-V 5 SP3 […]

    Pingback by Use App-V 5 Beta Publishing and Management Server with a Remote Database | Kirx' Blog - kirxblog.wordpress.com — April 11, 2015 @ 18:29

  4. When I download “PrepSQL_for_AppV5SP3_20150404.zip” from you link above and my Antivirus flag it as having a Trojan/Virus in it. I upload the file to “virustotal.com” and it says 8 of 57 antivirus scanner detected it as a Trojan/Virus. I have a feeling that this is a false positive however, I cannot be 100% sure. This is a very nice tool but not sure I want to run it on my servers until I can figure out if does or does not have a Trojan/Virus. FYI, the file that trips the antivirus software is “PrepSQL.EXE”.

    Comment by Andrew Batson — May 5, 2015 @ 20:38

    • Thank you for bringing this up!

      I heard that for an older version, too. I also learned that other application that are compiled powershell scripts do have the same issue. I’m investigating and looking for alternatives (maybe publish the source code)

      Comment by kirxblog — May 10, 2015 @ 19:37


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: