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

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

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

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

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

 

rds_restore_database – Issue – “A WebException with status ConnectFailure was thrown”

Background

Issued “rds_restore_database“, but experiencing issues.

Command

Here is the command we issued

Sample


declare @dbname sysname
declare @s3ARN  varchar(255)

set @dbname = 'csLogins'
set @s3ARN = 'arn:aws:s3:::db.Virginia/backup/dev/csLogins/csLogins.bak'

exec msdb.dbo.rds_restore_database
          @restore_db_name= @dbname
        , @s3_arn_to_restore_from=@S3ARN
		;

Troubleshooting

SQL

Sample


exec msdb.dbo.rds_task_status
		;

Output

msdb.dbo.rds_task_status.20190130.1224pm

Error

Error Message

Aborted the task because of a task failure or a concurrent RESTORE_DB request.
Task has been aborted
A WebException with status ConnectFailure was thrown

Remediation

Outline

  1. Create new S3 bucket
    • Name
      • Make sure that special characters such as periods are not in use
  2. Change SQL Statement
    • Change SQL Statement to refer to new S3 Bucket
      • Original
        • arn:aws:s3:::db.Virginia/backup/dev/csLogins/csLogins.bak
      • Revised
        • arn:aws:s3:::dbVirginia/backup/dev/csLogins/csLogins.bak

Sample


declare @dbname sysname
declare @s3ARN  varchar(255)

set @dbname = 'csLogins'
set @s3ARN = 'arn:aws:s3:::dbVirginia/backup/dev/csLogins/csLogins.bak'

exec msdb.dbo.rds_restore_database
          @restore_db_name= @dbname
        , @s3_arn_to_restore_from=@S3ARN
		;

AWS – Discussion Forums – Registration

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

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