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
You should get a command prompt similar to the one below.
# run this command
Install-Module dbatoolsStep 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.
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
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
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
ALTER DATABASE [tempdb] REMOVE FILE [tempdev3]
ALTER DATABASE [tempdb] REMOVE FILE [tempdev4]
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
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
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 126.96.36.199 -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.
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!