Cleaning up old tag sets

chris.ryan · 7 April 2017 18:46

I am trying to delete some old tags that reference clients no longer in our environment and the delete fails with: A tag you have attempted to remove is in use in: Variable sets. I have go through all our variable sets and confirmed that these tags are no longer used (although they may have existed in old deployments that have not yet been deleted). Is there a way to force these to be deleted?

I have poked through the DB and noticed all the tag sets exist in dbo.TagSet and each tag set has the tags in a JSON column. I added line breaks to make reading it easier.

{“Description”:"",“Tags”:[
{“Id”:“TagSets-3/Tags-1”,“Name”:“QA-SQL05”,“Description”:null,“Color”:"#ECAD3F",“SortOrder”:0},
{“Id”:“TagSets-3/Tags-2”,“Name”:“QA-SQL06”,“Description”:null,“Color”:"#A77B22",“SortOrder”:1},
{“Id”:“TagSets-3/Tags-25”,“Name”:“B-QA”,“Description”:“Uses B Database”,“Color”:"#6e6e6e",“SortOrder”:2},
{“Id”:“TagSets-3/Tags-122”,“Name”:“H-QA”,“Description”:“Uses H DB”,“Color”:"#6e6e6e",“SortOrder”:3},
{“Id”:“TagSets-3/Tags-26”,“Name”:“P-QA”,“Description”:“Uses P Database”,“Color”:"#6e6e6e",“SortOrder”:4},
{“Id”:“TagSets-3/Tags-62”,“Name”:“QA-SQL08”,“Description”:null,“Color”:"#983230",“SortOrder”:5},
{“Id”:“TagSets-3/Tags-141”,“Name”:“QA-SQL10”,“Description”:null,“Color”:"#3156B3",“SortOrder”:0},
{“Id”:“TagSets-3/Tags-142”,“Name”:“QA-SQL11”,“Description”:null,“Color”:"#203A88",“SortOrder”:0}

]}

I’d like to delete from the JSON column:

{“Id”:“TagSets-3/Tags-25”,“Name”:“B-QA”,“Description”:“Uses B Database”,“Color”:"#6e6e6e",“SortOrder”:2},
{“Id”:“TagSets-3/Tags-122”,“Name”:“H-QA”,“Description”:“Uses H DB”,“Color”:"#6e6e6e",“SortOrder”:3},
{“Id”:“TagSets-3/Tags-26”,“Name”:“P-QA”,“Description”:“Uses P Database”,“Color”:"#6e6e6e",“SortOrder”:4},

I assume this will achieve what I am trying to accomplish (remove the tags B-QA, H-QA and P-QA). What I don’t know is if this info persists anywhere else, or if there is a cleaner way to achieve what I am trying to do.

Michael_Noonan · 10 April 2017 01:33

Hi Chris,

Thanks for getting in touch! This message is telling you the tag(s) are still referenced by a Variable Set, which is most likely a variable snapshot for one of your releases. The reason we veto the delete operation is because we could break a your deployment process which depended on the tag(s) - either when promoting that existing release to a new environment, or re-deploying the release to an environment.

I would strongly recommend against making changes in the SQL database directly. This could void your warranty and generally make things harder.

Do you have retention policies configured, and how long do you keep releases for? https://octopus.com/docs/administration/retention-policies

To find the variable set snapshot you could scan for TagSets-3/Tags-25 in the RelatedDocumentIds column of the VariableSet table. You would then track the OwnerId back via a Deployment to the Release.

If you find a certain set of releases you are happy to delete, you can do that manually through the UI. Otherwise you may find these releases are really old, and retention policies could do the job for you.

If the releases are recent, or could potentially need to be deployed/redeployed - I would recommend leaving the tags alone until such time you can safely delete them.

Let me know how you go!

Hope that helps!
Mike

chris.ryan · 10 April 2017 15:05

Hi Mike, thank you for the quick reply.

I was able to track the releases down and delete them however the tags were also used in old versions of Library Variables. I am not sure how to go about deleting these (other than through SQL).

Thanks!
Chris

Michael_Noonan · 18 April 2017 04:03

Hi Chris,

Sorry I’ve let this one age a bit. I’ve been doing some thinking about this problem, and I can see cases where you will want to archive or delete a tag. In our current implementation, we veto deleting any tags which are used anywhere - this is a bit of a pessimistic approach to the problem, but it is very safe.

If you want to delete an environment, and it is used to scope variables, we consider the impact of removing the environment, and try to take the best approach with that variable. In some cases we will just remove the environment from the variable scope, and in other cases we will cascade delete the variable altogether.

We could look at taking a similar approach with tags…

To help me understand better, in your scenario, are those library variable sets shared amongst several projects? And are the variables, which are scoped to tenant tags, shared amongst several projects? Also, are you intending to delete these variables now, or to keep the variables but change their scoping?

Hope that helps!
Mike

