Markdown support in Notepad is fab….it would be fab-ber if it supported the backtick code syntax, and if it had a keyboard shortcut to switch between Formatted and Syntax view

First I built a list of files

FileList=dir -recurse C:\temp\folderx -Attributes !Directory | 
    ? name -notlike '*exe' | 
    ? name -notlike '*dll'| select fullname

Then for each one, record the name to a log file, edit it and prompt for and store a comment

foreach ($F in $Filelist) {
    $F.Fullname >> commentlog.txt
    $File = $F.Fullname
    C:\Progra~2\vim\vim74\gvim.exe $File
    $Comment = read-host "Changes made to $File"
    $comment >> commentlog.txt
}

This is fairly trivial…and I’ve no idea if ‘aliasee’ is a real word or not, but I found this quite useful today:

get-alias | select ResolvedCommand, name | sort -property resolvedcommand

On my laptop, this gives:

ResolvedCommand                Name
---------------                ----
Add-Content                    ac
Add-PSSnapin                   asnp
Clear-Content                  clc
Clear-History                  clhy
Clear-Host                     cls
Clear-Host                     clear
Clear-Item                     cli
Clear-ItemProperty             clp
Clear-Variable                 clv
Compare-Object                 diff
Compare-Object                 compare
Connect-PSSession              cnsn
Convert-Path                   cvpa
Copy-Item                      cp
Copy-Item                      copy
Copy-Item                      cpi
Copy-ItemProperty              cpp
Disable-PSBreakpoint           dbp
Disconnect-PSSession           dnsn
Enable-PSBreakpoint            ebp
Enter-PSSession                etsn
Exit-PSSession                 exsn
Export-Alias                   epal
Export-Csv                     epcsv
Export-PSSession               epsn
ForEach-Object                 foreach
ForEach-Object                 %
Format-Custom                  fc
Format-List                    fl
Format-Table                   ft
Format-Wide                    fw
Get-Alias                      gal
get-cal                        cal
Get-ChildItem                  dir
Get-ChildItem                  ls
Get-ChildItem                  gci
Get-Command                    gcm
Get-Content                    cat
Get-Content                    gc
Get-Content                    type
get-functions                  getf
Get-History                    history
Get-History                    ghy
Get-History                    h
Get-Item                       gi
Get-ItemProperty               gp
Get-Job                        gjb
Get-Location                   pwd
Get-Location                   gl
Get-Member                     gm
Get-Module                     gmo
get-os                         gos
Get-Process                    gps
Get-Process                    ps
Get-PSBreakpoint               gbp
Get-PSCallStack                gcs
Get-PSDrive                    gdr
Get-PSSession                  gsn
Get-PSSnapin                   gsnp
Get-Service                    gsv
Get-Unique                     gu
Get-Variable                   gv
Get-WmiObject                  gwmi
Group-Object                   group
help                           man
Import-Alias                   ipal
Import-Csv                     ipcsv
Import-Module                  ipmo
Import-PSSession               ipsn
Invoke-Command                 icm
Invoke-Expression              iex
Invoke-History                 r
Invoke-History                 ihy
Invoke-Item                    ii
Invoke-Locate.ps1              locate
Invoke-NullCoalescing          ??
Invoke-RestMethod              irm
Invoke-WebRequest              wget
Invoke-WebRequest              iwr
Invoke-WebRequest              curl
Invoke-WmiMethod               iwmi
Measure-Object                 measure
mkdir                          md
Move-Item                      move
Move-Item                      mv
Move-Item                      mi
Move-ItemProperty              mp
New-Alias                      nal
New-Item                       ni
New-Module                     nmo
New-PSDrive                    mount
New-PSDrive                    ndr
New-PSSession                  nsn
New-PSSessionConfigurationFile npssc
New-Variable                   nv
Out-GridView                   ogv
Out-Host                       oh
Out-Printer                    lp
Pop-Location                   popd
powershell_ise.exe             ise
Push-Location                  pushd
Receive-Job                    rcjb
Receive-PSSession              rcsn
Remove-Item                    rmdir
Remove-Item                    del
Remove-Item                    rd
Remove-Item                    rm
Remove-Item                    erase
Remove-Item                    ri
Remove-ItemProperty            rp
Remove-Job                     rjb
Remove-Module                  rmo
Remove-PSBreakpoint            rbp
Remove-PSDrive                 rdr
Remove-PSSession               rsn
Remove-PSSnapin                rsnp
Remove-Variable                rv
Remove-WmiObject               rwmi
Rename-Item                    rni
Rename-Item                    ren
Rename-ItemProperty            rnp
Resolve-Path                   rvpa
Resume-Job                     rujb
Select-Object                  select
Select-String                  sls
Set-Alias                      sal
Set-Content                    sc
set-debug                      db
Set-Item                       si
Set-ItemProperty               sp
Set-Location                   sl
Set-Location                   chdir
Set-Location                   cd
Set-PSBreakpoint               sbp
Set-Variable                   sv
Set-Variable                   set
Set-WmiInstance                swmi
Sort-Object                    sort
Start-Job                      sajb
Start-Process                  start
Start-Process                  saps
Start-Service                  sasv
Start-Sleep                    sleep
Stop-Job                       spjb
Stop-Process                   kill
Stop-Process                   spps
Stop-Service                   spsv
Suspend-Job                    sujb
Tee-Object                     tee
Trace-Command                  trcm
Update-LocateDB.ps1            updatedb
Wait-Job                       wjb
Where-Object                   where
Where-Object                   ?
Write-Output                   write
Write-Output                   echo

