cf_A-Header
This SQL script, cf_A-Header, is designed to set up the foundational
environment and configurations for a database application. It manages
versioning, user-configurable options, and performs several crucial checks to
ensure a smooth deployment.
Script Breakdown
1. Application Versioning
(cf_A-Version)
The script starts by declaring and setting an application
version variable:
@AppVersion: A varchar(50) variable is declared and set
to '250702'. This likely represents the application's release date (July 2nd,
2025).
2. Header and Global Object Initialization (cf_A-Header)
This
section handles initial setup and checks:
User-Configurable Options
Two important user-configurable options are defined:
@ManageJobs: A bit
variable that, when set to 1, indicates that SQL Agent Jobs should be deployed
or uninstalled if they don't already exist. A 0 means no job management. It's
initially set to 1.
@EchoOn: A bit variable that controls whether
deployment progress results are displayed (1) or suppressed (0). It's initially
set to 0. The script attempts to override this value by querying a
[Lookup].[Config] table if it exists, using the ID = 3 entry.
Temporary
Tables Initialization
The script creates and initializes several temporary
tables in tempdb, which are used to store configuration, messages, and version
information during the script's execution:
#Vars: Stores key-value pairs
for global variables used throughout the deployment process. Initialized values
include:
CurrentEntity: 'none'
CurrentType: 'none'
CurrentVersion: '00000000'
AppVersion: @AppVersion (from the previous
section)
ManageJobs: @ManageJobs
EchoOn: @EchoOn
(Commented
out: DebugMode)
#Messages: Used to log messages, especially errors,
during the deployment. It has Type and Message columns.
#EntityVers:
Stores version information for different entities within the application. It
includes columns for ID, Type, Name, Description, Schema, and Version. The
AppVersion is inserted here as an entity.
#Config: A temporary table to
store install configuration settings.
Pre-Deployment Checks
The script
performs several crucial pre-deployment checks to ensure the environment meets
the application's requirements:
SysAdmin Role Check: It verifies if the
user running the script is a member of the sysadmin server role. If not (and not
an RDS admin user), an error message is added to #Messages. This is a common
security measure for database deployments.
Database Compatibility Level
Check: It ensures the current database's compatibility_level is 90 (SQL Server
2005) or higher. Older compatibility levels might not support certain features.
ANSI NULLs Check: It verifies that ANSI_NULLS is ON for the currently
executing stored procedure (or batch). This ensures consistent behavior when
comparing values with NULL.
Quoted Identifier Check: It checks that
QUOTED_IDENTIFIER is ON. This allows for identifiers with spaces or reserved
keywords to be enclosed in double quotes.
Error Handling and Script
Halting
If any errors are found during the pre-deployment checks (i.e., if
#Messages contains entries with Type = 'Error'), the script updates a variable
in #Vars named HaltScript to 'True'.
It then selects and displays all
error messages from #Messages.
Finally, if HaltScript is 'True', the
script returns, effectively stopping its execution.
Schema Creation -
The script then proceeds to create several schemas if they don't already exist.
Schemas help organize database objects and manage permissions. The schemas
created are:
- [Deploy]
- [Util]
- [su]
- [Lookup]
In
Summary
This script acts as an essential setup and validation gateway for the
application's deployment. It ensures that the SQL Server environment meets the
necessary prerequisites, defines global configurations, and prepares temporary
storage for managing the deployment process. By performing these checks upfront,
it helps prevent issues and ensures a more reliable installation.
Do you
want to know more about a specific part of this script, like the significance of
SET NOCOUNT ON or the purpose of database schemas?
Page Last Updated: 03 July 2025