SCCM: Backup SCCM using native SQL Backup

Native SQL backup has many advantages like compression, for example. Kent Agerlund has a nice tutorial how to automate the backup including saving zipped cd.latest file which is necessary for SCCM CB recovery if you made at least one in-console upgrade for your SCCM. The article is here. Unfortunately his powershell script does not clean old cd.latest archives and that can be a problem taking in consideration their size. In one of the comments under original post a modification was suggested, so Copy cd.latest powershell script would look like:

powershell.exe -command “Get-ChildItem –Path ‘U:\SQLBackup\*’ –Include ‘*.zip’ | Where-Object {$_.CreationTime -lt (Get-Date).AddDays(-7)} | Remove-Item; Add-Type –Assembly ‘System.IO.Compression.FileSystem’ -PassThru | Select -First 1 | ForEach-Object { [IO.Compression.ZIPFile]::CreateFromDirectory(‘e:\program files\microsoft configuration manager\cd.latest’, ‘U:\sqlBackup\cdlatest’ + (Get-Date –format ‘yyyyMMddHHmm’) + ‘.zip’) }”

  (of course, change paths to your SCCM installation folder and your backup folders).

With that script implemented only last 7 cd.latest archives will be saved.

SCCM: In-console Update stuck in “Checking prerequisites”

I am installing quite a lot of SCCM environments these days and several times bumped into an issue when in-console update from one version of SCCM to the next one stuck on some step.

For “Downloading” Microsoft recommends to restart SMS_Executive, but for “Checkin Prerequisites” it was more difficult – Community recommended to play with SQL databases or start update from cd.latest.

