Sketchnotes from 24 Hours of Pass

These are my notes from the ’24 Hours of Pass’ event of September 2014.

’24 Hours of Pass’ is an online event where 24 presentations about SQL Server are delivered by SQL experts within 24 hours. It’s very good indeed.

Don’t put too much reliance on the contents of the Sketchnotes. I may have misunderstood, or written things down wrong, or otherwise mucked things up.

The slides from each of the Presentations is currently on-line on the pages for each presentation here:

24 Hours of PASS: Summit 2014 Preview > Schedule

Better still, the presentations themselves will be online shortly.

Brent Ozar on ‘Developers: Who Needs a DBA?’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

01 Who Needs a DBA

Brian Knight on ‘Performance Tuning SQL Server Integration Services (SSIS)’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

02 Tuning SSIS

Allan Hirt on ‘Availability Groups vs. Failover Cluster Instances: What’s the Difference?’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

03 Availabilty Groups and Failover Cluster Indexes

Erin Stellato, Jonathan Kehayias on ‘Everything You Never Wanted to Know about Extended Events’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

04 Extended Events

Gail Shaw on ‘Guessing Games: Statistics, Heuristics, and Row Estimations’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

06 Estimations

Tim Chapman, Denzil Ribeiro on ‘Troubleshoot Customer Performance Problems Like a Microsoft Engineer’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

07 Troubleshooting

Argenis Fernandez on ‘Secure Your SQL Server Instance without Changing Any Code’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

08 - Securing sql

Joe Webb on ‘Hiring the Right People: Interviewing and Selecting the Right Team’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

09 - Hiring

Robert Cain, Bradley Ball, Jason Strate on ‘Zero to Hero with PowerShell and SQL Server’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

10 Posh and Sql

Chris Shaw, John Morehouse on ‘Real World SQL 2014 Migration Path Decisions’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

12 - Migration

Davide Mauri on ‘Agile Data Warehousing: Start to Finish’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

14 Agile DWH

Julie Koesmarno on ‘”I Want It NOW!” Data Visualization with Power View’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

15 - PowerView

Jen Stirrup on ‘Business Intelligence Toolkit Overview: Microsoft Power BI and R’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

16 Power BI

Ryan Adams on ‘SQL Server AlwaysOn Quickstart

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

18 - always on

Posted in sqlserver

Filtering SSRS subscriptions from Sqlserver Jobs in Powershell

James Easton - The Salisbury Guide - Poultry CrossIf a Sqlserver instance hosts a Reporting Services database, then users’ report subscriptions are set up as Sql Server Agent jobs.

They are named like this:

E422ED2F-C8AD-445B-BF85-E4FD35EE8FAD
E668784B-0AA9-4DEC-8C3B-C9EB6CBC7ADE
E72E1822-408E-4008-B2D4-6065843A9C86
E8658059-BEF6-493A-908F-928B9B3F2065
EA5D435F-1095-4578-9325-EC3AB14327AC

If you query Sqlserver jobs from Powershell (or in SSMS) these can tend to ‘swamp’ the ‘normal’ jobs like so:

CAEC2E40-7AF5-4CB1-B7D2-66343A79471B
CDAEDD6F-07C6-4849-A8AA-5643F771B39B
CDF9A96D-747F-4439-855C-CE916E261646
CEEC38CC-3FCA-439E-8C32-33719F9291D2
database1 backup
DB64DBA4-D51E-43A0-BA17-A4B247075F70
DB834C0D-6206-4A22-87E6-821414EF95E4
DEF15AC7-8142-4909-A4A2-9D40B8CAC3AD
DF4E92E3-E689-44EF-B6C6-8065C18734F0
DF9471A5-4829-4C3E-ACF9-B0D912E26AD2
E05EC820-D572-4168-B14A-24A9102AB1EB
E13A783B-DAED-4F51-87AE-058B10AFCF60
E1E6746A-8F87-4DF3-BD30-3D88285F5305
E422ED2F-C8AD-445B-BF85-E4FD35EE8FAD
F441C4C6-342A-476B-B9B4-9B1FA7AFC545
F46BC346-24A6-41A8-8965-CC91E8F8BE26
F6EC8655-0487-435C-A390-622B692B63DE
F9174393-7A59-4E71-A74B-0E27A9A0118B
F9BB8EEA-BD93-4580-A5F8-DA754EF7DC32
F9C775B1-F724-4146-A4CD-64D0735ECE15
FC0FECED-BCC6-46AF-B7C9-E0551DA01E15
FDC6412F-C153-449E-B7E5-FD03281B69E4
FF2A1611-1763-4790-B7C9-64ABD83B710C
FF4A61A4-2580-4E94-AAEA-66FB7C7ABD9D
Warehouse Load Monday & Wednesday
Warehouse Load Sunday
Warehouse Load Thursday
Warehouse Load Tuesday & Friday

