Tag Archives: PowerShell

New-SPAppManagementServiceApplication : A SharePoint database named XYZ already exists.

I was configuring SharePoint app catalog service applications the other day using my old scripts for that, and there is one problem I often see when using PowerShell scripts for configuring SharePoint: those work much better as they save your time you may spend on wading through cumbersome GUI, but in case when you need to re-run this script once again (let’s say it failed in the middle as you not updated some of the variables with proper values, or forgot to adjust something in line with your naming convention), you are often in trouble as you need to clear up things created by the script before you will be able to re-run it without errors.

Normally (especially if you fully understand what your script actually does) it is not a big problem, but this time when creating App Management Service Application I run into this error:

New-SPAppManagementServiceApplication : A SharePoint database named SP2013_AppManagementSvc already exists. You must supply another
name for the new database.

Looks like straightforward error which prompts to fire off SSMS and drop DB in question and I think I did it before, but this time there was NO such DB on SQL server yet I keep receiving this error. A bit of Googling showed me that I just have a reference to this DB in Objects table of SharePoint configuration database. So we can use SQL script (GitHub link) to search and if necessary remove it:

--Check if reference to DB exist
SELECT * FROM Objects WHERE name='SP2013_AppManagementSvc';
--Drop DB reference if necessary
DELETE FROM Objects WHERE name='SP2013_AppManagementSvc';

Little quick fix, but as usual in case it is anything like production environment better take DB backup before messing with anything in it.

Please follow and like us:
error0

Scripts for taking K2 service memory dumps

I’ve spent some time today improving “create K2 service memory dump script” (one which I already mentioned in “K2 MSMQ thread & MSMQ abort exception” blog post) and creating “collect dump support files”. Next step will be merging them into one and adding some nice to have things I have no time for right now.

Collect dump support files script (GitHub link):

$DumpPath='C:\Dumps'
$NetV2='C:\Windows\Microsoft.NET\Framework\v2.0.50727'
$NetV2x64='C:\Windows\Microsoft.NET\Framework64\v2.0.50727'
$NetV4='C:\Windows\Microsoft.NET\Framework\v4.0.30319'
$NetV4x64='C:\Windows\Microsoft.NET\Framework64\v4.0.30319'

$NetV2Dump="$DumpPath\Microsoft.NET\Framework\v2.0.50727"
$NetV2x64Dump="$DumpPath\Microsoft.NET\Framework64\v2.0.50727"
$NetV4Dump="$DumpPath\Microsoft.NET\Framework\v4.0.30319"
$NetV4x64Dump="$DumpPath\Microsoft.NET\Framework64\v4.0.30319"

If (!(Test-Path $DumpPath))
{
Write-Host 'Creating folder'
(New-Item $DumpPath -ItemType Directory | out-null)
} 
Else {
Write-Host 'Folder already exist'
}

Write-Host "Attempting to copy $NetV2\mscordacwks.dll" -ForegroundColor Green
If (!(Test-Path "$NetV2\mscordacwks.dll"))
{
Write-Host "$NetV2\mscordacwks.dll - File not found, skipping" -ForegroundColor Red
}
Else 
{

If (!(Test-Path "$NetV2Dump")) {New-Item "$NetV2Dump" -Type Directory | out-null}
Copy-Item "$NetV2\mscordacwks.dll" -Destination "$NetV2Dump\mscordacwks.dll"
Write-Host "Done!" -ForegroundColor Green
}

Write-Host "Attempting to copy $NetV2\sos.dll" -ForegroundColor Green
If (!(Test-Path "$NetV2\sos.dll"))
{
Write-Host "$NetV2\sos.dll - File not found, skipping" -ForegroundColor Red
}
Else 
{

If (!(Test-Path "$NetV2Dump")) {New-Item "$NetV2Dump" -Type Directory | out-null}
Copy-Item "$NetV2\sos.dll" -Destination "$NetV2Dump\sos.dll"
Write-Host "Done!" -ForegroundColor Green
}

Write-Host "Attempting to copy $NetV2x64\mscordacwks.dll" -ForegroundColor Green
If (!(Test-Path "$NetV2x64\mscordacwks.dll"))
{
Write-Host "$NetV2x64\mscordacwks.dll - File not found, skipping" -ForegroundColor Red
}
Else 
{

If (!(Test-Path "$NetV2x64Dump")) {New-Item "$NetV2x64Dump" -Type Directory | out-null}
Copy-Item "$NetV2x64\mscordacwks.dll" -Destination "$NetV2x64Dump\mscordacwks.dll"
Write-Host "Done!" -ForegroundColor Green
}

