Introduction
SQL Server Integration Services (SSIS) is a Business Intelligence tool which can be used by database developers or administrators to perform Extract, Transform & Load (ETL) operations. In my previous article entitled Execute SQL and Bulk Insert Tasks in SSIS I discussed how to use the Execute SQL and Bulk Insert tasks which are available in SQL Server 2005 Integration Services and later versions. In this article I will examine the use of the Transfer Logins Task and Transfer Database Task which are available in SQL Server 2005 and later versions.
Overview of SQL Server Tasks in SSIS 2005 & Later Versions
The SQL Server Tasks within SSIS can be used to copy, modify and delete SQL Server objects and data. The different types of SQL Server Tasks which are available in SSIS are Execute SQL Task, Bulk Insert Task, Transfer Database Task, Transfer Error Message Task, Transfer Job Task, Transfer Login Task, Transfer Master Stored Procedure Task and Transfer SQL Server Objects Task. If you are new to SQL Server Integration Services then I would recommend you to start with my previous article entitled SQL Server 2008 Integration Services Tasks to understand different types of Integration Services Tasks which are available in SQL Server 2005 and later versions. In this article you will see an example for how to configure and use the Transfer Logins and Transfer Database tasks.
Overview of the Transfer Logins Task
Database Administrators can use the Transfer Logins Task to transfer logins between different instances of SQL Server. Using this task you can either transfer all the logins from one SQL Server Instance to another SQL Server Instance, transfer only specific logins, or transfer only logins which have access to a specific user database. At the destination server, the transferred SQL Server Logins are by default disabled and are assigned a random password. Once the task is complete, the DBA needs to assign the correct password for the SQL Server account and enable the logins to allow users to access the database. However, using the Transfer Logins Task you cannot transfer sa logins (including renamed sa logins) between different instances of SQL Server.
Overview of the Transfer Database Task
Database Administrators can use the Transfer Database Task to transfer a SQL Server database between two instances of SQL Server. Using this task you can either copy or move the database between two different instances of SQL Server. This task basically supports two modes of database transfer – namely DatabaseOnline and DatabaseOffline.
DatabaseOnline Mode: – Using database online mode the database will remain attached on the source SQL Server and the entire object within the database will be transferred to the destination SQL Server using SQL Management Object (SMO).
DatabaseOffline Mode: – Using database offline mode the database is detached from the source SQL Server, and all the related mdf, ndf and ldf files for the source server are copied or moved to the destination server. Once the database files are successfully copied, the database is attached to the destination SQL Server and made available for users. It is always better to use DatabaseOffline mode as it is much faster than DatabaseOnline mode. The only disadvantage to using DatabaseOffline mode is that the database will not be available for end users during the activity.
Worked example Using Transfer Logins and Transfer Database Tasks
In this example we will first use the Transfer Logins Task to transfer two SQL Server Logins namely WebUserA and WebUserB from SQL Server 2005 to SQL Server 2008. Once the SQL Server Logins are transferred successfully, we will use the Transfer Database Task to copy Northwind database in DatabaseOnline Mode from SQL Server 2005 to SQL Server 2008. Finally, we will use the Execute SQL Task to run a TSQL script which will assign the appropriate password to the copied SQL Server Logins and then enable them.
1. Create a new SQL Server Integration Services Project and rename the default package TransferLoginsAndDatabasesTask.dts
2. Double click the TransferLoginsAndDatabasesTask.dtsx package to open it up in Design mode.
3. Drag and drop a Sequence Container from the toolbox to the Control Flow window
4. Drag and drop “Transfer Logins Task” from the toolbox to the Sequence Container in the Control Flow window and rename it “Transfer Logins Using Transfer Login Task”.
5. Double click the Transfer Logins Task to open up the Transfer Logins Task Editor as shown in the snippet below.
In Transfer Logins Task Editor, you need to configure a Source Connection and a Destination Connection. In this example, the source server is a SQL Server 2005 instance and the destination server is a SQL Server 2008 instance. There are three different options available under the LoginsToTransfer drop down box namely
a. AllLogins
b. SelectedLogins
c. AllLoginsFromSelectedDatabases
In this example select “SelectedLogins” option and then from the “LoginsList” select WebUserA and WebUserB SQL Server Logins which need to be transferred to the SQL Server 2008 instance.
In the Options sub menu choose “Overwrite” as the value for
“IfObjectExists” and also make sure you have selected “True” as the value for
“CopySids”. If you don’t choose to copy Security Identifiers (SIDs) to the
destination server, then the transferred logins will not be recognized by the
destination databases. Finally, click OK to save the changes in the Transfer
Login Task