# Connect Microsoft SQL Servers with AWS Schema Conversion Tool
You can use AWS SCT to convert schemas, database code objects, and application code from SQL Server to the following targets:
+ Amazon RDS for MySQL
+ Amazon Aurora MySQL-Compatible Edition
+ Amazon RDS for PostgreSQL
+ Amazon Aurora PostgreSQL-Compatible Edition
+ Amazon RDS for SQL Server
+ Amazon RDS for MariaDB
**Note**
AWS SCT does not support using Amazon RDS for SQL server as a source.
You can use AWS SCT to create an assessment report for the migration of schemas, database code objects, and application code from SQL Server to Babelfish for Aurora PostgreSQL, as described following.
**Topics**
+ [Privileges for Microsoft SQL Server as a source](#CHAP_Source.SQLServer.Permissions)
+ [Using Windows Authentication when using Microsoft SQL Server as a source](#CHAP_Source.SQLServer.Permissions.WinAuth)
+ [Connecting to SQL Server as a source](#CHAP_Source.SQLServer.Connecting)
+ [Converting SQL Server to MySQL](CHAP_Source.SQLServer.ToMySQL.md)
+ [Migrating from SQL Server to PostgreSQL with AWS Schema Conversion Tool](CHAP_Source.SQLServer.ToPostgreSQL.md)
+ [Migrating from SQL Server to Amazon RDS for SQL Server with AWS Schema Conversion Tool](CHAP_Source.SQLServer.ToRDSSQLServer.md)
## Privileges for Microsoft SQL Server as a source
The privileges required for Microsoft SQL Server as a source are as follows:
+ VIEW DEFINITION
+ VIEW DATABASE STATE
The `VIEW DEFINITION` privilege enables users that have public access to see object definitions. AWS SCT uses the `VIEW DATABASE STATE` privilege to check the features of the SQL Server Enterprise edition.
Repeat the grant for each database whose schema you are converting.
In addition, grant the following privileges on the `master` database:
+ VIEW SERVER STATE
+ VIEW ANY DEFINITION
AWS SCT uses the `VIEW SERVER STATE` privilege to collect server settings and configuration. Make sure that you grant the `VIEW ANY DEFINITION` privilege to view endpoints.
To read information about Microsoft Analysis Services, run the following command on the `master` database.
```
EXEC master..sp_addsrvrolemember @loginame = N'', @rolename = N'sysadmin'
```
In the preceding example, replace the `` placeholder with the name of the user that you granted with the privileges before.
To read information about SQL Server Agent, add your user to the `SQLAgentUser` role. Run the following command on the `msdb` database.
```
EXEC sp_addrolemember , ;
```
In the preceding example, replace the `` placeholder with the name of the SQL Server Agent role. Then replace the `` placeholder with the name of the user that you granted with the privileges before. For more information, see [Adding a user to the SQLAgentUser role](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.Agent.html#SQLServerAgent.AddUser) in the *Amazon RDS User Guide*.
To detect log shipping, grant the `SELECT on dbo.log_shipping_primary_databases` privilege on the `msdb` database.
To use the notification approach of the DDL replication, grant the `RECEIVE ON .` privilege on your source databases. In this example, replace the `` placeholder with the schema name of your database. Then, replace the `` placeholder with the name of a queue table.
## Using Windows Authentication when using Microsoft SQL Server as a source
If your application runs on a Windows-based intranet, you might be able to use Windows Authentication for database access. Windows Authentication uses the current Windows identity established on the operating system thread to access the SQL Server database. You can then map the Windows identity to a SQL Server database and permissions. To connect to SQL Server using Windows Authentication, you must specify the Windows identity that your application is using. You must also grant the Windows identity access to the SQL Server database.
SQL Server has two modes of access: Windows Authentication mode and Mixed Mode. Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication. Mixed Mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication is always available and cannot be disabled. For more information about Windows Authentication, see the Microsoft Windows documentation.
The possible example for creating a user in TEST\$1DB is shown following.
```
USE [TEST_DB]
CREATE USER [TestUser] FOR LOGIN [TestDomain\TestUser]
GRANT VIEW DEFINITION TO [TestUser]
GRANT VIEW DATABASE STATE TO [TestUser]
```
### Using Windows Authentication with a JDBC connection
The JDBC driver does not support Windows Authentication when the driver is used on non-Windows operating systems. Windows Authentication credentials, such as user name and password, are not automatically specified when connecting to SQL Server from non-Windows operating systems. In such cases, the applications must use SQL Server Authentication instead.
In JDBC connection string, the parameter `integratedSecurity` must be specified to connect using Windows Authentication. The JDBC driver supports Integrated Windows Authentication on Windows operating systems through the `integratedSecurity` connection string parameter.
To use integrated authentication
1. Install the JDBC driver.
1. Copy the `sqljdbc_auth.dll` file to a directory on the Windows system path on the computer where the JDBC driver is installed.
The `sqljdbc_auth.dll` files are installed in the following location:
<*installation directory*>\$1sqljdbc\$1<*version*>\$1<*language*>\$1auth\$1
When you try to establish a connection to SQL Server database using Windows Authentication, you might get this error: This driver is not configured for integrated authentication. This problem can be solved by performing the following actions:
+ Declare two variables that point to the installed path of your JDBC:
`variable name: SQLJDBC_HOME; variable value: D:\lib\JDBC4.1\enu` (where your sqljdbc4.jar exists);
`variable name: SQLJDBC_AUTH_HOME; variable value: D\lib\JDBC4.1\enu\auth\x86` (if you are running 32bit OS) or `D\lib\JDBC4.1\enu\auth\x64` (if you are running 64bit OS). This is where your `sqljdbc_auth.dll` is located.
+ Copy `sqljdbc_auth.dll` to the folder where your JDK/JRE is running. You may copy to lib folder, bin folder, and so on. As an example, you might copy to the following folder.
```
[JDK_INSTALLED_PATH]\bin;
[JDK_INSTALLED_PATH]\jre\bin;
[JDK_INSTALLED_PATH]\jre\lib;
[JDK_INSTALLED_PATH]\lib;
```
+ Ensure that in your JDBC library folder, you have only the SQLJDBC4.jar file. Remove any other sqljdbc\$1.jar files from that folder (or copy them to another folder). If you are adding the driver as part of your program, ensure that you add only SQLJDBC4.jar as the driver to use.
+ Copy sqljdbc\$1auth.dll the file in the folder with your application.
**Note**
If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc\$1auth.dll file in the x86 folder, even if the operating system is the x64 version. If you are running a 64-bit JVM on a x64 processor, use the sqljdbc\$1auth.dll file in the x64 folder.
When you connect to a SQL Server database, you can choose either **Windows Authentication** or **SQL Server Authentication** for the **Authentication** option.
## Connecting to SQL Server as a source
Use the following procedure to connect to your Microsoft SQL Server source database with the AWS Schema Conversion Tool.
**To connect to a Microsoft SQL Server source database**
1. In the AWS Schema Conversion Tool, choose **Add source**.
1. Choose **Microsoft SQL Server**, then choose **Next**.
The **Add source** dialog box appears.
1. For **Connection name**, enter a name for your database. AWS SCT displays this name in the tree in the left panel.
1. Use database credentials from AWS Secrets Manager or enter them manually:
+ To use database credentials from Secrets Manager, use the following instructions:
1. For **AWS Secret**, choose the name of the secret.
1. Choose **Populate** to automatically fill in all values in the database connection dialog box from Secrets Manager.
For information about using database credentials from Secrets Manager, see [Configuring AWS Secrets Manager in the AWS Schema Conversion Tool](CHAP_UserInterface.SecretsManager.md).
+ To enter the Microsoft SQL Server source database connection information manually, use the following instructions:
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.html)
1. Choose **Test Connection** to verify that AWS SCT can connect to your source database.
1. Choose **Connect** to connect to your source database.
# Converting SQL Server to MySQL
To emulate Microsoft SQL Server database functions in your converted MySQL code, use the SQL Server to MySQL extension pack in AWS SCT. For more information about extension packs, see [Using extension packs with AWS Schema Conversion Tool](CHAP_ExtensionPack.md).
**Topics**
+ [Privileges for MySQL as a target database](#CHAP_Source.SQLServer.ToMySQL.ConfigureTarget)
+ [SQL Server to MySQL conversion settings](#CHAP_Source.SQLServer.ToMySQL.ConversionSettings)
+ [Migration considerations](#CHAP_Source.SQLServer.ToMySQL.MigrationConsiderations)
## Privileges for MySQL as a target database
The privileges required for MySQL as a target are as follows:
+ CREATE ON \$1.\$1
+ ALTER ON \$1.\$1
+ DROP ON \$1.\$1
+ INDEX ON \$1.\$1
+ REFERENCES ON \$1.\$1
+ SELECT ON \$1.\$1
+ CREATE VIEW ON \$1.\$1
+ SHOW VIEW ON \$1.\$1
+ TRIGGER ON \$1.\$1
+ CREATE ROUTINE ON \$1.\$1
+ ALTER ROUTINE ON \$1.\$1
+ EXECUTE ON \$1.\$1
+ INSERT, UPDATE ON AWS\$1SQLSERVER\$1EXT.\$1
+ INSERT, UPDATE, DELETE ON AWS\$1SQLSERVER\$1EXT\$1DATA.\$1
+ CREATE TEMPORARY TABLES ON AWS\$1SQLSERVER\$1EXT\$1DATA.\$1
You can use the following code example to create a database user and grant the privileges.
```
CREATE USER 'user_name' IDENTIFIED BY 'your_password';
GRANT CREATE ON *.* TO 'user_name';
GRANT ALTER ON *.* TO 'user_name';
GRANT DROP ON *.* TO 'user_name';
GRANT INDEX ON *.* TO 'user_name';
GRANT REFERENCES ON *.* TO 'user_name';
GRANT SELECT ON *.* TO 'user_name';
GRANT CREATE VIEW ON *.* TO 'user_name';
GRANT SHOW VIEW ON *.* TO 'user_name';
GRANT TRIGGER ON *.* TO 'user_name';
GRANT CREATE ROUTINE ON *.* TO 'user_name';
GRANT ALTER ROUTINE ON *.* TO 'user_name';
GRANT EXECUTE ON *.* TO 'user_name';
GRANT INSERT, UPDATE ON AWS_SQLSERVER_EXT.* TO 'user_name';
GRANT INSERT, UPDATE, DELETE ON AWS_SQLSERVER_EXT_DATA.* TO 'user_name';
GRANT CREATE TEMPORARY TABLES ON AWS_SQLSERVER_EXT_DATA.* TO 'user_name';
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *your\$1password* with a secure password.
If you use a MySQL database version 5.7 or lower as a target, then run the following command. For MySQL databases version 8.0 and higher, this command is deprecated.
```
GRANT SELECT ON mysql.proc TO 'user_name';
```
To use Amazon RDS for MySQL or Aurora MySQL as a target, set the `lower_case_table_names` parameter to `1`. This value means that the MySQL server handles identifiers of such object names as tables, indexes, triggers, and databases as case insensitive. If you have turned on binary logging in your target instance, then set the `log_bin_trust_function_creators` parameter to `1`. In this case, you don't need to use the `DETERMINISTIC`, `READS SQL DATA` or `NO SQL` characteristics to create stored functions. To configure these parameters, create a new DB parameter group or modify an existing DB parameter group.
## SQL Server to MySQL conversion settings
To edit SQL Server to MySQL conversion settings, in AWS SCT choose **Settings**, and then choose **Conversion settings**. From the upper list, choose **SQL Server**, and then choose **SQL Server – MySQL**. AWS SCT displays all available settings for SQL Server to MySQL conversion.
SQL Server to MySQL conversion settings in AWS SCT include options for the following:
+ To limit the number of comments with action items in the converted code.
For **Add comments in the converted code for the action items of selected severity and higher**, choose the severity of action items. AWS SCT adds comments in the converted code for action items of the selected severity and higher.
For example, to minimize the number of comments in your converted code, choose **Errors only**. To include comments for all action items in your converted code, choose **All messages**.
+ To allow your source SQL Server database to store the output of `EXEC` in a table. AWS SCT creates temporary tables and an additional procedure to emulate this feature. To use this emulation, select **Create additional routines for handling open datasets**.
## Migration considerations
Consider these things when migrating a SQL Server schema to MySQL:
+ MySQL doesn’t support the `MERGE` statement. However, AWS SCT can emulate the `MERGE` statement during conversion by using the `INSERT ON DUPLICATE KEY` clause and the `UPDATE FROM and DELETE FROM` statements.
For correct emulation using `INSERT ON DUPLICATE KEY`, make sure that a unique constraint or primary key exists on the target MySQL database.
+ You can use a `GOTO` statement and a label to change the order that statements are run in. Any Transact-SQL statements that follow a `GOTO` statement are skipped, and processing continues at the label. You can use `GOTO` statements and labels anywhere within a procedure, batch, or statement block. You can also nest `GOTO` statements.
MySQL doesn’t use `GOTO` statements. When AWS SCT converts code that contains a `GOTO` statement, it converts the statement to use a `BEGIN…END` or `LOOP…END LOOP` statement. You can find examples of how AWS SCT converts `GOTO` statements in the table following.
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.ToMySQL.html)
+ MySQL doesn't support multistatement table-valued functions. AWS SCT simulates table-valued functions during a conversion by creating temporary tables and rewriting statements to use these temporary tables.
# Migrating from SQL Server to PostgreSQL with AWS Schema Conversion Tool
You can use the SQL Server to PostgreSQL extension pack in AWS SCT. This extension pack emulates SQL Server database functions in the converted PostgreSQL code. Use the SQL Server to PostgreSQL extension pack to emulate SQL Server Agent and SQL Server Database Mail. For more information about extension packs, see [Using extension packs with AWS Schema Conversion Tool](CHAP_ExtensionPack.md).
**Topics**
+ [Privileges for PostgreSQL as a target database](#CHAP_Source.SQLServer.ToPostgreSQL.ConfigurePostgreSQL)
+ [SQL Server to PostgreSQL conversion settings](#CHAP_Source.SQLServer.ToPostgreSQL.ConversionSettings)
+ [Converting SQL Server partitions to PostgreSQL version 10 partitions](#CHAP_Source.SQLServer.ToPostgreSQL.PG10Partitions)
+ [Migration considerations](#CHAP_Source.SQLServer.ToPostgreSQL.MigrationConsiderations)
+ [Using an AWS SCT extension pack to emulate SQL Server Agent in PostgreSQL](CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Agent.md)
+ [Using an AWS SCT extension pack to emulate SQL Server Database Mail in PostgreSQL](CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail.md)
## Privileges for PostgreSQL as a target database
To use PostgreSQL as a target, AWS SCT requires the `CREATE ON DATABASE` privilege. Make sure that you grant this privilege for each target PostgreSQL database.
To use the converted public synonyms, change the database default search path to `"$user", public_synonyms, public`.
You can use the following code example to create a database user and grant the privileges.
```
CREATE ROLE user_name LOGIN PASSWORD 'your_password';
GRANT CREATE ON DATABASE db_name TO user_name;
ALTER DATABASE db_name SET SEARCH_PATH = "$user", public_synonyms, public;
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *db\$1name* with the name of your target database. Finally, replace *your\$1password* with a secure password.
In PostgreSQL, only the schema owner or a `superuser` can drop a schema. The owner can drop a schema and all objects that this schema includes even if the owner of the schema doesn't own some of its objects.
When you use different users to convert and apply different schemas to your target database, you can get an error message when AWS SCT can't drop a schema. To avoid this error message, use the `superuser` role.
## SQL Server to PostgreSQL conversion settings
To edit SQL Server to PostgreSQL conversion settings, choose **Settings**, and then choose **Conversion settings**. From the upper list, choose **SQL Server**, and then choose **SQL Server – PostgreSQL**. AWS SCT displays all available settings for SQL Server to PostgreSQL conversion.
SQL Server to PostgreSQL conversion settings in AWS SCT include options for the following:
+ To limit the number of comments with action items in the converted code.
For **Add comments in the converted code for the action items of selected severity and higher**, choose the severity of action items. AWS SCT adds comments in the converted code for action items of the selected severity and higher.
For example, to minimize the number of comments in your converted code, choose **Errors only**. To include comments for all action items in your converted code, choose **All messages**.
+ To allow to use indexes with the same name in different tables in SQL Server.
In PostgreSQL, all index names that you use in the schema, must be unique. To make sure that AWS SCT generates unique names for all your indexes, select **Generate unique names for indexes**.
+ To convert SQL Server procedures to PostgreSQL functions.
PostgreSQL version 10 and earlier doesn't support procedures. For customers who aren't familiar with using procedures in PostgreSQL, AWS SCT can convert procedures to functions. To do so, select **Convert procedures to functions**.
+ To emulate the output of `EXEC` in a table.
Your source SQL Server database can store the output of `EXEC` in a table. AWS SCT creates temporary tables and an additional procedure to emulate this feature. To use this emulation, select **Create additional routines for handling open datasets**.
+ To define the template to use for the schema names in the converted code. For **Schema name generation template**, choose one of the following options:
+ **** – Uses the SQL Server database name as a schema name in PostgreSQL.
+ **** – Uses the SQL Server schema name as a schema name in PostgreSQL.
+ **\$1** – Uses a combination of the SQL Server database and schema names as a schema name in PostgreSQL.
+ To keep the letter case of your source object names.
To avoid conversion of object names to lower case, select **Avoid casting to lower case for case sensitive operations**. This option applies only when you turn on case sensitivity option in your target database.
+ To keep the parameter names from your source database.
To add double quotation marks to the names of parameters in the converted code, select **Keep original parameter names**.
## Converting SQL Server partitions to PostgreSQL version 10 partitions
When you convert a Microsoft SQL Server database to Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) or Amazon Relational Database Service for PostgreSQL (Amazon RDS for PostgreSQL), be aware of the following.
In SQL Server, you create partitions with partition functions. When converting from a SQL Server portioned table to a PostgreSQL version 10 partitioned table, be aware of several potential issues:
+ SQL Server allows you to partition a table using a column without a NOT NULL constraint. In that case, all NULL values go to the leftmost partition. PostgreSQL doesn’t support NULL values for RANGE partitioning.
+ SQL Server allows you to create primary and unique keys for partitioned tables. For PostgreSQL, you create primary or unique keys for each partition directly. Thus, PRIMARY or UNIQUE KEY constraint must be removed from their parent table when migrating to PostgreSQL. The resulting key names take the format `_`.
+ SQL Server allows you to create foreign key constraint from and to partitioned tables. PostgreSQL doesn’t support foreign keys referencing partitioned tables. Also, PostgreSQL doesn’t support foreign key references from a partitioned table to another table.
+ SQL Server allows you to create indexes for partitioned tables. For PostgreSQL, an index should be created for each partition directly. Thus, indexes must be removed from their parent tables when migrating to PostgreSQL. The resulting index names take the format `_`.
+ PostgreSQL doesn’t support partitioned indexes.
## Migration considerations
Some things to consider when migrating a SQL Server schema to PostgreSQL:
+ In PostgreSQL, all object’s names in a schema must be unique, including indexes. Index names must be unique in the schema of the base table. In SQL Server, an index name can be the same for different tables.
To ensure the uniqueness of index names, AWS SCT gives you the option to generate unique index names if your index names are not unique. To do this, choose the option **Generate unique index names** in the project properties. By default, this option is enabled. If this option is enabled, unique index names are created using the format IX\$1table\$1name\$1index\$1name. If this option is disabled, index names aren’t changed.
+ A GOTO statement and a label can be used to change the order that statements are run in. Any Transact-SQL statements that follow a GOTO statement are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can also be nested.
PostgreSQL doesn’t use GOTO statements. When AWS SCT converts code that contains a GOTO statement, it converts the statement to use a BEGIN…END or LOOP…END LOOP statement. You can find examples of how AWS SCT converts GOTO statements in the table following.
**SQL Server GOTO statements and the converted PostgreSQL statements**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.ToPostgreSQL.html)
+ PostgreSQL doesn't support a MERGE statement. AWS SCT emulates the behavior of a MERGE statement in the following ways:
+ By INSERT ON CONFLICT construction.
+ By using the UPDATE FROM DML statement, such as MERGE without a WHEN NOT MATCHED clause.
+ By using CURSOR, such as with a MERGE with DELETE clause or by using a complex MERGE ON condition statement.
+ AWS SCT can add database triggers to the object tree when Amazon RDS is the target.
+ AWS SCT can add server-level triggers to the object tree when Amazon RDS is the target.
+ SQL Server automatically creates and manages `deleted` and `inserted` tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. AWS SCT can convert the usage of these tables inside DML trigger statements.
+ AWS SCT can add linked servers to the object tree when Amazon RDS is the target.
+ When migrating from Microsoft SQL Server to PostgreSQL, the built-in SUSER\$1SNAME function is converted as follows:
+ SUSER\$1SNAME – Returns the login name associated with a security identification number (SID).
+ SUSER\$1SNAME() – Not supported.
+ SUSER\$1SNAME() CURRENT\$1USER – Returns the user name of the current execution context.
+ SUSER\$1SNAME(NULL) – Returns NULL.
+ Converting table-valued functions is supported. Table-valued functions return a table and can take the place of a table in a query.
+ PATINDEX returns the starting position of the first occurrence of a pattern in a specified expression on all valid text and character data types. It returns zeros if the pattern is not found. When converting from SQL Server to Amazon RDS for PostgreSQL, AWS SCT replaces application code that uses PATINDEX with aws\$1sqlserver\$1ext.patindex(, ) .
+ In SQL Server, a user-defined table type is a type that represents the definition of a table structure. You use a user-defined table type to declare table-value parameters for stored procedures or functions. You can also use a user-defined table type to declare table variables that you want to use in a batch or in the body of a stored procedure or function. AWS SCT emulated this type in PostgreSQL by creating a temporary table.
When converting from SQL Server to PostgreSQL, AWS SCT converts SQL Server system objects into recognizable objects in PostgreSQL. The following table shows how the system objects are converted.
| MS SQL Server use cases | PostgreSQL substitution |
| --- | --- |
| SYS.SCHEMAS | AWS\$1SQLSERVER\$1EXT.SYS\$1SCHEMAS |
| SYS.TABLES | AWS\$1SQLSERVER\$1EXT.SYS\$1TABLES |
| SYS.VIEWS | AWS\$1SQLSERVER\$1EXT.SYS\$1VIEWS |
| SYS.ALL\$1VIEWS | AWS\$1SQLSERVER\$1EXT.SYS\$1ALL\$1VIEWS |
| SYS.TYPES | AWS\$1SQLSERVER\$1EXT.SYS\$1TYPES |
| SYS.COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1COLUMNS |
| SYS.ALL\$1COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1ALL\$1COLUMNS |
| SYS.FOREIGN\$1KEYS | AWS\$1SQLSERVER\$1EXT.SYS\$1FOREIGN\$1KEYS |
| SYS.SYSFOREIGNKEYS | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSFOREIGNKEYS |
| SYS.FOREIGN\$1KEY\$1COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1FOREIGN\$1KEY\$1COLUMNS |
| SYS.KEY\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.SYS\$1KEY\$1CONSTRAINTS |
| SYS.IDENTITY\$1COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1IDENTITY\$1COLUMNS |
| SYS.PROCEDURES | AWS\$1SQLSERVER\$1EXT.SYS\$1PROCEDURES |
| SYS.INDEXES | AWS\$1SQLSERVER\$1EXT.SYS\$1INDEXES |
| SYS.SYSINDEXES | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSINDEXES |
| SYS.OBJECTS | AWS\$1SQLSERVER\$1EXT.SYS\$1OBJECTS |
| SYS.ALL\$1OBJECTS | AWS\$1SQLSERVER\$1EXT.SYS\$1ALL\$1OBJECTS |
| SYS.SYSOBJECTS | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSOBJECTS |
| SYS.SQL\$1MODULES | AWS\$1SQLSERVER\$1EXT.SYS\$1SQL\$1MODULES |
| SYS.DATABASES | AWS\$1SQLSERVER\$1EXT.SYS\$1DATABASES |
| INFORMATION\$1SCHEMA.SCHEMATA | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1SCHEMATA |
| INFORMATION\$1SCHEMA.VIEWS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1VIEWS |
| INFORMATION\$1SCHEMA.TABLES | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1TABLES |
| INFORMATION\$1SCHEMA.COLUMNS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1COLUMNS |
| INFORMATION\$1SCHEMA.CHECK\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1CHECK\$1CONSTRAINTS |
| INFORMATION\$1SCHEMA.REFERENTIAL\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1REFERENTIAL\$1CONSTRAINTS |
| INFORMATION\$1SCHEMA.TABLE\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1TABLE\$1CONSTRAINTS |
| INFORMATION\$1SCHEMA.KEY\$1COLUMN\$1USAGE | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1KEY\$1COLUMN\$1USAGE |
| INFORMATION\$1SCHEMA.CONSTRAINT\$1TABLE\$1USAGE | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1CONSTRAINT\$1TABLE\$1USAGE |
| INFORMATION\$1SCHEMA.CONSTRAINT\$1COLUMN\$1USAGE | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1CONSTRAINT\$1COLUMN\$1USAGE |
| INFORMATION\$1SCHEMA.ROUTINES | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1ROUTINES |
| SYS.SYSPROCESSES | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSPROCESSES |
| sys.system\$1objects | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSTEM\$1OBJECTS |
# Using an AWS SCT extension pack to emulate SQL Server Agent in PostgreSQL
SQL Server Agent is a Microsoft Windows service that runs SQL Server jobs. SQL Server Agent runs jobs on a schedule, in response to a specific event, or on demand. For more information about SQL Server Agent, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent?view=sql-server-ver15).
PostgreSQL doesn't have an equivalent for SQL Server Agent. To emulate the SQL Server Agent features, AWS SCT creates an extension pack. This extension pack uses AWS Lambda and Amazon CloudWatch. AWS Lambda implements the interface that you use to manage schedules and run jobs. Amazon CloudWatch maintains the schedule rules.
AWS Lambda and Amazon CloudWatch use a JSON parameter to interact. This JSON parameter has the following structure.
```
{
"mode": mode,
"parameters": {
list of parameters
},
"callback": procedure name
}
```
In the preceding example, *`mode`* is the type of the task and `list of parameters` is a set of parameters that depend on the type of the task. Also, `procedure name` is the name of the procedure that runs after the task is completed.
AWS SCT uses one Lambda function to control and run jobs. The CloudWatch rule starts the run of the job and provides the necessary information to start the job. When the CloudWatch rule triggers, it starts the Lambda function using the parameters from the rule.
To create a simple job that calls a procedure, use the following format.
```
{
"mode": "run_job",
"parameters": {
"vendor": "mysql",
"cmd": "lambda_db.nightly_job"
}
}
```
To create a job with several steps, use the following format.
```
{
"mode": "run_job",
"parameters": {
"job_name": "Job1",
"enabled": "true",
"start_step_id": 1,
"notify_level_email": [0|1|2|3],
"notify_email": email,
"delete_level": [0|1|2|3],
"job_callback": "ProcCallBackJob(job_name, code, message)",
"step_callback": "ProcCallBackStep(job_name, step_id, code, message)"
},
"steps": [
{
"id":1,
"cmd": "ProcStep1",
"cmdexec_success_code": 0,
"on_success_action": [|2|3|4],
"on_success_step_id": 1,
"on_fail_action": 0,
"on_fail_step_id": 0,
"retry_attempts": number,
"retry_interval": number
},
{
"id":2,
"cmd": "ProcStep2",
"cmdexec_success_code": 0,
"on_success_action": [1|2|3|4],
"on_success_step_id": 0,
"on_fail_action": 0,
"on_fail_step_id": 0,
"retry_attempts": number,
"retry_interval": number
},
...
]
}
```
To emulate the SQL Server Agent behavior in PostgreSQL, the AWS SCT extension pack also creates the following tables and procedures.
## Tables that emulate SQL Server Agent in PostgreSQL
To emulate SQL Server Agent, the extension pack uses the following tables:
**sysjobs**
Stores the information about the jobs.
**sysjobsteps**
Stores the information about the steps of a job.
**sysschedules**
Stores the information about the job schedules.
**sysjobschedules**
Stores the schedule information for individual jobs.
**sysjobhistory**
Stores the information about the runs of scheduled jobs.
## Procedures that emulate SQL Server Agent in PostgreSQL
To emulate SQL Server Agent, the extension pack uses the following procedures:
**sp\$1add\$1job**
Adds a new job.
**sp\$1add\$1jobstep**
Adds a step to a job.
**sp\$1add\$1schedule**
Creates a new schedule rule in Amazon CloudWatch. You can use this schedule with any number of jobs.
**sp\$1attach\$1schedule**
Sets a schedule for the selected job.
**sp\$1add\$1jobschedule**
Creates a schedule rule for a job in Amazon CloudWatch and sets the target for this rule.
**sp\$1update\$1job**
Updates the attributes of the previously created job.
**sp\$1update\$1jobstep**
Updates the attributes of the step in a job.
**sp\$1update\$1schedule**
Updates the attributes of a schedule rule in Amazon CloudWatch.
**sp\$1update\$1jobschedule**
Updates the attributes of the schedule for the specified job.
**sp\$1delete\$1job**
Deletes a job.
**sp\$1delete\$1jobstep**
Deletes a job step from a job.
**sp\$1delete\$1schedule**
Deletes a schedule.
**sp\$1delete\$1jobschedule**
Deletes the schedule rule for the specified job from Amazon CloudWatch.
**sp\$1detach\$1schedule**
Removes an association between a schedule and a job.
**get\$1jobs, update\$1job**
Internal procedures that interact with AWS Elastic Beanstalk.
**sp\$1verify\$1job\$1date, sp\$1verify\$1job\$1time, sp\$1verify\$1job, sp\$1verify\$1jobstep, sp\$1verify\$1schedule, sp\$1verify\$1job\$1identifiers, sp\$1verify\$1schedule\$1identifiers**
Internal procedures that check settings.
## Syntax for procedures that emulate SQL Server Agent in PostgreSQL
The `aws_sqlserver_ext.sp_add_job` procedure in the extension pack emulates the `msdb.dbo.sp_add_job` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-job-transact-sql?view=sql-server-ver15).
```
par_job_name varchar,
par_enabled smallint = 1,
par_description varchar = NULL::character varying,
par_start_step_id integer = 1,
par_category_name varchar = NULL::character varying,
par_category_id integer = NULL::integer,
par_owner_login_name varchar = NULL::character varying,
par_notify_level_eventlog integer = 2,
par_notify_level_email integer = 0,
par_notify_level_netsend integer = 0,
par_notify_level_page integer = 0,
par_notify_email_operator_name varchar = NULL::character varying,
par_notify_netsend_operator_name varchar = NULL::character varying,
par_notify_page_operator_name varchar = NULL::character varying,
par_delete_level integer = 0,
inout par_job_id integer = NULL::integer,
par_originating_server varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sp_add_jobstep` procedure in the extension pack emulates the `msdb.dbo.sp_add_jobstep` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_step_id integer = NULL::integer,
par_step_name varchar = NULL::character varying,
par_subsystem varchar = 'TSQL'::bpchar,
par_command text = NULL::text,
par_additional_parameters text = NULL::text,
par_cmdexec_success_code integer = 0,
par_on_success_action smallint = 1,
par_on_success_step_id integer = 0,
par_on_fail_action smallint = 2,
par_on_fail_step_id integer = 0,
par_server varchar = NULL::character varying,
par_database_name varchar = NULL::character varying,
par_database_user_name varchar = NULL::character varying,
par_retry_attempts integer = 0,
par_retry_interval integer = 0,
par_os_run_priority integer = 0,
par_output_file_name varchar = NULL::character varying,
par_flags integer = 0,
par_proxy_id integer = NULL::integer,
par_proxy_name varchar = NULL::character varying,
inout par_step_uid char = NULL::bpchar,
out returncode integer
```
The `aws_sqlserver_ext.sp_add_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_add_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql?view=sql-server-ver15).
```
par_schedule_name varchar,
par_enabled smallint = 1,
par_freq_type integer = 0,
par_freq_interval integer = 0,
par_freq_subday_type integer = 0,
par_freq_subday_interval integer = 0,
par_freq_relative_interval integer = 0,
par_freq_recurrence_factor integer = 0,
par_active_start_date integer = NULL::integer,
par_active_end_date integer = 99991231,
par_active_start_time integer = 0,
par_active_end_time integer = 235959,
par_owner_login_name varchar = NULL::character varying,
*inout par_schedule_uid char = NULL::bpchar,*
inout par_schedule_id integer = NULL::integer,
par_originating_server varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sp_attach_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_attach_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-attach-schedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_schedule_id integer = NULL::integer,
par_schedule_name varchar = NULL::character varying,
par_automatic_post smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_add_jobschedule` procedure in the extension pack emulates the `msdb.dbo.sp_add_jobschedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobschedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_name varchar = NULL::character varying,
par_enabled smallint = 1,
par_freq_type integer = 1,
par_freq_interval integer = 0,
par_freq_subday_type integer = 0,
par_freq_subday_interval integer = 0,
par_freq_relative_interval integer = 0,
par_freq_recurrence_factor integer = 0,
par_active_start_date integer = NULL::integer,
par_active_end_date integer = 99991231,
par_active_start_time integer = 0,
par_active_end_time integer = 235959,
inout par_schedule_id integer = NULL::integer,
par_automatic_post smallint = 1,
inout par_schedule_uid char = NULL::bpchar,
out returncode integer
```
The `aws_sqlserver_ext.sp_delete_job` procedure in the extension pack emulates the `msdb.dbo.sp_delete_job` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-job-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_originating_server varchar = NULL::character varying,
par_delete_history smallint = 1,
par_delete_unused_schedule smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_delete_jobstep` procedure in the extension pack emulates the `msdb.dbo.sp_delete_jobstep` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-jobsteplog-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_step_id integer = NULL::integer,
out returncode integer
```
The `aws_sqlserver_ext.sp_delete_jobschedule` procedure in the extension pack emulates the `msdb.dbo.sp_delete_jobschedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-jobschedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_name varchar = NULL::character varying,
par_keep_schedule integer = 0,
par_automatic_post smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_delete_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_delete_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-schedule-transact-sql?view=sql-server-ver15).
```
par_schedule_id integer = NULL::integer,
par_schedule_name varchar = NULL::character varying,
par_force_delete smallint = 0,
par_automatic_post smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_detach_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_detach_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-schedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_schedule_id integer = NULL::integer,
par_schedule_name varchar = NULL::character varying,
par_delete_unused_schedule smallint = 0,
par_automatic_post smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sp_update_job` procedure in the extension pack emulates the `msdb.dbo.sp_update_job` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-job-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer
par_job_name varchar = NULL::character varying
par_new_name varchar = NULL::character varying
par_enabled smallint = NULL::smallint
par_description varchar = NULL::character varying
par_start_step_id integer = NULL::integer
par_category_name varchar = NULL::character varying
par_owner_login_name varchar = NULL::character varying
par_notify_level_eventlog integer = NULL::integer
par_notify_level_email integer = NULL::integer
par_notify_level_netsend integer = NULL::integer
par_notify_level_page integer = NULL::integer
par_notify_email_operator_name varchar = NULL::character varying
par_notify_netsend_operator_name varchar = NULL::character varying
par_notify_page_operator_name varchar = NULL::character varying
par_delete_level integer = NULL::integer
par_automatic_post smallint = 1
out returncode integer
```
The `aws_sqlserver_ext.sp_update_jobschedule` procedure in the extension pack emulates the `msdb.dbo.sp_update_jobschedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobschedule-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer
par_job_name varchar = NULL::character varying
par_name varchar = NULL::character varying
par_new_name varchar = NULL::character varying
par_enabled smallint = NULL::smallint
par_freq_type integer = NULL::integer
par_freq_interval integer = NULL::integer
par_freq_subday_type integer = NULL::integer
par_freq_subday_interval integer = NULL::integer
par_freq_relative_interval integer = NULL::integer
par_freq_recurrence_factor integer = NULL::integer
par_active_start_date integer = NULL::integer
par_active_end_date integer = NULL::integer
par_active_start_time integer = NULL::integer
par_active_end_time integer = NULL::integer
par_automatic_post smallint = 1
out returncode integer
```
The `aws_sqlserver_ext.sp_update_jobstep` procedure in the extension pack emulates the `msdb.dbo.sp_update_jobstep` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobstep-transact-sql?view=sql-server-ver15).
```
par_job_id integer = NULL::integer
par_job_name varchar = NULL::character varying
par_step_id integer = NULL::integer
par_step_name varchar = NULL::character varying
par_subsystem varchar = NULL::character varying
par_command text = NULL::text
par_additional_parameters text = NULL::text
par_cmdexec_success_code integer = NULL::integer
par_on_success_action smallint = NULL::smallint
par_on_success_step_id integer = NULL::integer
par_on_fail_action smallint = NULL::smallint
par_on_fail_step_id integer = NULL::integer
par_server varchar = NULL::character varying
par_database_name varchar = NULL::character varying
par_database_user_name varchar = NULL::character varying
par_retry_attempts integer = NULL::integer
par_retry_interval integer = NULL::integer
par_os_run_priority integer = NULL::integer
par_output_file_name varchar = NULL::character varying
par_flags integer = NULL::integer
par_proxy_id integer = NULL::integer
par_proxy_name varchar = NULL::character varying
out returncode integer
```
The `aws_sqlserver_ext.sp_update_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_update_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-ver15).
```
par_schedule_id integer = NULL::integer
par_name varchar = NULL::character varying
par_new_name varchar = NULL::character varying
par_enabled smallint = NULL::smallint
par_freq_type integer = NULL::integer
par_freq_interval integer = NULL::integer
par_freq_subday_type integer = NULL::integer
par_freq_subday_interval integer = NULL::integer
par_freq_relative_interval integer = NULL::integer
par_freq_recurrence_factor integer = NULL::integer
par_active_start_date integer = NULL::integer
par_active_end_date integer = NULL::integer
par_active_start_time integer = NULL::integer
par_active_end_time integer = NULL::integer
par_owner_login_name varchar = NULL::character varying
par_automatic_post smallint = 1
out returncode integer
```
## Examples for using procedures that emulate SQL Server Agent in PostgreSQL
To add a new job, use the `aws_sqlserver_ext.sp_add_job` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_add_job (
par_job_name := 'test_job',
par_enabled := 1::smallint,
par_start_step_id := 1::integer,
par_category_name := '[Uncategorized (Local)]',
par_owner_login_name := 'sa');
```
To add a new job step, use the `aws_sqlserver_ext.sp_add_jobstep` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_add_jobstep (
par_job_name := 'test_job',
par_step_id := 1::smallint,
par_step_name := 'test_job_step1',
par_subsystem := 'TSQL',
par_command := 'EXECUTE [dbo].[PROC_TEST_JOB_STEP1];',
par_server := NULL,
par_database_name := 'GOLD_TEST_SS');
```
To add a simple schedule, use the `aws_sqlserver_ext.sp_add_schedule` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_add_schedule(
par_schedule_name := 'RunOnce',
par_freq_type := 1,
par_active_start_time := 233000);
```
To set a schedule for a job, use the `aws_sqlserver_ext.sp_attach_schedule` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_attach_schedule (
par_job_name := 'test_job',
par_schedule_name := 'NightlyJobs');
```
To create a schedule for a job, use the `aws_sqlserver_ext.sp_add_jobschedule` procedure as shown following.
```
SELECT * FROM aws_sqlserver_ext.sp_add_jobschedule (
par_job_name := 'test_job2',
par_name := 'test_schedule2',
par_enabled := 1::smallint,
par_freq_type := 4,
par_freq_interval := 1,
par_freq_subday_type := 4,
par_freq_subday_interval := 1,
par_freq_relative_interval := 0,
par_freq_recurrence_factor := 0,
par_active_start_date := 20100801,
par_active_end_date := 99991231,
par_active_start_time := 0,
par_active_end_time := 0);
```
## Use case examples for emulating SQL Server Agent in PostgreSQL
If your source database code uses SQL Server Agent to run jobs, you can use the SQL Server to PostgreSQL extension pack for AWS SCT to convert this code to PostgreSQL. The extension pack uses AWS Lambda functions to emulate the behavior of SQL Server Agent.
You can create a new AWS Lambda function or register an existing function.
**To create a new AWS Lambda function**
1. In AWS SCT, in the target database tree, open the context (right-click) menu, choose **Apply extension pack for**, and then choose **PostgreSQL**.
The extension pack wizard appears.
1. On the **SQL Server Agent emulation service** tab, do the following:
+ Choose **Create an AWS Lambda function**.
+ For **Database login**, enter the name of the target database user.
+ For **Database password**, enter the password for the user name that you entered on the preceding step.
+ For **Python library folder**, enter the path to your Python library folder.
+ Choose **Create AWS Lambda function**, and then choose **Next**.
**To register an AWS Lambda function that you deployed earlier**
+ Run the following script on your target database.
```
SELECT
FROM aws_sqlserver_ext.set_service_setting(
p_service := 'JOB',
p_setting := 'LAMBDA_ARN',
p_value := ARN)
```
In the preceding example, *`ARN`* is the Amazon Resource Name (ARN) of the deployed AWS Lambda function.
The following example creates a simple task that consists of one step. Every five minutes, this task runs the previously created `job_example` function. This function inserts records into the `job_example_table` table.
**To create this simple task**
1. Create a job using the `aws_sqlserver_ext.sp_add_job` function as shown following.
```
SELECT
FROM aws_sqlserver_ext.sp_add_job (
par_job_name := 'test_simple_job');
```
1. Create a job step using the `aws_sqlserver_ext.sp_add_jobstep` function as shown following.
```
SELECT
FROM aws_sqlserver_ext.sp_add_jobstep (
par_job_name := 'test_simple_job',
par_step_name := 'test_simple_job_step1',
par_command := 'PERFORM job_simple_example;');
```
The job step specifies what the function does.
1. Create a scheduler for the job using the `aws_sqlserver_ext.sp_add_jobschedule` function as shown following.
```
SELECT
FROM aws_sqlserver_ext.sp_add_jobschedule (
par_job_name := 'test_simple_job',
par_name := 'test_schedule',
par_freq_type := 4, /* Daily */
par_freq_interval := 1, /* frequency_interval is unused */
par_freq_subday_type := 4, /* Minutes */
par_freq_subday_interval := 5 /* 5 minutes */);
```
The job step specifies what the function does.
To delete this job, use the `aws_sqlserver_ext.sp_delete_job` function as shown following.
```
PERFORM aws_sqlserver_ext.sp_delete_job(
par_job_name := 'PeriodicJob1'::character varying,
par_delete_history := 1::smallint,
par_delete_unused_schedule := 1::smallint);
```
# Using an AWS SCT extension pack to emulate SQL Server Database Mail in PostgreSQL
You can use SQL Server Database Mail to send email messages to users from the SQL Server Database Engine or Azure SQL Managed Instance. These email messages can contain query results or include files from any resource on your network. For more information about SQL Server Database Mail, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail?view=sql-server-ver15).
PostgreSQL doesn't have an equivalent for SQL Server Database Mail. To emulate the SQL Server Database Mail features, AWS SCT creates an extension pack. This extension pack uses AWS Lambda and Amazon Simple Email Service (Amazon SES). AWS Lambda provides users with an interface to interact with Amazon SES email sending service. To set up this interaction, add the Amazon Resource Name (ARN) of your Lambda function.
For a new email account, use the following command.
```
do
$$
begin
PERFORM sysmail_add_account_sp (
par_account_name :='your_account_name',
par_email_address := 'your_account_email',
par_display_name := 'your_account_display_name',
par_mailserver_type := 'AWSLAMBDA'
par_mailserver_name := 'ARN'
);
end;
$$ language plpgsql;
```
To add the ARN of your Lambda function to the existing email account, use the following command.
```
do
$$
begin
PERFORM sysmail_update_account_sp (
par_account_name :='existind_account_name',
par_mailserver_type := 'AWSLAMBDA'
par_mailserver_name := 'ARN'
);
end;
$$ language plpgsql;
```
In the preceding examples, *`ARN`* is the ARN of your Lambda function.
To emulate the SQL Server Database Mail behavior in PostgreSQL, the AWS SCT extension pack uses the following tables, views, and procedures.
## Tables that emulate SQL Server Database Mail in PostgreSQL
To emulate SQL Server Database Mail, the extension pack uses the following tables:
**sysmail\$1account**
Stores the information about the email accounts.
**sysmail\$1profile**
Stores the information about the user profiles.
**sysmail\$1server**
Stores the information about the email servers.
**sysmail\$1mailitems**
Stores the list of the email messages.
**sysmail\$1attachments**
Contains one row for each email attachment.
**sysmail\$1log**
Stores the service information about sending email messages.
**sysmail\$1profileaccount**
Stores the information about the user profiles and email accounts.
## Views that emulate SQL Server Database Mail in PostgreSQL
To emulate SQL Server Database Mail, AWS SCT creates the following views in the PostgreSQL database to ensure compatibility. The extension pack doesn't use them, but your converted code can query these views.
**sysmail\$1allitems**
Includes a list of all emails.
**sysmail\$1faileditems**
Includes a list of emails that couldn't be sent.
**sysmail\$1sentitems**
Includes a list of sent emails.
**sysmail\$1unsentitems**
Includes a list of emails that aren't sent yet.
**sysmail\$1mailattachments**
Includes a list of attached files.
## Procedures that emulate SQL Server Database Mail in PostgreSQL
To emulate SQL Server Database Mail, the extension pack uses the following procedures:
**sp\$1send\$1dbmail**
Sends an email to the specified recipients.
**sysmail\$1add\$1profile\$1sp**
Creates a new user profile.
**sysmail\$1add\$1account\$1sp**
Creates a new email account that stores such information as Simple Mail Transfer Protocol (SMTP) credentials, and so on.
**sysmail\$1add\$1profileaccount\$1sp**
Adds an email account to the specified user profile.
**sysmail\$1update\$1profile\$1sp**
Changes the attributes of the user profile such as description, name, and so on.
**sysmail\$1update\$1account\$1sp**
Changes the information in the existing email account.
**sysmail\$1update\$1profileaccount\$1sp**
Updates the email account information in the specified user profile.
**sysmail\$1delete\$1profileaccount\$1sp**
Removes an email account from the specified user profile.
**sysmail\$1delete\$1account\$1sp**
Deletes the email account.
**sysmail\$1delete\$1profile\$1sp**
Deletes the user profile.
**sysmail\$1delete\$1mailitems\$1sp**
Deletes emails from internal tables.
**sysmail\$1help\$1profile\$1sp**
Displays information about the user profile.
**sysmail\$1help\$1account\$1sp**
Displays information about the email account.
**sysmail\$1help\$1profileaccount\$1sp**
Displays information about email accounts associated with the user profile.
**sysmail\$1dbmail\$1json**
An internal procedure that generates JSON requests for AWS Lambda functions.
**sysmail\$1verify\$1profile\$1sp, sysmail\$1verify\$1account\$1sp, sysmail\$1verify\$1addressparams\$1sp**
Internal procedures that check settings.
**sp\$1get\$1dbmail, sp\$1set\$1dbmail, sysmail\$1dbmail\$1xml**
Deprecated internal procedures.
## Syntax for procedures that emulate SQL Server Database Mail in PostgreSQL
The `aws_sqlserver_ext.sp_send_dbmail` procedure in the extension pack emulates the `msdb.dbo.sp_send_dbmail` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver15).
```
par_profile_name varchar = NULL::character varying,
par_recipients text = NULL::text,
par_copy_recipients text = NULL::text,
par_blind_copy_recipients text = NULL::text,
par_subject varchar = NULL::character varying,
par_body text = NULL::text,
par_body_format varchar = NULL::character varying,
par_importance varchar = 'NORMAL'::character varying,
par_sensitivity varchar = 'NORMAL'::character varying,
par_file_attachments text = NULL::text,
par_query text = NULL::text,
par_execute_query_database varchar = NULL::character varying,
par_attach_query_result_as_file smallint = 0,
par_query_attachment_filename varchar = NULL::character varying,
par_query_result_header smallint = 1,
par_query_result_width integer = 256,
par_query_result_separator VARCHAR = ' '::character varying,
par_exclude_query_output smallint = 0,
par_append_query_error smallint = 0,
par_query_no_truncate smallint = 0,
par_query_result_no_padding smallint = 0,
out par_mailitem_id integer,
par_from_address text = NULL::text,
par_reply_to text = NULL::text,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_delete_mailitems_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_mailitems_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-mailitems-sp-transact-sql?view=sql-server-ver15).
```
par_sent_before timestamp = NULL::timestamp without time zone,
par_sent_status varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_add_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_add_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-add-profile-sp-transact-sql?view=sql-server-ver15).
```
par_profile_name varchar,
par_description varchar = NULL::character varying,
out par_profile_id integer,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_add_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_add_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-add-account-sp-transact-sql?view=sql-server-ver15).
```
par_account_name varchar
par_email_address varchar
par_display_name varchar = NULL::character varying
par_replyto_address varchar = NULL::character varying
par_description varchar = NULL::character varying
par_mailserver_name varchar = NULL::character varying
par_mailserver_type varchar = 'SMTP'::bpchar
par_port integer = 25
par_username varchar = NULL::character varying
par_password varchar = NULL::character varying
par_use_default_credentials smallint = 0
par_enable_ssl smallint = 0
out par_account_id integer
out returncode integer
```
The `aws_sqlserver_ext.sysmail_add_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_add_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-add-profileaccount-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
par_sequence_number integer = NULL::integer,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_help_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_help_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-profile-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_update_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_update_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-update-profile-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_description varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_delete_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-profile-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_force_delete smallint = 1,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_help_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_help_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-account-sp-transact-sql?view=sql-server-ver15).
```
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_update_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_update_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-update-account-sp-transact-sql?view=sql-server-ver15).
```
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
par_email_address varchar = NULL::character varying,
par_display_name varchar = NULL::character varying,
par_replyto_address varchar = NULL::character varying,
par_description varchar = NULL::character varying,
par_mailserver_name varchar = NULL::character varying,
par_mailserver_type varchar = NULL::character varying,
par_port integer = NULL::integer,
par_username varchar = NULL::character varying,
par_password varchar = NULL::character varying,
par_use_default_credentials smallint = NULL::smallint,
par_enable_ssl smallint = NULL::smallint,
par_timeout integer = NULL::integer,
par_no_credential_change smallint = NULL::smallint,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_delete_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-account-sp-transact-sql?view=sql-server-ver15).
```
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_help_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_help_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-profileaccount-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_update_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_update_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-update-profileaccount-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
par_sequence_number integer = NULL::integer,
out returncode integer
```
The `aws_sqlserver_ext.sysmail_delete_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-profileaccount-sp-transact-sql?view=sql-server-ver15).
```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```
## Examples for using procedures that emulate SQL Server Database Mail in PostgreSQL
To send an email, use the `aws_sqlserver_ext.sp_send_dbmail` procedure as shown following.
```
PERFORM sp_send_dbmail (
par_profile_name := 'Administrator',
par_recipients := 'hello@rusgl.info',
par_subject := 'Automated Success Message',
par_body := 'The stored procedure finished'
);
```
The following example shows how to send an email with query results.
```
PERFORM sp_send_dbmail (
par_profile_name := 'Administrator',
par_recipients := 'hello@rusgl.info',
par_subject := 'Account with id = 1',
par_query := 'SELECT COUNT(*)FROM Account WHERE id = 1'
);
```
The following example shows how to send an email with HTML code.
```
DECLARE var_tableHTML TEXT;
SET var_tableHTML := CONCAT(
'Work Order Report
',
'',
'| Work Order ID | Product ID | ',
'Name | Order Qty | Due Date | ',
'Expected Revenue |
|---|
',
'
'
);
PERFORM sp_send_dbmail (
par_recipients := 'hello@rusgl.info',
par_subject := 'Work Order List',
par_body := var_tableHTML,
par_body_format := 'HTML'
);
```
To delete emails, use the `aws_sqlserver_ext.sysmail_delete_mailitems_sp` procedure as shown following.
```
DECLARE var_GETDATE datetime;
SET var_GETDATE = NOW();
PERFORM sysmail_delete_mailitems_sp (
par_sent_before := var_GETDATE
);
```
The following example shows how to delete the oldest emails.
```
PERFORM sysmail_delete_mailitems_sp (
par_sent_before := '31.12.2015'
);
```
The following example shows how to delete all emails that can't be sent.
```
PERFORM sysmail_delete_mailitems_sp (
par_sent_status := 'failed'
);
```
To create a new user profile, use the `aws_sqlserver_ext.sysmail_add_profile_sp` procedure as shown following.
```
PERFORM sysmail_add_profile_sp (
profile_name := 'Administrator',
par_description := 'administrative mail'
);
```
The following example shows how to create a new profile and save the unique profile identifier in a variable.
```
DECLARE var_profileId INT;
SELECT par_profile_id
FROM sysmail_add_profile_sp (
profile_name := 'Administrator',
par_description := ' Profile used for administrative mail.')
INTO var_profileId;
SELECT var_profileId;
```
To create a new email account, use the `aws_sqlserver_ext.sysmail_add_account_sp` procedure as shown following.
```
PERFORM sysmail_add_account_sp (
par_account_name :='Audit Account',
par_email_address := 'dba@rusgl.info',
par_display_name := 'Test Automated Mailer',
par_description := 'Account for administrative e-mail.',
par_mailserver_type := 'AWSLAMBDA'
par_mailserver_name := 'arn:aws:lambda:us-west-2:555555555555:function:pg_v3'
);
```
To add an email account to the user profile, use the `aws_sqlserver_ext.sysmail_add_profileaccount_sp` procedure as shown following.
```
PERFORM sysmail_add_profileaccount_sp (
par_account_name := 'Administrator',
par_account_name := 'Audit Account',
par_sequence_number := 1
);
```
## Use case examples for emulating SQL Server Database Mail in PostgreSQL
If your source database code uses SQL Server Database Mail to send emails, you can use the AWS SCT extension pack to convert this code to PostgreSQL.
**To send an email from your PostgreSQL database**
1. Create and configure your AWS Lambda function.
1. Apply the AWS SCT extension pack.
1. Create a user profile using the `sysmail_add_profile_sp` function as shown following.
1. Create an email account using the `sysmail_add_account_sp` function as shown following.
1. Add this email account to your user profile using the `sysmail_add_profileaccount_sp` function as shown following.
```
CREATE OR REPLACE FUNCTION aws_sqlserver_ext.
proc_dbmail_settings_msdb()
RETURNS void
AS
$BODY$
BEGIN
PERFORM aws_sqlserver_ext.sysmail_add_profile_sp(
par_profile_name := 'Administrator',
par_description := 'administrative mail'
);
PERFORM aws_sqlserver_ext.sysmail_add_account_sp(
par_account_name := 'Audit Account',
par_description := 'Account for administrative e-mail.',
par_email_address := 'dba@rusgl.info',
par_display_name := 'Test Automated Mailer',
par_mailserver_type := 'AWSLAMBDA'
par_mailserver_name := 'your_ARN'
);
PERFORM aws_sqlserver_ext.sysmail_add_profileaccount_sp(
par_profile_name := 'Administrator',
par_account_name := 'Audit Account',
par_sequence_number := 1
);
END;
$BODY$
LANGUAGE plpgsql;
```
1. Send an email using the `sp_send_dbmail` function as shown following.
```
CREATE OR REPLACE FUNCTION aws_sqlserver_ext.
proc_dbmail_send_msdb()
RETURNS void
AS
$BODY$
BEGIN
PERFORM aws_sqlserver_ext.sp_send_dbmail(
par_profile_name := 'Administrator',
par_recipients := 'hello@rusgl.info',
par_body := 'The stored procedure finished',
par_subject := 'Automated Success Message'
);
END;
$BODY$
LANGUAGE plpgsql;
```
To view the information about all user profiles, use the `sysmail_help_profile_sp` procedure as shown following.
```
SELECT FROM aws_sqlserver_ext.sysmail_help_profile_sp();
```
The following example displays the information about the specific user profile.
```
select from aws_sqlserver_ext.sysmail_help_profile_sp(par_profile_id := 1);
select from aws_sqlserver_ext.sysmail_help_profile_sp(par_profile_name := 'Administrator');
```
To view the information about all email accounts, use the `sysmail_help_account_sp` procedure as shown following.
```
select from aws_sqlserver_ext.sysmail_help_account_sp();
```
The following example displays the information about the specific email account.
```
select from aws_sqlserver_ext.sysmail_help_account_sp(par_account_id := 1);
select from aws_sqlserver_ext.sysmail_help_account_sp(par_account_name := 'Audit Account');
```
To view the information about all email accounts that are associated with the user profiles, use the `sysmail_help_profileaccount_sp` procedure as shown following.
```
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp();
```
The following example filters the records by identifier, profile name, or account name.
```
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_id := 1);
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_id := 1, par_account_id := 1);
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_name := 'Administrator');
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_account_name := 'Audit Account');
```
To change the user profile name or description, use the `sysmail_update_profile_sp` procedure as shown following.
```
select aws_sqlserver_ext.sysmail_update_profile_sp(
par_profile_id := 2,
par_profile_name := 'New profile name'
);
```
To change the email account settings, use the `ysmail_update_account_sp` procedure as shown following.
```
select from aws_sqlserver_ext.sysmail_update_account_sp (
par_account_name := 'Audit Account',
par_mailserver_name := 'arn:aws:lambda:region:XXXXXXXXXXXX:function:func_test',
par_mailserver_type := 'AWSLAMBDA'
);
```
# Migrating from SQL Server to Amazon RDS for SQL Server with AWS Schema Conversion Tool
Some things to consider when migrating SQL Server schema and code to Amazon RDS for SQL Server:
+ AWS SCT can convert SQL Server Agent to provide schedules, alerts, and jobs on an Amazon RDS for SQL Server DB instance. After conversion, you can use an Amazon RDS for SQL Server DB instance with SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS), and SQL Server Integration Services (SSIS).
+ Amazon RDS currently doesn’t support SQL Server Service Broker or additional T-SQL endpoints that require you to run the CREATE ENDPOINT command.
+ Amazon RDS has limited support for linked servers. When converting SQL Server application code that uses linked servers, AWS SCT converts the application code. However, make sure to review the behavior of objects that use linked servers before you run the converted code.
+ Always on is used.
+ The AWS SCT assessment report provides server metrics for the conversion. These metrics about your SQL Server instance include the following:
+ Data mirroring is used.
+ SQL Server Log Shipping is configured.
+ Failover cluster is used.
+ Database Mail is configured.
+ Full Text Search Service is used. Amazon RDS for SQL Server has a limited full text search, and it does not support semantic search.
+ Data Quality Service (DQS) is installed. Amazon RDS doesn't support DQS so we recommend that you install SQL Server on an Amazon EC2 instance.
## Privileges for RDS for SQL Server as a target
To migrate to RDS for SQL Server, create a database user and then grant the required privileges for each database. You can use the following code example.
```
CREATE LOGIN user_name WITH PASSWORD 'your_password';
USE db_name
CREATE USER user_name FOR LOGIN user_name
GRANT VIEW DEFINITION TO user_name
GRANT VIEW DATABASE STATE TO user_name
GRANT CREATE SCHEMA TO user_name;
GRANT CREATE TABLE TO user_name;
GRANT CREATE VIEW TO user_name;
GRANT CREATE TYPE TO user_name;
GRANT CREATE DEFAULT TO user_name;
GRANT CREATE FUNCTION TO user_name;
GRANT CREATE PROCEDURE TO user_name;
GRANT CREATE ASSEMBLY TO user_name;
GRANT CREATE AGGREGATE TO user_name;
GRANT CREATE FULLTEXT CATALOG TO user_name;
GRANT CREATE SYNONYM TO user_name;
GRANT CREATE XML SCHEMA COLLECTION TO user_name;
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *db\$1name* with the name of your target database. Finally, replace *your\$1password* with a secure password.