NAME
DBIO::MSSQL::Deploy - Deploy and upgrade MSSQL schemas via test-deploy-and-compare
VERSION
version 0.900007
DESCRIPTION
DBIO::MSSQL::Deploy orchestrates schema deployment and upgrades for Microsoft SQL Server using the test-deploy-and-compare strategy.
The orchestration (install, diff, apply, upgrade) and the temp-database lifecycle are inherited from DBIO::Deploy::Base::TempDatabase: diff introspects the live database, deploys the desired DDL into a throwaway database, introspects that, and diffs the two models. This class supplies only the MSSQL-specific seams: the three class-name hooks and the CREATE/DROP DATABASE dialect.
my $deploy = DBIO::MSSQL::Deploy->new(
schema => MyApp::DB->connect($dsn),
);
$deploy->install; # fresh
my $diff = $deploy->diff; # or step-by-step
$deploy->apply($diff) if $diff->has_changes;
$deploy->upgrade; # convenience
Transactional DDL -- used, and bounded
DBIO::MSSQL::Storage declares _use_transactional_ddl(1), alone among this family's drivers apart from PostgreSQL. So install and apply hand their whole batch to "_execute_ddl" in DBIO::Deploy::Base, which brackets it in $storage->txn_do: a statement the server rejects half way through takes the earlier ones with it, and core's one-time "non-transactional DDL" notice does not appear. The reasoning, the statement inventory it rests on and the Transact-SQL sources are in DBIO::MSSQL::Storage; the short version is that Microsoft documents the rule as a closed list of statements that may not appear in an explicit transaction, and nothing this driver emits is on it.
Three boundaries are worth knowing, because the bracket is narrower than the capability sounds.
The temporary database is never inside it. _build_target_model connects to the throwaway database itself, and _execute_ddl only brackets a handle the storage owns (core 0.900004). The install DDL deployed into the temp database therefore runs statement at a time, silently and correctly: that database is dropped whole on any failure, which is its unit of recovery. _create_temp_db and _drop_temp_db are outside the batch path altogether -- CREATE DATABASE and DROP DATABASE are two of the statements on Microsoft's list, and both are issued straight to a handle that must already be in autocommit mode, which their reference pages require; a handle with AutoCommit => 0 is refused, never committed or switched.
The deployment handler reads the same capability and wraps more. DBIO::DeploymentHandler, seeing it true, brackets the entire body of its upgrade -- the diff as well as the apply -- in one transaction on the live connection, and the diff is what creates the temporary database. CREATE DATABASE cannot run inside that transaction, and this class does not end it: _create_temp_db and _drop_temp_db refuse a handle with AutoCommit off. The core base class DBIO::Deploy::Base::TempDatabase hands them a connection of its own with AutoCommit on while the storage holds a transaction (_admin_dbh), so the live transaction is not touched and the refusal is the guard for any other caller that passes the handle of an open transaction. Driving upgrade on this class has no such problem either: the temp database is created, used and dropped before apply opens its bracket.
Snapshot isolation is the documented way to break it. SQL Server does not version metadata, so under SET TRANSACTION ISOLATION LEVEL SNAPSHOT the statements ALTER TABLE, CREATE INDEX, ALTER INDEX and DROP INDEX are refused after a BEGIN TRANSACTION with error 3961 -- roughly half of what a diff emits. Nothing in this driver or in core asks for that isolation level, and a hand-written txn_do around the same DDL would fail identically, but a session that opts into it will see a bracketed deploy fail where an unbracketed one succeeded.
METHODS
_new_introspect
my $intro = $self->_new_introspect($dbh);
The introspector reads dbo and every other schema the classes name (a table schema.table), in the live database and in the throwaway one alike. A schema the classes do not name is not looked at: its tables are neither compared nor dropped.
_create_temp_db
my $name = $self->_create_temp_db($dbh);
Creates a uniquely-named throwaway database with T-SQL CREATE DATABASE, with the collation of the database the handle is connected to: the desired schema is deployed under the rules the live database has, not the server's default -- names that differ in case only are two objects in a case-sensitive database and a refusal (Msg 2714) in the default one. MSSQL cannot run CREATE DATABASE inside a transaction, and this method neither commits nor switches the handle to autocommit: a handle with AutoCommit off is refused with a DBIO::Exception naming the method, before any statement (the collation query included) is sent. The transaction stays as it was. The caller passes a handle with AutoCommit on.
_drop_temp_db
$self->_drop_temp_db($dbh, $name);
Drops the throwaway database with T-SQL DROP DATABASE. Refuses a handle with AutoCommit off like "_create_temp_db", before any statement.
_check_before_apply
$self->_check_before_apply($diff);
The driver's check of "apply" in DBIO::Deploy::Base, run for a released diff before its first statement: "_refuse_unfillable" against the live connection. apply and upgrade are DBIO::Deploy::Base's as they are (the guard for destructive and lossy operations allow_destructive, dry_run, the statements through _execute_diff).
_refuse_unfillable
$self->_refuse_unfillable($diff, $dbh);
Refuses -- a DBIO::Exception naming table and column, nothing sent -- what SQL Server would refuse for the rows a table holds half way through the diff:
a
NOT NULLcolumn without a default that the diff adds to a table that holds rows: "ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified" (Msg 4901);a column that becomes
NOT NULLin a table that has aNULLin it (Msg 515,ALTER COLUMN).
An empty table and a table with no NULL in the column pass. Without the check the server's error dooms the transaction _execute_ddl brackets the batch in; the rollback is refused, core discards the handle and runs the body again on the dead handle, and the caller got "Cannot allocate statement when disconnected from the database" instead of the cause.
_temp_dsn
MSSQL DSNs name the database with Database= (DBD::ODBC) or database= (DBD::Sybase); the key is replaced in its own spelling, or appended in the DBD's spelling when the DSN names none. The spelling matters: DBD::Sybase does not read Database=, ignores it, and connects to the login's default database -- the diff would then compare against that database instead of the temporary one. (The base default appends dbname=, which neither DBD honours.)
SEE ALSO
DBIO::Deploy::Base::TempDatabase - shared temp-database orchestration
DBIO::MSSQL - schema component
DBIO::MSSQL::DDL - generates DDL
DBIO::MSSQL::Introspect - reads live database state
DBIO::MSSQL::Diff - compares two introspected models
AUTHOR
DBIO & DBIx::Class Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.