Migrating SSRS 2012 Farm to SSRS 2016 Farm

SSRS 2016 Scale Out Deployment consists of

• three SSRS application servers 
• a reportserver and reportservertempdb databases on alwaysOn AG server
• a network load balancer

SSRS 2016 Load Balanced Farm

Install SSRS service in all three nodes which participate in SSRS Scale Out Deployment

First, install SSRS service on three windows 2012 R2 servers - with install only option. SSRS service is running under a service account. The service account is added as a local administrator on each server.

AlwaysOn AG Servers

In this section, I will not blog about how to build AlwaysOn AG servers. I assumed that readers know how to build alwayson availability group. I have already built SQL sever 2016 AlwaysOn Availability group. Listener name is SQL2016List.

Important Steps

SQL Server Reporting Service (SSRS) is installed on three application servers (Not configured yet) .

Backup

• Back up reportserver and reportservertempdb database from SSRS 2012 Farm
• Back up encryption key from one of the SSRS 2012 application servers from SSRS 2012 Farm. Keep the password of the encryption key

Restore

• Restore reportserver and reportservertempdb on Primary node on AlwaysOn Available Group server
• Add reportserver and reportservertempdb to Availability group on AlwaysOn server

Script

• On primary node of alwayson availability group, run the following script

           use ReportServer
           go

           select * from ReportServer.dbo.Keys


The query returned SSRS 2012 application servers because we restored a reportserver database from SSRS 2012.

• Delete the rows which are SSRS 2012 (old server) server names. 

Delete from reportserver.dbo.Keys where MachineName = 'Old SSRS 2012 app server name '

SQL Server Reporting Service Configuration (Do these steps in all SSRS servers )

Log in to first SSRS 2016 server and configure SSRS service.

Web Service URL

Click Apply

Database

Change the database to point to a Listener (SQL2016List) and connect to reportsever database.

SQL server Name : SQL2016List

Database : Reportserver

Web portal

Email

In Email settings, we need to fill out SMTP Server and Sender email. The SMTP information is used in subscription reports.

Execution Account

Execution Account is a different service account (not a service account running under SSRS) . It has read/executte access to Datamart and Data warehouse data sources.

Encryption Keys

Restore an encryption key from SSRS 2012 Farm to SSRS 2016 with password

After configuring every tab, SSRS servers joined in Scale out Deployment.

Test SSRS reporting individual link

Test each reporting service link - example http://ssrs01/ReportServer and http://ssrs01/Reports . All three SSRS reporting service web portal links must be working before setting up in a network load balancer.

View Server State Configuration ( do this in all SSRS app. server)

Generate a machine key

First, generate a machine key from the website http://www.developerfusion.com/tools/generatemachinekey/

• In three SSRS 2016 application server, Go to \Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer. 
• Make a copy of web.config file
• Open original web.config file in notepad as administrator
• Paste the machine key tag between system.web Tag
• Save the file
• Verify that all web.Config files in all SSRS servers contain identical elements in the section 
• Restart SSRS service

Configure Hostname and URL Root  (do this in all SSRS app. server)

• Go to \Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer
• Make a copy of rsreportserver.config file
• Open rsreportserver.config with notepad as administartor
• add Hostname and UrlRoot between Service tag
• Hostname is SSRSVirtualIPaddress.domain.dom --load balanced virtual ip address
• Urlroot is http://SSRSVirtualIpAddress.domain.dom
• Save the file
• Restart SSRS service

Update Host File (do this in all SSRS app. server)

• Go to C:\Windows\System32\drivers\etc
• Open host file in notepad as administrator
• Add SSRS app. server IP address and Load Balanced SSRS Virtual IP address
• Save the file

Example:
# Copyright (c) 1993-2009 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host

# localhost name resolution is handled within DNS itself.
# 127.0.0.1       localhost
# ::1             localhost
10.31.22.39 ssrsvip.domain.us.dom

Back Connection Hostname Registry (do this in all SSRS app. server)

• Click Start, click Run, type regedit, and then click OK.
• In Registry Editor, locate and then click the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
• Right-click MSV1_0, point to New, and then click Multi-String Value.

• Type BackConnectionHostNames, and then press ENTER.
• Right-click BackConnectionHostNames, and then click Modify.
• In the Value data box, type

