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:
- SourceDatabaseName – name of the source database.
-
SourceDatabaseFiles – required only if offline method – described
next – is used. It includes Source File and Source Folder values, which are
populated automatically once you provide the database name. However, you still
are required to fill out the Network File Share entry – even if the source
server is local. This has the format \ServerNameShareNameFolder,
where ServerName and ShareName are the names of server and share,
respectively, and Folder matches the Source Folder path, -
Method – can be either online (which allows for user access
during the transfer and can be performed by the database owner or members of SysAdmin
fixed server role), or offline (faster, but requiring SysAdmin privileges and
involving detaching database prior to initiating the copy process and
reattaching it after the copy completes, and therefore preventing users from
simultaneous access to the database), - Action – either copy or move,
-
ReattachSourceDatabase – relevant only when the offline method is
used. Its value determines whether the source database should be reattached
even if the transfer process fails.
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:
-
ErrorMessagesList – collection of user-defined messages, which
you pick from the Select Error Messages dialog box, by clicking on checkboxes
in the Select column. -
ErrorMessageLanguagesList – collection of languages in which the
messages are defined, populated using the same Select Error Messages dialog box
as the ErrorMessagesList. -
IfObjectExists – if a duplicate (a message with the same
identifier number and language) is found, you have an option to FailTask,
Overwrite, or Skip it. -
TransferAllErrorMessages – allows transferring all user-defined
error messages (making content of ErrorMessagesList and ErrorMessageLanguagesList
entries irrelevant).
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 AUTHORITYSYSTEM
generic account, or assigning job ownership to logins relying on SQL Server
authentication.
