SQL Server patch management tools or a way to do it with powershell / c# or something?

sqlagent007
sqlagent007 used Ask the Experts™
on
I am looking to manage all the patches on my SQL Server instances, and we don't have WSUS. Also, I need a way to make sure our local dev environment is at the same windows and SQL server patch level as the prod environment hosted in the data center (separate networks).

I would love it if there was a way to log all the updates installed per SQL instance in a database of some kind so I can get reports. I am not opposed to a roll your own solution, however I don't want to roll my own using powershell, then BASH gets to be the preferred method of Windows or Python or something and then I have to roll this solution again.

I have been hearing that windows server will be supporting bash script, I assume when this happens, most people will start using this. Also, using powershell to administer things has been great, however, I am not sure how I would manage the code of a powershell solution like this.

In a perfect world, I would love the ability to have a tool go get the updates and install them in DEV. We can then test and once we feel everything is good, we can just have a script that will execute the exact same update installation in PROD. Using windows updates has not worked for us in the past because at times we have to skip a certain update or by the time can roll to PROD there are newer updates that have not been tested. Also with windows update, I don't know how to to run a query where I can show all the updates that have been installed. I know I can get this list via control panel, but I need to be ale to give the results to our Information Security team.

Has anybody dealt with this? If so..what did you do to solve the problem?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Has anybody dealt with this? If so..what did you do to solve the problem?
Yes. With WSUS.
I realize that you don't have WSUS but isn't an option to have it?
System Center might have something that can Report on Windows and SQL Server versions but I'm not sure.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Configuration Manager has an Inventory feature.
sqlagent007Technology professional

Author

Commented:
Do I need System Center first, then Configuration Manager is a part of that?

One of my concerns with the Microsoft SCCM and stuff is that we need a license for each and every device. Sometimes we need to spin up a dev environment in a hurry, and honestly I don't what to have to go back and forth on license every time we spin up a stack then delete it.

Do the solutions mentioned by Vitor Montalvão allow the flexibility to just say here is a new SQL Server, and start managing with out having to procure a license every single time?

Also what is the cost for something like this?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Do I need System Center first, then Configuration Manager is a part of that?
No. You can have Configuration Manager only. System Center is the complete solution.
Unfortunaly I don't know how's the licensing since I'm working for a very big company and our contract with Microsoft allows us unlimited installation of their products.
Systems Administrator and Solutions Architect
Commented:
WSUS is the best solution for you.

Reasons explained:
It is free.
No license issues.
One of the purpose of Computer groups on WSUS is for Update Testing.
It doesnt matter if there are new updates untested, it is up to you to approve them for dev or for prod or for both, You have all the power to decide what to do.
Update files are downloaded only once.
You can schedule the installation of new updates for unattendant deployment on several computers simultaneously with auto-rebooting if needed.
You can get the native WSUS reports for patching compliance or obtain reports from individual computer using WMI. Also you can use PowerShell to generate fully detailed reports on computers and updates.
You dont need to wait until BASH get implemented to use these features, it can be done right now.

You can start here with the deployment scenarios https://technet.microsoft.com/en-us/library/cc708628%28v=ws.10%29.aspx

I guess that you will only need a SINGLE WSUS server with two custom Computer Groups: Dev, and Prod. Initially you only approve needed updates for DEV computers, then test them by running the update procedure on the client computers. If everything is OK, you can re-approve all tested updates this time for All computers, that will include PROD group as well.

For more details just read online or download the Deployment Guide for WSUS 3.0, and in this EE article you can find a step-by-step guide for Deploying WSUS 3.0 on Windows 2012.

I think that if you get to know better with WSUS you will see how easy it could be to implement solving your patching requeriments.
sqlagent007Technology professional

Author

Commented:
Thanks experts!!! This really helps

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial