August 16, 2022 at 9:57 am
I have compared two tables with the same data in two different databases(aslo the schema) with SSIS(Comparrison), sql attached. The Schema comparison return an exact match, the table quite a few new updates.
The tables are from Production and QA databases. I ran the exact queries on both of these databases, see attached. I am trying to find the difference in the Production database that is not populating table "auditLevyBreakdown". This table needs to have data in it for reporting purposes. Please assist?
August 16, 2022 at 11:23 am
Well you shouldn't compare data in QA to data in Production, the two data sets will be entirely different, also you shouldn't have production data in any other environment than production, you should be scrubbing the data and only having test data in your lower environments.
It looks like your missing dbo.fn_getsummaryreport from production, I would check what that does in QA, but if you have checked the schema for any differences and there are no other triggers, procedures etc which would directly insert into auditLevyBreakdown, then the issue will come from higher up the application stack.
You would need to look at SSIS packages, application code, external scripts, agent jobs etc etc etc the list is endless, to what should be inserting into auditLevyBreakdown and debug why it isn't happening.
August 16, 2022 at 11:41 am
Your feedback is in contractdiction of what i have been told in terms of comparing production with qa. It stems from this issue:
https://www.sqlservercentral.com/forums/topic/sys-object/page/2
August 16, 2022 at 12:05 pm
Well Jeff there is referring to static data. Do you know if the data in that table is static data or is it dynamic data?
Comparing data in QA to Production shouldn't be a thing ideally as you should be scrubbing anything which could be a data breach violation from the database prior to moving it to QA/UAT/DEV/TEST, as your opening yourself up for data breaches, HIPPA GDPR violations etc.
So anything names, addresses, emails, phone numbers, zip codes, anything which can personally identify anybody should be scrubbed to test data prior to moving to lower environments, so you cannot compare your customer table in QA to the customer table in production as the data wouldn't be a match as an example.
Also as the data drifts through regular QA routines again the data in these dynamic tables will not match your production databases, so you should really not compare QA to Production in terms of data, unless you have a solid data dictionary which details what table stores what information and if that table is static / configuration data or if it is tied to the daily OLTP processes etc.
Comparing the schema on the other hand is totally OK as yes you could end up with schema drift should the deployments be manual or something hasn't made its way to production yet, but schema <> data so yeah schema is OK, data is generally not OK unless you know the data.
August 16, 2022 at 12:23 pm
Im not sure about Static/Dynamic. I requested what it is, and was informed it is Static(or so this person guess).
August 16, 2022 at 12:28 pm
OK at a guess then, on your local copy of the production data, insert the missing data as it is in the QA copy to the production copy and verify if this indeed fixes the issue you have noticed at least locally on your local copy.
If it does then you can take an educated guess that the data is static and as such you must also insert that data manually into the real production copy of the database.
August 16, 2022 at 1:31 pm
You would need to write a script to insert the two rows from QA copy auditLevyBreakdown into your prod copy auditLevyBreakdown table.
Or you may use the import/export data wizard instead.
August 16, 2022 at 4:44 pm
No it will be DML.
INSERT INTO dbo.auditLevyBreakdown VALUES
(row one data),
(row two data)
August 16, 2022 at 7:21 pm
It’s not going to be in the database solution as that’s your schema only.
You have the data in the QA database just copy that and convert it to an insert statement and test it or use the import export data wizard to copy it from QAcopy to ProdCopy
August 16, 2022 at 7:36 pm
I dont see any procedure in QA that does this "INSERT INTO dbo.auditLevyBreakdown VALUES
(row one data),
(row two data)"
August 16, 2022 at 7:50 pm
You need to write it yourself.
If that data is static someone manually wrote a script to insert it as a one time operation into QA, it is not stored in your schema anywhere.
Copy the two rows from QA and paste them into a new query then wrap it all in the right syntax to turn them into DML insert statements.
August 16, 2022 at 7:55 pm
Or just copy auditLevyBreakdown from QA to Production, like you said?
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply