Module renamed

DrJohnT · Mar 20, 2019 · 9c40f2a · 9c40f2a
1 parent 6da13b3
commit 9c40f2a
Show file tree

Hide file tree

Showing 37 changed files with 1,112 additions and 2 deletions.
diff --git a/PublishDacPac.psd1 b/PublishDacPac.psd1
@@ -0,0 +1,81 @@
+#
+# Module manifest for module 'PublishDacPac'
+#
+# Generated by: Dr. John Tunnicliffe
+#
+# Generated on: 20/03/2019
+#
+
+@{
+
+# Script module or binary module file associated with this manifest.
+RootModule = 'PublishDacPac.psm1'
+
+# Version number of this module.
+ModuleVersion = '1.0.2'
+
+# ID used to uniquely identify this module
+GUID = '12957ebe-7de8-4bf6-9b19-c07596b04f9f'
+
+# Author of this module
+Author = 'Dr. John Tunnicliffe'
+
+# Company or vendor of this module
+CompanyName = 'Decision Analytics'
+
+# Copyright statement for this module
+Copyright = '(c) 2019 Dr. John Tunnicliffe. All rights reserved.'
+
+# Description of the functionality provided by this module
+Description = 'Publish your SQL Database DACPAC using a DAC Publish Profile'
+
+# Minimum version of the Windows PowerShell engine required by this module
+PowerShellVersion = '5.0'
+
+# Functions to export from this module, for best performance, do not use wildcards and do not delete the entry, use an empty array if there are no functions to export.
+FunctionsToExport = @(
+    'Publish-DacPac',
+    'Select-SqlPackageVersion',
+    'Get-SqlPackagePath',
+    'Ping-SqlDatabase',
+    'Ping-SqlServer'
+)
+
+# Cmdlets to export from this module, for best performance, do not use wildcards and do not delete the entry, use an empty array if there are no cmdlets to export.
+CmdletsToExport = @()
+
+# Variables to export from this module
+VariablesToExport = @()
+
+# Aliases to export from this module, for best performance, do not use wildcards and do not delete the entry, use an empty array if there are no aliases to export.
+AliasesToExport = @()
+
+# Private data to pass to the module specified in RootModule/ModuleToProcess. This may also contain a PSData hashtable with additional module metadata used by PowerShell.
+PrivateData = @{
+
+    PSData = @{
+
+        # Tags applied to this module. These help with module discovery in online galleries.
+        Tags = @("SSDT","deployment","DACPAC","deploy","publish","SQL","database","DAC","sqlserver","Profile","Azure","DevOps","SqlPackage","powershell","pipeline","release","data-tier","on-premise","azure","automation")
+
+        # A URL to the license for this module.
+        LicenseUri = 'https://github.com/DrJohnT/PublishDacPac/blob/master/LICENSE'
+
+        # A URL to the main website for this project.
+        ProjectUri = 'https://github.com/DrJohnT/PublishDacPac'
+
+        # A URL to an icon representing this module.
+        # IconUri = ''
+
+        # ReleaseNotes of this module
+        ReleaseNotes = 'https://github.com/DrJohnT/PublishDacPac/releases/tag/1.0.1'
+
+    } # End of PSData hashtable
+
+} # End of PrivateData hashtable
+
+# HelpInfo URI of this module
+HelpInfoURI = 'https://github.com/DrJohnT/PublishDacPac/wiki'
+
+}
+
diff --git a/PublishDacPac.psm1 b/PublishDacPac.psm1
@@ -0,0 +1,28 @@
+#handle PS2
+    if(-not $PSScriptRoot)
+    {
+        $PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent
+    }
+
+#Get public and private function definition files.
+    $scripts =  Get-ChildItem "$PSScriptRoot\public" -Recurse -Include *.ps1 -Exclude Tests;
+
+#Dot source the files
+    Foreach($script in $scripts)
+    {
+        Try
+        {
+            $scriptName = (Split-Path -Leaf $script) -replace ".ps1", "";
+
+            if (!($scriptName -like "*Tests")) {
+                . $script.fullname
+                Write-Verbose "Loading $scriptName"
+            }
+        }
+        Catch
+        {
+            Write-Error -Message "Failed to import function $($script.fullname): $_"
+        }
+    }
+
+Export-ModuleMember -Function ($scripts | Select-Object -ExpandProperty Basename);
diff --git a/PublishPSGalleryPackage.ps1 b/PublishPSGalleryPackage.ps1
@@ -0,0 +1,23 @@
+param (
+    [Parameter(Mandatory)]
+    [string] $ApiKey
+)
+
+
+$VerbosePreference = 'Continue'
+$ErrorActionPreference = 'Stop'
+$baseDir = $PSScriptRoot
+
+try {
+    $buildDir = "$baseDir\build\psgallery\Pester"
+    Write-Verbose 'Importing PowerShellGet module'
+    $psGet = Import-Module PowerShellGet -PassThru -Verbose:$false
+    & $psGet { [CmdletBinding()] param () Install-NuGetClientBinaries -CallerPSCmdlet $PSCmdlet -BootstrapNuGetExe -Force }
+
+    Write-Host 'Publishing module to PowerShellGet'
+    $null = Publish-Module -Path $buildDir -NuGetApiKey $ApiKey -Confirm:$false
+}
+catch {
+    Write-Error -ErrorRecord $_
+    exit 1
+}
diff --git a/README.md b/README.md
@@ -1,2 +1,107 @@
-# PublishDacPac
-Publish-DacPac
+[![Build status](https://qatar-re.visualstudio.com/QatarRe.BI/_apis/build/status/Pester%20Test)](https://qatar-re.visualstudio.com/QatarRe.BI/_build/latest?definitionId=51)
+
+### PublishDacPac
+
+# Deploy a SQL Database from a DACPAC
+
+## Overview
+
+**Publish-DacPac** allows you to deploy a SQL Server Database using a DACPAC to a SQL Server instance.
+
+SSDT (SQL Server Data Tools) is Microsoft's tool to design (declare) the entire database model including tables, views, stored procedures, functions, schemas, etc. etc. etc.  SSDT covering **all** aspects of a database design.
+
+SSDT is now fully integrated into Visual Studio.  When you perform a **build** of a SSDT Visual Studio project, it creates a [DACPAC](https://msdn.microsoft.com/en-IN/library/ee210546.aspx) which defines all of the SQL Server objects - like tables, views, and instance objects, including logins - associated with a database.
+
+**Publish-DacPac** simplifies the use of [SqlPackage.exe](https://docs.microsoft.com/en-us/sql/tools/sqlpackage) to deploy a [DACPAC](https://msdn.microsoft.com/en-IN/library/ee210546.aspx) by using a **DAC Publish Profile** which provides for fine-grained control over the database creation and upgrades, including upgrades for schema, triggers, stored
+procedures, roles, users, extended properties etc. Using a DAC Publish Profile, multiple
+different properties can be set to ensure that the database is created or
+upgraded properly.
+
+**Publish-DacPac** compares the content of a DACPAC to the database already on the target server and generates a deployment script.  You can tailor how publish works using a DAC Publish Profile.
+
+**Publish-DacPac** can be used to automate the deployment of databases, either as part of a build in Azure DevOps, or part of a server deployment using Octopus Deploy or Azure DevOps Release Manager.
+
+To automate build and deployment of databases in Azure DevOps, you can use MsBuild to create DACPAC from your Visual Studio solution.  You can then add a PowerShell task which uses **Publish-DacPac** to invoke SQLPackage.exe to deploy each DACPAC using your own custom DAC Publish Profile.
+
+[DAC Publish Profiles](https://github.com/DrJohnT/PublishDacPac/wiki/DAC-Publish-Profile) are created in Visual Studio when you Publish a database.
+
+## Install
+
+Install from PowerShell gallery using:
+
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ powershell
+Install-Module -Name PublishDacPac
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+## Usage
+
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ powershell
+Publish-DacPac -DacPacPath "C:\Dev\YourDB\bin\Debug\YourDB.dacpac" -DacPublishProfile "YourDB.CI.publish.xml" -TargetServerName "YourDBServer"
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+Where -DacPacPath is the path to your database DACPAC, -DacPublishProfile is the name of the [DAC Publish Profile](https://github.com/DrJohnT/PublishDacPac/wiki/DAC-Publish-Profile) to be found in the same folder as your DACPAC, and -TargetServerName is the name of the target server (including instance and port if required).  The above is the minimum set of parameters that can be used with **Publish-DacPac**.
+
+Normally, the database will be named the same as your DACPAC (i.e. YourDB in the example above).  However, by adding the -TargetDatabaseName parameter, you can name the database anything you like.
+
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ powershell
+Publish-DacPac -DacPacPath "C:\Dev\YourDB\bin\Debug\YourDB.dacpac" -DacPublishProfile "YourDB.CI.publish.xml" -TargetServerName "YourDBServer" -TargetDatabaseName "YourNewNameDB"
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+You can also provide the full path to an alternative [DAC Publish Profile](https://github.com/DrJohnT/PublishDacPac/wiki/DAC-Publish-Profile).
+
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ powershell
+Publish-DacPac -DacPacPath "C:\Dev\YourDB\bin\Debug\YourDB.dacpac" -DacPublishProfile "C:\Dev\YourDB\bin\Debug\YourDB.CI.publish.xml" -TargetServerName "YourDBServer"
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+Finnally, if there are multiple versions of SqlPackage.exe installed on your build agent, you can specify which version should be used with the  -PreferredVersion option.
+
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ powershell
+Publish-DacPac -DacPacPath "C:\Dev\YourDB\bin\Debug\YourDB.dacpac" -DacPublishProfile "C:\Dev\YourDB\bin\Debug\YourDB.CI.publish.xml" -TargetServerName "YourDBServer" -PreferredVersion latest
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+## List of commands
+
+The following is a list of commands provided by this module once you
+follow the steps in Installation
+
+| **Function**              | **Description**                                                             |
+|--------------------------|-----------------------------------------------------------------------------|
+| Publish-DacPac           | Publishes a DACPAC using a [DAC Publish Profile](https://github.com/DrJohnT/PublishDacPac/wiki/DAC-Publish-Profile)  |
+| Select-SqlPackageVersion | Finds a specific version of SqlPackage.exe               |
+| Get-SqlPackagePath       | Returns the path of a specific version of SqlPackage.exe |
+| Ping-SqlServer           | Checks if a specific SQL Server instance is available             |
+| Ping-SqlDatabase         | Checks if a database exists on a SQL Server              |
+
+## Pre-requisites
+
+The following pre-requisites need to be installed for **Publish-DacPac** to work properly.
+
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ powershell
+SqlPackage.exe
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+[SqlPackage.exe](https://docs.microsoft.com/en-us/sql/tools/sqlpackage) is installed when you install Visual Studio or SQL Server.  You can also install SqlPackage.exe using a dedicated installer as outlined below.
+
+## Azure DevOps Agent
+
+**Publish-DacPac** can be run on an in-house hosted Azure DevOps agent when once [SqlPackage.exe](https://docs.microsoft.com/en-us/sql/tools/sqlpackage) is installed:
+
+* By installing SQL Server 2012 or later
+
+* By installing Visual Studio 2012 or later
+
+* By installing the [Microsoft® SQL Server® Data-Tier Application Framework](https://docs.microsoft.com/en-us/sql/tools/sqlpackage-download)
+
+Be aware that it is best to install the latest
+[SQLPackage.exe](https://docs.microsoft.com/en-us/sql/tools/sqlpackage-download)
+as this provides support for all previous versions of SQL Server as well as the forthcoming SQL Server 2019.
+
+## Example SSDT DACPAC
+
+An example SSDT Visual Studio solution and the associated DACPAC is provided in the .\media folder.  You can use this to test that deployments work correctly.  Note that the SSDT Visual Studio solution is configured to deploy to SQL Server 2016.  Open the Visual Studio solution and change the target version and rebuild the solution if you have a different version of SQL Server installed.
+
+### Issue Reporting
+
+If you are facing problems in making this PowerShell module work, please report any
+problems on [PublishDacPac GitHub Project
+Page](https://github.com/DrJohnT/PublishDacPac/issues).
diff --git a/RequiredModules.psd1 b/RequiredModules.psd1
@@ -0,0 +1,7 @@
+@(
+    @{ ModuleName = "PackageManagement"; RequiredVersion = "1.3.1"}
+    @{ ModuleName = "PowerShellGet"; RequiredVersion = "2.1.2"}
+    @{ ModuleName = "InvokeBuild"; RequiredVersion = "5.4.5"}
+    @{ ModuleName = "Pester"; RequiredVersion = "4.7.2"}
+    @{ ModuleName = "PSScriptAnalyzer"; RequiredVersion = "1.17.1"}
+)
diff --git a/bootstrap.ps1 b/bootstrap.ps1
@@ -0,0 +1,17 @@
+using namespace Microsoft.PowerShell.Commands
+[CmdletBinding()]
+param(
+    [ValidateSet("CurrentUser", "AllUsers")]
+    $Scope = "CurrentUser"
+)
+[ModuleSpecification[]]$RequiredModules = Import-LocalizedData -BaseDirectory $PSScriptRoot -FileName RequiredModules
+$Policy = (Get-PSRepository PSGallery).InstallationPolicy
+Set-PSRepository PSGallery -InstallationPolicy Trusted
+
+try {
+    $RequiredModules | Install-Module -Scope $Scope -Repository PSGallery -SkipPublisherCheck -Verbose;
+}
+finally {
+    Set-PSRepository PSGallery -InstallationPolicy $Policy
+}
+$RequiredModules | Import-Module
diff --git a/media/DatabaseToPublish/.vs/DatabaseToPublish/v15/.suo b/media/DatabaseToPublish/.vs/DatabaseToPublish/v15/.suo
diff --git a/media/DatabaseToPublish/DatabaseToPublish.CI.publish.xml b/media/DatabaseToPublish/DatabaseToPublish.CI.publish.xml
@@ -0,0 +1,12 @@
+<?xml version="1.0" encoding="utf-8"?>
+<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
+  <PropertyGroup>
+    <IncludeCompositeObjects>True</IncludeCompositeObjects>
+    <TargetDatabaseName>DatabaseToPublish</TargetDatabaseName>
+    <DeployScriptFileName>DatabaseToPublish.sql</DeployScriptFileName>
+    <TargetConnectionString>Data Source=Build02;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
+    <ProfileVersionNumber>1</ProfileVersionNumber>
+    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
+    <CreateNewDatabase>True</CreateNewDatabase>
+  </PropertyGroup>
+</Project>
diff --git a/media/DatabaseToPublish/DatabaseToPublish.LOCAL.publish.xml b/media/DatabaseToPublish/DatabaseToPublish.LOCAL.publish.xml
@@ -0,0 +1,10 @@
+<?xml version="1.0" encoding="utf-8"?>
+<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
+  <PropertyGroup>
+    <IncludeCompositeObjects>True</IncludeCompositeObjects>
+    <TargetDatabaseName>DatabaseToPublish</TargetDatabaseName>
+    <DeployScriptFileName>DatabaseToPublish.sql</DeployScriptFileName>
+    <TargetConnectionString>Data Source=Build02;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
+    <ProfileVersionNumber>1</ProfileVersionNumber>
+  </PropertyGroup>
+</Project>
diff --git a/media/DatabaseToPublish/DatabaseToPublish.dbmdl b/media/DatabaseToPublish/DatabaseToPublish.dbmdl
diff --git a/media/DatabaseToPublish/DatabaseToPublish.sln b/media/DatabaseToPublish/DatabaseToPublish.sln
@@ -0,0 +1,27 @@
+
+Microsoft Visual Studio Solution File, Format Version 12.00
+# Visual Studio 15
+VisualStudioVersion = 15.0.28307.489
+MinimumVisualStudioVersion = 10.0.40219.1
+Project("{00D1A9C2-B5F0-4AF3-8072-F6C62B433612}") = "DatabaseToPublish", "DatabaseToPublish.sqlproj", "{692977CE-0842-4424-B08A-FE676674A66D}"
+EndProject
+Global
+	GlobalSection(SolutionConfigurationPlatforms) = preSolution
+		Debug|Any CPU = Debug|Any CPU
+		Release|Any CPU = Release|Any CPU
+	EndGlobalSection
+	GlobalSection(ProjectConfigurationPlatforms) = postSolution
+		{692977CE-0842-4424-B08A-FE676674A66D}.Debug|Any CPU.ActiveCfg = Debug|Any CPU
+		{692977CE-0842-4424-B08A-FE676674A66D}.Debug|Any CPU.Build.0 = Debug|Any CPU
+		{692977CE-0842-4424-B08A-FE676674A66D}.Debug|Any CPU.Deploy.0 = Debug|Any CPU
+		{692977CE-0842-4424-B08A-FE676674A66D}.Release|Any CPU.ActiveCfg = Release|Any CPU
+		{692977CE-0842-4424-B08A-FE676674A66D}.Release|Any CPU.Build.0 = Release|Any CPU
+		{692977CE-0842-4424-B08A-FE676674A66D}.Release|Any CPU.Deploy.0 = Release|Any CPU
+	EndGlobalSection
+	GlobalSection(SolutionProperties) = preSolution
+		HideSolutionNode = FALSE
+	EndGlobalSection
+	GlobalSection(ExtensibilityGlobals) = postSolution
+		SolutionGuid = {764A69E5-0727-40CF-9610-3B18B82484E7}
+	EndGlobalSection
+EndGlobal