Tag: powershell

Upload files and folder into Azure Blob Storage using Powershell

Recently, I have had to automate a process to generate csv files and upload them to a certain folder in Azure blob storage. Here is the powershell code that does just that. After some research online, I have put together couple of solutions into one that worked well for me.

In order for this to work, you will need to install Azure powershell module on your machine. This solution assumes you are authenticating using account name and storage account key

#Upload csv files to Azure Blob Storage
$ErrorActionPreference = "Stop"

$acct = "storage-account-name" #Storage Account Name
$key = "storage-account-key" #Storage Account Key
$ContainerName = "container-name" #Container Name
$containerdirectory = "subfolder path within the container"
$localfilepath = "local-file-directory"

#create a context for communicating with azure storage
$ctx = New-AzStorageContext -StorageAccountName $acct -StorageAccountKey $key -Protocol Https
$container = Get-AzStorageContainer -Name $ContainerName -Context $ctx

$container.CloudBlobContainer.Uri.AbsoluteUri

if ($container) {
#use Set-AzStorageBlobContent to upload file
    $filesToUpload = Get-Childitem -Path $localfilepath -Filter "*.csv" 

        ForEach ($x in $filesToUpload) { 
            $targetPath = $containerdirectory+($x.fullname.Substring($localfilepath.Length)).Replace("\", "/")

            Write-Verbose "Uploading $("\" + $x.fullname.Substring($localfilepath.Length)) to $($container.CloudBlobContainer.Uri.AbsoluteUri + "/" + $targetPath)"
            Set-AzStorageBlobContent -File $x.fullname -Container $container.Name -Blob $targetPath -Context $ctx -Force:$Force | Out-Null
            }
        }

Load excel files into a table on SQL server database

$File = "\\NFS01\SQLBackup\DATA_SQL.xlsx"
$Instance = "SQLSERVER01"
$Database = "dbname01"

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($File)

foreach($sheet in Get-ExcelSheetInfo $File)
{

$data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable
$tablename = "table_name"

Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -Truncate -Table $tablename

}

Create Windows Failover Cluster and SQL AlwaysOn availablity group using dbatools

This post is a condensed version of the original post by Andreas Jordan. Prerequisites were taken from sqlservercentral. They have compiled a nice and extensive list of them.

With SQL Server 2012, Microsoft introduced the AlwaysOn Availability Group feature, and since then many changes and improvements have been made. This article will cover the prerequisites and steps to install AlwaysOn in a SQL 2019 environment.

Prerequisites

Windows

  • Do not install AlwaysOn on a domain controller
  • The operating system must be Windows 2012 or later
  • Install all available Windows hotfixes on every server including replicas
  • Windows Server Failure Cluster must be installed on every server (I will include powershell code to install it using dbatools module)

SQL Server

  • Each server must be a node in the WFSC
  • No replica can run Active Directory services
  • Each replica must run on comparable hardware that can handle identical workloads
  • Each instance must run the same version of SQL server, and have the same SQL server collation
  • The account that runs SQL Services should be a domain account

Network

  • It is recommended to use the same network links for communication between WFSC nodes and AlwaysOn replicas

Databases in the AG

  • user databases (you cannot replicate system databases)
  • read/write
  • multi-user
  • AUTO_CLOSE disabled
  • databases should be to set full recovery mode
  • not configured for database mirroring

Step 1: Add Windows Failover Cluster to each server

Define the variables

#Define the variables for SQLCluster
$DomainName = 'DOMAIN'
$DomainController = 'DC1.com'
$Networkshare = 'FS01.com'
$ClusterNodes = 'SQLSERVER01.com', 'SQLSERVER02.com','SQLSERVER03.com'
$ClusterName = 'SQLSERVERCL01'
$ClusterIP = '10.0.0.28'

Below code will install the failover cluster feature on all the nodes (servers/replicas whatever you want to call them)

#Install Failover cluster on all the nodes
Invoke-Command -ComputerName $ClusterNodes -ScriptBlock { Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools } | Format-Table

Move the servers to a fixed OU in Active directory (I have had to create an OU named SQL Always ON for this purpose)

Move-ADObject -Identity 'CN=SQLSERVER01,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 
Move-ADObject -Identity 'CN=SQLSERVER02,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 
Move-ADObject -Identity 'CN=SQLSERVER03,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local'

Next up is create the windows cluster, which is the basis for SQL AlwaysOn

#We do not have shared storage for the quorum in the environment
#Therefore, we will create the network share on the domain controller and authorize the computer account of the failover cluster we just created
Invoke-Command -ComputerName $DomainController -ScriptBlock { 
	New-Item -Path "C:\WindowsClusterQuorum_$using:ClusterName" -ItemType Directory | Out-Null
	New-SmbShare -Path "C:\WindowsClusterQuorum_$using:ClusterName" -Name "WindowsClusterQuorum_$using:ClusterName" | Out-Null
	Grant-SmbShareAccess -Name "WindowsClusterQuorum_$using:ClusterName" -AccountName "$using:DomainName\$using:ClusterName$" -AccessRight Full -Force | Out-Null
}
$Cluster | Set-ClusterQuorum -NodeAndFileShareMajority "\\$DomainController\WindowsClusterQuorum_$ClusterName" | Format-List

If you have reached this point, then you have completed the windows server level prerequisites.

Next step is to create the availability group. Before, we do that, we need to enable the AlwaysOn on SQL instances using the below code. Don’t you love not having to RDP into a machine and do all this manually!

#Define the variable for AlwaysOn group
$SQLServerServiceAccount = 'svc-sql-serviceaccount'
$Password = 'SecurePassword'
$BackupPath = '\FS01.com.local\SQLBackup\Temp'
$DatabaseName = 'DB01'
$AvailabilityGroupName = 'SQLAG01'
$AvailabilityGroupIP = '10.0.0.19' 

#Enable Always On on SQL instances, typically done via SQL Server Configuration Manager
Enable-DbaAgHadr -SqlInstance $SqlInstances -Force | Format-Table

Next, setup the endpoints

#Setup of the endpoints
New-DbaEndpoint -SqlInstance $SqlInstances -Name hadr_endpoint -Port 5022 | Start-DbaEndpoint | Format-Table
New-DbaLogin -SqlInstance $SqlInstances -Login "$DomainName\$SQLServerServiceAccount" | Format-Table
Invoke-DbaQuery -SqlInstance $SqlInstances -Query "GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [$DomainName\$SQLServerServiceAccount]"

Finally, we need to backup the database on primary and restore them onto each replica with norecovery in order to be able to join them to an availability group.

#Transfer databases to replica
$Database = Get-DbaDatabase -SqlInstance $SqlInstances[0] -Database $DatabaseName
$Database | Backup-DbaDatabase -Path $BackupPath -Type Database | Restore-DbaDatabase -SqlInstance $SqlInstances[1] -NoRecovery | Out-Null
$Database | Backup-DbaDatabase -Path $BackupPath -Type Log | Restore-DbaDatabase -SqlInstance $SqlInstances[1] -Continue -NoRecovery | Out-Null

Finally, create the availability group and resume the data movement

#Create the availability group
$AvailabilityGroup = New-DbaAvailabilityGroup `
		-Name $AvailabilityGroupName `
		-Database $DatabaseName `
    	-ClusterType Wsfc `
    	-Primary $SqlInstances[0] `
    	-Secondary $SqlInstances[1] `
    	-SeedingMode Automatic `
        -IPAddress $AvailabilityGroupIP `
    	-Confirm:$false
$AvailabilityGroup | Format-List

Get-DbaAgReplica -SqlInstance $SqlInstances[0] -AvailabilityGroup $AvailabilityGroupName | Format-Table
Get-DbaAgDatabase -SqlInstance $SqlInstances -AvailabilityGroup $AvailabilityGroupName -Database $DatabaseName | Format-Table

Here is the complete code in one piece for your ease of copying it for implementation on your own environment. You will have to replicate the last piece of code in order to other databases. I have yet to experiment if I can pass multiple databases as parameters to backup-dbadatabase and join them to the AG.

#Define the variables for Windows SQLCluster
$DomainName = 'DOMAIN'
$DomainController = 'DC1.com'
$Networkshare = 'FS01.com'
$ClusterNodes = 'SQLSERVER01.com', 'SQLSERVER02.com','SQLSERVER03.com'
$ClusterName = 'SQLSERVERCL01'
$ClusterIP = '10.0.0.28' 

#Install Failover cluster on all the nodes
Invoke-Command -ComputerName $ClusterNodes -ScriptBlock { Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools } | Format-Table

#Move the servers to a fixed OU in Active directory
Move-ADObject -Identity 'CN=SQLSERVER01,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 
Move-ADObject -Identity 'CN=SQLSERVER02,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 
Move-ADObject -Identity 'CN=SQLSERVER03,CN=Computers,DC=com,DC=local' -TargetPath 'OU=SQL Always ON,OU=Servers,DC=com,DC=local' 

#We do not have shared storage for the quorum in the environment
#Therefore, we will create the network share on the domain controller and authorize the computer account of the failover cluster we just created
Invoke-Command -ComputerName $DomainController -ScriptBlock { 
	New-Item -Path "C:\WindowsClusterQuorum_$using:ClusterName" -ItemType Directory | Out-Null
	New-SmbShare -Path "C:\WindowsClusterQuorum_$using:ClusterName" -Name "WindowsClusterQuorum_$using:ClusterName" | Out-Null
	Grant-SmbShareAccess -Name "WindowsClusterQuorum_$using:ClusterName" -AccountName "$using:DomainName\$using:ClusterName$" -AccessRight Full -Force | Out-Null
}
$Cluster | Set-ClusterQuorum -NodeAndFileShareMajority "\\$DomainController\WindowsClusterQuorum_$ClusterName" | Format-List 


#Define the variable for AlwaysOn group
$SQLServerServiceAccount = 'svc-sql-serviceaccount'
$Password = 'SecurePassword'
$BackupPath = '\FS01.com.local\Temp'
$DatabaseName = 'DB01'
$AvailabilityGroupName = 'SQLAG01'
$AvailabilityGroupIP = '10.0.0.19' 

#Enable Always On on SQL instances, typically done via SQL Server Configuration Manager
Enable-DbaAgHadr -SqlInstance $SqlInstances -Force | Format-Table 

#Setup of the endpoints
New-DbaEndpoint -SqlInstance $SqlInstances -Name hadr_endpoint -Port 5022 | Start-DbaEndpoint | Format-Table
New-DbaLogin -SqlInstance $SqlInstances -Login "$DomainName\$SQLServerServiceAccount" | Format-Table
Invoke-DbaQuery -SqlInstance $SqlInstances -Query "GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [$DomainName\$SQLServerServiceAccount]"

#Transfer databases to replica
$Database = Get-DbaDatabase -SqlInstance $SqlInstances[0] -Database $DatabaseName
$Database | Backup-DbaDatabase -Path $BackupPath -Type Database | Restore-DbaDatabase -SqlInstance $SqlInstances[1] -NoRecovery | Out-Null
$Database | Backup-DbaDatabase -Path $BackupPath -Type Log | Restore-DbaDatabase -SqlInstance $SqlInstances[1] -Continue -NoRecovery | Out-Null

#Create the availability group
$AvailabilityGroup = New-DbaAvailabilityGroup `
		-Name $AvailabilityGroupName `
		-Database $DatabaseName `
    	-ClusterType Wsfc `
    	-Primary $SqlInstances[0] `
    	-Secondary $SqlInstances[1] `
    	-SeedingMode Automatic `
        -IPAddress $AvailabilityGroupIP `
    	-Confirm:$false
$AvailabilityGroup | Format-List

Get-DbaAgReplica -SqlInstance $SqlInstances[0] -AvailabilityGroup $AvailabilityGroupName | Format-Table
Get-DbaAgDatabase -SqlInstance $SqlInstances -AvailabilityGroup $AvailabilityGroupName -Database $DatabaseName | Format-Table

Powershell – Remoting over HTTP

By default, remoting will use WMI for communications using ports 5985 and 5986

If for some reason, you cant use WMI, say server is in DMZ. You can specify to use HTTP for remoting instead of WMI, which will use ports 80 and 443.

To use these listeners, WinRM needs to be configured to listen on ports 80 and 443 using the WinRM command or group policy. Once enabled, remoting works as normal.

How do you turn ON remoting in older machines (older than windows server 2012)?

Remoting is enabled by default on Windows 2012 and above. Older versions of windows need to have remoting enabled on the server.

On the machine which will receive commands, run Enable-PSRemoting

One could also push out the enable switch via GPO, this method can be a quick and easy way to enable remoting on all servers within the Active Directory domain.

Copy members from one AD Group to another, get members from an AD group

Powershell is powerful and versatile to perform numerous operations. It has been recently useful for me when i needed to migrate users from one AD group to another. Below is a one line statement to do just that

Add-ADGroupMember -Identity 'New Group' -Members (Get-ADGroupMember -Identity 'Old Group' -Recursive)

The -Recursive switch is used in case the old group contains nested groups.

What if you need to get all the users contained in an AD group?

There is a one line powershell statement for that too.

Get-ADGroupMember -Identity 'SQLADGroup' -Recursive | select name | Sort-Object -Property name

Sort-Object will take the previous output and sorts the name column in ascending order.

%d bloggers like this: