Making a replica of your production server to create a dev or test environment or moving your production data to new hardware are tasks a DBA needs to perform. So what are different methods to perform these tasks efficiently and without much effort? In this tip I am going to demonstrate how you can use the Transfer Jobs Task and the Transfer Logins Task to perform some of these operations.
In my last tip, Transfer Database Task and Transfer SQL Server Objects Task in SSIS, I showed how you can transfer your databases and SQL Server objects with the Transfer Database Task and Transfer SQL Server Objects Task. In this tip I am going to demonstrate you how you can transfer jobs and logins using the Transfer Jobs Task and Transfer Logins Task respectively without writing any code.
Transfer Jobs Task
Transfer Jobs Task is used to transfer SQL Server Agent jobs from one SQL Server instance to another. This task gives you an option to copy all the jobs or selected jobs from the source server to the destination server.
Let's demonstrate how you can create a SSIS package using the Transfer Jobs Task. Goto START -> Microsoft SQL Server 2005/2008 -> SQL Server Business Intelligence Development Studio -> then goto File menu -> New -> Project -> Select "Business Intelligence Projects" in the left tree pane -> Select "Integration Services Projects" and name the project as you wish and click OK.
In this new project you will see there is one package already named "Package.dtsx". Drag a Transfer Jobs Task from the Toolbox (which is normally on the left side) to the Control Flow pane as shown below. Right click on this task and click on Edit...
In the Transfer Jobs Task Editor, select Jobs on the left and now you are ready to configure this task.
These are the items that can be configured:
SourceConnection - specify the connection for the source SQL Server instance, if you have already created a connection then you can reuse it here or can create a new one.
DestinationConnection - specify the connection for the destination SQL Server instance.
TransferAllJobs - if this is set to True it will transfer all jobs. If this is set to False you can select specific jobs you want to transfer.
JobsList - this will be enabled if TransferAllJobs is set to False. Then you can select specific jobs to transfer. See the image below where I am only selecting two jobs (Backup Databases and Rebuild Indexes) to transfer to the destination.
IfObjectExists - If the jobs already exist on the destination then you have three choices, first FailTask execution, second Overwrite the destination job and third Skip the existing job and continue with others.
EnableJobsAtDestination - After the transfer you can also specify whether to enable or disable the jobs.
Once you are done with all these configurations you can hit F5 or click on play icon to execute the package. Your task will turn yellow during execution and then either red or green depending on the execution outcome. You can go to the progress/execution results tab to see any error messages if the execution fails.
Transfer Logins Task is used to transfer either all logins (except sa) or selected logins or all logins of selected databases from the source to the destination SQL Server instance. After the transfer, all the transferred SQL logins are assigned random passwords and SQL logins are disabled. The DBA needs to change the password and enable the SQL login before it can be used on the destination.
Let's walk through an example. Create another package in the current project and drag a Transfer Logins Task from the Toolbox to the Control Flow. Right click on the task and select Edit to configure the task's properties as shown below.
These are the items that can be configured:
SourceConnection - specify the connection for the source SQL Server instance
DestinationConnection - specify the connection for the destination SQL Server instance
LoginsToTransfer - You have three options for this:
AllLogins - this will transfer all logins from the source.
SelectedLogins - this allows you to select specific logins
AllLoginsFromSelectedDatabases - This allows you to transfer all logins associated with one or more databases as shown in the image below.
LoginsList - this will allow you to select specific logins if you select SelectedLogins for LoginsToTransfer
DatabaseList - this will allow you to select the databases if you select AllLoginsFromSelectedDatabases for LoginsToTransfer
IfObjectExists - If the logins already exist on the destination you have three choices; first FailTask execution, second Overwrite the destination logins and third Skip the existing login and continue with the others.
CopySids - if you set it to True then security identifiers (SIDs) associated with logins are also copied to the destination
Note: the person executing the package to transfer logins must be sysadmin on both the source and destination instances.
Funny enough, I just implemented a SSIS package to transfer Logins, Jobs and Stored SSIS packages from a production server to a log shipped secondary. A few of the problems that I had to overcome were that the Transfer Jobs task assumes that the agent operators, proxies and credentials are all present on the destination server. I have so far found no way to transfer proxies and credentials, but I ended up putting in a Data Flow task to transfer new operators from production to the secondary before transferring the jobs. With regards to the Transfer Logins task, if a login has a default database other than MASTER and that database is not in an online state (like recovering) the task will fail with a database inaccessable error. The simple simple solution is to make sure that all your logins have Master as their default database on the primary before running this task.
Tuesday, May 22, 2012 - 12:36:53 PM - Basil Heighter
Thanks for the easy to follow tutorial. Made copying 30+ jobs and 100+ logins very simple. In regards to the comment about ensuring the default database is set to Master for all logins prior to copying them. Is there an easy way to change all logins? Will there be any hinderance to the system by mass changing this setting?
Friday, March 22, 2013 - 3:43:11 PM - Daniel Figueroa
When I start the Job I receice the following error:
[Task 'Aufträge übertragen'] Fehler: Fehler bei der Ausführung: 'Der Wert NULL kann in die owner_sid-Spalte, msdb.dbo.sysjobs-Tabelle nicht eingefügt werden. Die Spalte lässt NULL-Werte nicht zu. Fehler bei INSERT. Die Anweisung wurde beendet.'. NULL value not allowed