SQL Server – Upgrade Advisor – v2012 – Error – “Attempt to perform an unauthorized operation”

Background

Tried running “Upgrade Advisor” against a SQL Server instance, but getting the error pasted below:

Attempted to perform unauthorized operation ( mscorlib )

Configuration

Btw, the SQL Server instance is running within the Data Center of one of our ISPs.  And, so we will likely have more network issues.

 

Steps to reproduce

Let us take a few steps back and see what occurred prior to getting the error message.

SQL Server Components

On the SQL Server Components window, we enter the Server name.

Please be sure to enter the actual MS Windows Host name or the Cluster name.  Create DNS entries or local host names if need be.

Once you have entered the Server name, please click the Next button.

SQLServerComponents-Full

 

Connection Parameters

Parameters

  1. Instance name: Default

 

ConnectionParameters

Error Message

If Domain Trust Relationship Not in place

Image

ConnectionParameters-NotTrusted

Textual

Unable to connect to server. Reason: Login failed for user ''. The user is not associated with a trusted SQL Server connection.

SQL Server Parameters

Choose the Databases to analyze…

SQLServerParameters

Confirm Upgrade Advisor Settings

ConfirmUpgradeAdvisorSettings

Ugrade Advisor Progress

Here is the error message we get …

UpgradeAdvisorProgress

Error Message

Please click the “Attempted to perf “… message

Error Message Box

Here is a more in-depth look at our error message.

AttemptToPerformAnUnauthorizedOperation-Msgbox

Error Message Details

Image

AttemptToPerformAnUnauthorizedOperation-AdvancedInformation

Textual


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

Attempted to perform an unauthorized operation. (mscorlib)

------------------------------
Program Location:

   at Microsoft.Win32.RegistryKey.Win32ErrorStatic(Int32 errorCode, String str)
   at Microsoft.Win32.RegistryKey.OpenRemoteBaseKey(RegistryHive hKey, String machineName, RegistryView view)
   at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.GetClusterInfo()
   at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.GetSqlInstances()
   at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.EngineExists()
   at Microsoft.SqlServer.UpgradeAdvisor.AnalyzerEngine.GetAnalyzer(AnalyzerType analyzerType)
   at Microsoft.SqlServer.UpgradeAdvisor.AnalyzerEngine.RunAnalyzer(AnalyzerType analyzerType, String& reportFile)


Diagnostic

SysInternals

Process Monitor

Tried to use SysInternal’s Process Monitor, but did not find what I was looking for.

Network Capture

As I said, I knew we likely had a Network Issue, and so a logical next step is so see if capturing network traffic yield anything.

 

netsh

We are using MS Windows 7 and as such network capturing is built-in.

Initiate Trace

Code

Here is a code that will let us tack network traffic between us and our DB Server.

Please change the IP Address and possibly the base capture folder.

 

@ECHO OFF 
REM http://snipplr.com/view/21573/print-datetime-in-dos-batch-file/ 

set _LOGFILE_DATE=%DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2% 
set _LOGFILE_TIME=%TIME:~0,2%_%TIME:~3,2%_%TIME:~6,2% 
set _LOGFILE=log-%_LOGFILE_DATE%-%_LOGFILE_TIME% 
set _fileExt=etl 
set _currentFolder=%cd% 
set _logFolder=%_currentFolder%\log 
set _logFileFull="%_logFolder%\%_LOGFILE%.%_fileExt%" 

if not exist %_logFolder% ( 
    mkdir %_logFolder% ) 

set "IPAddress=107.10.1.12" 

netsh trace start capture=yes Ethernet.Type=IPv4 IPV4.Address=%IPAddress% tracefile=%_logFileFull% 

 

 

Output

Image

NetworkTraceCapture - 20160430 - 1040AM

 

 

Stop Trace

Code


netsh trace stop

 

Output

NetworkTraceCaptureStopped - 20160430 - 1048AM

 

Microsoft Message Analyzer

Now that we have captured the Network Trace events.  We installed Microsoft Message Analyzer.

Filter

Upon loading our captured file, we filtered out SQL Server specific traffic


tcp.port != 1433

 

Output