You can filter the SSRS stuff out in Powershell by filtering on the ‘Category’ property as follows:

dir Sqlserver:\sql\$ServerInstance\Jobserver\Jobs | where {$_.Category -ne 'Report Server'}

This gives you a more useful list

Name
----
db1 backup
Warehouse Load Monday & Wednesday
Warehouse Load Sunday
Warehouse Load Thursday
Warehouse Load Tuesday & Friday

Posted in powershell

Sql server: Generate sql to disable triggers

This seems to work:

SELECT 
     'disable trigger ' + USER_NAME(trig.schema_id) + '.' +  trig.name + 
     ' on ' + 
     USER_NAME(tab.schema_id) + '.' + OBJECT_NAME(trig.parent_object_id) +
     char(10) +
     'go'
FROM sys.objects trig
INNER JOIN sys.objects tab
    ON trig.parent_object_id = tab.object_id 
WHERE trig.type = 'TR'

It generates sql like this:

disable trigger dbo.tr_AfterUpdate on dbo.BigTable
go
disable trigger dbo.tr_BeforeUpdate on dbo.BigTable
go

Notes and Queries - Cathedral, bell tower, stonehenge

Posted in sqlserver

Generate sqlserver table creation scripts from Powershell

NPG Ax143583; Edith Olivier by Lady Ottoline Morrell
I didn’t know you could do this – the ‘table’ object in the SQL provider has a ‘script’ method, so you can do the following:

$ServerInstance = "whatever"
$DatabaseName = "whatever"
foreach ($TableName in dir Sqlserver:\sql\$ServerInstance\databases\$DatabaseName\tables\) {
  $TableName.script()
}

It’s a rough equivalent to doing a right click >> ‘Script table as’ >> ‘Create’ in SSMS, except you can do it for some or all of the tables on a database.

I’m looking forward to playing with the ‘script’ method for other database objects.

Pic: Edith Olivier and the Palladian Bridge in the grounds of Wilton House, by Lady Ottoline Morrell [Public domain], via Wikimedia Commons

Posted in powershell

Sketchnotes from 24 Hours of Pass: 2014 launch edition

These are my sketchnotes from last week’s ’24 Hours of Pass’ on-line event. All the talks were useful and interesting, my notes perhaps are neither….but they do serve to jog my memory.

’24 Hours of Pass’ is an online event. 24 speakers talk about 24 sqlserver related topics over, surprisingly enough 24 hours. It’s very good and the next one, at the time of writing, is in September.

The ’24 Hours of Pass’ points of presence are:

Twitter: @24Hop
Website: 24 Hours of PASS

I’m going to put a bit of text around the works of art below, if only so that you can see where one starts and the other ends.

I’d like to emphasize that these are just my impressions. I may have misunderstood or written stuff down wrong or otherwise mucked up. The presenters slides should be available on the 24 Hours of PASS website – these will be much more authoritative….but will have fewer drawings of Terminators

SQL 2014: changes to the optimizer

First up was a great talk about changes to the optimizer in 2014 – specifically around the cardinality estimator. The way I understood it (which could be well wide of the mark – I can’t vouch for having recorded anything accurately….I may have been scanning or tweeting or wondering whether I could draw a penguin at a critical moment) was the the cardinality estimator has changed little or not at all since SQL 7.0.
SQL 2014 Changes to Query Plans

Next, a talk about some new features in SQL 2014. The main bits I took from this were:

  • buffer pool extension – I think I understand this to be a flash write cache
  • the use of Azure for either backups or AlwaysOn
  • the resource governor being extended to I/O

…and I can’t draw Arnie.
2014-features-743x1024