Write-Host "Attempting to copy $NetV2x64\sos.dll" -ForegroundColor Green
If (!(Test-Path "$NetV2x64\sos.dll"))
{
Write-Host "$NetV2x64\sos.dll - File not found, skipping" -ForegroundColor Red
}
Else 
{

If (!(Test-Path "$NetV2x64Dump")) {New-Item "$NetV2x64Dump" -Type Directory | out-null}
Copy-Item "$NetV2x64\sos.dll" -Destination "$NetV2x64Dump\sos.dll"
Write-Host "Done!" -ForegroundColor Green
}

Write-Host "Attempting to copy $NetV4\mscordacwks.dll" -ForegroundColor Green
If (!(Test-Path "$NetV4\mscordacwks.dll"))
{
Write-Host "$NetV4\mscordacwks.dll - File not found, skipping" -ForegroundColor Red
}
Else 
{

If (!(Test-Path "$NetV4Dump")) {New-Item "$NetV4Dump" -Type Directory | out-null}
Copy-Item "$NetV4\mscordacwks.dll" -Destination "$NetV4Dump\mscordacwks.dll"
Write-Host "Done!" -ForegroundColor Green
}

Write-Host "Attempting to copy $NetV4\sos.dll" -ForegroundColor Green
If (!(Test-Path "$NetV4\sos.dll"))
{
Write-Host "$NetV4\sos.dll - File not found, skipping" -ForegroundColor Red
}
Else 
{

If (!(Test-Path "$NetV4Dump")) {New-Item "$NetV4Dump" -Type Directory | out-null}
Copy-Item "$NetV4\sos.dll" -Destination "$NetV4Dump\sos.dll"
Write-Host "Done!" -ForegroundColor Green
}

Write-Host "Attempting to copy $NetV4x64\mscordacwks.dll" -ForegroundColor Green
If (!(Test-Path "$NetV4x64\mscordacwks.dll"))
{
Write-Host "$NetV4x64\mscordacwks.dll - File not found, skipping" -ForegroundColor Red
}
Else 
{

If (!(Test-Path "$NetV4x64Dump")) {New-Item "$NetV4x64Dump" -Type Directory | out-null}
Copy-Item "$NetV4x64\mscordacwks.dll" -Destination "$NetV4x64Dump\mscordacwks.dll"
Write-Host "Done!" -ForegroundColor Green
}

Write-Host "Attempting to copy $NetV4x64\sos.dll" -ForegroundColor Green
If (!(Test-Path "$NetV4x64\sos.dll"))
{
Write-Host "$NetV4x64\sos.dll - File not found, skipping" -ForegroundColor Red
}
Else 
{

If (!(Test-Path "$NetV4x64Dump")) {New-Item "$NetV4x64Dump" -Type Directory | out-null}
Copy-Item "$NetV4x64\sos.dll" -Destination "$NetV4x64Dump\sos.dll"
Write-Host "Done!" -ForegroundColor Green
}

Take K2 service process dump (GitHub link):

# Obtain K2 service PID
$procid=get-process "K2HostServer" |select -expand id
# Create target folder if not exist:
$DumpPath="C:\dumps"
If (!(Test-Path $DumpPath))
{
Write-Host 'Creating folder'
(New-Item $DumpPath -ItemType Directory | out-null)
} 
Else {
Write-Host 'Folder already exist'
}
# -ma - Write a full dump file which includes All the Image, Mapped and Private memory
# -ma - Just takes a full dump immidiately from running process
# -e -ma - takes full dump file on unhandled exception aka crash dump
C:\procdump\procdump.exe -accepteula -ma $procid $DumpPath\k2hostserver.dmp

Be sure checking out GitHub links as I keep editing/updating these scripts there.

Please follow and like us:
error0

Configuring SQL instance firewall rules via PowerShell

Doing distributed environments setups rather frequently these days I realized that I really don’t want wasting my time setting up SQL Server firewall rules via GUI (I described the process here) and luckily enough Ryan Mangan already created such script. All I had to do is try it (confirm that it works), save it on GitHub and share on my blog for the benefit of wider community:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned 
#Enabling SQL Server Ports
New-NetFirewallRule -DisplayName “SQL Server” -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName “SQL Admin Connection” -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Database Management” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Service Broker” -Direction Inbound –Protocol TCP –LocalPort 4022 -Action allow
New-NetFirewallRule -DisplayName “SQL Debugger/RPC” -Direction Inbound –Protocol TCP –LocalPort 135 -Action allow
#Enabling SQL Analysis Ports
New-NetFirewallRule -DisplayName “SQL Analysis Services” -Direction Inbound –Protocol TCP –LocalPort 2383 -Action allow
New-NetFirewallRule -DisplayName “SQL Browser” -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow
#Enabling Misc. Applications
New-NetFirewallRule -DisplayName “HTTP” -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow
New-NetFirewallRule -DisplayName “SSL” -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow
New-NetFirewallRule -DisplayName “SQL Server Browse Button Service” -Direction Inbound –Protocol UDP –LocalPort 1433 -Action allow
#Enable Windows Firewall
Set-NetFirewallProfile -DefaultInboundAction Block -DefaultOutboundAction Allow -NotifyOnListen True -AllowUnicastResponseToMulticast True

