Customise schema/object name of the MigrationLog table

dnlnlndnlnln Posts: 165 Silver 2
edited February 16, 2017 12:48AM in ReadyRoll
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
Daniel Nolan
Product Manager, ReadyRoll
Redgate Software
Tagged:

Comments

  • dnlnlndnlnln Posts: 165 Silver 2
    edited October 17, 2017 8:00AM
    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.
    Daniel Nolan
    Product Manager, ReadyRoll
    Redgate Software
  • Daniel, could you check the link to the "forum post to find out how to setup project filters", it seems to be broken?
  • 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';
  • 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.
    Daniel Nolan
    Product Manager, ReadyRoll
    Redgate Software
  • 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
  • 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
    Daniel Nolan
    Product Manager, ReadyRoll
    Redgate Software
  • dnlnlndnlnln Posts: 165 Silver 2
    edited September 21, 2017 3:08AM
    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
    
    Daniel Nolan
    Product Manager, ReadyRoll
    Redgate Software
Sign In or Register to comment.