For PowerSchool 5.2.x (Oracle) on Windows ONLY.
This customization will help you automate your live server backups and your restores to a test server.
customscripts/cleanArray.bat:
This script is called by updateTestServer.bat automatically and does not need to be scheduled for regular use.
- What it does:
1. Cleans out the server array information
- Useful when:
1. Test environment is a single sever while the live environment is multiple server
2. Test environment is on a newer version of PowerSchool
3. Possibly (but not tested) if you are trying to update your live server array but did not take down the application nodes politely
customscripts/cleanArray.sql:
This script is called by cleanArray.bat automatically and does not need to be scheduled for regular use.
- What it does:
1. Contains the sql commands to clean out the Server instances
customscripts/pssrvrbackup.bat:
Scheduled for nightly use on the Live PowerSchool Database server.
- What it does:
1. Extracts a backup of the live data into a file called POWERSCHOOL-MON.DMP where MON represents Monday. The MON will change to TUE, WED, etc for each day the script is run.
2. Copies that data to the test server for storing (1 for each day of the week). Every Monday it will overwrite the previous Monday backup and repeat this for each day of the week.
3. Copies the backup file to the oradata\PSPRODDB\data_pump_dir as POWERSCHOOL.DMP. This file is overwritten each time and is the file used for importing.
customscripts/startPS.bat:
This script is called by updateTestServer.bat automatically and does not need to be scheduled for regular use.
- What it does:
1. Starts the tomcat service
2. Starts the PowerSchool.exe application
- Other possible uses:
1. Could be scheduled to run on startup to make sure that tomcat gets started. For this reason a 5 minute wait period was placed at the start of the script to allow time for Oracle services to start up
customscripts/stopPS.bat:
This script is called by updateTestServer.bat automatically and does not need to be scheduled for regular use.
- What it does:
1. Stops the tomcat service
2. Stops the PowerSchool.exe application
customscripts/updateTestServer.bat:
Scheduled for regular use on the Test PowerSchool Database server.
- What it does:
1. Runs the stopPS.bat script
2. Imports data from POWERSCHOOL.DMP
3. Runs the cleanArray.bat script
4. Runs the startPS.bat script
V 1.0 (09/11/2008)
- Initial Release
V 1.1 (09/22/2008)
- Fixed problem with the backup script. Version 1.0 had a issue that prevented the backups from running after 7 days.
V 1.2 (05/21/2009)
- Added calls to scripts that turn off archiving and turn it back on when everything is done. Thanks to agutch for the information.
- Only updateTestServer.bat has been changed
Comments
Going from an array on live side to a single test server
Hi Guys,
I spoke with Brain a few weeks ago about how my test server runs fine after the initial install, but when I import a dmp file from my live data server over to the test server via the updatetestserver.bat file from this site, it now thinks it's in an array and I cannot connect to it via a web browser. I followed Brian's instructions on https://powersource.pearsonschoolsystems.com/thread/856?2839 and saved the default values and exported the webserver config after the initial install and I verified I could login to Powerschool via a web browser. Looking at the webserver config, the VirtualServerHostNameValue is the test server's IP address. However, after the update, the VirtualServerHostNameValue is now the load balancer's IP Address. Before I launched the powerschool.exe file (I remed out the line in the bat file that restarts powerschool), I used the Oracle SQL Developer program to confirm that the server_config and the server_instance tables were both indeed truncated. I asked Brian about the server array values in the live server's Prefs table, but he said that it should not matter according to Pearson documentation. If the server_config and server_instance tables were truncated and the array values in the Prefs table do not matter, where is the server pulling the load balancer's IP addres and the server array setting? And why is it overwriting my WebServerConfig.xml file? Any help would be greatly appreciated.
Thanks,
Rob
Hi Guys, Out of curiosity, I
Hi Guys,
Out of curiosity, I did the following and now I seem to be up and running on the test server with the data from the live server:
First, I shut down the powerschool server.
Next, I copied my original webserver config file back to powerschoolpremier\data\config, overwriting the bad config file.
Then I used the Oracle SQL Developer program and did the following:
In the server_instance table, I changed the Host_Name field from the load balancer IP address to the test server IP address.
In the server_config table, I changed the Config_Value field for the com.apple.powerschool.webserver.hostname record from the load balancer IP address to the test server IP address.
In the Prefs table I deleted the CLST_VirtualHost and EZSU_DestIPForSource records.
In the Prefs table I changed the value of the enableserverarray record from 1 to 0.
In the Prefs table I changed the value of the localipaddr record from load balancer IP address to the test server IP address.
I saved all the changes and then I restarted the powerschool server and it started correctly, it does not think it is in a server array, it is using the correct IP number and the hostfile is correct.
It seems to me that the server does indeed use the values from the Prefs table because the server tables were blank before I did the inital startup of powerschool after I did the database import. But after the first startup, it was using all the wrong info, so it had to come from somewhere. And since I corrected/deleted the array info from all those tables (and copied back my original webserver config file), the server has now started correctly and I can log into PowerSchool and see all the student data. Hope this info helps someone.
Thanks,
Rob
Test System has App Server and separate DB Server
I would like to run your updatetestserver.bat on our Test DB Server since that is where I copied powerschool.dmp to, but how can I tell it to run stopPS.bat and startPS.bat on a different server - our App server - in the same script?
Any help would be appreciated.
Mac Version?
Rosemary Horn
The American School of The Hague
Is there a chance of a Mac version of this Automatic Test Server script any time soon?
Re: Mac Version
Most of the contributors of this site use PC servers, so it's hard for us to make a mac version. I also believe that Macs prompt for passwords at specific tasks during importing which makes it that much more difficult.
Different DB Names
Is there anything to change if your live server DB has a different name than your test server DB? i.e. PSPRODDB (live server) vs. PSTESTDB (test server)?
Thanks,
Rob
re: Different DB Names
I agree with Brian and I'll add that I'd be interested to see if you find any negative side effects of having your test server set up this way. I had thought about it before and chose not to go this route as Pearson (then Apple still I think) recommended against it.
Jason Treadwell
Custom Solutions Specialist
jason@powerdatasolutions.org
www.powerdatasolutions.org
Re: Different DB Names
You would need to look at all the Test Server scripts and change any instance of PSPRODDB to PSTESTDB.
Brian Andle
Re: Different DB Names
You need to edit updateTestServer.bat and change the 2 locations where is says PSPRODDB. Also I noticed a mistake in the updateTestServer.bat file.
The "call %1:\oracle\scripts\stoparchiving.bat " should be "call %1:\oracle\scripts\stoparchiving.bat psproddb"
and
The "call %1:\oracle\scripts\startarchiving.bat " should be "call %1:\oracle\scripts\startarchiving.bat psproddb"
Since it's a Test Server you could really just stop Archiving and never start it again since you really don't need to FlashBack/Restore the test server.
Brian Andle
Re: Different DB Names
Hi Guys,
I ran the update with the modified updateTestServer.bat file and all is well except I had to change the WebServerConfig.xml file so that the website worked again. I made a copy of the good WebServerConfig.xml file off to the side in case I need it again. I will be running these scripts tonight to see how everything goes. If the web server gets screwed up again, I may just modify the startPS.bat file to copy to good WebServerConfig.xml file to the appropriate dir and then start Tomcat and PS. So far with today's data update, it looks like everything did go through even though the DB names are different. Thank you for this great tool as well as getting back to me on my question.
Thanks,
Rob
Re: Different DB Names
That's the first step I do once I setup a test server is to Export the Web Settings. As long as you have a WebServerConfig.xml (with Test Server Settings) file in your test servers \PowerSchoolPremier\data\config folder it will always import the correct test server settings on PS start.
If your Test Server doesn't have this file, once you get the Test Server all up and running from the PowerSchool App Select PowerSchool->Export Web Server Config. I do this once one my Prod server as well so that I have my SSL config stored properly if I need to rebuild it.
Brian Andle
6.0 support?
Has this script suite been blessed for use with 6.0.1.5 yet? I'm trying to implement it, but I keep getting locked out, even though both my production (dedicated Oracle server) and test (all-in-one server) boxes are running 6.0.1.5.
Looking forward to getting this going. At the very least, StopPS.bat is *very* useful, as now I can set a weekly reboot to apply any updates.
Thanks lots!
re: 6.0 support?
It should work fine with 6.0, with that said you need to make sure that the Test Server passwords match the production passwords.
Brian Andle
Got it
I basically ripped the guts out of each of the batch files, got rid of all the variable passing (I have everything on a single partition), and got it going!
Just curious - after each run, I have to do a bunch of things to make the sandbox set the way I like it. Is it possible to have a set of defaults, like setting the server to listen on port 80 instead of 8080?
Thanks again for this - I learned a lot about integrating PS with batch files, and have some other applications that can take advantage of the methods yo used!
Custom Screens
We have the Automatic Test Server backup running now, and it seems to work pretty well. However, is there a way to include the custom screens in the auto test server update? We can manually copy them over, but we just noticed after having to rebuild our test server that the custom screens don't copy over. Any Suggestions?
P.S. we are using this script with Powerschool 6, and we are noticing that the web config is being wiped out after the restore. does the fact that is is powerschool 6 have anything to do with it?
Not working correctly as of Tuesday night
This was working great for us until Tuesday on PowerSchool 6. Ever since then, it seems like something isn't going right with the database export, as I can just import the one from Monday night and everything's fine, but of course the data isn't up to date.
Edustructures remoted in and made some changes to our student locator on Tuesday. Is it possible that something changed in the SIF that could be causing this?
Basically, I really don't know where to begin trouble shooting this. Is there anyway to get a detailed log of the export/import process to find out where the monkeywrench is?
From Live to Test?
OK...I'm confused here. Am I missing how the file is moved from one server to the other? I see no place to set that up.
Kathy Davies
System Information Manager
Lamar County Schools, Ga.
re: From Live to Test?
The scripts require you to submit a location of a few folders for them to run. If you make the necessary folders shared between the live and test servers and tell the script how to get there it'll do the rest. The readme file should tell you where to enter the locations.
Jason Treadwell
Custom Solutions Specialist
jason@powerdatasolutions.org
www.powerdatasolutions.org
Well, of course. Silly
Well, of course. Silly me.
Thanks, Jason.
Kathy Davies
System Information Manager
Lamar County Schools, Ga.
AutoSend and AutoComm
I was wondering if anyone cleared out their AutoComm table? Or do you just run through and update all records to not be enabled? Also, I noticed an AutoSend table but I don't have anything in there. All of my AutoSend Jobs are in the AutoComm table. Is this true for everyone else?
Michael P. Moore
Student Information System Administrator
South Brunswick Public Schools
Archive Log generation
Because this script drops the PS and PS_MGMT schemas nightly, the archive logs on my test server reached their quota after a couple weeks of using this script. Archive logging also slows down the import operation. I suggest that users of this script either disable archive logging on their test server, or if you're like me and unwilling to do that, call %1:\oracle\scripts\stoparchiving.bat at the beginning of the script and startarchiving.bat at the end of it.
re: Archive Log generation
Thanks for that. I'll add it into the script.
Jason Treadwell
Custom Solutions Specialist
jason@powerdatasolutions.org
www.powerdatasolutions.org
I dont see where this was added.
Edit: now that I look at the date of your comment. I think I downloaded this before then sorry.
I wonder If this script will need to be altered for version 6. Wait and see I guess.
-Guy
version delta issues
When running different versions of PS, you can get an account lockout, if you're not careful.
Example - production server running 5.2.0.x, test server running 5.2.1.x. Backup runs fine, restore causes test server 4D module to die, logs show error ORA -28000.
Latest version of the Backup/Restore documentation covers the fix (Page 10 (Page 13 of the PDF))
Basically, you need to run PowerSchool Database Tools and change the password, then run sqlplus to unlock the PS and PS_MGMT accounts.
It'd be really cool if the script could recognize the version delta, otherwise both servers must be running 5.2.0 or 5.2.1.
Learned this the hard way. Luckily, it only hosed my test server. :(
re: version delta issues
That does stink. I will look into it but with all of my servers on 5.2.1 I'm not sure how successful it'll be. I'm also looking into a possible solution to the issue with Oracle Jobs as well as the issue with SIF still running on the test server once you move the data over. Verifying that the script does what I think it does now.
Jason Treadwell
Custom Solutions Specialist
jason@powerdatasolutions.org
www.powerdatasolutions.org
Web Server Properties
First of all thank you Jason for these scripts. They are great. But i have never found a way to get the web server props to populate automatically after the "truncate table ps.server_config;" line in the "cleanArray.sql" piece. Any new thoughts on that from anyone would be appreciated.
I have been manually setting those after the app starts and then restarting the app. But i would love to automate that so if i am not here, it will just happen. Thank you.
Alan P. Slocum
Network Specialist
Chenango Forks CSD
Re: Web Server Properties
When you have your test server up and running, on the PowerSchool App on the server select PowerSchool from the menu and Export Web Server Configuration. Then the next time you import the data the web server info will be automatically entered.
Brian Andle
Re: Web Server Properties
Thank you Brian! That was all it needed. Now it works like a charm.
Alan P. Slocum
Network Specialist
Chenango Forks CSD
Test Backup/Restore.
Would there be any sort of logs created for this process to check for success or failure?
Thanks
Completion Logs. Backup/Restore Process.
Hi Jason-
This is excellent!! Thank you very much for all the scripts.
I had one question. Would there be any sort of logs that are written as part of this backup/restore process? Or is there any way an email be generated after the backup and restore?
Thanks
Venkat.
Elizabeth Public Schools.
NJ.
What about SIF?
Is there anything i can use to disable SIF on the test server after it loads the live server data and before it starts with SIF enabled? Thank you
Also, I have reset the web server properties on the test server after it restarts, is that the way it should work?
Alan P. Slocum
Network Specialist
Chenango Forks CSD
Yes you can disable SIF on the Test Server
Jacqueline J. M. Brzezinski
First, do not install SIF on the test server..
Second if you did install it, you will have the option under System Settings SIF and you go to the Global setting and each school Setting. And disable execution..
Also at the Execute command line on the server..
You can input.
I have to double check this command line...I am home now.
Pretty close...but not sure
But it is
SAWF_Stop password
SAWF_Start password
There are actually an execute line that will stop SIF and Start Sif
disable sif on test server
Yes, thank you. I was aware of the sif settings under system settings. But by the time you get there, the test server will have already passed a thousand messages or so. So i have added a line that will kill sif before the app launches. Also, i think the sif settings come in with the data from the live server. So weather you have it installed or not, it throws messages. Unless there has been a change i am unaware of. Thank you.
UPDATE PS.prefs
SET value = NULL
WHERE name like 'SAPF_G.IsEnabled';
Alan P. Slocum
Network Specialist
Chenango Forks CSD
re: What about SIF?
Good question. I know our ZIS server is hard coded to look at a specific IP. Therefore even if my test server had SIF enabled it wouldn't talk to my ZIS.
However, PowerSchool has told me that I need to re-install SIF each time I do an upgrade to enable SIF. I wonder if it will even work on the test server without having re-run that there too. Probably will, but just not sure. Like I said, the ZIS won't talk to it unless I tell it to so I'm not sure that it's a problem.
What web server properties are you resetting?
Jason Treadwell
Custom Solutions Specialist
jason@powerdatasolutions.org
www.powerdatasolutions.org
re: What about SIF?
Thank you, I will look at my ZIS server and see if i can set that up for myself. Who's version of ZIS are you running? I had found some time ago, that the SIF install only added the link and pages. And when I would move live data to test server without SIF, it would still throw messages at ZIS. So I would use SQL advantage to SET value = NULL WHERE name like 'SAPF_G.IsEnabled' before launching the app.
As far as the test server web properties, I had to set "allow access" checking both "web" and "images" as well as checking "use external image server" and set the image server address. And then restart the app. With Sybase I was using SQL advantage to set these before launching the app but not sure yet how to get that to work with Oracle. I would think a script would work for this but not sure exactly how to form that up.
As always, thank you so much for all the great tools and information you have shared with me!
Alan P. Slocum
Network Specialist
Chenango Forks CSD
re: re: What about SIF?
If you have other lines you need to do specifically to your system then I'd recommend adding them in the cleanArray.sql file. Should be just as easy as adding another line with the next command.
When I remove the Array I clear out the entire Server_Instances table and all the setups. So when you launch PS on the test server it will create a new server instance and the setup which means it will have already given itself everything.
Anything that is universal (like removing SIF, everyone would want that off their test server) post here and I'll add it to the package.
Jason Treadwell
Custom Solutions Specialist
jason@powerdatasolutions.org
www.powerdatasolutions.org
Web Server Properties
Are you saying the web server props should be populated automatically? If i had restarted a second time, would they populate then? Otherwise you can't log in until someone checks the boxes and puts the image server address in there and restarts the test server. I will test some more and see if i can find out why the test server instance and set up are not auto generating. Thank you.
Alan P. Slocum
Network Specialist
Chenango Forks CSD
re: Web Server Properties
I wonder if both your task master on your live setup and your test server are using the same server instance ID. Mine both use 1. I wonder if 1 is automatic. Just a guess.
Jason Treadwell
Custom Solutions Specialist
jason@powerdatasolutions.org
www.powerdatasolutions.org
re: Web Server Properties
They all have the same server instance. Yes, it seems 1 is automatic.
Alan P. Slocum
Network Specialist
Chenango Forks CSD
Error Running Test Server after Restore!
Hi,
live servers are 2 apps and 1 database. test server is 1 server.
backup and restore went fine. however, the test server came up with the error,
Error encountered while connecting to the database.
See the log for details. Please check your settings in serverconfig.props file.
Greatly appreciate the help!
Long.
Check password
When you upgraded PowerSchool on you test server did you use the same passwords that you used on your live server? If you didn't, you will need to edit the serverconfig.props on your test server and change the password to the one that is in the serverconfig.props on your live server.
Brian Andle
PowerDataSolutions Contributor
Matched password!
Did copy and paste password from live server's serverconfig.props to test server's. Upgrade test server to 5.2.0.6 to match the live one. Server now starts. However when trying the web, http://servername/admin/pw.html, I got switched to the live server. When trying to go to test server's Utilities, Edit Server location, got an error about server array, load balancer (which is the setup on the live server), need to use web GUI. However, public access to test server still works.
Thanks for the help!
Long.
There is script in the
There is script in the package that removes the the server config info. It's called customscripts/cleanArray.bat try running this on your test server with Tomcat and PowerSchool shut down.
All Good Now1
I am able to clear the Server Array in the web, but still can't get the Alert about Server array and load balancer when clicking on Utilities, Edit Server Location in PS. It's now running in port 80.
I used the command: Putpref("enableserverarray";"0") in Execute window in PS to clear the array. It didn't work the first time, so I changed it to PutPref("disableserverarray";"1"). I am not sure which one did the trick though.
re: All Good Now
When you ran the cleanArray script how did you run it? Did you just double click it? In the directions it mentioned that you need to feed it the locations of the PowerSchoolPremier folder and the oracle folder. Well that main script passes this information to the other scripts as well so just double clicking the scripts will not make them work properly. I bet you were running the script and it opens and closes very quickly because there is no pause put in, but in reality it hadn't done anything.
Jason Treadwell
Custom Solutions Specialist
jason@powerdatasolutions.org
www.powerdatasolutions.org
Cleanarray.bat!
I did run by double clicking it the first time. However, the next 2 times I did add letter D at the end of the command. The result page showed that 2 tables truncated.
Some progress!
I changed the web server port from 80 to 81 in order to log in through the web, http://serverip:81/admin/pw.html
I checked Server Array Settings - Server List, saw 3 servers listed there
same server name with 3 different ID's (1, 52, 0). Here's view (a little messy but you get the idea)
ID Supplied Name Host Name Host IP State Last Ping Version OS Description TM RQ MyRQ CS MyCS WS SIF
1 PSTRAINING 10.9.1.36 10.9.1.36 Starting up 9/19/2008 17:45 5.2.0.6.0102 Windows(TM) 2000 5.2 No Yes 1 No 1 No Yes
52 PSTRAINING 10.9.1.36 10.9.1.36 Running 9/19/2008 19:17 5.2.0.6.0102 Windows(TM) 2000 5.2 Yes Yes 52 Yes 52 Yes No
0 PSTRAINING PSTRAINING 10.9.1.36 9/19/2008 16:55 No No No No No
Still working on to get it back to port 80, and get rid of the server array and load balancer.
Really appreciate any help!
Long.
Ran cleanarray.bat twice
Ran cleanarray.bat twice with PS and Tomcat down. Still the same problem with no web access. Utilities - Edit Server Location still comes up with error about Server array and load balancer.