Stored procedure version comment and database extended property about sql-server-maintenance-solution HOT 7 CLOSED

I think that the best would be to have both the last modified date and the release date as comments in the stored procedures. I will work on adding this to my release process.

from sql-server-maintenance-solution.

Thank you for the feedback. One question:

Let's say that I release a new version, where I have only updated one of the stored procedures.

The date in MaintenanceSolution.sql would then get updated.

Now what about the dates in the stored procedures? Should I only update the date in the stored procedure that has been updated, or should I update the dates in all of the stored procedures?

from sql-server-maintenance-solution.

Once installed, I can't easily determine which version of "MaintenanceSolution.sql" was used to install the procedures. I would make it simple and make all the dates the same as the last updated date. This would insure that the code is from one release and compatible. If I see I have an older version of a procedure, I see no reason not to replace all the procedures. If you version each separately, then it would be harder to figure out which if I have a consistent set.

Conceptually, I think the difference is version tracking for development verses version tracking for deployment. Source control can track all changes for a developer. Procedures can be tracked separately or all together (tag/branch). As a client, my interest is that I have the latest and greatest complete set of code (e.g., deployed from a tag on stable code). If you include a version date specific for the procedure, I would make it a separate comment in addition to the deployment version.

If there is a breaking change to the CommandLog table or to a job, it would be really nice to be able to check the version of the table or job. Without renaming the table (e.g., version in the name), I think a extended property on the object would be the way to do this. For the jobs, a comment in the job or in the step code might be enough to determine if it is compatible. Store version info in extended properties on the procedures, tables, jobs, etc.?

from sql-server-maintenance-solution.

PS I worry that the object can be changed without the extended property being updated. That's why in my sample I pull the version for the extended property out of the text of the procedure. I don't know what to do with a table without coding detection of column changes. Would a computed column that specifies the version be a poor hack to insure we have the correct table version? An unused output parameter in a stored procedure to hold the procedure version? I have not really thought about this before. There probably is a more elegant way to track versions.

from sql-server-maintenance-solution.

from sql-server-maintenance-solution.

I have updated my release procedure. It is now adding a version timestamp in the header of the stored procedures, and in the header of MaintenanceSolution.sql.

You can query the version timestamps like this:
https://ola.hallengren.com/scripts/misc/VersionCheck.sql

I have also included the version timestamp in the output from the stored procedures.

from sql-server-maintenance-solution.

I have currently no plans to add extended properties.

from sql-server-maintenance-solution.