Amazon Athena Overview

Amazon Athena is an interactive query service that makes it easy to analyze data directly in Amazon S3 using standard SQL.
Use cases : Buisness intelligence / analytics / reporting, analyze, & query VPC Flow logs, ELB logs, CloudTrails, etc.

- Serverless query service to analyse data stored in Amazon S3.
- Use standard SQL language to query files(built on pesto).
- Support CSV, JSON, ORC, Avro and Parquet.
- Pricing $5.00 per TB of data scanned.
- Commonly used with Amazon Quicksight for reporting/dashboards.

Amazon Athena : Performance Improvement

- Use Columnar Data for cost savings (by doing less scan).

- Apache Parquet or ORC is recommended.
- Huge performance improvement.
- Use Glue to convert your data to Parquet or ORC format.

- Compress data for smaller retrievals (bzip2, gzip, Iz4, snappy, zlip...)
- Partition datasets in S3 for easy querying on virtual columns

- s://yourbucketname/pathtotable

/[PARTITION_COLUMN_NAME]=[VALUE]
/[PARTITION_COLUMN_NAME]=[VALUE]
etc..

- Example s3://athenabucket/flight/parquet/year=1999/month=1/day=1/

- Use larger files (>128MB) to minimize overhead.

Amazon Athena : Federated Query

- Allows you to run SQL queries across data stored in relation, non-relational, objects or custom data sources (AWS or on-premises).

- Uses Data Sources Connectors that run on AWS Lambda to run federated queries (like on Cloudwatch logs, DynamoDB, RDS DB, Elasticache etc..)

- Store results back in Amazon S3.

Amazon DocumentDB Overview

We will talk about DocumentDB so we all know about Aurora RDS and Aurora RDS is AWS implementation of Postgresql or MySQL because it has the same API but it's built on AWS and it has some nice benefits to be cloud native.
DocumentDB is the same as Aurora RDS but for MongoDB (Which is NoSQL database).

- Mongodb is used to store, query and index JSON data so thanks to DocumentDB.
- Similar "deplyement concept" as Aurora RDS.
- Fully Managed, highly available with replication across 3 AZ.
- DocumentDB storage automatically grows in increments of 10GB.
- Automatically scales to workloads with millions of requests per seconds.

If using MongoDB on AWS so you pay for what you use in DocumentDB and there is no upfront cost but you need to understand the Architecture of DocumentDB, which is very similar to Aurora in order to understand its pricing.

DocumentDB - Pricing

- Pay as you Go, so pay for what you use. No up front cost.
- On-Demand Instances (pay per second with the minimum of 10 minutes)
- Database I/O: amount of IO used when Read/Write (per million IOs)
- Database Storage (per GB/month)
- Backup Storage (per GB/month)

We have database storage and then we have On Demand instances. We have primary instance and then replica instances that's up to you're going to pay for these on-demand instances, you can pay per second with the minimum of 10 minutes then these instances are going to do a read and write operations against your database storage so you're going to base for database IOs and this going to be counted per million IOs then of course the data is being stored on your database storage and therefore you're going to pay for the derby storage itself in gigabytes per month.

Amazon Timestream Overview

As name indicates, Amazon Timestream is a time series database so it's fully managed, fast, scalable, serverless time series database. Use cases for timestream would be to have an IoT application, operational applications, real-time Analytics or everything related to a Time series database.

