Project ideas for DBA to advance in powershell

Question

Project ideas for DBA to advance in powershell

ebooklub

Hall of Fame

Points: 3905
More actions
March 2, 2016 at 10:50 am

#316484

Hi,
I need forum brainstorm with powershell project ideas based on limitation of working environment
Environment
1. PS Version 2 (will not move to any other version soon)
2.my account Not a local admin of Workstation
3.my account is DBA/local admin with Remote access to SQL servers 2008-2014 where jobs , alert, backups, audits and monitoring already managed by powershell
or other tools
4.my pervious 15 + years experience DEV and PROD SQL DBA, but I started using PowerShell few month ago 🙂
I used books bellow for my study
"Learn Windows PowerShell in a Month of Lunches - D. Jones (Manning, 2011) "
"SQL Server 2012 with PowerShell V3 Cookbook - D. Santos (Packt, 2012)"
I went to through multiple Powershell blogs and post, but have hard time to find ideas for my own project based on existing environment and limitations
Any help or links highly appreciated

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

ebooklub (3/2/2016)
Hi,
Any help or links highly appreciated

My suggestion would be to first learn how to do the job of a DBA without the use of Powershell. If you can't do it using the native tools, then you don't have the necessary understanding to do it right in Powershell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ebooklub Hall of Fame Points: 3905 More actions · Answer 2

Hi Jeff, I will edit my post 🙂 , yes I know how use native tools (working as DBA for 15 years), but as I said need to progress in Powershell

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Did you see this thread?

http://www.sqlservercentral.com/Forums/Topic1765816-2799-1.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

Find all backup files for a db

delete old backup files (delete .baks older than the last 2)

copy backup files to new folder, based on criteria.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Steve Jones - SSC Editor (3/2/2016)
Find all backup files for a db
delete old backup files (delete .baks older than the last 2)
copy backup files to new folder, based on criteria.

Which can all be done using T-SQL. I understand those make good learning tools for Powershell but if you allow Powershell for those things, you're making life difficult for yourself.

Now, if you want to do something cool with Powershell, use it to iterate through servers calling WMI in the process to determine the status of all drive letters for size, space used, percent free, volume name, machine name, disk type, and the status of the IsDirty bit. Heh... that's one of the articles I'm working on. I suspect people won't like it though because I use a table to keep all the server names, T-SQL to create the PowerShell/WMI commands, xp_CmdShell to stitch it all together, T-SQL/XML to create the colorized report grid without going anywhere near SSRS, and a call to the Black Arts CDOSYS object using the automation procs so that you can implement the code without necessarily having to enable email on any given server. It also captures history and provides a method to predict when a disk may run out of space as well as providing a "Removal Media" finder.

I figured if I was going to offend anyone's sensibilities, I'd go for broke and try to offend everyone's. :hehe: I think the only things missing is the use of undocumented stored procedures and ad hoc query usage. Allen White hates me. That's an official endorsement if I ever heard one. 😛

I've been using it in production for about 3 years. The auditors love it. ISO, SOC2, etc, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

Certainly some of thsoe can be done with T-SQL, but they fit better, and I'd argue most file operations are better handled in PoSh. Better error handling and easier to parse for various items you might want to deal with.

Hitting WMI is a good one, looking at the registry, there certainly are some things outside of SQL Server made easier with PoSh. Anything inside the server, configs, settings, those are better in T-SQL.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

Steve Jones - SSC Editor (3/2/2016)
Certainly some of thsoe can be done with T-SQL, but they fit better, and I'd argue most file operations are better handled in PoSh. Better error handling and easier to parse for various items you might want to deal with.
Hitting WMI is a good one, looking at the registry, there certainly are some things outside of SQL Server made easier with PoSh.

Again, we'll have to agree to disagree. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gary Varga SSC Guru Points: 82166 More actions · Answer 8

Jeff Moden (3/2/2016)
...that's one of the articles I'm working on...

Looking forward to it 🙂

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Gazareth One Orange Chip Points: 27737 More actions · Answer 9

