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

This chapter reference information for configuring software and resource settings when migrating from Microsoft SQL Server 2019 to Amazon Aurora MySQL, focusing on database upgrades, session options, system variables, and configuration settings. These topics describe how each platform handles runtime settings, database-level options, and server configurations, highlighting the unique approaches of Aurora MySQL such as parameter groups and cluster-level settings.

**Topics**
+ [

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

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

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

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

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

This topic provides reference content about upgrading database instances in Amazon Aurora MySQL. You can learn about the reasons for database upgrades, the differences between upgrading SQL Server and Aurora MySQL, and the process of performing upgrades in Aurora MySQL.


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

The database upgrade approach can be planned 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-mysql.configuration.upgrades.sqlserver.upgrade"></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:

 **Prerequisites steps** 
+ Back up all SQL Server database files, so that it can be restored if required.
+ Run the appropriate Database Console Commands (DBCC CHECKDB) on databases to be upgraded to make sure 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-mysql.configuration.upgrades.sqlserver.migrate"></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*.

## MySQL Usage
<a name="chap-sql-server-aurora-mysql.configuration.upgrades.mysql"></a>

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

In a managed service like Amazon Relational Database Service (Amazon RDS), the upgrade process is much easier and simpler compare to the on-prem SQL Server process.

To determine the current Aurora MySQL version being used, you can use the following AWS CLI command:

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

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

```
SELECT AURORA_VERSION();
```

In an Aurora MySQL version number scheme, for example 2.08.1, the first digit represents the major version. Aurora MySQL version 1 is compatible with MySQL 5.6 and Aurora MySQL version 2 is compatible with MySQL 5.7. To find all Amazon Aurora and MySQL versions mapping, see [Database engine updates for Amazon Aurora MySQL version 2](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.20Updates.html).

 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 applications testing is highly recommended.

Applying automatic minor upgrades can be set by configuring the Amazon RDS instance to allow it.

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

```
aws rds describe-db-engine-versions --output=table --engine mysql --engine-version minor-version --region region
```

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

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

If you want to upgrade your cluster to a compatible cluster, you can do so by running an upgrade process on the cluster itself. This kind of upgrade is an in-place upgrade, in contrast to upgrades that you do by creating a new cluster. The upgrade is relatively fast because it doesn’t require copying all your data to a new cluster volume. In place upgrade preserves the endpoints and set of DB instances for your cluster.

To verify application compatibility, performance and maintenance procedures for the upgraded cluster, you can perform a simulation of the upgrade by doing following
+ Clone a cluster.
+ Perform an in-place upgrade of the cloned cluster.
+ Test applications, performance and so on, using the cloned cluster.
+ Resolve any issues, adjust your upgrade plans to account for them.
+ Once all the testing looks good, you can perform the in-place upgrade for your production cluster.

For major upgrades, this is the recommended:
+ Check for open XA transactions by running the `XA RECOVER` statement. Commit or Rollback the XA transactions before starting the upgrade.
+ Check for DDL statements by running a `SHOW PROCESSLIST` statement and looking for `CREATE`, `DROP`, `ALTER`, `RENAME`, and `TRUNCATE` statements in the output. Allow all DDLs to finish before starting the upgrade.
+ Check for any uncommitted rows by querying the `INFORMATION_SCHEMA.INNODB_TRX` table. The table contains one row for each transaction. Let the transaction complete or shut down applications that are submitting these changes.

 Aurora MySQL performs a major version upgrade in multiple steps. As each step begins, Aurora MySQL records an event. You can monitor the current status and events as they occur on the Events page in the Amazon RDS console.

 Amazon Aurora performs a series of checks before beginning the upgrade process. If any issues are detected during these checks, resolve the issue identified in the event details and restart the upgrade process.

 Aurora takes the cluster offline, performs a similar set of tests as in the previous step. If no new issues are identified, then Aurora moves with the next step. If any issues are detected during these checks, resolve the issue identified in the event details and restart the upgrade process again.

 Aurora backups up the MySQL cluster by creating a snapshot of the cluster volume.

 Aurora clones the cluster volume. If any issues are encountered during the upgrade, Aurora reverts to the original data from the cloned cluster volume and brings the cluster back online.

 Aurora performs a clean shutdown and it rolls back any uncommitted transactions.

 Aurora upgrades the engine version. It installs the binary for the new engine version and uses the writer DB instance to upgrade your data to new to MySQL compatible format. During this stage, Aurora modifies the system tables and performs other conversions that affect the data in your cluster volume.

The upgrade process is completed. Aurora records a final event to indicate that the upgrade process completed successfully. Now DB cluster is running the new major version.

Upgrade can be done through the AWS Console or AWS CLI.

### Console
<a name="chap-sql-server-aurora-mysql.configuration.upgrades.mysql.console"></a>

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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).

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

