SQL patching solution and automation

Question

SQL patching solution and automation

Saran

Right there with Babe

Points: 760
More actions
March 9, 2020 at 5:50 pm

#3732577

Hi All,
Would like to get some suggestion of SQL patching solution and automation for multiple servers. I have 150+ SQL with each version 2012,2014,2016 & 2017. We do not have cost to buy patching tool, is there any easy and good solution to automate this like WSUS, PS etc.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

One option with Powershell is to use dbatools and the cmdlet Update-DbaInstance. You can find more information on that cmdlet in this link: Update-DbaInstance

An example of how to use this and have some central location for the patches can be found in this post:

Patching Multiple SQL Servers With Powershell and dbatools

Sue

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

As a bit of a side bar, I'm always amazed when someone says they have a shedload of servers but no money to actually support them. It just doesn't make any sense.

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

Saran Right there with Babe Points: 760 More actions · Answer 3

Thanks Sue.

Yes Jeff, the same I have asked our management, they want all in zero cost. We are small 3 person team.

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

Saran wrote:

Thanks Sue.
Yes Jeff, the same I have asked our management, they want all in zero cost. We are small 3 person team.

Heh... you're also a 150+ server team. 😉

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

Zousan Grasshopper Points: 13 More actions · Answer 5

If you think its expensive to hire a professional to do the job, wait until you hire an amateur.

Saran Right there with Babe Points: 760 More actions · Answer 6

Sue_H wrote:

One option with Powershell is to use dbatools and the cmdlet Update-DbaInstance. You can find more information on that cmdlet in this link: Update-DbaInstance
An example of how to use this and have some central location for the patches can be found in this post:
Patching Multiple SQL Servers With Powershell and dbatools
Sue

Hi Sue,

I am getting this error. I am admin of OS.

WARNING: [17:32:34][Update-DbaInstance] Failed to get reboot status from servername| Specified credentials are known to not work!

Sue_H SSC Guru Points: 90860 More actions · Answer 7

Saran wrote:

Sue_H wrote:
One option with Powershell is to use dbatools and the cmdlet Update-DbaInstance. You can find more information on that cmdlet in this link: Update-DbaInstance
An example of how to use this and have some central location for the patches can be found in this post:
Patching Multiple SQL Servers With Powershell and dbatools
Sue
Hi Sue,
I am getting this error. I am admin of OS.
WARNING: [17:32:34][Update-DbaInstance] Failed to get reboot status from servername| Specified credentials are known to not work!

The warning is due to running the commands using credentials that don't have permissions to reboot after the patching if needed. Wouldn't matter what you use to patch in that case, you would need to use a login that can reboot whichever patches require a reboot.

Sue

Saran Right there with Babe Points: 760 More actions · Answer 8

Thanks, I have admin access to the remote server, do I need to add in any particular group of local policy.

Saran Right there with Babe Points: 760 More actions · Answer 9

Just used other option -Credential $psCred -Verbose

[Update-DbaInstance] Explicit -Credential might be required when running agains remote hosts and -Path is a network folder

WARNING: [20:57:22][Update-DbaInstance] Failed to enumerate files in -Path | Access is denied

The path // is having permission to everyone with full access.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Aren't those supposed to be backslashes instead of forward slashes? Could that be the remaining issue?

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

Saran Right there with Babe Points: 760 More actions · Answer 11

Saran

Right there with Babe

Points: 760

March 13, 2020 at 10:02 am

#3733963

Sorry that was typo \\.