You may encounter this error, about your storage networks, when setting up your Windows 2008 Failover Cluster. The following errors, Event ID 1129, will show up in Cluster Events… Cluster network ‘SAN1′ is partitioned. Some attached failover cluster nodes cannot communicate with each other over the network. The failover cluster was not able to determine [...]

I’m moving the backup jobs we run onto specific users and need to assign the db_backupoperator role to the user for each database. Very tedious to do in SSMS so here’s a quick script I knocked up. The script will assign a specific database role to all databases on a SQL Server. The user will [...]

It’s considered a bad practice  Not using Primary Keys and Clustered Indexes here’s a Powershell script that can make checking a database for this very easy. Just set the $server to the sql instance you want to check and $database as appropriate. ?View Code POWERSHELL# variables $server = "sqlinstance"; $database = "badDB"; # Load SMO [...]

I’ve been meaning to check out Maatkit for a while now. Today I had a reason to as one of our MySQL slaves got out of sync with the master. I’d heard about mk-table-sync, a tool that synchronizes tables, so I thought I’d give it a shot. As it turns out it’s this easy; mk-table-sync [...]

I’ve been looking at using Powershell in our release process to automate various things. I’ve used it to compare table data between databases and I’m now thinking of using it to validate our schema upgrades. I want to be easily alerted to any missing tables, columns, stored procedures and other objects. We have TFS at [...]

I was looking for a way to grab a list of processes running inside Sql Server but wasn’t having much luck. Essentially I wanted something like the Get-Process cmdlet but for Sql Server. Shortly after tweeting for help I stumbled across the EnumProcesses SMO method. Using this is quite simple. To list all Sql Server [...]

And I don’t mean writing it down in a word document, leaving it somewhere on the network, and then forgetting about it. How about keeping the documentation with the job? Microsoft provides us with a space for it… In this description field ideally I’d like to see A brief description of what the job does. [...]

Here’s just a quick Powershell script I knocked up to find out the server-level and database collations on multiple servers. Just specify each SQL Server in the array called $servers and you’re good to go. ?View Code POWERSHELL# Load SMO [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null; # Specify servers here $servers = @("localhost\sqlexpress", "localhost");   foreach($server in $servers) [...]

We’re currently busy reviewing some of the historical database design decisions taken in our organisation. We’ve noticed quite a lot of columns, that are specified as nullable, but do not actually contain any nulls. Obviously this fact makes the column a possible candidate for changing to NOT NULL. I wanted to make this task a [...]

In order to automate testing of backups it’s useful to be able to query backup files to access various bits of meta-data. We can do this with the RESTORE FILELISTONLY TSQL command. In the simplest format the command is as follows; ?View Code TSQLRESTORE FILELISTONLY FROM DISK = ‘c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\AdventureWorks_20110320.bak’; This doesn’t help [...]