Migrating SQL Server Databases – The DBA’s Checklist (Part 2)
- by Sadequl Hussain
Continuing from Part 1 , our Migration Checklist continues:
Step
5: Update statistics
It is always a good idea to update the
statistics of the database that you have just installed or migrated. To do
this, run the following command against the target database:
sp_updatestats
The sp_updatestats system stored
procedure runs the UPDATE STATISTICS command against every user and
system table in the database. However, a word of caution: running the
sp_updatestats against a database with a compatibility level below 90 (SQL
Server 2005) will reset the automatic UPDATE STATISTICS settings for every
index and statistics of every table in the database. You may therefore want to
change the compatibility mode before you run the command.
Another thing you should remember to do is to
ensure the new database has its AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS
properties set to ON. You can do so using the ALTER DATABASE command or from
the SSMS.
Step
6: Set database options
You may have to change the state of a
database after it has been restored. If the database was changed to single-user
or read-only mode before backup, the restored copy will also retain these
settings. This may not be an issue when you are manually restoring from
Enterprise Manager or the Management Studio since you can change the
properties. However, this is something to be mindful of if the restore process
is invoked by an automated job or script and the database needs to be written
to immediately after restore. You may want to check the database’s status
programmatically in such cases.
Another important option you may want to set
for the newly restored / attached database is PAGE_VERIFY. This option
specifies how you want SQL Server to ensure the physical integrity of the data.
It is a new option from SQL Server 2005 and can have three values: CHECKSUM
(default for SQL Server 2005 and latter databases), TORN_PAGE_DETECTION
(default when restoring a pre-SQL Server 2005 database) or NONE.
Torn page detection was itself an option for
SQL Server 2000 databases. From SQL Server 2005, when PAGE_VERIFY is set to
CHECKSUM, the database engine calculates the checksum for a page’s contents and
writes it to the page header before storing it in disk. When the page is read
from the disk, the checksum is computed again and compared with the checksum
stored in the header.
Torn page detection works much like the same
way in that it stores a bit in the page header for every 512 byte sector. When
data is read from the page, the torn page bits stored in the header is compared
with the respective sector contents.
When PAGE_VERIFY is set to NONE, SQL Server
does not perform any checking, even if torn page data or checksums are present
in the page header. This may not be something you would want to set unless
there is a very specific reason.
Microsoft suggests using the CHECKSUM page
verify option as this offers more protection.
Step
7: Map database users to logins
A common database migration issue is related
to user access. Windows and SQL Server native logins that existed in the source
instance and had access to the database may not be present in the destination.
Even if the logins exist in the destination, the mapping between the user
accounts and the logins will not be automatic.
You can use a special system stored procedure
called sp_change_users_login to address these situations. The procedure
needs to be run against the newly attached or restored database and can accept
four parameters. Depending on what you want to do, you may be using less than
four though.
The first parameter, @Action, can take three
values. When you specify @Action = ‘Report’, the system will provide you with a
list of database users which are not mapped to any login.
If you want to map a database user to an
existing SQL Server login, the value for @Action will be ‘Update_One’. In this
case, you will only need to provide the database user name and the login it
will map to. So if your newly restored database has a user account called “bob”
and there is already a SQL Server login with the same name and you want to map
the user to the login, you will execute a query like the following:
sp_change_users_login
@Action = ‘Update_One’,
@UserNamePattern = ‘bob’,
@LoginName = ‘bob’
If the login does not exist, you can instruct
SQL Server to create the login with the same name. In this case you will need
to provide a password for the login and the value of the @Action parameter will
be ‘Auto_Fix’. If the login already exists, it will be automatically mapped to
the user account.
Unfortunately sp_change_users_login system
stored procedure cannot be used to map database users to trusted logins
(Windows accounts) in SQL Server. You will need to follow a manual process to
re-map the database user accounts.
Continues…
