Restore DB to AWS instance - Error - Aborted the task because of a task failure or a concurrent RESTORE_DB request. Task has been aborted Access Denied

What I tried to do?
Restore SQL Server database backup file (.bak) to AWS RDS instance. (see how to restore here)
After run the restore command, the restoring task was failed with the error message below.
(see error message by running: exec msdb.dbo.rds_task_status @task_id = 8)

Error message:
[2020-02-17 13:06:22.063] Aborted the task because of a task failure or a concurrent RESTORE_DB request. 
[2020-02-17 13:06:22.097] Task has been aborted [2020-02-17 13:06:22.097] Access Denied

Possible solutions:
1. If you don't use Master:
GRANT EXECUTE ON dbo.rds_restore_database to [USERNAME]

2. 1. If you use Master:
Creating an IAM Policy to Access Amazon S3 Resources
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.S3CreatePolicy.html
Creating an IAM Role to Allow Amazon Aurora to Access AWS Services
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.CreateRole.html
Add role to the DB instance
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.Authorizing.IAM.AddRoleToDBCluster.html

3. Another option: The bak file is not valid or encrypted.
(try to restore other bak and see if it also failed).

Restoring a SQL Server database backup file to AWS RDS instance

What we want to do?
Restore SQL Server database backup file (.bak) to AWS RDS instance.
We have:
1. A bak file
2. An AWS RDS instance.

What we can't do?
We can't use the "restore" task ib SSMS - because we cann't access the local file system of the SQL Server (it handled by AWS).

What we can do?
Restoring using Amazon S3 - the object storage of AWS.

Restoring steps:

In Amazon S3:
1. Create bucket
a. Enter a name.
b. Enter the same Region as in the target RDS instance.
2. Upload the bak file to the bucket.

In AWS RDS:
3. Setting an Option Group
a. Create an option group in AWS RDS --> Options Groups tab.
b. Add SQLSERVER_BACKUP_RESTORE option to the option group.
4. Modify settings in the AWS RDS instance
a. on the databases list, sign your instance and check "Modify" button.
b. Under "Database options" section - select the Option group that was created in step 

In SQL Server Management Studio (SSMS):
5. run msdb.dbo.rds_restore_database (see script below). this SP crates a task to restore the DB
6. check task execution:
exec msdb.dbo.rds_task_status @task_id = 8

(A diagram from aws site)

-- msdb.dbo.rds_restore_database script:

-- ==============================================
-- Declaration
-- ==============================================
DECLARE @par_restore_db_name sysname
DECLARE @par_s3_arn_to_restore_from nvarchar(2048)
DECLARE @par_kms_master_key_arn nvarchar(256)
DECLARE @par_type nvarchar(256)
DECLARE @par_with_norecovery int

----------------------------
-- Set parameter values:
----------------------------
SET @par_restore_db_name = N'ItaiDB'
SET @par_s3_arn_to_restore_from = 'arn:aws:s3:::itaibckt/ItaiDB.bak'
-- @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name.extension'
SET @par_with_norecovery = 0
-- 0 = restore with RECOVERY. The database is online after the restore.
-- 1 = restore with NORECOVERY. The database remains in the RESTORING state after restore task completion.
--SET @par_kms_master_key_arn = 
-- If you encrypted the backup file, the KMS key to use to decrypt the file.
--SET @par_type = 'FULL'
-- Type of restore: DIFFERENTIAL or FULL. The default is FULL

-- ==============================================
-- Execution
-- ==============================================
EXECUTE dbo.rds_restore_database
@restore_db_name = @par_restore_db_name,
@s3_arn_to_restore_from = @par_s3_arn_to_restore_from,
--@kms_master_key_arn = @par_kms_master_key_arn,
--@type = @par_type ,
@with_norecovery = @par_with_norecovery
GO

-- ==============================================
-- check task execution:
-- ==============================================
exec msdb.dbo.rds_task_status @task_id = 8
GO

Connect to AWS RDS SQL Server instance from SSMS

Connect to AWS RDS SQL Server instance from SSMS:

Server name: <Endpoint>,<port>
Authentication: SQL Server Authentication
Login: <Master username>
Password: <Master password>

Find Endpoint and port in AWS portal, under RDS-->Databases--><Your DB identifier>, Connectivity & security tab.

PostgreSQL - translate encoding char to numbers and vice versa

select pg_encoding_to_char(6);
--Result: UTF8

select pg_char_to_encoding('UTF8');
--Result: 6

SQL Server - Disable Parameter Sniffing

CREATE PROCEDURE MyProcedure(@MyParam INT)
AS
SELECT ...
FROM ...
WHERE  MyParam = @MyParam
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

PostgreSQL - EXPLAIN - Show execution plan

EXPLAIN
Display the execution plan which PostgreSQL generates.
EXPLAIN ANALYZE
Display more statistics. Display actual run-time statistics.

In order to analyze INSERT, UPDATE, DELETE without affecting the data – run EXPLAIN ANALYZE in a transaction:

BEGIN;

    EXPLAIN ANALYZE sql_statement;

ROLLBACK;

CTE - SQL Server vs PostgreSQL

With myCTE AS (SELECT * FROM dbo.MyTable)

SELECT *

  FROM myCTE 

  WHERE Id = 1;

In SQL Server CTEs are processed with the main query.
In PostgreSQL CTEs are processed separately from the main query

Implications of this difference are:

1. A query that should touch a small amount of data instead reads a whole table and possibly spills it to a tempfile.
2. You cannot UPDATE or DELETE FROM a CTE term, because it’s more like a read-only temp table rather than a dynamic view.
3. In PostgreSQL, in this query:

With myCTE AS (SELECT * FROM dbo.MyTable)

SELECT *

  FROM myCTE 

  WHERE Id = 1;

WHERE clauses aren’t applied until the execution of the main query, and it’s different than:

With myCTE AS (SELECT * FROM dbo.MyTable WHERE Id = 1)

SELECT *

  FROM AllPosts;

PostgreSQL - relation "pg_stat_statements" does not exist

What I did:
select * from pg_stat_statements;
-- and pg_stat_statements is enabled (if not - check here)

Error message:
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

Cause:
pg_stat_statements is not set in postgresql.conf.
It probably looks like:
#shared_preload_libraries = '' # (change requires restart)
or with other libraries.

Solution:
1. Set pg_stat_statements in postgresql.conf; adding:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

2. Restart PostgreSQL service

Check it:
SHOW shared_preload_libraries;

PostgreSQL - relation "pg_stat_statements" does not exist

What I did:
select * from pg_stat_statements;

Error message:
ERROR:  relation "pg_stat_statements" does not exist
LINE 1: select * from pg_stat_statements
                      ^
Cause:
pg_stat_statements is not available globally but can be enabled for a specific database with CREATE EXTENSION.

Solution:
Enable pg_stat_statements:

CREATE EXTENSION pg_stat_statements;

Check it:
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
SELECT * FROM pg_available_extension_versions WHERE name = 'pg_stat_statements';

SAP ASE (Sybase) - Select jobs list

sp_sjobhelp