Here’s a simple Powershell snippet to check the mirroring status on your SQL Server instances. ?View Code POWERSHELL# Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;   # Servers to check $sqlservers = @("server1", "server2", "server3"); foreach($server in $sqlservers) { $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server; # Get mirrored databases $databases = $srv.Databases | Where-Object {$_.IsMirroringEnabled -eq $true}; Write-Host [...]

Here’s a little Powershell function I’m using to check the Event Logs and SQL Server Error Logs in one easy swoop; ?View Code POWERSHELLfunction Get-ServerErrors { # Server to check & hours back. Will only support default sql instances # Could add a third param for instance and modify script where appropriate if needed param ($server, [...]

We don’t use SSRS much at my workplace but its usage is slowly creeping up. I realised that none of us are keeping an eye on the few subscriptions we have set-up. So I decided to do something about that. Here’s a bit of Powershell code that uses the SSRS Web Service to pull out [...]

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 [...]

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 [...]

Disk alignment has been well discussed on the web and the methods to check this always seem to use wmic or DISKPART. I’ve always loathed wmi so here’s a few lines of Powershell that achieves the same thing; ?View Code POWERSHELL$sqlserver = "sqlinstance"; # Get disk partitions $partitions = Get-WmiObject -ComputerName $sqlserver -Class Win32_DiskPartition; $partitions [...]

I’ve been reading a bit about VLFs (Virtual Log Files) this week. I’ve found quite a few interesting links, especially this one, informing us that there’s such a thing as too few or too many VLFs. We can view details about VLFs using the DBCC LOGINFO TSQL command. This only works against the current database [...]

The PWDCOMPARE function is really handy for further securing your SQL Servers by checking for a range of blank or common passwords. If you google for common password list you’ll probably recognise several if you’ve been working in IT for any reasonable amount of time. Fortunately you can use this function, in conjunction with the [...]

I got this error when attempting to expand the msdb ssis package store on my recently built test cluster; "Failed to retrieve data for this request. Additional information: The SQL Server instance specified in SSIS service configuration is not present or available. This might occur when there is no default instance of SQL Server on [...]

I wanted to find a way of programatically identifying the active node of a SQL Server Cluster. I found this post that demonstrated how to do it with TSQL. As I love Powershell so much here’s another method to do it; ?View Code POWERSHELL# Set cluster name $cluster_name = "ClusterName"; # Load SMO extension [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") [...]