### AWS CLI
<a name="chap-sql-server-aurora-mysql.configuration.upgrades.mysql.cli"></a>

To upgrade the major version of an Aurora MySQL DB cluster, use the AWS CLI `modify-db-cluster` command with the following required parameters:

For Linux, macOS, or Unix:

```
aws rds modify-db-cluster \
--db-cluster-identifier sample-cluster \
--engine aurora-mysql \
--engine-version 5.7.mysql_aurora.2.09.0 \
--allow-major-version-upgrade \
--apply-immediately
```

For Windows:

```
aws rds modify-db-cluster ^
--db-cluster-identifier sample-cluster ^
--engine aurora-mysql ^
--engine-version 5.7.mysql_aurora.2.09.0 ^
--allow-major-version-upgrade ^
--apply-immediately
```

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


| Phase | SQL Server Step |  Aurora MySQL  | 
| --- | --- | --- | 
|  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  |  Commit or rollback uncommitted transactions  | 
|  Prerequisite  |  Select instances to upgrade  |  Select right Amazon RDS instance  | 
|  Prerequisite  |  Review pre-upgrade summary  |  N/A  | 
|  Runtime  |  Monitor upgrade progress  |  Can be reviewed from the console  | 
|  Post-upgrade  |  Results  |  Can be reviewed from the console  | 
|  Post-upgrade  |  Register server  |  N/A  | 
|  Post-upgrade  |  Test applications again the new upgraded database  |  Same  | 
|  Production deployment  |  Re-run all steps in a production environment  |  Same  | 

For more information, see [Upgrading Amazon Aurora MySQL DB clusters](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.Upgrading.html) in the *User Guide for Aurora*.

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

This topic provides reference information about session options and system variables in SQL Server and Amazon Aurora MySQL. You can use this content to understand the differences and similarities between how these two database systems handle runtime settings that control server behavior.


| 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-mysql-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-mysql.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 the `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-mysql.configuration.sessionoptions.sqlserver.syntax"></a>

The following example includes categories and settings for the `SET` command:

```
SET
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-mysql.configuration.sessionoptions.sqlserver.setrowcount"></a>

The SET ROWCOUNT for DML statements has been deprecated as of SQL Server 2008.

Up to and including SQL Server 2008 R2, 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 having `ForDelete` 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 with SQL Server 2012, `SET ROWCOUNT` is ignored for `INSERT`, `UPDATE` and `DELETE` statements.

