Deploy System Center Service Manager (SCSM)

Part 1 – First Management Server

Prerequisites

  • SQL 2016 already configured
  • Windows Server 2016 VMs created to host SCSM
  • Domain account for the SCSM Service
  • Domain account for the Workflow Service
  • Domain Group for SCSM administrators
  • SCSM service and workflow accounts added to local administrators group on the server that will become the first management server
  • Copied ‘sxs’ folder from source media to C:\Windows\Sources\sxs
  • Extracted SCSM install folder
  • Downloaded SQL 2014 Analysis Services AMO installer and copied to the SCSM Prerequisites folder
  • Downloaded SQL 2012 Client installer and copied to the SCSM Prerequisites folder
  • SCSM Ports configured on the firewall SCSM Prerequisites – Firewall Ports

Prepare the Server

Install .Net Framework 2/3

Install-WindowsFeature Net-Framework-Core -source C:\Windows\Sources\sxs

Deploy SQL Tools

msiexec /i "E:\System Center Service Manager\Prerequisites\SQL_AS_AMO.msi" /quiet
msiexec /i "E:\System Center Service Manager\Prerequisites\sqlncli.msi" ACCEPTSQLNLICENSETERMS=YES /quiet
"E:\System Center Service Manager\Prerequisites\ReportViewer.exe" /q:a /c:"install.exe /q"

Install SCSM

"E:\System Center Service Manager\Setup.exe"
/install:Server
/ProductKey: Your key here
/AcceptEula:YES
/RegisteredOwner:Eval
/RegisteredOrganization:Eval
/CreateNewDatabase
/SQLServerInstance:AG01-Listener
/DatabaseName:ServiceManager
/DatabaseDataFilePath:F:\Databases
/DatabaseLogFilePath:G:\Logs
/InstallPath:"E:\Program Files\Microsoft System Center\Service Manager"
/ServiceRunUnderAccount:azuredev\scsmservice\password
/WorkflowAccount:azuredev\scsmworkflow\password
/ManagementGroupName:SM_AzureDev_MG_1802
/AdminRoleGroup:"azuredev\scsm admins"
/CustomerExperienceImprovementProgram:YES
/EnableErrorReporting:YES
/Silent
Advertisements

Create an SQL 2016 AlwaysOn Availability Group – Lab

Server Configuration

  • 2x Windows Server 2016 virtual servers; Server01, Server02
  • Disk Config:
    • Apps Disk on SCSI Controller 0
    • SQL DB Disk on SCSI Controller 1
    • SQL Log Disk on SCSI Controller 1
    • SQL TempDB Disk on SCSI Controller 2
  • SQL 2016 with SP2 Installed using DEFAULT instance
  • SQL Server service account and computer accounts granted access to backup share
  • An empty database called db1 on Server01
  • Latest Windows Updates applied
  • File Share configured on a third server for hosting the file share witness

Prepare Cluster

Install Failover Clustering

Install-WindowsFeature Failover-Clustering -IncludeManagementTools
Test-Cluster -Node Server01, Server02

Configure Failover Clustering

New-Cluster -Name SQLAAG01 -Node Server01, Server02 -StaticAddress 192.168.0.120
Set-ClusterQuorum -NodeAndFileShareMajority \\Server03\AAGFSW$

Configure SQL AlwaysOn

Set Parameters

#Parameters
$Domain = "sqldev.local"
$SQLBackup = "\\Server01\backups"
$DB = "DB01"
$Server01Object = Get-Item "SQLSERVER:\Sql\Server01\DEFAULT"
$Server02Object = Get-Item "SQLSERVER:\Sql\Server02\DEFAULT"
$Server01Nm = $Server01Object.Name
$Server02Nm = $Server02Object.Name
$Server01Instance = "DEFAULT"
$Server02Instance = "DEFAULT"
$ReplicaPort = "5022"
$FailoverMode = "Automatic"
$AvailabilityMode = "SynchronousCommit"
$EndpointURLSrv01 = "$Server01Nm" + "." + "$Domain" + ":" + "$ReplicaPort"
$EndpointURLSrv02 = "$Server02Nm" + "." + "$Domain" + ":" + "$ReplicaPort"
$AGName = "AG01"

