Nottingham based SQL Server DBA who dabbles with many other technologies. 15+ years of experience with databases and still keen to learn and explore.
T-SQL Tuesday time again. This month SQLChow (b|t) is has set us the homework assignment to talk about SQL Sever Automation. So here’s my take on the subject:
Every job has those mundane repetitive tasks than you HAVE to do, day in day out.
As SQL Server DBAs we have a particularly long list of things that we have to do to make sure our databases are working properly, and also to ensure that when they’re not we can fix them quickly enough that we’re not looking for a new job!
I spent a fair chunk of the Christmas break ripping yet more of my CD collection to mp3s so I can shove them on the home NAS, put the CDs in the loft and reclaim a load of space. Ripping them was a fairly predictably repetitive job of shoving CDs into the drive, waiting for the FreeDB lookup to run (or fail, which meant a bit of typing) and then hitting the go button.
Not the most fun of jobs, but still a lot less work that when I digitised some large parts of my vinyl collection a few years ago After a couple of days I had a nice collection of mp3s all sat there on a disk ready to be imported into the main collection folder.
Just one job remained, getting the Album art together. This is always a fun job of getting the right image. I’ve been using Album Art Downloader for this for a while, as you can set plenty of options to remove most of the effort. But, it doesn’t embed the images into the files. Which as an iDevice user I want so I can have the pictures transfer across when I sync. This got me thinking. I’ve got a windows file system with a nice hierarchical folder structure containing files, and I want to perform an action on each of the files in that structure.
Now if only there was some sort of Windows scripting language that was good at this sort of thing……..
Not resolutions, oh no! A resolution can be broken by one relapse, goals are something I can keep working on even if there’s a setback!
Just putting them here, as once they’re shared it’s very hard to ignore them.
SQL Server related ones:
1) Get more inventive with BI work, and get involved in some Big Data technologies
2) Automate even more of my workload
3) Get really down and dirty with SQL Server Clustering and AlwaysOn
4) Present some open access training courses.
5) Speak at more SQL community events, maybe overseas
And because it’s not all about work, here’s some Personal ones:
1) Keep on at my OU Maths Degree
2) Once I find out about what I can do with my knee, target a 100km audax or a marathon
3) Read more fiction and non-technical non-fiction
4) Pick my photography back up a bit
5) Find and settle into a new home
What are your goals and hopes for 2014? Feel free to share them with us in the comments.
A quick question on twitter from William Durkin (b|t) reminded me that I hadn’t covered SQL Server multistriped backupsets during my 31 Days of SQL Server Backups and Restores with PowerShell series, so without further ado I’m going to correct that:
Just a quicky this time.
I was called in to install a SQL Server instance on an already built and populated Windows Cluster. Unfortunately as the system had grown, noone had kept an accurate record of which drive letters had been used. So a quick drop into PowerShell and the fun of WMI (as there isn’t a native PowerShell way to get this, yet), and I had this little one liner
Get-WmiObject -Namespace root\MSCluster MSCluster_DiskPartition -ComputerName "Cluster.contoso.com" -Authentication PacketPrivacy `
| Format-List Path, VolumeLabel
(In this case ComputerName is the cluster name)
And I had a nice list of all the Drives attached to the cluster with their Path and Volume Label. This also returns all the mountpoint mounted volumes as well, which may cloud things on a complex setup. These can be filtered out quite easily, and the list ordered to make it easier to spot the gaps like so:
Get-WmiObject -Namespace root\MSCluster MSCluster_DiskPartition -ComputerName "Cluster.contoso.com" -Authentication PacketPrivacy `
| Format-List Path, VolumeLabel `
| Where-Object {$_.Path -like "?:"} `
| Sort-Object $_.Path
| Format-List Path, VolumeLabel
While this is really handy, it’s still no real substitute for properly documenting your cluster while it’s being built. Please for your own sanity, and mine if I ever get called out to it, document all your drives, saves relying on random PowerShell scripts you find lying around on the Internet!
Here are the Presentation and the demo scripts I was using during my SQL Server Backups and Restores with PowerShell sessions at the Birmingham and Norwich SQLRelay events.
Please make use of them. And if you have any queries (or find any problems) please get in touch via email or twitter.
A common question that comes up during my PowerShell Restore presentation is on how to delete your old SQL Server backups after a certain period of time. This is especially important for those DBAs who are looking after SQL Express installs who don’t have the luxury of SQL Server Agent and Maintenance Wizards to configure it for them.
Ever wonder how some people always seem to know the answer in SQL Server? Or seem to come up with it with a minimum of digging around?
Well, they’re not necessarily cleverer than you, it could just be that they’re better at playing with SQL Server than you are. Knowing how to quickly and simply build up a test scenario means whenever you want to try something out, or prove a hunch then you can be most of the way to proving something while others are still Googling and Binging away. Or if you’ve found something on the Internet, you can check it does actually do what it claims to. Because as Abraham Lincoln once said:
I’m sure I’m not alone amongst SQL Server bloggers in starting to write a new post in a fit of great enthusiasm, but eventually getting bogged down in a mire due to dithering about whether:
Having helped various people over the year proofread documents (My wife’s MA dissertation in English Literature being a highlight) I realise how important it can be to have another pair of eyes look over your work.
That pair of eyes are even better when they can offer usable criticism. So when I proofread my wife’s dissertation I could spot obvious spulling mistkes, but the chances of me spotting a problem when discussing the actions of the Ranting Poets of the early 80’s was nigh on impossible. And vice versa, whilst an expert on Poetry and English Literature, Charlotte isn’t likely to pick up on my making a mistake whilst discussing PowerShell backups.
So, assuming that there are other SQL Server bloggers out there in the same boat I was considering setting up a mutual help group? We’d offer to help proofread and copy edit each others future posts, offering advice, criticism (Constructive!) and a potential soundboard.
If you are interested, drop a comment below. Depending on the uptake we might need to think of a clever way of managing this, but hey, we’re nothing if not inventive!
I’m happy to announce that I’ll be speaking at 2 of the SQL Relay 2013R2 events happening in November.
If you’ve not been to a SQL Relay event before, these are FREE all day SQL Server training events featuring some of the top SQL Server experts from around the world (and me!). This edition is visiting 10 different locations around the UK over 2 weeks, so there’s bound to be one near you.
I’ll be speaking at Birmingham on 14th November and Norwich on 27th November, presenting on PowerShell for SQL Server backups and restores.
In Birmingham you’ll also get Denny Cherry speaking on Hekaton, the new in memory SQL Server objects coming in SQL Server 2014
And in Norwich, Klaus Aschenbrenner will be presenting. While he’s not announced his topic yet, Klaus is always a great presenter so it’ll be a good ‘un.
Some of the events are filling up fast, so register quickly to make sure of a place: Register for SQL Relay
Hope to see some of you there. Please come up and say Hi if you do.
Powered by WordPress & Theme by Anders Norén