SQL Azure Friendly Database Versioning Idea

Question

I have a number of identical SQL Azure databases for like clients that need to have their versions tracked. SQL Azure in November of 2014 does not support extended properties as far as I know, if it did I could do something with extended properties for tracking versioning changes as was done here:

http://wateroxconsulting.com/archives/versioning-in-your-sql-database#

I was thinking of adapting that concept by making a DDL trigger that altered a stored procedure that held nothing but versioning comments.

So my question is about strategy - is a stored procedure the best place to keep this kind of info, or is there a better place to store textual versioning info in a SQL Azure container? I do not necessarily want to add a table to each database because that will dilute the purpose of the DB and leaves room for antics and it will need its own care and feeding of its table structure and etc.

And my follow-up question is about tactics - what is the best way to alter a procedure completely in TSQL doing string manipulation to append comment text?

Thanks.

Did I get this right that you want to use the procedure text to store data? What do you want to store there? A date of modification? — usr, Nov 19 '14 at 15:01
@usr, I was thinking of storing a date and in YYYYMMDD-HHMM and a version number and 40 chars of text. — Snowy, Nov 20 '14 at 02:43

Stephen Turner · Answer 1 · 2015-04-22T22:34:48.533

You seem to be asking how to store some some data in a stored procedure, which is odd, because SQL server is a fantastic tool for storing data, but not in stored procedures.

I'd say a much better strategy would be to create a very simple table to store the information you mentioned in your comment i.e.

CREATE TABLE dbo.DatabaseVersion
(
    Date datetime,
    VersionNumber decimal(5,2),
    Description narchar(40)
);

And simply write an additional row to that database on each DDL trigger.

I'd say the reasons you have against it are pretty slim, but perhaps you could create a database schema just for this metadata i.e.

CREATE SCHEMA Metadata;

To make the purpose of the table clear and change the "dbo." in the DDL above to "Metadata." Some permissions can prevent antics, and if you need to care for the table structure it is far better to do this as a first class data table than as some unstructured comments in a stored procedure.

score 0 · Answer 2 · edited May 23 '17 at 12:00

This seems very similar to "Create a global static variable in SQL Server?" in a sense of storing some data in obscure way.

I think function would be shorter both in definition:

CREATE FUNCTION [dbo].[fn_GetVersion]()
RETURNS NVARCHAR(100)
BEGIN
  RETURN N'YYYYMMDD-HHMM and a version number and 40 chars of text'
END

... and in data retrieval:

Select [dbo].[fn_GetVersion]();

And it can be updated through dynamic DDL in triggers:

Exec ('ALTER FUNCTION [dbo].[fn_GetVersion]()
RETURNS NVARCHAR(100)
BEGIN
  RETURN N''YYYYMMDD-HHMM and version number and other 40 chars of text''
END')

SQL Azure Friendly Database Versioning Idea

2 Answers2