Production reports from a test database - is this really best practice?
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
Would you be then running reports on potentially outdated database all the time? What if someone tests the application using that database and just changes the data? It might panic the government, right?
"You'd have to be a floating database guru clad in a white toga and ghandi level of sereneness to fix this goddamn clusterfuck.", BruceN[^]
-
Would you be then running reports on potentially outdated database all the time? What if someone tests the application using that database and just changes the data? It might panic the government, right?
"You'd have to be a floating database guru clad in a white toga and ghandi level of sereneness to fix this goddamn clusterfuck.", BruceN[^]
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
johnsyd wrote:
My People Manager and her manager both maintain there is nothing wrong with this.
They say this to you - I bet they wouldn't declare this modus operandi to the external auditors though?
-
These guys aren't stupid - they refresh the test database from Production every time they need to run a report.
-
johnsyd wrote:
My People Manager and her manager both maintain there is nothing wrong with this.
They say this to you - I bet they wouldn't declare this modus operandi to the external auditors though?
-
These guys aren't stupid - they refresh the test database from Production every time they need to run a report.
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
At one bank I worked at the EOD image was loaded onto a reporting system each night, it is the only way. Reporting should never be from production, that is for data capture and process.
veni bibi saltavi
-
At one bank I worked at the EOD image was loaded onto a reporting system each night, it is the only way. Reporting should never be from production, that is for data capture and process.
veni bibi saltavi
-
That would actually make sense, but what these guys are doing is copying live data into a totally uncontrolled environment on an ad-hoc basis. The code that produces the reports, the data itself, is all subject to change by anyone at any time.
If you are using a standard database product then mirroring is your friend; Oracle, SQL Server & Mongo all support mirroring. I have set up systems where there is a real time mirror of production onto a reporting DB. All updates are there and there is no problem for the eejits who say they need up to the minute data. Then you can allow the geeks and freaks in MIS to build whatever mad reports they like. I have even seen on Oracle a reporting user create views of the [mirrored] production data in the most vile unnormalised way imaginable, but it gave the weirdo the data they wanted. The only rules are reporting is from a *copy* of production rather than production itself, and that the integrity of the data is maintained.
veni bibi saltavi
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
I think some people missed the decisive point. There are laws on protecting data (well, if you are in the US, things might hardly matter, but in Germany that does). On the production database, there ought to be access restrictions: not everyone can read any kind of data there. And those restrictions are normally not enforced on a "test database".
-
I think some people missed the decisive point. There are laws on protecting data (well, if you are in the US, things might hardly matter, but in Germany that does). On the production database, there ought to be access restrictions: not everyone can read any kind of data there. And those restrictions are normally not enforced on a "test database".
Yes, we have just recently adopted a data masking policy for client personal data when copying Production data to Test - their names & addresses & tax identifiers are changed to random values. However in this recent case, that policy was not followed. I thinking mainly of our legal duty of care to provide accurate data for the Tax Office but you're right - we have violated our own data masking policy as well. Not sure if data masking is enforced by law here, but it is company policy. We do have privacy laws, so we can't make our clients' data public, but the law mainly covers movement of data in and out of the organisation.
-
Yes, we have just recently adopted a data masking policy for client personal data when copying Production data to Test - their names & addresses & tax identifiers are changed to random values. However in this recent case, that policy was not followed. I thinking mainly of our legal duty of care to provide accurate data for the Tax Office but you're right - we have violated our own data masking policy as well. Not sure if data masking is enforced by law here, but it is company policy. We do have privacy laws, so we can't make our clients' data public, but the law mainly covers movement of data in and out of the organisation.
johnsyd wrote:
provide accurate data for the Tax Office
Data for the Tax Office are not "live data". When you do your VAT report, you report past data: e.g. on March 10 you send a report for February. Hence that won't be a problem.
-
johnsyd wrote:
provide accurate data for the Tax Office
Data for the Tax Office are not "live data". When you do your VAT report, you report past data: e.g. on March 10 you send a report for February. Hence that won't be a problem.
Data for tax *is* a problem in the context we are in, despite the fact that it is past data. The mirrored reporting database is an excellent idea. It can be protected from alteration. However we don't have it and unlikely to get it any time soon. We downloaded production data into a testing database. The data (including past data) is unprotected. It could be changed by anyone in between the download and running the report. So could the code to generate the report. Management told me there was nothing wrong with that particular approach - my posting was intended to see what the members here thought about it.
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
Is this a temporary scenario or business as usual? If it's BAU that's bad; they should look to improve things so that they're in a better scenario long term; perhaps a simple compromise would be creating a staging database which they can use for these tasks (i.e. created with this purpose in mind, so it's not going to be affected by test activities, but gives flexibility of deployments alongside protecting production). If it's a one off because of some reason then go with what's pragmatic; these things are OK as long as everyone understands it's a bad thing and accepts that it's "just this once" in an emergency situation... so long as you don't find you're always in an emergency situation...
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
Identical databases aren't. Running reports from a Test environment for a specific purpose is OK as long as all parties are aware of what's been done and what are the potential limitations of the reporting process. The phrase "informed consent" comes to mind. Sometimes close enough is close enough.
-
Is this a temporary scenario or business as usual? If it's BAU that's bad; they should look to improve things so that they're in a better scenario long term; perhaps a simple compromise would be creating a staging database which they can use for these tasks (i.e. created with this purpose in mind, so it's not going to be affected by test activities, but gives flexibility of deployments alongside protecting production). If it's a one off because of some reason then go with what's pragmatic; these things are OK as long as everyone understands it's a bad thing and accepts that it's "just this once" in an emergency situation... so long as you don't find you're always in an emergency situation...
It was intended to be BAU, but I said that I would go elsewhere if it continued. So it has stopped. Our CIO maintains that change is the enemy, so releases to Production are now only once a month. To create a staging database would probably be a 6 to 12 month project with a budget of at least %500,000 if it were approved at all. To open a port on a firewall takes 6 weeks. To increase the size of a database by 150GB takes from 4 to 8 weeks. To develop a system to FTP files from an external server takes 6 months. Yes, I know anyone could knock up a script in half an hour! This is what happens when an organisation becomes terrified of change.
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
This is not best practice. By definition, there is no telling what state the test system would be in. For example, despite taking a copy of the live database, how can they be sure all the report application code, dlls, configuration etc is as per live too? The report could be erroneous and they wouldn't even know it! If updating the Production system is too risky then there is probably an underlying problem with architecture, configuration management or quality control. You could suggest that using a test system to produce 'live' reports is inappropriate and inefficient and should consider a 'live' reporting server. The reporting server would either have a copy of the production database, periodically sync'd in some fashion or use the production db remotely. The report server would house only the application code for the reports and as such you could argue that updating that server is a low risk to the Production Server's operation and a relaxed set of release procedures can be employed.
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
This is "reporting"; not "file maintenance". Instead of catastrophizing, you should "prove" the two reports will vary. Perhaps the managers actually know what's going on here (for once). In any event, I'm all for a distinction between "operational" (OLTP) systems versus "informational" (DW) systems (which one tears down and builds up all the time).
-
I'd heard of testing in Production, but until I joined my current job, I'd never heard of running live reports from a Testing environment (test code, test database). Apparently releasing code changes to Production outside the monthly release window is so dangerous and/or bad practice that it is safer to take a copy of the Production database into the Test database and run the revised code from there. This was a compliance report to the Tax Office by the way. My People Manager and her manager both maintain there is nothing wrong with this. What could possibly go wrong? Well, only the main database was copied down, not the lookup databases etc. What if the main routine called a another routine which had been changed? What if a tester or a batch process changed the data? What if the collation or other properties of the database were different? What does the Code Project think? I don't think logic is going to make any difference but majority opinion may sink in.
As a thumb rule - Reporting should always be from replica/mirror/snapshot of the production DB. - Code that generate report has to be exactly same as in production. - Reporting Environment should pass synthetic tests all the time to go ahead and generate report. - Report generation should be autonomous without user intervention If you meet these 4 conditions, its okay if you want to name the environment "Test" or "Report" anything you like. In your case Live reporting from test code and test database might/might not alter quality of reports as it is not clear what business logic goes into generating that report and what changes are there in test code which are not there in production. But, yeah, you should avoid this practice.
tf