Kirx' Blog - kirxblog.wordpress.com

Remote Database Creation for App-V 5 Revised | December 30, 2013


AppVDBAbout a year ago I published a Powershell script that helps preparing the App-V 5 Management and Reporting database for scenarios, where executing App-V’s setup wizard locally on the SQL server is not appropriate. The script has evolved, and a new version is available right now.

With PrepSQL for App-V 5, 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 für 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

Dowload PrepSQL v2 from kirx.org

Download from kirx.org

Upate: This article applies to App-V 5.0 RTM and 5.0 SP1.  For App-V 5.0 SP3, please refer to the corresponding article!

Let’s be short.

If you want to learn more about the requirements to prepare the databases for App-V5 that should not be locally installed, I encourage you to read the following blog posts

The intention of PrepSQL for App-V 5 is essentially to avoid most of the manual and semi-autmated steps like poking around in .sql files, guessing and re-formatting SIDs and having a good chance to make unwanted modifications to a SQL server’s master databse.

The result is a small programm (technically a wrapped Powershell script) that adjusts all the .sql files based on human-readable command line parameters, rename copies of them in a way that indicates the right execution order and even create .cmd files to automate the import process of these .sql scripts into SQL server using OSQL.

The archive contains an Admin Guide that explains all parameters, requirements and options quite detailed.

Requirements

PrepSQL has been designed for App-V 5 and App-V 5 SP1  (There is no SP2 for App-V 5 server components)

  • 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: 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)

Usage:

Quick Start:

  • Get and locate the original .sql files
  • Adjust the RunPrepSQL.cmd batch file by removing/adjusting the parameter you need
  • Go!

In fact there is a little more. PrepSQL.exe allows and requires a variety of command line parameters that identify the database names, group names, source and destination folder and – in the case of OSQL .cmd file creation – some simple connection information to SQL server. Please, refer to the Admin Guide to get a full list of parameters and options.

A sample command could look like this (all written in one line):

PrepSQL -ARGUMENTS  -log "C:\temp\PrepSQL.log"  -SqlSrc "C:\temp\appvextracted\DatabaseScripts"  -SqlDest "C:\Temp\Modified_Scripts"  
  -MgtPrep  -MgtDb "MS_Appv_5_Mgt_Prod"  -MgtDbUro "demo\AppvDbAccessGroup"  -MgtDbUrw "demo\AppvDbAccessGroup"  
  -RptPrep  -RptDb "MS_Appv_5_Rpt_Prod"  -RptDbUro "demo\AppvDbAccessGroup"  -RptDbUrw "demo\AppvDbAccessGroup"  
  -OSQLPrep  -MgtDbSrvN "sqlsrv.demo.lab"  -RptDbSrvN "sqlsrv.demo.lab"

Note the ‘-arguments’ parameter that is required at the beginning – this is a habit of PowerGUI (which I used to write and wrap the script).

The Admin Guide contains some more parameters (including longer parameter name alternatives, which are easier to read, info about how to wrap lines in .cmd files and more)

Remember: No function of the PrepSQL actively changes anything on SQL server.

It is your responsibility to bring PrepSQL’s results to the SQL server (though that can be fully automated)

Dowload PrepSDownload from kirx.orgQL v2 from kirx.org

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 – this article)

2015-04: Remote Database Creation for App-V 5 SP3 

Advertisements

14 Comments »

  1. […]   Update 2  (2013-12): There is an even newer version of the preparation tool available here at https://kirxblog.wordpress.com/2013/12/30/remote-db-for-app-v-5-revised/     […]

    Pingback by Use App-V 5 Beta Publishing and Management Server with a Remote Database | Kirx' Blog — December 30, 2013 @ 22:56

  2. Thank you dear Kirx for the updated script.

    Please note that I am not able to download the script.
    Request Time Out.

    Please advise.

    Thank you.

    Comment by Sharbel, — December 31, 2013 @ 13:03

    • it appears that the link was broken in the original post, it now has been fixed.

      Falko

      Comment by kirxblog — January 14, 2014 @ 18:08

  3. Hi. I edited the .cmd file and tried to execute it. I’m getting the following error:

    ERROR: Either -PrepManagament or -PrepReporting are required

    The .cmd file has “-MgtPrep” in the arguments list.

    Comment by Erick — July 8, 2014 @ 15:50

    • -MgtPrep is an Alias for -PrepManagement, so it should work. If you have the paramaters in several lines. make sure a space/blank is there bevor the ‘^’.

      Comment by kirxblog — August 24, 2014 @ 18:14

  4. […] I prefer to use the second method in combination with Falko’s PowerShell scripts to modify the SQL script. More info here. […]

    Pingback by Setting up a SQL AlwaysOn Availability Group for App-V 5 | IT Tech Log — September 22, 2014 @ 14:57

  5. […] Falco’s Solution for fixing up the Database Scripts to give to your DBA to create the database: https://kirxblog.wordpress.com/2013/12/30/remote-db-for-app-v-5-revised/ […]

    Pingback by App-V 5 Management Server DB on Remote Machine Configuration | Confessions of a Guru — September 27, 2014 @ 18:23

  6. Anyone know if anything needs to be done when upgrading an App-V 5.0 database to SP1? As far as I can see it’s just the Version Info that needs running but I’m not certain. Thanks

    Comment by showbizjim — November 10, 2014 @ 12:20

  7. Run into problems using PrepSQLat SP3. SQL file of creating tables reference dbo.schemaVersion (2-1 or CreateTables.sql) and UpdateTables.sql (2-3) and InsertVersionInfo (2-4) Reference table dbo.schemachanges.

    Do I have to create tables from SP1 or SP2 media ? and then Upgrade to SP3 ?

    Comment by Henning — January 5, 2015 @ 15:31

  8. […] 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 […]

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

  9. […] 2013-12: Remote Database Creation for App-V 5 Revised (5.0 RTM and SP1) […]

    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

  10. […] site KirxBlog there is an great tool for automating the proces (all the credits go to them) . Klick here for the […]

    Pingback by Install APP-V 5 with database on different server | IT-Solved — May 24, 2015 @ 11:44


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: