2009-09-26

Database version

When the daily administration of several database instances is done using a standard database, it is rather nice to be able to check that all management database have the same structure.
A quick solution is to calculate a checksum of the database objects and its columns.

To hold the version number of the database and its checksum a (singleton) table is created.
CREATE TABLE [dbo].[Database_Version]
(
    [Database_Version_No] nvarchar(128) NOT NULL,
    [Database_Version_Checksum] bigint NOT NULL,
    CONSTRAINT PK_Database_Version PRIMARY KEY ([Database_Version_No])
);
The table can be initialized by a INSERT statement:
INSERT INTO [dbo].[Database_Version] ([Database_Version_No], [Database_Version_Checksum])
VALUES ('0', 42);

The table is used by two stored procedures - one to read:
CREATE PROCEDURE [dbo].[Get_Database_Version]
AS
BEGIN
    SET NOCOUNT ON;
   
    -- Check stored checksum
    --   Calculate checksum
    DECLARE @objects_checksum bigint;
    SELECT @objects_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([object_id])) AS bigint))
    FROM sys.objects
    WHERE is_ms_shipped = 0;
    DECLARE @columns_checksum bigint;
    SELECT @columns_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([column_id])) AS bigint))
    FROM sys.columns;
    --   Check is the checksum stored is equal to the calculated checksum
    IF (@objects_checksum + @columns_checksum) <> (
        SELECT [Database_Version_Checksum]
        FROM [dbo].[Database_Version]
    )
    BEGIN
        DECLARE @calculated_checksum nvarchar(128) = CAST((@objects_checksum + @columns_checksum) AS nvarchar(128));
        DECLARE @stored_checksum nvarchar(128) = CAST((SELECT [Database_Version_Checksum] FROM [dbo].[Database_Version]) AS nvarchar(128));
        RAISERROR(N'The calculated checksum (%s) is not equal to the stored checksum (%s).',10, 1,@calculated_checksum,@stored_checksum) WITH NOWAIT;
    END
   
    -- Get stored checksum
    SELECT [Database_Version_No], [Database_Version_Checksum]
    FROM [dbo].[Database_Version];
END
...and one to update:
CREATE PROCEDURE [dbo].[Set_Database_Version]
    @Database_Version_No nvarchar(128)
AS
BEGIN
    SET NOCOUNT ON;
   
    -- Check if the given Database Version value is different from the existing
    IF @Database_Version_No = (
        SELECT [Database_Version_No]
        FROM [dbo].[Database_Version]
    )
    RAISERROR('You have to update the Database Version to another value than the existing one.', 18, 0) WITH NOWAIT;

    -- Calculate checksum
    DECLARE @objects_checksum bigint;
    SELECT @objects_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([object_id])) AS bigint))
    FROM sys.objects
    WHERE is_ms_shipped = 0;
    DECLARE @columns_checksum bigint;
    SELECT @columns_checksum = SUM(CAST(CHECKSUM(OBJECT_DEFINITION([column_id])) AS bigint))
    FROM sys.columns;
   
    -- Check is the checksum stored is equal to the calculated checksum
    IF (@objects_checksum + @columns_checksum) = (
        SELECT [Database_Version_Checksum]
        FROM [dbo].[Database_Version]
    )
    RAISERROR('The calculated checksum is equal to the stored checksum.',18, 1) WITH LOG;
   
    -- @objects_checksum + @columns_checksum
    UPDATE [dbo].[Database_Version]
    SET [dbo].[Database_Version].[Database_Version_No] = @Database_Version_No,
        [dbo].[Database_Version].Database_Version_Checksum = @objects_checksum + @columns_checksum;
END


The get procedure also checks if the stored checksum is like to actual calculated checksum. Of course the checksum inserted above is not correct, so the get procedure raise an error:
EXEC [dbo].[Get_Database_Version];
{Message} The calculated checksum (3381089942582) is not equal to the stored checksum (42).

The update procedure calculate a checksum and update the stored checksum. The version number is actually just a string, so you can make your own version standard.
EXEC [dbo].[Set_Database_Version] @Database_Version_No = '3.1';
The get procedure now executes with success.
Database_Version_No   Database_Version_Checksum
-------------------   -------------------------
3.1                   3381089942582

