MSSQL脚本自动生成与发布实践-猿码集

1. 概述

MSSQL脚本自动生成与发布是一种自动化的方法，它可以减少手动操作的错误和时间，提高效率和一致性。在本文中，我们将介绍如何建立一个基础架构来实现MSSQL脚本自动生成和发布流程。

2. 准备工作

2.1 创建数据库

首先要为我们的脚本创建一个数据库。为此，我们需要在MSSQL Server Management Studio中执行以下脚本：


CREATE DATABASE ScriptDB;
GO

2.2 创建目录结构

为了容易管理，我们将在文件系统中创建以下目录结构：


C:\
 |
 +--Scripts\
 |  |
 |  +--Production\
 |  |  |
 |  |  +--Tables\
 |  |  |
 |  |  +--StoredProcedures\
 |  |  |
 |  |  +--Views\
 |  |
 |  +--QA\
 |  |  |
 |  |  +--Tables\
 |  |  |
 |  |  +--StoredProcedures\
 |  |  +--Views\
 |  |
 |  +--Dev\
    |
    +--Tables\
    +--StoredProcedures\
    +--Views\

按照上述目录结构，我们可以将产生的SQL脚本归类到具有不同用途的子目录中。例如，根据目录结构，存储过程应该放在“StoredProcedures”子目录下，视图和表应该相应地放在“Views”和“Tables”子目录下。

2.3 创建源代码版本控制仓库

使用源代码管理工具(Git)创建单独的版本控制仓库(ScriptDB)来管理我们的所有SQL脚本。

3. 使用PowerShell发布脚本

3.1 安装PowerShell脚本

要使用PowerShell自动化发布脚本，我们需要安装SqlServer模块。有以下几种方法安装。

安装方式一： Install-Module -Name SqlServer -AllowClobber 安装方式二： go to the link: https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15

3.2 实现PowerShell脚本

我们将得到自动化脚本的基础架构，下面我们将实现PowerShell脚本。

3.2.1 脚本变量定义

在使用PowerShell和SqlServer模块之前，我们需要定义以下变量：


$ServerName = 'localhost'
$DatabaseName = 'ScriptDB'
$ScriptPath = 'C:\Scripts\Production'

变量$ServerName和$DatabaseName分别用于指定连接到哪个MSSQL数据库。变量$ScriptPath用于指定要发布的脚本的路径。变量$ScriptPath指向“Production”子目录，其中包含与生产环境相关的脚本。

3.2.2 建立目录连接

PowerShell脚本将连接到ScriptDB Git存储库并检查有关目录的信息，这可以通过以下命令来完成：


$SecurePassword = ConvertTo-SecureString "$PwdChars" -AsPlainText -Force
$Credentials = New-Object System.Management.Automation.PSCredential ("Git用户名",$SecurePassword)
git config --global credential.helper store
Set-Location $ScriptPath
$branch = Get-ChildItem -Path $ScriptPath | Where-Object {$_.PSIsContainer -eq $true} | Select-Object -Last 1
$dataSyncBranch = $branch.FullName.Replace($ScriptPath, "")
$dataSyncBranch

第一行是一个命令，它将从控制台获取密码并将其转换为安全字符串。第二行创建PS凭据对象。之后是Git用户凭据（用户名和密码）。在第三行中，我们将确保访问Git存储库不用手动输入凭据。第四行设置ScriptDB Git存储库的位置。接下来，我们从子目录列表中选出最后一个目录，作为我们的主分支。在最后一行，我们为数据同步存储库设置了$ dataSyncBranch变量，该变量仅是最后一个子目录的名称。

3.2.3 数据库对象枚举

现在，脚本将从指定的数据存储库中获取表，存储过程和视图的信息。以下是此操作的代码：


$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$ServerName;Initial Catalog=$DatabaseName;Integrated Security=True")
$command = New-Object System.Data.SqlClient.SqlCommand
$command.Connection = $conn
$conn.Open()
$Tables = @()
$command.CommandText = @"
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA NOT IN ('sys', 'Information_schema')
"@
$reader = $command.ExecuteReader()
while ($reader.Read()) {
    $obj = New-Object PSObject
    $obj | Add-Member -MemberType NoteProperty -Name SchemaName -Value $reader[0]
    $obj | Add-Member -MemberType NoteProperty -Name ObjectName -Value $reader[1]
    $Tables += $obj
}
$reader.Close()
$Procedures = @()
$command.CommandText = @"
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA NOT IN ('sys', 'INFORMATION_SCHEMA')
"@
$reader = $command.ExecuteReader()
while ($reader.Read()) {
    $obj = New-Object PSObject
    $obj | Add-Member -MemberType NoteProperty -Name SchemaName -Value $reader[0]
    $obj | Add-Member -MemberType NoteProperty -Name ObjectName -Value $reader[1]
    $Procedures += $obj
}
$reader.Close()
$Views = @()
$command.CommandText = @"
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA NOT IN ('sys', 'INFORMATION_SCHEMA')
"@
$reader = $command.ExecuteReader()
while ($reader.Read()) {
    $obj = New-Object PSObject
    $obj | Add-Member -MemberType NoteProperty -Name SchemaName -Value $reader[0]
    $obj | Add-Member -MemberType NoteProperty -Name ObjectName -Value $reader[1]
    $Views += $obj
}
$reader.Close()
$conn.Close()

在此代码段中，我们连接到指定的数据库，然后使用多个SQL查询枚举三种数据库对象：表，存储过程和视图。每个对象都将包含对象的架构和名称，以便稍后使用。

3.2.4 进行自动部署

现在，我们将使用Git来部署生产环境的脚本。以下是脚本实现的代码：


$branchName = "deploy/" + (Get-Date -Format 'yyyy-MM-dd_HH-mm-ss')
$commitMessage = "Deploy changes " + (Get-Date -Format 'yyyy-MM-dd HH:mm:ss')
git checkout $dataSyncBranch
git checkout -b $branchName
$Tables | ForEach-Object {
    $script = Get-Content "$ScriptPath\Production\tables\$($_.ObjectName).sql"
    $script | Out-File deploy.sql
    git add deploy.sql
    git commit -m "Deploy $_.ObjectName"
}
$Procedures | ForEach-Object {
    $script = Get-Content "$ScriptPath\Production\storedprocedures\$($_.ObjectName).sql"
    $script | Out-File deploy.sql
    git add deploy.sql
    git commit -m "Deploy $_.ObjectName"
}
$Views | ForEach-Object {
    $script = Get-Content "$ScriptPath\Production\views\$($_.ObjectName).sql"
    $script | Out-File deploy.sql
    git add deploy.sql
    git commit -m "Deploy $_.ObjectName"
}
git push origin $branchName
git checkout $dataSyncBranch

在此代码段中，我们将生产环境的脚本发布到Git仓库中。特别是，该脚本将连接到ScriptDB存储库，检查表，存储过程和视图的列表，然后将该列表中的所有对象的脚本提交为单独的Git提交。提交后，我们将$branchName变量设置为包含当前日期和时间的字符串。然后，我们将所有SQL脚本推送到Git，然后切换回master分支。

4. 结论

在本文中，我们介绍了如何使用PowerShell和SqlServer模块自动化MSSQL脚本发布流程。我们创建了一个基础架构，并使用PowerShell创建了一个脚本，以自动发布生产环境的SQL脚本。我们还建议使用源代码管理工具存储MSSQL脚本，并为此创建单独的数据同步存储库。

MSSQL脚本自动生成与发布实践