Amazon

Ola Hallengren :- AWS/RDS – SQL Server – IndexOptimize Job

Daniel Adeniji 1088, Amazon, Amazon Web Services ( AWS ), Microsoft, Ola hallengren, Relational Data Services ( RDS ), SQL Server, Technical , ,

Background

When Ola Hallengren’s job is ran against an AWS/RDS SQL Server Instance, you might get an error.

Error

Error Text

Cannot find the object "rdsadmin.dbo.log_backup_manifest" because it does not exist or you do not have permissions.

Error Image

rdsadmin.dbo.log_backup_manifest.20190206.0950AM

Remediation

Modify script or job and have its skip rdsadmin and other databases that you do have not have sufficient permissions only.

Script

Outline

  • For benefit of AWS/RDS, please skip databases that you do not have access sql to
    • Databases :- rdsadmin
    • What was
      • @Databases = ‘USER_DATABASES’
    • Will become
      • @Databases = ‘USER_DATABASESmicrosoft, -rdsadmin’,

Code

Original


  INSERT INTO @Jobs ([Name], CommandTSQL, DatabaseName, OutputFileNamePart01)
  SELECT 'IndexOptimize - USER_DATABASES',
         'EXECUTE [dbo].[IndexOptimize]' + CHAR(13) + CHAR(10) + '@Databases = ''USER_DATABASES'',' + CHAR(13) + CHAR(10) + '@LogToTable = ''' + @LogToTable + '''',
         @DatabaseName,
         'IndexOptimize'

Revision


  INSERT INTO @Jobs ([Name], CommandTSQL, DatabaseName, OutputFileNamePart01)
  SELECT 'IndexOptimize - USER_DATABASES',
         'EXECUTE [dbo].[IndexOptimize]' + CHAR(13) + CHAR(10) + '@Databases = ''USER_DATABASES'
		 + CASE WHEN @AmazonRDS = 1 THEN ', -rdsadmin' ELSE '' END + ''','
		 + CHAR(13) + CHAR(10) + '@LogToTable = ''' + @LogToTable
		 + '''',
         @DatabaseName,
         'IndexOptimize'

SQL Server Agent

It is also possible to change individual jobs

Outline

  1. DatabaseIntegrityCheck – USER_DATABASES
  2. Index Optimize – USER_DATABASES

DatabaseIntegrityCheck – USER_DATABASES

Already in place as

Textual

EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'USER_DATABASES, -rdsadmin',
@LogToTable = 'Y'

Image

DatabaseIntegrityCheck - USER_DATABASES.20190206.1021AM

Index Optimize – USER_DATABASES

Images

Image – Before

IndexOptimize - USER_DATABASES.before.20190206.1010AM

Image – After

IndexOptimize - USER_DATABASES.after.20190206.1009AM.PNG

Cross Check

2019-02-07 6:26 AM PST

Sojourned here

And, here is the code

Image

github.code.20190207.0625AM

Textual

  1. DatabaseIntegrityCheck
    • Includes check for @AmazonRDS
  2. IndexOptimize
    • Does not include check for @AmazonRDS

Acknowledged

Acknowledging the good work and listening of Ola Hallengren.

gratitude.20190206.1145AM

 

AWS/RDS – SQL Server – Self Identify

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), Microsoft, Relational Data Services ( RDS ), SQL Server, Technical

Background

Playing around more and more with AWS/RDS.

There is quite a bit of restrictions compared to self hosted platform.

Self Identify

To use some code on premise or cloud hosted, need to sprinkle in conditional statements.

Code

Outline

Here are some fingerprints to look for :-

  1. Server Name
    • AWS/RDS
      • EC2AMAZ-N4O
  2. Databases
    • RDSADMIN

SQL


declare @servername sysname
declare @bAWS       bit
declare @bAWSRDS    bit

set @servername
	= cast(serverproperty('servername') as sysname)

set @bAWS =
	isNull(charindex( 'EC2AMAZ-', @servername), 0)

set @bAWSRDS =
	isNull( (db_id('rdsadmin') ), 0)

print 'server :- ' + @servername
print 'AWS :- ' + cast(@bAWS as varchar)
print 'AWSRDS :- ' + cast(@bAWSRDS as varchar)

Output

Platform – RDS

Platform – RDS – Image – 01

selfIdentify.AWSRDS.20190205.1202PM

 

AWS – Account’s – Canonical User ID

Daniel Adeniji Amazon Web Services ( AWS ), CLI ( AWS ), Identity and Access Management ( IAM ) - AWS, Management ( S3 ), S3, S3 Bucket, Security Credentials ( AWS - IAM )

Background

Reviewing access to an S3 Bucket and noted a lone account has access.

GUI

Amazon S3 / Bucket

S3Bucket.Permissions.AccessControlList.20190131.0539PM.PNG

Translate Canonical ID

Let us translate the Canonical ID

Outline

  1. CLI
    1. Install CLI
    2. Configure CLI
    3. Issue S3 list bucket command

Tasks

Install CLI

Install CLI from here

Configure CLI

Syntax


aws configure

Syntax

C:\>aws configure
AWS Access Key ID sonwlson
AWS Secret Access Key 19101
Default region name [US East]: us-east-1
Default output format [None]:

Commands

aws s3api list-buckets

Syntax

aws s3api list-buckets

Output

{
    "Buckets": [
        {
            "Name": "cf-templates-us-east-1",
            "CreationDate": "2019-01-16T20:28:17.000Z"
        },
        {
            "Name": "dbsqlserver",
            "CreationDate": "2019-01-24T00:13:01.000Z"
        },
        {
            "Name": "dbsqlserver.virginia",
            "CreationDate": "2019-01-26T11:55:58.000Z"
        },
        {
            "Name": "dbsqlserveruseast",
            "CreationDate": "2019-01-30T18:30:56.000Z"
        }
    ],
    "Owner": {
        "DisplayName": "aws",
        "ID": "25"
    }
}

Explanation

The canonical ID is listed in the section owner\ID.

S3 Browser – Pro – Purchase

Daniel Adeniji Management ( S3 ), S3, S3 Bucket, s3browser.com

Background

Having evaluated S3 Browser for about a week now.

Enjoy using it, but will like more.

 

Pro Version

It is time to upgrade from the free to Pro.

Purchase

To purchase please go here.

Purchase Pro

Prepare

purchase.ProVersion.20190131.0917AM.PNG

Shopping Cart

purchase.yourshoppingcart.20190131.0927am

Payment Method

purchase.YourShoppingCart.PaymentMethod.20190131.0929AM.PNG

 

Activate

Outline

  1. Please connect to the Host that you will be sourcing from
    • Activate
      • Ensure host as Internet access
      • Enter activation key
      • Click “Activate button”
      • Wait to receive activation message

 

Activation

activate.cropped.20190131.0939am

Successful Activation

activate.successful.20190131.0942AM.PNG

Determine Currently Installed Version

Outline

  1. Launch App
  2. Version Info available
    • Top Menu Bar
    • Menu
      • Help / About

Images

Top Menu Bar

Image – Free

version.free.dadeniji.20190201.0705AM

Textual
  1. Version Number :- 8-1-5
  2. Free Version ( for non-commercial use only )
Image – Pro

version.pro.dadeniji.20190201.0712AM.PNG

Textual
  1. Version Number :- 8-1-5
  2. Pro Version

 

Help / About

Image – Free

version.help.about.free.dadeniji.20190201.0748AM

Textual
  1. Version Number :- 8-1-5
  2. Free Version ( for non-commercial use only )
Image – Pro

version.help.about.pro.dadeniji.20190201.0747AM.PNG

Textual
  1. Version Number :- 8-1-5
  2. Pro Version

 

AWS – Discussion Forums – Registration

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), Discussion Forums ( Amazon - AWS - Services ), Technical

Background

Running into petty roadblocks with AWS and so it is time to look for help.

Forum

URL

The forum’s URL is https://forums.aws.amazon.com/index.jspa ( Link ).

Registration

Though one might already be registered as an Amazon buying customer, Amazon Web Services usage and registration is another thing, and so is the privilege to post questions in the AWS forum.

Outline

  1. Launch web browser
  2. Go here
  3. Member ?
    • If existing user, please click on the My Account / Console button
    • If new user, please click on the Sign Up button
  4. Register for Discussion Forums
    • IAM
      • Choose an IAM Discussion Forum Nickname
      • Enter your Email Address
  5. Wait till your Account is created and validated

 

Images

Home Page

userregistration.signup.20190126.0852am

Discussion Forums – AWS Identity & Access Management ( IAM ) – Forums Nickname & Email

userregistration.forumnicknameandemail.20190126.0902am

Your Account is not ready for posting messages

userRegistration.accountNotReadyForPostingMessages.20190126.0835AM.PNG

 

 

Discussion Thread

Outline

  1. Start Discussion Thread
  2. Thread
    • Thread Header
    • Thread Body
    • Thread Preview
      • Because of markup it is important to use the Preview pane
        • See if some of the body’s text are being interpreted as markup
        • See if actual markup are effectual
  3. Wait till your Account is created and validated
  4. Post Messages

Images

Start Discussion Thread

Plain Text

forumQA.20190126.0832AM.PNG

Preview

forumQA.20190126.0836AM.PNG

AWS/S3 – File Move and Rename using S3 Browser

Daniel Adeniji Amazon, Amazon Web Services ( AWS ), S3, S3 Bucket, s3browser.com, Technical

Background

Common File Utility functions includes upload & download and create & delete; as well as move and rename.

S3 Browser

File Move and Rename

S3 File Move and Rename are easy to trigger through Web browsers and S3 Utilities.

When we do so, please keep in mind that the change is is not in place, but an actual copy of the originating file unto the new file and removal of the old.

Images

Tab – Tasks

Image

file.rename.tab.Tasks.20190125.1123AM.PNG

Explanation

  1. Moving the file
  2. Two chunks of 32 MB are moved at a time

Tab – EventLog

Image

file.rename.tab.EventLog.cropped.20190125.1123AM.PNG

Explanation

  1. File is broken into chunks
  2. Each chunk is processed
    • Copying part
    • Successfully copied

Summary

Key points :-

  1. Pay studious attention
    • You might be using more bandwidth than you intend
    • And, things may take a lot longer than you expe

 

Dedicated

Dedicated to Stack Exchange / StackOverflow.

References

  1. Stackoverflow
    • How to rename files and folder in Amazon S3?
      Link

AWS/S3 Management via S3Browser

Daniel Adeniji Amazon Web Services ( AWS ), S3, S3 Bucket, s3browser.com

Background

Needing to move files to AWS S3, but unfortunately the file is quite big and my browser is not cooperating.

S3Browser

Googled for help and one of the utilities that came up is S3Browser.

Prerequisite

AWS

AWS – IAM

AWS – IAM – Users – Security Credentials – Overview

S3Browser requires the AWS User’s security credentials.

To get the credentials, please connect to the AWS Console.

And, navigate to the the IAM Service.

Once in IAM, trek to Users and specific user account.

Once you have the user, please click on the “Security Credentials” tab.

If access keys are not listed, please click on the “Create Access key” button.

Capture the generated Access Key and password.

AWS – IAM – Images

AWS – IAM – Users – Security Credentials

Users.user.IAM.Summary.SecurityCredentials.20190124.0852AM.PNG

AWS – IAM – Users – Security Credentials – Access Keys

Users.user.IAM.Summary.SecurityCredentials.AccessKeys.20190124.0853AM.PNG

 

Artifacts

It is available from here.

As of today, 2019-01-24, current version is 8.1.5.

Installation

Outline

Installation steps is most straightforward and only included here for completeness.

  1. Welcome
  2. License Agreement
  3. Select Destination
  4. Select Additional Tasks
  5. Completing

Images

Welcome

welcome_20171206_0952AM.png

License Agreement

license_20171206_0953AM.png

Select Destination

SelectDestinationLocation_20171206_0954AM.png

Select Additional Tasks

SelectAdditionalTasks_20171206_0955AM.png

Completing

CompletingTheS3BrowserSetupWizard_20171206_0956AM.png

 

Usage

Steps

  1. Register AWS Account
  2. File Management

Registration

Outline

  1. Security Information
    • Access Key ID
    • Secret Access Key

Registration – Initial

addNewAccount_20171206_0955AM.png

Registration – Revised

s3client.register.20190124.0711am

 

File Management

Sessions

Session –  Uploaded Files

fileManagement.20190124.0733AM.PNG

Session –  Uploading File
Image

S3Client.usage.DBBackupFile.20190124.0942PM.PNG

Explanation
  1. File Size :- 235 GB
  2. Status :- 2.92%
  3. Speed :- 103.42 MB/s

 

Summary

Performance is throttled on the free edition.

If you are happy with ease and usability, I will encourage you to consider the paid version.