How do I create and sync a federated database for reports only from two live dbs?

I need to quickly implement a read-only database containing data retrieved from two identically structured live databases.

Live dbs are actually dbs from Dynamics accounting, so I'm happy for any Dynamics specific advice, but it's basically a SQL question. This is a fairly old version of Dynamics from Great Plains and was acquired by Microsoft. This is on SQL Server 2000.

We have reports and applications that access Dynamics data. These apps are meant to be viewed by one db company. Now we need to add another one. It is appropriate for most of these reports and applications to display combined data. They don't care which company has an order or invoice. They only look at a small number of tables.

It seems to me that the simplest solution is to generate only db reports with combined data. Preferably, we need an efficient way to update this db with changes multiple times a day.

I am a developer, not a db expert, but here's my plan:

Create a merged reporting database with the required tables initially with the same table structure as the current dbs.

All Dynamics tables seem to have an int DEX_ROW_ID id column. I'm not sure what it was used for (it is not indexed), but it seems like an obvious general way to uniquely identify strings. In db reporting, I will change it to a regular int (not a person). I will create a unique index on DEX_ROW_ID in all dbs.

Dynamics has no timestamps, so I will add a timestamp column to tables in live dbs and a corresponding binary (8) column in reporting db. My guess and hope is that Dynamics doesn't get frustrated with the extra index and column.

Add an int CompanyId column to the db reporting tables and add it to the end of any unique indexes. Most of the data will be naturally unique even without it. those. order and invoice numbers, etc. will be different for two live dbs. We may have to make some minor changes to the applications, but I don't expect to do anything other than point them to the new db report.

Assuming my db report is named Reports, live dbs are Live1 and Live2, the timestamp column is called TS and all dbs are on the same server ... here's my first attempt at updating a script to copy changes in one table called MyTable in Live1 to reporting database.

USE Reports

CREATE TABLE #Changes
(
ReportId int,
LiveId int
)

/* Collect in a temp table the ids or rows which have been deleted or changed
in the live db L.DEX_ROW_ID will be null if the row has been deleted */

INSERT INTO #Changes
SELECT R.DEX_ROW_ID, L.DEX_ROW_ID
FROM MyTable R LEFT OUTER JOIN Live1.dbo.MyTable L ON L.DEX_ROW_ID = R.DEX_ROW_ID
WHERE R.CompanyId = 1 AND L.DEX_ROW_ID IS NULL OR L.TS <> R.TS

/* Delete rows that have been deleted or changed on the live db 
I wonder if using join syntax would run better than the subquery. */
DELETE FROM MyTable
WHERE CompanyId = 1 AND DEX_ROW_ID IN (SELECT ReportId FROM #Changes)

/* Recopy rows that have changed in the live db */
INSERT INTO MyTable
SELECT 1 AS CompanyId, * FROM Live1.dbo.MyTable L
WHERE L.DEX_ROW_ID IN (SELECT ReportId FROM #Changes WHERE LiveId IS NOT NULL)

/* Copy the rows that are new in the live db */
INSERT INTO MyTable
SELECT 1 AS CompanyId, * FROM Live1.dbo.MyTable
WHERE DEX_ROW_ID > (SELECT MAX(DEX_ROW_ID) FROM MyTable WHERE CompanyId = 1)

      

        
        
        
      

    

Then do the same for Live2 db. Repeat for each table in reports. I know that I should use the @CompanyId parameter instead of a literal, but I cannot do that for the live db name, which I could generate with a C # program or something.

I'm looking for any advice, suggestions, or criticism on what I'm doing here. I know it won't be atomic. Things can happen on the live db while this script is executing. I think we can live with it. We will probably make a full copy either at night or in the week when nothing is happening on live dB.

We should value performance over elegance or perfection. Some initial testing has a first query with TS mappings that run for about 30 seconds for the largest table, so I am an optimist that this will work, but I would also like to know if I am missing something obvious or am missing a forest for trees ...

We really don't want to deal with log files in the reporting db. Can we just set this up for a simple recovery model and forget about the logs?

thanks