# Configuration overview
<a name="chap-sql-server-aurora-pg.configuration"></a>

This topic provides conceptual content comparing various aspects of Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL in the context of database migration. You can gain insights into the differences between these two database systems in terms of session options, system variables, database options, features, and parameter configurations. The content explores how SQL Server concepts translate to Aurora PostgreSQL, covering areas such as date and time handling, locking mechanisms, transaction management, and query execution settings. By understanding these equivalencies and differences, database administrators and developers can more effectively plan and execute migrations from SQL Server to Aurora PostgreSQL, ensuring consistent functionality and performance in the new environment. This knowledge facilitates a smoother transition between the two database systems and helps in optimizing the Aurora PostgreSQL environment to match familiar SQL Server behaviors.

**Topics**
+ [

# Configuring upgrades
](chap-sql-server-aurora-pg.configuration.upgrades.md)
+ [

# Configuring session options
](chap-sql-server-aurora-pg.configuration.sessionoptions.md)
+ [

# Configuring database options
](chap-sql-server-aurora-pg.configuration.databaseoptions.md)
+ [

# Configuring server options
](chap-sql-server-aurora-pg.configuration.serveroptions.md)

# Configuring upgrades
<a name="chap-sql-server-aurora-pg.configuration.upgrades"></a>

This topic provides reference information on upgrading database instances, comparing the process for Microsoft SQL Server and Amazon Aurora PostgreSQL. You can use this information to plan and execute database upgrades, whether you’re working with on-premises SQL Server or managed Aurora PostgreSQL in the cloud. The guide walks you through the necessary steps for each platform, including prerequisites, upgrade procedures, and post-upgrade tasks.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|  N/A  |  N/A  |  N/A  |  N/A  | 

## SQL Server Usage
<a name="chap-sql-server-aurora-pg.configuration.upgrades.sqlserver"></a>

As a database administrator, from time to time a database upgrade is required. It can be either for security fix, bugs fixes, compliance, or new database features.

You can plan the database upgrade to minimize the database downtime and risk. You can perform an upgrade in-place or migrate to a new installation.

## Upgrade in-place
<a name="chap-sql-server-aurora-pg.configuration.upgrades.sqlserver.process"></a>

With this approach, we are retaining the current hardware and OS version by adding the new SQL Server binaries on the same server and then upgrade the SQL Server instance.

Before upgrading the database engine, review the SQL Server release notes for the intended target release version for any limitations and known issues to help you plan the upgrade.

In general, these will be the steps to perform the upgrade:

 **Prerequisite steps** 
+ Back up all SQL Server database files, so that you can restore them if required.
+ Run the appropriate Database Console Commands (DBCC CHECKDB) on databases to be upgraded to ensure that they are in a consistent state.
+ Ensure to allocate enough disk space for SQL Server components, in addition to user databases.
+ Disable all startup stored procedures as stored procedures processed at startup time might block the upgrade process.
+ Stop all applications, including all services that have SQL Server dependencies.

 **Steps for upgrade** 
+ Install new software.
  + Fix issues raised.
  + Set if you prefer to have automatic updates or not.
  + Select products install to upgrade, this is the new binaries installation.
  + Monitor the progress of downloading, extracting, and installing the Setup files.
+ Specify the instance of SQL Server to upgrade.
  + On the Select Features page, the features to upgrade will be preselected. The prerequisites for the selected features are displayed on the right-hand pane. SQL Server Setup will install the prerequisite that aren’t already installed during the installation step described later in this procedure.
+ Review upgrade plan before the actual upgrade.
+ Monitor installation progress.

 **Post upgrade tasks** 
+ Review summary log file for the installation and other important notes.
+ Register your servers.

### Migrate to a new installation
<a name="chap-sql-server-aurora-pg.configuration.sectionname.sqlserver.migratetoanewinstallation"></a>

This approach maintains the current environment while building a new SQL Server environment. This is usually done when migrating on a new hardware and with a new version of the operating system. In this approach migrate the system objects so that they are same as the existing environment, then migrate the user database either using backup and restore.

For more information, see [Upgrade Database Engine](https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-database-engine?view=sql-server-ver15) in the *SQL Server documentation*.

