This is the second article in the series of Migrating SQL Server Objects using SSDT 2017. In this article, we will learn how to copy user stored procedures created in the SQL Server master database.

Demo Setup

For demonstration, I have executed the Ola-hallengren database maintenance script on a master database of Nisarg-PC\SQL01. You can download the latest version of the maintenance scripts from here. These scripts create some stored procedures and tables. Once the scripts were executed successfully, you can view them by executing the following query.

We are going to migrate the above stored procedures to the master database of the Nisarg-PC\SQL02 server.

Create integration service package

Now, first, let us create an integration service package. Open SSDT 2017 🡪 Click on Crete New Project 🡪 Select Integration services project. The name of the project will be Migrate Master database stored procedures.

Drag Transfer Master stored procedure task and drop it on control flow surface, rename it to Migrate Maintenance Stored Procedure.

Double-click on Transfer Master Stored Procedures task to configure it.

To transfer the stored procedure between instances, we must configure the source and destination connections. To do that, click on SourceConnection and select New Connection. First, let us see how we can configure the Source connection.

Configure SourceConnection

To configure the connection to the Source server, click on SourceConnection and select New connection.

In the SMO Connection Manager Editor dialog box, specify the Server name of the source and specify the authentication method used to connect to the SQL Server. In our demo, the Server name is Nisarg-PC\SQL01, and the authentication method is Windows Authentication.

Click OK to save the connection properties and close the dialog box.

Configure DestinationConnection

To configure the connection for the destination server, click on DestinationConnection and select New connection.

In the SMO Connection Manager Editor dialog box, specify the Server name of the source and specify the authentication method used to connect to the SQL Server. In our demo, the Server name is Nisarg-PC\SQL02, and the authentication method is Windows Authentication.

Click OK to save the connection properties and close the dialog box. The connections section looks like the following:

Under the stored procedure section, you get the following options:

1. IfObjectExists: When the object already exists which we are transferring, you can choose any of the following actions performed by the Transfer Master stored procedure task
   1. FailTask: If the stored procedure exists on the destination instance, the job will fail
   2. Overwrite: If the stored procedure exists on the destination server, the task will overwrite the existing stored procedure
   3. Skip: If the stored procedure exists on the destination server, the task will skip the transfer of that specific stored procedure
2. TransferAllStoredProcedures: If you want to transfer all user-defined store procedures, select True. If you want to transfer specific stored procedures, select False
3. StoredProcedureList: If you want to transfer a specific stored procedure, you can choose the SPs you want to transfer. This option enables when you select the False value for the TransferAllStoredProcedures parameter

We want to skip the transfer of the existing stored procedure in our demo, so select Skip. We want to transfer all stored procedures, so select True in the TransferAllStoredProcedures option.

The Transfer Master Stored Procedures Task Editor looks like the following image:

Now, we should be notified when stored procedures are transferred. To do that, we will configure the Notify Operator task.

Configure Notify Operator Task

First, drag the Notify operator task, drop it in the Control flow surface, rename it to Send an email, and double-click on it.

First, we must configure the connection between the server on which the operator has been created. To do that, click on the New in Notify Operator task dialog box. Another dialog box, Connect Properties, opens. Specify the appropriate values of the following parameters.

1. Connection Name: Specify the desired connection name. In our demo, I have named it as SQLConnection
2. Server Name: Specify the Server name on which the SQL Server operator has been created. I have already created an operator named DBASupport in Nisarg-PC\SQL01
3. Authentication Method: Specify the authentication method. In our demo, I have used windows authentication

Click OK to close the dialog box.

Specify the email subject in the Notification message subject textbox. In our demo, the subject line is Status of Migration of Stored procedure of Master database.

Specify the email body in the notification message body. In our demo, the email body is following:

Hello DBASupport,
The stored procedure of the master database has been transferred successfully.

Finally, the Notify operator task looks like the following image:

The email must be sent after all jobs are migrated successfully. To do that, we must connect both tasks using a connector. The package looks like the following:

The Transfer Master Stored Procedures task has been configured successfully.

Summary

In the second article of this series, we learned about the Transfer Master Stored Procedures Task. I have explained how we can configure it to transfer the user stored procedure created in the master database between two instances of SQL Server using SQL Server Data Tools (SSDT 2017). In the next article, we will learn about the Transfer Error Messages Task and create a package in SSDT 2017 to migrate the error logs between the two instances of SQL Server.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022