Customise schema/object name of the MigrationLog table

dnlnln

Is it possible to configure a ReadyRoll project to use a __MigrationLog table in a schema other than dbo, so that more than one application could manage and version control its database schema within a shared database?

Alternatively, is it possible to use a table named something other than __MigrationLog, to the same end?

Apologies if this is documented somewhere; I have tried to find an answer, and failed.

-Mark Davies

dnlnln

It is indeed possible to deploy the [dbo].[__MigrationLog] table to an alternative schema (i.e. other than dbo).

In order to set a custom schema name, edit your project file (.sqlproj) and add the following under the root node:

<PropertyGroup>
<MigrationLogSchemaName>MySchema</MigrationLogSchemaName>
</PropertyGroup>

The next time you deploy your project, the migration log table will be created within the specified schema (as will the related view object, [MigrationLogCurrent]).

(Incidentally, if you're looking to split your shared database into multiple projects by schema (i.e. in order have individual namespaces, where each project contains a subset of objects from a given database), have a look at the following article in the documentation to find out how to setup project filters:
Partitioning a database by schema)

It is also possible to customise the table name (default: __MigrationLog) and view name (default: __MigrationLogCurrent):

Updated 2017-09-19: Please note that customising of the table and view name is now deprecated, and support for the the following project settings will soon be removed:

<PropertyGroup>
<MigrationLogTableName>MyMigrationLog</MigrationLogTableName>
<MigrationLogViewName>MyMigrationLogCurrent</MigrationLogViewName>
</PropertyGroup>

See further below for steps on how to adapt your existing projects to the MigrationLogSchemaName setting which replaces these settings.

Orm

Daniel, could you check the link to the "forum post to find out how to setup project filters", it seems to be broken?

Orm

nvm, found it https://forum.red-gate.com/discussion/80015

Orm

Tried to implement this solution, found one bug. Deployment now fails when trying to run

EXECUTE sp_rename 'PK___MigrationLog', 'PK___MigrationLog_todrop';

I guess this works fine when ___MigrationLog is within default (dbo) schema, but for custom schema it doesn't work. It should be

EXECUTE sp_rename '<custom schema>.PK___MigrationLog', 'PK___MigrationLog_todrop';

dnlnln

Orm wrote: »

Tried to implement this solution, found one bug. Deployment now fails when trying to run

EXECUTE sp_rename 'PK___MigrationLog', 'PK___MigrationLog_todrop';

Sorry to hear you're encountering a problem customizing the schema name. May I ask, if you have a look at the sqlproj file, what value is specified for the <ReadyRollToolsVersion> property? Is it something other than 1.12?

The reason why I ask is that I can only reproduce the issue either when that property is either not present or set to something other than this value.

Orm

Hi Daniel,

You're totally right, this node somehow was missing from the file (I played a lot with it to set up the project). Now when it's back it works fine, thanks

dnlnln

Great to hear, thanks for confirming!

We're in the process of resolving the T-SQL defect you spotted. I'll update this thread once its available

dnlnln

Please note that customising of the table and view name is now deprecated, and support for the following project settings will soon be removed:

<PropertyGroup>
<MigrationLogTableName>MyMigrationLog</MigrationLogTableName>
<MigrationLogViewName>MyMigrationLogCurrent</MigrationLogViewName>
</PropertyGroup>

If you have any projects that are currently using the above settings, you will need to adapt your project to use the MigrationLogSchemaName setting. This means that your migration log table and view objects will always be named __MigrationLog and __MigrationLogCurrent respectively, however the schema in which they are contained will still be customisable.

Once you have removed the MigrationLogTableName and MigrationLogViewName settings from your sqlproj file, and added the MigrationLogSchemaName setting, you will need to move your existing migration log table to the newly-defined schema.

Adapting existing projects can be accomplished by performing the following steps:

1. Open your ReadyRoll project in Visual Studio
2. In the Solution Explorer, right-click the project and select Unload Project.
3. In the document window, remove the <MigrationLogTableName> and <MigrationLogViewName> elements, and add the <MigrationLogSchemaName> property in their place:

   <PropertyGroup>
   <MigrationLogSchemaName>MySchema</MigrationLogSchemaName>
   </PropertyGroup>

4. Save and right-click the project and select Reload Project.
5. In the Solution Explorer, right-click the Pre-Deployment folder and select Add Script
6. This script should run after 01_Create_Database.sql, so give it a name like 02_Move_MigrLog_to_Schema.sql
7. Paste the below code into the script window
8. Replace the values of the variables in the header of the script as appropriate. OldMigrationLogSchema should be left as "dbo" unless you had previously customised the schema name. OldMigrationLogTableName should use the value of the <MigrationLogTableName> (former) sqlproj setting, OldMigrationLogViewName should use the value of <MigrationLogViewName>, and NewMigrationLogSchemaName should use the value of <MigrationLogSchemaName>.
9. Click Build... Deploy solution

In the Output window, look for the following message:

Successfully moved migration objects

If this message does not appear, check that the variables are set correctly and try again. Note that you may receive the following error after this message:

This script "Migrations\0001_<<scriptSuffix>>.sql" has already been executed within the "<<DatabaseName>>" database on this server. Halting deployment.

However, the migration object move should have succeeded in spite of this message, and the error should not appear during subsequent deployment attempts. If the error reappears, check that the NewMigrationLogSchemaName is set to the correct value.


Content of Pre-Deployment\02_Move_MigrLog_to_Schema.sql:

:setvar OldMigrationLogSchema "dbo"
:setvar OldMigrationLogTableName "__MigrationLog"
:setvar OldMigrationLogViewName "__MigrationLogCurrent"
:setvar NewMigrationLogSchemaName "MyMigrationObjSchema"
GO

USE [$(DatabaseName)]

BEGIN TRANSACTION

IF OBJECT_ID('[$(NewMigrationLogSchemaName)].[__MigrationLog]', 'U') IS NULL AND OBJECT_ID('[$(OldMigrationLogSchema)].[$(OldMigrationLogTableName)]', 'U') IS NOT NULL
BEGIN
	IF SCHEMA_ID(N'$(NewMigrationLogSchemaName)') IS NULL
		EXEC sp_executesql N'CREATE SCHEMA [$(NewMigrationLogSchemaName)] AUTHORIZATION [dbo]'

	ALTER SCHEMA [$(NewMigrationLogSchemaName)] TRANSFER [$(OldMigrationLogSchema)].[$(OldMigrationLogTableName)]
	ALTER SCHEMA [$(NewMigrationLogSchemaName)] TRANSFER [$(OldMigrationLogSchema)].[$(OldMigrationLogViewName)]

	IF OBJECT_ID(N'[$(OldMigrationLogSchema)].[__SchemaSnapshot]', 'U') IS NOT NULL
		ALTER SCHEMA [$(NewMigrationLogSchemaName)] TRANSFER [$(OldMigrationLogSchema)].[__SchemaSnapshot]

	PRINT N'Dropping [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)].[IX_$(OldMigrationLogTableName)_CompleteDt]...';
	DROP INDEX [IX_$(OldMigrationLogTableName)_CompleteDt]
		ON [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)];

	PRINT N'Dropping [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)].[IX_$(OldMigrationLogTableName)_Version]...';
	DROP INDEX [IX_$(OldMigrationLogTableName)_Version]
		ON [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)];

	PRINT N'Dropping [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)].[UX_$(OldMigrationLogTableName)_SequenceNo]...';
	DROP INDEX [UX_$(OldMigrationLogTableName)_SequenceNo]
		ON [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)];

	PRINT N'Dropping [$(NewMigrationLogSchemaName)].[DF_$(OldMigrationLogTableName)_deployed]...';
	ALTER TABLE [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)] DROP CONSTRAINT [DF_$(OldMigrationLogTableName)_deployed];

	PRINT N'Creating [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)].[IX___MigrationLog_CompleteDt]...';
	CREATE NONCLUSTERED INDEX [IX___MigrationLog_CompleteDt]
		ON [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)]([complete_dt] ASC);

	PRINT N'Creating [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)].[IX___MigrationLog_Version]...';
	CREATE NONCLUSTERED INDEX [IX___MigrationLog_Version]
		ON [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)]([version] ASC);

	PRINT N'Creating [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)].[UX___MigrationLog_SequenceNo]...';
	CREATE UNIQUE NONCLUSTERED INDEX [UX___MigrationLog_SequenceNo]
		ON [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)]([sequence_no] ASC);

	PRINT N'Creating [$(NewMigrationLogSchemaName)].[DF___MigrationLog_deployed]...';
	ALTER TABLE [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)]
		ADD CONSTRAINT [DF___MigrationLog_deployed] DEFAULT ((1)) FOR [deployed];

	PRINT N'Rename [$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)] to __MigrationLog';
	EXECUTE sp_rename @objname = N'[$(NewMigrationLogSchemaName)].[$(OldMigrationLogTableName)]', @newname = N'__MigrationLog', @objtype = N'OBJECT';

	PRINT N'Rename [$(NewMigrationLogSchemaName)].[$(OldMigrationLogViewName)] to __MigrationLogCurrent';
	EXECUTE sp_rename @objname = N'[$(NewMigrationLogSchemaName)].[$(OldMigrationLogViewName)]', @newname = N'__MigrationLogCurrent', @objtype = N'OBJECT';

	ALTER TABLE [$(NewMigrationLogSchemaName)].[__MigrationLog]
		DROP CONSTRAINT [PK_$(OldMigrationLogTableName)]

	ALTER TABLE [$(NewMigrationLogSchemaName)].[__MigrationLog] ADD CONSTRAINT
		[PK___MigrationLog] PRIMARY KEY CLUSTERED 
		(
		migration_id,
		complete_dt,
		script_checksum
		)

	PRINT N'Altering [$(NewMigrationLogSchemaName)].[__MigrationLogCurrent]...';
	EXEC ('
		ALTER VIEW [$(NewMigrationLogSchemaName)].[__MigrationLogCurrent]
				AS
				WITH currentMigration AS
				(
				  SELECT 
					 migration_id, script_checksum, script_filename, complete_dt, applied_by, deployed, ROW_NUMBER() OVER(PARTITION BY migration_id ORDER BY sequence_no DESC) AS RowNumber
				  FROM [$(NewMigrationLogSchemaName)].[__MigrationLog]
				)
				SELECT  migration_id, script_checksum, script_filename, complete_dt, applied_by, deployed
				FROM currentMigration
				WHERE RowNumber = 1
	');

	PRINT 'Successfully moved migration objects to the [$(NewMigrationLogSchemaName)] schema'
END

COMMIT TRANSACTION