The effect of RAID cache backup battery failure

RAID write times are improved by writing to a cache.   This is a quick operation, the calling program can get on with its business, and the RAID controller can solidify the cached changes to disk(s) in its own time.  The cache power supply has a backup battery.

When the cache backup battery condition measures below a safe limit it is marked as FAILED! (which raises alerts) and the RAID controller stops using the cache and writes only direct to disk.

Performance Effect?

How much does that affect performance?   Those in the know (some network engineers) suggested it might add 20ms to write times.  They weren’t far wrong.  The Disk avg write time metric on the Redgate monitor showed just under 30ms increase

It had no effect on average disk read time, as would be exected:

 

 

Maintenance – Record Deletion

A simple framework for implementing a time-framed record deletion policy: deleting records that are older than a certain threshold.

Consists of a logging table and a stored procedure which deletes the records.   The procedure deletes rows in batches and provides for an overall maximum to be specified for a single execution to limit impact on resources when there is a huge set of records to delete.

Logging Table

CREATE TABLE [dbo].[RecordDeletions]
(
	[RecordDeletionId] INT NOT NULL IDENTITY, 
    [TableName] VARCHAR(50) NULL, 
	[DeletionThresholdDate] smalldatetime NULL,
    [OperationStatus] VARCHAR(50) NOT NULL CONSTRAINT DF_RecordDeletions_OperationStatus DEFAULT 'Running', 
    [StartTime] DATETIME NOT NULL CONSTRAINT DF_RecordDeletions_StartTime DEFAULT GetDate(), 
    [EndTime] DATETIME NULL, 
    [NbrRecordsDeleted] INT NULL , 
    [DurationSeconds] as DATEDIFF(SECOND, StartTime, EndTime), 
    CONSTRAINT [PK_RecordDeletions] PRIMARY KEY ([RecordDeletionId]),
	CONSTRAINT [CK_RecordDeletions_OperationStatus_Valid_Values] 
        CHECK (OperationStatus = 'Running' OR OperationStatus = 'Success' OR OperationStatus = 'Failure')
)

GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Keeps a log of records that have been deleted from tables for housekeeping',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'RecordDeletions',
    @level2type = NULL,
    @level2name = NULL
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Time that this operation started',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'RecordDeletions',
    @level2type = N'COLUMN',
    @level2name = N'StartTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Time that this operation ended',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'RecordDeletions',
    @level2type = N'COLUMN',
    @level2name = N'EndTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Number of records deleted',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'RecordDeletions',
    @level2type = N'COLUMN',
    @level2name = N'NbrRecordsDeleted'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Name of table',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'RecordDeletions',
    @level2type = N'COLUMN',
    @level2name = N'TableName'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Status of operation:  Running, Success, Failure',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'RecordDeletions',
    @level2type = N'COLUMN',
    @level2name = N'OperationStatus'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'(Computed column) Seconds duration of completed events.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'RecordDeletions',
    @level2type = N'COLUMN',
    @level2name = N'DurationSeconds'
GO

EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Rows older than the DeletionThresholdDate are targetted for deletion in this event.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'RecordDeletions',
    @level2type = N'COLUMN',
    @level2name = N'DeletionThresholdDate'

Record Deletion Procedure

This procedure is fairly basic and requires duplication and customisation for each table (or cluster of related tables).   Search and replace TARGET_TABLE_NAME – but the appropriate Primary Key and Date columns will also have to be edited in the “Deletion Candidates Selection Block”.

CREATE PROCEDURE dbo.DeleteOldRows_TARGET_TABLE_NAME