SQL 2014: Private and Hybrid Cloud Features

I had the vaguest idea of what Polybase was before the talk. My understanding now is that it allows Parallel Data Warehouse (and therefore the user) to talk to Hadoop seamlessly. I was particularly interested in the idea that you could have your Dimension tables in SQL and Fact table in Hadoop.
Polybase

SQL 2014: Migration to SQL Azure

The talk on migration to Azure was very interesting. I liked the idea of ‘the cloud continuum’. What I haven’t reflected in the Sketch are that there was a demo of the cloud GUI – well worth seeing.
Migration to sql azure

SQL 2014: Columnstore indexes

Columnstore indexes seem very cool. In principle data is stored on column-by-column (duh!) rather than row-by-row. This means that you can compress a gazillion times more effectively, and as long as people aren’t doing ‘select *’ from every table you won’t need to traverse as much data.

First talk was by Jimmy May:
columnstore - jimmy may

Second was by Sunil Agarwal:
Clustered columnstore

SQL 2014: Always On

I didn’t know much about Always On, so this filled in a lot of gaps…and there seem to have been a bunch of improvements in 2014
Always On 2014

SQL 2014: the Modern Data Warehouse

Finally, Dan Kogan’s talk about ‘the modern data warehouse’ – how ‘Big Data’ changes things, and Polybase bridges the gap bewteen the modern and the trad warehouse:
modern-data-warehouse-743x1024

Posted in sqlserver

T-sql to show SSRS permissions

Bristol_Boxkite_1911

I’m not great at T-sql, and I know that cursors are unfashionable in Microsoft world, but this gives me the output I want, as below:

Path Username GrantedPermissions
/Data Sources cfc\jmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/Data Sources/Statszone BUILTIN\Administrators Content Manager
/Data Sources/Statszone cfc\jmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/Data Sources/Statszone cfc\jmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/zTest/By far the greatest team report cfc\jmourinho Browser,Content Manager,My Reports,Publisher,Report Builder
/zTest/By far the greatest team report cfc\bbuck Browser
/zTest/By far the greatest team report cfc\rfaria Content Manager

drop table #ReportPermissions

create table #ReportPermissions
(Path varchar(100),
Username varchar(100),
GrantedPermissions varchar(100))

declare
UserWithPermissionsCursor cursor for
select -- ,roles.[RoleID]
rolename
-- ,users.[UserID]
,username
-- ,catalog.[PolicyID]
,path
FROM [ReportServerRep01].[dbo].[PolicyUserRole],
roles,
users,
catalog
where policyuserrole.RoleID = roles.roleid
and policyuserrole.UserID = users.userid
and policyuserrole.policyid = catalog.PolicyID
order by path, username, rolename

declare @rolename varchar(100)
declare @username varchar(100)
declare @path varchar(100)
declare @PermissionsString varchar(100)
declare @Savedusername varchar(100)
declare @Savedpath varchar(100)

open UserWithPermissionsCursor
FETCH UserWithPermissionsCursor INTO @rolename, @username, @path

WHILE 0 = @@fetch_status
BEGIN
if (@SavedUserName = @username AND @SavedPath = @path)

set @PermissionsString = @PermissionsString + ',' + @Rolename

else
begin
-- Output the line
insert into #ReportPermissions
(Path,
Username,
GrantedPermissions)
values
(@SavedPath,
@SavedUserName,
@PermissionsString)

-- Reinitialize variables
set @SavedPath = @path
set @SavedUsername = @username
set @PermissionsString = @rolename

end

FETCH UserWithPermissionsCursor INTO @rolename, @username, @path

END
close UserWithPermissionsCursor
deallocate UserWithPermissionsCursor

select * from #ReportPermissions

Pic: A Bristol Boxkite at Stonehenge. Glossing Wikimedia: It is a photograph created by the United Kingdom Government and taken prior to 1 June 1957 and HMSO has declared that the expiry of Crown Copyrights applies worldwide (see File:Bristol Boxkite 1911.jpg – Wikimedia Commons)

Posted in sqlserver

SSRS error: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater – simple fix

Memorials of Old Wiltshire - Raising the Leaning Stone at Stonehenge

Memorials of Old Wiltshire - Raising the Leaning Stone at StonehengeA quick post on a quick fix.

