SQL Server 2005 Integration Services - Part 19
February 27, 2006
Database Management Tasks
After dedicating most recently published articles to the Data Flow features of SQL Server 2005 Integration Services (focusing mainly on the characteristics of the Script Component), we will return to the Control Flow-related topics. In particular, we will concentrate on tasks designed to assist with migration of data as well as server and database objects. This functionality supersedes Copy SQL Server Objects task of Data Transformation Services in earlier versions of SQL Server. However, SSIS not only offers its enhanced equivalent in the form of Transfer SQL Server Objects task, but also delivers a number of more specialized components - geared specifically towards migrating databases, user-defined error messages, SQL Server Agent jobs, server logins, and master stored procedures.
We will start our overview with the Transfer Database task, which provides the ability to copy or move databases between two instances of SQL Server 2005 or 2000 or migrate it from the former to the latter. In order to get familiar with its properties, let's look into its implementation. Launch the SQL Server 2005 Business Intelligence Development Studio and create a new Integration Services project. Pick the Transfer Database Task icon from the Toolbox and drag it to the Control Flow tab of the Designer interface. Launch the Editor dialog box by selecting Edit... entry from its context sensitive menu. The General section allows you to assign a custom name and description if you are not satisfied with the default ones. More relevant settings are located in the Databases section. From here, you need to designate two connections - identifying source and destination servers and authentication method used to connect to each. You can either create them (this is done from the SMO Connection Manager Editor dialog box, which is displayed once you select <New connection...> option from the connection list box) or reuse existing ones if you defined them earlier. After this step is completed, assign values to parameters describing the source database (take care of it before you attempt to set the destination database), which include the following:
Destination database settings include DestinationDatabaseName, DestinationDatabaseFiles (which requires you to specify destination file, folder, and network file share, in the format identical to the one used for the SourceDatabaseFiles), and DestinationOverwrite (applicable in situations where a database with the same name or files already exists on the target server). The Expressions section of Transfer Database Task Editor is useful in scenarios where you want to set property values using the result of dynamically evaluated expressions. Successful execution of the task results in a copy of the original database being automatically attached to the new server and becoming immediately ready for access.
Configuring the Transfer Error Messages Task is a bit less complicated. This component is intended for copying user-defined error messages, which are characterized by the fact that their identifier is greater than 5000 (as opposed to predefined system error messages, for which the identifier values are below this limit and which can not be transferred). Error messages are server level objects, which can be accessed via the sys.sysmessages view in the master database. Depending on preferences, they can coexist in multiple language versions, which you can selectively transfer to a destination server (although note that us_english version of any message must be copied to - or defined on this server - before other language specific versions of such message might be transferred). The ability to execute the Transfer Error Messages task is limited to members of the sysadmin and serveradmin fixed server roles on the target server.
To add user-defined messages to an instance of SQL Server 2005, use sp_addmessage system stored procedure, which accepts several parameters, with msgnum (message identifier, which, as we already mentioned, must be greater than 5000), severity (an integer between 0 and 25, with the range from 19 to 25 available exclusively to members of sysadmin role), and msgtext (message text up to 255 characters long) being mandatory and lang (language), with_log (indicating whether the error will be recorded in the Windows Application Event Log, in addition to SQL Server error log), and replace (used to replace text of existing error message) being optional. For example, the following:
USE master EXEC sp_addmessage @msgnum = 66666, @severity = 25, @msgtext = N'Oh No You Did Not...', @lang = 'us_english'
creates an error message with identifier 66666, severity level 25, sample text message (enclosed in single quotes) and us_english language parameter. Once you ensure that your source server contains at least one user-defined message, create a new Integration Services project and add Transfer Error Messages Task to its Designer interface. Its Editor dialog box (which is displayed after you select Edit... item in its context sensitive menu) is divided in the General, Expressions, and Messages sections, with the first two serving identical roles to the one described when discussing Transfer Database task. In the Messages section, you can designate source and destination connections and set the following parameters:
Transfer Jobs Task allows members of sysadmin fixed server role or SQL Server Agent fixed msdb database role to copy SQL Server Agent jobs between two instances of SQL Server. Just like the two tasks we have described earlier, this one is also configurable via the Editor interface, which consists of General, Expression, and Job sections. Within the last one, you need to define the source and destination connections, identify jobs you are planning on transferring (you have a choice of running the bulk copy by setting TransferAllJobs parameter to True or selecting individual ones as part of JobList collection) and specify whether the newly copied job should be automatically enabled. In case identical jobs already exist at the destination, you have the option to overwrite them, skip the copy, or fail the task.
Note that the SQL Server Agent does not need to be running on either source or destination server for the task to successfully complete. However, you will need to ensure that the account specified as the owner of a transferred job is recognized on the destination server. This might become a problem if you are using local Windows accounts, which security context does not extend beyond the computer on which they are defined. (In such cases, task execution will fail, since the SID of the local account, invalid on the destination server, will be treated as NULL, which, in turn, is not permitted in the owner_sid column in the dbo.sysjobs table in the msdb database). This can be resolved by replacing them with domain Windows accounts (assuming that both source and destination servers reside in the same or trusted domains), using NT AUTHORITY\SYSTEM generic account, or assigning job ownership to logins relying on SQL Server authentication.