The ever amazing Steve Jones (blog|twitter) is our host this month. And in case you didn’t know he also is currently running T-SQL Tuesday so you if you plan on joining in you might give him a thanks sometime. Between him and Adam Machanic (blog|twitter) we are up to 155 (almost 13 years) worth of blog parties. If you want to look at the archive go to http://tsqltuesday.com/.
Steve asked us to talk about dynamic code. Code that creates more code. This is a subject that I quite enjoy which made this subject a bit tricky for me. I’ve written quite a few posts on the subject over the years. I did finally come up with something though. Comments. Another subject near and dear to me. Dynamic code gets complicated pretty quickly. That of course means that it should have comments. And I’m going to say that you should not just comment your generator code, but the generated code as well.
As an example I was given a project a while back that required backing up a database, restoring it to multiple locations, one of which is in an availability group, fix the permissions, and make a small predictable data change. After the second time doing this it got annoying which means it quickly bot bumped up on my list of code to automate (or at least partially automate). In the end my design was to use dynamic SQL to create the SQL code necessary to do the restores, remove/add from the AG group, permissions, etc. There are other ways this could have been done for sure, this is just the way I chose at the time.
Because this is a fairly complex piece of code that I expect someone else will have to read/use at some point I put a comment block at the top explaining the basics and then commented the rest of the code with what was happening in each section, what different variables are for, etc. You know .. basic commenting. But since I was creating a piece of code for someone else to run I put comments inside the generated code. Note: The end goal is for this to be automated but I wanted to run it manually for a while to make sure it was bulletproof. I still feel like these comments would be useful after that point in case someone needs to make changes or debug a problem.
So for example the AG code looks something like this:
DECLARE @PrimaryName varchar(50) = 'PrimaryInstance'
DECLARE @SecondaryName varchar(50) = 'SecondaryInstance'
PRINT '/*********************************************'
PRINT ' This code uses SQLCmd code so make sure to '
PRINT ' turn on SQLCmd mode (In SSMS go to the Query '
PRINT ' menu, near the bottom) before running it. '
PRINT '*********************************************/'
PRINT '/* Remove DBName from the availability Group */'
PRINT 'USE master;'
PRINT 'GO'
PRINT 'ALTER AVAILABILITY GROUP [AGName]
REMOVE DATABASE [DBName];'
PRINT 'GO'
--- Do some work here
PRINT
'/********************************************************
Add DBName back to AGName
YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
*********************************************************//* The database must be removed from the secondary instance
so a new copy can be created when it''s added back. */:CONNECT ' + @SecondaryName + '
USE [master];
GO
DROP DATABASE [DBName];
GO
/* Actually add DBName back to AGName. Fortunately this is
a small DB so it doesn''t take long. */:CONNECT ' + @PrimaryName + '
USE [master];
GO
ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON N''' + @SecondaryName + ''' WITH (SEEDING_MODE = AUTOMATIC);
GO
ALTER AVAILABILITY GROUP [AGName]
ADD DATABASE [DBName];
GO
/* I''m not really sure why this is necessary but it was
part of the code SSMS generated for me so I left it in.
If you know better feel free to take it back out again.*/:CONNECT ' + @SecondaryName + '
USE [master];
GO
ALTER AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE;
GO'
And the output looks like this:
/*********************************************
This code uses SQLCmd code so make sure to
turn on SQLCmd mode (In SSMS go to the Query
menu, near the bottom) before running it.
*********************************************//* Remove DBName from the availability Group */USE master;
GO
ALTER AVAILABILITY GROUP [AGName]
REMOVE DATABASE [DBName];
GO
/********************************************************
Add DBName back to AGName
YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
*********************************************************//* The database must be removed from the secondary instance
so a new copy can be created when it's added back. */:CONNECT SecondaryInstance
USE [master];
GO
DROP DATABASE [DBName];
GO
/* Actually add DBName back to AGName. Fortunately this is
a small DB so it doesn't take long. */:CONNECT PrimaryInstance
USE [master];
GO
ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON N'SecondaryInstance' WITH (SEEDING_MODE = AUTOMATIC);
GO
ALTER AVAILABILITY GROUP [AGName]
ADD DATABASE [DBName];
GO
/* I'm not really sure why this is necessary but it was
part of the code SSMS generated for me so I left it in.
If you know better feel free to take it back out again.*/:CONNECT SecondaryInstance
USE [master];
GO
ALTER AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE;
GO
Note that the output code is nicely commented and those comments are still useful in the source code as well.
In a case of great and kinda great minds thinking alike it turns out that Brent (blog|twitter) and I posted on the same topic.
https://www.brentozar.com/archive/2022/10/tsql2sday-start-your-dynamic-sql-with-a-comment/
What’s interesting is that while we both talked about comments in generated code, which is not exactly a large subject, we both came at it from different but complimentary (his word, not mine) angles. Brent talks about putting comments in bits of code (and advice on where in the code) so that you know what’s going on when you see it in the cache and need to find where it’s coming from. I spoke more on larger bits of code where you (or someone else) is likely to need to read the output.
It just goes to show that even with a very narrow topic it’s possible to have multiple blogs that are completely different.