使用Powershell的Microsoft SQL Server非包含对象迁移部署过程

As DBAs, we all face a lot of challenges while performing the migration of SQL Server databases from one server to another or even the whole servers at some times. The Database Migration process is not always a simple Backup and Restore process so we might need a huge amount of effort if we have to migrate objects which are not included in the native backups for a specified database, these objects are called Non-Contained Objects.

作为DBA，我们有时在执行SQL Server数据库从一台服务器到另一台甚至整个服务器的迁移时都面临许多挑战。 数据库迁移过程并不总是一个简单的备份和还原过程，因此如果我们必须迁移指定数据库的本机备份中未包含的对象，则可能需要大量的工作，这些对象称为非包含对象。

I will give you detailed, yet simple and comprehensive information here, about migration of SQL Server non-contained object migration which is core part of Database Migration and required in mostly Disaster Recovery implementations.

在这里，我将为您提供有关SQL Server非包含对象迁移的详细而又简单而全面的信息，这是数据库迁移的核心部分，在大多数灾难恢复实现中都是必需的。

So, before going into the details I would like to present some scenario for you and you will find a good example where a simple backup and restore will not be enough.

因此，在详细介绍之前，我想为您介绍一些方案，您将找到一个很好的示例，简单的备份和还原是不够的。

Scenario in which we might use the below mentioned approach is given below:

下面给出了可以使用以下提到的方法的方案：

You have couple of databases on a Source Server A and you need to create Always On Availability Group Replica on Destination Server B for full disaster recovery solution. After you configure each and every detail of AG (Availability Group) and perform and full failover of databases and bam! there you go, all the security object, jobs, alerts, Operators, PBMs, LinkedServers, Database Mail Settings, SQL Server User objects in Systems Databases or permissions are missing.

您在源服务器A上有几个数据库，并且需要在目标服务器B上创建Always On可用性组副本才能获得完整的灾难恢复解决方案。 配置完AG（可用性组）的每个细节并执行数据库和bam的全面故障转移之后！ 到那里，系统数据库中的所有安全对象，作业，警报，操作员，PBM，LinkedServer，数据库邮件设置，SQL Server用户对象或权限都丢失了。

So, what will you do? Will you transfer everything manually? Okay for one server you can but what if you have 50 servers with AG configured? Now you need some more robust and intelligent yet automated solution for transferring Non-Contained Object Migration. And Powershell is the solution here and there is nothing you can do in Powershell which you can perform in SQL server. Even backing up the SQL Server Replication for Disaster Recovery which is not natively supported. I won’t explain it here as that will be out of the scope of current article.

那么，你会怎么做？ 您会手动传输所有内容吗？ 可以使用一台服务器，但是如果您配置了50台服务器，那该怎么办？ 现在，您需要一些更健壮和智能的自动化解决方案来传输非包含对象迁移。 Powershell是这里的解决方案，您无法在Powershell中做任何事情，而可以在SQL Server中执行。 甚至备份本机不支持SQL Server Replication for Disaster Recovery。 我将不在这里解释，因为这超出了本文的范围。

So, here is the solution you will need in the scenario discussed above.

因此，这是您在上述情况下需要的解决方案。

Some of the most common object which are required in migration process but not included in the backups are security object, jobs, alerts, Operators, PBMs, Linked Servers, Database Mail Settings, SQL Server User objects in Systems Databases and permissions.

迁移过程中需要但备份中未包括的一些最常见的对象是安全对象，作业，警报，操作员，PBM，链接服务器，数据库邮件设置，系统数据库中SQL Server用户对象和权限。

The quick way to install the Powershell modules produced by DBA Tools Team. Thanks to the “DBA Tools” Team and extremely hard working DBAs working for community work on this site that we are able to do this easily like a piece of cake using PowerShell Modules. DBA Tools is a free PowerShell toolkit and it’s free to download from the URL. The Toolkit is really vast and has a variety of options available. I have used only eight of the commands but registered ALL the modules in the PowerShell windows. 