I installed Sql Server Reporting Services 2008 R2 on the report server, then installed Oracle client 11.2.0.4.

I copied across a valid tnsnames.ora file, and verified connectivity with both sqlplus and tnsping.

However, trying to connect to one of the Oracle databases from within SSRS I got:

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

…helpfully highlighted in an angry red.

Fix is fairly straightforward – SSRS hadn’t picked up the change to %PATH% done by the Oracle install yet, so the fix was to re-start the SSRS service.

Pic: From an old out-of-copyright book called ‘Memorials of Old Wiltshire’

Posted in sqlserver

Powershell pattern matching to check if a string is hex

Hexadecimal-countingI’ve been playing with pattern matching in Powershell.

I was trying to use ‘-match‘ to ‘Check if the string is a hexadecimal number’ for the ‘Perl one-liners in Powershell’ page I’ve been working on.

I can’t pretend to entirely understand or explain pattern matching in Powershell, or in *nix, but this seems to work.

$X = "21e" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"

This says match the string against ‘any combination of the characters within the square brackets (i.e. the hex digits) to the same length as the original string’.

So, the square brackets contain the allowable characters.

The curly brackets give the number of characters.

I tried just doing:

$X = "21e" ; $X -match "[0123456789abcde]{$X.length}"

…but this didn’t seem to work.

Tests

I tried the following strings – they all seemed to come up with the right answer:

$ $X = "21e" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
True
$ $X = "21edjhsd" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
False
$ $X = "21e34782348237847832748723" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
True
$ $X = "21e34782348237847832748723f" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
False
$ $X = "21e34782348237847832748723acbdaaa" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
True
$ $X = " " ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
False

….although I’m not sure whether this is right or wrong:

$ $X = "" ; $Y = $X.length ; $X -match "[0123456789abcde]{$Y}"
True

Picture: By mate2code (Own work) [Public domain], via Wikimedia Commons

Posted in powershell

Idiot’s guide to github

Screenshot from 2014-02-20 22:13:47

Screenshot from 2014-02-20 22:13:47I’ve been finding it difficult to get my head around github.

As is often the case in the end I found it easiest to do so through the command line.

I’d already created a repository which holds my firefox ‘New tab’ and home page

To amend it I did the following. I think.

Set up my githubcredentials:

git config --global user.name mattypenny
git config --global user.email mattypenny@gmail.com
git config --global credential.helper 'cache --timeout=36000'

Clone the repository locally

git clone https://github.com/mattypenny/start_page.git

This creates a folder with all the repository files in it locally

cd start_page

Then I made all my changes to the start page.

Then I did a local commit:

git commit -m 'New pics and added in all the picture credits'

This shows that git was pointing to the github repository as ‘origin’. This seems to have been retained from the clone command.

$ git remote -v
origin	https://github.com/mattypenny/start_page.git (fetch)
origin	https://github.com/mattypenny/start_page.git (push)

Then I pushed it all back to github

$ git push origin master
Username for 'https://github.com': mattypenny
Password for 'https://mattypenny@github.com': 
Counting objects: 17, done.
Delta compression using up to 2 threads.
Compressing objects: 100% (11/11), done.
Writing objects: 100% (11/11), 1.05 MiB | 172 KiB/s, done.
Total 11 (delta 1), reused 0 (delta 0)
To https://github.com/mattypenny/start_page.git
   9f6e3d5..40431e1  master -> master
Posted in Uncategorized

SSIS: Invalid access to memory location

Problem came up trying to connect to a remote Oracle source:

Started:  12:03:03
Error: 2013-10-09 12:03:03.84
   Code: 0xC0202009
   Source: <removed>
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x800703E6.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x800703E6  Description: "Invalid access to memory location.".
End Error
Error: 2013-10-09 12:03:03.84
   Code: 0xC020801C
   Source: Copy Data
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "xxx" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2013-10-09 12:03:03.84
   Code: 0xC0047017
   Source: xxxx
   Description: component "xxx" (1) failed validation and returned error code 0xC020801C.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

Fix was to go into job step properties, select the ‘Execution options’ tab and click on ‘Use 32-bit runtime’.

I’m wondering whether we had an Oracle 32-bit install, or a 32-bit install that wasn’t entirely removed

Posted in sqlserver