Original blog post by Ryan/source of this script: PowerShell Script for SQL Firewall rules

Please follow and like us:
error0

How to: configure K2 NLB port rules with PowerShell

Long time ago I did a video and blog post on configuring Windows NLB K2 cluster. I know that those materials are not perfect, but thanks to blogging you not only can be subject of mockeries for mistakes and naivety in your old posts, but also you can review and improve on them over time 🙂

Anyhow my old blog post on creating K2 NLB cluster contained this neat picture of required port rules:

As I tread my test K2 environments as “wipe and load”-ready and subject them to all sort of experiments leading to wipe and load and rebuilds I grow tired of creating this rules via GUI. Thanks to PowerShell and Microsoft Community it is not a problem to find a sample script to create Windows NLB cluster. I actually wanted to rewrite it with minor improvements and K2 specifics to spin off K2 NLB cluster quicker but due to endless lack of time this idea moved on the back-burner. What I did instead though is prepared PS script to create port rules:

$InterfaceName = "Ethernet"
$ClusterPrimaryIP = "192.168.100.116"

#Removing default port rule for the NLB Cluster
Write-Host "Removing default port rule..." -ForegroundColor yellow
Get-NlbClusterPortRule -HostName . -ErrorAction SilentlyContinue | `
Remove-NlbClusterPortRule -Force -ErrorAction SilentlyContinue
#Adding SmartForms/Workspace rule for port 80 HTTP
Write-Host "Adding port rule for HTTP (TCP 80)" -ForegroundColor yellow
Add-NlbClusterPortRule -Protocol Tcp -Mode Multiple -Affinity Single -StartPort 80 `
-EndPort 80 -Timeout 1 -InterfaceName $InterfaceName -IP $ClusterPrimaryIP| Out-Null
#Adding SmartForms/Workspace rule for port 443
Write-Host "Adding port rule for HTTPS (TCP 443)" -ForegroundColor yellow
Add-NlbClusterPortRule -Protocol Tcp -Mode Multiple -Affinity Single -StartPort 443 `
-EndPort 443 -Timeout 1 -InterfaceName $InterfaceName -IP $ClusterPrimaryIP| Out-Null
#Adding blackpearl rule for port 5252 - K2 workflow client connections
Write-Host "Adding blackpearl port rule for port 5252" -ForegroundColor yellow
Add-NlbClusterPortRule -Protocol Tcp -Mode Multiple -Affinity None -StartPort 5252 `
-EndPort 5252 -InterfaceName $InterfaceName -IP $ClusterPrimaryIP| Out-Null
#Adding blackpearl rule for port 5555 - K2 Host Server connections from client assemblies
Write-Host "Adding blackpearl port rule for port 5555" -ForegroundColor yellow
Add-NlbClusterPortRule -Protocol Tcp -Mode Multiple -Affinity None -StartPort 5555 `
-EndPort 5555 -InterfaceName $InterfaceName -IP $ClusterPrimaryIP| Out-Null
Write-Host "Rules configuration compoleted" -ForegroundColor green

That’s help a bit when I rebuilding my test environment. You can grab this script from GitHub too.

Please follow and like us:
error0

PS script to get SQL version from BAK file

Quite unpleasant thing about MS SQL Server database backup files it that you can’t restore them on an older version of SQL Server (it seems that even if you really want to create such BAK file it is not possible), moreover this is valid not only for major versions but also for things like R2, meaning you can’t restore BAK file created in SQL Server 2008 R2 on SQL Server 2008 Server (non-R2).

Just to save my time trying to restore BAK files against wrong versions of SQL Server I created this script:

It will allow you to retrieve SQL version from BAK file headers and compare it with your server SQL version. Specify path to your BAK file and check the output – if BAK file SQL version is newer that your server version then BAK file can’t be restored on this server. Sample script output can be found below:

It tells you that backup was taken on SQL Server 2012 SP3, while you run SQL Server 2012 SP2. Once you install SP3 for SQL Server script output will change to this:

Once two numbers match (or your SQL Server version number is higher than BAK file SQL version number) you are ready for backup restore 🙂 I’ve also added to this script comparison and output of its result, along with prompt for BAK file name and location:

Please follow and like us:
error0