SQL Server 2005 Integration Services - Migration Tasks
March 10, 2006
In the previous article of our series covering SQL Server 2005 Integration Services, we discussed Control Flow tasks designed to simplify the transfer of SQL Server objects (such as databases, user-defined error messages, and SQL Server Agent jobs). We will complete this overview by describing the characteristics of the remaining tasks in this category - two of which are intended specifically for server logins and master stored procedures - and one (called Transfer SQL Server Objects task) with more universal purpose.
However, before we focus on these topics, we need to first take a closer look at SQL Server 2005 and Windows specific characteristics that have potential impact on successful operation of SSIS transfer tasks. This includes the authentication procedure used when creating connections to remote computers, configuration defaults that affect communication via these connections, as well as security settings introduced in the latest service packs for Windows XP and 2003 Server operating systems.
Let's start with the authentication mechanism (for its more detailed overview, refer to one of our earlier articles). SQL Server 2005 supports two modes that can be applied to validate identity during login. The first one, called Windows Authentication, uses credentials of the Windows account that belongs to a user initiating the logon to establish whether access to the server should be permitted (following this step, the authorization process determines the level of privileges that are granted to this login). In addition to its inherent security, this process is transparent from the user's perspective, since it does not prompt for username or password. The second mode (which you can switch to from the Security section of the Server Properties dialog box in SQL Server Management Studio), known as SQL Server and Windows Authentication, still supports the first method, but also allows using accounts defined within SQL Server itself. In situations when connection via such an account is attempted, its credentials need to be explicitly provided. While this is essentially identical to what was available in earlier versions of SQL Server, there is a caveat that makes the new implementation different. If you choose Windows Authentication mode at installation time, but later you change it to include SQL Server Authentication, the sa account will remain disabled. (To enable it, execute the ALTER LOGIN command or use the Status section of the Login Properties dialog box, which is invoked from the context sensitive menu of the sa entry under the Security/Logins node of the SQL Server Management Studio). Keep in mind however, that, in general, you should avoid authenticating with the sa account (and rename it as a security precaution).
Another setting that might have impact on the outcome of SSIS transfer tasks is configurable from SQL Server Configuration Manager. Within its Network Configuration node, you can designate protocols (as well as specify their parameters) for communicating with SQL Server. By default, only Shared Memory is enabled - which is the simplest one to use (since it has no modifiable options) but suitable only for local connections. Depending on your requirements and preferences, you might need to allow Named Pipes or TCP/IP-based connections (and configure them accordingly).
Among other factors, which can potentially affect server-to-server communication are default Internet Connection and Windows Firewall settings (component name and functionality depends on the operating system platform and service pack version). Connectivity problems are likely to surface when running SQL Server 2005 on a newly installed Windows 2003 Server (slipstreamed setup with integrated SP1 files) or Windows XP SP2 computers, which by default block all incoming traffic types. To address this issue, allow traffic on the TCP port designated within the TCP/IP protocol section of SQL Server 2005 Network Configuration (most commonly TCP port 1433), by identifying it as part of the firewall exceptions.
Keeping all of these dependencies in mind, we are ready to return to our original agenda. The first one on the list is Transfer Logins Task. Its purpose is to allow members of the sysadmin fixed server role to duplicate any arbitrarily chosen logins (with the exception of sa login, which can not be copied) between two instances of SQL Server. In order to get familiar with its characteristics, launch SQL Server 2005 Business Intelligence Development Studio and create a new Integration Services project. Drag the Transfer Logins Task from the Toolbox onto the empty Control Flow area and display the Editor dialog box by selecting the Edit entry from its context sensitive menu. The General section lets you alter the default name and description of the component. All of the non-read only properties (including name and description) can also be set dynamically using Property Expressions Builder, accessible from the Expressions section. The Logins section contains remaining settings. That is where you designate the Source and Destination connections that identify SQL Server instances from and to which transfer will take place, as well as the required authentication method (with associated credentials, in case SQL Server Authentication is used). Once connections are available, you can assign one of three available values (AllLogins, SelectedLogins, or AllLoginsFromSelectedDatabase) to the LoginsToTransfer option. Depending on the value selected, you might need to populate the LoginsList parameter (by selecting checkboxes next to the login name in the Select Logins dialog box) or specify databases in the DatabasesList. By default, in cases when a target server already contains a transferred login, the task will fail. To change this behavior, assign IfObjectExists to Overwrite or Skip, depending on the desired outcome. Finally, when transferring logins based on Windows Authentication, it is possible to copy their associated Security Identifiers (SIDs) by setting CopySids property to True.
While the task is intended to produce the same outcome regardless of which authentication method is used (Windows or SQL Server), it turns out that at this point, connections based on logins defined in SQL Server cause failures during transfers. While they work flawlessly when the connection properties are modified to use Windows authentication, such configuration requires that both source and destination servers are members of the same or trusted Active Directory domains (if this is not the case, you might have to resort to the workaround outlined in the Microsoft Knowledge Base article 246133). Note that the task allows you to transfer logins based on Windows and SQL Server authentication (irrespective of the type of authentication method chosen for source and destination connections), however, SQL Server logins are automatically disabled once transferred (and have to be manually enabled in order to become functional).
Transfer Master Stored Procedures task copies user-defined (but not system) stored procedures stored in the master database from one instance of SQL Server to another. To execute it, you will need dbo privileges to the master database. Just as other tasks, which offer transfer functionality, its Editor interface contains General and Expression sections (which serve the same roles as described in earlier paragraphs). Its unique set of features is configurable from the Stored Procedures section. From there, besides defining source and destination connections, you can specify whether you want to transfer all stored procedures (by setting the value of TransferAllStoredProcedures property to True) or only selected ones (stored as values of the StoredProceduresList property). You can also indicate how you want to handle duplicates (with IfObjectExists property, which can be set to FailTask, Overwrite, or Skip). Interestingly, this task completes successfully with both Windows and SQL Server authentication applied for source and destination connections.
Transfer SQL Server Objects task was designed to be the most universal and versatile of all the ones we have described so far. Its intended purpose is to transfer any type of objects (their availability is dependent on SQL Server versions involved in this operation) between two existing databases. This includes tables (with associated items, such as indexes, triggers, primary and foreign keys), views, stored procedures, user-defined functions, defaults, user-defined data types for both SQL Server 2000 and 2005. Among SQL Server 2005-specific objects are user-defined types based on the Common Language Runtime, partition functions, partition schemes, assemblies, schemas, user-defined aggregates, and XML Schema collections. As with other transfer tasks, you are given flexibility of working with all objects of a particular type or select them individually (there is also a CopyAllObjects option). The ability to perform a copy of SQL Server logins, as well as database users and roles (by setting to True CopySqlServerLogins, CopyDatabaseUsers, and CopyDatabaseRoles properties) provides the necessary basis for copying object level permissions (controlled with CopyObjectLevelPermissions property). This functionality is further enhanced by allowing dropping corresponding objects at the destination, optional copy of data and schema, applying collation from the source server, and including dependent objects (applicable when transferring SQL Server 2005 user-defined types, which rely on CLR assemblies).
Unfortunately, despite the feature-rich promising design, the implementation of the Transfer SQL Server object task appears to be suffering from a few problems. In particular, there seems to be an issue with transferring objects that are associated with schemas other than dbo. In addition, successful completion of the task might require in-depth analysis of object dependencies (such as foreign and primary key relationships), which might not be properly handled by auto-generated T-SQL scripts. Furthermore, it is necessary to verify whether objects to be transferred already exist at the destination. The task will fail in such case, unless you set the DropObjectsFirst property to True. On the other hand, the failure will also occur if this property is enabled and the destination database does not contain all of the objects you are trying to transfer. The task suffers as well from the earlier described limitation, which prevents the use of SQL Server accounts for remote server authentication. We will look into alternative solutions to these issues in our next article.