ADT Tasks required after Upgrading Microsoft SQL Server from SQL2000 to SQL2005.

Document ID : KB000053958
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

There are two categories of databases related to Advantage Data Transformer. One is the database hosting the ADT tables, referred to as the IDB. The other is tables accessed by ADT Profiles for reading or modifying data. These are referred to as the source and target, or source and destination, tables. Some adjustments are required for both types of databases after they are upgraded to SQL2005.

Solution:

The IDB

The internal database may exist on its own, containing only ADT tables, or it may be a collection of tables 1 in the CA MDB. The ADT 2.2 stand-alone product or ADT as a component of UAPM requires a patch 2 to support SQL2005. ADT as a component of CMDB installs the patch during ADT installation, so it isn't necessary to apply the patch manually.

For SQL Server 2005, Microsoft has provided the new SQL Native Client driver to replace the previous SQL Server driver. After you have upgraded your IDB host database server to SQL Server 2005, any ODBC connection needs to use the SQL Naive Client driver. By applying the patch published as QO78998, the ADT will recognize the SQL Native Client driver as a valid driver for an IDB host.

  • Stop all ADT executables.
  • Install patch #13 which can be downloaded from CA Online Support as QO78998.
  • In the ODBC DataSources Administrator, remove the ODBC DSN that was configured for ADT's IDB using the SQL Server driver.
  • In the ODBC DataSources Administrator, configure a new DSN using the SQL Native Client driver. This DSN must be named exactly the same as the one you removed.

By this method, you'll slip in the right driver behind the scenes. ADT doesn't save driver information, so after this change, all ADT executables will continue to use the DataSource Name they know, but it will now be using the SQL Native Client driver.

While creating the new ODBC DSN, be sure to use the same configuration settings recommended for any SQL Server IDB DataSource. Of note are: use SQL Server Authentication, check and select a default SQL Idb database, uncheck "Use ANSI nulls, paddings and warnings." Do not uncheck "Use ANSI quoted identifiers."

Sources and Destinations

ADT supports any Microsoft-supported version of SQL Server as a source or destination and needs no patches for SQL2005. However, you will need to modify any profiles that previously accessed SQL2000 using the "Microsoft SQL Server" interface if the database has been upgraded to SQL2005. Change the interface to "Microsoft SQL Server/ODBC." The older "Microsoft SQL Server" interface is only valid for SQL2000 and below. The "Microsoft SQL Server/ODBC" interface is valid for both SQL2000 and SQL2005. 3

If the "Microsoft SQL Server/ODBC" interface is not listed on the Interface tab of your Profiles, then you need to install the Microsoft SQL Server 2005 Client on that computer and run the Script Interface Synchronizer to register this new interface.

Profile parameters are different for an ODBC interface than the SQL Server interface. This means that the "Server" field on the "Server/Database" tab should now contain an ODBC DataSource Name instead of the SQL Server machine alias. Whenever a Profile interface is changed, you need to regenerate any scripts using the Profile. It's necessary to regenerate because script handlers, particularly STORE, have different parameters based on the interface. 4

Notes about SQL Metadata and the ODBC Scanner

Any DataSources used by Mapper's ODBC Scanner to scan SQL Server should use the driver recommended by Microsoft. For scanning SQL2005, use a DSN configured with the SQL Native Client driver.

When the ODBC Scanner scans metadata into the Mapper metadata store, it puts the metadata in a tree structure under a "Server Type." Server Types' names include the database version. ADT's ODBC Scanner does this for any upgrade, not just major releases, so your Data tab may have a list of SQL Servers like this.

- Status: DEV
  + Microsoft SQL Server (08.00.0194) 
  + Microsoft SQL Server (08.00.0760)
  + Microsoft SQL Server (08.00.2039)
  + Microsoft SQL Server (09.00.2047)
  + Microsoft SQL Server (09.00.3042)
  + Microsoft SQL Server (09.00.3233)

This reflects the SQL Server version at the time the scan was done. It is not necessary to rescan tables that are now hosted by a different version of SQL Server as long as the table/column definitions have not changed.

1  TEC476048 Names of the Tables in the ADT IDB.
2  TEC484545 ADT Patch Required for SQL2005 IDB.
3  TEC483559 ADT working with SQL2000 vs. SQL2005.
4  TEC301615 Interface Profile Matrix.