- Automatically scale up/down to scale capacity.
- Store and analyse trillions of events per day.
- 1000s times faster & 1/10th the cost of relation databases.
- Schedule queries, multi-measure records and SQL Compatibility.
- Data storage tiering : Recent data kept in memory and historical data kept in a cost-optimised storage.
- Built-in time series analytics functions (helps you identify patterns in your data in real-time.
- Supports Encryption in-transit and at rest.

Amazon Timestream Architecture

Timeseries database can receive data from AWS IoT so internet of things, the Kineses Data Streams through them that can receive data as well, Prometheus, Telegraf, their integrations for that case the streams as well through conditions data analytics for Apache link can receive data into Amazon time stream and Amazon msk as well through the same process.

In terms of what can connect to do timesstream where can build a dashboards using Amazon Quicksight and machine learning using Amazon SageMaker, can connect to grafana or because there is a standard jdbc connection into your database and the application that is compatible with jdbc and SQL can leverage Amazon timestream.

Amazon Redshift Overview

Amazon Redshift is a data warehouse product which forms part of the larger cloud-computing platform Amazon Web Services.

- Redshift is based on PostgreSQL, but it's not used for OLTP. 
- It's OLAP - Online Analytical Processing (analysis and data warehousing).
- Built on top of technology from the massive parallel processing data warehouse company ParAccel, to handle large scale data sets and database migrations.
- 10X better performance than other data warehouses, Scale to PBs of data.
- Columnar storage of data (instead of row based data).
- Pay as you go based on the instances provisioned.
- Can use SQL interface for performing the queries.
- BI tools such as AWS QuickSight or Tableau integrate with it.
- How to load data into Redshift?
Data is loaded from Amazon S3(using COPY command), Kinesis Firehose (To load data near real-time), DynamoDB, DMS (Data Migration Service)...
- Based on node type : up to 100+ nodes, each node can have up to 16 TB of storage space.
- Can provision multiple nodes, with Multi-AZ only for some clusters.
- There are two types of nodes in Redshit :

- Leader Node : For query planning and result aggregation from compute node.
- Compute Node : For performing the queries, and send result to leader node.

- As Redshit is a managed service; so we only get Backup & Restore, Security VPC enhancements /IAM (for accessing cluster)/KMS (for encryption), Monitoring using Cloudwatch.
- Redshift Enhanced VPC routing: COPY / UNLOAD goes through VPC (for better performance and lower cost).
- Redshift is provisioned, so it's worth it when you have a sustained usage (use Athena if the queries are sporadic instead).

Redshift - Snapshot & DR

- Snapshots are point-in-time backups of your cluster, stored internally in S3.
- Snapshots are incremental (Only what has changed in your redshift cluster will be saved).
- You can restore a snapshot into a new cluster (You've to create new cluster to restore the data).
- There are two types of snapshots :

- Automated Snapshot : Happens every 8 hours or every 5 GB of data change in the cluster or on a schedule you set. And set retention ( for example, like for 30 Days and after 30 days snapshot will be automatically deleted).
- Manual Snapshot : On-demand snapshot, retained until you delete it.

It is very similar, how RDS works. But one cool thing in Redshift, you can configure Amazon Redshift to automatically copy snapshots(automated or manual) of a cluster to another AWS Region and this is very useful to setup disaster recovery for your Redshift cluster.

Cross-Region Snapshot Copy for an KMS-Encrypted Redshift Cluster

How to copy a cross-regions snapshot for kms encrypted redshift snapshots?

You have the source and your snapshot is in your source region it's encrypted using the KMS Key A, and you want to copy it into destination came as KMS key B so what we need to do is call a redshift "snapshot copy grant" and that it will allow redshift the service to perform encryption operations in the destination region when this is done then you can copy your snapshot from the original one into the region 2 and then it will be encrypted by redshift with the correct kms key so the the really magical thing here that happens is that you need to have and create a snapshot to copy.

Redshift Spectrum

- Query data that is already in S3 without loading it. - Must have a Redshift cluster available to start the query. - The query is then submitted to thousand of Redshift Specturm nodes.

How does that work?

Here, our existing redshift cluster has a leader node and has a bunch of compute nodes then if we do a query on data on Amazon S3 that will look like above image then what's going to happen is that redshift is going to spin up a lot of virtual spectrum nodes they will do the computation and the query over the data set in Amazon S3 and then once the result is there is going to be sent back to the compute nose for aggregation and then will be rolled back into the leader node.

Redshift WorkLoad Management (WLM)

- It enables you to flexibly manage queries priorities within workloads.
- Use case for this is to prevent short files from inquiries from getting stuck behind long running queries.
- You can define multiple query queues you get a very simple super user queue and user defined queue and so on and then,
- Route your query to the proper queue at runtime.

Lets take an example, here in Amazon redshift and have three queues. One is the Super User Queue, the short running queue and the long running queue the names are pretty explicits and say we have two kinds of users we have Admin and User.

Now, they want their queries to be done as soon as possible right so they will do system queries and then these queries will go directly into the Super User Queue.
And this will have priority the user may have some short-running queries and these we want to have into their short-running query queue this way we are sure that it will just be a lot of short-running queries.
But if the user is submitting a longer running query and we're not gonna take a lot of time then we should send it directly into the long running queue to make sure not to block the short running queries.
So we have two kind of work load management :
- Automatic WLM : where the cues and resources are managed by shift.
- Manual WLM : which accusing resources are managed by you (i.e. the user).

Redshift Concurrency Scaling Cluster

- It enables you to provide closely fast performance with virtually and limited number of users and queries.
- Redshift automatically adds additional cluster capacity on the fly so this additional concert capacity is called Concurrencies Scaling Cluster and allows you to process an increase in request.
- Ability to decide which queries sent to the Concurrencies Scaling Cluster using WLM.

So let's say in the below image, we have a normal version cluster with a few nodes under and then this sustains you know a bunch of users that we have normally let's say tomorrow we get a lot more users submitting queries into Amazon redshift then automatically the concurrency scaling cluster will add those automatically to accommodate for these number of users.

Now we get the ability to decide which queries get sent to the concurrency is getting cluster using the WLM feature we just saw in previous discussion and this feature is charged per second.

Choosing the Right Cloud Database Service offered by cloud providers : AWS, Azure and GCP

The majority of cloud providers started off treating databases as application that could be run on general-purpose compute instances. However, they quickly started adding higher-level application services to their IaaS offerings. Cloud databases have grown to be a crucial area of technological advancement, with manufacturers battling for customers by introducing several database kinds.

Architects, administrators, and developers must be aware of the various database options available because the IT industry is currently experiencing a significant displacement of data-centric workloads to the cloud.

Ensure that the IT team (including DBA and App Team) choose the cloud database provider that best suits the requirements of your business by following the below tips :

1. Become familiar with the database's options:

The deployment architecture and the database requirements are the two main factors to take into account while selecting cloud database services. Each element will include parameters specific to a given application.

The following are the variables that influence cloud database selection:
- Deployment environment: on-site, in a public cloud or clouds, or a combination of hosted and private resources.
- Database manager: specifically, whether the database will be self-managed or a cloud service (DBaaS).
- Database type: including relational SQL (RDBS), distributed SQL, NoSQL variants like a column (key-value) store, wide column or document DB, cache, graph, or various specialty databases like time series and quantum ledger.
- Availability and resilience requirements: these will dictate whether many replicated and synchronized instances are required.
- Performance and capacity requirements: these include transactions per second, query latency, and scalability of both database throughput and capacity.
- Flexibility: defining precise version criteria and the degree of authority required for an IT staff to manage more intricate elements like database setup and configuration.

IT teams including DBA's and Application team should investigate the spectrum of database services offered by AWS, Microsoft Azure, and Google Cloud Platform to suit their individual requirements.

2. Examine cloud trade-offs:

Cloud database services are more cost-effective for businesses looking to cut costs than self-managed internal database systems because they do not require hardware, system administration, or any DBA overhead. Using cloud goods built on open source or in-house produced software, which does not require license costs, also increases the savings for businesses.

Cloud database services work well for businesses that:
- Desire the simplest, fastest implementation possible.
- Desire access to a large selection of database products without having to set up and maintain each one.
- Reluctant to oversee the software.
- Require maximum expandability.
- Desire high availability through a number of widely separated instances.
- Recognize the benefits of the cloud's intrinsic security and its shared security paradigm; additionally, you don't have to micromanage every database option.

In general, cloud users need to understand the limitations on the capacity and performance of specific database instances. Nonetheless, problems may frequently be handled using a distributed architecture that takes advantage of networking and replication capabilities included in cloud solutions.

3. Analyze your consumption and business:

Consider some of the following aspects while making the best choice for the workloads at your organisation.
First, database and cloud infrastructure products are better suited for new applications than for aging ones. IT departments may decide to shift old systems to the cloud, meanwhile, if they wish to get off outdated hardware, experience utilization spikes, and anticipate cost savings from the cloud's consumption-based pricing. IT teams may need to relocate a historical database in order to connect it with more recent apps if an organization is pursuing a cloud-native strategy.

However, a hybrid cloud deployment—where on-premises data is replicated to or accessed from the cloud—is typically a better starting point and provides better risk management than a lift-and-shift database migration if you're hesitant to jump right into the cloud. By using a hybrid strategy, IT can also move its primary system from the on-premises database to the cloud, with the on-premises database serving as a backup.

Systems architects and application developers need to be especially mindful of service costs when designing cloud-based environments, regardless of the cloud database they use. This is because careless usage and setups can quickly negate any potential savings from cloud deployments.

RDS SQL Server : Enable and Configure Database mail profile

In this article will discuss about enabling and configuring Database Mail on AWS RDS SQL Server.

Before configuring Database Mail, you've to first enable it through a database parameter group.
In Amazon RDS, parameter groups act as a container for engine configuration values that are applied to one or more DB instances. Each RDS instance comes with an associated default parameter group; however, we can’t modify it.

You can either use a new parameter group or an existing created parameter group. If choosing an existing parameter group, it must support your SQL Server instance edition and version.
To enable Database Mail through a parameter group, complete the following steps:

- On the Amazon RDS console, choose Parameter groups.
- Choose the parameter group you want to use.
- In the search box, enter database mail xps.
- Choose Edit Parameters to modify the value.
- For Values, choose 1.
- Save your changes.
- On the Amazon RDS console, choose Databases.
- Choose the instance you want to use.
- Choose Modify.
- Under Database options, choose the parameter group that has database mail xps set to 1.
- Choose Continue.
- Under Scheduling of modifications, choose Immediately.
- Choose Modify DB Instance to apply the changes.

Before we can use Database Mail, we need to set up a mail configuration.
- Launch SQL Server Management Studio.
- Connect to the SQL Server engine of the RDS instance that Database Mail is enabled for.
- Open a new query.
Use the following stored procedures to create a simple Database Mail configuration.

- Create a Database Mail profile (a profile is a container used to store email accounts). See the following code:
use msdb
go

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Notifications',
@description = 'Profile used for sending outgoing notifications using SES.' ;

- Add principles to the profile; use public so any user can access the profile:
use msdb
go

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Notifications',
@principal_name = 'public',
@is_default = 1 ;

We can grant the permissions on Database Mail objects as needed, but public is fine for now.
- Create the Database Mail account (make sure to enter the correct SMTP credentials):

use msdb
go

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Acc1',
@description = 'Mail account for sending outgoing notifications.',
@email_address = 'example@example.com',
@display_name = 'Automated Mailer',
@mailserver_name = 'email-smtp.us-west-2.amazonaws.com',
@port = 587,
@enable_ssl = 1, @username = 'SMTP-username',
@password = 'SMTP-password' ;

- Add Database Mail account to the Database Mail profile:
use msdb
go

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Notifications',
@account_name = 'Acc1',
@sequence_number = 1 ;

- Sending a test email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'success@simulator.amazonses.com',
@body = 'The database mail configuration was completed successfully.',
@subject = 'Automated Success Message';
GO

Then run this stored procedure to see all email items:
SELECT * FROM msdb.dbo.sysmail_allitems

Please refer the below document for more details on the same : [+] Using Database Mail on Amazon RDS for SQL Server; https://aws.amazon.com/blogs/database/using-database-mail-on-amazon-rds-for-sql-server/

RDS SQL Server : Script to automate native backup of all the individual databases using msdb.dbo.rds_backup_database Stored procedure

You can use the below command to automate native backup of all the databases using msdb.dbo.rds_backup_database Stored procedure. You can also use the below script to schedule as a agent job to take native backup (in .bak file) of your all databases.

Script to take Full backup without encryption

DECLARE @BackupFileName varchar(50)
DECLARE @DBName sysname
DECLARE @S3ARN_Prefix nvarchar(100)
DECLARE @S3ARN nvarchar(100)
SET @S3ARN_Prefix = 'arn:aws:s3:::awsbucket_name/' -- update your bucketname

DECLARE DBBackup CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('rdsadmin', 'master', 'model', 'msdb', 'tempdb')
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND is_read_only = 0

OPEN DBBackup
FETCH NEXT FROM DBBackup INTO @DBName
WHILE (@@FETCH_STATUS = 0)

BEGIN
SET @BackupFileName = @DBName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),20),'-',''),':',''),' ','') + '.bak'
SET @S3ARN = @S3ARN_Prefix + @BackupFileName
EXEC msdb.dbo.rds_backup_database
@source_db_name = @DBName,
@S3_arn_to_backup_to = @S3ARN,
@overwrite_S3_backup_file = 0
FETCH NEXT FROM DBBackup INTO @DBName
END