安装DBA Tools Team生产的Powershell模块的快速方法。 感谢“ DBA工具 ”团队和在此站点上为社区工作而努力工作的DBA，我们能够像使用PowerShell模块一样轻松地完成此任务。 DBA工具是一个免费的PowerShell工具包，可以从URL免费下载。 该工具包确实非常庞大，并且具有多种可用选项。 我只使用了八个命令，但是在PowerShell窗口中注册了所有模块。

Here is a way to deploy the DBA Tools and use them for your environment for Non-Contained Object Migration in SQL Server.  

这是一种部署DBA工具并将其用于您的环境以在SQL Server中进行非包含对象迁移的方法。

1. • Registration of PowerShell modules in windows
   • Setting the right Execution Policy
   • Commands for migration of non-contained objects
   • Automating the migration

1. 在Windows中注册PowerShell模块
2. 制定正确的执行政策
3. 迁移非包含对象的命令
4. 自动化迁移

1. ()

2. Before we can use any command from the PowerShell modules from dbatools in windows we need to register it in the windows.

   在Windows中dbatools的PowerShell模块中使用任何命令之前，我们需要在Windows中注册它。

   Registering is a very easy setup, you just need to copy the folder containing the modules to the windows path where PowerShell is hosted. Mostly the path is as follows and you can find it easily by starting the PowerShell ISE and taking properties and getting path from there.

   注册是一个非常简单的设置，您只需要将包含模块的文件夹复制到托管PowerShell的Windows路径。 通常，路径如下，您可以通过启动PowerShell ISE并获取属性并从中获取路径轻松找到它。

   “C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules”

   “ C：\ WINDOWS \ system32 \ WindowsPowerShell \ v1.0 \ Modules”

   Source Folder containing the dbatools which will be copied to the destination.

   包含dbatools的源文件夹，它将被复制到目标位置。

   

   Destination folder where you need to copy the modules where other modules (windows PowerShell) will be residing.

   您需要在其中复制其他模块（Windows PowerShell）所在模块的目标文件夹。

   

3. ()

4. On the PowerShell ISE before starting the execution of the dbatools scripts you need to set the execution policy to bypass to allow the custom scripts to be executed. For this purpose, you need to issue the command on powershell cmd or ISE and accept it as YES when prompted.

   在开始执行dbatools脚本之前，在PowerShell ISE上，您需要将执行策略设置为绕过，以允许执行自定义脚本。 为此，您需要在powershell cmd或ISE上发出命令，并在出现提示时将其接受为YES。

   Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

   Set-ExecutionPolicy-范围过程-ExecutionPolicy绕过

   After registration and setting the execution policy right, you can use any command and like Copy-SqlLogin and specify source and destination servers and it will migrate.

   注册并设置执行策略权限后，您可以使用任何命令（如Copy-SqlLogin）并指定源服务器和目标服务器，它将迁移。

   In SQL Server AlwaysOn Availability Groups as a Disaster Recovery Solution, migrating the Non-Contained Objects more often would be preferred and keep in mind that we can only transfer the Non-Contained Objects as the destination database are already in Read-Only mode on any of the Secondary Replica and all the Contained Objects will automatically be migrated any way.

   在作为灾难恢复解决方案SQL Server AlwaysOn可用性组中，将首选更频繁地迁移非包含对象，并且请记住，由于目标数据库已经在任何数据库上处于只读模式，因此我们只能传输非包含对象。辅助副本和所有包含的对象将自动以任何方式迁移。

5. ()

