Step
8: Recovery model considerations and backup requirements
If the database you have migrated or
installed can be classed as “production”, ensure it is included in your backup
plan.
One of things the recovery model of a
database dictates is the type of backups that can be performed against it. For
example, transaction log backups cannot be done against a database set to simple
recovery mode. The restored or attached database will retain its original
recovery mode, so you will need to check it once the database has been
migrated.
If your server has a maintenance plan set up
that backs up the transaction logs of “all user databases”, the log backup step
will fail if the new database is in simple recovery mode. To get around, you
will either need to modify the maintenance plan to exclude the new database or
change the database’s recovery model to full.
Even if the restored database is in full
recovery mode, you will need to take a full backup of it if transaction log
backups are going to be taken later. Unless the new database has been fully
backed up once, log backups will not have a base to start from and will fail.
In such cases you will have a message like the following:
Msg 4214, Level
16, State 1, Line 1
BACKUP LOG cannot
be performed because there is no current database backup.
Msg 3013, Level
16, State 1, Line 1
BACKUP
LOG is terminating abnormally.
Step
9: Changing database code and associated configurations
This step will probably be required if your
database is being migrated from an older version of SQL to a newer one. The DBA
will be working with developers and system architects to identify commands and
keywords that are no longer supported or those that can be replaced with newer
features. Unlike most of the steps described above, this is not a simple, one
off procedure but can comprise of a series of iterations.
For example, if the database is migrated from
SQL Server 2000 to 2005 or 2008, code that uses xp_sendmail may need to be
changed to use the newer database mail feature with the sp_send_dbmail stored
procedure. Likewise, isql and osql commands in stored procedures should be
changed to make use of the sqlcmd utility.
Note that some of these features may require
the DBA to make server or database level changes. For example, the Surface Are
Configuration tool (or facet in SQL 2008) may need to be used to enable
xp_cmdshell, CLR code, database mail or older style OPENROWSET queries.
Developers and DBAs will also need to look
for linked server references within the code. New linked servers may need to be
created and the code accordingly modified.
Not all required configuration changes may be
evident from the code either. Databases tables sometimes store application
specific metadata such as connection strings or server names. DBAs and
developers will need to modify these data to reflect any changes.
A database property called TRUSTWORTHY
controls whether CLR assemblies marked with EXTERNAL_ACCESS or UNSAFE mode will
be able to access resources outside the databases. This property also dictates
whether functions and procedures that use impersonation via EXECUTE AS clause
can access resources outside the database.
By default, SQL Server keeps the TRUSTWORTHY
property of a database to OFF. Even if your source database has this property
set to ON, it will automatically revert to OFF when you detach and reattach the
database. This is to ensure that no malicious user can detach and attach a user
database and add malicious code there. To allow the newly attached database’s
code to access external resources, the TRUSTWORTHY property needs to be set
explicitly:
ALTER
DATABASE database_name SET TRUSTWORTHY ON
Also there are additional requirements for
CLR assemblies to run successfully. The DBA will probably need to work closely
with the developers in this area.
_______________
The final part of the series will examine full text catalogs and indexing, database collation, service broker considerations, encryption and high availability.
]]>