LogonFailure=NTLM v1-cropped

Explanation

  1. Module :- SMB2
  2. Status :- STATUS_LOGON_FAILURE

Now, we know that SMB traffic (port 445) is fluid and we simply have authentication failure, let us deal with that.

 

Remediation

Run as

Initiate Command Session / as remote user

Syntax


runas /netonly /user:[remote-account] cmd

Sample


runas /netonly /user:ISP\daniel cmd

Enter your remote password

Use Initiated Command Session

Syntax


UpgradeAdvisorWizardCmd.exe

 

Summary

Thankfully, our network firewall rules are already fluid.  And, our registry access violation was simply because out corporate user did not have access.

Once we initiated a runas session and entered our ISP user credentials, we are good.

 

Amazon – AWS – RDS – SQL Server – DB Server – IP Address

Background

Wanted to see what it will take to access our RDS Server via its IP Address as different from accessing through the FQDN.

 

Console

RDS Home Page

Access our region specific RDS Home page ( https://us-west-2.console.aws.amazon.com/rds/home?region=us-west-2#dbinstances: ).

DBInstance

 

Get Matching IP Address

Code:


ping FQDN

Output:

ping

 

Validated

Validated that we are able to access the SQL Server Instance using the FQDN’s IP Address

What is the Server’s actual IP Address?

Dynamic Management View

SYS.DM_EXEC_CONNECTIONS

Code


SELECT 
		  [Machine Name] = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 
		, [ServerName] = SERVERPROPERTY('ServerName')
		, [IP Address Of SQL Server] = LOCAL_NET_ADDRESS
		, [IP Address Of Client] = CLIENT_NET_ADDRESS

FROM SYS.DM_EXEC_CONNECTIONS 

WHERE SESSION_ID = @@SPID

 

Output

SYS

The IP Address of SQL Server is 172.30.0.95

CONNECTIONPROPERTY

Code


SELECT  
	     net_transport       = CONNECTIONPROPERTY('net_transport')
	   , protocol_type       = CONNECTIONPROPERTY('protocol_type')
	   , auth_scheme         = CONNECTIONPROPERTY('auth_scheme')
	   , local_net_address   = CONNECTIONPROPERTY('local_net_address')
	   , local_tcp_port      = CONNECTIONPROPERTY('local_tcp_port') 
	   , client_net_address  = CONNECTIONPROPERTY('client_net_address')

Output

CONNECTIONPROPERTY

The IP Address of SQL Server is again noted as 172.30.0.95.

Attempt DB Server Access using Server’s IP Address

If you try to access the DB Server using the IP Address 172.30.0.95, you will not be able to.

 

Is your DB Server IP Address private?

MxToolbox

Go to http://mxtoolbox.com/ReverseLookup.aspx and determine if our address is private

Enter Address

ReverseLookup

Response

IsAPrivateIPAddress

Is a private IP address…

 

Commentary

It is important to either mark the DB Server as publicly available or to properly configure the VPC to properly route traffic behind the scene to the DB Server’s private IP Address.

Amazon – AWS – Free Tier – RDS – Create Instance

RDS

Create Instance

Launch a DB Instance

We access our region specific RDS Dashboard ( https://us-west-2.console.aws.amazon.com/rds/home?region=us-west-2 )

DB Instances Exists

If DB Instances exists, the count of DB Instances will be listed besides the “DB Instances” item.

CreateInstance-LaunchADBInstance

 

DB Instances Do Not Exist

If DB Instances do not exist.

URLs

  1. Region Specific

 

EmptyDBInstances

 

 

Select an engine

Initial

Here is the initial screen for choosing the DB Engine.

We can see that the default is Amazon Aurora.

SelectEngine-Original

 

SQL Server

Once we select SQL Server, we can see the editions of SQL Servers available – Express, Web, Standard, and Enterprise.

SelectEngineSQLServer (Express-Web--StandardEdition--EnterpriseEdition)

 

SQL Server Express

Again, we go the free route.

And, so we will choose “Microsoft SQL Server Express Edition”.

Specify DB Details

Initial

SpecifyDBDetails-Initial

 

Constrain to Free Tier – Off

When we do not have free tier checked here is our screen.

SpecifyDBDetails-OnlyShowOptionsThatAreEligibleForRDSFreeTrier-Off

 

Explanation
  1. db.t2.micro – 1 vpc, 1 Gib RAM
  2. db.t2.micro – 1 vpc, 0.613 Gib RAM

 

Constrain to Free Tier – On

When we have free tier checked here is our screen.

SpecifyDBDetails-OnlyShowOptionsThatAreEligibleForRDSFreeTrier-On

Availed
  1. A single db.t2.micro instance
  2. 20 GB of storage
Purpose
  1. Allows new AWS customers to gain hands-on experience with Amazon RDS
DB Instance Class
  1. db.t2.micro – 1 vpc, 1 Gib RAM
  2. db.t2.micro – 1 vpc, 0.613 Gib RAM

 

Choices

SpecifyDBDetails-Completed

 

Explanation

  1. DB Engine :- sqlserver-ex
  2. License Model :- license-included
  3. DB Engine Version :- 12.00.4422.0.v1
  4. DB Instance Class :- db.t1.micro — 1 vCPU, 1 GiB RAM
  5. Storage Type :- Magnetic
  6. Allocated Storage :- 20 GB
  7. Storage Type :- Magnetic
  8. Allocated Storage :- 20 GB
  9. DB Instance Identifier :- adriel
  10. Master username :- sa
  11. Master Password :- xxxx
  12. Confirm Password :- xxxx

Btw, the name adriel means “flock of God“; as seen here

 

Configure Advanced Settings

ConfigureAdvancedSettings-Original

 

Configure Advanced Settings – Network & Security

ConfigureAdvancedSettings - Network & Security

 

Configure Advanced Settings – Microsoft SQL Server Windows Authentication

ConfigureAdvancedSettings - Microsoft SQL Server Windows Authentication

 

Configure Advanced Settings – Database Options

ConfigureAdvancedSettings - Database Options

Configure Advanced Settings – Backup

ConfigureAdvancedSettings - Backup

 

Configure Advanced Settings – Monitoring

ConfigureAdvancedSettings - Monitoring

Configure Advanced Settings – Maintenance

ConfigureAdvancedSettings - Maintenance

 

 

Your DB Instance Is Being Created

YourInstanceIsBeingCreated

We are told that our “Database Instance is being created“….

And, assigned a couple of follow up items.  And, those are:

  1. Configure Security group
  2. Consider Amazon Elasticache
    • Memcached
    • Redis-compatible in-memory cache

 

Review Database Instance Creation Progress

URL

  1. Region Specific URL

 

Status – Creating

ReviewDBInstanceCreationProgress

Columns

  1. Engine :- SQL Server Express
  2. DB Instance :- adriel
  3. Status
    • Creating
    • backing-up
  4. Class :- db.t2.micro
  5. VPC :- vpc-75d97a11
  6. Multi-AZ :- N/A
  7. Replication Role
  8. Encrypted :- No

Status – Backing-up

ReviewDBInstanceCreationProgress-Status-backingup

 

Status – available

ReviewDBInstanceCreationProgress-Status-available

 

VPC

VPC Dashboard

VPC Resources

Here are our currently assigned VPC Resources

VPCDashboard-Initial

 

VPC Resources

Here is a current list of VPC Resources

VPCResources

Which one is our SQL Server Instance using?

Here is one way to determine our DB Instance’s VPC:

  1. Access RDS Dashboard

 

DB Instance

ListDBInstances

 

VPC Resource – VPC Selected

VPCSelected

 

  1. VPC ID :- vpc-75d97a11
  2. State :- available
  3. VPC CIDR :- 172.30.0.0/16
  4. Route Table :- rtb-8ba921ef
  5. Network ACL :- acl-2b06b44f

 

Security

There are a couple of choices for guiding our DB Instance availability.

Those choices are Network ACLs and Network Groups.

Security Groups

URL

  1. Region Specific

 

Here are the Network Groups that are currently assigned to us:

Security-SecurityGroups-List

 

 

Which Security Groups?

Which security groups are relevant to our VPC

  1. VPC
    • We know that our VPC is vpc-75d97a11
      • And, so we will ignore Group ID sg-a95d78ce, at this time
      • And, focus on sg-32fbc955 ( default ) and sg-07fbc960 ( rds-launch-wizard )

Took to the Net and found

What are the default security groups created when I set up AWS EB for the first time?
http://stackoverflow.com/questions/27829620/what-are-the-default-security-groups-created-when-i-set-up-aws-eb-for-the-first

 

Here is Scuba Dev’s response

  1. rds-launch-web
    • When you manually launch an EC2 VM from the web console, AWS will provide you with the option of reusing an existing security group or creating a new one.
    • When you create a new one, the default rule is SSH (port 22) and a default security group name of “launch-wizard-#“.
  2. default

It looks like either will do.

Out of curiosity let us dig deeper, by clicking on each security group and reviewing its present construct.

VPC Security Group – default

DefaultVPCSecurityGroup-Initial

 

VPC Security Group – RDS Launch Wizard

InboundRules

Tabulated

Group Name Group ID Type Product Port Range Source
Default sg-32fbc955
 All Traffic  ALL  ALL  sg-32fbc955
rds-launch-wizard  sg-07fbc960
 MS SQL (1433)  TCP (6)  1433  207.140.111.60/32

 

 

Explanation

  1. Default
    • The default group is wide open
  2. RDS Launch Wizard
    • Type = MS SQL Server (1433)
    • Product = TCP (6)
    • Port Range = 1433
    • Source = 207.140.111.60 / 32
      • Because the subnet is 32, the range is the lone host ( 207.140.111.60 )

Specificity is good here and so we will choose the “RDC Launch Wizard”

Security Groups – RDC Launch Wizard

Expand to Self

Get Public IP Address

Authorizing Access to an instance
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/authorizing-access-to-an-instance.html
Decide who requires access to your instance; for example, a single host or a specific network that you trust. In this case, we use your local system’s public IP address. You can get the public IP address of your local computer using a service. For example, we provide the following service: http://checkip.amazonaws.com. To locate another service that provides your IP address, use the search phrase “what is my IP address”. If you are connecting through an ISP or from behind your firewall without a static IP address, you need to find out the range of IP addresses used by client computers.

When we access http://checkip.amazonaws.com/, we received http://checkip.amazonaws.com/.

checkip

As suggested, you can simply google same, what is my ip address ( https://www.google.com/#q=what+is+my+ip+address ).

 

Review & Add Public IP Address

Let us expand our IP Addresses by adding our public IP Address

Here are the currently listed IP Addresses

ManageExistingInboundRules

 

Acknowledgement.

Only now did I notice that our public listed IP Address is the one auto-added, in the first place.

RDS

Console

Review DB Instance

ConnectionInformation-20160428-0222PM-Cropped

 

Explanation

  1. Endpoint: adriel.[xxxxx].us-west-2.rds.amazonaws.com:1433
  2. DB Instance: adriel
  3. Status : available
  4. Connection Information
    • Publicly Accessible : No
    • Master Username: sa
    • Security Group Rules
      • Security Group
        • rds-launch-wizard
          • Type :- CIDR-IP – bound
          • Rule  :- 207.140.111.60/32

 

Client

SQL Server Management Studio

Connect to DB Instance

ConnectToServer

 

Error Messages

Error=25AndError=87

 

Image

AdvancedInformation

Textual


A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (.Net SqlClient Data Provider)

Error Number: 87
Severity: 20
State: 0

VPC

VPC Security

Allow all hosts

Add All Source

AllIPAddresses - Edit

Review Sources

AllIPAddresses - Completed

 

RDC

Console

ConnectionInformation-20160428-0301PM

Connection Information

Rule

  1. 207.140.111.60/32
  2. 0.0.0.0/32

 

Make Publicly Available

CLI

Let us make publicly available via CLI

Code

Syntax

aws rds modify-db-instance --db-instance-identifier [instance-identifier] --publicly-accessible --apply-immediately

Sample

aws rds modify-db-instance --db-instance-identifier adriel --publicly-accessible --apply-immediately

Output

makePubliclyAvailable

 

Console

Access DB Instance Modify Panel

We can modify the DB instance by doing the following

  1. Access RDS Dashboard
  2. Select the DB Instance
  3. Click on the Instance Actions button
  4. From the drop-down menu, select the Modify option

 

Modify

 

Modify DB Instance

ModifyDBInstance

 

 

Review RDS Dashboard – Instance – Connection Information

Same confirmed via RDS Dashboard – Connection Information …

ConnectionInformation-20160428-0403PM

Client

SQL Server Management Studio

Connected….

Connected

 

Summary

We were successfully able to create a new DB Instance.

We accessed the Virtual Private Cloud (VPC) panels to expand the IP Addresses that are allowed access to our DB.

But, unfortunately none of our attempts succeeded.

We thus reverted to making the DB Instance itself publicly available.

 

References

AWS – Official

  1. Creating a SQL Server DB Instance and Connecting to a Database on a SQL Server DB Instance
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.SQLServer.html
  2. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Virtual Private Clouds (VPCs) and Amazon RDS » Scenarios for Accessing a DB Instance in a VPC
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.Scenarios.html#USER_VPC.Scenario4
  3. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » MySQL on Amazon RDS » Modifying a DB Instance Running the MySQL Database Engine
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ModifyInstance.MySQL.html
  4. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Amazon RDS DB Instance Lifecycle » Modifying a DB Instance and Using the Apply Immediately Parameter
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.DBInstance.Modifying.html
  5. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Virtual Private Clouds (VPCs) and Amazon RDS » Working with an Amazon RDS DB Instance in a VPC
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html
  6. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Amazon RDS DB Instance Lifecycle » Renaming a DB Instance
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RenameInstance.html
  7. AWS Documentation » Amazon Virtual Private Cloud » Getting Started Guide » Getting Started with Amazon VPC » Step 2: Create a Security Group
    http://docs.aws.amazon.com/AmazonVPC/latest/GettingStartedGuide/getting-started-create-security-group.html
  8. Authorizing Inbound Traffic for Your Linux Instances
    http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/authorizing-access-to-an-instance.html

 

CLI

  1. Modify DB Instance
    http://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html

 

Network CIDR

  1. Setting CIDR/IP so anyone can access it from any IP?
    http://stackoverflow.com/questions/6365773/setting-cidr-ip-so-anyone-can-access-it-from-any-ip

 

Sample Implementation

  1. MySQL
  2. Apache
  3. MS SQL Server


StackOverflow

  1. What are the default security groups created when I set up AWS EB for the first time?
    http://stackoverflow.com/questions/27829620/what-are-the-default-security-groups-created-when-i-set-up-aws-eb-for-the-first

 

SlideShare.Net

  1. AWS Cloud – Network Security and Access Control in AWS
    http://www.slideshare.net/AmazonWebServices/network-security-and-access-control-in-aws

SQL Server on Amazon RDS – Create Database – File Groups

Background

Quick commentary on creating a database that sits on multiple filegroups on Amazon RDS.

 

Guide

Launch SQL Server Management Studio (SSMS) and connect to your Amazon RDS MS SQL Server.

New Database

General

Initial

Here we specify the database name has DBLAB.

And, the system autofills the Logical names of DBLAB for the File Type of ROW.  And, DBLAB_log for the LOG.

NewDatabase - General

 

Filegroups

Initial

Here is the initial screen for filegroups.

NewDatabase-FileGroups

 

Post

Here is what things look like once we added Indexes and Indexes2 as file groups.

NewDatabase-FileGroups-AddedAdditionalFilegroups

General

Post

We return to the general Tab and added new files and positioning them to the corresponding Filegroup

  1. Logical name of DBLAB_Indexes attached to Indexes Filegroup
  2. Logical name of DBLAB_Indexes2 attached to Indexes2 Filegroup

 

NewDatabase - General-AddedAdditionalFiles

 

Best Practice

Best practice suggest that we should target a separate physical disk, LUN, for each file group.

If we try to seek out an alternate File path, we will get the error stated below.

Image

xp_fixeddrives-error-229

Textual


The execute permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'

Implication

We do not have permission to access xp_fixedrives.  xp_fixedrives lists available storage on attached physical drives.

SQL Script

Btw, here is the SQL Script.


CREATE DATABASE [DBLAB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DBLAB', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB.mdf' , SIZE = 5120KB , FILEGROWTH = 10%), 
 FILEGROUP [Indexes] 
( NAME = N'DBLAB_Indexes', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_Indexes.ndf' , SIZE = 5120KB , FILEGROWTH = 204800KB ), 
 FILEGROUP [Indexes2] 
( NAME = N'DBLAB_Indexes2', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_Indexes2.ndf' , SIZE = 5120KB , FILEGROWTH = 204800KB )
 LOG ON 
( NAME = N'DBLAB_log', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO


Summary

One has to rely on the underlying storage configuration when using a provisioned system such as RDS.

Typical engineering thoughts such as separating data and logs are not available.

Same with attempts to disambiguate data and index I/O.

SQL Server – Data Sampling – TABLESAMPLE

Background

A quick follow-up to a couple of blog posts on pruning.  We have a requirement to prune about a dozen log tables.  The lone decisive factor is the number of months elapsed.

After experiencing deadlocks and the like took to the .Net and re-discovered that it might be better to use the Clustering ID rather that than the date.

 

Previous Blogs

  1. Transact SQL – Prune Data based on Date
  2. Transact SQL – Prune Data based on Date – Material Effect of Indexes

 

Why might it be better to use the ID?

There are a couple of percepts behind why it might be better to use the ID…

  1. Halloween Effect
  2. Deadlocks
    • When different column combinations are use to search for data than are used per the actual deletion, the latter locks may already have been granted for another session

SQL Statements

Determine ID High Bar Value

Back to our current problem, we need to identify the high bar


select max([id])
from   [table]
where  [date-column] < dateadd('month', [number-of-months] * -1, getdate())

Actual Deletion

And, here is a snippet of the batched delete command.


declare @NumberofRecordsInEachBatch bigint
declare @iNumberofRecordsAffected   bgint

set @NumberofRecordsInEachBatch = 1000
set @iNumberofRecordsAffected = -1


while (@iNumberofRecordsAffected != 0)
begin


    delete top (@NumberofRecordsInEachBatch) tblL

    from   dbo.[MailingListRequestsLog] tblL

    where  tblL.[entryID] < @idPKCutoff 

    set @iNumberofRecordsAffected = @@ROWCOUNT

end

Index Requirement

We can see quite quickly that we will need an Index on the date column.

Index Costs

As anything else, if a corresponding index does not exist, we have to measure the cost of creating one.

Index Size

Using sp_helpindexinfo, here are our current indexes, their size and number of rows

sp_helpindexInfo

Size is 280 GB and 204 million records.

Index Script

INDX_DBA_SentDt

Tried creating an index on date column…


IF NOT EXISTS 
(
	SELECT * 
	FROM   sys.indexes 
	WHERE  object_id = OBJECT_ID(N'[dbo].[MailingListRequestsLog]') 
	AND    name = N'INDX_DBA_SentDt'
)
begin

	CREATE NONCLUSTERED INDEX [INDX_DBA_SentDt] 
	ON [dbo].[MailingListRequestsLog]
	(
		[SentDt] ASC
	)
	INCLUDE 
	(
		[entryID]
	) 
	WITH 
	(
		FILLFACTOR = 100
	) 
	ON [Indexes2]

end


It is always nice to be able to confidently state that FILL_FACTOR is 100.

Not so sure whether the Primary Key( entryID ) should be part of our index key columns, an add on using INCLUDE, or not needed at all.

 

Create Index

Tried creating the index, but aborted due to glaring session blocks, etc.

Remediation Choices

Create Index ONLINE

If we had an Enterprise Edition of SQL Server, I can try


ONLINE = { ON | OFF }

But, our edition is standard.

 

Sample Table

One hopefully, creative choice is to create a table that sample’s our existing data.

Requirements

  1. It has to be big enough to give us good correlation between the date and the ID column.
  2. Its creation should minimally affect ongoing access on the primary table
  3. It’s size should be manageable

 

Script

Create Tracking Table – [dbo].[MailingListRequestsLogTracking]


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

set noexec off;
go

if object_id('[dbo].[MailingListRequestsLogTracking]') is not null
begin

	set noexec on;

end
go

CREATE TABLE [dbo].[MailingListRequestsLogTracking]
(

	  --[entryID] [int] IDENTITY(1,1) 
	  [entryID] [bigint]

	, [sentDt] [datetime] NOT NULL 
			CONSTRAINT [DF_MailingListRequestsLogTracking_sentDt]  DEFAULT (getdate())

	, CONSTRAINT [PK_MailingListRequestsLogTracking] PRIMARY KEY CLUSTERED 
	(
		[entryID] ASC
	)

) ON [PRIMARY]

set noexec off;
go

if not exists
	(
		select tblSI.*
		from   sys.indexes tblSI
		where  tblSI.object_id = object_id('[dbo].[MailingListRequestsLogTracking]') 
		and    tblSI.name = 'INDX_SentDt_EntryID'
	)
begin

	create index [INDX_SentDt_EntryID]
	on [dbo].[MailingListRequestsLogTracking]
	(
		  [sentDt]
		, [entryID]
	)

end
go

Diagram

Here is what our existing table, dbo.MailingListRequestLog, looks like.

And, on the right side is our tracking table  dbo.MailingListRequestLogTracking.

MailingListRequestsLog

 

Population Script




SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS 
(
	SELECT * 
	FROM   sys.schemas
	WHERE  schema_id = schema_ID('dbmgmt') 
)
BEGIN

	EXEC dbo.sp_executesql @statement = N'CREATE SCHEMA [dbmgmt] authorization [dbo]' 

END
GO


IF NOT EXISTS 
(
	SELECT * 
	FROM   sys.objects 
	WHERE  object_id = OBJECT_ID(N'[dbmgmt].[usp_MailingListRequestsLogTracking_Populate]') 
	AND    type in (N'P', N'PC')
)
BEGIN

	EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbmgmt].[usp_MailingListRequestsLogTracking_Populate] AS' 

END
GO


ALTER procedure [dbmgmt].[usp_MailingListRequestsLogTracking_Populate]
as

begin

	set transaction isolation level read uncommitted;
	set nocount on;
	set XACT_ABORT on;

	declare @CHAR_TAB varchar(30)

	declare @iNumberofRecordsToSample bigint
	declare @iNumberofRecordsAffected bigint
	declare @strLog	varchar(600)

	set @CHAR_TAB = char(9)

	set @iNumberofRecordsToSample = 1000

	truncate table [dbo].[MailingListRequestsLogTracking];

	; with cteTop
	(
		  [entryID]
		, [sentDt]

	)
	as
	(
		select top 1

				  tblMLRLT.[entryID]

				, tblMLRLT.[sentDt]

		from   [dbo].[MailingListRequestsLog] tblMLRLT 

		order by [entryID] asc
	)
	, cteBottom
	(
		  [entryID]
		, [sentDt]

	)
	as
	(
		select top 1

				  tblMLRLT.[entryID]

				, tblMLRLT.[sentDt]

		from   [dbo].[MailingListRequestsLog] tblMLRLT 

		order by [entryID] desc
	)

	, cteSample
	(
		  [entryID]
		, [sentDt]

	)
	as
	(
		select 

				  tblMLRLT.[entryID]

				, tblMLRLT.[sentDt]

		from   [dbo].[MailingListRequestsLog] tblMLRLT

					TABLESAMPLE (  1000000 ROWS)

					with ( NOLOCK )

	)

	insert into [dbo].[MailingListRequestsLogTracking]
	(
		  [entryID]

		, [sentDt]
	)

	select 

		  [entryID]
		, [sentDt]

	from   cteTop

	union

	select 

			 [entryID]

			, [sentDt]

	from   cteSample

	UNION

	select 
		  [entryID]
		, [sentDt]

	from   cteBottom


	set @iNumberofRecordsAffected = @@rowcount

	set @strLog=
					@CHAR_TAB
					+ cast(@iNumberofRecordsAffected as varchar(10))
					+ ' record(s) affected'

	print @strLog

end
go


 

Quick Explanation
  1. Common Table Expression
    • cteTop
      • In cteTop, we get the earliest record
    • cteBottom
      • In cteBottom, we get the latest record
    • cteSample
      • In cteSample, we invoke SQL Server’s TABLESAMPLE clause to sample a million record

 

Pros & Cons

Cons

  1. TableSample is expensive
  2. Correlative with your sample size, you still get to trash your data cache

Statistics

Session

  1. On our DR Server which effectively does not have any data cached
    • It took 5.46 minutes to get 1 million records
Statistics IO

StatisticsIO

Explanation
  1. Though we asked for a million records, we only got back 999,330 records
    • Please plan ahead and pass in more records than you actually need

 

Index Size

Let us use sp_helpindexinfo to get index stats.


exec [dbo].[sp_helpindexInfo]
		  'dbo'
		, 'MailingListRequestsLog'

exec [dbo].[sp_helpindexInfo]
		  'dbo'
		, 'MailingListRequestsLogTracking'

Output
Graphical

IndexSize

Tabulated

 

Table Index Number of Records Size (MB)
dbo.MailingListRequestsLog
PK_MailingListRequestsLog  204,848,859  280,135
 MailingListRequestsLogTracking
PK_MailingListRequestsLogTracking 999330  24
INDX_SentDt_EntryID 999330 18

 

Summary

With a bit of reasoned thoughts, vexing problems are a bit more approachable in cases where sampled data is sufficient.

Amazon – RDS – Command Line Interface ( CLI ) – Returning empty resultset

Background

Just blew a couple of hours trying to determine why a couple of RDS/CLI Commands are coming back empty.

 

Commands

Here are the commands I tried out

describe-db-instances

Command


aws rds describe-db-instances

Output


{
"DBInstances": []
}

 

describe-db-parameter-groups

Command


aws rds describe-db-parameter-groups

 

Output


{
 "DBParameterGroups": []
}

Remediation

Traced the error back to the fact that I had set a wrong region.

Workarounds are to override the default per each command utterance, or go back and reset the default.

Specify Region at Command Level

describe-db-instance

Command


aws rds describe-db-instances  --region us-west-2

Output

describe-db-instances-20160426

describe-db-parameter-groups

Command


aws rds describe-db-parameter-groups --region us-west-2

Output

describe-db-parameter-groups-20160426

 

Reset default region

Review Configuration

Command


aws configure

Output

Wrong default – us-west-1

aws configure - 20160426 - 0658PM

Reset Configuration

  1. Connect to Console and get default

 

Connect to Console and get default

dbinstances-RDS-ScreenShot

 

Reset default via “aws configure”

Here we change the default region from us-west-1 to us-west-2


aws configure

   default region name [us-west-1] : us-west-2

aws configure - 20160426 - 0708PM

 

Confirm

Issue query without specifying default

Code


aws rds describe-db-instances

Output

DescribeDBInstancePostSettingDefault

Redwoods


whiteroseswithabitofgreen-cropped
Came home yesterday afternoon, went to lay down
Outside of spending time with my brother to get something to eat
Spent the rest of the evening in bed
Woke at 2
But, couldn’t stand to get out of bed


What the redwoods of California will do for a soul bruised up
What a timely getaway will do
Driving the night and needing a full beam
Made my mind so small

 

Listening to you speak your Heart out
About all the things I said and shouldn’t have
Laying down in a so so Hotel
Pulling you so close
Enough for only you and I to hear

 


I say I am sorry
Asking for forgiveness
And, how I can wait till the morning
to see if you will give it

 


JB: The meaning of forgiveness
JB: People make mistakes

Went to Church, It is Sunday Morning
The pastor made the Altar Call
Something about the Lord
Is ready to forgive


Forgiveness came a day early for me
I didn’t have to hear it
I just had to feel you yield your Soul & Spirit

 


JB: The meaning of forgiveness
JB: People make mistakes, doesn’t mean you have to give in
JB: Life is worth living again

Took a long drive in the California Redwoods
Wished we made it to Oregon
But, came home
And, held it all in
Wish I could stay here forever

 

Listening to ….

Justin Bieber – Life Is Worth Living (PURPOSE : The Movement)
Video