Any guidance on how to easily deploy & execute sql scripts on a shared sql server environment?
What I have right now is a 2 project process…
deploy the scripts to a central local on the sql server
execute the needed scripts on sql server against 1 database
(which is controlled by a variable set that is updated between runs)
Since the databases are on a shared environment, there is no way to us the machine to scope the execution.
This approach is acceptable for simple, manual updates…but does not work well when deploying to
multiple dbs as part of an automated deployment.
Just to clarify, is the goal to scope the machine so that Tentacle runs under different user accounts, so that you can use integrated authentication when connecting to the different databases? Let me know if I have misunderstood. Thanks!
Not quite. The installation process runs db update scripts…on the SQL Server…but not all databases on a server will be updated at the same time.
So what i need is some way to scope the script execution to a single db on the sql server.
You can create a powershell function around that Cmdlet that accepts multiple database names AND a SQL script path. On the code logic it will run the script on each Database.
From Octopus you would add 2 variables with prompts:
Databases: here you’ll put a set of databases names separated by a coma
ScriptPath: The full path of your script.
Let me know if this makes enough sense. If not i could write a quick version of this for you.
That makes sense…and is similar to what I have…if you have an example that would be useful.
My problem with this approach is the “prompt” part. This is going into an automated build and deploy process.
What would be ideal is to determine the set of DBs to update based on the systems being deployed.
Right now, I have a variable set for each server with the variables scoped to the specific server (I have a C#
app that builds the variable set and adds it to the appropriate projects). So if i deploy to 3 servers,
all the code is scoped appropriately…but having all the databases on one sqlserver…well, you see the problem.
I’m trying picture a scenario to help you with your situation here :). Could you send us some screenshots about your deployment process, you variables and your environment setup? An example based on the environments/tentacles seen on your screenshots would rock.
I’ve decided to take a slightly different approach…
I’ve put together a little c# console app using the .Net client library for the rest api…and I’m using it to activate the application server and database servers for a deploy and setting the configuration variables as needed.
I’m having a bit of trouble updating the variables of the release i want to deploy. Do you have which updates a variable in a library variable set and then updates a release to incorporate the changed values?