I am a developer learning PowerShell from Helmick and Snover over at Channel9 - I know the series is from 2013 but I’m enjoying it. It is giving me some ideas for cmdlets to manage my development environment - sql servers, dev environment folders, and random other items. I have an idea for simple storage and retrieval of settings and how to leverage them in custom cmdlets that manage my environments. What I am looking for here is some ideas of how to do that. I’m sure I have the option of using anything from CSVs to Excel to a SQL database, but at this time I don’t know the pros and cons nor really the capabilities of each with PowerShell. I’m not looking for anyone to write code for me, that is part of the fun. Some suggestions and breadcrumbs to help me along the path would be lovely.
Here is one example of what I am thinking about (cmelet names are loose ideas):
get-myservers > lists each of my sql server instances and the client database I currently have loaded to each
backup-myserver > backup all my development databases on specified server, using the associated client property stored to determine client subfolder in my sqlbackups directory
restore-clientdb > restore databases for specified client to specified server instance, writes back to storage location to associate server and client
It’s something I’d probably do in SQL Server. It’s central on your network, accessible from anywhere, and it’s designed to store data. It’s easily accessible from PowerShell (see, “The PowerShell Scripting and Toolmaking Book;” the free sample has a chapter on accessing SQL Server). You could certainly set up a database that let you store arbitrary key=value pairs, for example. Query everything with the “sqlserver” key and you’d have a list of your servers. That kind of store could also maintain other settings for you.
However, consider another approach, where perhaps you have SRV records in DNS for each SQL Server. That helps to abstract the physical server layer from your management surface - DNS is easy to keep updated, and you could even have each server register its own SRV records every time they start. Once you queries those SRV records, you could easily query each server for its production databases, for example.
I’m not a big fan of CSV for most data storage. It’s a very limited format. XML can get overly complicated, on the other hand. PowerShell’s not bad at JSON, though, which strikes a nice balance between the two - and in fact you could use a NoSQL store to store JSON documents that contained your settings.
It’s kinda whatever you’re comfortable with. Just keep maintainability and scalability in mind.
I’ll tell Jason and Snover you said nice things about them :).