Database Managment project

Build

Build the project would genenerate a .dacpac file which would be used for deployment.

Deploy

We can use SqlPackage.exe command tool for deployment.

It can be found from path like “C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe”. When install sql server please include the feature SQL Server Development Tools (SSDT). Currently seems there is an issue for visual studio, the package file cannot be executed by previous version of Sqlpackage.exe, we can use the nuget package https://www.nuget.org/packages/Microsoft.Data.Tools.Msbuild for lastest Sqlpackage. For easy access, we have created a project SqlPackageNugetReference which will download that package.

Samples to call the cmd:

• First perform a check of what will be updated in the target database.

  SqlPackage.exe /Action:DeployReport /SourceFile:./db.dacpac /Profile:./db.publish.xml /OutputPath:./ChangesToDeploy.xml /TargetPassword:xxxx 

• If everything is fine, deploy your changes with the following command. May need to enable other options.

  SqlPackage.exe /Action:Publish /SourceFile:./db.dacpac /Profile:./db.publish.xml /TargetPassword:xxxx

• Specify SQLCMD parameter if needed like below:

  /Variables:Mode=LOCAL

• Sample deployment cmd lines

  Specify correct password. And you may need to set 'BlockOnPossibleDataLoss' to False in case make some table schema changes like alter column.

  Deploy to central SQL Server

  .\SqlPackage\sqlpackage.exe /Action:Publish /SourceFile:./DataServiceDB.dacpac /TargetServerName:xxx /TargetDatabaseName:XXX /TargetUser:sa /TargetPassword:xxx /Variables:Mode=REMOTE /p:BlockOnPossibleDataLoss=True

  Deploy to SQL Express 2012 with LOCAL mode

  .\SqlPackage\sqlpackage.exe /Action:Publish /SourceFile:./DataServiceDB.dacpac /TargetServerName:xxx\SQL2012,1433 /TargetDatabaseName:XXX /TargetUser:sa /TargetPassword:XXX /Variables:Mode=LOCAL /p:BlockOnPossibleDataLoss=False

Notes

1. By default it only controls DB schema changes. Currently we are using own data version table to manage data version in post deployment script.

2. Can create the project by importing from existings DB or script files.

3. Should only have one pre or post deployment script. But it can include more than one scripts for non build item. Also you need to enable SQLCMD mode by click menu "SQL" -> "Execution Settings" -> "SQLCMD Mode".

   Sample reference script file, it has version control to make sure only run the file once:

    /*Check the version to avoid duplicate update*/
    declare @versionId nvarchar(50) 
    SELECT @versionId = 'Version1'
    if EXISTS (select VersionId from __DataVersion where VersionId = @versionId )
    BEGIN
        PRINT @versionId + ' has already been applied. Ingore it.'
        RETURN
    END
    
    /*Only allow to run script under specified mode, the mode is defined as a global SQLCMD variable.
    --Only allow to deploy it under LOCAL mode
    IF ('$(Mode)' <> 'LOCAL') 
    BEGIN
        PRINT 'No need to deploy ' + @versionId + ' for $(Mode) Mode.'
        RETURN
    END
    */
   
    BEGIN TRY
        BEGIN TRANSACTION
    	    /*Insert version at first*/
    	    insert into __DataVersion (VersionId, [State], Note) VALUES (@versionId, 'processing','Version1 Desc')
   
    	    /*
    	    * Handle data changes here
    	    */	    
    	    --***********Replace code here************--
   
    	    print  @versionId + ' Complete'
    	    /*Update version state at last*/
    	    UPDATE __DataVersion SET [State] = 'finished' WHERE VersionId = @versionId
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        print  @versionId + ' failed'
        DELETE FROM __DataVersion WHERE VersionId = @versionId
        DECLARE 
            @ErrorMessage NVARCHAR(4000),
            @ErrorSeverity INT,
            @ErrorState INT;
        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
        RAISERROR (
            @ErrorMessage,
            @ErrorSeverity,
            @ErrorState    
            );    
    END CATCH
   
    
   

4. Important rules for DB update:

   • Don't delete DB object like table, view, column. If you still want to do it, please make sure to also modify all reference scripts in post deploy that will use this object, or a fresh install may fail.
   • If you want to add new column in existing table, mostly it is good to have default value for it. Or you also need to take care of existing reference scripts for a fresh deployment.
   • Make sure apply version control for data change script file

Known Issues

1. When deploy to SQL 2005, it may get below exception. Just ignore it and rerun it againg.

   Error SQL72014: .Net SqlClient Data Provider: Msg 2812, Level 16, State 62, Line 1 找不到存储过程 'sp_refreshsqlmodule'。