Scripting
| Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
|---|---|---|---|
|
|
N/A |
N/A |
Non-compatible tool sets and scripting languages. Use PostgreSQL |
SQL Server Usage
SQL Server supports T-SQL and XQuery scripting within multiple run frameworks such as SQL Server Agent, and stored procedures.
The SQLCMD command line utility can also be used to run T-SQL scripts. However, the most extensive and feature-rich scripting environment is PowerShell.
SQL Server provides two PowerShell snap-ins that implement a provider exposing the entire SQL Server Management Object Model (SMO) as PowerShell paths. Additionally, you can use cmd in SQL Server to run specific SQL Server commands.
Note
You can use Invoke-Sqlcmd to run scripts using the SQLCMD utility.
The sqlps utility launches the PowerShell scripting environment and automatically loads the SQL Server modules. You can launch sqlps from a command prompt or from the Object Explorer pane of SQL Server Management Studio. You can run one-time PowerShell commands and script files (for example, .\SomeFolder\SomeScript.ps1).
Note
SQL Server Agent supports running PowerShell scripts in job steps. For more information, see SQL Server Agent.
SQL Server also supports three types of direct database engine queries: T-SQL, XQuery, and the SQLCMD utility. You can call T-SQL and XQuery from stored procedures, SQL Server Management Studio (or other IDE), and SQL Server agent jobs. The SQLCMD utility also supports commands and variables.
Examples
Backup a database with PowerShell using the default backup options.
PS C:\> Backup-SqlDatabase -ServerInstance "MyServer\SQLServerInstance" -Database "MyDB"
Get all rows from the MyTable table in the MyDB database.
PS C:\> Read-SqlTableData -ServerInstance MyServer\SQLServerInstance" -DatabaseName "MyDB" -TableName "MyTable"
For more information, see SQL Server PowerShell
PostgreSQL Usage
As a Platform as a Service (PaaS), Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) accepts connections from any compatible client, but you can’t access the PostgreSQL command line utility typically used for database administration. However, you can use PostgreSQL tools installed on a network host and the Amazon Relational Database Service (Amazon RDS) API. The most common tools for Aurora PostgreSQL scripting and automation include PostgreSQL pgAdmin, PostgreSQL utilities, and the Amazon RDS API. The following sections describe each tool.
PostgreSQL pgAdmin
PostgreSQL pgAdmin is the most commonly used tool for development and administration of PostgreSQL servers. It is available as a free Community Edition and paid support is available.
The PostgreSQL pgAdmin also supports a Python scripting shell that you can use interactively and programmatically. For more information see: pgAdmin
Amazon RDS API
The Amazon RDS API is a web service for managing and maintaining Aurora PostgreSQL and other relational databases. You can use Amazon RDS API to setup, operate, scale, backup, and perform many common administration tasks. The Amazon RDS API supports multiple database platforms and can integrate administration seamlessly for heterogeneous environments.
Note
The Amazon RDS API is asynchronous. Some interfaces may require polling or callback functions to receive command status and results.
You can access Amazon RDS using the Amazon Management Console, the Amazon Command Line Interface (CLI), and the Amazon RDS Programmatic API as described in the following sections.
Amazon Management Console
The Amazon Management Console is a simple web-based set of tools for interactive management of Aurora PostgreSQL and other Amazon RDS services. To access the Amazon Management Console, sign in to your Amazon account, and choose RDS.
Amazon Command Line Interface
The Amazon Command Line Interface is an open source tool that runs on Linux, Windows, or macOS having Python 2 version 2.6.5 and higher or Python 3 version 3.3 and higher.
The Amazon CLI is built on top of the Amazon SDK for Python (Boto), which provides commands for interacting with Amazon services. With minimal configuration, you can start using all Amazon Management Console functionality from your favorite terminal application.
-
Linux shells — Use common shell programs such as Bash, Zsh, or tsch.
-
Windows command line — Run commands in PowerShell or the Windows Command Processor.
-
Remotely — Run commands on Amazon EC2 instances through a remote terminal such as PuTTY or SSH.
The Amazon Tools for Windows PowerShell and Amazon Tools for PowerShell Core are PowerShell modules built on the functionality exposed by the Amazon SDK for .NET. These Tools enable scripting operations for Amazon resources using the PowerShell command line.
Note
You can’t use SQL Server cmdlets in PowerShell.
Amazon RDS Programmatic API
You can use the Amazon RDS API to automate management of database instances and other Amazon RDS objects.
For more information, see Actions
Examples
The following walkthrough describes how to connect to an Aurora PostgreSQL database instance using the PostgreSQL utility.
-
Sign in to your Amazon account, choose RDS, and then choose Databases.
-
Choose the PostgreSQL database you want to connect to and copy the cluster endpoint address.
Note
You can also connect to individual database instances. For more information, see High Availability Essentials.
-
In the command shell, enter the following:
psql --host=mypostgresql.c6c8mwvfdgv0.us-west-2.rds.amazonaws.com --port=5432 --username=awsuser --password --dbname=mypgdb
In the preceding example, the --host parameter is the endpoint DNS name of the Aurora PostgreSQL database cluster.
In the preceding example, the --port parameter is the port number.
For more information, see Command Line Interface Command Reference