Scripting features - SQL Server to Aurora PostgreSQL Migration Playbook
SQL Server UsagePostgreSQL Usage

Scripting features

This topic provides reference information comparing the scripting and automation capabilities of Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. It highlights the differences in tool sets and scripting languages between these two database systems. The topic explains that SQL Server supports T-SQL and XQuery scripting within various frameworks, while Aurora PostgreSQL, as a Platform as a Service, offers different approaches for database administration and scripting.

Feature compatibility AWS SCT / AWS DMS automation level AWS SCT action code index Key differences

No feature compatibility

N/A

N/A

Non-compatible tool sets and scripting languages. Use PostgreSQL pgAdmin, Amazon RDS API, AWS Management Console, and AWS CLI.

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, Database Engine Scripting, and sqlcmd Utility in the SQL Server documentation.

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 AWS Management Console, the AWS Command Line Interface (CLI), and the Amazon RDS Programmatic API as described in the following sections.

AWS Management Console

The AWS Management Console is a simple web-based set of tools for interactive management of Aurora PostgreSQL and other Amazon RDS services. To access the AWS Management Console, sign in to your AWS account, and choose RDS.

AWS Command Line Interface

The AWS 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 AWS CLI is built on top of the AWS SDK for Python (Boto), which provides commands for interacting with AWS services. With minimal configuration, you can start using all AWS 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 AWS Tools for Windows PowerShell and AWS Tools for PowerShell Core are PowerShell modules built on the functionality exposed by the AWS SDK for .NET. These Tools enable scripting operations for AWS 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, Data Types, Common Parameters, and Common Errors in the Amazon Relational Database Service API Reference.

Examples

The following walkthrough describes how to connect to an Aurora PostgreSQL database instance using the PostgreSQL utility.

  1. Sign in to your AWS account, choose RDS, and then choose Databases.

  2. 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.

  3. 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 and Amazon Relational Database Service API Reference.