## PostgreSQL Usage
<a name="chap-sql-server-aurora-pg.configuration.sectionname.pg"></a>

After migrating your databases to Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL), you will still need to upgrade your database instance from time to time, for the same reasons you have done in the past, new features, bugs and security fixes.

In a managed service like Amazon Relational Database Service, the upgrade process is much easier and simpler compared to the on-premises Oracle process.

To determine the current Aurora PostgreSQL version being used, use the following AWS CLI command:

```
aws rds describe-db-engine-versions --engine aurora-postgresql --query '*[].[EngineVersion]' --output text --region your-AWS-Region
```

This can also be queried from the database, using the following queries:

```
SELECT AURORA_VERSION();

aurora_version
4.0.0

SHOW SERVER_VERSION;

server_version
12.4
```

For all Aurora and PostgreSQL versions mapping, see [Amazon Aurora PostgreSQL releases and engine versions](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Updates.20180305.html) in the *User Guide for Aurora*.

 AWS doesn’t apply major version upgrades on Amazon Aurora automatically. Major version upgrades contains new features and functionality which often involves system table and other code changes. These changes may not be backward-compatible with previous versions of the database so application testing are highly recommended.

Applying automatic minor upgrades can be set by configuring the Amazon Relational Database Service (Amazon RDS) instance to allow it.

You can use the following AWS CLI command on Linux to determine the current automatic upgrade minor versions.

```
aws rds describe-db-engine-versions --engine aurora-postgresql | grep -A 1 AutoUpgrade| grep -A 2 true |grep PostgreSQL | sort --unique | sed -e 's/"Description": "//g'
```

If no results are returned, there is no automatic minor version upgrade available and scheduled.

When enabled, the instance will be automatically upgraded during the scheduled maintenance window.

For major upgrades, this is the recommended process:
+ Have a version-compatible parameter group ready. If you are using a custom DB instance or DB cluster parameter group, you have two options:

  1. Specify the default DB instance, DB cluster parameter group, or both for the new DB engine version.

  1. Create your own custom parameter group for the new DB engine version.
**Note**  
If you associate a new DB instance or DB cluster parameter group as a part of the upgrade request, make sure to reboot the database after the upgrade completes to apply the parameters. If a DB instance needs to be rebooted to apply the parameter group changes, the instance’s parameter group status shows pending-reboot. You can view an instance’s parameter group status in the console or by using a CLI command such as `describe-db-instances` or `describe-db-clusters`.
+ Check for unsupported usage:

  1. Commit or roll back all open prepared transactions before attempting an upgrade. You can use the following query to verify that there are no open prepared transactions on your instance.

     ```
     SELECT count(*) FROM pg_catalog.pg_prepared_xacts;
     ```

  1. Remove all uses of the reg\$1 data types before attempting an upgrade. Except for `regtype` and `regclass`, you can’t upgrade the reg\$1 data types. The `pg_upgrade` utility can’t persist this data type, which is used by Amazon Aurora to do the upgrade. To verify that there are no uses of unsupported reg\$1 data types, use the following query for each database.

     ```
     SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_
     catalog.pg_attribute aWHERE c.oid = a.attrelid
     AND NOT a.attisdropped
     AND a.atttypid IN ('pg_catalog.regproc'::pg_catalog.regtype,
     'pg_catalog.regprocedure'::pg_catalog.regtype,
     'pg_catalog.regoper'::pg_catalog.regtype,
     'pg_catalog.regoperator'::pg_catalog.regtype,
     'pg_catalog.regconfig'::pg_catalog.regtype,
     'pg_catalog.regdictionary'::pg_catalog.regtype)
     AND c.relnamespace = n.oid
     AND n.nspname NOT IN ('pg_catalog', 'information_schema');
     ```
+ Perform a backup. The upgrade process creates a DB cluster snapshot of your DB cluster during upgrading.
+ Upgrade certain extensions to the latest available version before performing the major version upgrade. The extensions to update include the following:

  1. pgRouting

  1. postGIS
+ Run the following command for each extension that you are using.

  ```
  ALTER EXTENSION PostgreSQL-extension UPDATE TO 'new-version'
  ```

