In this part of our SQL PERORMANCE MONITORING project we are going to create the databases that will hold our data. The following three (3) databases are needed. Only one (1) of them is required on each server instance.
For our demonstration purposes, anytime I refer to a server as being the “MAIN” or “HEADQUARTER” server, I am referring to an EC2 server at the MAIN COMPANY (A) that is running SQL Server, PowerShell, AWS CLI, and SSIS Integration services that process our CSV files. All of my instances use SSIS, in this example, except for the RDS Server that runs reporting. Currently SSIS can’t be installed on an RDS instance with read-only replicas, so we will run the SSIS package step for this server on our EC2 instance. (More about that later.)
| Database Name | Required on All Instances | SSIS Project/package Use | Reporting Use |
|---|---|---|---|
| ServerAnalysis | YES | YES, but no PowerShell | NO |
| ServerAnalysisDwStaging* | NO | YES, PowerShell Required | NO |
| ServerAnalysisDW* | NO | NO | YES |
ServerAnalysis Database
Create database ServerAnalysis with the tables shown above, using schema Analysis in place of dbo.
NOTE 1: The table ServerAnalysis.Analysis.Server should only contain the EC2 instance names associated with the current server instance. The same may be true with the ServerAnalysis.Analysis.PerfLocation table. However, if you auto update that table (like we do) you may need to keep it in sync with the master DW table. This means that the PerfLocation.ID column and the Server.PerfLocationID are the same and a circular reference. We auto update, since with RDS, AWS can switch to a new EC2 instance at any point in failover. We’ve even seen updates go bad and all associated EC2 instances attached to RDS recreated.
/****** Object: Table [Analysis].[PerfCounterLog] Script Date: 8/25/2022 8:56:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfCounterLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PerfLocationID] [int] NOT NULL,
[MetricID] [int] NOT NULL,
[DateKey] [int] NOT NULL,
[TimeKey] [char](6) NOT NULL,
[iVal] [bigint] NULL,
[dVal] [decimal](10, 4) NULL,
[PartName] [varchar](250) NULL,
[UtcDate] [datetime] NULL,
CONSTRAINT [PK_PerfCounterLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Analysis].[PerfCounterLogActions] Script Date: 8/25/2022 8:56:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfCounterLogActions](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ParentLogID] [int] NULL,
[PerfLocationID] [int] NULL,
[LocationID] [int] NULL,
[ClusterID] [int] NULL,
[ServerID] [int] NULL,
[DateKey] [int] NOT NULL,
[TimeKey] [char](6) NOT NULL,
[QueryName] [varchar](250) NULL,
[StepID] [int] NULL,
[StartID] [int] NULL,
[EndID] [int] NULL,
[Description] [varchar](250) NULL,
[Action] [varchar](250) NULL,
[FileName] [varchar](250) NULL,
[RunStartUtc] [datetime] NULL,
[RunEndUtc] [datetime] NULL,
[RunSuccess] [bit] NULL,
[RowsAffected] [int] NULL,
[ErrorCount] [int] NULL,
[VerifiedUtc] [datetime] NULL,
CONSTRAINT [PK_PerfCounterLogActions] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Analysis].[PerfCounterMetric] Script Date: 8/25/2022 8:56:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfCounterMetric](
[MetricID] [int] IDENTITY(1,1) NOT NULL,
[MetricIntervalMinutes] [int] NOT NULL,
[MetricName] [varchar](150) NULL,
[MetricDurationDesc] [varchar](50) NULL,
[MetricSubName] [varchar](250) NULL,
[MetricTsql] [varchar](max) NULL,
CONSTRAINT [PK_PerfCounterMetric] PRIMARY KEY CLUSTERED
(
[MetricID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Analysis].[PerfLocation] Script Date: 8/25/2022 8:56:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfLocation](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [int] NOT NULL,
[ClusterID] [int] NOT NULL,
[ServerID] [int] NOT NULL,
[LocationName] [varchar](150) NULL,
[ClusterName] [varchar](150) NULL,
[ServerName] [varchar](150) NULL,
CONSTRAINT [PK_PerfLocation] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Analysis].[Server] Script Date: 8/25/2022 8:56:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[Server](
[ServerID] [int] IDENTITY(1,1) NOT NULL,
[ServerNm] [varchar](50) NOT NULL,
[ServerResolvedName] [varchar](50) NULL,
[IsCurrent] [bit] NULL,
[LocationID] [int] NULL,
[ClusterID] [int] NULL,
[LocationName] [nvarchar](50) NULL,
[ClusterName] [nvarchar](50) NULL,
[PerfLocationID] [int] NULL,
CONSTRAINT [PK_Server] PRIMARY KEY CLUSTERED
(
[ServerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GOHere is a sample naming convention for the ClusterID for the PerfLocation table:
The METRICS table is where all the TSQL data definitions are stored.
The PERFORMANCE COUNTER LOG table is where the actual counters are stored. It holds up to 3 values that can be used, (in addition to the MetricID reference): iVal (BIGINT), dVal (DECIMAL(10,4), and PartName (VARCHAR(250)).
ServerAnalysisDwStaging Database
USE [ServerAnalysisDwStaging]
GO
/****** Object: Table [Analysis].[PerfCounterMetric] Script Date: 8/25/2022 9:45:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfCounterMetric](
[MetricID] [int] IDENTITY(1,1) NOT NULL,
[MetricIntervalMinutes] [int] NOT NULL,
[MetricName] [varchar](150) NULL,
[MetricDurationDesc] [varchar](50) NULL,
[MetricSubName] [varchar](250) NULL,
[MetricTsql] [varchar](max) NULL,
CONSTRAINT [PK_PerfCounterMetric] PRIMARY KEY CLUSTERED
(
[MetricID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Analysis].[PerfCounterStats] Script Date: 8/25/2022 9:45:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfCounterStats](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [int] NOT NULL,
[ClusterID] [int] NOT NULL,
[MetricID] [int] NOT NULL,
[PartName] [varchar](250) NOT NULL,
[DateKey] [int] NOT NULL,
[TimeKey] [char](6) NOT NULL,
[iVal] [bigint] NULL,
[dVal] [decimal](10, 4) NULL,
[UtcDate] [datetime] NULL,
CONSTRAINT [PK_PerfCounterStats] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Analysis].[PerfFileExtractionLog] Script Date: 8/25/2022 9:45:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfFileExtractionLog](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NULL,
[ServerName] [varchar](150) NULL,
[FilPath] [varchar](250) NULL,
[FileName] [varchar](550) NULL,
[RunStartUtc] [datetime] NULL,
[RunEndUtc] [datetime] NULL,
[RunSuccess] [bit] NULL,
[RowsAffected] [int] NULL,
[ErrorCount] [int] NULL,
CONSTRAINT [PK_PerfFileExtractionLog] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Analysis].[PerfLocation] Script Date: 8/25/2022 9:45:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfLocation](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [int] NOT NULL,
[ClusterID] [int] NOT NULL,
[ServerID] [int] NOT NULL,
[LocationName] [varchar](150) NULL,
[ClusterName] [varchar](150) NULL,
[ServerName] [varchar](150) NULL,
CONSTRAINT [PK_PerfLocation] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Analysis].[PerfCounterStats] ADD CONSTRAINT [DF_PerfCounterStats_PartName] DEFAULT ('') FOR [PartName]
GO
/****** Object: Table [Analysis].[PerfCounterLogStaging] Script Date: 8/25/2022 2:28:24 PM ******/
-- ALL FIELDS ARE VARCHAR for the quick importation of the CSV file. TRANSFORMATION happens in the SSIS package
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfCounterLogStaging](
[ID] varchar(50) NULL,
[PerfLocationID] varchar(50) NULL,
[MetricID] varchar(50) NULL,
[DateKey] varchar(50) NULL,
[TimeKey] varchar(50) NULL,
[iVal] varchar(50) NULL,
[dVal] varchar(50) NULL,
[PartName] [varchar](250) NULL
) ON [PRIMARY]
GO
ServerAnalysisDW Database
USE [ServerAnalysisDW]
GO
/****** Object: Table [Analysis].[PerfCounterStats] Script Date: 8/25/2022 9:31:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfCounterStats](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [int] NOT NULL,
[ClusterID] [int] NOT NULL,
[MetricID] [int] NOT NULL,
[PartName] [varchar](250) NOT NULL,
[DateKey] [int] NOT NULL,
[TimeKey] [char](6) NOT NULL,
[iVal] [bigint] NULL,
[dVal] [decimal](10, 4) NULL,
[UtcDate] [datetime] NULL,
CONSTRAINT [PK_PerfCounterStats] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [Analysis].[PerfCounterMetric] Script Date: 8/25/2022 9:31:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfCounterMetric](
[MetricID] [int] IDENTITY(1,1) NOT NULL,
[MetricIntervalMinutes] [int] NOT NULL,
[MetricName] [varchar](150) NULL,
[MetricDurationDesc] [varchar](50) NULL,
[MetricSubName] [varchar](250) NULL,
[MetricTsql] [varchar](max) NULL,
CONSTRAINT [PK_PerfCounterMetric] PRIMARY KEY CLUSTERED
(
[MetricID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Analysis].[PerfLocation] Script Date: 8/25/2022 9:31:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Analysis].[PerfLocation](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [int] NOT NULL,
[ClusterID] [int] NOT NULL,
[ServerID] [int] NOT NULL,
[LocationName] [varchar](150) NULL,
[ClusterName] [varchar](150) NULL,
[ServerName] [varchar](150) NULL,
CONSTRAINT [PK_PerfLocation] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GOThe above code with additional functionality will be available on my GitHub as soon as this series is complete. Download at: https://github.com/LoveTheSql .
NEXT we will set up our AWS buckets, users, and permissions.