Script to take Full backup with encryption

DECLARE @BackupFileName varchar(50)
DECLARE @DBName sysname
DECLARE @S3ARN_Prefix nvarchar(100)
DECLARE @S3ARN nvarchar(100)
DECLARE @KMS_master_key_ARN nvarchar(100)
SET @S3ARN_Prefix = 'arn:aws:s3:::awsbucket_name/' -- update your bucketname
SET @KMS_master_key_ARN = 'arn:aws:kms:us-east-1:XXXXXXXXXX:key/xxxxxxxx-xxxx-xxxx-xxxx-xxxx' -- update your kms key ARN

DECLARE DBBackup CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('rdsadmin', 'master', 'model', 'msdb', 'tempdb')
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND is_read_only = 0

OPEN DBBackup
FETCH NEXT FROM DBBackup INTO @DBName
WHILE (@@FETCH_STATUS = 0)

BEGIN
SET @BackupFileName = @DBName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),20),'-',''),':',''),' ','') + '.bak'
SET @S3ARN = @S3ARN_Prefix + @BackupFileName
EXEC msdb.dbo.rds_backup_database
@source_db_name = @DBName,
@S3_arn_to_backup_to = @S3ARN,
@KMS_master_key_arn = @KMS_master_key_ARN,
@overwrite_S3_backup_file = 0
FETCH NEXT FROM DBBackup INTO @DBName
END

