Копирайте базата данни на SQL Server 2012 в SQL Azure чрез BACPAC (за непрекъсната интеграция)

Затова реших да използвам PowerShell (тъй като това наистина е по-скоро скрипт), с само клиентски DAC. Примерът тук беше много полезен.

Използването на MSBuild Extension Pack позволява създаването на задачи за обвиване на шаблона на PowerShell.

Имайте предвид, че трябва да инсталирате PowerShell 3 за достъп до DAC 3.0, което се постига най-добре чрез инсталиране на Windows Management Framework 3.0.

Обвивки на TaskFactory, както следва:

<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

  <!-- This custom task encapsulates DAC Framework 3.0 BACPAC import and export routines,
       which are compatible with SQL Server 2012 and later, and SQL Azure -->

  <!-- Required Import to use MSBuild Extension Pack -->
  <PropertyGroup>
    <AssemblyFile>$(MSBuildExtensionsPath)\ExtensionPack\4.0\MSBuild.ExtensionPack.TaskFactory.PowerShell.dll</AssemblyFile>
    <SqlServerDacDll>C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll</SqlServerDacDll>
  </PropertyGroup>

  <UsingTask TaskFactory="PowershellTaskFactory" TaskName="ExportBacpac" AssemblyFile="$(AssemblyFile)">
    <ParameterGroup>
      <ConnectionString Required="true" ParameterType="System.String" />
      <BacpacFile Required="true" ParameterType="System.String" /> 
      <DatabaseName Required="true" ParameterType="System.String" />
    </ParameterGroup>
    <Task>
      <![CDATA[
            #write progress to activity log
            $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High","Starting export of database '$databasename' to '$bacpacfile' with connection string '$connectionstring' ")

            # load in DAC DLL (requires config file to support .NET 4.0)
            # change file location for a 32-bit OS
            add-type -path "$(SqlServerDacDll)"

            # make DacServices object, needs a connection string
            $d = new-object Microsoft.SqlServer.Dac.DacServices $connectionstring

            # register events, if you want 'em
            register-objectevent -in $d -eventname Message -source "msg" -action { $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High", $Event.SourceArgs[1].Message.Message) }

            # Export schema and data from database $databasename
            $d.exportbacpac($bacpacfile, $databasename)

            # clean up event
            unregister-event -source "msg"

            $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High","Completed export of database '$databasename' to '$bacpacfile'")
      ]]>
    </Task>
  </UsingTask>

  <UsingTask TaskFactory="PowershellTaskFactory" TaskName="ImportBacpac" AssemblyFile="$(AssemblyFile)">
    <ParameterGroup>
      <ConnectionString Required="true" ParameterType="System.String" />
      <BacpacFile Required="true" ParameterType="System.String" /> 
      <DatabaseName Required="true" ParameterType="System.String" /><!-- Not relevant for Azure import, which uses the Bacpac file name as the database name -->
    </ParameterGroup>
    <Task>
      <![CDATA[
            #write progress to activity log
            $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High","Starting import of database '$databasename' from '$bacpacfile' with connection string '$connectionstring' ")

            # load in DAC DLL (requires config file to support .NET 4.0)
            # change file location for a 32-bit OS
            add-type -path "$(SqlServerDacDll)"

            # make DacServices object, needs a connection string
            $d = new-object Microsoft.SqlServer.Dac.DacServices $connectionstring

            # register events, if you want 'em
            register-objectevent -in $d -eventname Message -source "msg" -action { $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High", $Event.SourceArgs[1].Message.Message) }

            # Load bacpac from file & import to database named $databasename
            $bp = [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacfile)
            $d.importbacpac($bp, $databasename)

            # clean up event
            unregister-event -source "msg"

            $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High","Completed import of database '$databasename' from '$bacpacfile'")
      ]]>
    </Task>
  </UsingTask>

</Project>

Примерна цел за извикване на тези обвивки ще изглежда по следния начин:

<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <Import Project="BacpacImportExport.xml"/>

  <PropertyGroup>
    <TempBacpacFile>$(ReleaseFolderPublish)\$(DestinationDBName).bacpac</TempBacpacFile>
  </PropertyGroup>

  <Target Name="CopyAndReplaceDatabaseViaBacpac">
    <Message Text="Clean bacpac directory"/>
    <Exec Command="mkdir $(ReleaseFolderPublish)\" IgnoreExitCode="true"></Exec>
    <Exec Command="del /Q $(ReleaseFolderPublish)\*.bacpac " IgnoreExitCode="true"></Exec>

    <MSBuild Projects="$(MSBuildProjectFile)" Targets="ReportBuildProgress" Properties="Message=Exporting database to BACPAC from source"/>
    <ExportBacpac
      ConnectionString="$(SourceConnectionString)"
      BacpacFile="$(TempBacpacFile)"
      DatabaseName="$(SourceDBName)"
    />


    <MSBuild Projects="$(MSBuildProjectFile)" Targets="ReportBuildProgress" Properties="Message=Dropping database from destination (does not fail on error)"/>
    <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="$(DestinationDBServer)" Database="master"
                                            LogOn="$(DestinationDBUser)" Password="$(DestinationDBPass)"
                                            CommandLineQuery="DROP DATABASE [$(DestinationDBName)];"
                                            RedirectStandardError="true" SeverityLevel="1" />


    <MSBuild Projects="$(MSBuildProjectFile)" Targets="ReportBuildProgress" Properties="Message=Importing database from BACPAC to destination"/>
    <ImportBacpac
      ConnectionString="$(DestinationConnectionString)"
      BacpacFile="$(TempBacpacFile)"
      DatabaseName="$(DestinationDBName)"
    />

  </Target>
</Project>

Това може лесно да бъде променено, за да се използва Хостван DAC от извикване на референтната реализация конзолно приложение чрез <Exec Command="" /> вместо извикване <ImportBacpac />.

Ако забележите някакви подобрения, уведомете ме!