Archive for 2011

TSQL to generate date lookup table data

I needed to generate a range of data about dates for a lookup table. There’s an elegant solution using a recursive cte that does the job; ?View Code TSQLWITH daysCte ( d ) AS ( SELECT CONVERT(DATETIME, ‘1 January 2011’) AS d — starting date UNION ALL SELECT DATEADD(D, 1, d) FROM daysCte WHERE DATEPART(yyyy, d) […]

MySQL Storage engine benchmarking

Here’s a stored procedure I use to perform some simple benchmarking of inserts for MySQL. It takes three parameters; p_table_type which should be set to the storage engine you wish to benchmark i.e. ‘MyISAM’, ‘InnoDB’, p_inserts; set this to the number of inserts to perform. p_autocommit; set the autocommit variable (relevant to InnoDB only) to […]

Testing a Failover Cluster with Powershell

Just a quick Powershell snippet that I’m going to use to run validation tests on one of my staging Failover Clusters during OOH. The script below will take some services offline, run the validation tests, before bringing the appropriate cluster groups back online. The report will be saved using the date as the name. To […]

Domain user password expiry with Powershell

I needed to figure out a method for producing alerts when a domain account is approaching the password reset date. Here it is in a few lines of Powershell… ?View Code POWERSHELL# Set name here $name = "Rhys Campbell"; $user = Get-ADUser -LDAPFilter "(Name=$name)" -Properties PasswordLastSet; $days = $(Get-ADDefaultDomainPasswordPolicy).MaxPasswordAge; $expires = $user.PasswordLastSet + $days; $days_left […]

The difference statistics can make

A few days ago a developer came to me with a query that was executing slowly on a staging server. On this server it took 16 long seconds to execute while on other servers it took about 1 second. I did a quick schema compare and found them to be identical. So I ran the […]