# Migrating management features to Aurora PostgreSQL Management This topic provides conceptual content comparing various aspects of Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL in the context of database migration. It covers key differences in features and functionalities such as task scheduling, alerting, email capabilities, ETL processes, data export/import, logging, maintenance tasks, monitoring, resource management, linked servers, and scripting. By understanding these differences, you can better plan and execute your migration strategy from SQL Server to Aurora PostgreSQL. The content highlights alternative solutions and workarounds available in the AWS ecosystem to replicate or replace SQL Server-specific features, helping you anticipate challenges and adapt your database management practices. This comprehensive comparison enables database administrators and developers to make informed decisions and smooth their transition when migrating from SQL Server to Aurora PostgreSQL. **Topics** + [ # SQL Server Agent and PostgreSQL ](chap-sql-server-aurora-pg.management.scheduledlambda.md) + [ # Alerting features ](chap-sql-server-aurora-pg.management.alerting.md) + [ # Database mail features ](chap-sql-server-aurora-pg.management.databasemail.md) + [ # ETL features ](chap-sql-server-aurora-pg.management.etl.md) + [ # Export and import features ](chap-sql-server-aurora-pg.management.exportimport.md) + [ # Viewing server logs ](chap-sql-server-aurora-pg.management.serverlogs.md) + [ # Maintenance plans ](chap-sql-server-aurora-pg.management.maintenanceplans.md) + [ # Monitoring features ](chap-sql-server-aurora-pg.management.monitoring.md) + [ # Resource governor features ](chap-sql-server-aurora-pg.management.resourcegovernor.md) + [ # Linked servers ](chap-sql-server-aurora-pg.management.linkedservers.md) + [ # Scripting features ](chap-sql-server-aurora-pg.management.scripting.md) # SQL Server Agent and PostgreSQL This topic provides reference information about the differences between SQL Server Agent and PostgreSQL in the context of migrating from Microsoft SQL Server 2019 to Amazon Aurora PostgreSQL. You can understand the key functions of SQL Server Agent, including scheduling automated maintenance jobs and alerting, and how these features are utilized in SQL Server. ## SQL Server Usage SQL Server Agent provides two main functions: scheduling automated maintenance jobs and alerting. **Note** Other SQL Server built-in frameworks such as replication, also use SQL Server Agent jobs. For more information, see [Maintenance Plans](chap-sql-server-aurora-pg.management.maintenanceplans.md) and [Alerting](chap-sql-server-aurora-pg.management.alerting.md). ## PostgreSQL Usage Currently, there is no equivalent in Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) for scheduling tasks but you can create scheduled AWS Lambda that will run a stored procedure. Find an example in [Database Mail](chap-sql-server-aurora-pg.management.databasemail.md). # Alerting features This topic provides reference information about alert and notification systems in SQL Server and Amazon Aurora PostgreSQL. You can understand how SQL Server Agent generates alerts for various events and performance conditions, and how it allows for customized responses. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[One star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-1.png) | N/A | N/A | Use [Amazon RDS event notification](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Events.html) with [Amazon Simple Notification Service](https://aws.amazon.com/sns/). | ## SQL Server Usage SQL Server provides SQL Server Agent to generate alerts. When running, SQL Server Agent constantly monitors SQL Server windows application log messages, performance counters, and Windows Management Instrumentation (WMI) objects. When a new error event is detected, the agent checks the `msdb` database for configured alerts and runs the specified action. You can define SQL Server Agent alerts for the following categories: + SQL Server events. + SQL Server performance conditions. + WMI events. For SQL Server events, the alert options include the following settings: + **Error Number** — Alert when a specific error is logged. + **Severity Level** — Alert when any error in the specified severity level is logged. + **Database** — Filter the database list for which the event will generate an alert. + **Event Text** — Filter specific text in the event message. **Note** SQL Server Agent is pre-configured with several high severity alerts. It is highly recommended to turn on these alerts. To generate an alert in response to a specific performance condition, specify the performance counter to be monitored, the threshold values for the alert, and the predicate for the alert to occur. The following list identifies the performance alert settings: + **Object** — The Performance counter category or the monitoring area of performance. + **Counter** — A counter is a specific attribute value of the object. + **Instance** — Filter by SQL Server instance (multiple instances can share logs). + **Alert if counter and Value** — The threshold for the alert and the predicate. The threshold is a number. Predicates are **falls below**, **becomes equal to**, or **rises above** the threshold. WMI events require the WMI namespace and the WMI Query Language (WQL) query for specific events. You can assign alerts to specific operators with schedule limitations and multiple response types including: + Run an SQL Server Agent job. + Send Email, Net Send command, or a pager notification. You can configure alerts and responses with SQL Server Management Studio or system stored procedures. ### Example Configure an alert for all errors with severity 20. ``` EXEC msdb.dbo.sp_add_alert @name = N'Severity 20 Error Alert', @severity = 20, @notification_message = N'A severity 20 Error has occurred. Initiating emergency procedure', @job_name = N'Error 20 emergency response'; ``` For more information, see [Alerts](https://docs.microsoft.com/en-us/sql/ssms/agent/alerts?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) doesn’t support direct configuration of engine alerts. Use the Event Notifications Infrastructure to collect history logs or receive event notifications in near real-time. Amazon Relational Database Service (Amazon RDS) uses Amazon Simple Notification Service (Amazon SNS) to provide notifications for events. SNS can send notifications in any form supported by the region including email, text messages, or calls to HTTP endpoints for response automation. Events are grouped into categories. You can only subscribe to event categories, not individual events. SNS sends notifications when any event in a category occurs. You can subscribe to alerts for database instances, database clusters, database snapshots, database cluster snapshots, database security groups, and database parameter groups. For example, a subscription to the Backup category for a specific database instance sends notifications when backup related events occur on that instance. A subscription to a Configuration Change category for a database security group sends notifications when the security group changes. **Note** For Amazon Aurora, some events occur at the cluster rather than instance level. You will not receive those events if you subscribe to an Amazon Aurora DB instance. SNS sends event notifications to the address specified when the subscription was created. Typically, administrators create several subscriptions. For example, one subscription to receive logging events and another to receive only critical events for a production environment requiring immediate responses. You can disable notifications without deleting a subscription by setting the Enabled radio button to No in the Amazon RDS console. Alternatively, use the Command Line Interface (CLI) or Amazon RDS API to change the Enabled setting. Subscriptions are identified by the Amazon Resource Name (ARN) of an Amazon SNS topic. The Amazon RDS console creates ARNs when subscriptions are created. When using the CLI or API, you must create the ARN using the Amazon SNS console or the Amazon SNS API. ### Examples The following walkthrough demonstrates how to create an event notification subscription. 1. Sign in to your AWS account, and choose **RDS**. 1. Choose **Events** on the left navigation pane. This screen that presents relevant Amazon RDS events occurs. 1. Choose **Event subscriptions** and then choose **Create event subscription**. 1. Enter the **Name of the subscription** and select a **Target of ARN** or **Email**. For email subscriptions, enter values for **Topic** name and **With these recipients**. 1. Select the event source, choose specific event categories to be monitored, and choose **Create**. 1. On the Amazon RDS dashboard, choose **Recent events**. For more information, see [Using Amazon RDS event notification](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Events.html) in the *Amazon Relational Database Service User Guide*. ### Raising Errors from Within the Database The following table shows the PostgreSQL log severity levels. | Log type | Information written to log | | --- | --- | | `DEBUG1…DEBUG5` | Provides successively-more-detailed information for use by developers. | | `INFO` | Provides information implicitly requested by the user. | | `NOTICE` | Provides information that might be helpful to users. | | `WARNING` | Provides warnings of likely problems. | | `ERROR` | Reports the error that caused the current command to abort. | | `LOG` | Reports information of interest to administrators. | | `FATAL` | Reports the error that caused the current session to abort. | | `PANIC` | Reports the error that caused all database sessions to abort. | Several parameters control how and where PostgreSQL log and errors files are placed: | Parameter | Description | | --- | --- | | `log_filename` | Sets the file name pattern for log files. To modify, use an Aurora Database Parameter Group. | | `log_rotation_age` | (min) Automatic log file rotation will occur after N minutes. To modify, use an Aurora Database Parameter Group. | | `log_rotation_size` | (kB) Automatic log file rotation will occur after N kilobytes. To modify, use an Aurora Database Parameter Group. | | `log_min_messages` | Sets the message levels that are logged, such as `DEBUG`, `ERROR`, `INFO`, and so on. To modify, use an Aurora Database Parameter Group. | | `log_min_error_statement` | Causes all statements generating errors at or above this level to be logged, such as `DEBUG`, `ERROR`, `INFO`, and so on. To modify, use an Aurora Database Parameter Group. | | `log_min_duration_statement` | Sets the minimum run time above which statements will be logged (ms). To modify, use an Aurora Database Parameter Group. | **Note** Modifications to certain parameters such as `log_directory` (which sets the destination directory for log files) or `logging_collector` (which starts a subprocess to capture the `stderr` output or `csvlogs` into log files) are disabled for an Aurora PostgreSQL instance. For more information, see [Error Reporting and Logging](https://www.postgresql.org/docs/13/runtime-config-logging.html) in the *PostgreSQL documentation*. # Database mail features This topic provides reference information about email capabilities in Microsoft SQL Server and their counterparts in Amazon Aurora PostgreSQL. You can understand the differences in email functionality between these two database systems and learn about alternative solutions for sending emails from Aurora PostgreSQL. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[One star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-1.png) | N/A | [SQL Server Mail](chap-sql-server-aurora-pg.tools.actioncode.md#chap-sql-server-aurora-pg.tools.actioncode.mail) | Use Lambda integration. | ## SQL Server Usage The Database Mail framework is an email client solution for sending messages directly from SQL Server. Email capabilities and APIs within the database server provide easy management of the following messages: + Server administration messages such as alerts, logs, status reports, and process confirmations. + Application messages such as user registration confirmation and action verifications. **Note** Database Mail is turned off by default. The main features of the Database Mail framework are: + Database Mail sends messages using the standard and secure Simple Mail Transfer Protocol (SMTP) . + The email client engine runs asynchronously and sends messages in a separate process to minimize dependencies. + Database Mail supports multiple SMTP Servers for redundancy. + Full support and awareness of Windows Server Failover Cluster for high availability environments. + Multi-profile support with multiple failover accounts in each profile. + Enhanced security management with separate roles in the `msdb` database. + Security is enforced for mail profiles. + Administrators can monitor and cap attachment sizes. + You can add attachment file types to a deny list. + You can log Email activity to SQL Server, the Windows application event log, and a set of system tables in the `msdb` database. + Supports full auditing capabilities with configurable retention policies. + Supports both plain text and HTML messages. ### Architecture Database Mail is built on top of the Microsoft SQL Server Service Broker queue management framework. The system stored procedure `sp_send_dbmail` sends email messages. When you run this stored procedure, it inserts a row to the mail queue and records the Email message. The queue insert operation triggers the run of the Database Mail process (`DatabaseMail.exe`). The Database Mail process then reads the Email information and sends the message to the SMTP servers. When the SMTP servers acknowledge or reject the message, the Database Mail process inserts a status row into the status queue, including the result of the send attempt. This insert operation triggers the run of a system stored procedure that updates the status of the Email message send attempt. Database Mail records all Email attachments in the system tables. SQL Server provides a set of system views and stored procedures for troubleshooting and administration of the Database Mail queue. ### Deprecated SQL Mail framework The previous SQL Mail framework using `xp_sendmail` has been deprecated as of SQL Server 2008R2. For more information, see [Deprecated Database Engine Features in SQL Server 2008 R2](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms143729(v=sql.105)) in the *SQL Server documentation*. The legacy mail system has been completely replaced by the greatly enhanced DB mail framework described here. The previous system has been out of use for many years because it was prone to synchronous run issues and windows mail profile quirks. ### Syntax ``` EXECUTE sp_send_dbmail [[,@profile_name =] ''] [,[,@recipients =] ''] [,[,@copy_recipients =] ''] [,[,@blind_copy_recipients =] ''] [,[,@from_address =] ''] [,[,@reply_to =] ''] [,[,@subject =] ''] [,[,@body =] ''] [,[,@body_format =] ''] [,[,@importance =] ''] [,[,@sensitivity =] ''] [,[,@file_attachments =] ''] [,[,@query =] ''] [,[,@execute_query_database =] ''] [,[,@attach_query_result_as_file =] ] [,[,@query_attachment_filename =] ] [,[,@query_result_header =] ] [,[,@query_result_width =] ] [,[,@query_result_separator =] ''] [,[,@exclude_query_output =] ] [,[,@append_query_error =] ] [,[,@query_no_truncate =] ] [,[,@query_result_no_padding =] @] [,[,@mailitem_id =] ] [,OUTPUT] ``` ### Examples Create a Database Mail account. ``` EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'MailAccount1', @description = 'Mail account for testing DB Mail', @email_address = 'Address@MyDomain.com', @replyto_address = 'ReplyAddress@MyDomain.com', @display_name = 'Mailer for registration messages', @mailserver_name = 'smtp.MyDomain.com' ; ``` Create a Database Mail profile. ``` EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'MailAccount1 Profile', @description = 'Mail Profile for testing DB Mail' ; ``` Associate the account with the profile. ``` EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'MailAccount1 Profile', @account_name = 'MailAccount1', @sequence_number =1 ; ``` Grant the profile access to the `DBMailUsers` role. ``` EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'MailAccount1 Profile', @principal_name = 'ApplicationUser', @is_default = 1 ; ``` Send a message with `sp_db_sendmail`. ``` EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MailAccount1 Profile', @recipients = 'Recipient@Mydomain.com', @query = 'SELECT * FROM fn_WeeklySalesReport(GETDATE())', @subject = 'Weekly Sales Report', @attach_query_result_as_file = 1 ; ``` For more information, see [Database Mail](https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) doesn’t provide native support for sending email message from the database. For alerting purposes, use the Event Notification Subscription feature to send email notifications to operators. For more information, see [Alerting](chap-sql-server-aurora-pg.management.alerting.md). The only way to send an Email from the database is to use AWS Lambda integration. For more information, see [AWS Lambda](https://aws.amazon.com/lambda). ### Examples The following walkthrough shows how to send an Email from Aurora PostgreSQL using AWS Lambda integration. First, configure Amazon Simple Email Service (Amazon SES). For more information, see [What is Amazon SES?](https://docs.aws.amazon.com/ses/latest/dg/Welcome.html) in the *Amazon Simple Email Service Developer Guide*. 1. In the AWS console, choose **SES**, **SMTP Settings**, and then choose **Create My SMTP Credentials**. Copy the SMTP server name, which you will use in the AWS Lambda function. 1. For **IAM User Name**, enter the SMTP user name, and then choose **Create**. 1. Save the credentials, which you will use to authenticate with the SMTP server. After you leave this page, you can’t retrieve these credentials. 1. In the AWS console, choose **SES**, **Email Addresses**, and then choose **Verify a New Email Address**. Before you send emails, verify the email address. 1. After you verify the email, create a table to store messages to be sent by the AWS Lambda function. ``` CREATE TABLE emails (title varchar(600), body varchar(600), recipients varchar(600)); ``` 1. In the AWS console, choose **Lambda**, and then choose **Create function**. 1. Select **Author from scratch**, enter a name for your project, and select Python 2.7 as the runtime. Make sure that you use a role with the correct permissions. Choose **Create function**. 1. Download this [GitHub project](https://github.com/alexcasalboni/awslambda-psycopg2). 1. In your local environment, create two files: `main.py` and `db_util.py`. Copy and paste the following content into these files. Make sure that you replace the code placeholders with values for your environment. **main.py** ``` #!/usr/bin/python import sys import logging import psycopg2 from db_util import make_conn, fetch_data def lambda_handler(event, context): query_cmd = "select * from mails" print query_cmd # get a connection, if a connect can't be made an exception will be raised here conn = make_conn() result = fetch_data(conn, query_cmd) conn.close() return result ``` **db\$1util.py:** ``` #!/usr/bin/python import psycopg2 import smtplib import email.utils from email.mime.multipart import MIMEMultipart from email.mime.text import MIMEText db_host = 'YOUR_RDS_HOST' db_port = 'YOUR_RDS_PORT' db_name = 'YOUR_RDS_DBNAME' db_user = 'YOUR_RDS_USER' db_pass = 'YOUR_RDS_PASSWORD' def sendEmail(recp, sub, message): # Replace sender@example.com with your "From" address. # This address must be verified. SENDER = 'PUT HERE THE VERIFIED EMAIL' SENDERNAME = 'Lambda' # Replace recipient@example.com with a "To" address. If your account # is still in the sandbox, this address must be verified. RECIPIENT = recp # Replace smtp_username with your Amazon SES SMTP user name. USERNAME_SMTP = "YOUR_SMTP_USERNAME" # Replace smtp_password with your Amazon SES SMTP password. PASSWORD_SMTP = "YOUR_SMTP PASSWORD" # (Optional) the name of a configuration set to use for this message. # If you comment out this line, you also need to remove or comment out # the "X-SES-CONFIGURATION-SET:" header. CONFIGURATION_SET = "ConfigSet" # If you're using Amazon SES in a region other than US West (Oregon), # replace email-smtp.us-west-2.amazonaws.com with the Amazon SES SMTP # endpoint in the appropriate region. HOST = "YOUR_SMTP_SERVERNAME" PORT = 587 # The subject line of the email. SUBJECT = sub # The email body for recipients with non-HTML email clients. BODY_TEXT = ("Amazon SES Test\r\n" "This email was sent through the Amazon SES SMTP " "Interface using the Python smtplib package." ) # The HTML body of the email. BODY_HTML = """