chris.ryan · 19 April 2017 12:28

Hi Mike,
These specific tags and variable sets are used across multiple projects. We use one tag set to identify which SQL server a particular client is homed to so it is used in both the deployment of our DB project and also our web front end project to update the connection strings. The tenant tags I am looking to delete are for databases that are no longer online, so if something was to try and deploy to these servers, there’d be a failure regardless of the tag existing or not.

Thanks,
Chris

Michael_Noonan · 19 April 2017 22:47

Hi Chris,

Thanks for keeping in touch! So in this case you have a variable like this in the library variable set?

DatabaseServer=SQLDB999.mycompany.local; Scope: MyTag

And is there only one tag scoped to each variable?

In this case we could safely remove the variable from everywhere since it will never apply again. Does that sound right in your scenario?

Where it gets tricky is to figure out the right choice to make when cascade-deleting a tag will end up “widening” the scope of a variable value having unintended side-effects.

An alternative idea

An alternative to hard deleting tags may be to “archive” tags - similar to what we’ve recently done with Certificates. The idea is the certificate is still around in an archive if you need it for some reason, but it can’t be used anywhere, and is generally hidden from the UI/API.

At the moment I’m personally leaning towards providing an easier way to delete tags, performing a cascade delete where we can do so safely.

In the meantime

There is no way through the API or UI to delete those variables from the variable set snapshots. We actively prevent changes to the variable snapshots. The only options you have right now are:

Wait for the releases to be cleaned up by retention policies (or delete the releases linked to the variable sets in question)
Just live with the tags, but perhaps rename them to DB01 - Archived or similar (and change their colour?)
Hand edit the JSON in the database to remove those variables - last resort.

Let me know what you think.
Mike

chris.ryan · 21 April 2017 12:49

Our usage for the SQL Server tag is a little more in depth and probably isn’t the ideal use case but let me see if I can explain it.

We have a tenant tag set with a tag for each SQL Server (6… well 12 + right now as I am upgrading our SQL servers to 2016 and decommissioning the 2012 servers).

There is a Variable set called SQL Server, so I can use #{SQLServer} to insert the correct SQL Server name into scripts . This table looks like this:

Name	Value	Scope
SQLServer	DEV-SQL01	tag: DEV-SQL01
SQLServer	DEV-SQL02	tag: DEV-SQL02
SQLServer	QA-SQL01	tag: QA-SQL01
SQLServer	QA-SQL02	tag: QA-SQL02
SQLServer	PRD-SQL01	tag: PRD-SQL01
SQLServer	PRD-SQL02	tag: PRD-SQL02

Then there is another Variable Set “Web.Config” that (sort of) looks like this:

Name	Value	Scope
Some Value	connection string='data source=#{SQLServer};initial catalog=#{Client}_db_#{ENV};integrated security=True;	ENV: DEV

These tags are used in Environments to tag the SQL server that matches the name, so SQL Server QA-SQL01 will have the tag QA-SQL01 and in tenants so each tenant who has a DB on QA-SQL01 will also have the tag QA-SQL01

The actual database deployment step is a robust script that is included within the nuget package that I call as:
Script Filename: Tools\Deploy-All.ps1
Script parameters: -client #{Client} -env #{ENV} -sqlServer #{SQLServer}

For the second part,

I could be wrong but it does not appear that the tag mapped to a unique GUID in the database.

Following what is your preferred method, and I think it would be mine also… If a tag (hypothetically “VIP User”) was to be deleted, but a cascade delete could not be safely run, you could recreate the tag “VIP User” in the same tenant tag set and regain it’s functionality - correct?

Michael_Noonan · 24 April 2017 01:15

Hi Chris,

Thanks for keeping in touch! Nice: that seems very similar to our example of allocating tenants to machines for hosting their software. The only recommendation I would make about “style” would be to consider using tag names that are not coupled directly to the name of the database server. An example might be QA-SQL-US-WEST. This way if/when you need to migrate the underlying hardware, you could just change the variable value - and then any deployment you run would connect the tenant software to that new server. I’m sure there’s lots of details I’ve missed, but I think the idea is worth consideration.

I’ve had a conversation with some other team members, and I’ve raised this GitHub issue based on our conversation: https://github.com/OctopusDeploy/Issues/issues/3436

That doesn’t help too much right now though! Each tag does have a GUID ID, but we expose tags to the API and command-line tools by “canonical tag name” which is a couplet of the tag set name and tag name. This makes it a lot easier to work with them externally, but means we can keep stronger referential integrity in the database: https://octopus.com/docs/key-concepts/tenants/tenant-tags#TenantTags-Referencingtenanttags

If you can track these “snapshots” (where IsLocked=true) back to their source Release, deleting those Releases is still the easiest way to finally delete the tags. Otherwise you will have to resort to editing the JSON in those snapshots to remove the variable value scoped to the GUID for the deleted tag.

Hope that helps!
Mike