Speaking SQL Saturday Atlanta, Sept 22, 2018

Create IaaS SQL Server Test Premise Environment in Azure Cloud

Speaker: Michael Wharton
Duration: 60 minutes
Track: Cloud Application Development & Deployment
Level: Intermediate
Why create a SQL Server Test premise environment in Azure cloud? One of the first steps into moving to Azure cloud, is building an environment that looks a feels like premise but only in the cloud. This session is an overview of getting started with basics of Azure environment, such as Accounts, virtual networks, Storage and creating virtual machines.. Once we fly by reviewing several building blocks, we then build Active Directory VM, SQL Server VM and App Server Tier VM using Azure portal and PowerShell.
You may ask what does this have to do with project?  Its a foundation for building a TEST Project Server in Azure.  The savings is that you only pay for when its needed. Things like SharePoint patches or software development.
SQL Saturday Atlanta

Project Server 2016 Provision Menus Are Gone


What happened to the options creating, editing and viewing the PWA in project server 2016?  Below is a screen shot of creating a project web app instance in SharePoint 2013.

ManageProjectWebApps2

Now look at the options in SharePoint 2016.  Notice they are missing except for manage.

ManageProjectWebApps

What this now means is that the easy to use menu driving procedure of creating and managing PWA instances is now history.   PowerShell is the way it is done now.   I really like PowerShell, but really wish Microsoft would keep some of the menu options. Rumor is that everything is going the way of PowerShell.
Here are a few PowerShell scripts that provides the same functions as before:
#######################################################
# Author: Michael Wharton
# Date: 08/30/2016
# Description: Project Server 2016 configuration
#    Create one or multiple PWA sites
#######################################################
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Confirm:$false -Verbose
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue  -Verbose
#######################################################
#  Update the following metadata that is used consistantly thru the script
#######################################################
<$SqlServerName        = “LAB-2016SP.DOMAIN.LOCAL”
$WebAppURL            = “http://LAB-2016SP.DOMAIN.LOCAL&#8221;
$SitePwaURL           = “http://LAB-2016SP.DOMAIN.LOCAL/pwa&#8221;
$WebHostHeader        = “LAB-2016SP.DOMAIN.LOCAL”$WebAppURL            = “http://LAB-2016SP&#8221;
$SitePwaURL           = “http://LAB-2016SP/pwa&#8221;
$SitePwa2URL          = “http://LAB-2016SP/pwa2/PMO&#8221;
$SitePwaOOBURL        = “http://LAB-2016SP/pwaoob”$WebAppName           = “Project Server 2016”
$ProjectServiceApp    = “Project Service App”
$ProjectServicePool   = “Project Service Pool”
$WSS_ContentDB        = “LABK_PWA_WSS_Content”
# Create credentials
$ServiceSP            = “DOMAIN\ServiceSP”
$ServicePass          = “password”
$credServiceSP  = New-Object System.Management.Automation.PSCredential -ArgumentList @($ServiceSP,(ConvertTo-SecureString -String $ServicePass -AsPlainText -Force))
################################################################
# create Managed Account
New-SPManagedAccount -Credential $credServiceSP -Verbose
################################################################
# Create Project Service Pool
New-SPServiceApplicationPool -Name $ProjectServiceApp  -Account $ServiceSP -Verbose
################################################################
#  Create Project Server Service and Enable Project Key
$ProjectServiceID = New-SPProjectServiceApplication -Name $ProjectServiceApp -Proxy -ApplicationPool (Get-SPServiceApplicationPool $ProjectServicePool)
Enable-projectserverlicense -Key “23CB6-N4X8Q-WWD7M-6FHCW-9TPVP”
#################################################################
# Create Web Application on root that contains project server collection
#################################################################
$AP = New-SPAuthenticationProvider -Verbose
New-SPWebApplication -Name $WebAppName -port 80 -URL $WebAppURL -DatabaseName $WSS_ContentDB  -ApplicationPool $ProjectServicePool   -ApplicationPoolAccount (Get-SPManagedAccount $ServiceSP) -AuthenticationProvider $AP -Verbose -Confirm:$false
# Create PWA on Root
New-SPSite -Url $WebAppURL -Template “PWA#0” -Name “Project Web App” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $WebAppURL -Verbose
Set-SPProjectPermissionMode -Url $WebAppURL -Mode ProjectServer -Verbose
# Add a few resources and make basic configuration updates such as publish sites
Start $WebAppURL
###################################################
#  Create multiple PWA sites
$SitePwaURL           = “http://LAB-2016SP/pwa&#8221;
$SitePwa2URL          = “http://LAB-2016SP/pwa2&#8221;
$SitePwaOOBURL        = “http://LAB-2016SP/pwaoob&#8221;
###################################################
# Create Root
New-SPSite -Url $WebAppURL -Template “STS#0” -Name “Root” -OwnerAlias “DOMAIN\mawharton” -Verbose
# Create PWA Site as managed path
New-SPManagedPath “PWA” -WebApplication $WebAppURL -Explicit
# remove-spmanagedpath “PWA”  -WebApplication $WebAppURL
New-SPSite -Url $SitePwaURL -Template “PWA#0” -Name “Project Web App” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $SitePwaURL -Verbose
Set-SPProjectPermissionMode -Url $SitePwaURL -Mode ProjectServer -Verbose
Start $SitePwaURL
# Create PWA2 as second site
New-SPManagedPath “PWA2” -WebApplication $WebAppURL -Explicit
New-SPSite -Url $SitePWA2URL -Template “PWA#0” -Name “Project Web App2” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $SitePWA2URL -Verbose
Set-SPProjectPermissionMode -Url $SitePWA2URL -Mode ProjectServer -Verbose
Start $SitePWA2URL
# Create PWAOOB as second site (Out-Of-Box)
New-SPManagedPath “PWAOOB” -WebApplication $WebAppURL -Explicit
New-SPSite -Url $SitePwaOOBURL -Template “PWA#0” -Name “Project Web App OOB” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $SitePwaOOBURL -Verbose
Set-SPProjectPermissionMode -Url $SitePwaOOBURL -Mode ProjectServer -Verbose
Start $SitePwaOOBURL
# Create PWA2 as second site
New-SPManagedPath “PWA2” -WebApplication $WebAppURL
New-SPSite -Url $SitePWA2URL -Template “PWA#0” -Name “Project Web App2” -OwnerAlias “DOMAIN\mawharton” -Verbose
Enable-SPFeature PWASITE -Url $SitePWA2URL -Verbose
Set-SPProjectPermissionMode -Url $SitePWA2URL -Mode ProjectServer -Verbose
Start $SitePWA2URL
#################################################
#  List Web Application
#################################################
Get-SPProjectWebInstance | Select URL , adminAccount, ProvisioningResult, Online | Sort-Object URL
#################################################
#  Delete PWA Site
#################################################
Remove-SPSite -Identity $SitePwaURL  -Confirm:$false -verbose     # Removes PWA site
Remove-SPSite -Identity $SitePwa2URL -Confirm:$false -verbose     # Removes PWA2 site
Remove-SPSite -Identity $SitePwaOOB  -Confirm:$false -verbose     # Removes PWAOOB site

