This is an overview of an AWS multiple account, multiple company, SQL server performance monitor that I am working on.
This will create a near-real time monitor for multiple companies using multiple AWS accounts.
Each Database server will collect monitoring stats, then output those a csv file which is then sent to the main company account in AWS S3, imported into a DW and available for reporting.
Once the ten parts of this series are completed, my final dashboard will look something like this.
I will be using the following set up:
Separate AWS account. 1 in region eu-west-1 all the others in region us-east-1. A mixture of EC2 instances with various versions of SQL 2017+ installed. And RDS instances with SQL Enterprise 2017 or 2019 installed. One will have a read-only replica as well. Some of my environments use the default AWS KMS Encryption Key and others have custom AWS KMS Encryption keys. Permissions will be handled so S3 buckets can be accessed by both. One of my RDS servers does not use integrated MS Active Directory, but the others do.
Given the mixture of various set ups, I will be running the MAIN HEADQUARTERS database DW (data warehouse) where everything ends up, in our main company environment with the following specs:
An EC2 instance with SQL Server 2019, SSIS, SSRS. This will instance will process our files and reports, however, the database with the actual data is located on an RDS server running SQL 2019 Enterprise with multi-AZ and a read-only replica.
Note 1: This “ServerAnalysisDW” database could theoretically be located on any server. Likewise, SSIS, SSRS or Power-Bi could be separated out onto different servers. We just happen to have them all on the same EC2 instance for now.
Prerequisites:
This assumes you have S3 Integration installed and configured on RDS.
This assumes you have SSIS installed and configured on RDS and EC2 instances, except for RDS with read replicas. (I’ll demonstrate how to work around that.)
This assumes you have SSIS and SQL Agent permissions set up on RDS correctly.
This assumes you have experience with RDS SSIS and SQL Agent Credential and Proxy set up.
This assumes you have PowerShell with AWS CLI set up on the main EC2 SQL Server instance. We’ll go over permission configurations later.
TABLE OF CONTENTS
- Part 1: Overview (This post)
- Part 2: Active Directory Configurations
- Part 3: SQL Server Analysis Database Setup
- Part 4: AWS Bucket & IAM User Setup
- Part 5: Capturing Metrics
- Part 6: Outputting Metrics to a CSV File (SSIS)
- Part 7: Pulling the Metrics into the Data Warehouse (SSIS)
- Part 8: Creating Reports (Views & Sprocs)
- Part 9: Creating Dashboard & Reports (SSRS)
- Part 10: Snapshots & Fine Tuning for Performance
- BONUS 1: Power BI Dashboard