Enable AlwaysOn

Import-Module sqlps
Enable-SqlAlwaysOn -ServerInstance Server01 -Force
Enable-SqlAlwaysOn -ServerInstance Server02 -Force

Create AlwaysOn Group

# Backup the database
Backup-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.bak" -ServerInstance "$Server01Nm"
Backup-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.log" -ServerInstance "$Server01Nm" -BackupAction Log

# Restore the database and log on node2
Restore-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.bak" -ServerInstance "$Server02Nm" -NoRecovery
Restore-SqlDatabase -Database "$DB" -BackupFile "$SQLBackup\$DB.log" -ServerInstance "$Server02Nm" -RestoreAction Log -NoRecovery

# Create an in-memory representation of the primary replica.
$primaryReplica = New-SqlAvailabilityReplica -Name "$Server01Nm" -EndpointURL "TCP://$EndpointURLSrv01" -AvailabilityMode "$AvailabilityMode" -FailoverMode "$FailoverMode" -Version $Server01Object.Version -AsTemplate

# Create an in-memory representation of the secondary replica.
$secondaryReplica = New-SqlAvailabilityReplica -Name "$Server02Nm" -EndpointURL "TCP://$EndpointURLSrv02" -AvailabilityMode "$AvailabilityMode" -FailoverMode "$FailoverMode" -Version $Server02Object.Version -AsTemplate

# Create the availability group
New-SqlAvailabilityGroup -Name "$AGName" -Path "SQLSERVER:\SQL\$Server01Nm\$Server01Instance" -AvailabilityReplica @($primaryReplica,$secondaryReplica) -Database "$DB"

Verify in SQL whether it’s created properly

Check the ports:

SELECT type_desc, port FROM sys.tcp_endpoints;
GO

Check the endpoints are started:

SELECT state_desc FROM sys.database_mirroring_endpoints;
GO

Start an endpoint:

ALTER ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 1433)
FOR database_mirroring (ROLE = ALL);
GO

Check permissions:

SELECT 'Metadata Check';
SELECT EP.name, SP.STATE,
   CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
      AS GRANTOR,
   SP.TYPE AS PERMISSION,
   CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
      AS GRANTEE
   FROM sys.server_permissions SP , sys.endpoints EP
   WHERE SP.major_id = EP.endpoint_id
   ORDER BY Permission,grantor, grantee;
GO

Continue the Availability Group configuration

# Join the secondary replica to the availability group.
Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\$Server02Nm\$Server02Instance" -Name "$AGName" 

# Join the secondary database to the availability group.
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$Server02Nm\$Server02Instance\AvailabilityGroups\$AGName" -Database "$DB"

This is an alternate scripting method to create an Availability Group

# Alternative way to create the group
$PrimaryServer = Get-Item "SQLSERVER:\SQL\Server01\DEFAULT"
$SecondaryServer = Get-Item "SQLSERVER:\SQL\Server02\DEFAULT"
$PrimaryReplica = New-SqlAvailabilityReplica -Name "Server01" -EndpointUrl "TCP://Server01.sqldev.local:5022" -FailoverMode "Automatic" -AvailabilityMode "SynchronousCommit" -AsTemplate -Version 13
$SecondaryReplica = New-SqlAvailabilityReplica -Name "Server02" -EndpointUrl "TCP://Server02.azuredev.local:5022" -FailoverMode "Automatic" -AvailabilityMode "SynchronousCommit" -AsTemplate -Version 13
New-SqlAvailabilityGroup -InputObject $PrimaryServer -Name "AG01" -AvailabilityReplica ($PrimaryReplica, $SecondaryReplica) -Database @("DB01")

Configure Listener

New-SqlAvailabilityGroupListener -Name AG01-Listener ` -StaticIp '192.168.0.121/255.255.255.0' ` -Path SQLSERVER:\Sql\$Server01\$Server01Instance\AvailabilityGroups\$AGName