SSRSPRDVIPR01SSRSPRDVIPR01.domain.us.domSSRSPRDAPPR01SSRSPRDAPPR01.domain.us.domSSRSPRDAPPR02SSRSPRDAPPR02.domain.us.domSSRSPRDAPPR03SSRSPRDAPPR03.domain.us.dom 

• OK
• Restart the server

Database Permission

Verify RSExecRole exists in master, msdb,Reportserver and ReportServertTempDB databases on Primary and Secondary node. If not,  please run the following script

USE [master]
GO

/****** Object:  DatabaseRole [RSExecRole]    Script Date: 8/31/2017 2:01:51 PM ******/
CREATE ROLE [RSExecRole]
GO

Assume that the same service account is running SSRS service on there SSRS application servers. It must have the following permission.

master - Public,RSExecRole

msdb - db_owner,RSExecRole,SQLAgentOperatorRole,SQLAgentReaderRole,SQLAgentUserRole

ReportServer - db_owner, RSExecRole

ReportServerTempDB - db_owner,RsExecRole

Master Script

Run the following script in master database on where reportserver and reportservertempdb exist.

Use master

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole

GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole

GO

Test Report Subscription

Create a test subscription on each SSRS link and verify subscription email works on all SSRS portal which participate in Scale Out deployment.

Last, create a test a subscription on SSRS load balance VIP link. SSRSPRDVIPR01.domain.us.dom\Reports

Below is subscription test cases via SSRS Load Balanced link.

Test	Result
Create a subscription	Passed
Send email via a subscription	Passed
Does a subscription create a job in primary	Passed
Does a subscription create a subscription job in  secondary	Failed
Failover databases from primary to secondary
	Passed
After SSRS restart, does a created subscription job created in a primary(previously secondary)	Passed
Delete a subscription in SSRS load balanced link	Passed
Does deleting a subscription from SSRS load balanced link remove a subscription job from SQL agent in both primary and secondary node?	Failed
Does deleting a subscription from SSRS load balanced link remove a subscription job from SQL agent in primary node	Passed
Does deleting a subscription from SSRS load balanced link remove a subscription job from SQL agent in Secondary node	Failed

We discovered that subscription jobs are only created in SQL Server Agent in Primary replica and they not created in SQL Agent job in a secondary replica. When availability group failovers from primary to secondary node,  these subscription job are not automatically created in SQL Server Agent upcoming Primary replica. In order to recreate these subscription jobs in SQL Server Agent automatically after AG group failover we have to restart SSRS service on one of the SSRS server node in the farm.

In order to restart one of the SSRS services automatically , we need to detect AG failover events in Primary and Secondary replica. We will detect EventID=41074 or EventID=41075 under applications from System events. Please follow the screenshots below.

Program Script : C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Add Arguments : noprofile -executionpolicy RemoteSigned -file E:\SSRSLog\SSRSWakeUpCall.ps1

SSRSWakeUpCall.ps1 powershell script is located at E:\SSRSLog\ on AlwaysOn server primary and secondary node.

The powershell script restarts SSRS service on SSRS server node 1 and launch SSRS web link.

SSRS Restart Powershell Script


#WakeUp SSRS resources
Get-Service -Name "SQL Server Reporting Services (MSSQLSERVER)" -ComputerName SSRS01 |Stop-Service
Get-Service -Name "SQL Server Reporting Services (MSSQLSERVER)" -ComputerName SSRS01 |Start-Service
[string] $url = "http://SSRS01/Reports/Pages/Folder.aspx";
[System.Net.WebClient] $wc = New-Object System.Net.WebClient;
$wc.UseDefaultCredentials = $true;
$result = $wc.DownloadString($url);
$wc.Dispose();

Disable Report Subscription jobs in SQL Server Agent on AlwaysOn Secondary replica

To prevent duplicate email report, we need to disable subscription jobs in Secondary node SQL Agent. To disable these job automatically, we need to get a notification of AG failover event. To get notification of AlwaysOn AG group failover, AG Role Change alert is created in SQL Alerts.

USE [msdb]
GO