6. 

   Copy-SqlLogin -Source MySourceServer -Destination MyDestinationServer -Force Copy-SqlJob -Source MySourceServer -Destination MyDestinationServer -Force Copy-SqlOperator -Source MySourceServer -Destination MyDestinationServer -Force Copy-SqlAlert -Source MySourceServer -Destination MyDestinationServer -Force Copy-SqlDatabaseMail -Source MySourceServer -Destination MyDestinationServer -Force Copy-SqlLinkedServer -Source MySourceServer -Destination MyDestinationServer –Force Copy-SqlSysDbUserObjects -Source MySourceServer -Destination MyDestinationServer Sync-SqlLoginPermissions -Source MySourceServer -Destination MyDestinationServer

   Copy-SqlLogin -Source MySourceServer -Destination MyDestinationServer -Force Copy-SqlJob -Source MySourceServer -Destination MyDestinationServer -Force Copy-SqlOperator -Source MySourceServer -Destination MyDestinationServer -Force Copy-SqlAlert -Source MySourceServer -Destination MyDestinationServer -Force Copy-SqlDatabaseMail- -Destination MyDestinationServer-强制 复制-SqlLinkedServer -Source MySourceServer -Destination MyDestinationServer-强制 复制-SqlSysDbUserObjects -Source MySourceServer -Destination MyDestinationServer Sync-SqlLoginPermissions -Source MySourceServer -Destination MyDestinationServer

   Almost all the commands which I have used are using the optional parameter “Force” which will enforce that the object mentioned will be dropped and re-created. In my environment its feasible so if you don’t want to drop/re-create the objects then it’s your choice you can easily remove the –Force optional parameter and get only the merge functionality.

   我使用的几乎所有命令都使用可选参数“ Force”，该参数将强制删除提到的对象并重新创建它。 在我的环境中，它是可行的，因此，如果您不想删除/重新创建对象，则可以轻松删除–Force可选参数并仅获取合并功能，这是您的选择。

7. ()

8. To Automate the migration process, simply place the above mentioned commands with your actual server names in a .ps1 (PowerShell File) file and place the file anywhere which can be accessed by your Source/Destination Server on which you want to create the automation jobs.

   要自动化迁移过程，只需将上述命令和您的实际服务器名称放在.ps1（PowerShell文件）文件中，然后将该文件放置在您要在其上创建自动化作业的源/目标服务器可以访问的任何位置。

   After placing the file in a location you can simply call that file in TSQL and schedule it to run over the weekend or any time which is suitable for your load.

   将文件放置在某个位置后，您可以简单地在TSQL中调用该文件并将其安排为在周末或适合您的负载的任何时间运行。

    
   DECLARE @SQLString nVARCHAR(1000)
   SET		@SQLString = 'powershell.exe -file "X:\Poweshell\Migrate Non Contained Objects to DR.ps1" '
   EXEC xp_cmdshell @SQLString 
   GO
    
   

   Keep in mind that whenever creating the logins the above mentioned commands will re-create the sql logins and while re-creating them the connection to the DR will be dropped so if you have any secondary replica reading applications then keep in mind that the object migration should only be done while a non-production hour for the replica.

   请记住，无论何时创建登录名，上述命令都将重新创建sql登录名，并且在重新创建sql登录名时，将断开与DR的连接，因此，如果您有任何辅助副本读取应用程序，那么请记住，对象迁移仅应在副本的非生产时间进行。

   The job will take some time between 30-40 minutes (in my environment) or more depending upon the network latency and number of objects to migrate.

   根据网络延迟和要迁移的对象数量，该作业将花费30-40分钟（在我的环境中）或更长的时间。

   The first run should be monitored and should only be executed by experienced DBAs and after detailed verification you can deploy it and then make your life easier by having a confidence that now you have each and everything replicated.

   应该对第一次运行进行监视，并且仅应由经验丰富的DBA执行，并且在进行详细验证之后，您可以对其进行部署，然后确信自己已复制了所有内容，从而使生活变得更加轻松。

   So, Powershell with this module in combination with AG is really a great match. It will save a lot of time and manual effort in case of actual disaster.

   因此，将此模块与AG结合使用的Powershell确实是一个很好的选择。 如果发生实际灾难，它将节省大量时间和人工。

翻译自: https://www.sqlshack.com/microsoft-sql-server-non-contained-object-migration-deployment-procedure-using-powershell/