PowerShell is a powerful scripting tool often used by database administrators for managing Microsoft SQL Server. This blog will focus on the aspects of using PowerShell for common database tasks and management on a Cloud SQL for SQL Server instance. We will also look at dbatools.io and how this can be used on instances with cross-region replicas, external replication, and other key features enabled.
From our partners:
Initial setup and getting started
You can install PowerShell on a Compute Engine VM, just as you can install SQL Server Management Studio on a VM for managing a Cloud SQL instance. PowerShell is installed by default and requires no setup on any Windows Compute Engine VM that you create, and you can also install it in a Compute Engine Linux VM. The 7 steps below are needed to get the PowerShell environment set up on a Compute Engine Linux VM with dbatools.io
- Create a VM
- Connect to the VM
- Install PowerShell
- Launch PowerShell
- Verify PowerShell setup
- Install dbatools.io
- Verify dbatools.io setup
Step 1. Create a VM

Step 4. Launch PowerShell
Now start PowerShell using the command below
# Start PowerShell
pwsh
You should get a command prompt similar to the one below.


# run this command
Install-Module dbatools
Step 6. Verify dbatools.io setupIn these examples I will be using SQL Server authentication to connect to each database. To do this, we need to create a PowerShell credential so that we can authenticate to the database server.
$sqlserver = Get-Credential -UserName "sqlserver"

Get-DbaDatabase -SqlInstance 10.59.176.56 -SqlCredential $sqlserver -ExcludeSystem

- Viewing the number of TempDB files.
- Adding/removing more files to TempDB after instance resize.
Updating TempDB
There are certain best practices for TempDB to achieve optimal performance. One of the main recommendations is having an equal number of files for TempDB (up to 8) matching the number of cores available. You can easily review and manage TempDB configurations using powershell.
Viewing the number of TempDB files
To review your TempDB files for your Cloud SQL instance, use the Get-DbaDbFile cmdlet like the example below.
Get-DbaDbFile -SqlInstance 10.59.176.208 -SqlCredential $sqlserver -Database tempdb |Format-Table -Property FileGroupName, LogicalName, Size, Growth, GrowthType

Adding/removing more files to TempDB after instance resize
If additional files are needed (for example, after resizing your Cloud SQL instance), you can add more files using the Set-DbaTempDbConfig
command shown below. You may also need to add TempDB files based on contention observed in TempDB.
In this example, we have resized the instance to have 6 vCPUs, so we need to add four more TempDB data files to have 6 data files in total. This step can also be done outside of PowerShell as documented here as well.

Set-DbaTempDbConfig -SqlInstance 10.59.176.208 -SqlCredential $sqlserver -DataFileSize 48 -DataFileCount 6

USE [tempdb]
GO
exec msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = 'tempdev2', @empty_file = 1
exec msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = 'tempdev3', @empty_file = 1
exec msdb.dbo.gcloudsql_tempdb_shrinkfile @filename = 'tempdev4', @empty_file = 1
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
ALTER DATABASE [tempdb] REMOVE FILE [tempdev3]
ALTER DATABASE [tempdb] REMOVE FILE [tempdev4]
GO


Review DB wait statistics
If you are experiencing performance issues or want to see what your Cloud SQL instance is waiting on, you can use the Get-DbaWaitStatistic
cmdlet and check wait stats with a single command.
Get-DbaWaitStatistic -SqlInstance 10.59.176.56 -SqlCredential $sqlserver

Sync objects between replicas
If you are using a Cloud SQL for SQL Server read replica or Cloud SQL as a publisher for transactional replication, there are a few tasks that you should continue to perform, like keeping the SQL agent jobs in sync between instances. In this example, use the steps in Cloud SQL documentation to create a read replica. At the initial creation, objects are in sync on both the primary and secondary. We need to make sure to sync objects created after the replica is set up.
SQL Agent Jobs
Let’s create a sample job on the primary instance that we will later sync to the replica instance.
You can use the New-DbaAgentJob cmdlet as below
New-DbaAgentJob -SqlInstance $primary -Job 'test-job' -Description 'sample job' -SqlCredential $sqlserver

