Automating LIVE to TEST copies

I'm sure someone will chime in with a script for doing the actual copy (we're on RAMP so I don't have that), but I do have a fairly involved script for update Tessitura configuration after copy completion, if you're interested in seeing that.

Gawain Lavers

That would be interesting.  Thank you for the willingness to share.

Yes, could you share that script with us?

Thanks, Ashley

Okay.  I'm sure I'm supposed to post some legalize somewhere in the product description ("This could quite possibly delete all your data!"), as with most things I've sort of tried to keep it a bit generally applicable, but there are likely a number of cases where it is built specifically to our instance.  The one thing I do know is problematic is the audit columns constraints on the system tables: I have custom functions to get user/location that you'll want to replace.

LTR_CPSMA_ENV_SERVERNAME

Basically, there is a table for specifying your different "Environments" (we used to have three).  This allows you to have different changes for each environment, but also should prevent this from running if the environment specified doesn't match the server name detected.

LTR_CPSMA_ENV_UPDATES

This allows you to select a table and up to three columns to match on (so for T_DEFAULTS you'd match on "parent_table" and "field_name", and then you specify a target column and various alterations you can make (prefix, suffix, complete replacement, substring replacements (e.g. TSQL REPLACE function), etc.)).

Since these are "freeform" entries, where a column uses an id you'll have to look those ids up to put them in here.  T_DEFAULTS will probably be your main entry here, and it tends to use a lot of text values, but most other tables will typically refer to things (like "parent_table", usually "organization") by id.

LTR_CPSMA_ENV_OPERATIONS

This allows you to specify stored procedures (and a parameter list) to run for bigger data conversion operations.  You might want web table truncation and payment card purge standard procedures in here.

LP_CPSMA_MAKE_ENV_CHANGES

This is the procedure that reads the configurations above and makes the changes and runs the procedures.

Other Procedures

These are custom stored procedures that I run or have run in the past (for instance, I no longer expire user accounts since we moved to active directory login).

bitbucket.org/.../

How do I get to this bitbucket folder?

Ashley

Ashley Elliott (Past Member)

To Access Bitbucket you need to sign up as a Tessitura Network developer account.

When you sign up as a developer you get access to the Developers Slack and Bitbucket.

The following link takes you to the signup page https://www.tessituranetwork.com/Support/Start/Developers/Access

--Tom

Oh! Thanks!

Ashley

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.

Wow!

So after reading your reply I found a sample script online:

Simple Script

Okay so why did I JUST realize I could select this button to get some of the setting in script form:

If I set everything up the way I would manually, then script to query window, I will have at least that part written for me.

Indeed -- none of these tasks are accessible exclusively via the SSMS GUI. You can always find them in the T-SQL reference, which I highly recommend keeping handy: docs.microsoft.com/.../restore-statements-transact-sql

Nick Reilingh said:

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,

Nice. It's always a little annoying when TIM nags/warns about services being stopped when upgrade documentation instructs us to stop these same services.

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!