To track the status of the job, use this SQL statement:

exec msdb..rds_task_status @task_id= 5 -- 5 as an example of your task id

RDS SQL Server : How to reduce the size on disc for RDS SQL Server database?

First of all I would like to explain you that SQL Server databases have two types of files; Data File and Log File.

1. Data files contain data and objects such as tables, indexes, stored procedures, and views.
Data files can be shrink, however it is not a good practice to shrink the data file unless necessary as it can result into blocking, dead locks and index fragmentation. The below documentation to provides more insights on why shrink data files should be avoided.

[+] https://littlekendra.com/2016/11/08/shrinking-sql-server-data-files-best-practices-and-why-it-sucks/
[+] https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
2. Log files contain the information that is required to recover all transactions in the database.

Log Files can be shrink, based upon the log space utilization and on left a transaction open and running.
Now if you would like to know which file in the database is taking more space can use below command to identify file utilization:

USE [your_database_name]
GO

SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
GO

Then accordingly perform the shrink, Here are the steps to perform the shrink for database files:

For Log files:
Firstly, I would humbly request you to check if you have an open or active transaction. The following queries may help:

1. DBCC SQLERF(LOGSPACE)
>> Provides the log space utilization
[+] How can I troubleshoot storage consumption in my Amazon RDS DB instance that is running SQL Server? -
https://aws.amazon.com/premiumsupport/knowledge-center/rds-sql-server-storage-optimization/
2. SELECT name, log_reuse_wait_desc FROM sys.databases
>> Provides why each database's log file is not clearing out
[+] sys.databases (Transact-SQL)
- https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15
>> Includes the possible reasons[+]:
a. Log backup needs to be run
b. Active backup running - because the full backup needs the transaction log to be able to restore to a specific point in time
c. Active transaction - someone typed BEGIN TRAN and locked their workstation for the night

[+] My Favorite System Column: LOG_REUSE_WAIT_DESC
- https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/
As already mentioned above, you can perform the shrink on the transaction log using the below command.

Step 1: Please run the below command to get the used space and free space of transaction log in the database [+]
DBCC SQLPERF(LOGSPACE); --> review transaction log file utilization per database level, if it is more than 80% free then only perform step2
[+] https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-2017
Step 2: Query to shrink transaction log file
USE [userdb-name]
GO
DBCC SHRINKFILE (logfilename, sizeinmb)

If this instance is vital to production, I would also like to recommend using CloudWatch Alarms [+] to monitor your 'FreeStorageSpace' to
prevent Storage Full issues. The alarm will monitor this metric and notify you when the defined threshold is breached, so you may take
immediate actions to increase storage.
[+] How can I create CloudWatch alarms to monitor the Amazon RDS free storage space and prevent storage full issues?
- https://aws.amazon.com/premiumsupport/knowledge-center/storage-full-rds-cloudwatch-alarm/
For Data Files:

You can use same 'DBCC SHRINKFILE' command for shrinking a data file to a specified target size.

The following example shrinks the size of a data file named 'DataFile1' in the user database to 1024 MB.

USE [userdb-name];
GO
DBCC SHRINKFILE (DataFile1, 1024);
GO