/*
	Delete rows older than the retention period 
	
	@DeleteBatchSize : number of records deleted in each loop.
	@OverallMaxRowsPerExecution : absolute max number of rows that will be deleted 
								in a single execution of the procedure.

*/
AS
SET NOCOUNT ON;
SET XACT_ABORT ON
BEGIN TRY
	DECLARE @DeleteBatchSize int = 5000
	DECLARE @OverallMaxRowsPerExecution int = 400000

	DECLARE @DebugInfo varchar(2000)
	DECLARE @MaxRowNumber int = 0
	DECLARE @RangeStart int
	DECLARE @RangeEnd int
	DECLARE @RecordDeletionId int
	DECLARE @NbrRecordsDeleted int = 0
	DECLARE @OperationStatus varchar(50) = 'Failure'


	CREATE TABLE #RecordsToDelete
		(RowNumber int not null PRIMARY KEY IDENTITY,
		 RecordId  int not null
		 )

	-- Insert logging row, calculate time threshold.
	INSERT dbo.RecordDeletions 
		(TableName,
		 DeletionThresholdDate)
		VALUES
		('TARGET_TABLE_NAME',
		  DATEADD(MONTH, (-60), CAST(GETDATE() AS date);

	SET @RecordDeletionId = SCOPE_IDENTITY()

	-- Deletion Candidates Selection Block
	INSERT INTO #RecordsToDelete (RecordId)
		SELECT TOP (@OverallMaxRowsPerExecution)
			ID
		FROM
			dbo.TARGET_TABLE_NAME L
			INNER JOIN dbo.RecordDeletions RD
			ON L.RowCreatedDate <= RD.DeletionThresholdDate
		WHERE
			RD.RecordDeletionId = @RecordDeletionId

	SELECT
		@RangeStart = MIN(RowNumber),
		@MaxRowNumber = MAX(RowNumber)
	FROM
		#RecordsToDelete


	WHILE (@RangeStart < @MaxRowNumber)
	BEGIN
		SET @RangeEnd = (@RangeStart + @DeleteBatchSize -1)

		DELETE TargetTable
		FROM 
			dbo.TARGET_TABLE_NAME TargetTable
			INNER JOIN #RecordsToDelete R ON TargetTable.ID = R.RecordId
		WHERE	
			R.RowNumber BETWEEN @RangeStart and @RangeEnd
	
		SET @NbrRecordsDeleted += @@ROWCOUNT
		SET @RangeStart += @DeleteBatchSize

		WAITFOR DELAY '000:00:00.500' -- pause for 500ms to allow other processes to gain locks.
	END

	SET @OperationStatus = 'Success'


END TRY
BEGIN CATCH
	EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo
	SET @OperationStatus = 'Failure';
END CATCH	

BEGIN TRY
	SET @DebugInfo = 'Logging and Cleanup section'
	UPDATE
		dbo.RecordDeletions
	SET
		OperationStatus = @OperationStatus,
		EndTime = GETDATE(),
		NbrRecordsDeleted = @NbrRecordsDeleted
	WHERE
		RecordDeletionId = @RecordDeletionId

	DROP TABLE #RecordsToDelete
END TRY
BEGIN CATCH
	EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo
	RETURN 5555
END CATCH

Uses the  Error Handler for T-SQL.

HTTP Access to Analysis Services

In retrospect this was a simple task: the steps set out in the invaluable video Configuring HTTP for SSAS  all work.  However, in the middle of a long process trying to get it to work the first time it seems anything but simple.  So here are some observations to help reassure you that it really is straightforward, even after a full day battling without success.

Environment

These observations are from configuring HTTP SSAS access in this environment:

  • Windows Server 2016 Datacentre (EC2 instance)
  • SQL Server Analysis Services 2017 Evaluation (developer) edition
  • IIS 10

both SSAS and IIS running on the same server.

Install Cumulative Update 9

The most important lesson is to install SQL Server CU9 or later.   The fault in msmdpump.dll is indicated in this MS article: FIX: Access to SSAS by using HTTP fails in SQL Server The article is dated 18 July 2018.

Here are some of the responses you will see if you are using the pre-CU9 version of the file – assuming the configuration is otherwise correct.

SSMS

The response is immediate.

Cannot connect to http://...url/.

File system error: The following error occurred while writing to the file 'MSOLAP ISAPI 
Extension: \\?\C:\inetpub\OLAP\msmdpump.dll': Access is denied. .
Internal error: An unexpected exception occurred. (Unknown)

Excel

Response is immediate.

XML parsing failed at line 1, column 1: Incorrect document syntax.
.
File system error: The following error occurred while reading from the file 'Compressed stream'.

Browser

The response to a browser http request is the same as from the correct DLL, so this cannot be used to determine whether the msmdpump.dll version is correct.

Successful Installation

Connecting to the HTTP address on the same server as IIS returns this:

 

 

 

 

This is the right response.  It’s a 501 error code, so this won’t be seen from browsers on remote hosts.

Other Errors

Excel will report an error message from the host quickly although some are replaced by a generic “check typing of username” friendly message from Excel.  SSMS might allow server-level nodes and some cube structure to be viewed in the explorer.  If a cube is visible, attempting to browse it can provide more error information (although hidden by default).

  • Either the user, ‘NT AUTHORITY\IUSR’, does not have access to the ‘<Cube Name>’ database, or the database does not exist.    –  The solution is to make IUSR a member of the Server Administrators of the SSAS instance.  IUSR does not (and should not, for security) need to be a member of any Role within the cube.

Other Observations

SSL Offloading

The HTTP endpoint handles SSL encoding on load balancer without problem.  No additional configuration is required: IIS is configured as an unsecured http site.

 

Resources

Script to Configure SSRS 2017 Reporting Services

This is an attempt to create a script that will configure an instance of SSRS that has been left at the “installed but not configured” stage by the installer.   The script is required to run during the spin-up of a virtual SQL Server machine (e.g. AWS) to result in a fully functioning SSRS site with no further configuration to be done.

(Work in progress)

Versions

  • SSRS Native Mode 2017 (v14) Standard edition
  • Windows Server 2016 Standard

Actions to be Scripted

  • Reserve /Reports and /ReportServer URLs  [done]
  • Greate SSRS databases and assign rights [done]
  • SMTP Settings
  • Specify Execution Account
  • Create and Backup Encryption Keys
  • Set File Share Account for Subscriptions

Resources

The Script

Main changes to update from SSRS v12 to v14:

  • WMI object namespace is now “root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin”   (v12 was “…\RS_MSSQLSERVER\v12\Admin”) – reflecting the fact that SSRS as an application is now separate to SQL Server.
  • The default service account is now “NT Service\SQL Reporting Services” (instead of “NT Service\ReportServer” for v12).
  • The Report Manager (/ReportServer) web application name is “ReportServerWebApp” (formerly “ReportManager”).  This means there are two very similarly, but logically, named applications:
    • ReportServerWebService  the service that emits the actual reports – /ReportServer
    • ReportServerWebApp (formerly ReportManager) the full SSRS user and admin interface – /Reports
## This script configures a Native mode report server without HTTPS
## SSRS Version :  2017 - v14
## Script source: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sqlclassic/virtual-machines-windows-classic-ps-sql-report
## Amended by Andrew Ing for v14
## Script Version 25-11-2018
 
 
$ErrorActionPreference = "Stop"
 
$server = $env:COMPUTERNAME
$HTTPport = 80 # change the value if you used a different port for the private HTTP endpoint when the VM was created.
 
## Set PowerShell execution policy to be able to run scripts
Set-ExecutionPolicy -Scope CurrentUser RemoteSigned -Force
 
## Utility method for verifying an operation's result
function CheckResult
{
     param($wmi_result, $actionname)
     if ($wmi_result.HRESULT -ne 0) {
         write-error "$actionname failed. Error from WMI: $($wmi_result.Error)"
     }
}
 
$starttime=Get-Date
write-host -foregroundcolor DarkGray $starttime StartTime
 
## ReportServer Database name - this can be changed if needed
$dbName='ReportServer'
 
## Register for MSReportServer_ConfigurationSetting
$RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin"
 
## Report Server Configuration Steps

## Setting the web service URL ##
write-host -foregroundcolor green "Setting the web service URL"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## SetVirtualDirectory for ReportServer site
     write-host 'Calling SetVirtualDirectory'
     $r = $RSObject.SetVirtualDirectory('ReportServerWebService','ReportServer',1033)
     CheckResult $r "SetVirtualDirectory for ReportServer"
 
## ReserveURL for ReportServerWebService - port $HTTPport (for local usage)
     write-host "Calling ReserveURL port $HTTPport"
     $r = $RSObject.ReserveURL('ReportServerWebService',"http://+:$HTTPport",1033)
     CheckResult $r "ReserveURL for ReportServer port $HTTPport" 
 
## Setting the Database ##
write-host -foregroundcolor green "Setting the Database"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## GenerateDatabaseScript - for creating the database
     write-host "Calling GenerateDatabaseCreationScript for database $dbName"
     $r = $RSObject.GenerateDatabaseCreationScript($dbName,1033,$false)
     CheckResult $r "GenerateDatabaseCreationScript"
     $script = $r.Script
 
## Execute sql script to create the database
     write-host 'Executing Database Creation Script'
     $savedcvd = Get-Location
     Import-Module SQLPS              ## this automatically changes to sqlserver provider
     Invoke-SqlCmd -Query $script
     Set-Location $savedcvd
 
## GenerateGrantRightsScript 
     $DBUser = "NT SERVICE\SQLServerReportingServices"
     write-host "Calling GenerateDatabaseRightsScript with user $DBUser"
     $r = $RSObject.GenerateDatabaseRightsScript($DBUser,$dbName,$false,$true)
     CheckResult $r "GenerateDatabaseRightsScript"
     $script = $r.Script
 
## Execute grant rights script
     write-host 'Executing Database Rights Script'
     $savedcvd = Get-Location
     cd sqlserver:\
     Invoke-SqlCmd -Query $script
     Set-Location $savedcvd
 
## SetDBConnection - uses Windows Service (type 2), username is ignored
     write-host "Calling SetDatabaseConnection server $server, DB $dbName"
     $r = $RSObject.SetDatabaseConnection($server,$dbName,2,'','')
     CheckResult $r "SetDatabaseConnection"  

## Setting the Report Manager URL ##
 
write-host -foregroundcolor green "Setting the Report Manager URL"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## SetVirtualDirectory for Reports (Report Manager) site
     write-host 'Calling SetVirtualDirectory'
     $r = $RSObject.SetVirtualDirectory('ReportServerWebApp','Reports',1033)
     CheckResult $r "SetVirtualDirectory"
 
## ReserveURL for ReportManager  - port $HTTPport
     write-host "Calling ReserveURL for ReportServerWebApp, port $HTTPport"
     $r = $RSObject.ReserveURL('ReportServerWebApp',"http://+:$HTTPport",1033)
     CheckResult $r "ReserveURL for ReportManager port $HTTPport"
 
write-host -foregroundcolor green "Open Firewall port for $HTTPport"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## Open Firewall port for $HTTPport
     New-NetFirewallRule -DisplayName "Report Server (TCP on port $HTTPport)" -Direction Inbound –Protocol TCP –LocalPort $HTTPport
     write-host "Added rule Report Server (TCP on port $HTTPport) in Windows Firewall"
 
## Restart SSRS service ##
 write-host -foregroundcolor green "Restart SQLServerReportingServices"
 write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
 $time=Get-Date
 write-host -foregroundcolor DarkGray $time

Restart-Service SQLServerReportingServices

write-host 'Operations completed, Report Server is ready'
write-host -foregroundcolor DarkGray $starttime StartTime
$time=Get-Date
write-host -foregroundcolor DarkGray $time

Restoring the Unconfigured state

During iterative testing it’s necessary to restore the SSRS instance to the unconfigured state.   This is not possible from the SSRS Configuration Tool.  There are some WMI methods to remove URLs, which I haven’t tried yet.  Here are some manual methods.

  • Remove URL Reservations .
    • Open C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\rsreportserver.config  in notepad++, find <URLReservations>  and remove all <URL> nodes  (there will be at least two once configured: for the ReportServerWebService and ReportServerWebApp applications.
    • Run commands –  assuming default names (Use netsh http show urlacl to show all current reservations):
      • netsh http delete urlacl URL=http://+:80/Reports
      • netsh http delete urlacl URL=http://+:80/ReportServer
    • Restart SQLServerReportingServices service.
    • Alternatively use this script, which calls the WMI RemoveURL methods:
      ## UN-CONFIGURE a native instance of SQL Server Reporting Services.
      ##
      ## This is intended for test iterations where SSRS needs to be returned to its freshly-installed state.
      ## SSRS Version :  2017 - v14
      ## Script Version 25-11-2018
      
      
       $ErrorActionPreference = "Stop"
      
       $server = $env:COMPUTERNAME
       $HTTPport = 80 # change to match what was configured.
      
       ## Set PowerShell execution policy to be able to run scripts
       Set-ExecutionPolicy -Scope CurrentUser RemoteSigned -Force
      
       ## Utility method for verifying an operation's result
       function CheckResult
       {
           param($wmi_result, $actionname)
           if ($wmi_result.HRESULT -ne 0) {
               write-error "$actionname failed. Error from WMI: $($wmi_result.Error)"
           }
       }
      
       $starttime=Get-Date
       write-host -foregroundcolor DarkGray $starttime StartTime
      
       ## ReportServer Database name - this can be changed if needed
       $dbName='ReportServer'
      
       ## Register for MSReportServer_ConfigurationSetting
       $RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin"
      
       
       ## Unset Web Service (/Reports) URL ##
       write-host -foregroundcolor green "Unset Web Service (/Reports) URL"
       write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
       $time=Get-Date
       write-host -foregroundcolor DarkGray $time
      
      
           write-host "Calling RemoveURL port $HTTPport"
           $r = $RSObject.RemoveURL('ReportServerWebService',"http://+:$HTTPport", 1033)
           CheckResult $r "RemoveURL for ReportServer port $HTTPport" 
      
       ## Unset Web App (/Reportserver) URL ##
       write-host -foregroundcolor green "Unset Web App (/Reportserver) URL"
       write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
      
      
          write-host "Calling RemoveURL for ReportServerWebApp, port $HTTPport"
          $r = $RSObject.RemoveURL('ReportServerWebApp',"http://+:$HTTPport",1033)
          CheckResult $r "RemoveURL for ReportManager port $HTTPport"
      
      
       
       ## Restart SSRS service ##
       write-host -foregroundcolor green "Restart SQLServerReportingServices"
       write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
       $time=Get-Date
       write-host -foregroundcolor DarkGray $time
      
       Restart-Service SQLServerReportingServices
      
       $time=Get-Date
       write-host -foregroundcolor DarkGray $time

       

  • Drop the SSRS databases
    ALTER DATABASE ReportServer SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE ReportServerTempDB SET SINGLE_USER WITH ROLLBACK IMMEDIAT
    USE master
    DROP DATABASE ReportServer
    DROP DATABASE ReportServerTempDB

     

Selected Exception Messages

…you might encounter during testing

  • Error from WMI: The Url has already been set for this application. Remove existing URL reservations (see above) first.  
  • Error from WMI: The Url has already been reserved.   (as above)
  • GenerateDatabaseRightsScript failed. Error from WMI: [blank!]    WMI returned no error information, but at this point the script has already created the databases so is able to execute SQL commands.  This script is assigning rights, therefore it is likely  that the database user specfied in the script ($DBUser)  is not valid – which turned out to be the case in this instance.
  • Error from WMI: A virtual directory must first be created before performing this operation.   This occurs if the whole <Application> node is deleted from rsreportserver.config. Only delete the <URL> nodes.  Restore the config file and restart SQLServerReportingServices.

Further Reading

  • Powershell ReportingServicesTools (repository: https://github.com/Microsoft/ReportingServicesTools) contains commands for configuring as well as administering SSRS.  See Claudio Silva’s SQL Bits presentation: https://sqlbits.com/Sessions/Event17/Administrating_SSRS_without_boring_web_based_clicks.

Colours

Colour Choices for Graphs and Tables

Heatmap Colouring for SSRS

Sample colours for a range of 10 levels with mid-point roughly yellow.

Code for BackgroundColor property of cell:

= IIF(Fields!FieldName.Value >= .98, "#63be7b", 
  IIF(Fields!FieldName.Value >= .95, "#86c87d", 
  IIF(Fields!FieldName.Value >= .92, "#b9d780", 
  IIF(Fields!FieldName.Value >= .88, "#e1e383", 
  IIF(Fields!FieldName.Value >= .84, "#feea83", 
  IIF(Fields!FieldName.Value >= .78, "#fedf81", 
  IIF(Fields!FieldName.Value >= .72, "#fdd780", 
  IIF(Fields!FieldName.Value >= .68, "#fba977", 
  IIF(Fields!FieldName.Value >= .65, "#fa9573", 
									 "#f8696b" )
))))))))

 

Standard Colours

Sample Code Note
 123 #DDEBF7 Excel Pivot Header Blue
 123 #FFF2CC Excel Light Yellow Background

 

SSISDB getting large – failure of Retention Window Cleanup

The SSIS Catalog Rentention Period (days) defaults to 365.   The SSIS database can quickly become very large (as a function of SSIS activity and logging levels).

A stored procedure SSISDB.internal.cleanup_server_retention_window is executed by a job SSIS Server Maintenance Job on a nightly schedule at 00:00:00.

(All defaults observed in 2012, v.11.0.5058.0)

cleanup_server_retention_window

This stored procedure deletes rows from internal.operations table that are older than the retention period.  One row in internal.operations represents a package (?) invocation – this table won’t grow too large,  but related detail tables can;  internal.event_messages, internal.event_message_context, internal.operation_messages, internal.executable_statistics being the largest.

The cleanup stored procedure deletes rows from internal.operations tables.  Cascading foreign key constraints delete rows from other tables – some indirectly via internal.executions.  This is operational part of the procedure:

 WHILE (@rows_affected = @delete_batch_size)
   BEGIN
     DELETE TOP (@delete_batch_size)
       FROM [internal].[operations] 
       WHERE ( [end_time] <= @temp_date
       OR ([end_time] IS NULL AND [status] = 1 AND [created_time] <= @temp_date ))
     SET @rows_affected = @@ROWCOUNT
  END

Procedure continually fails to complete

The default for @delete_batch_size is 1,000 – and herein lies the problem.  The deletion of a single internal.operations row will cascade to many thousands of rows.  But internal.operations itself is relatively low cardinality.  Assembling such a large deletion set is a time consuming, log-expanding and blocking operation which can fail – and fail continually thereafter.  Although the deletion is sensibly in a loop, 1000 is a large proportion of  internal.operations  rows. As can be seen from the 16GB example above there are only 2.8K rows in the table to start with, so the loop would barely exceed a couple of iterations.    Naturally all this depends on the activity and retention period of the SSIS instance.  But 1,000 seems a lot in any case.

Some posts state that  @delete_batch_size is 10, though not in the versions I have checked.   If the procedure continually fails the solution is to run a copy the code with a smaller batch size to clear the backlog.   I am tempted to reduce the setting in the stored procedure itself – should a backlog of deletions accrue again for whatever reason the deletions will again fail.

The following chart shows the table sizes once the retention period has been reached – in this case, 2.8K rows in internal.operations were reduced to 827.  The projected results on the other large tables is significant.

View Change Tracking Settings

Statements to reveal Change Tracking settings.  This query does not require VIEW DATABASE STATE permission, which is required to display the database properties page in SSMS.

select 
	[database_name] = DB_NAME(database_id) 
	,*
from  sys.change_tracking_databases

 

Populating Date Dimension with Fiscal Periods

A standard method for populating a date table with fiscal periods will always come to grief over exceptions.  This post includes a procedure that should be customisable enough to cater for most variations on fiscal periods – provided all periods contain a whole number of weeks.

Fiscal Periods

… do vary.

  • A 4-4-5 week pattern is standard… except when adjustments are made for busy months.
  • Normally a quarter contains three months… except when a company is adjusting its financial year end.

The procedure detailed below takes a few parameters which specify the fiscal period schema:

  • FirstDayOfFiscalYear  : the day it all starts, which also determines the first-day-of-week.
  • WeeksPerMonthPattern : a csv list stating the number of weeks in each month.  e.g.  4,4,5,4,4,5,4,4,5,4,5,4.    The procedure defaults to 4-4-5 throughout the year.
  • MonthsPerQuarterPattern : a csv list stating the number of months per quarter.  defaults to 3,3,3,3 so only needs overriding in those exceptional circumstances.

There is some validation to ensure that the numbers of months, quarters and weeks align.  There’s a “checksum” NbrOfWeeks parameter which defaults to 52; set a value if 53 weeks applies – or for fiscal years with other numbers of weeks.

Columns to Update

A date table might have any number of specialised fiscal columns to update – week and month numbers, start and end date keys, overall period counts.  Therefore the procedure provides a simple but flexible way of specifying an update clause using placeholders for values to be inserted.

  • TableToUpdate : name of table
  • DateKeyColumn : The Date-key column of the TableToUpdate. Column must be integer conversion of date format 112 (yyyymmdd).
  • ColumnUpdatePattern :  The contents of a SET clause as it would appear in a an UPDATE statement, with placeholders.  E.g.  “FiscalMonthNumber={m}, FiscalQuarterNumber={q},… FiscalMonthStart={mst},…”. Available placeholders:
    • {year} : the value of @FiscalYearName
    • {d}, {w}, {m}, {q}, {s} : day, week, month, quarter and semester numbers.
    • {wst}, {mst}, {qst}, {sst} : week, month, quarter, semester start date key
    • {wend}, {mend}, {qend}, {send} : week, month, quarter, semester end date key

Procedure ai_GenerateFiscalCalendar

This procedure generates table update statements for a single financial year.

Fiscal Calendar Update Generator.sql

Example Call

This call uses a specialised week-per-month pattern.

DECLARE @ColumnUpdatePattern varchar(1000) = 'Fiscal_year={year}, Fiscal_quarter={q}, ' +
    'Fiscal_period={m}, Fiscal_Week={w}, ' +
	'Fiscal_week_overall_nbr=(1161 + {w}), Fiscal_period_overall_nbr=(1037 + {m}), ' +
	'[quarter_Fiscal_of_year_end_date_key] = {qend}';
EXEC ai_GenerateFiscalCalendar    
	@FirstDayOfFiscalYear='2017-04-30',
	@FiscalYearName = '2017',
	@TableToUpdate = 'dbo.DimDate',
	@DateKeyColumn = 'date_key',
	@ColumnUpdatePattern = @ColumnUpdatePattern,
	@WeeksPerMonthPattern = '4,4,5,4,4,5,4,5,4,4,4,5';

A summary is output to the messages tab so that the periods can be checked.

FISCAL YEAR: 2017
START DATE: 2017-04-30 Sunday
TOTAL DAYS: 364
TOTAL WEEKS: 52
MONTHS:
 1  2017-04-30 Sun  28 days  4 weeks  Qtr: 1  Sem: 1
 2  2017-05-28 Sun  28 days  4 weeks  Qtr: 1  Sem: 1
 3  2017-06-25 Sun  35 days  5 weeks  Qtr: 1  Sem: 1
 4  2017-07-30 Sun  28 days  4 weeks  Qtr: 2  Sem: 1
 5  2017-08-27 Sun  28 days  4 weeks  Qtr: 2  Sem: 1
 6  2017-09-24 Sun  35 days  5 weeks  Qtr: 2  Sem: 1
 7  2017-10-29 Sun  28 days  4 weeks  Qtr: 3  Sem: 2
 8  2017-11-26 Sun  35 days  5 weeks  Qtr: 3  Sem: 2
 9  2017-12-31 Sun  28 days  4 weeks  Qtr: 3  Sem: 2
10  2018-01-28 Sun  28 days  4 weeks  Qtr: 4  Sem: 2
11  2018-02-25 Sun  28 days  4 weeks  Qtr: 4  Sem: 2
12  2018-03-25 Sun  35 days  5 weeks  Qtr: 4  Sem: 2

The update statements (output as rows) are created by simple concatenation of the TableToUpdate, ColumnUpdatePattern and DateKeyColumns specified.   One update is created for each day in the fiscal year.

UPDATE dbo.DimDate SET Fiscal_year=2017, Fiscal_quarter=1, Fiscal_period=1, Fiscal_Week=1, Fiscal_week_overall_nbr=(1161 + 1), Fiscal_period_overall_nbr=(1037 + 1), [quarter_Fiscal_of_year_end_date_key] = 20170729 WHERE date_key = 20170430;

-- 364 update statements generated.

 

A Procedure for generating Data Warehouse Update code

Background

Transactional tables exist in Database A.   These tables have partner archive tables in the same or another database.  For each insert or change of a value in the transactional database a timestamped new row is entered into the counterpart history table.  Standard archiving, similar to the temporal archiving offered in SQL Server 2016.

The difficulty is that writing the code to populate the archive table (or a Type 2 Slowly Changing Dimension data warehouse table) is complex and error prone.  Particularly since a Merge statement cannot on its own achieve this, so a temporary table has to populated from the Merge Output clause, and so on.

Code Generator

The procedure UTIL_GenerateArchiveProcedureDDL will generate the code required to populate an archive table.  The procedure is in the file attached at the end of this article.

It can generate code for a single specified table or all tables in a schema or the database.

The source table(s) must have an explicit primary key.

The archive table(s) must

  • have same name as the source tables (TODO add option to add _Archive to the name)
  • have temporal date columns named StartDate and EndDate (TODO allow other naming schemes to be used)
  • have the schema as the source tables with the addition of two date columns StartDate (not null) and EndDate (nullable).

The generator and generated code requires this Error reporting procedure.

Examples

Usage and description.

Standard Table (single Primary Key)

-- Typical table with a single primary key.
CREATE TABLE dbo.Products(
	ProductId int NOT NULL IDENTITY PRIMARY KEY,
	ProductName varchar(30) NOT NULL,
	AvailableFromDate smalldatetime NOT NULL,
	IntroducerId int NULL);

Run this with results to grid (it may be necessary to set the SSMS option to display carriage returns in the output grid view).

-- Generate Archive procedure for this table only by specifying table name
-- (if there is a similarly named table in another schema, provide @SourceSchema too)
EXEC UTIL_GenerateArchiveProcedureDDL
	@WarehouseDatabaseName = Archive,
	@SourceTable = 'Products';

This is the generated DDL.

  • The main action occurs in the MERGE statement. This joins all rows in the LIVE table with all current rows, i.e. rows where the EndDate is Null, in the Warehouse table (selected by CTE TargetCTE).
  • The Live and Warehouse tables are joined on all columns of the Primary Key (this example has only one).
  • If there is a match it means the same record (“same” here means having the same Primary Key value) exists in live and warehouse databases.
    • The procedure then compares the sets of non-PK columns in each table using the EXCEPT set operator. If a row is returned, there is a difference in one of the non-PK columns.
    • The THEN clause UPDATES the EndDate column of the warehouse table to obsolete the row in the warehouse table.
    • Note that at this stage no new row can be inserted into the warehouse to represent the new value; that is handled after the MERGE statement.
  • The remaining two MERGE clauses are simpler
    • Not matched by SOURCE:  the record no longer exists in live so the warehouse row is obsoleted.
    • Not matched by TARGET: the record doesn’t exist in warehouse, so a row is inserted.
  • All the SOURCE columns are OUTPUT-ed to a temporary table, together with the MERGE action key.
  • We still need to insert rows for records that have changed value since the warehouse was last updated.  The Merge Output will contain all rows from the full join of the source and target tables.  These can be filtered to only the records that resulted in an UPDATE operation, but even that is too wide since it includes records which have been deleted from the source; we don’t want to insert those again.  Therefore we filter to insert only rows that exist in the merge Source – that is, those where the ouput-ed Source columns are not null.  To test this, the code chooses the primary key (or the first PK column of a compound key) since this cannot be null if the row exists.
  • The Merge and changed-row Insert statements are wrapped in a transaction.
  • Row counts follow.
CREATE PROCEDURE [dbo].[UpdateDataWarehouse]
/*
	** DO NOT EDIT. 
	** Auto-generated by procedure UTIL_GenerateArchiveProcedureDDL ON Jan 22 2017  3:35AM.

	PURPOSE
		This procedure updates an archive warehouse database with the current values in the source database.
		The warehouse database must contain the same tables with the same columns plus temporal (StartDate, 
		EndDate) columns.		

	CODE GENERATOR HISTORY
		18/01/2017 Andrew Ing 1.02.

*/
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRY

DECLARE @EffectiveDate datetime;
DECLARE @ObsoletedRowCount int;
DECLARE @InsertedRowCount int;
DECLARE @UpdatedRowCount int;
DECLARE @TableActionSummary varchar(1000);
DECLARE @DebugInfo varchar(2000);

SET @EffectiveDate = GETDATE();


-------------------------------------------------------------
-- TABLE: [dbo].[Products]

  SET @DebugInfo = 'Create Temp Table [#MergeOutput_Products]';
  CREATE TABLE [#MergeOutput_Products] (
    MergeAction nvarchar(10)
   ,[ProductId] int NULL
   ,[ProductName] varchar(30) NULL
   ,[AvailableFromDate] smalldatetime NULL
   ,[IntroducerId] int NULL
  );

  SET @DebugInfo = 'Merge [dbo].[Products]';
  BEGIN TRANSACTION;
  -- Select only Current (EndDate IS NULL) warehouse rows for the comparison.
  WITH TargetCTE AS
    (SELECT * 
     FROM Archive.[dbo].[Products]
     WHERE EndDate IS NULL
    )
  MERGE  TargetCTE AS [Target]
  USING  [dbo].[Products] AS [Source]
    ON
         [Target].[ProductId] = [Source].[ProductId]
  WHEN MATCHED
    AND EXISTS (
      SELECT
          [Target].[ProductName]
         ,[Target].[AvailableFromDate]
         ,[Target].[IntroducerId]
      EXCEPT
      SELECT
          [Source].[ProductName]
         ,[Source].[AvailableFromDate]
         ,[Source].[IntroducerId]
    )
    THEN  -- Record has changed.
      UPDATE
      SET [EndDate] = @EffectiveDate
  WHEN NOT MATCHED BY SOURCE
    THEN  -- Record deleted from source.
      UPDATE
      SET [EndDate] = @EffectiveDate
  WHEN NOT MATCHED BY TARGET
    THEN  -- New Record.
      INSERT (
        [StartDate]
       ,[ProductId]
       ,[ProductName]
       ,[AvailableFromDate]
       ,[IntroducerId]
       ) VALUES (
        @EffectiveDate
       ,[Source].[ProductId]
       ,[Source].[ProductName]
       ,[Source].[AvailableFromDate]
       ,[Source].[IntroducerId]
       )
  OUTPUT
     $Action AS MergeAction
     ,[Source].[ProductId]
     ,[Source].[ProductName]
     ,[Source].[AvailableFromDate]
     ,[Source].[IntroducerId]
  INTO [#MergeOutput_Products];

  -- Insert new rows for records that have changed (old rows were marked as expired by
  -- the first MATCHED clause. #MergeOutput_ has been populated only with Source columns
  -- but MERGE is a full outer join so exclude rows with a Null primary key column.
  SET @DebugInfo = 'Insert for changed rows [dbo].[Products]';
  INSERT INTO Archive.[dbo].[Products]
    (
      [StartDate]
     ,[ProductId]
     ,[ProductName]
     ,[AvailableFromDate]
     ,[IntroducerId]
    )
    SELECT
      @EffectiveDate
     ,[ProductId]
     ,[ProductName]
     ,[AvailableFromDate]
     ,[IntroducerId]
    FROM [#MergeOutput_Products]
    WHERE
      MergeAction = 'UPDATE'
      AND [ProductId] IS NOT NULL;
    SELECT @UpdatedRowCount = @@ROWCOUNT;

  COMMIT TRANSACTION

  SET @DebugInfo = 'Get Row Counts [dbo].[Products]';
  SELECT
    @ObsoletedRowCount = SUM(CASE WHEN MergeAction = 'UPDATE' THEN 1 ELSE 0 END),
    @InsertedRowCount = SUM(CASE WHEN MergeAction = 'INSERT' THEN 1 ELSE 0 END)
  FROM
    [#MergeOutput_Products];

  SET @TableActionSummary = CONVERT(varchar(19), GETDATE(), 121) + ' [dbo].[Products]
			 ' + CAST(ISNULL(@ObsoletedRowCount, 0) AS varchar(10)) + ' Rows Obsoleted, ' + 
			 CAST(ISNULL(@InsertedRowCount, 0) AS varchar(10)) + ' New Rows Inserted, ' +
			 CAST(@UpdatedRowCount AS varchar(10)) + ' Update Rows Inserted.';
  PRINT @TableActionSummary;
  D ROP TABLE [#MergeOutput_Products];

-- END OF TABLE [dbo].[Products]


END TRY
BEGIN CATCH
  EXECUTE dbo.ERROR_HandleCaughtError @DebugInfo;
  RETURN 5555;
END CATCH
/*
  END OF GENERATED SCRIPT
*/
GO

Many to Many tables

Or any table with just primary key columns.

CREATE TABLE dbo.StudentCourses(
	StudentId int NOT NULL,
	CourseId int NOT NULL,
	CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentId, CourseId)
	);

If the table only has a primary key then it is not possible to have changed values in the same record. The record either exists in both live and warehouse tables (in which case no action is needed) or it exists in only one, in which case it must be inserted or obsoleted in the warehouse accordingly.

If no non-primary key columns are found, the WHEN MATCHED AND EXISTS… clause is omitted from generated code.  Note in the section below that both primary key columns are used in the Merge join statement, and that the Matched And Exists clause is not generated.

...
  WITH TargetCTE AS
    (SELECT * 
     FROM Archive.[dbo].[StudentCourses]
     WHERE EndDate IS NULL
    )
  MERGE  TargetCTE AS [Target]
  USING  [dbo].[StudentCourses] AS [Source]
    ON
         [Target].[StudentId] = [Source].[StudentId]
     AND [Target].[CourseId] = [Source].[CourseId]
  WHEN NOT MATCHED BY SOURCE
    THEN  -- Record deleted from source.
      UPDATE
      SET [EndDate] = @EffectiveDate
  WHEN NOT MATCHED BY TARGET
    THEN  -- New Record.
      INSERT (
...

Generating Updates for all Tables

Omitting the SourceTable and SourceSchema parameters will generate DDL for a procedure that will include all tables in the database.   This can be restricted by providing SourceSchema (which will include all tables in the named schema) or SourceTable.

 

UTIL_GenerateArchiveProcedure.sql (3)

Mscomctl32.ocx ActiveX Control

Phenomenally useful. Not supported at 64bit.

Location

Normally:  C:\Windows\SysWOW64.

Versions

I’m using version 6.01.9846 of 9/12/2015

Registering

  • C:\Windows\system32>Regsvr32  c:\windows\syswow64\mscomctl.ocx
  • Unregister C:\Windows\system32>Regsvr32 /u  c:\windows\syswow64\mscomctl.ocx

Troubleshooting

First check References.  If it shows as MISSING, untick it and close the References dialog box.  Compile the code (which will fail, naturally), then re-add the reference.

Problem Library is  correctly (Re)Registered but you get The Expression MouseDown you entered …  error.  Or there is a The OLE server may not be registered error when double-clicking a control in design view:

The operation on the ListViewCtrl object failed.  The OLE server may not be registered.  To register the OLE server, reinstall it.

The OLE server may not be registered

Solution:  open the reference dialog and increase the Priority of the Microsoft Windows Common Controls 6.0 (SP6) entry  as far as it will go (it won’t go higher than the Access Object Library reference). This has immediate effect.