If a object is changes, e.g. a column is added
ALTER TABLE [dbo].[Database_Version] ADD
    [Database_Version_Description] nvarchar(MAX) NULL;

Now the get procedure raises a error
The calculated checksum (3383237426229) is not equal to the stored checksum (3381089942582).
If the column is dropped,
ALTER TABLE [dbo].[Database_Version]
    DROP COLUMN [Database_Version_Description];

the get procedure executes with success.

Discussion
The database version objects are in the schema "dbo", but you might want to keep this schema clean.
When the get procedure fails, you have no indication on what is different. You could use a comparison tool like Red Gate SQL Compare.
There is no timestamp in the solution, but you could format the version as a timestamp, e.g. "2009-09-17A".
The version checksum calculation is somewhat simple. It could also include indexes, users, files and what else you need.
A version and checksum could be calculated and stored for each database. Some changes to the version objects are needed, but it should not be a problem to the dedicated DBA.

2009-09-21

Central Registrered Servers and groups

Adding a new database group to a Central Management Server con be done by a T-SQL statement like this:
DECLARE @new_server_group_id AS int;
DECLARE @parent_server_group AS int;
SELECT @parent_server_group = server_group_id
FROM msdb.dbo.sysmanagement_shared_server_groups
WHERE [name]='DatabaseEngineServerGroup';
EXECUTE msdb.dbo.sp_sysmanagement_add_shared_server_group
  @name = N'Development'
  ,@description = N'Development SQL Server database instances.
  ,@parent_id = @parent_server_group
  ,@server_type = 0
  ,@server_group_id = @new_server_group_id OUTPUT;


The result can be viewed through msdb.dbo.sysmanagement_shared_server_groups.

After the database group is created, some database instances can be registrered in the group by a T-SQL statement like this:
DECLARE @server_group AS int;
SELECT @server_group = server_group_id FROM msdb.dbo.sysmanagement_shared_server_groups WHERE [name]='Development';
DECLARE @new_server_id AS int;
EXECUTE msdb.dbo.sp_sysmanagement_add_shared_registered_server
  @name = N'Frodo\SSDB01'
  ,@server_group_id = @server_group
  ,@server_name = N'Frodo.sqladmin.lan\SSDB01'
  ,@description = N'First database instance on Frodo.sqladmin.lan.'
  ,@server_type = 0
  ,@server_id = @new_server_id OUTPUT;
EXECUTE msdb.dbo.sp_sysmanagement_add_shared_registered_server
  @name = N'Frodo\SSDB02'
  ,@server_group_id = @server_group
  ,@server_name = N'Frodo.sqladmin.lan\SSDB02'
  ,@description = N'Second database instance on Frodo.sqladmin.lan.'
  ,@server_type = 0
  ,@server_id = @new_server_id OUTPUT;


The result can be viewed through msdb.dbo.sysmanagement_shared_registered_servers.

There is a unique constraint on the name, but the procedure is not „intelligent“ enough to handle instert or update of a server registration.

When a group is deleted, the registrered servers in the group is also deleted
DECLARE @del_server_grp AS int;
SELECT @del_server_grp = server_group_id
FROM msdb.dbo.sysmanagement_shared_server_groups
WHERE [name] = N'Development';
EXECUTE msdb.dbo.sp_sysmanagement_delete_shared_server_group @server_group_id = @del_server_grp;


In this example the two previously registered servers are deleted from the Central Management Server - without warning or additional messages.

A automated population, like from System Center Operations Manager (SCOM) will need some coding of a wrapper to handle insert or update.
Also some work is needed for handling database mirror and log shipping sets.

PowerShell

Inspired by the blog entry &bdqou;Managing Registered servers with SQL Powershell“ by Arco Scheepen I looked into using the PowerShell SQL Server provider for accessing the Management Server registrations with Set-Location and New-Item on SQLSERVER:\sqlregistration\Database Engine Server Group.
More details on this later...

This was some quickies - comments are most welcome.

History

2009-09-21 Initial blog entry
2016-10-25 Section on PowerShell added