If you are upgrading versions older than PostgreSQL 12, there are a few more steps. For more information, see [Upgrading the PostgreSQL DB engine for Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html) in the *User Guide for Aurora*.

You can perform the actual upgrade through the console or AWS CLI.

 **Console** 

1. Sign in to the AWS Management Console and choose **RDS**.

1. In the navigation pane, choose **Databases**, and then choose the DB cluster that you want to upgrade.

1. Choose **Modify**. The **Modify DB cluster** page appears.

1. For **DB engine version**, choose the new version.

1. Choose **Continue** and check the summary of modifications.

1. To apply the changes immediately, choose **Apply immediately**. Choosing this option can cause an outage in some cases. For more information, see [Modifying an Amazon Aurora DB cluster](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Modifying.html) in the *User Guide for Aurora*.

1. On the confirmation page, review your changes. If they are correct, choose **Modify Cluster** to save your changes. Or choose **Back** to edit your changes or **Cancel** to cancel your changes.

 ** AWS CLI** 

For Linux, macOS, or Unix:

```
aws rds modify-db-cluster \
--db-cluster-identifier mydbcluster \
--engine-version new_version \
--allow-major-version-upgrade \
--no-apply-immediately
```

For Microsoft Windows:

```
aws rds modify-db-cluster ^
--db-cluster-identifier mydbcluster ^
--engine-version new_version ^
--allow-major-version-upgrade ^
--no-apply-immediately
```

## Summary
<a name="chap-sql-server-aurora-pg.configuration.sectionname.summary"></a>


| Phase | SQL Server Step |  Aurora PostgreSQL  | 
| --- | --- | --- | 
|  Prerequisite  |  Perform an instance backup.  |  Run Amazon RDS instance backup.  | 
|  Prerequisite  |  DBCC for consistent verification.  |  N/A  | 
|  Prerequisite  |  Validate disk size and free space.  |  N/A  | 
|  Prerequisite  |  Disable all startup stored procedures (if applicable).  |  N/A  | 
|  Prerequisite  |  Stop application and connection.  |  N/A  | 
|  Prerequisite  |  Install new software and fix prerequisites errors raised.  |  [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.configuration.upgrades.html)  | 
|  Prerequisite  |  Select instances to upgrade.  |  Select the right Amazon RDS instance.  | 
|  Prerequisite  |  Review pre-upgrade summary.  |  N/A  | 
|  Runtime  |  Monitor upgrade progress.  |  You can review from the console.  | 
|  Post-upgrade  |  Results.  |  You can review from the console.  | 
|  Post-upgrade  |  Register server.  |  N/A  | 
|  Post-upgrade  |  Test applications against the new upgraded database.  |  Test applications against the new upgraded database.  | 
|  Production deployment  |  Re-run all steps in a production environment.  |  Re-run all steps in a production environment.  | 

For more information, see [Upgrading the PostgreSQL DB engine for Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html) in the *User Guide for Aurora*.

# Configuring session options
<a name="chap-sql-server-aurora-pg.configuration.sessionoptions"></a>

This topic provides reference information comparing session options and system variables between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. You can understand how SQL Server’s session options translate to system variables, which is crucial for database administrators and developers migrating from SQL Server to Aurora PostgreSQL.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-2.png)   |  N/A  |  N/A  |   `SET` options are significantly different, except for transaction isolation control.  | 

## SQL Server Usage
<a name="chap-sql-server-aurora-pg.configuration.sessionoptions.sqlserver"></a>

Session options in SQL Server is a collection of run-time settings that control certain aspects of how the server handles data for individual sessions. A session is the period between a login event and a disconnect event or an `exec sp_reset_connection` command for connection pooling.

Each session may have multiple run scopes, which are all the statements before the GO keyword used in SQL Server Management Studio scripts, or any set of commands sent as a single run batch by a client application. Each run scope may contain additional sub-scopes. For example, scripts calling stored procedures or functions.

You can set the global session options, which all run scopes use by default, using the `SET` T-SQL command. Server code modules such as stored procedures and functions may have their own run context settings, which are saved along with the code to guarantee the validity of results.

Developers can explicitly use `SET` commands to change the default settings for any session or for an run scope within the session. Typically, client applications send explicit `SET` commands upon connection initiation.

