CONTENT
When migrating database instances between two different environments there are several details to be aware of. Two different options exist for methods to deal with environments that have different supporting database servers. To clarify environment here refers to the concept of development versus testing versus production style setups where different warehouses and data may be used.
When migrating objects between these environments using different database servers there are two ways to handle database instances. The first is to edit the database instance to use a different definition between environment. The second is to make a new database instance and modify objects to use the new database instance.
Each of these options has different benefits and caveats depending on the desired workflow in the environment. There is no single best option. It is instead best to analyze the two options and pick the one that best lines up with the desired workflows and whose caveats least matter to the workflow in question.
Option 1: Modifying the database instance in the destination environment.
Benefits
- This requires only a single modification of the desired database instance in the destination environment.
- This requires no modification of reports, logical views, data import cubes, freeform SQL reports, etc
- This can save time when configuring single sign on, warehouse authentication and other connectivity or authentication methods that depend on the database
Caveats
- When migrating the workflow must ensure not to update the definition of the database instance based on the source as that may change the data for the production environment
- New database instances for data import and freeform workflows require additional work on each migration
Option 2: Using a new database instance in the destination environment
Benefits
- No risk of migrations causing invalid data or results in production
- Less security concerns with migration, (users attempting to move production connection information to other environments)
- Less questions about data integrity for reports data upon execution for testing purposes
- Easy workflow if only one database instance is used in an environment
Caveats
- Significantly more administrative and maintenance work is required. All objects with manual database instance references must be manually updated.
- Maintenance work must be done upon each migration of schema, data import or freeform SQL objects
- Users must be trained to choose the correct production versus development database instances to see the correct data (may be a benefit for comparison purposes)
KB483094
