So it’s been a while (AGAIN!), i’m such a slacker, sorry..
Last night we had to do an update for a specific version of the EHR software we host at work. While normally updates are fine, the way this specific update had to be completed resulted in the following for over 70 separate databases.
1) Find all of the instances of where the plugin is installed (each database)
2) Download update (takes about a minute)3) Uninstall the old version of the plugin
4) Install the new version, so that it runs against the MSSQL database.
While this process on paper seems easy/quick…
To do 70 database updates, with 3 guys working on updating plugins and servers (think terminal servers), it took us about an hour and a half, so you can imagine how long it would take for one person to do everything. Oh, and did I mention this plugin was the LEAST used one?! Yup, the number one plugin has roughly 130 databases in usage… eep.
I have in the past attempted to contact the vendor, to see if there was a way to automate the installation.
Of all of the steps posted above, I think I can circumvent the following..
-Downloading the plugin to the website..I can fix this by just downloading to one central location and just copy and pasting the contents to the website where it indicates what version you have, the only problem is that if in a specific version they fix verb-age in the database, that the SQL query that runs at the end fixes, we won’t ever know. Hence my previous comment, I had talked with the vendor to see if they could release the SQL scripts that were embedded in the cab file (I have tried using most known applications to open the cab file, but each time it goes to an exe that is compiled ._.), and when approached the vendor stated that because our setup is so unique, they would not help us out (You know, because being the premier Cloud platform for your product, being nationally recognized, doesn’t do it for you..), so i’m left with hoping that the SQL job is never really needed..
This leads me to my biggest hurdle, documentation.. While things since I took over have been better, there is still too much room for error. Example, how do I know if a new database is added, and if they have the plugins I need to update?!
That’s where my project today came into play. I noticed that my task list was low today, so I took the opportunity to create a nifty (my opinion) script that will take a comma delimited list of servers, and connect to each one, grab the available databases and query for the specific plugin that you are looking for.
Since I’m a PHP developer, and know NOTHING about proper VB coding, I went to google after having the direction I needed to go with the code, I went about finding a way to query the server to get a list of all of the servers.. To my delight, someone posted a very easy and nifty way to do just that, the Sysdatabase database contains everything I needed!
Here is the code I used to query that, using the given server.
' objects needed to connect to the SQL server
Set objConnection = CreateObject("ADODB.Connection")
' Open the connection to the SQL server.
objConnection.Open "Provider=SQLOLEDB.1;Data Source=" & ArrayOfServers(i) & ";Initial Catalog=Master;Uid=" & MSUser & ";Pwd=" & MSPwd & ""
' Select the list of databases on this server
Set objRecordset = objConnection.Execute("Select Name From SysDatabases")
So this will be called initially to grab the list of databases on the server, I then take this and plug it in to a loop statement where it will query the database, check if any rows are returned, if they are then I mark it. Typically if the plugin is active within the database we can find a row within the specific table.
As I loop through the databases and if a hit appears on the table, i’ll write to a text file placed off server so that I can view it when I need it.
So, with this script it will cut down on the potential for missing a database, the script will give all instances so that even if the plugin is removed, we can verify that the database gets the plugin or not.
the tl;dr, my AMAZING VB scripts win again </sarcasm>, but it will be nice to use this down the road to get an accurate count of databases..
My end goal with this project is to make it semi automated, where it can give an output of available plugins, what versions are where and update accordingly.. it’s a long ways away, but it will cut down on the human aspect of a giant upgrade process..