ColdFusion - Is there a way to pull query details from prod database in dev environment?

Question

I want a copy down of order details and related tables from prod to dev and I wanted to know whether we can query the details of prod server from dev environment using ColdFusion cfquery or queryExecute?

Answer 1

That all depends on your access to production. Security would dictate that prod CF can access prod DB, but there's no external access to prod DB outside of the prod network.

You would need to use the DB tools to export your production table data, then restore that data in your dev DB. This then trips multiple security issues related to Personal Identifiable Information (PII) data. You would need to scrub that data so that developers don't have access to actual, real user data.

So, can you (should you) do this via CF, no. Via DB tools, maybe.

Answer 2

You can. We did it where I used to work, but there might be issues. To do so

1. Arrange for your CF development environment to get the necessary database permissions. In fact, it might have them already.
2. Set up a DSN in the CF development environment.

Arian J. Moreno mentioned security issues. In addition to that, you have to worry about code complications, and the protection of the data.

Code complications are based on the assumption that your CF dev environment has dsns to both the production and development databases. That means the two dsns will have different names, which becomes important when you promote code to production. Developers will either have to make last minute code changes or incorporate some logic that makes the dsn a variable based on the environment. We did both.

Protection of the data is an issue when CF has read/write permission as opposed to read only. You need to ensure that production data is not inadvertently affected by development activities.

Answer 3

This is a perfect example of something that CAN be done, but probably SHOULDN'T be done.

There are multiple ways to accomplish what you're asking for, but all would pretty much involve either some additional configuration or working with another team.

And depending on the type of business you are, there may be legal or financial ramifications for this as well.

Regardless, in my opinion, this shouldn't be something that a Developer is allowed to do. Allowing a Development environment to have access to a Production environment invites a lot of problems. Not the least of which is the potential for a breach of your data by an inside threat. Or, more likely, the temptation to deploy code straight to Production (or do it accidentally), which is then lost at the next Dev deployment.

What I believe is your actual need, (and a fairly common need, at that) is to have Production-like data for Development purposes.

NOTE: I primarily work with Microsoft SQL Server, so if you use a different DBMS, my suggestions may not be applicable.

In my opinion, the way to do this is completely on the Database side. Let the databases work with each other instead of giving a Development environment access to the Production database.

Option 1: This is pretty much the type of thing SSIS (SQL Server Integration Services) was made for. Depending on your DBMS, this may be the simplest method.

Option 2: Configure Shared Server access between Prod and Dev. I wouldn't recommend this one unless there was no other choice. There are a lot of security implications.

Option 3: What I would recommend is:

• Make sure that in Development you script out any changes you make to the database.

• Backup your Production Database.

• Restore your Production Backup on the Development server.

• Run a script that will strip, anonymize or eliminate PII. <<< VERY IMPORTANT

• Run your integration script to make the necessary schema changes.

• You now have a fresh copy of your Production data in your Development environment.

  This also allows you to verify your deployment process for the database changes. And no matter what DBMS you are using, you should be able to do this. It will require some initial setup and testing, but once it's scripted the first time, it should be fairly easy to repeat.

If you just need a single table, script out the data and schema from Production, run the changes on the Dev database, handle PII, run the integration script.

If any of your needed data or table schemas involve Primary or Foreign Keys (or other constraints), you'll likely have to account for those, too. This is why I've always found a full Backup/Restore to be the best option. However, you have to make sure that you religiously script out the changes to the Dev database, or you'll lose them on the Restore.

Migrating Production data to the Dev database is very doable, but requires some planning, validation and testing to make sure it's correct, without sacrificing the security of your database.