Kirx' Blog - kirxblog.wordpress.com

Remote Database Preparation for App-V 5 | November 1, 2012


A while ago I wrote an article about what to do if you want to implement a Full App-V 5 Infrastructure – and you’re not the SQL Admin. Now, with App-V 5 going RTM, there are some updates – including additions for the Reporting Database and a little Powershell helper for you.

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

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

I strongly recommend that you read the ‘old’ article first. It explains some background Information and still is valid (though it was written for a Beta version).

Let’s start with the same constraints that we assumed for the last article.

  • You want to implement a Microsoft Application Virtualization with a ‘Full Infrastructure’ and/or you want to implement App-V 5’s Reporting Feature
  • You are not the SQL Server Administrator and the SQL Admins refuse to run any installer nor executable on their SQL Server machines
  • MS’ instruction of how to ‘install’ the database tells you some of the things for a ‘remote DB configuration’

Warming up

With App-V 5 RTM (and even in the pre-release versions) Microsoft allows you to ‘install’ the database(s) using the Setup Wizard – so you take your CD and go over to the SQL Admins: and they just start laughing  ;-)

Luckily there is a less shameful way to get things done: Along with the RTM version of App-V 5 Microsoft provides the necessary SQL scripts (*.sql files) that allow to prepare the SQL databases and configuration in a ‘clear text’ way – which SQL Admins easily can inspect and validate.

However, these scripts

  • Are embedded in the server installer Setup.exe
  • Need some adjustments

Extract the SQL scripts from Setup.exe

This one is easy. Just take a Test VM (you even might use one of the later App-V server machines or your current Desktop PC) and run the following command:

appv_server_setup.exe /layout c:\Temp

This extracts the content of appv_server_setup.exe into the specified directory (C:\Temp in my case). The content then should look like this:

image

And guess what – it’s the DatabaseScripts folder that contains the ‘plain’ scripts. In there you’ll find two subfolders – one for the Management database and one for the Reporting database

Each subfolder contains a readme.txt that instructs you what to do. Essentially, you have to replace the targeted database name in the “Database.sql” files and enter the SID and Name for the Read/Full database access groups into the “Permissions.sql” scripts.

Because there are some things I sort-of don’t like (you have to figure out the group’s SIDs, you have to remove the “-“ of them, manually add them into the .sql files, run the .sql files in the right order…) I wrote a Powershell scripts that does some of the work for you. I suppose you and the SQL Admin are different persons, so here is the process:

Gather the information

Ask the SQL Admin how the new databases for App-V Management (and optionally Reporting) should be named.

Ask the SQL Admin (or ask the Active Directory team) which groups should be used to configure the database access. For each App-V 5 Database (Management and Reporting) you need two groups (Read access and Write access).

You should NOT ask for individual user accounts, but for groups. Following the AGDLP model (http://en.wikipedia.org/wiki/AGDLP) these groups fall under the “Permission” Group category. You (or someone else) will add “Accounts” groups (that include the individual users) . As a Best Practice you should have dedicated groups for every permission, so four in total.

Side note: I’m planning for a dedicated article on App-V Accounts and Permissions. (But I don’t commit to an expected release date ;-) ).

For now add yourself and the App-V Management Server’s account (<servername>$) to all groups.

Prepare the Powershell Script

To make the task somewhat easier, I wrote a Powershell Script that you can download from kirx.org.

Copy the Powershell file (PrepareSQL_inplace.ps1) into the same folder where the ManagementDatabase and the ReportingDatabase folders (erhm… copies!) are located: C:\Temp for my example.

Open the .ps1 file with an editor, like Notepad, Microsoft’s PowerShell ISE, Quest’s PowerGUI or alike.

I think the variable names are self-explaining, you should verify/adjust the ones which are highlighted in the screenshot.

image

If ‘$AlsoPrepareReportingSqlScripts’ is set to $true (the default), the .sql scripts for the Reporting database are prepared as well. If it is set to $false, only the .sql scripts for the Management DB are adjusted.

$SqlFilesSourceDirRoot’ should stay as it is (empty), because then the scripts looks at its own location for the subfolders and files.

After your modifications, save the script.

Run the Powershell Script

Important: The script is ‘destructive’, i.e. it modifies and renames the files directly. It does NOT create a backup copy.

To run the script, you ‘only’ need write permission in the current folder (C:\Temp). You may need to set the Powershell ExecutionPolicy (and for that you might need Admin rights on your test machine).

The script should run without “red” Powershell errors. If there are any, verify that the ‘original’ files are at the proper location.

Verification

After the script finished, the ManagamentDatabase folder should look like this (and the ReportingDatabase folder should look similar, but not identical). Check the ‘1-1_Database.sql’ file(s) if it contains the right Database information and the ‘2-5_Permissions.sql’ file(s) if it contains the right permission groups.

Execute the SQL Script

Then, turn over to your SQL Admins…. And pay homage to them. They deserve it. After that, you may send them the .sql scripts. They should like you, because you don’t want to run an executable on their servers, plus you don’t require them to create and adjust the databases manually.

