Summary: Is there anything wrong with uploading content directly to production? This is not code change or functionality change. Only content editing/adding. We currently use 4 servers to do this, it's a bit of a pig. For details, read below.
I just inherited a SMB network that has all kinds of idiosyncrasies. One such idiosyncrasy is baffling in its complexity. I'm pretty sure that it's Rube-Goldberg-machine-like but I want to be certain.
We have a client/server application that uses an IIS-based application server. This application server mediates web GUI and a database connection - which in our case, is hosted on another server (MSSQL2005). There are .tif files that are uploaded via this application and stored in the database as a blob. There is also data that makes a mask over the image (so that it can make populated forms). We also host a public server that acts as a repository for these images/masks so that others do not have to manually create them.
OK, with me so far? Basically, .tif file and data go in one end and at the other end, the public can download this image/data.
Here's the strange part. Rather than having our users upload these completed (and QAed) images directly into production, they go into an internal server. A process then extracts the blob and recreates the .tif file. This process also extracts only the necessary data for the forms. It then goes to a staging server. This staging server is a duplicate of the production server, but we never actually use this part of it - except for this process. Once in staging, another task runs to finally replicate the image and data to the production server. The staging server is, however, used for web development. If something happens, it can break this chain of events and stop the replications.
It is also worth noting that this production server is backed up regularly, so the staging is not meant for disaster recovery. The staging server is also not in any way public, so it's not used for redundancy. It's just there.
To add insult to injury, these tasks seem to be done with vbs scripts, bat files and Windows scheduled tasks, rather than any kind of SQL Server task/trigger.
My question is "is this all necessary?" Why can't a trigger be set up on the original SQL server to update the production server whenever a QA flag is set to true? Why go through all of this copying. Is there a reason that I am missing?
I just want to make sure that I'm doing the right thing to get our network sorted out.
Thanks for reading.