Amazon SES SMTP Email Test

""" + message + """ """ # Create message container - the correct MIME type is multipart/alternative. msg = MIMEMultipart('alternative') msg['Subject'] = SUBJECT msg['From'] = email.utils.formataddr((SENDERNAME, SENDER)) msg['To'] = RECIPIENT # Comment or delete the next line if you aren't using a configuration set #msg.add_header('X-SES-CONFIGURATION-SET',CONFIGURATION_SET) # Record the MIME types of both parts - text/plain and text/html. part1 = MIMEText(BODY_TEXT, 'plain') part2 = MIMEText(BODY_HTML, 'html') # Attach parts into message container. # According to RFC 2046, the last part of a multipart message, in this case # the HTML message, is best and preferred. msg.attach(part1) msg.attach(part2) # Try to send the message. try: server = smtplib.SMTP(HOST, PORT) server.ehlo() server.starttls() #stmplib docs recommend calling ehlo() before & after starttls() server.ehlo() server.login(USERNAME_SMTP, PASSWORD_SMTP) server.sendmail(SENDER, RECIPIENT, msg.as_string()) server.close() # Display an error message if something goes wrong. except Exception as e: print ("Error: ", e) else: print ("Email sent!") def make_conn(): conn = None try: conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'" % (db_name, db_user, db_host, db_pass)) except: print "I am unable to connect to the database" return conn def fetch_data(conn, query): result = [] print "Now running: %s" % (query) cursor = conn.cursor() cursor.execute(query) print("Number of new mails to be sent: ", cursor.rowcount) raw = cursor.fetchall() for line in raw: print(line[0]) sendEmail(line[2],line[0],line[1]) result.append(line) cursor.execute('delete from mails') cursor.execute('commit') return result ``` **Note** In the body of `db_util.py`, AWS Lambda deletes the content of the mails table. 1. Place the `main.py` and `db_util.py` files inside the GitHub extracted folder and create a new archive file using the ZIP file format that includes your two new files. 1. Return to your Lambda project and change the **Code entry type** to **Upload a .ZIP file**, change the Handler to `mail.lambda_handler`, and upload the file. Choose Save. 1. To test the lambda function, choose **Test** and enter the **Event name**. **Note** You can trigger the AWS Lambda function by multiple options. This walkthrough demonstrates how to schedule it to run every minute. Remember, you are paying for each AWS Lambda run. 1. To create a scheduled trigger, use Amazon CloudWatch, enter all details, and choose **Add**. **Note** This example runs every minute, but you can use a different interval. For more information, see [Schedule expressions using rate or cron](https://docs.aws.amazon.com/lambda/latest/dg/services-cloudwatchevents-expressions.html). 1. Choose **Save**. # ETL features This topic provides reference information about migrating ETL (Extract, Transform, Load) functionality from Microsoft SQL Server 2019 to Amazon Aurora PostgreSQL. It introduces AWS Glue as an alternative to SQL Server’s native ETL tools, specifically SQL Server Integration Services (SSIS) which replaced the older Data Transformation Services (DTS). | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[No feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-0.png) | N/A | N/A | Use [AWS Glue](https://aws.amazon.com/glue) for ETL. | ## SQL Server Usage SQL Server offers a native extract, transform, and load (ETL) framework of tools and services to support enterprise ETL requirements. The legacy Data Transformation Services (DTS) has been deprecated as of SQL Server 2008 and replaced with SQL Server Integration Services (SSIS), which was introduced in SQL Server 2005. For more information, see [Data Transformation Services (DTS)](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/cc707786(v=sql.105)) in the *SQL Server documentation*. ### DTS DTS was introduced in SQL Server version 7 in 1998. It was significantly expanded in SQL Server 2000 with features such as FTP, database level operations, and Microsoft Message Queuing (MSMQ) integration. It included a set of objects, utilities, and services that enabled easy, visual construction of complex ETL operations across heterogeneous data sources and targets. DTS supported OLE DB, ODBC, and text file drivers. It allowed transformations to be scheduled using SQL Server Agent. For more information, see [SQL Server Agent](chap-sql-server-aurora-pg.management.scheduledlambda.md). DTS also provided version control and backup capabilities with version control systems such as Microsoft Visual SourceSafe. The fundamental entity in DTS was the DTS Package. Packages were the logical containers for DTS objects such as connections, data transfers, transformations, and notifications. The DTS framework also included the following tools: + DTS Wizards. + DTS Package Designers. + DTS Query Designer. + DTS Run Utility. ### SSIS The SSIS framework was introduced in SQL Server 2005, but was limited to the top-tier editions only, unlike DTS which was available with all editions. SSIS has evolved over DTS to offer a true modern, enterprise class, heterogeneous platform for a broad range of data migration and processing tasks. It provides a rich workflow-oriented design with features for all types of enterprise data warehousing. It also supports scheduling capabilities for multi-dimensional cubes management. SSIS provides the following tools: + SSIS Import/Export Wizard is an SQL Server Management Studio extension that enables quick creation of packages for moving data between a wide array of sources and destinations. However, it has limited transformation capabilities. + SQL Server Business Intelligence Development Studio (BIDS) is a developer tool for creating complex packages and transformations. It provides the ability to integrate procedural code into package transformations and provides a scripting environment. Recently, BIDS has been replaced by SQL Server Data Tools - Business intelligence (SSDT-BI). SSIS objects include: + Connections. + Event handlers. + Workflows. + Error handlers. + Parameters (starting with SQL Server 2012). + Precedence constraints. + Tasks. + Variables. SSIS packages are constructed as XML documents and you can save them to the file system or store within a SQL Server instance using a hierarchical name space. For more information, see [SQL Server Integration Services](https://docs.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver15) in the *SQL Server documentation* and [Data Transformation Services](https://en.wikipedia.org/wiki/Data_Transformation_Services) in *Wikipedia*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) provides [AWS Glue](https://aws.amazon.com/glue) for enterprise class extract, transform, and load (ETL). It is a fully managed service that performs data cataloging, cleansing, enriching, and movement between heterogeneous data sources and destinations. Being a fully managed service, the user doesn’t need to be concerned with infrastructure management. ### AWS Glue Key Features **Integrated data catalog** The AWS Glue Data Catalog is a persistent metadata store, that you can use to store all data assets, whether in the cloud or on-premises. It stores table schemas, job steps, and additional meta data information for managing these processes. AWS Glue can automatically calculate statistics and register partitions to make queries more efficient. It maintains a comprehensive schema version history for tracking changes over time. **Automatic schema discovery** AWS Glue provides automatic crawlers that can connect to source or target data providers. The crawler uses a prioritized list of classifiers to determine the schema for your data and then generates and stores the metadata in the AWS Glue Data Catalog. You can schedule crawlers or run on-demand. You can also trigger a crawler when an event occurs to keep metadata current. **Code generation** AWS Glue automatically generates the code to extract, transform, and load data. All you need to do is point Glue to your data source and target. The ETL scripts to transform, flatten, and enrich data are created automatically. You can generate AWS Glue scripts in Scala or Python and use them in Apache Spark. **Developer endpoints** When interactively developing AWS Glue ETL code, AWS Glue provides development endpoints for editing, debugging, and testing. You can use any IDE or text editor for ETL development. You can import custom readers, writers, and transformations into Glue ETL jobs as libraries. You can also use and share code with other developers in the [AWS Glue GitHub repository](https://github.com/awslabs/aws-glue-libs). **Flexible job scheduler** You can trigger AWS Glue jobs for running either on a pre-defined schedule, on-demand, or as a response to an event. You can start multiple jobs in parallel and explicitly define dependencies across jobs to build complex ETL pipelines. AWS Glue handles all inter-job dependencies, filters bad data, and retries failed jobs. All logs and notifications are pushed to Amazon CloudWatch; you can monitor and get alerts from a central service. ### Migration Considerations You can use AWS Schema Conversion Tool (AWS SCT) to convert your Microsoft SSIS ETL scripts to AWS Glue. For more information, see [Converting SSIS](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP-converting-aws-glue-ssis.html). ### Examples The following walkthrough describes how to create an AWS Glue job to upload a comma-separated values (CSV) file from Amazon S3 to Aurora PostgreSQL. The source file for this walkthrough is a simple Visits table in CSV format. The objective is to upload this file to an Amazon S3 bucket and create an AWS Glue job to discover and copy it into an Aurora PostgreSQL database. #### Step 1 — Create a Bucket in Amazon S3 and Upload the CSV File 1. In the AWS console, choose **S3**, and then choose **Create bucket**. **Note** This walkthrough demonstrates how to create the buckets and upload the files manually, which is automated using the Amazon S3 API for production ETLs. Using the console to manually run all the settings will help you get familiar with the terminology, concepts, and workflow. 1. Enter a unique name for the bucket, select a region, and define the level of access. 1. Turn on versioning, add tags, turn on server-side encryption, and choose **Create bucket**. 1. On the Amazon S3 Management Console, choose the newly created bucket. 1. On the bucket page, choose **Upload**. 1. Choose **Add files**, select your CSV file, and choose **Upload**. #### Step 2 — Add an Amazon Glue Crawler to Discover and Catalog the Visits File 1. In the AWS console, choose ** AWS Glue **. 1. Choose **Tables**, and then choose **Add tables using a crawler**. 1. Enter the name of the crawler and choose **Next**. 1. On the **Specify crawler source type** page, leave the default values, and choose **Next**. 1. On the **Add a data store** page, specify a valid Amazon S3 path, and choose **Next**. 1. On the **Choose an IAM role** page, choose an existing IAM role, or create a new IAM role. Choose **Next**. 1. On the **Create a schedule for this crawler** page, choose **Run on demand**, and choose **Next**. 1. On the **Configure the crawler’s output** page, choose a database for the crawler’s output, enter an optional table prefix for easy reference, and choose **Next**. 1. Review the information that you provided and choose **Finish** to create the crawler. ![\[Crawler\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-crawler.png) #### Step 3 — Run the Amazon Glue Crawler 1. In the AWS console, choose ** AWS Glue **, and then choose **Crawlers**. 1. Choose the crawler that you created on the previous step, and choose **Run crawler**. After the crawler completes, the table should be discovered and recorded in the catalog in the table specified. Click the link to get to the table that was just discovered and then click the table name. Verify the crawler identified the table’s properties and schema correctly. **Note** You can manually adjust the properties and schema JSON files using the buttons on the top right. If you don’t want to add a crawler, you can add tables manually. 1. In the AWS console, choose ** AWS Glue **. 1. Choose **Tables**, and then choose **Add table manually**. #### Step 4 — Create an ETL Job to Copy the Visits Table to an Aurora PostgreSQL Database 1. In the AWS console, choose ** AWS Glue **. 1. Choose **Jobs (legacy)**, and then choose **Add job**. 1. Enter a name for the ETL job and pick a role for the security context. For this example, use the same role created for the crawler. The job may consist of a pre-existing ETL script, a manually-authored script, or an automatic script generated by Amazon Glue. For this example, use Amazon Glue. Enter a name for the script file or accept the default, which is also the job’s name. Configure advanced properties and parameters if needed and choose **Next**. 1. Select the data source for the job and choose **Next**. 1. On the **Choose a transform type** page, choose **Change schema**. 1. On the **Choose a data target** page, choose **Create tables in your data target**, use the JDBC Data store, and the `gluerds` connection type. Choose **Add Connection**. 1. On the **Add connection** page, enter the access details for the Amazon Aurora Instance and choose **Add**. 1. Choose **Next** to display the column mapping between the source and target. Leave the default mapping and data types, and choose **Next**. 1. Review the job properties and choose **Save job and edit script**. 1. Review the generated script and make manual changes if needed. You can use the built-in templates for source, target, target location, transform, and spigot using the buttons at the top right section of the screen. 1. Choose **Run job**. 1. In the AWS console, choose ** AWS Glue **, and then choose **Jobs (legacy)**. 1. On the history tab, verify that the job status is set to **Succeeded**. 1. Open your query IDE, connect to the Aurora PostgreSQL cluster, and query the visits database to make sure the data has been transferred successfully. For more information, see [AWS Glue Developer Guide](https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html) and [AWS Glue resources](https://aws.amazon.com/glue/resources). # Export and import features This topic provides reference information on data export and import capabilities in Microsoft SQL Server and PostgreSQL, with a focus on migration scenarios. You can use various tools and utilities to export data from SQL Server and import it into PostgreSQL, which is particularly useful when migrating to Amazon Aurora PostgreSQL. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[No feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-0.png) | N/A | N/A | Non-compatible tool. | ## SQL Server Usage SQL Server provides many options for exporting and importing text files. These operations are commonly used for data migration, scripting, and backup. + Save results to a file in SQL Server Management Studio (SSMS). For more information, see [KB - How to create .csv or .rpt files from an SQL statement in Microsoft SQL Server](https://support.microsoft.com/en-us/topic/kb-how-to-create-csv-or-rpt-files-from-an-sql-statement-in-microsoft-sql-server-baaccba6-a3d9-b77d-7f4e-107ae4dd739b) in the *SQL Server documentation*. l SQLCMD. For more information, see [Run the script file](https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-run-transact-sql-script-files?view=sql-server-ver15#save-the-output-to-a-text-file) in the *SQL Server documentation*. l PowerShell wrapper for SQLCMD l SSMS Import/Export Wizard. For more information, see [Start the SQL Server Import and Export Wizard](https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard?view=sql-server-ver15) in the *SQL Server documentation*. l SQL Server Reporting Services (SSRS) l Bulk Copy Program (BCP). For more information, see [Import and export bulk data using bcp (SQL Server)](https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-and-export-bulk-data-by-using-the-bcp-utility-sql-server?view=sql-server-ver15) in the *SQL Server documentation*. All of the options described before required additional tools to export data. Most of the tools are open source and provide support for a variety of databases. SQLCMD is a command line utility for running T-SQL statements, system procedures, and script files. It uses ODBC to run T-SQL batches. For example: ``` SQLCMD -i C:\sql\myquery.sql -o C:\sql\output.txt ``` SQLCMD utility syntax: ``` sqlcmd -a packet_size -A (dedicated administrator connection) -b (terminate batch job if there is an error) -c batch_terminator -C (trust the server certificate) -d db_name -e (echo input) -E (use trusted connection) -f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage] -g (enable column encryption) -G (use Azure Active Directory for authentication) -h rows_per_header -H workstation_name -i input_file -I (enable quoted identifiers) -j (Print raw error messages) -k[1 | 2] (remove or replace control characters) -K application_intent -l login_timeout -L[c] (list servers, optional clean output) -m error_level -M multisubnet_failover -N (encrypt connection) -o output_file -p[1] (print statistics, optional colon format) -P password -q "cmdline query" -Q "cmdline query" (and exit) -r[0 | 1] (msgs to stderr) -R (use client regional settings) -s col_separator -S [protocol:]server[instance_name][,port] -t query_timeout -u (unicode output file) -U login_id -v var = "value" -V error_severity_level -w column_width -W (remove trailing spaces) -x (disable variable substitution) -X[1] (disable commands, startup script, environment variables, optional exit) -y variable_length_type_display_width -Y fixed_length_type_display_width -z new_password -Z new_password (and exit) -? (usage) ``` ### Examples Connect to a named instance using Windows Authentication and specify input and output files. ``` sqlcmd -S MyMSSQLServer\MyMSSQLInstance -i query.sql -o outputfile.txt ``` If the file is needed for import to another database, query the data as `INSERT` commands and `CREATE` for the object. You can export data with SQLCMD and import with the Export/Import wizard. For more information, see [sqlcmd Utility](https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage PostgreSQL provides the native utilities `pg_dump` and `pg_restore` to perform logical database exports and imports with comparable functionality to the SQl Server SQLCMD utility. For example, moving data between two databases and creating logical database backups. + **pg\$1dump** to export data. + **pg\$1restore** to import data. The binaries for both utilities must be installed on your local workstation or on an Amazon EC2 server as part of the PostgreSQL client binaries. You can export and copy PostgreSQL dump files created using `pg_dump` to an Amazon S3 bucket as cloud backup storage or for maintaining the desired backup retention policy. Later, when you need the dump files for database restore, you can copy them copied back to a desktop or server that has a PostgreSQL client, such as your workstation or an Amazon EC2 server. Then you can issue the `pg_restore` command. Starting with PostgreSQL 10, these capabilities were added: + You can exclude a schema in `pg_dump` and `pg_restore` commands. + Can create dumps with no blobs. + Allow to run `pg_dumpall` by non-superusers, using the `--no-role-passwords` option. + Create additional integrity option to ensure that the data is stored in disk using `fsync()` method. Starting with PostgreSQL 11, the following capabilities were added: \$1 `pg_dump` and `pg_restore` now export or import relationships between extensions and database objects established with `ALTER …​ DEPENDS ON EXTENSION`, which allows these objects to be dropped when extension is dropped with `CASCADE` option. ### Notes + `pg_dump` creates consistent backups even if the database is being used concurrently. + `pg_dump` doesn’t block other users accessing the database (readers or writers). + `pg_dump` only exports a single database. To backup global objects common to all databases in a cluster (such as roles and tablespaces), use `pg_dumpall`. + PostgreSQL dump files can be plain-text and custom format files. Another option to export and import data from PostgreSQL database is to use `COPY TO/COPY FROM` commands. Starting with PostgreSQL 12, you can use the `COPY FROM` command to load data into DB. This command has support for filtering incoming rows with the `WHERE` condition. ``` CREATE TABLE tst_copy(v TEXT); COPY tst_copy FROM '/home/postgres/file.csv' WITH (FORMAT CSV) WHERE v LIKE '%apple%'; ``` ### Examples Export data using `pg_dump`. Use a workstation or server with the PostgreSQL client installed to connect to the Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) instance. Issue the `pg_dump` command providing the hostname (-h), database user name (-U), and database name (-d). ``` $ pg_dump -h hostname.rds.amazonaws.com -U username -d db_name -f dump_file_name.sql ``` The output `dump_file_name.sql` file is stored on the server where the `pg_dump` command runs. You can copy the output file to an Amazon S3 bucket if needed. Run `pg_dump` and copy the backup file to an Amazon S3 bucket using a pipe and the AWS CLI. ``` $ pg_dump -h hostname.rds.amazonaws.com -U username -d db_name -f dump_file_name.sql | aws s3 cp - s3://pg-backup/pg_bck-$(date"+%Y-%m-%d-%H-%M-%S") ``` Restore data using `pg_restore`. Use a workstation or server with the PostgreSQL client installed to connect to the Aurora PostgreSQL instance. Issue the `pg_restore` command providing the hostname (-h), database user name (-U), database name (-d), and the dump file. ``` $ pg_restore -h hostname.rds.amazonaws.com -U username -d dbname_restore dump_file_name.sql ``` Copy the output file from the local server to an Amazon S3 Bucket using the AWS CLI. Upload the dump file to an Amazon S3 bucket. ``` $ aws s3 cp /usr/Exports/hr.dmp s3://my-bucket/backup-$(date "+%Y-%m-%d-%H-%M-%S") ``` **Note** The `{-$(date "+%Y-%m-%d-%H-%M-%S")}` format is valid on Linux servers only. Download the output file from the Amazon S3 bucket. ``` $ aws s3 cp s3://my-bucket/backup-2017-09-10-01-10-10 /usr/Exports/hr.dmp ``` **Note** You can create a copy of an existing database without having to use `pg_dump` or `pg_restore`. Instead, use the template keyword to specify the source database. ``` CREATE DATABASE mydb_copy TEPLATE mydb; ``` ## Summary | Description | SQL Server export / import | | --- | --- | | PostgreSQL Dump | Export data to a file | | Using SQLCMD or Export/Import Wizard 
SQLCMD -i C:\sql\myquery.sql -o C:\sql\output.txt
| 
pg_dump -F c -h hostname.rds.amazonaws.com
    -U username -d hr -p 5432 > c:\Export\hr.dmp
| | Import data to a new database with a new name | Run SQLCMD with objects and data creation script 
SQLCMD -i C:\sql\myquery.sql
| For more information, see [SQL Dump](https://www.postgresql.org/docs/13/backup-dump.html) and [pg\$1restore](https://www.postgresql.org/docs/13/app-pgrestore.html) in the *PostgreSQL documentation*. # Viewing server logs This topic provides reference information about logging capabilities in SQL Server and Amazon Aurora PostgreSQL. You can use these logging features to monitor database activities, troubleshoot issues, and maintain the health of your database systems. The topic explains how to access and interpret logs in both environments, highlighting key differences and similarities. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | N/A | N/A | View logs from the Amazon RDS console, the Amazon RDS API, the AWS CLI, or the AWS SDKs. | ## SQL Server Usage SQL Server logs system and user generated events to the *SQL Server Error Log* and to the *Windows Application Log*. It logs recovery messages, kernel messages, security events, maintenance events, and other general server level error and informational messages. The Windows Application Log contains events from all windows applications including SQL Server and SQL Server agent. SQL Server Management Studio Log Viewer unifies all logs into a single consolidated view. You can also view the logs with any text editor. Administrators typically use the SQL Server Error Log to confirm successful completion of processes, such as backup or batches, and to investigate the cause of run time errors. These logs can help detect current risks or potential future problem areas. To view the log for SQL Server, SQL Server Agent, Database Mail, and Windows applications, open the SQL Server Management Studio Object Explorer pane, navigate to **Management**, **SQL Server Logs**, and choose the current log. The following table identifies some common error codes database administrators typically look for in the error logs: | Error code | Error message | | --- | --- | | 1105 | Couldn’t allocate space. | | 3041 | Backup failed. | | 9002 | Transaction log full. | | 14151 | Replication agent failed. | | 17053 | Operating system error. | | 18452 | Login failed. | | 9003 | Possible database corruption. | ### Examples The following screenshot shows the typical log file viewer content: ![\[Log file viewer\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-log-file-viewer.png) For more information, see [Monitoring the Error Logs](https://docs.microsoft.com/en-us/sql/tools/configuration-manager/monitoring-the-error-logs?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) provides administrators with access to the PostgreSQL error log. The PostgreSQL error log is generated by default. To generate the slow query and general logs, set the corresponding parameters in the database parameter group. For more information, see [Server Options in SQL Server and Parameter Groups in Amazon Aurora](chap-sql-server-aurora-pg.configuration.serveroptions.md). You can view Aurora PostgreSQL logs directly from the Amazon RDS console, the Amazon RDS API, the AWS CLI, or the AWS SDKs. You can also direct the logs to a database table in the main database and use SQL queries to view the data. To download a binary log, use the AWS Console. The following table includes the parameters, which control how and where PostgreSQL places log and errors files. | Parameter | Description | | --- | --- | | `log_filename` | Sets the file name pattern for log files. You can modify this parameter in an Aurora Database Parameter Group. | | `log_rotation_age` | (min) Automatic log file rotation will occur after N minutes. You can modify this parameter in an Aurora Database Parameter Group. | | `log_rotation_size` | (kB) Automatic log file rotation will occur after N kilobytes. You can modify this parameter in an Aurora Database Parameter Group. | | `log_min_messages` | Sets the message levels that are logged such as `DEBUG`, `ERROR`, `INFO`, and so on. You can modify this parameter in an Aurora Database Parameter Group. | | `log_min_error_statement` | Causes all statements generating errors at or above this level to be logged such as `DEBUG`, `ERROR`, `INFO`, and so on. You can modify this parameter in an Aurora Database Parameter Group. | | `log_min_duration_statement` | Sets the minimum run time above which statements will be logged (ms). You can modify this parameter in an Aurora Database Parameter Group. | ### Examples The following walkthrough demonstrates how to view the Aurora PostgreSQL error logs in the Amazon RDS console. 1. In the AWS console, choose **RDS**, and then choose **Databases**. 1. Choose the instance for which you want to view the error log. ![\[Log file viewer\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-view-error-log.png) 1. Scroll down to the logs section and choose the log name. The log viewer displays the log content. For more information, see [PostgreSQL database log files](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.PostgreSQL.html) in the *Amazon Relational Database Service User Guide*. # Maintenance plans This topic provides reference information comparing database maintenance tasks between Microsoft SQL Server and Amazon Aurora PostgreSQL. You can understand the key differences in how these two database systems handle common maintenance operations such as backups, index management, statistics updates, and consistency checks. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | N/A | N/A | Backups using the Amazon RDS services. Table maintenance using SQL commands. | ## SQL Server Usage A *maintenance plan* is a set of automated tasks used to optimize a database, performs regular backups, and ensure it is free of inconsistencies. Maintenance plans are implemented as SQL Server Integration Services (SSIS) packages and are run by SQL Server Agent jobs. You can run them manually or automatically at scheduled time intervals. SQL Server provides a variety of pre-configured maintenance tasks. You can create custom tasks using TSQL scripts or operating system batch files. Maintenance plans are typically used for the following tasks: + Backing up database and transaction log files. + Performing cleanup of database backup files in accordance with retention policies. + Performing database consistency checks. + Rebuilding or reorganizing indexes. + Decreasing data file size by removing empty pages (shrink a database). + Updating statistics to help the query optimizer obtain updated data distributions. + Running SQL Server Agent jobs for custom actions. + Running a T-SQL task. Maintenance plans can include tasks for operator notifications and history or maintenance cleanup. They can also generate reports and output the contents to a text file or the maintenance plan tables in the `msdb` database. You can create and manage maintenance plans using the maintenance plan wizard in SQL Server Management Studio, Maintenance Plan Design Surface (provides enhanced functionality over the wizard), Management Studio Object Explorer, and T-SQL system stored procedures. For more information, see [SQL Server Agent and PostgreSQL Scheduled Lambda](chap-sql-server-aurora-pg.management.scheduledlambda.md). ### Deprecated DBCC Index and Table Maintenance Commands The DBCC DBREINDEX, INDEXDEFRAG, and SHOWCONTIG commands have been deprecated as of SQL Server 2008R2. For more information, see [Deprecated Database Engine Features in SQL Server 2008 R2](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms143729(v=sql.105)) in the *SQL Server documentation*. In place of the deprecated DBCC, SQL Server provides newer syntax alternatives as detailed in the following table. | Deprecated DBCC command | Use instead | | --- | --- | | `DBCC DBREINDEX` | `ALTER INDEX …​ REBUILD` | | `DBCC INDEXDEFRAG` | `ALTER INDEX …​ REORGANIZE` | | `DBCC SHOWCONTIG` | `sys.dm_db_index_physical_stats` | For the Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) alternatives to these maintenance commands, see [Aurora PostgreSQL Maintenance Plans](#chap-sql-server-aurora-pg.management.maintenanceplans.pg). ### Examples Enable Agent XPs, which are disabled by default. ``` EXEC [sys].[sp_configure] @configname = 'show advanced options', @configvalue = 1 RECONFIGURE ; ``` ``` EXEC [sys].[sp_configure] @configname = 'agent xps', @configvalue = 1 RECONFIGURE; ``` Create a T-SQL maintenance plan for a single index rebuild. ``` USE msdb; ``` Add the Index Maintenance `IDX1` job to SQL Server Agent. ``` EXEC dbo.sp_add_job @job_name = N'Index Maintenance IDX1', @enabled = 1, @description = N'Optimize IDX1 for INSERT' ; ``` Add the T-SQL job step `Rebuild IDX1 to 50 percent fill`. ``` EXEC dbo.sp_add_jobstep @job_name = N'Index Maintenance IDX1', @step_name = N'Rebuild IDX1 to 50 percent fill', @subsystem = N'TSQL', @command = N'Use MyDatabase; ALTER INDEX IDX1 ON Shcema.Table REBUILD WITH ( FILL_FACTOR = 50), @retry_attempts = 5, @retry_interval = 5; ``` Add a schedule to run every day at 01:00 AM. ``` EXEC dbo.sp_add_schedule @schedule_name = N'Daily0100', @freq_type = 4, @freq_interval = 1, @active_start_time = 010000; ``` Associate the schedule `Daily0100` with the job index maintenance `IDX1`. ``` EXEC sp_attach_schedule @job_name = N'Index Maintenance IDX1' @schedule_name = N'Daily0100' ; ``` For more information, see [Maintenance Plans](https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/maintenance-plans?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Relational Database Service (Amazon RDS) performs automated database backups by creating storage volume snapshots that back up entire instances, not individual databases. Amazon RDS creates snapshots during the backup window for individual database instances and retains snapshots in accordance with the backup retention period. You can use the snapshots to restore a database to any point in time within the backup retention period. **Note** The state of a database instance must be ACTIVE for automated backups to occur. You can backup database instances manually by creating an explicit database snapshot. Use the AWS console, the AWS CLI, or the AWS API to take manual snapshots. ### Examples **Create a manual database snapshot using the Amazon RDS console** 1. In the AWS console, choose **RDS**, and then choose **Databases**. 1. Choose your Aurora PostgreSQL instance, and for **Instance actions** choose **Take snapshot**. ![\[Take snapshot\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-take-snapshot.png) **Restore a snapshot using the Amazon RDS console** 1. In the AWS console, choose **RDS**, and then choose **Snapshots**. 1. Choose the snapshot to restore, and for **Actions** choose **Restore snapshot**. This action creates a new instance. 1. Enter the required configuration options in the wizard for creating a new Amazon Aurora database instance. Choose **Restore DB Instance**. You can also restore a database instance to a point-in-time. For more information, see [Backup and Restore](chap-sql-server-aurora-pg.hadr.backup.md). For all other tasks, use a third-party or a custom application scheduler. **Rebuild and reorganize a table** Aurora PostgreSQL supports the `VACUUM`, `ANALYZE`, and `REINDEX` commands, which are similar to the `REORGANIZE` option of SQL Server indexes. ``` VACUUM MyTable; ANALYZE MyTable; REINDEX TABLE MyTable; ``` + `VACUUM` reclaims storage. + `ANALYZE` collects statistics. + `REINDEX` recreates all indexes. For more information, see [ANALYZE](https://www.postgresql.org/docs/13/sql-analyze.html), [VACUUM](https://www.postgresql.org/docs/13/sql-vacuum.html), and [REINDEX](https://www.postgresql.org/docs/13/sql-reindex.html) in the *PostgreSQL documentation*. **Convert deprecated DBCC index and table maintenance commands** | Deprecated DBCC command | Aurora PostgreSQL equivalent | | --- | --- | | `DBCC DBREINDEX` | `REINDEX INDEX` or `REINDEX TABLE` | | `DBCC INDEXDEFRAG` | `VACUUM table_name` or `VACUUM table_name column_name` | **Update statistics to help the query optimizer get updated data distribution** For more information, see [SQL Server Managing Statistics and PostgreSQL Table Statistics](chap-sql-server-aurora-pg.tuning.statistics.md). ## Summary The following table summarizes the key tasks that use SQL Server maintenance plans and a comparable Aurora PostgreSQL solutions. | Task | SQL Server | Aurora PostgreSQL | | --- | --- | --- | | Rebuild or reorganize indexes | `ALTER INDEX` or `ALTER TABLE` | `REINDEX INDEX` or `REINDEX TABLE` | | Decrease data file size by removing empty pages | `DBCC SHRINKDATABASE` or `DBCC SHRINKFILE` | `VACUUM` | | Update statistics to help the query optimizer get updated data distribution | `UPDATE STATISTICS` or `sp_updatestats` | `ANALYZE` | | Perform database consistency checks | `DBCC CHECKDB` or `DBCC CHECKTABLE` | N/A | | Back up the database and transaction log files | `BACKUP DATABASE` or `BACKUP LOG` | Automatically (for example, using AWS CLI) | | Run SQL Server Agent jobs for custom actions | `sp_start_job` or `scheduled` | N/A | For more information, see [Working with backups](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.html) in the *PostgreSQL documentation*. # Monitoring features This topic provides reference information about monitoring capabilities in Microsoft SQL Server and Amazon Aurora PostgreSQL. You can use various tools and services to monitor and maintain the performance of your database systems. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | N/A | N/A | Use Amazon CloudWatch service. For more information, see [Monitoring metrics in an Amazon RDS instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Monitoring.html) in the *Amazon Relational Database Service User Guide*. | ## SQL Server Usage Monitoring server performance and behavior is a critical aspect of maintaining service quality and includes ad-hoc data collection, ongoing data collection, root cause analysis, preventative actions, and reactive actions. SQL Server provides an array of interfaces to monitor and collect server data. SQL Server 2017 introduces several new dynamic management views: + `sys.dm_db_log_stats` exposes summary level attributes and information on transaction log files, helpful for monitoring transaction log health. + `sys.dm_tran_version_store_space_usage` tracks version store usage for each database, useful for proactively planning `tempdb` sizing based on the version store usage for each database. + `sys.dm_db_log_info` exposes VLF information to monitor, alert, and avert potential transaction log issues. + `sys.dm_db_stats_histogram` is a new dynamic management view for examining statistics. + `sys.dm_os_host_info` provides operating system information for both Windows and Linux. SQL Server 2019 adds new configuration parameter, `LIGHTWEIGHT_QUERY_PROFILING`. It turns on or turns off the lightweight query profiling infrastructure. The lightweight query profiling infrastructure (LWP) provides query performance data more efficiently than standard profiling mechanisms and is enabled by default. For more information, see [Query Profiling Infrastructure](https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure?view=sql-server-ver15) in the *SQL Server documentation*. ### Windows Operating System Level Tools You can use the Windows Scheduler to trigger run of script files such as CMD, PowerShell, and so on to collect, store, and process performance data. System Monitor is a graphical tool for measuring and recording performance of SQL Server and other Windows-related metrics using the Windows Management Interface (WMI) performance objects. **Note** Performance objects can also be accessed directly from T-SQL using the SQL Server Operating System Related DMVs. For a full list of the DMVs, see [SQL Server Operating System Related Dynamic Management Views (Transact-SQL)](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sql-server-operating-system-related-dynamic-management-views-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. Performance counters exist for real-time measurements such as CPU Utilization and for aggregated history such as average active transactions. For a full list of the object hierarchy, see: [Use SQL Server Objects](https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/use-sql-server-objects?view=sql-server-ver15) in the *SQL Server documentation*. ### SQL Server Extended Events SQL Server’s latest tracing framework provides very lightweight and robust event collection and storage. SQL Server Management Studio features the New Session Wizard and New Session graphic user interfaces for managing and analyzing captured data. SQL Server Extended Events consists of the following items: + SQL Server Extended Events Package is a logical container for Extended Events objects. + SQL Server Extended Events Targets are consumers of events. Targets include Event File, which writes data to the file Ring Buffer for retention in memory, or for processing aggregates such as Event Counters and Histograms. + SQL Server Extended Events Engine is a collection of services and tools that comprise the framework. + SQL Server Extended Events Sessions are logical containers mapped many-to-many with packages, events, and filters. The following example creates a session that logs lock escalations and lock timeouts to a file. ``` CREATE EVENT SESSION Locking_Demo ON SERVER ADD EVENT sqlserver.lock_escalation, ADD EVENT sqlserver.lock_timeout ADD TARGET package0.etw_classic_sync_target (SET default_etw_session_logfile_path = N'C:\ExtendedEvents\Locking\Demo_20180502.etl') WITH (MAX_MEMORY=8MB, MAX_EVENT_SIZE=8MB); GO ``` ### SQL Server Tracing Framework and the SQL Server Profiler Tool The SQL Server trace framework is the predecessor to the Extended Events framework and remains popular among database administrators. The lighter and more flexible Extended Events Framework is recommended for development of new monitoring functionality. For more information, see [SQL Server Profiler](https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver15) in the *SQL Server documentation*. ### SQL Server Management Studio SQL Server Management Studio (SSMS) provides several monitoring extensions: + **SQL Server Activity Monitor** is an in-process, real-time, basic high-level information graphical tool. + **Query Graphical Show Plan** provides easy exploration of estimated and actual query run plans. + **Query Live Statistics** displays query run progress in real time. + **Replication Monitor** presents a publisher-focused view or distributor-focused view of all replication activity. For more information, see [Overview of the Replication Monitor Interface](https://docs.microsoft.com/en-us/sql/relational-databases/replication/monitor/overview-of-the-replication-monitor-interface?view=sql-server-ver15) in the *SQL Server documentation*. + **Log Shipping Monitor** displays the status of any log shipping activity whose status is available from the server instance to which you are connected. For more information, see [View the Log Shipping Report (SQL Server Management Studio)](https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/view-the-log-shipping-report-sql-server-management-studio?view=sql-server-ver15) in the *SQL Server documentation*. + **Standard Performance Reports** is set of reports that show the most important performance metrics such as change history, memory usage, activity, transactions, HA, and more. ### T-SQL From the T-SQL interface, SQL Server provides many system stored procedures, system views, and functions for monitoring data. System stored procedures such as `sp_who` and `sp_lock` provide real-time information. The `sp_monitor` procedure provides aggregated data. Built in functions such as `@@CONNECTIONS`, `@@IO_BUSY`, `@@TOTAL_ERRORS`, and others provide high level server information. A rich set of System Dynamic Management functions and views are provided for monitoring almost every aspect of the server. These functions reside in the sys schema and are prefixed with `dm_string`. For more information, see [System Dynamic Management Views](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver15) in the *SQL Server documentation*. ### Trace Flags You can set trace flags to log events. For example, set trace flag 1204 to log deadlock information. For more information, see [DBCC TRACEON - Trace Flags (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. ### SQL Server Query Store Query Store is a database-level framework supporting automatic collection of queries, run plans, and run time statistics. This data is stored in system tables. You can use this data to diagnose performance issues, understand patterns, and understand trends. It can also be set to automatically revert plans when a performance regression is detected. For more information, see [Monitoring performance by using the Query Store](https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Relational Database Service (Amazon RDS) provides a rich monitoring infrastructure for Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) clusters and instances with the Amazon CloudWatch service. For more information, see [Monitoring metrics in an Amazon RDS instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Monitoring.html) and [Monitoring OS metrics with Enhanced Monitoring](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html) in the *Amazon Relational Database Service User Guide*. You can also use the AWS Performance Insights tool to monitor PostgreSQL. PostgreSQL can also be monitored by querying system catalog table and views. Starting with PostgreSQL 12, you can monitor progress of `CREATE INDEX`, `REINDEX`, `CLUSTER`, and `VACUUM FULL` operations by querying system views `pg_stat_progress_create_index` and `pg_stat_progress_cluster`. Starting with PostgreSQL 13, you can monitor progress of `ANALYZE` operations by querying system view `pg_stat_progress_analyze`. Also, you can monitor shared memory usage with system view `pg_shmem_allocations`. ### Example The following walkthrough demonstrates how to access the Amazon Aurora Performance Insights Console. 1. In the AWS console, choose **RDS**, and then choose **Performance insights**. 1. The web page displays a dashboard containing current and past database performance metrics. You can choose the period of the displayed performance data (5 minutes, 1 hour, 6 hours, or 24 hours) as well as different criteria to filter and slice the information such as waits, SQL, hosts, users, and so on. ![\[Performance insights\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-performance-insights.png) ### Turning on Performance Insights Performance insights are turned on by default for Amazon Aurora clusters. If you have more than one database in your Amazon Aurora cluster, performance data for all databases is aggregated. Database performance data is retained for 24 hours. For more information, see [Monitoring DB load with Performance Insights on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html) in the *Amazon Relational Database Service User Guide*. # Resource governor features This topic provides reference information comparing resource management capabilities between Microsoft SQL Server and Amazon Aurora PostgreSQL. You can understand how SQL Server’s Resource Governor functionality, which allows administrators to control and manage resource consumption, differs from Aurora PostgreSQL. While Aurora PostgreSQL doesn’t have built-in resource management equivalent to SQL Server, it leverages cloud economics and flexibility to address similar needs. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | N/A | N/A | Distribute load, applications, or users across multiple instances. | ## SQL Server Usage SQL Server Resource Governor provides the capability to control and manage resource consumption. Administrators can specify and enforce workload limits on CPU, physical I/O, and Memory. Resource configurations are dynamic and you can change them in real time. In SQL Server 2019 configurable value for the `REQUEST_MAX_MEMORY_GRANT_PERCENT` option of `CREATE WORKLOAD GROUP` and `ALTER WORKLOAD GROUP` has been changed from an integer to a float data type to allow more granular control of memory limits. For more information, see [ALTER WORKLOAD GROUP (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-workload-group-transact-sql?view=sql-server-ver15) and [CREATE WORKLOAD GROUP (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-workload-group-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. ### Use Cases The following list identifies typical Resource Governor use cases: + **Minimize performance bottlenecks and inconsistencies** to better support Service Level Agreements (SLA) for multiple workloads and users. + **Protect against runaway queries** that consume a large amount of resources or explicitly throttle I/O intensive operations. For example, consistency checks with DBCC that may bottleneck the I/O subsystem and negatively impact concurrent workloads. + **Allow tracking and control for resource-based pricing scenarios** to improve predictability of user charges. ### Concepts The three basic concepts in Resource Governor are Resource Pools, Workload Groups, and Classification. + **Resource Pools** represent physical resources. Two built-in resource pools, internal and default, are created when SQL Server is installed. You can create custom user-defined resource pools for specific workload types. + **Workload Groups** are logical containers for session requests with similar characteristics. Workload Groups allow aggregate resource monitoring of multiple sessions. Resource limit policies are defined for a Workload Group. Each Workload Group belongs to a Resource Pool. + **Classification** is a process that inspects incoming connections and assigns them to a specific Workload Group based on the common attributes. User-defined functions are used to implement Classification. For more information, see [User-Defined Functions](chap-sql-server-aurora-pg.tsql.udf.md). ### Examples Enable the Resource Governor. ``` ALTER RESOURCE GOVERNOR RECONFIGURE; ``` Create a Resource Pool. ``` CREATE RESOURCE POOL ReportingWorkloadPool WITH (MAX_CPU_PERCENT = 20); ``` ``` ALTER RESOURCE GOVERNOR RECONFIGURE; ``` Create a Workload Group. ``` CREATE WORKLOAD GROUP ReportingWorkloadGroup USING poolAdhoc; ``` ``` ALTER RESOURCE GOVERNOR RECONFIGURE; ``` Create a classifier function. ``` CREATE FUNCTION dbo.WorkloadClassifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN RETURN (CASE WHEN HOST_NAME()= 'ReportServer' THEN 'ReportingWorkloadGroup' ELSE 'Default' END) END; ``` Register the classifier function. ``` ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.WorkloadClassifier); ``` ``` ALTER RESOURCE GOVERNOR RECONFIGURE; ``` For more information, see [Resource Governor](https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage PostgreSQL doesn’t have built-in resource management capabilities equivalent to the functionality provided by SQL Server’s Resource Governor. However, due to the elasticity and flexibility provided by cloud economics, workarounds could be applicable and such capabilities might not be as of similar importance to monolithic on-premises databases. The SQL Server’s Resource Governor primarily exists because traditionally, SQL Server instances were installed on very powerful monolithic servers that powered multiple applications simultaneously. The monolithic model made the most sense in an environment where the licensing for the SQL Server database was per-CPU and where SQL Server instances were deployed on physical hardware. In these scenarios, it made sense to consolidate as many workloads as possible into fewer servers. With cloud databases, the strict requirement to maximize the usage of each individual server is often not as important and you can use a different approach. You can deploy individual Amazon Aurora clusters with varying sizes, each dedicated to a specific application or workload. You can use additional read-only Amazon Aurora Replica servers to offload any reporting workloads from the master instance. With Amazon Aurora, you can deploy separate and dedicated database clusters, each dedicated to a specific application or workload creating isolation between multiple connected sessions and applications. Each Amazon Aurora instance (primary or replica) can scale independently in terms of CPU and memory resources using different instance types. Because you can instantly deploy multiple Amazon Aurora Instances and much less overhead is associated with the deployment and management of Amazon Aurora instances when compared to physical servers, separating different workloads to different instance classes could be a suitable solution for controlling resource management. For more information, see [Amazon EC2 Instance Types](https://aws.amazon.com/ec2/instance-types/). In addition, each Amazon Aurora instance can also be directly accessed from your applications using its own endpoint. This capability is especially useful if you have multiple Amazon Aurora read-replicas for a given cluster and you want to use different Amazon Aurora replicas to segment your workload. You can adjust the resources and some parameters for Amazon Aurora read-replicas in the same cluster to avoid having additional cluster, however, this will allow to be used only for read operations. ### Examples Follow these steps to create an Amazon Aurora cluster. 1. In the AWS console, choose **RDS**. 1. Choose **Databases**, and then choose **Create database**. 1. Follow the wizard. Your new cluster appears in the **Databases** section. Suppose that you were using a single SQL Server instance for multiple separate applications and used SQL Server Resource Governor to enforce a workload separation, allocating a specific amount of server resources for each application. With Amazon Aurora, you might want to create multiple separate databases for each individual application. Follow these steps to add additional replica instances to an existing Amazon Aurora cluster: 1. In the AWS console, choose **RDS**. 1. Choose the Amazon Aurora cluster that you want to scale-out by adding an additional read replica. 1. For **Instance actions**, choose **Create Aurora Replica**. 1. Select the instance class depending on the amount of compute resources your application requires. 1. Choose **Create Aurora Replica**. ### Dedicated Aurora PostgreSQL Instances | Feature | Amazon Aurora instances | | --- | --- | | Set the maximum CPU usage for a resource group. | Create a dedicated Amazon Aurora instance for a specific application. | | Limit the degree of parallelism for specific queries. | 
SET max_parallel_workers_per_gather TO x;
Setting the PostgreSQL `max_parallel_workers_per_gather` parameter should be done as part of your application database connection. | | Limit parallel runs | 
SET max_parallel_workers_per_gather TO 0;
or 
SET max_parallel_workers TO x; -- for the whole system (since PostgreSQL 10)
| | Limit the number of active sessions. | Manually detect the number of connections that are open from a specific application and restrict connectivity either with database procedures or within the application DAL itself. 
select pid from pg_stat_activity where usename in( select usename from pg_stat_activity
    where state = 'active' group by usename having count(*) > 10)
    and state = 'active' order by query_Start;
| | Restrict maximum runtime of queries. | Manually terminate sessions that exceed the required threshold. You can detect the length of running queries using SQL commands and restrict max run duration using either database procedures or within the application DAL itself. 
SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE now()-pg_stat_activity.query_start > interval '5 minutes';
| | Limit the maximum idle time for sessions. | Manually terminate sessions that exceed the required threshold. You can detect the length of your idle sessions using SQL queries and restrict maximum run using either database procedures or within the application DAL itself. 
SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = 'regress' AND pid <> pg_backend_pid()
    AND state = 'idle' AND state_change < current_timestamp - INTERVAL '5' MINUTE;
| | Limit the time that an idle session holding open locks can block other sessions. | Manually terminate sessions that exceed the required threshold. You can detect the length of blocking idle sessions using SQL queries and restrict max run duration using either database procedures or within the application DAL itself. 
SELECT pg_terminate_backend(blocking_locks.pid)
    FROM pg_catalog.pg_locks AS blocked_locks
    JOIN pg_catalog.pg_stat_activity AS blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks AS blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity AS blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.granted and blocked_activity.state_change < current_timestamp - INTERVAL '5' minute;
| For more information, see [Resource Consumption](https://www.postgresql.org/docs/13/runtime-config-resource.html) in the *PostgreSQL documentation*. # Linked servers This topic provides reference information about linked servers in SQL Server and their equivalent functionality in PostgreSQL. You can understand how linked servers enable SQL Server to connect to external data sources, allowing for distributed queries and data access across heterogeneous systems. The topic explains the benefits of using linked servers, how they are configured, and the methods for accessing remote data. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | N/A | [Linked Servers](chap-sql-server-aurora-pg.tools.actioncode.md#chap-sql-server-aurora-pg.tools.actioncode.linkedservers) | Syntax and option differences, similar functionality. | ## SQL Server Usage Linked servers enable the database engine to connect to external Object Linking and Embedding for databases (OLE-DB) sources. They are typically used to run T-SQL commands and include tables in other instances of SQL Server, or other RDBMS engines such as Oracle. SQL Server supports multiple types of OLE-DB sources as linked servers, including Microsoft Access, Microsoft Excel, text files and others. The main benefits of using linked servers are: + Reading external data for import or processing. + Running distributed queries, data modifications, and transactions for enterprise-wide data sources. + Querying heterogeneous data source using the familiar T-SQL API. You can configure linked servers using either SQL Server Management Studio, or the system stored procedure `sp_addlinkedserver`. The available functionality and the specific requirements vary significantly between the various OLE-DB sources. Some sources may allow read only access, others may require specific security context settings, and so on. The linked server definition contains the linked server alias, the OLE DB provider, and all the parameters needed to connect to a specific OLE-DB data source. The OLE-DB provider is a .NET Dynamic Link Library (DLL) that handles the interaction of SQL Server with all data sources of its type. For example, OLE-DB Provider for Oracle. The OLE-DB data source is the specific data source to be accessed, using the specified OLE-DB provider. **Note** You can use SQL Server distributed queries with any custom OLE DB provider as long as the required interfaces are implemented correctly. SQL Server parses the T-SQL commands that access the linked server and sends the appropriate requests to the OLE-DB provider. There are several access methods for remote data, including opening the base table for read or issuing SQL queries against the remote data source. You can manage linked servers using SQL Server Management Studio graphical user interface or T-SQL system stored procedures. + `EXECUTE sp_addlinkedserver` to add new server definitions. + `EXECUTE sp_addlinkedserverlogin` to define security context. + `EXECUTE sp_linkedservers` or `SELECT * FROM sys.servers` system catalog view to retrieve meta data. + `EXECUTE sp_dropserver` to delete a linked server. You can access linked server data sources from T-SQL using a fully qualified, four-part naming scheme: `...`. Additionally, you can use the `OPENQUERY` row set function to explicitly invoke pass-through queries on the remote linked server. Also, you can use the `OPENROWSET` and `OPENDATASOURCE` row set functions for one-time remote data access without defining the linked server in advance. ### Syntax ``` EXECUTE sp_addlinkedserver [ @server= ] [ , [ @srvproduct= ] ] [ , [ @provider= ] ] [ , [ @datasrc= ] ] [ , [ @location= ] ] [ , [ @provstr= ] ] [ , [ @catalog= ] ]; ``` ### Examples Create a linked server to a local text file. ``` EXECUTE sp_addlinkedserver MyTextLinkedServer, N'Jet 4.0', N'Microsoft.Jet.OLEDB.4.0', N'D:\TextFiles\MyFolder', NULL, N'Text'; ``` Define security context. ``` EXECUTE sp_addlinkedsrvlogin MyTextLinkedServer, FALSE, Admin, NULL; ``` Use `sp_tables_ex` to list tables in a folder. ``` EXEC sp_tables_ex MyTextLinkedServer; ``` Issue a `SELECT` query using a four-part name. ``` SELECT * FROM MyTextLinkedServer...[FileName#text]; ``` For more information, see [sp\$1addlinkedserver (Transact-SQL)](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-ver15) and [Distributed Queries Stored Procedures (Transact-SQL)](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/distributed-queries-stored-procedures-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Querying data in remote databases is available through two primary options: + `dblink` database link function. + Foreign data wrapper (FDW) `postgresql_fdw` extension. The PostgreSQL foreign data wrapper extension is new to PostgreSQL and provides functionality similar to `dblink`. However, the PostgreSQL foreign data wrapper aligns closer with the SQL standard and can provide improved performance. ### Examples Load the `dblink` extension into PostgreSQL. ``` CREATE EXTENSION dblink; ``` Create a persistent connection to a remote PostgreSQL database using the `dblink_connect` function specifying a connection name (`myconn`), database name (`postgresql`), port (`5432`), host (`hostname`), user (`username`), and password (`password`). ``` SELECT dblink_connect ('myconn', 'dbname=postgres port=5432 host=hostname user=username password=password'); ``` You can use the connection to run queries against the remote database. Run a query using the previously created `myconn` connection by using the `dblink` function. The query returns the id and name columns from the employees table. On the remote database, you must specify the connection name and the SQL query to run as well as parameters and datatypes for selected columns (id and name in this example). ``` SELECT * from dblink ('myconn', 'SELECT id, name FROM EMPLOYEES') AS p(id int,fullname text); ``` Close the connection using the `dblink_disconnect` function. ``` SELECT dblink_disconnect('myconn'); ``` Alternatively, you can use the `dblink` function specifying the full connection string to the remote PostgreSQL database including the database name, port, hostname, username, and password. You can do this instead of using a previously defined connection. Make sure that you specify the SQL query to run as well as parameters and data types for the selected columns (id and name, in this example). ``` SELECT * from dblink ('dbname=postgres port=5432 host=hostname user=username password=password', 'SELECT id, name FROM EMPLOYEES') AS p(id int,fullname text); ``` DML commands are supported on tables referenced by the `dblink` function. For example, you can insert a new row and then delete it from the remote table. ``` SELECT * FROM dblink('myconn',$$INSERT into employees VALUES (3,'New Employees No. 3!')$$) AS t(message text); SELECT * FROM dblink('myconn',$$DELETE FROM employees WHERE id=3$$) AS t(message text); ``` Create a new `new_employees_table` local table by querying data from a remote table. ``` SELECT emps.* INTO new_employees_table FROM dblink('myconn','SELECT * FROM employees') AS emps(id int, name varchar); ``` Join remote data with local data. ``` SELECT local_emps.id , local_emps.name, s.sale_year, s.sale_amount FROM local_emps INNER JOIN dblink('myconn','SELECT * FROM working_hours') AS s(id int, hours worked int) ON local_emps.id = s.id; ``` Run DDL statements in the remote database. ``` SELECT * FROM dblink('myconn',$$CREATE table new_remote_tbl (a int, b text)$$) AS t(a text); ``` For more information, see [dblink](https://www.postgresql.org/docs/13/dblink.html) in the *PostgreSQL documentation*. # 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\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-0.png) | 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](chap-sql-server-aurora-pg.management.scheduledlambda.md). 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](https://docs.microsoft.com/en-us/sql/powershell/sql-server-powershell?view=sql-server-ver15), [Database Engine Scripting](https://docs.microsoft.com/en-us/sql/ssms/scripting/database-engine-scripting?view=sql-server-ver15), and [sqlcmd Utility](https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15) 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](https://www.pgadmin.org/). ### 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](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_Operations.html), [Data Types](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_Types.html), [Common Parameters](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/CommonParameters.html), and [Common Errors](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/CommonErrors.html) 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**. 1. 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](chap-sql-server-aurora-pg.hadr.essentials.md). 1. 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](https://docs.aws.amazon.com/cli/latest/reference/) and [Amazon Relational Database Service API Reference](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/Welcome.html).