/****** Object:  Alert [AG Role Change]    Script Date: 8/31/2017 2:23:45 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'AG Role Change',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'6bc97b04-4786-474f-8c48-8aca8bd4c5c7'
GO

The AG Role Change alert will run a job which executes a stored procedure when AG role change from primary to secondary. The stored procedure will disable report subscription jobs in SQL Server Agent on secondary replica.

JOBChange_AGFailover Stored Procedure

CREATE PROCEDURE [dbo].[JobChange_AGFailover]
AS

BEGIN

  SET NOCOUNT ON;

  DECLARE @jobID UNIQUEIDENTIFIER  --variable for job_id
  DECLARE @AG_enable tinyint

  select @AG_enable = sys.fn_hadr_is_primary_replica ('ReportServer')
 
  DECLARE jobidCursor CURSOR FOR   --used for cursor allocation

  SELECT j.job_id
  FROM msdb.dbo.sysjobs j
  INNER JOIN msdb.dbo.syscategories c
  ON j.category_id = c.category_id
  where c.name = 'Report Server'

--update jobs
  OPEN jobidCursor
  FETCH NEXT FROM jobidCursor INTO @jobID
  WHILE @@Fetch_Status = 0  
  BEGIN              
        EXEC msdb.dbo.sp_update_job @job_id=@jobID, @enabled = @AG_enable              
 FETCH Next FROM jobidCursor INTO @jobID  
  END

  CLOSE jobidCursor
  DEALLOCATE jobidCursor

END

Optimize reporting service startup

Recycle time in RSreportserver.config

In current setting, recycle of application domain is every 12 hrs(default). We can optimize slow response by changing recycle time to larger value(24 hr) and initiate the first call of the report..
By implementing the changes, App Domain recycle will occur outside of business hour and respond time of reporting service will improve.

In rsreportserver config file, change RecycleTime to 1440


Powershell Script

SSRS WakeUp Call

I scheduled to run the powershell script every night at 1 am/1:05 amd /1:10 am on all SSRS application servers . The powershell script in scheduled in Task Scheduler. The script stops and starts SSRS service. Starts reporting service web link.

#SSRSwakeupcall.ps
#WakeUp SSRS resources
Stop-Service "SQL Server Reporting Services (MSSQLSERVER)"
Start-Service "SQL Server Reporting Services (MSSQLSERVER)"
[string] $url = "http://SSRSAPP01/Reports/browse/";
[System.Net.WebClient] $wc = New-Object System.Net.WebClient;
$wc.UseDefaultCredentials = $true;
$result = $wc.DownloadString($url);
$wc.Dispose();

Using Powershell to install advertised OS Patches from SCCM software Center

#Get a list of server from DBA DW datatbase
$servername = invoke-sqlcmd -ServerInstance 2012eitsqltest -Database DBA_ServerDW `
-Query "select ServerName from DBA_ServerDW.dbo.SQLServers
where servername not like '%\% and'
and OSPatchServerMonthly = 1 and Decommissioned = 0
and Environment in('QA','UAT')"


foreach($server in $servername)

{

                $ServerN=$server.ServerName

             
#Compliance state of the software update that indicates if the software update is missing and needs to be installed.ComplianceState=0, 0 =ciNotPresent

$AvailableUpdates = Get-WmiObject -Class CCM_SoftwareUpdate -Filter ComplianceState=0 -Namespace root\CCM\ClientSDK -Computername $ServerN
              

# The following is done to do 2 things: Get the missing updates (ComplianceState=0)and take the PowerShell object and turn it into an array of WMI objects 

$AvailableUpdatesReformatted = @($AvailableUpdates | ForEach-Object {if($_.ComplianceState -eq 0){[WMI]$_.__PATH}}) 
              
                            
                
# The following is the invoke of the CCM_SoftwareUpdatesManager.InstallUpdates with our found updates 
# NOTE: the command in the ArgumentList is intentional, as it flattens the Object into a System.Array for us 
# The WMI method requires it in this format.


$InstallReturn = Invoke-WmiMethod -Class CCM_SoftwareUpdatesManager -Name InstallUpdates -ArgumentList (,$AvailableUpdatesReformatted) -Namespace root\ccm\clientsdk -Computername $ServerN
                 
                               
                                     
                                
}
  
  

foreach($server in $servername)

{

       $ServerN_Check=$server.ServerName


        # Set a variable to True
        $Allinstalledstatus = $True

#After installation is started ,get completion status of all updates installation by getting evaluationstate
#NOTE : When a software update is not in a progress state, the value of EvaluationState is none or available, depending on whether there was any progress at any point in the past
#if a software update was downloaded at activation time, the value of EvaluationState is none. 

While ($Allinstalledstatus -ne $false)
                
                        {
                
                    
 # Checking EvaluationState( 0=ciJobStateNone,2=ciJobStateSubmitted, 3=ciJobStateDetecting, 4=ciJobStatePreDownload, 5=ciJobStateDownloading, 6=ciJobStateWaitInstall, 7=ciJobStateInstalling, 11=ciJobStateVerifying)
#If Evaluation State is not False stay in while loop

$CCMUpdate = get-wmiobject -query "SELECT EvaluationState FROM CCM_SoftwareUpdate" -namespace "ROOT\ccm\ClientSDK" -Computername $ServerN_Check
                            if(@($CCMUpdate | where {$_.EvaluationState -eq 0 -or  $_.EvaluationState -eq 2 -or $_.EvaluationState -eq 3 -or $_.EvaluationState -eq 4 -or $_.EvaluationState -eq 5 -or $_.EvaluationState -eq 6 -or $_.EvaluationState -eq 7 -or $_.EvaluationState -eq 11 }).length -ne 0) { $Allinstalledstatus=$true } else { $Allinstalledstatus=$false }  
write-host $ServerN_Check
write-host $Allinstalledstatus
                
}
                                                    
#When installation is done, it returns False.

If($Allinstalledstatus -eq $false)
{
            
#Get EvaluationState of each patch, KB name and number, URL

$StatusList = (Get-WmiObject -ComputerName $ServerN_Check -Query 'SELECT EvaluationState,ArticleID,Name,URL,ErrorCode FROM CCM_SoftwareUpdate where ComplianceState = 0' -Namespace ROOT\ccm\ClientSDK);
                
                        foreach ($Status in $StatusList)
                        {
                            $InstalledStatus= $Status.EvaluationState
                            $KBDescription= $Status.ArticleID +' '+ $Status.Name +' '+ $Status.URL
                            $ErrorCode = $Status.ErrorCode
                                       
#Log the updates status to Log Table
                            invoke-sqlcmd -ServerInstance 2012eitsqltest -Database DBA_ServerDW `
                            -Query  "INSERT INTO SCCM_InstalledOSUpdates([ServerName],[KBDescription],[InstalledStatus],ErrorCode)
                            VALUES ('$ServerN_Check','$KBDescription','$InstalledStatus','$ErrorCode')"
                        
                        }

                        }

}    

Manage SQL server service on Linux(RHEL)

Check SQL server status

sudo systemctl status mssql-server

Start and Stop service

sudo systemctl stop mssql-server

sudo systemctl start mssql-server

sudo systemctl restart mssql-server

Access error log files

The SQL Server engine logs to the /var/opt/mssql/log

cd /var/opt/mssql/log

cat errorlog

Reset SA password

sudo systemctl stop mssql-server

sudo /opt/mssql/bin/mssql-conf setup

Basic Linux commands

Find out linux version

more /etc/*-release

How long the server is up?

uptime

How many cpus I have ?

lscpu

Last system reboot

who -b

Log off

Ctrl + d

print working directory

pwd

Change to home

cd $home

Create a directory

mkdir name

mkdir -p xx/yy/zz

Remove direcotry

rmidr name

List Directory

ls

ls -l  (long format)

Deleting files

rm filename

rm -i source/report.cpp

rm -r *  -- delete all the files

Process

ps -a 

kill processid

Grep

You can use the grep command to search for a specified pattern in a file or list of files.

The pattern used by the grep command is called regular expression, hence the strange

name of the command (Global Regular Expression Print).

$cat myfile

$grep WORK myfile

touch newfile -d 

CPU usage

top

Basic File Management

cp - copy

dd -image

mv - move and rename

rm - remove

ls - list

Remote Login

ssh -l root

ssh servername

Linux Performance

rpm –ql procps-ng

rpm – redhat package manager

ql – query list

proc ps  -ng (next generation)

rpm –ql  /usr/bin/top

What programs are available in user ?

rpm –ql procps-ng | grep  ‘^/usr/bin/’

Go to the process

cd /proc

Find my process

pwdx $$

Go to my process

cd /proc/$$

Generating Transaction Log script

Production transaction logs are backed up every 15 or 30 mins. We have a full database backup daily. When we need to restore a database point in time. We will need to restore transaction logs. It will take time to restore each transaction log if we have more than 20 transaction log a day.

I wrote the following script to generate Transaction log restore script which I can use to restore T log faster after full backup is restored. 

--***********Read this before running the script**************************************
-- The following script generates Transaction Log restore script.
-- If database crashed , we will need to restore a full database backup and Transaction logs.
-- After restoring a full database backup, we will need to restore all the good transaction logs which are backed up after the full back up is taken. 

-- This script will generates T Log restores scripts. We copy the scripts which is generated in message window.

-- Step1 . We will need to enable xp_cmdshell to 1 by running the following script one time only . After restore is done, we need to disbale xp_cmdshell

--sp_configure xp_cmdshell,1

--go

--reconfigure

--go

-- Step 2 . We need to change 3 variable values in the script, @BP,@BP2,@DatabaseName

-- @BP and @BP2 values are the Transaction Log backup location path and both values must be the same 

-- @DatabaseName is the name of the database that you are restoring

Declare @BP varchar(max)

Declare @GF varchar(max)

Declare @sql nvarchar(max)

--************************************************************

--Change the backup path of Transaction Logs here

--************************************************************

set @BP = 'C:\BackUp'

--************************************************************

--************************************************************

--construct sql for getting files

set @GF = ''' dir '+  @BP +'''' 

set @sql = 'EXEC MASTER..XP_CMDSHELL'+ ''+ @GF + ''

--Create a staging table

CREATE TABLE GetBackupFiles (Files VARCHAR(500))

INSERT INTO GetBackupFiles

Exec sp_executesql @sql

--Cleaning up the output from file list

--delete all directories

DELETE GetBackupFiles WHERE Files LIKE '%
%'

----delete all informational messages

DELETE GetBackupFiles WHERE Files LIKE ' %'

----delete the null values

DELETE GetBackupFiles WHERE Files IS NULL

--add FileDate column

ALTER TABLE GetBackupFiles add FileDate DateTime

GO

--split dateinfo

UPDATE GetBackupFiles SET FileDate =LEFT(files,20)

--get rid of dateinfo

UPDATE GetBackupFiles SET files =RIGHT(files,(LEN(files)-20))

--get rid of leading spaces

UPDATE GetBackupFiles SET files =LTRIM(files)

Declare @Databasename varchar(200)

Declare  @BP2 as varchar(max)

--*************************************************************

--Change the backup path here again same backup path as @BP

--Change the Databaes name here

--*************************************************************

set @BP2 = 'C:\BackUp'

Set @Databasename = 'MyDatabase'

--**************************************************************

--**************************************************************

-- Generate Restore script for Transaction Log

Declare @FN varchar(max)

Declare GetFileNames Cursor LOCAL for

--************************************************************************

--************************************************************************

-- Here we change the timestamp of the full database backup***************

--************************************************************************

--************************************************************************

select RIGHT(files,LEN(files) -PATINDEX('% %',files)) from GetBackupFiles

where FileDate >= '2017-05-24 23:45:00.000'

order by FileDate asc

--************************************************************************

--************************************************************************

Open GetFileNames;

Fetch GetFileNames into

@FN

WHILE @@FETCH_STATUS = 0

Begin

----Build Transaction Logs restore script

Print 'RESTORE DATABASE ' + @Databasename + ' FROM DISK = '''+ @BP2+@FN+' '+ ''' WITH NORECOVERY'

Fetch GetFileNames into

@FN

End

Close GetFileNames

Deallocate GetFileNames

--Drop staging table

Drop table GetBackupFiles

I gave credit to http://www.tek-tips.com/viewthread.cfm?qid=1278283

Thank you for reading.

Fix for “The target principal name is incorrect. Cannot generate SSPI context.”

After changing a service account running SQL server, I get an error when I connect to SQL server from Management Studio from my laptop or other servers. The error is below.

“The target principal name is incorrect.  Cannot generate SSPI context.”

To Fix this,

You need to register the service account in SPN.

MSSQLSvc/ Server1.domain.us.dom Domain\ServiceAccount

MSSQLSvc/ Server1.domain.us.dom:1433 Domain\ServiceAccount

After SPN is registered successful, the error is resolved. Thank you.

xp_logininfo

xp_logininfo 'Domain\ADGroup',members

xp_logininfo returns members of the AD group in SQL server. I use this command to check who has access to a database server in the AD group.

Renaming NIC , NetBios Setting, Disabling Protocols

The objective of powershell script is to automate renaming NIC, changing NetBios setting in Private and Public NIC and Disabling some protocols in Private NIC.

I created the following powershell scripts to automate the changes. The changes are necessary to set up AlwaysOn availability group severs in my process. Thank you for reading.

import-module Netadapter

Get-NetAdapter | ? status -eq 'up'| Get-NetIPAddress -ea 0 -AddressFamily IPv4 | Select InterfaceAlias, IPAddress

#Rename NIC Public 10.*
$PubNic=Get-NetAdapter | ? status -eq 'up'| Get-NetIPAddress -ea 0 -AddressFamily IPv4 | where IPAddress -like '10.*'|Select InterfaceAlias
$PubNicName= $PubNic.InterfaceAlias
$NewPublicNicName = "Public NIC"
Rename-NetAdapter -Name $PubNicName -NewName $NewPublicNicName

#Rename NIC Private 192.*
$PriNic=Get-NetAdapter | ? status -eq 'up'| Get-NetIPAddress -ea 0 -AddressFamily IPv4 | where IPAddress -like '192.*'|Select InterfaceAlias
$PriNicName= $PriNic.InterfaceAlias
$NewPrivateNicName ="Private NIC"
Rename-NetAdapter -Name $PriNicName -NewName $NewPrivateNicName


#In Public NIC, Enable NetBios over TCP
#0: Enable Netbios via DHCP.1: Enable Netbios on the interface.2: Disable Netbios on the interface.

$PubNicConfig = Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '10.*'}
$PubNicConfig.SetTcpipNetbios(1)
#Verify the changes
Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '10.*'}|Select IpAddress,Description,TcpipNEtbiosOptions |format-list


#In Private NIC, Enable NetBios over TCP
#0: Enable Netbios via DHCP.1: Enable Netbios on the interface.2: Disable Netbios on the interface.

$PriNicConfig = Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'}
$PriNicConfig.SetTcpipNetbios(2)
#Verify the changes
Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'}|Select IpAddress,Description,TcpipNEtbiosOptions |format-list


#Private NIC, Do Not Register this connection  address in DNS
$PriNicConfig = Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'}
$PriNicConfig.SetDynamicDNSRegistration($false,$false)  
# Verify Wins, Netbios setting
Get-WmiObject Win32_NetworkAdapterConfiguration -filter "ipenabled = 'true'"  | where {$_.IpAddress -like '192.*'} |Select * |format-list


#Disable Protocols Public and Private protocols

#Disable Client for Microsoft Network
Disable-NetAdapterBinding -Name 'Private NIC' -ComponentID ms_msclient
#Disable File and Printer Sharing for Microsoft Networks
Disable-NetAdapterBinding -Name 'Private NIC' -ComponentID ms_server
#Enable Ipv6 for both Private and Public NIC
Enable-NetAdapterBinding -Name 'Private NIC' -ComponentID ms_tcpip6
Enable-NetAdapterBinding -Name 'Public NIC' -ComponentID ms_tcpip6
#Verify the changes
Get-netadapterbinding -Name 'Private NIC'
Get-netadapterbinding -Name 'Public NIC'

Changing NIC Bindings Order using Powershell Script

NIC Bindings Order

I did a lot of research on how to change NIC Binding Order using powershell. I am changing the binding Order to meet the requirement of Always On server setup. I created the following powershell script which changes registry of NIC binding. After you run the script you will not see the binding order is changed in UI but it is actually updated when you run IP config. I believe that the setting not being updated in UI is a bug in Windows.

In Adapters and Bindings tab, I move Public NIC on top of Private NIC. The powershell script will update 3 places in - Bind , Export and Route under CurrentControl set in Registry.

#-------------------------Public Setting ID------------------------------------------------#

$PublicSettingID = Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter "IPenabled = $true" | where {$_.IpAddress -like '10.*'}|select Settingid
$PubSettingID = $PublicSettingID.Settingid
Write-host $PubSettingID

#-------------------------Private Setting ID------------------------------------------------#

$PrivateSettingID = Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter "IPenabled = $true" | where {$_.IpAddress -like '192.*'}|select Settingid
$PriSettingID = $PrivateSettingID.Settingid
Write-host $PriSettingID

#---------------------------------BIND------------------------------------------------------#
$BindNewOrder = @()
$writereg = $null
$Bindkey = 'HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage'
$Bindvalue = 'Bind'

#get values in registry
$BindOldOrder = (Get-ItemProperty $Bindkey $Bindvalue).$Bindvalue 
write-host $BindOldOrder

$BindPubSettingID='\Device\'+$PubSettingID
$BindPriSettingID='\Device\'+$PriSettingID

#Order
if ($BindOldOrder -contains $BindPubSettingID) {$BindNewOrder += $BindPubSettingID}
if ($BindOldOrder -contains $BindPriSettingID) {$BindNewOrder += $BindPriSettingID}
if ($BindOldOrder.count -gt $BindNewOrder.count) {$BindNewOrder += $BindOldOrder}


$BindNewOrder = $BindNewOrder | select -unique
Write-host $BindNewOrder

#Change registry valules
Set-ItemProperty -path $Bindkey -Name $Bindvalue -Value $BindNewOrder

#>

#----------------------------EXPORT----------------------------------------------------#

$ExportNewOrder = @()
$Exportkey = 'HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage'
$Exportvalue = 'Export'
$ExportPubSettingID = '\Device\Tcpip_' + $PubSettingID
$ExportPriSettingID = '\Device\Tcpip_' + $PriSettingID

Write-host $ExportPubSettingID
Write-host $ExportPriSettingID

#get values in registry
$ExportOldOrder = (Get-ItemProperty $Exportkey $Exportvalue).$Exportvalue 
write-host $ExportOldOrder

#Order
if ($ExportOldOrder -contains $ExportPubSettingID) {$ExportNewOrder += $ExportPubSettingID}
if ($ExportOldOrder -contains $ExportPriSettingID) {$ExportNewOrder += $ExportPriSettingID}
if ($ExportOldOrder.count -gt $ExportNewOrder.count) {$ExportNewOrder += $ExportOldOrder}


$ExportNewOrder = $ExportNewOrder | select -unique
Write-host $ExportNewOrder
Set-ItemProperty -path $Exportkey -Name $Exportvalue -Value $ExportNewOrder


#-----------------------------ROUTE-------------------------------------------------------#

$RouteNewOrder = @()
$Routekey = 'HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Linkage'
$Routevalue = 'Route'
$RoutePubSettingID = '"' + $PubSettingID + '"'
$RoutePriSettingID = '"' + $PriSettingID + '"'

Write-host $RoutePubSettingID
Write-host $RoutePriSettingID

#get values in registry
$RouteOldOrder = (Get-ItemProperty $Routekey $Routevalue).$Routevalue 
write-host $RouteOldOrder

#Order
if ($RouteOldOrder -contains $RoutePubSettingID) {$RouteNewOrder += $RoutePubSettingID}
if ($RouteOldOrder -contains $RoutePriSettingID) {$RouteNewOrder += $RoutePriSettingID}
if ($RouteOldOrder.count -gt $RouteNewOrder.count) {$RouteNewOrder += $RouteOldOrder}


$RouteNewOrder = $RouteNewOrder | select -unique
Write-host $RouteNewOrder
Set-ItemProperty -path $Routekey -Name $Routevalue -Value $RouteNewOrder


After you run the script, you will see the output similar to below.

The changes takes affect on NIC Binding but UI is not updated. It think it is a bug in Microsoft windows. The UI does not change even after rebooting the server.When you run Ipconfig /all on the server, it returns public ip address then private ip address.