You can achieve the same functionality using `TOP`, which can be converted to `LIMIT` in Aurora MySQL. For example, you can rewrite the preceding example as shown following:

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

 AWS Schema Conversion Tool (AWS SCT automatically converts this example to Aurora MySQL.

### Examples
<a name="chap-sql-server-aurora-mysql.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
```

**Note**  
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*.

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

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

Use the `SHOW VARIABLES` command to view a list of all variables.

```
SHOW SESSION VARIABLES;
-- 532 rows returned
```

**Note**  
 Aurora MySQL 5.7 provides additional variables that don’t exist in MySQL 5.7 standalone installations. These variables are prefixed with Amazon Aurora or AWS.

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

To view all sessions, use the `SHOW PROCESSLIST` command or the `information_schema PROCESSLIST` view, which displays information such as session current status, default database, host name, and application name.

**Note**  
Unlike standalone installations of MySQL, Amazon Aurora doesn’t provide access to the configuration file containing system variable defaults. Cluster-level parameters are managed in database cluster parameter groups and instance-level parameters are managed in database parameter groups. In Aurora MySQL, some parameters from the full base set of standalone MySQL installations can’t be modified and others were removed. See Server Options for a walkthrough of creating a custom parameter group.

### Converting from SQL Server 2008 SET ROWCOUNT for DML operations
<a name="chap-sql-server-aurora-mysql.configuration.sessionoptions.mysql.setrowcount"></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 to use `TOP` before running AWS SCT, or manually change it afterward.

The following example runs 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 `LIMIT` clause in Aurora MySQL.

```
WHILE row_count() > 0
DO
    DELETE
    FROM MyTable
    WHERE ForDelete = 1
    LIMIT 5000;
END WHILE;
```

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

View the metadata for all processes.

```
SELECT *
FROM information_schema.PROCESSLIST;
```

```
SHOW PROCESSLIST;
```

Use the `SET` command to change session isolation level and SQL mode.

```
SET sql_mode = 'ANSI_QUOTES';
SET SESSION TRANSACTION ISOLATION LEVEL 'READ-COMMITTED';
```

Set isolation level using a system variable.

```
SET SESSION tx_isolation = 'READ-COMMITTED'
```

The `SET SESSION` command is the equivalent to the `SET` command in T-SQL.

However, there are far more configurable parameters in Aurora MySQL than in SQL Server.

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

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


| Category | SQL Server |  Aurora MySQL  | Comments | 
| --- | --- | --- | --- | 
|  Date and time  |   `DATEFIRST`   `DATEFORMAT`   |   `default_week_format`   `date_format` (deprecated)  |   `default_week_format` operates different than `DATEFIRST`. You can use only Sunday and Monday as the start of the week. It also controls what is considered week one of the year and whether returned `WEEK` value is zero- based, or one-based. There is no alternative to the deprecated `date_format` variable.  | 
|  Locking  |   `LOCK_TIMEOUT`   |   `lock_wait_timeout`   |  Set in database parameter groups.  | 
|  ANSI  |   `ANSI_NULLS`   `ANSI_PADDING`   |  N/A  `PAD_CHAR_TO_FULL_LENGTH`   |  Set with the sql\$1mode system variable.  | 
|  Transactions  |   `IMPLICIT_TRANSACTIONS`   `TRANSACTION ISOLATION LEVEL`   |   `autocommit`   `SET SESSION TRANSACTION ISOLATION LEVEL`   |  The default for Aurora MySQL, as in SQL server, is to commit automatically. Syntax is compatible except the addition of the `SESSION` keyword.  | 
|  Query run  |   `IDENTITY_INSERT`   `LANGUAGE`   `QUOTED_IDENTIFIER`   `NOCOUNT`   |  See [Identity and Sequences](chap-sql-server-aurora-mysql.tsql.identitysequences.md)   `lc_time_names`   `ANSI_QUOTES`  N/A and not needed  |   `lc_time_names` are set in a database parameter group. `lc_messages` isn’t supported in Aurora MySQL. `ANSI_QUOTES` is a value for the `sql_mode` parameter. Aurora MySQL doesn’t add row count information to the errors collection.  | 
|  Runtime stats  |   `SHOWPLAN_ALL`, `TEXT`, and `XML`   `STATISTICS IO`, `XML`, `PROFILE`, and `TIME`   |  See [Run Plans](chap-sql-server-aurora-mysql.tuning.plans.md)   |  | 
|  Miscellaneous  |   `CONCAT_NULL_YIELDS_NULL`   `ROWCOUNT`   |  N/A  `sql_select_limit`   |   Aurora MySQL always returns NULL for any NULL concatenation operation. `sql_select_limit` only affects `SELECT` statements unlike `ROWCOUNT`, which also affects all DML.  | 

For more information, see [Server System Variables](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html) in the *MySQL documentation*.

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

This topic provides reference information about database options in Microsoft SQL Server and how they differ from Amazon Aurora MySQL. You can understand the key differences in database configuration between these two database systems.


| 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-mysql-migration-playbook/images/pb-compatibility-0.png)   |  N/A  |  N/A  |  SQL Server database options are inapplicable to Aurora MySQL.  | 

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

SQL Server provides database level options that can be set using the `ALTER DATABASE …​ SET` command.

These settings enable you to:
+ Set default session options. For more information, see [Session Options](chap-sql-server-aurora-mysql.configuration.sessionoptions.md).
+ Turn on or turn off 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-mysql.configuration.databaseoptions.sqlserver.syntax"></a>

Use the following syntax to set database options:

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

### Examples
<a name="chap-sql-server-aurora-mysql.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*.

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

The concept of a database in Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) is different than SQL Server. In Aurora MySQL, a database is synonymous with a schema. Therefore, the notion of database options isn’t applicable to Aurora MySQL.

**Note**  
 Aurora MySQL has two settings that are saved with the database/schema: the default character set, and the default collation for creating new objects.

## Migration Considerations
<a name="chap-sql-server-aurora-mysql.configuration.databaseoptions.summary"></a>

For migration considerations, see [Server Options](chap-sql-server-aurora-mysql.configuration.serveroptions.md).

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

This topic provides reference content comparing server and database configuration options between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can understand the key differences in how these database systems manage global settings, runtime configurations, and security parameters.


| 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-mysql-migration-playbook/images/pb-compatibility-1.png)   |  N/A  |  N/A  |  Use cluster and database parameter groups.  | 

## SQL Server Usage
<a name="chap-sql-server-aurora-mysql.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.
+ Turn on and turn off 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 through the T-SQL commands `CREATE` and `ALTER SERVER AUDIT`.

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

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

### Examples
<a name="chap-sql-server-aurora-mysql.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 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*.

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

The concept of an database in Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) is different than SQL Server. For Aurora MySQL, the terms database and schema are synonymous. Therefore, the concept of database options does isn’t applicable to Aurora MySQL.

The Aurora MySQL equivalent of SQL Server database and server options are Server System Variables, which are run time settings you can modify using one of the following approaches:
+ MySQL command line utility.
+  Aurora DB Cluster and DB Instance Parameters.
+ System variables used by the SQL `SET` command.

Compared to SQL Server, Aurora MySQL provides a much wider range of server settings and configurations. For a full list of the options available in Aurora MySQL, see the links at the end of this section. The Aurora MySQL default parameter group lists more than 250 different parameters.

**Note**  
Unlike standalone installations of MySQL, Amazon Aurora doesn’t provide file system access to the configuration file. Cluster-level parameters are managed in database cluster parameter groups. Instance-level parameters are managed in database parameter groups. Also, in Aurora MySQL some parameters from the full base set of standalone MySQL installations can’t be modified and others were removed. Many parameters are viewable but not modifiable.

SQL Server and Aurora MySQL are completely different engines. Except for a few obvious settings such as max server memory which has an equivalent of `innodb_buffer_pool_size`, most of the Aurora MySQL parameter settings aren’t compatible with SQL Server.

In most cases, you should use the default parameter groups because they are optimized for common use cases. Amazon Aurora is a cluster of DB instances and, as a direct result, some of the MySQL parameters apply to the entire cluster while other parameters apply only to particular database instances in the cluster. The following table describes how Aurora MySQL parameters are controlled:


|  Aurora MySQL Parameter Class | Controlled by | 
| --- | --- | 
|  Cluster-level parameters Single cluster parameter group for each Amazon Aurora cluster.  |  Managed by cluster parameter groups. For example, `aurora_load_from_s3_role`, `default_password_lifetime`, `default_storage_engine`.  | 
|  Database instance-level parameters You can associate every instance in your Amazon Aurora cluster with a unique database parameter group.  |  Managed by database parameter groups. For example, `autocommit`, `connect_timeout`, `innodb_change_buffer_max_size`.  | 

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

Server-level options are set with the `SET GLOBAL` command.

```
SET GLOBAL <option> = <Value>;
```

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

 **Modify compression level** 

Decrease compression level to reduce CPU usage.

```
SET GLOBAL innodb_compression_level = 5;
```

 **Create parameter groups** 

The following walkthrough demonstrates how to create and configure the Amazon Aurora database and cluster parameter groups:

1. Navigate to **Parameter group** in the Amazon RDS service of the AWS Console.

1. Choose **Create parameter group**.
**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.

1. For **Parameter group family**, choose `aurora-mysql5.7`.

1. For **Type**, choose **DB Parameter Group**. Another option is to choose **Cluster Parameter Group** to modify cluster parameters.

1. Choose **Create**.

 **Modify a parameter group** 

The following walkthrough demonstrates how to modify an existing parameter group

1. Navigate to **Parameter group** in the Amazon RDS service of the AWS Console.

1. Choose the name of the parameter group 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 Relational Database Service User Guide* and [Server System Variables](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html) in the *MySQL documentation*.