Change SQL Servers Authentication Mode with PowerShell using SMO

# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'sqlnodedev1'
[string]$nm = $s.Name
[string]$mode = $s.Settings.LoginMode

write-output "Instance Name: $nm"
write-output "Login Mode: $mode"


#Change to Mixed Mode
$s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

# Make the changes
$s.Alter()



PowerShell Script – Restart the SQL Server Agent Service (Using SMO)
-----------------------------------------------------------------------------------------

save the content of below highlighted in a  file.
file name SQLServerAgent_restart.ps1

Example Execution: .\SQLServerAgent_restart.ps1 ServerName

param([String]$ServerName)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null

#Create a new Managed computer object for the instance
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $ServerName

$sqlagnt = $mc.Services['SQLSERVERAGENT']

Write-Host "Stopping SQL Server Agent"

$sqlagnt.Stop()
start-sleep -s 10
$sqlagnt.Start()

Write-Host "Started SQL Server Agent"



PowerShell Script – Restart the default  SQL Server Service (Using SMO)
---------------------------------------------------------------------------------------------

save the content of below highlighted in  red in a  file.
file name SQLServer_restart.ps1

Example Execution: .\SQLServer_restart_restart.ps1 ServerName


param([String]$ServerName)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null

#Create a new Managed computer object for the instance
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $ServerName

$sqlservr1 = $mc.Services['MSSQLSERVER']

Write-Host "Starting SQL Server"

$sqlservr1.Stop()
start-sleep -s 10
$sqlservr1.Start()

Write-Host "Started SQL Server"


Now we have to change it to suppose X list of servers in one shot, then how we will do
---------------------------------------------------------------------------------------------------
$srvlist = @'

Server1

Server2

Server3

'@

foreach ($srvnm in $srvlist)
  {

 # Connect to the instance using SMO

 $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srvnm

 #Change to Mixed Mode

 $s.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

 # Make the changes

 $s.Alter()
 }

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How to move Tempdb database to another drive using T-SQL in AZURE so you will have low cost for SQL Server

Go to local D:\ drive and add  NT Service\MSSQLSERVER to security and give full access.

Then execute the output of  below command to move the file..

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''D:\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');

Then go to Azure portal and deassociate the disks, so that you will low cost for SQL Server for testing..