Migration using a 2nd SQL server as the database
This topic is related to sections:
This page contains the following sections:
When the project uses a 2nd SQL Server as the migration database, all of the transformation of that data takes place within the non Migration SQL Server database. Migration is stored within 1st SQL Server as well as the CONSTRUCT Tables. These notes are very important when using a 2nd SQL Server as the migration database:
The Migration database runs from 1st SQL Server and the Working databases run from 2nd SQL Server - all use dbo as the SCHEMA.
Migration runs all applications from 1st SQL Server but does not store any of the rows of data
Working database SQL Server stores all Data for Snapshot (src, tgt, or wrk) databases - this includes a SRCCONSTRUCT database and a REPORT database
The 2nd SQL Server is setup in Administer > Setup > Servers as a separate server of type SQLSERVER
SRCCONSTRUCT Database Build in 2nd SQL Server
The team creates CONSTRUCT as a Snapshot Source Datasource (SRCCONSTRUCT) within the 2nd SQL Server database rather than using the one delivered with Syniti Migrate. This new Snapshot datasource stores the CONSTRUCT table data for use during Mock runs as the table data remains static. Each template table built within Syniti Migrate and stored within SQL Server CONSTRUCT database needs to be added to the SRCCONSTRUCT datasource for the 2nd SQL Server, and the Metadata is imported as well.
Datasources: Source Snapshot of CONSTRUCT db: Add Tables
This Source Snapshot is connected to the CONSTRUCT datasource that comes delivered and points to the Syniti Migrate system CONSTRUCT database in SQL Server. As each table is added to the Source Snapshot datasource, the system attempts to import the Metadata (Columns, etc.)
Datasources: Source Snapshot Datasource SRCCONSTRUCT: Import Table Metadata
As with any Snapshot Datasource, the SRCCONSTRUCT datasource is built out for XML. The new tables automatically generate within the 2nd SQL Server, and the Import Project and all tables are scripted in XML for import to Data Services.
Report Database in 2nd SQL Server
During the refresh of reports, the rows of data are saved to the Report of the same ID as stored within the Report details page. These reports are all stored in the REPORT database in the Working Database (client's SQL Server DB).
Mappings: Target Reports: Details: Report ID
2nd SQL Server: REPORT database: Report tables list
The REPORT database is stored within the 2nd SQL Server, and the migration of data using DataServices as the ETL generates the report tables and store them within this REPORT db on 2nd SQL Server. The Parameters page designates the appropriate datasource for reports, and the Datasource REPORT points to the 2nd SQL Server. As the object goes through the job run, the reports generate as tables within this 2nd SQL Server - REPORT for the static report data.
The Parameters for Working Database Type is SQLSERVER. Since the REPORT datasource is pointing to the 2nd SQL Server as the Server value, the reports all generate within the 2nd SQL Server REPORT DB as expected.