**self-hosted*
Can anyone share with me their process for automating the LIVE to TEST copy?
Right now I have a checklist that I follow to do it manually. Basically I stop the TessService app pool, then blow away the impresario database on our TEST server (right click --> delete). I then execute a database restore using the most recent back up. After that I recreate the logins and then run a series of queries (in a specific order) to do things like change the dbo owner, update defaults, etc.
I do this once a month right now. If I could automate the process I would like to see it occur more often (one a week maybe).
Anybody have any thoughts???
Ashley Elliott
Database Administrator
St. Louis Symphony Orchestra
314-286-4198
ashleye@slso.org
Sounds like you might have a few redundant steps in your process -- I would suggest experimenting to see what you can omit or why it's necessary. For example, the services and app pools don't have control over the database, so there's no real need to stop them. You also don't have to delete the test database when you can restore over top of it. Additionally, any series of queries that you run one-by-one can be reduced to one single script execution if structured properly.
I have live-to-test automated down to a single PowerShell script which starts with a COPY ONLY backup of LIVE to a shared folder, puts the test database into RESTRICTED_USER mode to kill any existing sessions and do away with needing to stop/restart any services or app pools, then restores the backup to the test server. Once the restore is done, still in restricted mode, the usual series of scripts are run to synchronize encryption keys, recreate the services user, etc. This includes a custom script to set environment-specific defaults and email profiles (seemingly what Gawain's script is doing), and also includes a TIM-provided script for DropAndRecreateTessituraLogins.sql. Then when everything is ready for access it puts the database back into MULTI_USER mode so that the services can reconnect, and finally it sends an email to hello@ from my account to request a TNEW live-to-test copydown.
I have plans to make this self-service for the box office manager by allowing it to be invoked from a Slack bot... but check back in with me next year on that one.
Nick, would you be willing to share your script? If so, send in a zip to my email gerald.boutot@strazcenter.org or provide a download link. I'm very interested in studying the script to see how it works. I'm not sure I could get it approved to use here, but I'm still very interested in how you are doing it step-by-step and the order of operations. Thanks!
Copy me, please?
Ashley
Me too please if sharing!
As you all know, I'm ideologically opposed to sharing code out of band of network collaboration tools and am contractually obligated (jk) to shill Bitbucket whenever possible. (The real reason is so people searching this thread in the future can still access the content without emailing me.) Please enjoy this snippet on Bitbucket:
https://bitbucket.org/TN_WebShare/workspace/snippets/bxL697/db-copy-scripts
You will need Bitbucket access in order to view -- see Tom Brown's post from a few days ago in this same topic for directions if you are unfamiliar.
Also: this is HEAVILY caveated as being highly org-specific and also minimum-effort. There are hardcoded secrets/tokens which have been replaced by benign text. There are plenty of opportunities for making a more robust system out of it, but I think the concepts at play here are useful to wrap your head around and can be easily applied to your own systems.
My only other ask is that you please do ask for clarifications using the comment thread at the bottom of the snippet. The WTFs per Line of Code is definitely not zero.
CC Jerry BoutotJo BramleyAshley Elliott (Past Member)