You can view the metadata for current sessions using the `sp_who_system` stored procedure and the `sysprocesses` system table.

**Note**  
To change the default setting for SQL Server Management Studio, choose **Tools**, **Options**, **Query Execution**, **SQL Server**, **Advanced**.

### Syntax
<a name="chap-sql-server-aurora-pg.configuration.sessionoptions.sqlserver.syntax"></a>

Syntax for the `SET` command:

```
SET
Category Setting
Date and time    DATEFIRST | DATEFORMAT
Locking          DEADLOCK_PRIORITY | SET LOCK_TIMEOUT
Miscellaneous    CONCAT_NULL_YIELDS_NULL | CURSOR_CLOSE_ON_COMMIT | FIPS_FLAGGER | SET IDENTITY_INSERT | LANGUAGE | OFFSETS | QUOTED_IDENTIFIER
Query Execution  ARITHABORT | ARITHIGNORE | FMTONLY | NOCOUNT | NOEXEC | NUMERIC_ROUNDABORT | PARSEONLY | QUERY_GOVERNOR_COST_LIMIT | ROWCOUNT | TEXTSIZE
ANSI             ANSI_DEFAULTS | ANSI_NULL_DFLT_OFF | ANSI_NULL_DFLT_ON | ANSI_NULLS | ANSI_PADDING | ANSI_WARNINGS
Execution Stats  FORCEPLAN | SHOWPLAN_ALL | SHOWPLAN_TEXT | SHOWPLAN_XML | STATISTICS IO | STATISTICS XML | STATISTICS PROFILE | STATISTICS TIME
Transactions     IMPLICIT_TRANSACTIONS | REMOTE_PROC_TRANSACTIONS | TRANSACTION ISOLATION LEVEL | XACT_ABORT
```