None of those methods is supported by Microsoft though. (Se more here: http://gerryhampsoncm.blogspot.ca/2016/04/configuration-manager-cb-upgrades-what.html)

With version SCCM build 1706 Microsoft finally introduced a CMUpdateReset tool allowing to rectify a failed state for in-console upgrade by deleting a failing package. Here is a link to KB describing the process: https://docs.microsoft.com/en-us/sccm/core/servers/manage/update-reset-tool

SCCM: Side-by-side migration issue with client reassignment

I was busy with an SCCM migration recently. A Customer wanted to get gradual side-by-side migration from an old SCCM 2012 R2 to a shiny SCCM CB.

The issue I faced was related to a Client re-assignment from the old to the new SCCM site.

As recommended I tried Jason Sandy’s script to reinstall the old client and configure the new one for the new site.

The Client was successfully installed, but kept connect to the old site.

I tried to re-register site assignment in WMI as described https://prajwaldesai.com/change-site-code-of-configuration-manager-client/ and restart CCMEXEC service.

In ClientLocation log I saw the new site was assigned, MP found, but after that the site immediately was re-assigned to an old one and the Client tried to connect back to the old site

I tried  completely uninstall the client, use push etc.. without success.

Finally I noted

LSRefreshSiteCode: Group Policy Updated the assigned site code <old site code>, which is different than the existing assigned site code <new site code >. Will attempt re-assignment.

I checked GPOs and found a disabled GPO containing SCCM ADMX template with a site assignment.

The matter in fact once applied GPO tattoes its settings in the registry and they remains there even if GPO is not active anymore.

So I opened GPO template (located in ConfigMgr installation folder\Tools\ConfigMgrADMTemplates and founf the registry key in question is “hklm\SOFTWARE\Microsoft\SMS\Mobile Client”. Originally I planned to change only site code value there, but found Henrik’s article where he recommended to remove all values from the key all together.

Probably both approaches can work, so I created a simple cmd script and pushed it from the SCCM

REG delete “hklm\SOFTWARE\Microsoft\SMS\Mobile Client” /v GPRequestedSiteAssignmentCode /f REG delete “hklm\SOFTWARE\Microsoft\SMS\Mobile Client” /v GPSiteAssignmentRetryDuration(Hour) /f REG delete “hklm\SOFTWARE\Microsoft\SMS\Mobile Client” /v GPSiteAssignmentRetryInterval(Min) /f cscript set-site-code.vbs

first three commands are cleaning settings hardcoded by GPO, the forth one force SCCM site code using a VBS script from here

After the script finished I restarted ccmexec and fount the client registered in the new site successfully.

Azure: SQL Server 2017 Always-On Availability group on Windows Server 2016 hosted by Azure

One of my Customers needed a help with installation of SQL 2017 Always-on Cluster on Windows Server 2016 in Azure (IAAS).

Here is a log of my actions. I used available instructions for SQL 2014 on Windows Server 2012 R2, but added a new feature for Server 2016 – Cloud witness. That allowed my Customer to save on a File Server compute.

It was just one time install, so I did not bother to create an ARM template (unfortunately the one available on GitHub for Server 2012R2 and SQL 2014 SP2 does not work with Server 2016 image)

1. My Customer wanted to use its own licenses, so I decided to use images Microsoft made available for this scenario recently recently. Here is a script to check what BYOL images are available for your region

Get-AzureRMVMImageOffer -Location ‘Eastus’ -Publisher ‘MicrosoftSQLServer’ | Select Offer

I used SQL2017-WS2016-BYOL (the image conveniently has a data drive in addition to System and Scratch drives)

2. Bring two VMs from the image

3. Logon to the VMs and join them to the domain (or add an extension for autojoin)

4. Change Logon accounts for SQL  Server Service and SQL Client Service to  domain accounts (necessary for Clustering)

5. Configure SPN for SQl Server Service account

6. Add ports TCP 1433, 59999 (Azure LB Probe), 5022 (Database Mirroring) to Windows firewall exceptions

5. Restart VMs

6. Add Failover Clustering Feature on both

7. Build a one node Cluster on the first node. DO NOT USE VERIFICATION

8. Create Client Access name (if installation account has enough rights it will be automatically added to the same OU where machine accounts for the nodes are otherwise you need to create it manually).

9. Azure cannot bound multiple IPs to NIC, so Change IP address for Client Access to Manual and set it up to a free IP from your subnet. For that Right-click the failed IP Address resource, and then click Properties

10. Select Static IP Address and specify an available address from subnet where the SQL Server is in the Address text box. Then, click OK.

11. In the Cluster Core Resources section, right-click cluster name and click Bring Online. Then, wait until both resources are online. When the cluster name resource comes online, it updates the DC server with a new AD computer account. Use this AD account to run the Availability Group clustered service later.

12. Add the second node to the Cluster (using Failover Cluster Management Console). Do not do Validation

13. In the Confirmation page if you are using Storage Spaces, clear the checkbox labeled Add all eligible storage to the cluster.

14. Add a witness using Azure Storage account as described here: https://docs.microsoft.com/en-us/windows-server/failover-clustering/deploy-cloud-witness

15. Start SQL Server Manager, Goto to Properties of SQLServer (MSSQLSERVER) Click the AlwaysOn High Availability tab, then select Enable AlwaysOn Availability Groups

16. Apply and restart SQL Server Service

17. Create an empty database on the first SQL

18. Create a backup share and backup db there (full backup)

19. Restore the full and log backups to the second SQL Server with the NORECOVERY option

20. Create the Availability Group

     – right-click AlwaysOn High Availability and click New Availability Group Wizard  

– In the Specify Availability Group Name page, type a name for the Availability Group, for example AG1, in Availability group name.

– In the Select Databases page, select your database. The database meets the prerequisites for an Availability Group because you have taken at least one full backup on the intended primary replica.

– Click Add replica

– Type name of the second server in popup window and click Connect

– Now the second server should be visible in the Specify Replica page

– Click Endpoints to see db mirroring. Check the port!

– In the Select Initial Data Syncronization page select Full and specify a shared network location. Note: if DB is already restored you do need to do full syncronization; in this case use “Joint Only”

21. Check Availability Group: In Object Explorer, expand AlwaysOn High Availability, then expand Availability Groups. You should now see the new Availability Group in this container. Right-click the Availability Group and click Show Dashboard.

22. In Failover Cluster Manager, click your cluster. Select Roles. The Availability Group name you used is a role on the cluster. That Availability Group does not have an IP address for client connections, because you did not configure a listener. You will configure the listener after you create an Azure load balancer.

23. Create Azure Load balancer with probe on 59999 and LB rule to load balance port 1433

24. Configure the listener

– Select the Networks node, and note the cluster network name. Use this name in the $ClusterNetworkName variable in the PowerShell script.

– Add the client access point.
a. The client access point is the network name that applications use to connect to the databases in an availability group. Create the client access point in Failover Cluster Manager.

a. Expand the cluster name, and then click Roles.

b. In the Roles pane, right-click the availability group name, and then select Add Resource > Client Access Point.

c. In the Name box, create a name for this new listener. The name for the new listener is the network name that applications use to connect to databases in the SQL Server availability group

d. To finish creating the listener, click Next twice, and then click Finish. Do not bring the listener or resource online at this point

– Configure IP resource for the Availability Group

a. Click the Resources tab, and then expand the client access point you created.
The client access point is offline.

b. Right-click the IP resource, and then click properties. Note the name of the IP address, and use it in the $IPResourceName variable in the PowerShell script.

c. Under IP Address, click Static IP Address. Set the IP address as the same address that you used when you set the load balancer address on the Azure portal.

– Make the SQL Server availability group resource dependent on the client access point.

a. In Failover Cluster Manager, click Roles, and then click your availability group.

b. On the Resources tab, under Other Resources, right-click the availability resource group, and then click Properties.

c. On the dependencies tab, add the name of the client access point (the listener) resource.

d. Click OK

– Make the client access point resource dependent on the IP address.

a. In Failover Cluster Manager, click Roles, and then click your availability group.

b. On the Resources tab, right-click the client access point resource under Server Name, and then click Properties.

c. Click the Dependencies tab. Verify that the IP address is a dependency. If it is not, set a dependency on the IP address. If there are multiple resources listed, verify that the IP addresses have OR, not AND, dependencies. Click OK.

d. Right-click the listener name, and then click Bring Online.

– Set the cluster parameters in PowerShell.

a. Copy the following PowerShell script to one of your SQL Server instances. Update the variables for your environment.

PowerShell

$ClusterNetworkName = “<MyClusterNetworkName>” # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name) $IPResourceName = “<IPResourceName>” # the IP Address resource name $ILBIP = “<n.n.n.n>” # the IP Address of the Internal Load Balancer (ILB). This is the static IP address for the load balancer you configured in the Azure portal. [int]$ProbePort = <nnnnn> Import-Module FailoverClusters Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{“Address”=”$ILBIP”;”ProbePort”=$ProbePort;”SubnetMask”=”255.255.255.255″;”Network”=”$ClusterNetworkName”;”EnableDhcp”=0}

b. Set the cluster parameters by running the PowerShell script on one of the cluster nodes.

Note

If your SQL Server instances are in separate regions, you need to run the PowerShell script twice. The first time, use the $ILBIP and $ProbePort from the first region. The second time, use the $ILBIP and $ProbePort from the second region. The cluster network name and the cluster IP resource name are the same.

25. Set Listener Port

1. Launch SQL Server Management Studio and connect to the primary replica.
2. Navigate to AlwaysOn High Availability | Availability Groups | Availability Group Listeners.
3. You should now see the listener name that you created in Failover Cluster Manager. Right-click the listener name and click Properties.
4. In the Port box, specify the port number for the Availability Group listener by using the $EndpointPort you used earlier (1433 was the default), then click OK.

26. Test Connection to listener

For connectivity test use either telnet to port 1433 of listener or (from the server which is not the replica owner)

sqlcmd –S <listenername> -E