New-DbaAgentJobStep -SqlInstance $primary -Job test-job -StepName get-date -Command 'select getdate()' -SqlCredential $sqlserver

Copy-DbaAgentJob -Source c -SourceSqlCredential $sqlserver -Destination $secondary -DestinationSqlCredential $sqlserver
Get-DbaAgentJob -SqlInstance $secondary

- Create a second sql agent job called second-job
- Add a second job step called second-step to the job named test-job
We will review then sync these changes above to the secondary server in these next steps.
Create a new job
New-DbaAgentJob -SqlInstance $primary -Job 'second-job' -Description 'second job' -SqlCredential $sqlserver
New-DbaAgentJobStep -SqlInstance $primary -Job second-job -StepName get-date -Command 'select @@servername' -SqlCredential
New-DbaAgentJobStep -SqlInstance $primary -Job test-job -StepName second-step -Command 'select current_time' -SqlCredential $sqlserver
Get-DbaAgentJobStep -SqlInstance $primary -SqlCredential $sqlserver | format-table

Copy-DbaAgentJob -Source $primary -SourceSqlCredential $sqlserver -Destination $secondary -DestinationSqlCredential $sqlserver -Force

Importing data
You can also use powershell to import data (for example, a CSV file). You can choose your own CSV file or create a sample one using docs.google.com/spreadsheets/ Here is one with a small sample that I created

cat ./import/States.csv
id,name,capitol
1,washington,olympia
2,oregon,Salem
3,california,Sacramento
4,montana,Helena
5,idaho,BoiseUse Import-DbaCsv to import this file to your Cloud SQL instance as shown below. This can also be used as an alternative to BULK INSERT.
Import-DbaCsv -Path ./import/States.csv -SqlInstance 35.232.7.183 -Database test -Table States -SqlCredential $sqlserver

Get-DbaDbTable -SqlInstance $primary -Database test -Table States -SqlCredential $sqlserver

Copy-DbaDbTableData -SqlInstance $primary -SqlCredential $sqlserver -Destination $replica -DestinationSqlCredential $sqlserver -Database test -DestinationDatabase newtest -Table dbo.States -AutoCreateTable

Performing common DBA tasks
There are certain tasks that a DBA/DBE may need to perform to keep their Cloud SQL for SQL Server instance healthy, many of which can be done using PowerShell.
Unused indexes and Duplicate indexes
In many cases, having indexes improves the performance of selects, but they also cause some overhead to inserts and updates. It is normally recommended to review unused indexes and duplicated indexes. The two cmdlets listed below can be used to do this.
Find-DbaDbDuplicateIndex
Find-DbaDbUnusedIndex
Diagnostic queries on Cloud SQL
There is a common set of diagnostic queries provided by SQL Server MVP Glen Berry here.
We can use Invoke-DbaDiagnosticQuery to automatically execute and return the results for a specific set of queries or all these queries. There are a lot of queries and information that this returns so it could take a while. It might be a good idea to limit this to specific queries or target certain databases.
Here is an example of what a partial output looks like.
Invoke-DbaDiagnosticQuery -SqlInstance $primary -SqlCredential $sqlserver

Invoke-DbaDiagnosticQuery -SqlInstance $primary -SqlCredential $sqlserver -QueryNam 'AG Status' | Select -ExpandProperty result | Format-Table -AutoSize

Invoke-DbaDiagnosticQuery -SqlInstance $primary -SqlCredential $sqlserver -QueryNam 'Last Backup By Database' | Select -ExpandProperty result | Format-Table -AutoSize

By: Bryan Hamilton (Database Engineer, Cloud SQL)
Source: Google Cloud Blog
For enquiries, product placements, sponsorships, and collaborations, connect with us at [email protected]. We'd love to hear from you!
Our humans need coffee too! Your support is highly appreciated, thank you!