For more information, see [SET Statements (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.

### SET ROWCOUNT for DML Deprecated Setting
<a name="chap-sql-server-aurora-pg.configuration.sessionoptions.sqlserver.setrowcount"></a>

The SET ROWCOUNT for DML statements has been deprecated as of SQL Server 2008. 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*.

For SSQL Server version 2008 R2 and lower, you could limit the number of rows affected by `INSERT`, `UPDATE`, and `DELETE` operations using `SET ROWCOUNT`. For example, it is a common practice in SQL Server to batch large `DELETE` or `UPDATE` operations to avoid transaction logging issues.

The following example loops and deletes rows where `ForDelete` is set to 1, but only 5000 rows at a time in separate transactions (assuming the loop isn’t within an explicit transaction).

```
SET ROWCOUNT 5000;
WHILE @@ROWCOUNT > 0
BEGIN
  DELETE FROM MyTable
  WHERE ForDelete = 1;
END
```

Starting from SQL Server 2012, `SET ROWCOUNT` is ignored for `INSERT`, `UPDATE`, and `DELETE` statements. You can achieve the same functionality using `TOP`. You can convert `TOP` to the Aurora PostgreSQL `LIMIT`.

For example, you can rewrite the preceding code as:

```
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP (5000)
  FROM MyTable
  WHERE ForDelete = 1;
END
```

 AWS Schema Conversion Tool can convert this syntax automatically.

### Examples
<a name="chap-sql-server-aurora-pg.configuration.sessionoptions.sqlserver.examples"></a>

Use SET within a stored procedure.

```
CREATE PROCEDURE <ProcedureName>
AS
BEGIN
  <Some non-critical transaction code>
  SET TRANSACTION_ISOLATION_LEVEL SERIALIZABLE;
  SET XACT_ABORT ON;
  <Some critical transaction code>
END
```

Explicit `SET` commands affect their run scope and sub scopes. After the scope terminates and the procedure code exits, the calling scope resumes its original settings used before the calling the stored procedure.

For more information, see [SET Statements (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql?view=sql-server-ver15) in the *SQL Server documentation*.

## PostgreSQL Usage
<a name="chap-sql-server-aurora-pg.configuration.sessionoptions.pg"></a>

 Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) supports hundreds of Server System Variables to control server behavior and the global and session levels.

PostgreSQL provides session-modifiable parameters that are configured using the `SET SESSION` command. Configuration of parameters using `SET SESSION` will only be applicable in the current session. To view the list of parameters that can be set with `SET SESSION`, you can query `pg_settings`:

```
SELECT * FROM pg_settings where context = 'user';
```

Examples of commonly used session parameters:
+  `client_encoding` configures the connected client character set.
+  `force_parallel_mode` forces use of parallel query for the session.
+  `lock_timeout` sets the maximum allowed duration of time to wait for a database lock to release.
+  `search_path` sets the schema search order for object names that aren’t schema-qualified.
+  `transaction_isolation` sets the current Transaction Isolation Level for the session.

You can view Aurora PostgreSQL variables using the PostgreSQL command line utility, Amazon Aurora database cluster parameters, Amazon Aurora database instance parameters, or SQL Server interface system variables.

### Converting from SQL Server 2008 SET ROWCOUNT for DML operations
<a name="chap-sql-server-aurora-pg.configuration.sessionoptions.pg.rowcount"></a>

The use of `SET ROWCOUNT` for DML operations is deprecated as of SQL Server 2008 R2. Code that uses the `SET ROWCOUNT` syntax can’t be converted automatically.

You can either rewrite the code to use `TOP` before running AWS SCT, or manually change it afterward.

Consider the example that is used to batch `DELETE` operations in SQL Server using `TOP`:

```
WHILE @@ROWCOUNT > 0
BEGIN
  DELETE TOP (5000)
  FROM MyTable
  WHERE ForDelete = 1;
END
```

You can rewrite the preceding example to use the Aurora PostgreSQL `LIMIT` clause:

```
WHILE row_count() > 0 LOOP
  DELETE FROM num_test
  WHERE ctid IN (
  SELECT ctid
  FROM num_test
  LIMIT 10)
END LOOP;
```

### Examples
<a name="chap-sql-server-aurora-pg.configuration.sessionoptions.pg.examples"></a>

Change the time zone of the connected session.

```
SET SESSION DateStyle to POSTGRES, DMY;
SET

SELECT NOW();

now
Sat 09 Sep 11:03:43.597202 2017 UTC
(1 row)

SET SESSION DateStyle to ISO, MDY;
SET

SELECT NOW();

now
2017-09-09 11:04:01.3859+00
(1 row)
```

## Summary
<a name="chap-sql-server-aurora-pg.configuration.sessionoptions.summary"></a>

The following table summarizes commonly used SQL Server session options and their corresponding Aurora PostgreSQL system variables.


| Category | SQL Server |  Aurora PostgreSQL  | 
| --- | --- | --- | 
|  Date and time  |   `DATEFIRST`   |  Use `DOW` in queries  | 
|  Date and time  |   `DATEFORMAT`   |   `DateStyle`   | 
|  Locking  |   `LOCK_TIMEOUT`   |   `lock_timeout`   | 
|  Transactions  |   `IMPLICIT_TRANSACTIONS`   |   `SET TRANSACTION`   | 
|  Transactions  |   `TRANSACTION ISOLATION LEVEL`   |   `BEGIN TRANSACTION ISOLATION LEVEL`   | 
|  Query run  |   `IDENTITY_INSERT`   |  See [Sequences and Identity](chap-sql-server-aurora-pg.tsql.sequences.md).  | 
|  Query run  |   `LANGUAGE`   |   `lc_monetary`, `lc_numeric`, or `lc_time`   | 
|  Query run  |   `QUOTED_IDENTIFIER`   |  N/A  | 
|  Query run  |   `NOCOUNT`   |  N/A and not needed  | 
|  Run stats  |   `SHOWPLAN_ALL`, `TEXT`, `XML`, `STATISTICS IO`, `PROFILE`, and `TIME`   |  See [Run Plans](chap-sql-server-aurora-pg.tuning.plans.md).  | 
|  Miscellaneous  |   `CONCAT_NULL_YIELDS_NULL`   |  N/A  | 
|  Miscellaneous  |   `ROWCOUNT`   |  Use `LIMIT` within `SELECT`.  | 

For more information, see [SET](https://www.postgresql.org/docs/13/sql-set.html) in the *PostgreSQL documentation*.

# Configuring database options
<a name="chap-sql-server-aurora-pg.configuration.databaseoptions"></a>

This topic provides reference information about the differences in database options and features between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. You can understand how SQL Server’s database-level options and features translate to cluster and instance-level parameters. The topic helps you grasp the architectural differences between the two database systems, particularly in terms of database configuration, security settings, and high availability options.


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

## SQL Server Usage
<a name="chap-sql-server-aurora-pg.configuration.databaseoptions.sqlserver"></a>

SQL Server provides database level options that you can set using the `ALTER DATABASE …​ SET` command. You can use these settings to:
+ Set default session options. For more information, see [Session Options](chap-sql-server-aurora-pg.configuration.sessionoptions.md).
+ Enable or disable database features such as `SNAPSHOT_ISOLATION`, `CHANGE_TRANCKING`, and `ENABLE_BROKER`.
+ Configure high availability and disaster recovery options such as always on availability groups.
+ Configure security access control such as restricting access to a single user, setting the database offline, or setting the database to read-only.

### Syntax
<a name="chap-sql-server-aurora-pg.configuration.databaseoptions.sqlserver.syntax"></a>

Syntax for setting database options:

```
ALTER DATABASE { <database name> } SET { <option> [ ,...n ] };
```

### Examples
<a name="chap-sql-server-aurora-pg.configuration.databaseoptions.sqlserver.examples"></a>

Set a database to read-only and use ARITHABORT by default.

```
ALTER DATABASE Demo SET READ_ONLY, ARITHABORT ON;
```

Set a database to use automatic statistic creation.

```
ALTER DATABASE Demo SET AUTO_CREATE_STATISTICS ON;
```

Set a database offline immediately.

```
ALTER DATABASE DEMO SET OFFLINE WITH ROLLBACK IMMEDIATE;
```

For more information, see [ALTER DATABASE SET options (Transact-SQL)](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15) in the *SQL Server documentation*.

## PostgreSQL Usage
<a name="chap-sql-server-aurora-pg.configuration.databaseoptions.pg"></a>

 Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) supports `CREATE SCHEMA` and `CREATE DATABASE` statements.

As with SQL Server, Aurora PostgreSQL does have the concept of an instance hosting multiple databases, which in turn contain multiple schemas. Objects in Aurora PostgreSQL are referenced as a three-part name: `<database>.<schema>.<object>`.

Database options are related to the cluster-level parameters which are managed by the AWS Cluster Parameter Groups. You can find some SQL Server equivalent parameters at the instance level in the AWS Database Parameter Group.

Datable options are being compared to AWS Database Parameter Group and Server Options are being compared to AWS Cluster Parameter Group. For more information, see [Server Options](chap-sql-server-aurora-pg.configuration.serveroptions.md).

# Configuring server options
<a name="chap-sql-server-aurora-pg.configuration.serveroptions"></a>

This topic provides reference information about parameter configuration in SQL Server and PostgreSQL, specifically in the context of migrating from SQL Server 2019 to Amazon Aurora PostgreSQL. You can understand the differences in how server-level settings and parameters are managed between these two database systems.


| 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 Cluster and Database/Cluster Parameter.  | 

## SQL Server Usage
<a name="chap-sql-server-aurora-pg.configuration.serveroptions.sqlserver"></a>

SQL Server provides server-level settings that affect all databases and all sessions. You can modify these settings using the `sp_configure` system stored procedure.

You can use server options to perform the following configuration tasks:
+ Define hardware utilization such as memory management, affinity mask, priority boost, network packet size, and soft Non-Uniform Memory Access (NUMA).
+ Alter run time global values such as recovery interval, remote login timeout, optimization for ad-hoc workloads, and cost threshold for parallelism.
+ Enable and disable global features such as C2 Audit, OLE, procedures, CLR procedures, and allow trigger recursion.
+ Configure global security settings such as server authentication mode, remote access, shell access with `xp_cmdshell`, CLR access level, and database chaining.
+ Set default values for sessions such as user options, default language, backup compression, and fill factor.

Some settings require an explicit `RECONFIGURE` command to apply the changes to the server. High risk settings require `RECONFIGURE WITH OVERRIDE` for the changes to be applied. Some advanced options are hidden by default. To view and modify these settings, set `show advanced options` to 1 and run `sp_configure`.

**Note**  
Server audits are managed with the T-SQL commands `CREATE` and `ALTER SERVER AUDIT`.

### Syntax
<a name="chap-sql-server-aurora-pg.configuration.serveroptions.sqlserver.syntax"></a>

```
EXECUTE sp_configure <option>, <value>;
```

### Examples
<a name="chap-sql-server-aurora-pg.configuration.serveroptions.sqlserver.examples"></a>

Limit server memory usage to 4 GB.

```
EXECUTE sp_configure 'show advanced options', 1;
```

```
RECONFIGURE;
```

```
sp_configure 'max server memory', 4096;
```

```
RECONFIGURE;
```

Allow command shell access from T-SQL.

```
EXEC sp_configure 'show advanced options', 1;
```

```
RECONFIGURE;
```

```
EXEC sp_configure 'xp_cmdshell', 1;
```

```
RECONFIGURE;
```

View the current values.

```
EXECUTE sp_configure
```

For more information, see [Server Configuration Options (SQL Server)](https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-configuration-options-sql-server?view=sql-server-ver15) in the *SQL Server documentation*.

## PostgreSQL Usage
<a name="chap-sql-server-aurora-pg.configuration.serveroptions.pg"></a>

When running PostgreSQL databases as Amazon Aurora Clusters, Parameter Groups are used to change to cluster-level and database-level parameters.

Most of the PostgreSQL parameters are configurable in an Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) cluster, but some are disabled and can’t be modified. Because Amazon Aurora clusters restrict access to the underlying operating system, modification to PostgreSQL parameters must be made using Parameter Groups.

 Amazon Aurora is a cluster of database instances and, as a direct result, some of the PostgreSQL parameters apply to the entire cluster while other parameters apply only to a particular database instance.


|  Aurora PostgreSQL parameter class | Controlled by | 
| --- | --- | 
|   **Cluster-level parameters**  Single cluster parameter group for each Amazon Aurora Cluster.  |  Managed by cluster parameter groups. For example, [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.configuration.serveroptions.html)  | 
|   **Database instance-level parameters**  You can associate every instance in an Amazon Aurora cluster with a unique database parameter group.  |  Managed by database parameter groups. For example, [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.configuration.serveroptions.html)  | 

New parameters in PostgreSQL 10:

1.  `enable_gathermerge` enables the gather merge run plan.

1.  `max_parallel_workers` stands for the maximum number of parallel workers process.

1.  `max_sync_workers_per_subscription` stands for the maximum number of synchronous workers for subscription.

1.  `wal_consistency_checking` checks consistency of WAL on the standby instance (can’t be set in Aurora PostgreSQL).

1.  `max_logical_replication_workers` stands for the maximum number of logical replication worker process.

1.  `max_pred_locks_per_relation` stands for the maximum number of records that you can predicate-lock before locking the entire relation.

1.  `max_pred_locks_per_page` stands for the maximum number of records that you can predicate-lock before locking the entire page.

1.  `min_parallel_table_scan_size` stands for the minimum table size to consider parallel table scan.

1.  `min_parallel_index_scan_size` stands for the minimum table size to consider parallel index scan.

### Examples
<a name="chap-sql-server-aurora-pg.configuration.serveroptions.pg.examples"></a>

 **To create and configure a new parameter group** 

1. Sign in to the AWS Management Console and choose **RDS**.

1. Choose **Parameter groups**.
**Note**  
You can’t edit the default parameter group. Create a custom parameter group to apply changes to your Amazon Aurora cluster and its database instances.

    ![\[Parameter groups\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-parameter-groups.png) 

1. Select the DB family from the Parameter group family drop-down list.

1. For **Type**, select the DB parameter group.

1. Choose **Create**.

 **To modify an existing parameter group** 

1. Sign in to the AWS Management Console and choose **RDS**.

1. Choose **Parameter groups**.

1. Choose the name of the parameter to edit.

1. Choose **Edit parameters**.

1. Change parameter values and choose **Save changes**.

For more information, see [Working with parameter groups](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html) in the *Amazon RDS User Guide*.