Execute a Powershell script from inside a stored procedure?

Question

Execute a Powershell script from inside a stored procedure?

Gail Wanabee

SSCrazy Eights

Points: 8471
More actions
November 30, 2016 at 2:50 pm

#401138

Can this be done? If so, please point me in the right direction so I can learn how to do it.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

You would have to make a call to xp_cmdshell. It's going to be more than a little sloppy. Why not reverse it. Create a Powershell command that calls to the procs needed? Another option, put the PowerShell into an Agent Job. You can call agent jobs from T-SQL directly.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 2

Grant, this needs to implemented in a startup stored procedure in the master database, before SQL Server has actually started.

What I plan to do is to create a RAM Disk using a PowerShell command, then have SQL Server create the TempDB files in the RAM Disk.

The rationale for this is detailed in another post: http://www.sqlservercentral.com/Forums/Topic1839110-3411-1.aspx

Is what I'm proposing to do possible? I think it is.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Grant Fritchey (12/1/2016)
It's going to be more than a little sloppy.

Not sure why anyone would say such a thing. What kind of "slop" are you taking about?

--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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Gail Wanabee (12/1/2016)
Grant, this needs to implemented in a startup stored procedure in the master database, before SQL Server has actually started.
What I plan to do is to create a RAM Disk using a PowerShell command, then have SQL Server create the TempDB files in the RAM Disk.
The rationale for this is detailed in another post: http://www.sqlservercentral.com/Forums/Topic1839110-3411-1.aspx
Is what I'm proposing to do possible? I think it is.

I wouldn't do it there. I'd do it as a part of the startup of the OS itself.

--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)

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 5

Jeff, I agree, I just didn't know you could do that. I'm not a Windows Server administrator.

On a complete server reboot, can you recommend a way to have a Powershell script execute before SQL Server starts?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Gail Wanabee (12/1/2016)
Jeff, I agree, I just didn't know you could do that. I'm not a Windows Server administrator.
On a complete server reboot, can you recommend a way to have a Powershell script execute before SQL Server starts?

I guess I don't understand... For Ram Disk, it's a normal Windows installation.. There's no need for Powershell or anything else. Ram Disk gets installed like any application.

--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)

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 7

Jeff Moden (12/1/2016)
Grant Fritchey (12/1/2016)
It's going to be more than a little sloppy.
Not sure why anyone would say such a thing. What kind of "slop" are you taking about?

Running Posh through the command shell. Error trapping, all sorts of stuff gets weird. I'm not against it. I'm just nervous about it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 8

Jeff Moden (12/1/2016)
Gail Wanabee (12/1/2016)
Jeff, I agree, I just didn't know you could do that. I'm not a Windows Server administrator.
On a complete server reboot, can you recommend a way to have a Powershell script execute before SQL Server starts?
I guess I don't understand... For Ram Disk, it's a normal Windows installation.. There's no need for Powershell or anything else. Ram Disk gets installed like any application.

-----------

I read that RAM Disk was a software component of Windows NT until Windows Server 2003 at which point it was removed from the product. If that's true, we'll have to use a 3rd party product if we want a RAM Disk.

I'm just starting to evaluate 3rd party RAM Disk products. If I find a good one that meets our requirements, I'll install it like any other software product. Some installed products don't execute unless you ask them to execute, like Excel. Others start and execute when Windows starts, like SQL Server (if you have it configured to do that).

I'm not making any assumptions about what is required to start a RAM Disk software product. I'm considering the possibility that maybe it needs to be started manually by some command, issued somewhere, by something, every time Windows Server is restarted.

For SQL Server to create all the TempDB files in the RAM Disk on startup, the software that supports the RAM Disk would need to be running on the server and the RAM Disk would need to exist as a "disk drive" before SQL Server started, otherwise, the creation of the TempDB database would fail and SQL Server would fail to start.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Gail Wanabee (12/2/2016)
Jeff Moden (12/1/2016)
Gail Wanabee (12/1/2016)
Jeff, I agree, I just didn't know you could do that. I'm not a Windows Server administrator.
On a complete server reboot, can you recommend a way to have a Powershell script execute before SQL Server starts?
I guess I don't understand... For Ram Disk, it's a normal Windows installation.. There's no need for Powershell or anything else. Ram Disk gets installed like any application.
-----------
I read that RAM Disk was a software component of Windows NT until Windows Server 2003 at which point it was removed from the product. If that's true, we'll have to use a 3rd party product if we want a RAM Disk.
I'm just starting to evaluate 3rd party RAM Disk products. If I find a good one that meets our requirements, I'll install it like any other software product. Some installed products don't execute unless you ask them to execute, like Excel. Others start and execute when Windows starts, like SQL Server (if you have it configured to do that).
I'm not making any assumptions about what is required to start a RAM Disk software product. I'm considering the possibility that maybe it needs to be started manually by some command, issued somewhere, by something, every time Windows Server is restarted.
For SQL Server to create all the TempDB files in the RAM Disk on startup, the software that supports the RAM Disk would need to be running on the server and the RAM Disk would need to exist as a "disk drive" before SQL Server started, otherwise, the creation of the TempDB database would fail and SQL Server would fail to start.

I wasn't talking about the Windows version of Ram Disk. I thought you were talking about one of the 3rs party versions. Either way, it should NOT be a part of the SQL Server startup. It should either be permanently installed manually or as a part of the OS startup for Windows. You shouldn't need to do anything in the SQL Server startup process because the ram disk, whatever flavor or product you've decided to use, should already be active and have a drive letter available for TempDB to use.

--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)