Installing Project Server using AutoSPInstaller

Wow!  For years I have been building PowerShell scripts to install and configure SharePoint and Project server.  It has worked well and have learned a lot about PowerShell in doing so.   However, I recently discovered a tool in CodePlex called AutoSPInstaller.   http://autospinstaller.codeplex.com/

My first reaction to this tool was the same as using SharePoint Products Configuration Wizard.  It couldn’t be that good and all being executed behind code.   When I finally looked at AutoSPInstaller and discovered that it was all based on PowerShell.  This perk my interested.

My first few attempts using AutoSPInstaller failed for me and my reaction was not very good.  However, I kept hearing about how others were using it and so I decided to give it another chance only this time, I took a little time in reading the documentation.  After examining the PowerShell code and using the “Build Farm” tool found at https://autospinstaller.com/ and with a successful installation of SharePoint and a configuration of SharePoint Farm; I WAS SOLD.

This is one of the best tools I have seen for building a SharePoint farm and documenting the farm.  And it also configures other useful items to make your farm more robust such as setting up SQL alias and disabling the loop check.  And it works for SharePoint and Project server 2010, 2013 and 2016.  I am consistently build different SharePoint scenarios in my lab and AutoSPInstaller automates the process of setting it up.

One other item, I should mention about AutoSPInstaller. If you want to learn more about PowerShell and see good examples of modules and well written code, then dig deep into the code.  I really like PowerShell and learning PowerShell techniques.  AutoSPInstaller is full of many PowerShell tricks.

Building the SharePoint Farm XML is done by jumping to the https://autospinstaller.com/ site and then clicking “Build a Farm”.   The following screen shot are for building a SharePoint farm that configures Project Server on a single server.  My configuration is for the very basics services that project server typically uses such as Excel Services, Secure Store, Performance Point Service and of course project server service.  I should point out project server service doesn’t always work after the installation and at times I have needed to de-provision and re-provision the project service to get it working.

SPAutoInstaller-Servers

Servers Page.  Its purpose is to define the servers in the SharePoint farm. If only one server is being setup for the farm, then leave servers list blank.  It requires either no servers or a minimum of two servers for the Share Point farm.

SPAutoInstaller-2-Options

Installation Options page.  The highlighted options are important to me.

SPAutoInstaller-3-Main

Farm – Configure the Main Farm Options

General settings for the SharePoint configuration.  I don’t like putting real password in the build farm tool, so I put in bogus passwords and replace them once I create the XML file.

I really like the database preview option.   It allows you to have all the SharePoint farm databases prefixed and makes it much easier for the DBA to organize the databases.

The create alias option is a nice feature for setting this up as well.  Makes it much easier to move SharePoint to another SQL server in the future.

I specify only a few managed accounts for project server deployment.  If setting up for SharePoint deployment, I would add all the account in this page.  It validates the username and passwords.

SPAutoInstaller-4-FarmServices

Farm Services is really depends on your requirements.  I will leave it at that.

SPAutoInstaller-5-FarmLogging

Farm Logging screen.  Take the default and change it later.  I had some issues, so now keep it as the default.

SPAutoInstaller-6-RootWeb

Root Web Application.  I change this up a little by just setting up a root.

SPAutoInstaller-7-stateService

For project server, I typically setup State Service, Secure Store, Excel Services and PerformancePoint Services.  Under the Other option are special settings for project server.

 

SPAutoInstaller-8-SecureStore

 

Miscellaneous Components.   Setting up the PDF content type has never been so easy.

SPAutoInstaller-9-ExcelServiceSPAutoInstaller-10-PerformancePointSPAutoInstaller-11-Miscelanous

SPAutoInstaller-12-ProjectService

Here is the final screen.  Word of caution is that the configuring project server doesn’t work for me.  I am going to look into the PowerShell and see what the problem is.  In the meanwhile, I would just letting it configure project server. Once it is complete, test to see if it works.  If project server doesn’t work properly then do the following

  1. De-provision PWA
  2. Delete the web application
  3. Reboot the server
  4. Create a new Project Server 2013 web application
  5. Provision PWA

Finally, I would like to thank Ivan Josipovic and Brian Lalancette for building and maintaining AutoSPInstaller on CodePlex. You can find out more about these guys at https://autospinstaller.com/Contact