For reasons that aren’t necessarily relevant, I wanted to use Remote Desktop to visit a list of servers¹. I tried doing this:

foreach ($S in "server01", "server02, "server03") { 
    mstsc /f /V:$S
}

This works….but it immediately starts rdp sessions to each of the servers. This is fine in this example, where there are only 3 servers, but in real life I’ve got a list of twenty or so and I don’t really want to open 20-odd rdp sessions at once.

What I did instead to force it do the rdp’s sequentially was this:

foreach ($S in "server01", "server02, "server03") { 
    mstsc /f /V:$S
    $ThrowAway = read-host "Hit Return"
}

This was the Powershell code I used to create the all the podcasts I’ve ever tweeted about post.

I downloaded the tweets from Twitter itself - I think there was a link somewhere within ‘Settings’

The .csv file looks like this:

So the code is:

$PodTweets = Import-Csv c:temptweets.csv | ? text -like "*podcast*"

$TweetsAsHtml = foreach ($P in $PodTweets)
{
#   write-output $P.timestamp.Substring(0,10)

   # Splitting the tweet text into words to allow for the processing of urls
   $TweetTextAsArray = $P.text.split(" ")

   $TextWithLink=""

   foreach ($Word in $TweetTextAsArray)
   {
        if ($Word -like "http:*")
        {
            # if there is an expanded_url, then use that instead
            if ($P.expanded_urls -ne "")
            {
                $Word = $P.expanded_urls

                # for some reason the expanded url is sometimes repeated in the download
                if ($Word -like "*,*")
                {
                    $Word = $Word.split(",")[0]
                }
            }

            # re-format the URL as a link
            $Word = "`<a href=`"$Word`"`>$Word`<`/a`>"
        }
        $TextWithLink = "$TextWithLink$Word "


    }

    # create an object and output that
    $properties = @{'TweetDate'=$P.timestamp.Substring(0,10);
                    'TweetText'=$TextWithLink}

    $ReformattedTweets = New-Object -Type PSObject -Prop $properties

    write-output $ReformattedTweets

}

$TweetsAsHtml | fl | out-file -encoding ascii -FilePath x.txt -width 1000

This almost speaks for itself…but it might be worth noting that:

• I’m only interrogating the latest file here

• it’s a teeny bit cumbersome - I would probably function-alize it

• the big advantage here compared to the GUI is you can cut-and-paste it really easily

dir sqlserver:sql\the_server_name
$logs.readerrorlog(0) |
  where logdate -gt ([datetime]::ParseExact('25/02/2015 07:27:36',"dd/MM/yyyy HH:mm:ss",$null))  |
  select processinfo, text |
  ft -a -wrap

I hadn’t noticed this before.

If you do a dir listing of the databases for an instance within the Powershell Sqlserver provider, it doesn’t show the system databases

PS C:powershell> dir SQLSERVER:\SQL\my_pcinst2012\databases

Name                 Status          Recovery Model CompatLvl Collation                      Owner
----                 ------          -------------- --------- ---------                      -----
AdventureWorks2012   Normal          Simple               110 SQL_Latin1_General_CP1_CI_AS   matty
TSQL2012             Normal          Full                 110 Latin1_General_CI_AS           matty

To get a listing for the system databases you can do the following. I would imagine there’s a better way (perhaps some equivalent to ls -a?)…but I can’t think of it at the minute¹

PS C:powershell> foreach ($DB in ("master", "msdb", "model", "tempdb")) {gi SQLSERVER:\SQL\my_pcinst2012\databases\$DB }

Name                 Status          Recovery Model CompatLvl Collation                      Owner
----                 ------          -------------- --------- ---------                      -----
master               Normal          Simple               110 Latin1_General_CI_AS           sa
msdb                 Normal          Simple               110 Latin1_General_CI_AS           sa
model                Normal          Simple               110 Latin1_General_CI_AS           sa
tempdb               Normal          Simple               110 Latin1_General_CI_AS           sa

This is extracted from Adam Pritchard’s Markdown Cheatsheet.

It’s what I would see as the useful bits….but of course my useful bits won’t be the same as your useful bits!

I’m installing sqlserver 2005, and it seemed to hang towards the end of the installation.

The installer hung while it was installing ‘Workstation Components, Books Online’, with the progress message being ‘Removing Backup Files’. As it happened I kicked this off at the end of the day, and it was still hanging the next morning

I checked the installer log but there was nothing very interesting

<Func Name='LaunchFunction'>
Function=Set_CommitFlag
<Func Name='GetCAContext'>
<EndFunc Name='GetCAContext' Return='T' GetLastError='0'>
Doing Action: Set_CommitFlag
PerfTime Start: Set_CommitFlag : Mon Jan 26 16:46:51 2015
<Func Name='Set_CommitFlag'>
Can I commit?
Set_CommitFlag called successfully.Committed
<EndFunc Name='Set_CommitFlag' Return='0' GetLastError='0'>
PerfTime Stop: Set_CommitFlag : Mon Jan 26 16:46:51 2015
<EndFunc Name='LaunchFunction' Return='0' GetLastError='0'>

It turned out to be very much a problem between the chair and the keyboad.

At the start of the install I’d had this warning message:

I wasn’t too worried about it - I’m installing an old version of SQL on a newer OS - but I left the warning message window open…partly so that I could investigate further later.

Anyway…as soon as I shut that warning window down, the install kicked back into life and soon completed.

I’m installing sqlserver 2005, and it seemed to hang towards the end of the installation.

The installer hung while it was installing ‘Workstation Components, Books Online’, with the progress message being ‘Removing Backup Files’. As it happened I kicked this off at the end of the day, and it was still hanging the next morning

I checked the installer log but there was nothing very interesting

<Func Name='LaunchFunction'>
Function=Set_CommitFlag
<Func Name='GetCAContext'>
<EndFunc Name='GetCAContext' Return='T' GetLastError='0'>
Doing Action: Set_CommitFlag
PerfTime Start: Set_CommitFlag : Mon Jan 26 16:46:51 2015
<Func Name='Set_CommitFlag'>
Can I commit?
Set_CommitFlag called successfully.Committed
<EndFunc Name='Set_CommitFlag' Return='0' GetLastError='0'>
PerfTime Stop: Set_CommitFlag : Mon Jan 26 16:46:51 2015
<EndFunc Name='LaunchFunction' Return='0' GetLastError='0'>

It turned out to be very much a problem between the chair and the keyboad.

At the start of the install I’d had this warning message:

I wasn’t too worried about it - I’m installing an old version of SQL on a newer OS - but I left the warning message window open…partly so that I could investigate further later.

Anyway…as soon as I shut that warning window down, the install kicked back into life and soon completed.

This was a bit of a ‘Duh!’ moment.

I use the following to ‘comment out’ chunks of Wordpress pages that I’m still working on

<?php /*
Some old rubbish that I have'nt finished working on yet
*/?>

This seemed to have ‘stopped working’.

The reason was my ‘Exec-PHP’ plugin was de-activated. I don’t remember de-activating it. I guess it’s possible that an automat(t)ic update might have done so, but I don’t know.

I haven’t been able to find a T-sql equivalent of the Oracle ‘set feedback on’, which outputs a line telling you how many rows have been effected by the last sql statement.

The closest thing, I think, is:

select 'Rows updated: ' + CAST(@@ROWCOUNT as varchar(10))

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

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

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:

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

A 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.

A 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.

I’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 linux, 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

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

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

This page is a bit of documentation for how I do stuff on the multifarious websites that I’ve perpetrated over the years. These are very much notes for myself but if anybody else should happen upon this page I hope you find it useful :)

On This Day - how it works

These are a couple of vim things I use regularly.

My local history website has a list of stuff that happened on this day' in Salisbury’s history

I maintain the list by adding a page for each event into a Wordpress page structure that looks like this:

• on-this-day
  • month
  • event page, title of which is prefixed by the date

I fill in the event pages' ‘Order’ field (under ‘Page Attributes’) with the day of the month. The months' ‘Order’ field has the ‘number of the month’ (i.e. 1 is January, 2 is February etc). This keeps everything in order.

Then the top level page has this:

    <ul class="sitemap-pages">
    
    

I think I’m going to change this to have a separate wp_list_pages for each month. This will allow me to have a table of contents, although it will slow it down, I guess.

On This Day - reformatting from dynamic list of Wordpress child pages to static list for the weekly post

Anyhoo, I use the top level list to create a weekly post, summarizing that weeks events in Salisbury history.

There are a couple of vim things that help me do that.

When I grab the page source from the top level list, each page looks like this:

<li class="page_item page-item-7354"><a href="/on-this-day/april/28th-april-1948-terry-pratchetts-birthday">28th April 1948 - Terry Pratchett's birthday</a>

which looks like this on the page:

map q /[0-9][0-9][0-9][0-9] dw$A (pi).

/ May
cw: 

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: 
   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

Clustering Conundrum - Allan Hirt

Running some HA classes in Europe.

Loose definition - networked servers.

Used in different contexts in sqlserver - clusterered indexes, SETI type stuff, windows clusters, NLB, WSFC

• Windows - network load balance, windows server failover cluster(wsfc)
• Sqlserver - failover cluster instance

Can’t have the latter without the former

Sqlserver built on WSFC

75% of problems tend not to be SS

Resource group - like a folder of resources

Resources are often interdependent

Quorum - what you need to have to be up or down

Has become much easier from 2008 onwards.

Availability Groups - are sql-level

www.sqlha.com/2012/01/0… www.sqlha.com/2013/04/2…

Need to get involved with the Windows very heavily - can’t just do the sql bit. <— big takeaway

DBAs tend to become expert in Windows clustering

Can’t convert a standalone instance to cluster. Has to be new install.

Can do a 2-node cluster at Standard Edition.

2008 needs to be Windows Enterprise. 2012 onwards can be standard or datacenter editions

You get a ‘tranport error’ during failover, but SSMS, at least, automatically failsover.

Got to have AD

Only one default instance per WSFC, but can have multiple instances on one WSFC

Keys to success Learn WSFC Validation can show up problems Need to have test clustered instances Need to be an WSFC expert

www.sql ha.com MS Cluster Team blog KBs: find the slide

Hands-on fundamentals for good SQL programming - Kevin Kline

Test harness needs: dbcc freeproccache dbcc dropcleanbuffers set statistics time set statistics io set showplan text|xml

sys.dm_os_performance counters sys.dm_os_wait_stats

sqlskills.com - Glenn Berry

Scan count - number of scans! Reads - in 8K pages Read-ahead - it dynamically goes multi-page read (i.e. multiblock reads)

set showplan_text on - does the command line style explain plan

clustered index scan is equivalent of an Oracle FTS, because, by default tables are held in clustered indexes

Can set statistics time and i/o from within the toolbar

Worth enabling:

• estimated execution plan and

• actual execution plan

• it shows up differences, which can show problems with statisitcs etc

Sys.dm_os_wait_stats

• shows bottlenecks

Sys.dm_exec_query_stats

• query performance stats

sys.dm_db_index_usage_stats

sys.dm_io_virtual_file_stats

Queries from SQLAgent man ?

Spools are red flags.

As are, scans, parallelism

Kimberly Tripps articles on ‘indexes from every angle’

Window Function Fundamentals - Christina Leo

Stuff like:

SELECT empid, ordermonth, qty, SUM(qty) OVER(PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runqty FROM Sales.EmpOrders;

Indexing segment and sequence operators in Explain Plan

Index should be POC

• Partition
• Ordering
• Columns

Even if this doesn’t improve the elapsed time of the query it will eat up less memory.

The where clause happens before the select, so the filter happens before the Window function kicks in

christinaleo.net

CTE - the view thing at the top of the query with the WITH

Powerpivot - Susan O'Connell

Free add-in in 2010, native in 2013

Very worthwhile to leeave stuff out of the data model, both for performance and so as to simplify from a user perspective.

There are options to hide stuff from the users anyway.

Needs 8G of RAM on client

Data Internals - Bradley Balls

@sqlballs

Why learn internals - it conveys the idea that you know what you’re talking about!

Byte swapping - swaps hex pairs to protect proprietry data

dbcc ind dbcc page dbcc traceon(3604) -output goes to smss window

Record structure

• Tag bytes
• null bitmap
• fixed length
• null bitmap
• variable offset attay
• variable length columns

forwarding records - only in heaps like chains in Oracle

• only occurs in heaps

Text records

• LOBS > 8000 bytes (or sometimes 4000 bytes)
• 24 byte pointer to LOB

Ghost record

• deleted record left in place for undo optimization
• when in snapshot isolation mode

Versioned Records

• when Version Store os used. e.g. got online index rebuilds

MCM Video Series - especially

Record structure

• Tag bytes
• null bitmap
• fixed length
• null bitmap
• variable offset attay
• variable length columns

In the bitmap, 1 is null, 0 is not null

Record structure

• Tag bytes
• null bitmap
• fixed length
• null bitmap
• variable offset attay
• variable length columns

Pages

Data page header 96 bytes data 8096 bytes Row offest array (aka slot array) at the bottom

Index page leaf non-leaf

Text text tree lob text mox slob

sort

boot page 1:9 in any database dbcc page (‘db name’ , 1, 9, 3) - shows lots of boot info about database <— worth doing

Extents

• grouping of 8 pages

Allocation bitmaps

• free pages and allocated pages

Structure is: page is allocated is mixed allocation page is iam page page has ghosted rows space indicator - 3 bits show whether over 10% full, 30% full etc

IAM page

Storage and Virtualization - Denny Cherry

With Fusion IO, disk i/o is no longer the bottleneck

OLTP systems don’t much use the SAN cache because they don’t tend to use the same data twice. Data warehouses get more value because of pre-fetching.

Implies San cache is more about pre-cacheing than re-use

Force flushing effectively disables the cache until it’s finished. Don’t want to do it Low-water mark should be around 20% - you don’t want to be flushing immediately High-water mark 90% - because force flushes are bad

Power failure - most arrays have internal batteries.

Planned outage - MUST shut down sql server before the cache flushes.

Disk alignment matters

Big Data Visualization for Business Intelligence Professionals - @JenStirrup

Powerview - anti-spiderman. great power, but no responsibility for the data

Powermap -3d excel plugin. Formerly known as geo flow

Hortonworks sandbox- free download with hadoop stuff

Database Design - @DataAudrey

Availabilty Groups - Joe Dantoni

ex-Entergy chap!

Valid DR plan - we can do without system X, but not system Y.

Dilbert Our Disaster Recovery Plan Goes Something Like This <— google this

HA DR

Clustering works well from windows/sql 2008 on, but it’s not a DR solution.

Most cloud providers don’t charge for inbound data.

RPO - recovery point objective. How much data can we afford to lose? RTO - recovery time objective. How long can we do without a system?

Everything should be written down, and executive signed off.

Free Azure capacity with MSDN ? are we DR-ing Reporting Services?

Software as a service - gmail, salesforce.com

Infrastructure as a service - Azure, Amazon

platform as a service - Azure SQL, Amazon Relational Database Service

Sql azure now knwon as Windows Azure Sql Database (WASD).

Security in the Cloud

• are your security standards as good as Microsoft or Amazons

MS compliant with SOX and European regulations

Always On Availability Groups

• doesn’t need shared storage
• can go across IP subnets
• can therfore be a DR solution

Is based on log shipping(?)

Availability Groups is standard only, but there are ways of doing log-shipping. No failover capacity in standard.

Dont quite get why you nedd wsfc

Scalability - Glenn Berry

Common issues:

• overuse of query generators
• ad-hoc sql from the application
• lots of xml columns
• user-defined functions
• too much business logic in the database
• overuse of DML triggers
• overuse of CLR assemblies

Glenn has sql server diagnostic information queries <—-worth downloading

Intel tick-tock release strategy

Servers are like pets - scale up Servers are like cattle - commodity

Trying to change the sql server agent to run as a non-admin user, I couldn’t get Sql Server Agent to work without giving it administrator privileges. I got:

WMI Provider Error
Access is denied. [0x80070005]
OK

You have to grant two privileges - both the Agent User and the MSSQL user.

• SQLServerSQLAgentUser$bigserver$inst01
• SQLServerMSSQLUser$bigserver$inst01

Trying to change the sql server agent to run as a non-admin user, I couldn’t get Sql Server Agent to work without giving it administrator privileges. I got:

---------------------------
WMI Provider Error
---------------------------
Access is denied. [0x80070005]
---------------------------
OK
---------------------------

You have to grant two privileges - both the Agent User and the MSSQL user.

• SQLServerSQLAgentUser$bigserver$inst01
• SQLServerMSSQLUser$bigserver$inst01

This was spectacularly incompetent even by my high standards of incompetence.

It illustrates that if you spell the name of a column incorrectly in Powershell, you don’t get an error, you just get a null column

get-wmiobject -class win32_operatingsystem -computer rdm016 |
select __Server, ServicePackMajorVerstion

And got:

__SERVER                             ServicePackMajorVerstion
--------                             ------------------------
RDM016

I thought that no service packs had been applied. This, as more attentive readers will have spotted, was not the case. The null under ServicePackMajorVerstion is because you don’t spell ‘version’ with a ’t'. Two learnings from this:

• one should test one’s code
• if you put something invalid in a ‘select’ list in Powershell it won’t necessarily error

I’m slowly getting to grips with Powershell.

# -----------------------------------------------------------------
# Script - Catalog-MP3s.ps1 #
# Gets mp3 tags from the mp3 file.
# -----------------------------------------------------------------
# Parameter is either a directory or an individual mp3 Param ( $P_DIR_OR_MP3 )  
# --------------------------
# Function: get-mp3info
# The function gets the tags
# for the specified mp3
# --------------------------
Function get-mp3info ($P_MP3)
{
     # Get the tages into a variable
     $TAGS = [TagLib.File]::Create("$P_MP3")
     $TAGS_OBJECT = @{Filename = $TAGS.Name;

     Bitrate = $TAGS.Properties.AudioBitRate;
     Artist = $TAGS.tag.FirstArtist;
     Title =  $TAGS.tag.Title;
     Genre = $TAGS.tag.FirstGenre;
     LP = $TAGS.tag.Album}

     # Output as an object
     New-Object PSObject -Property $TAGS_OBJECT
}

     # --------------------------
     # Main body
     # --------------------------
     # Store the location of the taglib dll
     $TAGLIB="C:\Users\Matt\Downloads\taglib-sharp-2.1.0.0-windows\taglib-sharp-2.1.0.0-windows\Libraries\tag\lib-sharp.dll"

     # Load the DLL
     [System.Reflection.Assembly]::LoadFile($Taglib)

     # Get a list of all the MP3s under the specified folder
     # - exclude anything that's not an mp3
     # - Fullname contains the full path and filename
     $MP3_LIST = gci -recurse Filesystem::$DIR_OR_MP3 |
                     where {$_.Extension -eq '.mp3'} |
                     select FullName

     # Walk through the list, and output the tags
     foreach ($MP3 in $MP3_LIST)
     {
          get-mp3info $MP3.Fullname
     }

The heavily borrows from:

• PowerShell - Automatically organizing your mp3-collection - Speaking of which… - Site Home - MSDN Blogs
• Scripting Mp3 Tags through PowerShell | PowerShell.nu