Introducing The Kentico Xperience SQL Utility Scripts Open Source Repo

Introduction

Over the years of working with Kentico CMS, Kentico EMS, and now Kentico Xperience I have either written, or been a part of team that has written, various TSQL scripts to support the SQL Database side of working with the tool. Most recently I was actually working on a script that was meant to clean up e-commerce order and customer history. That task had some acceptance criteria of keeping some order history for a certain list of customers (those customers with a certain email domain), but removing all the rest (which is a pretty good idea in the world of data privacy and data security when you are copying databases to bring up new environments of a e-commerce website). That last requirement made the task call for a custom script as opposed to a one time TRUNCATE all type of solution. 

As I was doing that, I got to thinking, there have been many times over the years where I have had to do this. Why not find all of these one of utility type of scripts and open source them on GitHub so that other Kentico Xperience developers out there could benefit. And with that, the idea for the Kentico Xperience SQL Utility Script repository was born.

Kentico Xperience SQL Utility Script GitHub Repository

If you head on over to the GitHub repository at https://github.com/mcbeev/Kentico-Xperience-SQL-Utility-Scripts, or click the image below you can see the actual scripts.

The repo has a collection of SQL Scripts used to help maintain and work with Kentico Xperience databases. But remember, with great power comes great responsibility. These scripts are only tested in a few environments. Make sure to test them out first in your development environment and please be sure to create a database backup before running for real.

A few notes about the repo:

• Most of the scripts will be most useful with clean up of long running Kentico Xperience instances, where you want to trim down a database size from say over 25 GB back down to less than 5 GB.
• There are version specific folders inside of /src for each main version of Kentico: KX12 == Kentico Xperience 12.0.x.
• There is an All subfolder that has a useful Shrink SQL Log File script in it, or in my head is a place for a script that works across all versions of Kentico.
• The one we use the most of at over at BizStream is the Disable Tasks for Development Environment Spinup. This script will ensure your new restore to dev doesn't do something stupid like e-mail out to 10k real email addresses.
• I can personally attest to using each of these scripts and seeing them work, they are however, to use at your own risk.
• Feel free to collaborate with me on the scripts, if you have a new one and want to add it, shoot me a Pull Request. If you think my SQL is terrible, fix it, and shoot me a Pull Request.
• Please make sure to include some comments on what the script does and what version of Kentico it is intended for.

Conclusion

I hope the Kentico Xperience development community finds these scripts useful for working with Kentico databases. Again, if you have a new one and want to add it, shoot me a PR. If you think my SQL is terrible, fix it, and shoot me a PR. Thanks!