For the SQL Admin:

  • Start with the “ManagementDatabase” Folder
  • Run the 1-1_… Script against the server. It will create a database
  • Then, verify that the database exists. And MAKE SURE that the following .sql script are executed on the new database (DO NOT! run them on the ‘master’ DB).
  • Run them in the order defined by the number: 2-1, 2-2 and so on
  • If required continue with the ‘ReportingDatabase’ folder.
  • Same procedure as above: Run the 1-1 script against the server and the 2-x scripts against the newly created reporting database.

Executing the ‘ScheduleReportingJob.sql’ script may throw errors or warnings, depending on the status of the SQL Agent Server

After that is done, you can install the App-V 5 infrastructure servers and point them to the new remote database(s)

Again, the Powershell Script be found here.

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

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

Advertisements

19 Comments »

  1. Thanks for your help. This saved me a ton of time and headaches…worked as advertised.

    Comment by Eric Stewart — December 4, 2012 @ 19:32

  2. […] the SQL scripts into the universal installer (setup.exe). There is a newer article available at https://kirxblog.wordpress.com/2012/11/01/remote-database-preparation-for-app-v-5/, however you should start reading here (and continue […]

    Pingback by Use App-V 5 Beta Publishing and Management Server with a Remote Database « Kirx' Blog — January 1, 2013 @ 14:11

  3. Thank You for the steps! Worked great. I didn’t want to have to use the installer and worry about all the dependencies.

    Comment by Andrew Gawronski — February 28, 2013 @ 22:15

  4. Very useful article. I applied it successfully for the RTM version. Now SP1 has been introduced. Launching mgmt setup warns about a change in the database. Do you know how we can remotely upgrade the database. Embedded-SP1 SQL scripts don’t show an “upgrade” facility.

    Comment by Thierry Paché — May 6, 2013 @ 10:24

  5. Thanks so much :) time saver. Simple and works great. – Jay.

    Comment by Jay — September 2, 2013 @ 02:43

  6. Hey, thanks for the helpful script! For the future maybe you can enter a “use DB” command at the beginning of each 2-X script. In your script we have the DB name.

    Comment by Steff — October 30, 2013 @ 09:59

    • Very good hint! I will add that to an updated version of the SQL script/post that I am currently preparing.
      In fact selecting the right database is the most critical task right now that is error-prone, too.

      Thank you!

      Comment by kirxblog — November 6, 2013 @ 10:14

  7. The “permissions.sql” ends with “Msg 102, Level 15, State 1, Line 3 Incorrect Syntax near ‘S123…..” any idea why it fails?

    Comment by Martin — November 6, 2013 @ 00:08

    • Can you post your final pemissions.sql? Maybe X-out sensitive data

      Comment by kirxblog — November 6, 2013 @ 10:10

      • Here it is:

        RAISERROR(‘Granting access on SchemaChanges table to Public role’, 0, 1) WITH NOWAIT
        GO

        /* Grants access on SchemaChanges table to Public role */
        GRANT SELECT ON dbo.SchemaChanges TO PUBLIC
        GO

        RAISERROR(‘Setting up server login and db login for read-access to SchemaChanges table’, 0, 1) WITH NOWAIT
        GO

        /* Setting up server login and db login for read-access to SchemaChanges table */
        EXEC dbo.spSetupLogin 0x[S1521264568754510749417249983951841129], N'[App-V_Mgmt]’, 0
        GO

        RAISERROR(‘Setting up server login, db login and db roles for write-access to AppVManagement database’, 0, 1) WITH NOWAIT
        GO

        /* Setting up server login, db login and db roles for write-access to AppVManagement database */
        EXEC dbo.spSetupLogin 0x[S1521264568754510749417249983951841129], N'[App-V_Mgmt]’, 1
        GO

        Comment by Martin — November 6, 2013 @ 20:36

      • In your SIDs, the 0x and the S have to be removed.
        Was is one generated wth the Powershell script (then it’d contain an bug)?

        Comment by kirxblog — December 4, 2013 @ 21:05

      • Indeed it was created with the ps script. However when I started from scratch couple of days ago (fresh sql, app-v, etc) everything was fine…

        Comment by Martin Fuchs — December 4, 2013 @ 21:10

  8. […] The first version of the PrepSQL powershell script with some explainations: https://kirxblog.wordpress.com/2012/11/01/remote-database-preparation-for-app-v-5/ […]

    Pingback by Remote Database Creation for App-V 5 Revised | Kirx' Blog — December 30, 2013 @ 22:50

  9. Note that I created a new version of PrepSQL: http://bit.ly/1cD3ZJN

    Comment by kirxblog — December 30, 2013 @ 23:03

  10. The solution I used was to make certain that the logged on domain user, that was running the App-V install, had Database permissions to create the app-v instances. As expected the install was run as an administrator.

    Comment by BD — April 6, 2014 @ 01:45

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

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

  12. […] The first version of the PrepSQL powershell script with some explainations: https://kirxblog.wordpress.com/2012/11/01/remote-database-preparation-for-app-v-5/ […]

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

  13. […] 2012-11: Remote Database Preparation for App-V 5 (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


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: