mandag den 16. maj 2016

SharePoint 2013 alignment (1/?)

I have wondering on what I should write next on my blog, and came on a little idea on a serie on how to align two SharePoint 2013 environments.
We are all making new code in an test- or development environment. And for that purpurse we need to establish some valid data in the environments.

So this serie will cover all the steps to create a complete script that can take the production data, and restore it on an test/dev server.

So we should first cover our steps, before we can make a script. And if you have any comments I really like to hear your points. This serie will target those that dont have a deep knowledge in PowerShell and are looking for a copy paste solution.

So the steps are as follow:
  1. Backup (copy style) of the production content database
  2. Check the backup files
  3. Dismount the content databases
  4. Restore the content databases
  5. Assign new site collection admins
  6. Migrate groups and users. (If your changing AD domain)
  7. Migrate service applications
    1. Manage metadata
    2. ... Please ask for a service application, you like me to script. 
So the way I like to make my scripts is making a master script and a funktion script. But it could be the same. I also like to make a configuration file with all my variables.

Lets make 3 files.
  • RunningScript.ps1
  • Functions.ps1
  • Configuration.xml

So lets have a close look at how we build the XML file.
I have build mine with all the server settings.
<?xml version="1.0"?>
<Settings>
<ServerSettings>
 <WebApp>http://contoso.com</WebApp>
<SQLServerFrom>SQLsServer\PRODinstance</SQLServerFrom>
<SQLServerTo>SQLsServer\TESTinstance</SQLServerTo>
<SiteColAdmin>contoso\administrator</SiteColAdmin>
<OldADDomain>ContosoPROD</OldADDomain>
<NewADDomain>ContosoTEST</NewADDomain>
<SQLBackupJob>SQL-Backup-JOB</SQLBackupJob>
<SQLRestoreJob>SQL-Restore-JOB</SQLRestoreJob>
</ServerSettings>

So the first step is to take the backup of the produktion database. This is bedst done by using the SMO PowerShell module.
To install the SMO please look at ...

The first part of the script we need to implement the use of the Functions.ps1 and Configuration.xml file. Note that I am using a "main" function. this is done to get an overview of the running script, and to be able to place it at the top of the script.

Using the RunningScript.ps1
function main
{
  cls
  Write-Host -ForegroundColor Blue "Starting AligmentScript"
  $bits = pwd
  . "$bits\Functions.ps1"
 
  [xml]$xmlConfiguration = Get-Content "$bits\Configuration.xml"
  Run-SQL-Job "$xmlConfiguration.Settings.ServerSettings.SQLServerFrom" "$xmlConfiguration.Settings.ServerSettings.SQLBackupJob"
{

 After in adding the script path, I include the configuration file.
Note: There are many ways to include your script path, I use the "pwd" command, but this dose not always work.
after including the files, I use the function [Run-SQL-Job]. This function is used for both backup and restore. Do mind that the account running the script need access to the production server.

The function looks like this:
function Run-SQL-Job
(
  $instancename,
  $jobname
)
{
 
$db = "MSDB"
   try
   {
 
   $sqlConnection = new-object System.Data.SqlClient.SqlConnection
     $sqlConnection.ConnectionString = 'server=' + $instancename + ';integrated security=TRUE;database=' + $db
     $sqlConnection.Open()
 
   Start-Sleep -s 1
     $sqlCommand = new-object System.Data.SqlClient.SqlCommand
     $sqlCommand.CommandTimeout = 120
     $sqlCommand.Connection = $sqlConnection
    
$sqlCommand.CommandText = "EXEC dbo.sp_start_job N'$JobName'"
     Write-Host "Executing Job => $jobname..."
     $result = $sqlCommand.ExecuteNonQuery() 
     $sqlConnection.Close()
   }
 
  catch
   {
 
   Write-Eventlog -Logname 'Application' -Source 'Application' -EventID 19002 -EntryType Error -Message "Start SQL job :$jobname Failed"
  }
 
# Preload Sql server SMO Assemblies
  $null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Connect ionInfo")
  $null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum ")
  $null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
  # Initilize SMO Objects
  $server = New-Object Microsoft.SqlServer.Management.Smo.Server $instancename
  $i = 0
 
while ($i -ne 100)
  {
    $agent = $server.JobServer.Jobs
 
  if($agent){$i = 100}
    else
    {
 
    Start-Sleep -s 1
      $i += 1
    }
  }
 
$Restore = $agent | Where-Object {$_.Name -eq $jobname}
  $Today = Get-Date
  $continue = $true
 
# Get Time Value to Start and Stop
  $ActiveDateTime=[datetime]::Parse((Get-Date -Format 'HH:MM'))
  $StopDateTime=$ActiveDateTime.addminutes(1)
 
$StopTime=$StopDateTime.toString("HH:MM")
  if ($Restore -eq $null)
  {
 
   Write-host "$jobname does not exist on $instancename"
    $continue = $false
  }
 
if ($continue){write-host -ForegroundColor Blue "$jobname Job is Executing. Please Wait.." -NoNewline}
  while ($continue)
{
$Restore.Refresh()
 
# if Job Completed Successfully     
  if ($Restore.CurrentRunStatus.value__ -eq 4 -and $Restore.LastRunDate -gt $Today.AddDays(-1) -and $Restore.LastRunOutcome -eq "Succeeded")
  {
 
   Write-host "$jobname complete."
    $continue = $false
  }
 
# if Job is curently executing     
  elseif ($Restore.CurrentRunStatus.value__ -eq 1)
  {
 
  Write-host -ForegroundColor Blue "." -NoNewline
    start-Sleep -s 5
  }
 
# if Job Suspended    
  elseif ($Restore.CurrentRunStatus.value__ -eq 5)
  {
 
   Write-host "$jobname Job has been suspended."
    $continue = $false
  }
 
# if Job Cancelled     
  elseif ($Restore.CurrentRunStatus.value__ -eq 4 -and $Restore.LastRunOutcome -eq "Cancelled")
  {
 
    Write-host "$jobname Job has been Cancelled"
     $continue = $false
  }
 }
}
 

 

mandag den 7. oktober 2013

SPBG High availability with SharePoint 2013

I hold a speech at the SPBG network seminar's, the topic was (High availability with SharePoint 2013) and yes, still using the Microsoft "metro" style.

A great seminar with with a lot of good questions. 
SPBG, is known for it's high end SharePoint users and technicians, with a broad knowledge in alll topics.
Please wisit http://www.spbg.dk

 The slides from the seminar is shown here and your all welcome to leave some comment.

Slides

torsdag den 6. juni 2013

jBoye seminar in London

Yesterday I hold a speech at the one of jBoye network seminar's in London, the topic (High availability with SharePoint 2013) and yes, still using the Microsoft metro style.

A great seminar with with a great view over London city. I wish my office hade this view.
Big thanks to Lau, who facilitated the meeting and to jBoye for arranging the seminar. Please visit http://www.jboye.com

 The slides from the seminar is shown here and your all welcome to leave some comment.
(Please mind that all Saxo Bank specific slides have been removed)

Slides

onsdag den 10. april 2013

Disable all Health analyzer rules in SharePoint 2013


As in SharePoint 2010 we also in SharePoint 2013 have a lot of jobs running and analyzing the overall health of our server.  
All those rules are really nice, but in some cases you don’t need them. 
Example: 
  • If you know that you are running with databases over the limit and you are okay with that. 
  • You are running with only one service account, and is okay with that.
  • Your running whit a smaller page file than your physical RAM  (Quit a big file if you have 48Gb)

They all have in common that you as an admin made choices, and as we all know admins never make bad decisions. So instead of mannerly disable rules one at the time, you can just disable all and enable the ones you need.

To disable all rules just run the script below, this will disable all rules. To include the SharePoint SnapIn read this post  

If you like, this can be combined with a xml file so you can disable rules based on this as well. See this post to get inspiration. I would recommend using this if your maintaining a large organisation with a lot of environments.

PowerShell Script:
------------------------------
function main
{
$healthList = [Microsoft.SharePoint.Administration.Health.SPHealthRulesList]::Local.Items
 foreach ($Listitem in $healthList)
 {
  Write-Host -ForegroundColor Green $listitem.Id ": " -NoNewline
  if ($Listitem["HealthRuleCheckEnabled"] -ne $false)
  {
   Write-Host -ForegroundColor Magenta "Disable job"
   $Listitem["HealthRuleCheckEnabled"] = $false
   $Listitem.Update()
  }
  else
  {
   Write-Host -ForegroundColor White Done -BackgroundColor Black
  }
 }
}

Have fun with PowerShell 


tirsdag den 12. marts 2013

Sharepoint Document cheked out to

I wass asked to make a blog on an topic that was related to the checked out to <none> problem.
When SharePoint gets a deadlock in relation to Check-In/Check-Out situation it is due to the state of the CheckOutUserID field in the AllDocs table.

This field is set to -1, which by SharePoint is interpreted as <none>

To fix the page and thus be able to check-in or -out the page the CheckOutUserID must be replaced by an existing UserID from that particular Site Collection/Web Application.

The below SQL script does that, however it is important, that one item exist in the list in addition to the broken one, as the ID used in the script below, will be taken from the first item having an ID different from -1.
Alternative Microsoft advice to use the ID "58"

Generic script below, only the Database name is needed prior to running this.

USE [Portal_XXX_content]
GO
BEGIN TRY
BEGIN TRANSACTION
-- Check if any documents checked out with an invalid status
IF (SELECT COUNT(*)
FROM AllDocs (NOLOCK)
WHERE CheckoutUserId = -1) <= 0
BEGIN
PRINT 'INFO: No document checked out with invalid user id (-1)'
END
ELSE
BEGIN
-- Check if any checked out documents available
IF (SELECT COUNT(*)
FROM AllDocs(NOLOCK)
WHERE CheckoutUserId IS NOT NULL
AND CheckoutUserId > -1) <= 0
BEGIN
PRINT 'ERROR: Nothing checked out so can''t find a valid user ID'
END
ELSE
BEGIN
-- Show documents that has invalid user id
SELECT *
  FROM AllDocs(NOLOCK)
  WHERE CheckoutUserId = -1
  --Updating sharepoint field [checkoutUserId] to a value different from -1
  UPDATE [AllDocs]
  SET CheckoutUserId = (SELECT TOPCheckoutUserId
                        FROM [AllDocs] (NOLOCK)
                        WHERE CheckoutUserId > -1)
WHERE CheckoutUserId = -1


SELECT *
  FROM AllUserData (NOLOCK)
  WHERE tp_CheckoutUserId = -1
  --Updating sharepoint field [tp_CheckoutUserId] to a value different from -1
  UPDATE [AllUserData]
  SET tp_CheckoutUserId = (SELECT TOP 1 CheckoutUserId
                           FROM [AllDocs] (NOLOCK)
                           WHERE CheckoutUserId > -1)
  WHERE tp_CheckoutUserId = -1
END
END
--Finish SharePoint update
PRINT 'The operation completed successfully.'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION
END
DECLARE @ErrorInfo NVARCHAR(4000)
SELECT @ErrorInfo = ERROR_MESSAGE()
RAISERROR(@ErrorInfo, 16, 1)
END CATCH

This Solution is supported by Microsoft!