Moving SOFie databases to a new SQL Server
After backing up the SOFie databases and restoring them to the new location, review the following steps to see if any of them require manual completion.
You should maintain the existing SOFie URL so that the Excel add-in will not need to be updated and re-installed on user's workstations.
|1.||Re-create the ODBC system data source on the sql server machine itself.
|Driver:||Neon Client 32-bit|
(and after pressing the Advanced button):
You also must enter an NIH Data warehouse account and initials and password to the data source, so that SOFie can log in to the data warehouse
in order to download. (The account must have access to all of the IC's CANs in the Budget and Finance business area; you can copy the account
from the ODBC data source on the existing sql server, but must get the password again from the account's owner.)
|2.||Check the website to confirm that the web.config uses the sql server's correct address.
The remaining steps should have been accomplished automatically when you restore from backup to the new machine:
||Re-create users sofieAdmin and sofieWeb with the following permissions:
- sofieWeb: Grant db_datareader and db_datawriter access to all SOFie databases
- sofieAdmin: Grant dbo accesson all SOFie databases
||Grant execute access to the sofieWeb user for all procedures on each database. We can provide a script to do this if it is not restored automatically.
||Confirm that the linked server still exists. It probably is named after the IC name; e.g., the linked server for NIDCR is DwNidcrServer.
If you need to re-create it, there is a script available that is part of any FY database (you only need to run the script on a single FY database to create the linked server on the machine).
In an FY database, type
Exec CreateLinkedServer 'IC'
where IC is the IC acronym used in naming the SOFie databases.
SOFie needs to know the name of the linked server in order to download from the data warehouse.
||Configure the SQL Server Agent to re-start automatically. There are two or three jobs related to SOFie, depending on the installation:
- The download job, which runs on the current FY database;
- A summarization job, which runs on the root database (such as SOFIE_NIDCR);
- And for some ICs, and VEDS import job.