We periodically need to modify the structure of our production database. In many cases, these changes correspond to changes in the codebase which reference the new changes.
We usually end up putting up a fail whale page for a minute while we pull the new changes and run the new SQL queries, but I'm interested in writing a component or something to run the SQL queries on the fly, so that no downtime would be required.
I haven't tried it yet, but here's my plan:
IF EXISTS so they run only once. I think it will also have to clear the model / persistent caches too.beforeFilter.Is this a crazy idea? Here's my questions: A. Will something like this work? B. Is there a better way to do this that I'm missing? C. What do I need to know about the model caches to keep from throwing errors. Presumably, the debug level will be set to 0 (because the site will be in production).
By the way, it seems relevant to mention that we are not on a load balanced system. We're on a single dedicated server.
Will something like this work?
Sort of. You will likely still suffer downtime. Even if only briefly while the queries finish running. You may also have concurrency issues if the site has heavy traffic during your deployment.
Is there a better way to do this that I'm missing?
With a load balanced system you could update one system at a time while diverting traffic to another system until all systems are updated.
You could feature flag your code. Code will not run until this feature is enabled. So launch your code and when your database updates are complete, enable the feature.
What do I need to know about the model caches to keep from throwing errors.
I am not familiar enough with caches, but cache could provide the illusion that the site is available. But any dynamic request (form submission) could still result in an error.
As an aside, take a look at Schema Migrations if you haven't already.
Will something like this work?
I'm sure it would work, but you can also pound a nail with a shoe if you hit it hard enough.
Is there a better way to do this?
Jason McCreary is absolutely correct with his comment mentioning how code shouldn't deploy itself and that you should be using a deployment process - whether that be (at the very least) custom scripts, migrations, or even a custom tool like my company's tool BuildMaster which handles database deployments as a first-class concept.
Using a tool like this to flesh out your process will allow you to incrementally build-up to a fully automated system easily, that way if you ever do add load-balancing (or some other additional infrastructure) you don't have spend a whole lot of time updating your deployment process all over the place. You can also plan for rollbacks, advanced deployment scenarios, and other cool things that cannot be accomplished simply by putting deployment code within application code.
For database deployments specifically, BuildMaster can manage the scripts and will deploy them in the same order automatically when the appropriate deployment actions are used. This will minimize any downtime you may experience trying to run them manually. You can also put in stop/start application actions which will clear any server-side caches. There will always be the problem of ViewState or other client side persistence however, but that's a different issue altogether.