Jeff Moden (3/2/2016)
Steve Jones - SSC Editor (3/2/2016)
Find all backup files for a db
delete old backup files (delete .baks older than the last 2)
copy backup files to new folder, based on criteria.
Which can all be done using T-SQL. I understand those make good learning tools for Powershell but if you allow Powershell for those things, you're making life difficult for yourself.
Now, if you want to do something cool with Powershell, use it to iterate through servers calling WMI in the process to determine the status of all drive letters for size, space used, percent free, volume name, machine name, disk type, and the status of the IsDirty bit. Heh... that's one of the articles I'm working on. I suspect people won't like it though because I use a table to keep all the server names, T-SQL to create the PowerShell/WMI commands, xp_CmdShell to stitch it all together, T-SQL/XML to create the colorized report grid without going anywhere near SSRS, and a call to the Black Arts CDOSYS object using the automation procs so that you can implement the code without necessarily having to enable email on any given server. It also captures history and provides a method to predict when a disk may run out of space as well as providing a "Removal Media" finder.
I figured if I was going to offend anyone's sensibilities, I'd go for broke and try to offend everyone's. :hehe: I think the only things missing is the use of undocumented stored procedures and ad hoc query usage. Allen White hates me. That's an official endorsement if I ever heard one. 😛
I've been using it in production for about 3 years. The auditors love it. ISO, SOC2, etc, etc.

I have something similar, it's mostly WMI & SMO. T-SQL (embedded) is only used to collect index usage & missing indexes stats.

Even managed to write something to grab the last good checkdb run.

Zero code required on the remote servers.

SSRS is used to display much of the collected data. Currently working on bringing proc & query execution stats to the party.

Lowell SSC Guru Points: 323518 More actions · Answer 10

i did a big Powershell documentation initiative.

this script from awhile ago started it:

Powershell script to script out jobs, databases, users, linked servers, logins, roles, alerts, etc from a list of servers

using that as a baseline, i added the scripting all database objects(tables/views/etc);

after that i added scripts to extract all SSRS Reports and objects from the ReportServer database;

then I've got a pair that extract SSIS packages from either the SSIS catalog, or the SSISDB if it was a 2012+ server.

I've got a couple still on my to-do list: extracting master keys, credentials and SSRS encryption keys via powershell;

things i also already do in TSQL, that i could do in powershell would be scripting out traces,extended events, database mail settings, and linked servers(in the way i like it, not the way the above link does it).

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gazareth One Orange Chip Points: 27737 More actions · Answer 11

Steve Jones - SSC Editor (3/2/2016)
Find all backup files for a db
delete old backup files (delete .baks older than the last 2)
copy backup files to new folder, based on criteria.

Script to restore those backups to a new server with options for most recent or point in time.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 12

One of my ongoing projects is maintaining a PowerShell script to automate the A - Z installation of SQL Server on freshly provisioned virtual machine environments. It first does a silent install and then follows that up with about 100 post-installation steps including: applying service packs, setting service accounts / passwords (credentials automatically retreived from Thycotic SecretServer via webservice calls), setting of default instance properties, creating logins, roles, permissions, maintenace plans, MSDTC configuration, dbmail, alerts, extended event audits, file shares, backup schedules, sending email notitications to network operations, Octopus Deployment integration, etc. etc.

It's always a work in progress and stretching the boundaries of my PowerShell knowledge.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Lowell (3/3/2016)
i did a big Powershell documentation initiative.
this script from awhile ago started it:
Powershell script to script out jobs, databases, users, linked servers, logins, roles, alerts, etc from a list of servers
using that as a baseline, i added the scripting all database objects(tables/views/etc);
after that i added scripts to extract all SSRS Reports and objects from the ReportServer database;
then I've got a pair that extract SSIS packages from either the SSIS catalog, or the SSISDB if it was a 2012+ server.
I've got a couple still on my to-do list: extracting master keys, credentials and SSRS encryption keys via powershell;
things i also already do in TSQL, that i could do in powershell would be scripting out traces,extended events, database mail settings, and linked servers(in the way i like it, not the way the above link does it).

That sounds like an awesome usage of PowerShell. Any chance of you posting yours?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)