OR From GUI- right click on database>click task> shrink> files > select data file from drop down.
I would also like you to know that data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.
To know fragmentation information for the data and indexes of the specified database ‘DB_name’

  SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
	avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
	FROM sys.dm_db_index_physical_stats
	(DB_ID(DB_name'), NULL, NULL, NULL , 'SAMPLED')
	ORDER BY avg_fragmentation_in_percent DESC

To rebuild all indexes in a table, here is the sample query:
	ALTER INDEX ALL ON DatabaseName.SchemaName
	REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

[+] https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15
These suggestions are based on best effort basis, to conclude I would highly recommend you to please test these thoroughly before implementing them on any production environment. This will help in proactive testing to avoid any unforeseen issues during actual implementation.

SQL Server : Capture expensive queries using Server side trace

You can follow the below steps to capture the expensive queries by creating the trace and also from SQL cache plan DMV's :

1. Create Server Side trace: by defining a trace, its the events and columns to captured, and add the filter condition.

You can use the below Script to create the trace:

-- Create Server-side trace
-- Create a Queue
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
set @maxfilesize = 2048

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

DECLARE @MyFileName nvarchar(256)
SELECT @MyFileName='D:\RDSDBDATA\Log\RDSSQLTrace_' + CONVERT(nvarchar(10),GetDate(),121) + '_' + REPLACE(CONVERT(nvarchar(10),GetDate(),108),':','-')


exec @rc = sp_trace_create @TraceID output, 0, @MyFileName, @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on bit
set @on = 1 --sp_trace_setevent @traceid ,@eventid ,@columnid,@on

-- Capturing the RPC:Completed event

EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 17, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 8, @on
EXEC sp_trace_setevent @TraceID, 10, 10, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 35, @on

--Capturing the SQL:BatchCompleted event

EXEC sp_trace_setevent @TraceID, 12, 16, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 17, @on
EXEC sp_trace_setevent @TraceID, 12, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 18, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on
EXEC sp_trace_setevent @TraceID, 12, 8, @on
EXEC sp_trace_setevent @TraceID, 12, 10, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 35, @on

-- Set the Filters
EXEC sp_trace_setfilter @TraceID,11,0,7,N'%NT AUTHORITY%' --SQL login
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'RdsAdminService'

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- display trace id and file name, Note these details for future references
SELECT @TraceID,@MyFileName

goto finish

error:
select ErrorCode=@rc

finish:
go

2. Start the Trace using below command :

-- To Start the trace (Please update the trace_id as you may recived at the time of creation)
Exec sp_trace_setstatus @traceid = 2 , @on = 1 -- start

3. Start the application test run

4. Once test run is completed, Stop the trace using below command:

-- To Stop the trace (Please update the trace_id as you may recived at the time of creation)

Exec sp_trace_setstatus @traceid = 2 , @on = 0 -- stop
-- To Delete the trace, once application test is completed.
--Exec sp_trace_setstatus @traceid = 2 , @on = 0 -- Delete definition from server

5. Insert the trace output in dummy table, this helps to get the data in easy to read format.

USE [DB_name]
GO
SELECT * INTO RDSSQLTrace_20210722_1215 FROM ::fn_trace_gettable ('D:\RDSDBDATA\Log\RDSSQLTrace_2021-07-22_12-15-19.trc', default)
6. Capture the output of below commands and update the output in the attached excel sheet template in the TraceData sheet (File Name : Capture_ExpensiveQueries_Template.xlsx)

--To collect queries based on max Duration 
SELECT TOP 10 StartTime,EndTime,Reads, Writes, 
Duration/1000000 DurationSec,CPU, TextData,RowCounts,sqlHandle,[DatabaseName],
SPID,LoginName,EventClass FROM [gptst].[dbo].[RDSSQLTrace_20210722] 
ORDER BY Duration DESC

--To collect queries based on max CPU utilization
SELECT TOP 10 StartTime,EndTime,Reads, Writes, 
Duration/1000000 DurationMs,CPU, TextData,RowCounts,sqlHandle,[DatabaseName],
SPID,LoginName,EventClass FROM [gptst].[dbo].[RDSSQLTrace_20210722] 
ORDER BY CPU DESC

--To collect queries based on max Reads,Writes
SELECT TOP 10 StartTime,EndTime,Reads, Writes, 
Duration/1000000 DurationSec,CPU, TextData,RowCounts,sqlHandle,[DatabaseName],
SPID,LoginName,EventClass FROM [gptst].[dbo].[RDSSQLTrace_20210722]
ORDER BY Reads,Writes DESC

7. Also, capture the output of the below scripts as well and update the same in the attached excel sheet template in the CacheData sheet (File Name : Capture_ExpensiveQueries_Template.xlsx)

--TOP 10 Queries by Duration (Avg Sec) from system dmv's		
			
SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
o.name AS ObjectName,
qs.total_worker_time / 1000000 / qs.execution_count As Avg_CPU_time_sec,
qs.total_worker_time / 1000000 as Total_CPU_time_sec,
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS Average_Seconds,
qs.total_elapsed_time / 1000000.0 AS Total_Seconds,
'',
qs.execution_count as Count,
qs.last_execution_time as Time,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
--,qt.text [MainQuenry]
--,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qs.execution_count>5 and DB_NAME(qt.dbid) not in ( 'master','msdb','model')
--and DB_NAME(qt.dbid) is not null
ORDER BY average_seconds DESC

--TOP 10 Queries by CPU (Avg CPU time) from system dmv's

SELECT TOP 10 DB_NAME(qt.dbid) as DBName,
	qs.total_worker_time / 1000000 / qs.execution_count AS Avg_CPU_time_sec,
	qs.total_worker_time / 1000000 As Total_CPU_time_sec,
	qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
	qs.total_elapsed_time / 1000000 As Total_Seconds,
    (total_logical_reads + total_logical_writes) / qs.execution_count as Average_IO,
	total_logical_reads + total_logical_writes as Total_IO,	
    qs.execution_count as Count,
    qs.last_execution_time as Time,
	SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
		(
			(
				CASE qs.statement_end_offset
					WHEN -1 THEN DATALENGTH(qt.[text])
					ELSE qs.statement_end_offset
				END - qs.statement_start_offset
			) / 2
		) + 1
	) as Query
   ,qt.text
	,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where 
DB_NAME(qt.dbid) not in ( 'master','msdb','model')
ORDER BY Avg_CPU_time_sec DESC

-- Top 10 query by IO (Avg IO) from system dmv's

SELECT TOP 10 DB_NAME(qt.dbid) AS DBName,
o.name AS ObjectName,
qs.total_elapsed_time / 1000000 / qs.execution_count As Average_Seconds,
qs.total_elapsed_time / 1000000 As Total_Seconds,
(total_logical_reads + total_logical_writes ) / qs.execution_count AS Average_IO,
(total_logical_reads + total_logical_writes ) AS Total_IO,
'',
qs.execution_count AS Count,
last_execution_time As Time,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query
--,qt.text
--,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where last_execution_time > getdate()-1 
and DB_NAME(qt.dbid) not in ( 'master','msdb','model')
--and DB_NAME(qt.dbid) is not null
and qs.execution_count > 5 
ORDER BY average_IO DESC

Note : Please do capture the output after each run if running the test multiple times, to compare the result set.

RDS SQL Server : Unable to restore and getting an error message : "There is not enough space on the disk to perform restore database operation."

If you are getting an error message as "There is not enough space on the disk to perform restore database operation."

-- This issue appears when rds restore starts, RDS makes an estimate about how much disk space will be required by the database that is going to be restored. If that estimate exceeds the amount of free space left on the disk, you will see this error.

Kindly note that as part of the restore process is trying to look for the data and log file size as was configured on the source and if the amount of space is not available on disk upfront its failing the restore process in the first place while trying perform the restore and also the SQL server restore process will not initiate the Storage Autoscaling.

For example: lets say the source database (On-prem\EC2\RDS sql server instance) is configured with 30 GB of data file and 30 GB of log file sizes and the used space inside the 30 GB data file is only 10 GB and used space inside the 30 GB log file is only 10 GB.The backup size of the database in this case would be only 20 GB(with compression this backup file size can further be reduced) out of actual 60 GB configured on the source database.During the restore of this 20 GB backup file on the RDS SQL server instance the process looks for the 60 GB of space on the underlying storage per the source data and log file configurations.In this case if the Target RDS database instance if it has 50 GB of underlying storage so restore process would fail as it requires 60 GB of storage space to proceed with the restore and it fails with the space issue.

Now I would suggest you to please check the actual database size on the source database using below script:
--Useful script to know the current size of database files and the free space.
USE [DBNAME] -- update your db name
GO
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

If log is using the most of the storage space then you can try to shrink the log file and then again the backup and try to restore again.

AWS : How to connect S3 Bucket from EC2 Instance which is in Private VPC without Internet access?

Problem:

How to upload the files to S3 bucket from the Win Server (i.e. EC2 Instance) which is in private vpc with no general internet access?

 

Solution:

First of all, as you already know to enable an upload you need to ensure that there is a connectivity between Server and S3 Buckets.

So there are different approaches based upon Setup and Configuration, few options explained as follows:

1. If the EC2 instance and the S3 bucket are in the same AWS region, Then Using VPC Endpoint : You can use VPC Endpoints and associate it with the route table of the EC2 instance's subnet which would allow traffic to the S3 service in that region even if you don't have a NAT or Internet gateway associated with your subnet.

For more about S3 VPC endpoints:
    S3 VPC endpoint in detail:
        [+] https://docs.aws.amazon.com/vpc/latest/userguide/vpc-endpoints-s3.html
    create a VPC Endpoint:
        [+] https://aws.amazon.com/blogs/aws/new-vpc-endpoint-for-amazon-s3/

2. If the EC2 instance and the S3 bucket are in different AWS regions, Then Using NAT Gateway or Internet Gateway: You have to make use of either NAT Gateway or Internet gateway to allow traffic from your instance to the S3 service in that region.
If you dont want to keep the internet open on the server, you can later deny traffic between your EC2 Instance and S3 Service after copying the backup file on to S3 Bucket.

For more about NAT\Internet Gateways:
    NAT gateways :
     [+] https://docs.aws.amazon.com/vpc/latest/userguide/vpc-nat-gateway.html
    Internet gateways :
     [+] https://docs.aws.amazon.com/vpc/latest/userguide/VPC_Internet_Gateway.html
 

AWS : Relational Database Service (RDS) - Basics Part 4

 ==============================================================
How do you maintain my database ? How can I plan for it?

• Any maintenance that causes downtime (typically only a few times per year) will be scheduled in your maintenance window
• Operating system or Amazon RDS software patches are usually performed without restarting databases
• Database engine upgrades require downtime
    • Minor version upgrades — automatic or manually applied (Automatic not applicable in case of SQL Server, always manual)
    • Major version upgrades — manually applied
    • Version deprecations—three-to six-month notification before scheduled upgrades
• View upcoming maintenanceevents in your AWS Personal Health Dashboard

==============================================================
How am I charged for Amazon RDS ?

• Database instance (instance hours)
    • Combination of Region + instance type + database engine + license (optional)
• Database storage (GB-mo)
    • Can be either provisioned (Amazon EBS) or consumed (Amazon Aurora)
    • Provisioned IOPS (IOPS-Mo) for IO1 storage type
    • Database I/O requests (IOs) for Amazon Aurora and Amazon EBS magnetic-storage types
• Backup storage (GB-mo
    • Size of backups and snapshots stored in Amazon S3
    • No charge for backup storage up to 100% of total database storage
• Data transfer (GB-mo)
    • Uses AWS regional data-transfer pricing

==============================================================
How can I save money on my database?

• Amazon RDS Reserved Instances (RIs) provide a discount over on-demand prices
• Region, instance family, and engine of on-demand usage must match to apply benefit
• Amazon RDS RIs offer size flexibility for open-source and Oracle BYOL engines
• By default, RIs are shared between allaccounts in consolidated billing
• Use the RI utilization report to determinehow your RIs are being used
    • Support for RI coverage reportcoming soon

==============================================================
Can I stop my database when it’s not in use?

• Stop and start a running database instance from the console or AWS CLI
• Available for single-AZ DB instances
• While instance is stopped, you only pay for storage
• Backup retention window is maintained while stopped
• Instances are restarted after 7 days
    • Pending maintenance operations are applied
    • Instances can be stopped again if desired

==============================================================

AWS : Relational Database Service (RDS) - Basics Part 3

 ==============================================================
How do I secure my Amazon RDS database?

• Amazon RDS is designed to be secure by default
• Network isolation with Amazon Virtual Private Cloud (Amazon VPC)
• AWS Identity and Access Management (IAM)-based resource-level permission controls
• Encryption at rest using AWS KMS (all engines) or Oracle/Microsoft TDE
• Use SSL protection for data in transit

==============================================================
What does Amazon VPC provide?

• Places your instance in a private subnet, making it secure from public routes on the Internet
• Database instance IP firewall protection lets you securely control network configuration
• Turn off Public Accessibility in DB instance settings to restrict access outside Amazon VPC
• Use ClassicLink to network with non-VPC resources

==============================================================
How do I grant access to my database?

• Use IAM to control who can perform actions on RDS resources
• Do not use AWS root credentials to manage Amazon RDS resources—you should create an IAM user for everyone, including yourself
• Can use AWS Multi-Factor Authentication (MFA) to provide extra level of protection

==============================================================
How do I encrypt my database?

- Use AWS KMS-based encryption in the AWS console
- No performance penalty for encrypting data
- Centralized access and audit of key activity
- Best practices
    • Encryption cannot be removed from DB instances
    • If source is encrypted, Read Replicas must be encrypted
    • Add encryption to an unencrypted DB instance by encrypting a snapshot copy

==============================================================
How do I monitor my Amazon RDS database ?

- Amazon CloudWatch metrics
    • CPU/Storage/Memory•Swap usage
    • I/O (read and write)
    • Latency (read and write)
    • Throughput (read and write)
    • Replica lag
- Amazon CloudWatch Alarms
    • Similar to on-premises monitoring tools
- Enhanced monitoring for Amazon RDS
    • Access to over 50 CPU, memory, file system, and disk I/O metrics
    • Low as 1-second intervals
-Integration with third-party monitoring tools

==============================================================
How do I improve database performance?

• Introducing Amazon RDS Performance Insights
• Measures DB Load: Average Active Sessions (AAS)
• Identifies database bottlenecks (Top SQL):
    • Easy
    • Powerful
• Identifies source of bottlenecks
• Enables problem discovery
• Adjustable time frame
    • Hour, day, week, and longer
• Coming soon for Amazon EBS-based Amazon RDS engines

==============================================================
Can I know when service events happen?

• Amazon RDS uses Amazon SNS to receive notification when an event occurs
• Notifications can be in any form supported by Amazon SNS (email, a text message, or a call to an HTTP endpoint)
• Six different source types (DB instance, DB parameter group, DB security group, DB snapshot, DB cluster, DB cluster snapshot)
• 17 different event categories (availability, backup, deletion, configuration change, etc.)

==============================================================

AWS : Relational Database Service (RDS) - Basics Part 2

 ==============================================================
How do I ensure database high availability?

• Multi-AZ provides enterprise-grade fault-tolerance solution for production databases
    • Automatic failover
    • Synchronous replication
    • Inexpensive and enabled with one click

==============================================================
What happens during a Multi-AZ failover? How long does it take?

• Each host manages set of Amazon EBS volumes with a full copy of the data
• Instances are monitored by an external observer to maintain consensus over quorum
• Failover initiated by automation or through the Amazon RDS API
• Redirection to the new primary instance is provided through DNS

==============================================================
Why would I use Read Replicas?

• Relieve pressure on your source database with additional read capacity
• Bring data close to your applications in different regions
• Promote a Read Replica to a master for faster recovery in the event of disaster
• Upgrade a Read Replica to a new engine version•Supported for MySQL, MariaDB, and PostgreSQL

==============================================================
When should I use Multi-AZ ?

 • Synchronous replication—highly durable
 • Only primary instance is active at any point in time
 • Backups can be taken from secondary
 • Always in two Availability Zones within a Region•Database engine version upgrades happen on primary
 • Automatic failover when a problem is detected

==============================================================
When should I use Read Replicas ?

 • Asynchronous replication—highly scalable
 • All replicas are active and can be used for read scaling
 • No backups configured by default•Can be within an Availability Zone, cross-AZ, or cross-region
 • Database engine version upgrades independently from source instance
 • Can be manually promoted to a standalone databaseRead ReplicasMulti-AZ

==============================================================
How does Amazon RDS manage backups?

• Two options –automated backups and manual snapshots
• Amazon RDS backups leverage Amazon EBS snapshots stored in Amazon S3
• Transaction logs are stored every 5 minutes in Amazon S3 to support point-in-time recovery (PITR)
• No performance penalty for backups
• Snapshots can be copied across regions or shared with other accounts

==============================================================
When should I use automated backups?

• Specify backup retention window per instance (7-day default)
• Kept until outside of window (35-day maximum) or instance is deleted
• Supports PITR
• Good for disaster recovery

==============================================================
When should I use snapshots?

• Manually created through AWS console, AWS CLI, or Amazon RDS API
• Kept until you delete them
• Restores to saved snapshot
• Use for checkpoint before making large changes, non-production/test environments, final copy before deleting a databaseManual snapshots

==============================================================
How do I restore a backup? Why does it take so long?

- Restoring creates an entirely new database instance
    • Define the instance configuration just like a new instance
    • Will get the default parameter, security, and option groups

- New volumes are hydrated from Amazon S3
    • While the volume is usable immediately, full performance requires the volume to warm up until fully instantiated
    • Migrate to a DB instance class with high I/O capacity
    • Maximize I/O during restore process

==============================================================

AWS : Relational Database Service (RDS) - Basics Part 1

==============================================================
What is Amazon RDS?

• Managed relational database service in the AWS cloud
• Multi-engine support: Amazon Aurora, MySQL, MariaDB, PostgreSQL, Oracle, SQL Server
• Automated provisioning, patching, scaling, replicas, backup/restore
• Easily scales to handle growth•High availability with Multi-AZ and Amazon Aurora

==============================================================

Why use Amazon RDS?

• Lower TCO because RDS manage “the muck”
    • Get more leverage from your teams
    • Focus on the things that differentiate you
• Built-in high availability and cross-region replication across multiple data centers
• Even a small startup can leverage multiple data centers to design highly available apps with over 99.95% availability

==============================================================
Which RDS engine should I use?

Amazon EBS-based Storage :
- Commercial : SQL Server and Oracle
- Open Source : MySQL , PostgreSQL and MariaDB
Aurora Storage System :
- AWS Cloud Native : MySQL CompatiblePostgreSQL Compatible

==============================================================
Which instance type should I choose?

- T2 Family
    • Burstable instances
    • 1 vCPU/1 GB RAM > 8  vCPU 32 GB RAM
    • Moderate networking performance
    • Good for smaller or variable workloads
    • Monitor CPU credit metrics in Amazon CloudWatch
    • T2.micro is eligible for free tier

- M3/M4 Family
    • General-purpose instances
    • 2 vCPU/8 GiB RAM > 64 vCPU 256 GiB RAM
    • High-performance networking
    • Good for running CPU intensive workloads (e.g., WordPress)

- R3/R4 Family
    • Memory-optimized instances
    • 2 vCPU/16 GiB RAM > 64 vCPU 488 GiB RAM
    • High-performance networking
    • Good for query-intensive workloads or high connection counts
==============================================================
Configuring DB Instance Class Processor ?

Modify processor features to optimize DB instance for specific workloads:
• Number of CPU Cores –Optimize licensing costs for instance with sufficient RAM for memory-intensive workloads, but fewer CPU cores
• Threads per Core –Disable Hyper-Threading for, e.g. HPC workloads

Changes can be applied when you:
• Create a DB instance
• Modify a DB instance
• Restore a DB instance from a snapshot
• Restore a DB instance to a point in time
==============================================================
Which AWS EBS storage type should I choose?

- General purpose (GP2)
    • SSD storage
    • Maximum of 16 TB
    • Leverages Amazon EBS Elastic Volumes
    • IOPS determined by volume size
    • Minimum of 100 IOPS (below 33.33GiB)
    • Bursts to 3,000 IOPS (applicable below 1.3 TB)
    • Baseline of 16,000 IOPS (at 3.3 TB and above)
    • Affordable performance

- Provisioned IOPS (IO1)
    • SSD storage•Maximum of 16 TB
    • Leverages Amazon EBS Elastic Volumes
    • Maximum of 40K IOPS (32K on SQL Server)
    • Delivers within 10% of the IOPS performance 99.9% of the time
    • High performance and consistency

- Magnetic
    • Magnetic storage
    • Maximum of 1 TB
    • Supported for legacy databases

- General purpose (GP3), is coming soon.
==============================================================
How do I decide between GP2 and IO1 AWS EBS Storage Type? Why am I not seeing less IOPS or only 30 K IOPS?

• GP2 is a great choice, but be aware of burst credits on volumes < 1 TB
    • Hitting credit-depletion results in IOPS drop—latency and queue depth metrics will spike until credits are replenished
    • Monitor BurstBalance to see percent of burst-bucket I/O credits available
    • Monitor read/write IOPS to see if average IOPS is greater than the baseline

==============================================================
How do I scale my database instance? Will there be downtime ?

• Scale compute/memory vertically up or down
    • Handle higher load to grow over time
    • Lower usage to control costs
    • New host is attached to existing storage with minimal downtime

• Scale up Amazon EBS storage (now up to 16 TB)
    • Amazon EBS engines now support Elastic Volumes for fast scaling (now including SQL Server)
    • No downtime for storage scaling
    • Initial scaling operation may take longer, because storage is reconfigured on older instances
    • Can re-provision IOPS on the fly

==============================================================

Cloud Services Comparison: AWS Vs Azure Vs Google: Pricing Model

Cloud Services Comparison: AWS Vs Azure Vs Google:

Pricing Model The pricing model of these cloud service is to pay as you go; it means pay on the basis of usage.

Considering AWS Vs Azure Vs Google:
Amazon charges on an hourly basis, While Azure and Google charge on the minute basis.
Also can choose to make upfront payments - prepaid or monthly payments i.e postpaid 

Pricing Model
	AWS	Azure	Google
Pricing	Per Hour - Rounded Up	Per Minute - Rounded Up Commitments (Prepaid or Monthly)	Per Minute - Rounded Up (Minimun 10 Minutes)
Model	On-Demand Spot Reserved	On-Demand Short Term Commitments (Pre-Paid or Monthly)	On-Demand - Sustained Use

Cloud Services Comparison: AWS Vs Azure Vs Google: Networking and Content Delivery & Security

 Cloud Services Comparison: AWS Vs Azure Vs Google: Networking and Content Delivery

Networking and Content Delivery
Services	AWS	Azure	Google
Load Balancing Configuration	Elastic Load Balancing	Load Balancer Application Gateway	Cloud Load Balancing
Global Content Delivery Networks	CloudFront	Content Delivery Network	Cloud Interconnect
Manage DNS Name and Records	Route 53	Traffic Manager Azure DNS	Google Cloud DNS
Cross Premises Connectivity	API Gateway	VPN Gateway	Cloud VPN
Virtual Networking	Virtual Private Cloud	Virtual Network	Subnet
Dedicated Private Network Connection	Direct Connect	Express Route	-

 
Cloud Services Comparison: AWS Vs Azure Vs Google: Security

Security
Services	AWS	Azure	Google
Authentication and Authorization	Identity and Access Management (IAM)	Active Directory Active Directory Permium	Cloud IAM Cloud Identity-Aware Proxy
Protection with Data Encryption	Key Management Service	Storage Service Encryption	-
Firewall	Web Application Firewall	Application Gateway	-
Identity Management	Cognito	Active Directory B2C	-
Cloud Services with Protection	Shield	DDoS Protection Service	-

 

Cloud Services Comparison: AWS Vs Azure Vs Google: Development Tools

 Cloud Services Comparison: AWS Vs Azure Vs Google: Development Tools

Development Tools
Services	AWS	Azure	Google
Media Transcoding	Elastic Transcoder	Media Services	-
App Testing	Device Farm	DevTest Labs (Backend)	Cloud Test Lab
DevOps	Code Build	Visual Studio Team Services	-
Developer Tools	Developer Tools	Developer Tools	-
Git Repositories	AWS Source Repositories	Azure Source Repositories	Cloud Source Repositories

Cloud Services Comparison: AWS Vs Azure Vs Google: Management and Monitoring

Cloud Services Comparison: AWS VS Azure VS Google : Management and Monitoring

Management and Monitoring
Services	AWS	Azure	Google
Billing	Billing API	Billing API	Cloud Billing API
Cloud Advisor Capabilities	CloudWatch X-Ray Management Console	Portal Monitor Application Insights	Stackdriver Monitoring CloudShell Debugger Trace Error Reporting
DevOps Deployment Orchestration	OpsWorks (Chef-based) Cloud Formation	Automation Resource Manager VM Extensions	Cloud Deployment Manager
Cloud Resources Management and Monitoring	Trusted Advisor	Advisor	Cloud Platform Security