May 3, 2009 at 8:51 am
What are my options for a high availability system that alows regular roll outs of schema changes. I am looking for an HA solution that allows hot fixing of SQL objects rather than just a fail over.
Example hot fixes might include new tables or altered tables and possibly the SPs to update/insert from these tables. I need to be able to roll out these updates with minimal or zero down time. All suggestions welcome.
Thanks,
May 4, 2009 at 12:13 am
All forms of high availability allow schema changes. The only option that has any sort of limitation on schema changes is replication.
May 4, 2009 at 1:30 pm
May 4, 2009 at 1:40 pm
Jules Bonnot (5/4/2009)
So mirroring and log shipping will allow me to run schema updates on the secondary or slave DBs whilst the transactional replication will continue feeding the uneffected tables?
If you run mirroring or log shipping, there's no need to run schema updates on the standby instances; that will be done automatically.
May 4, 2009 at 1:44 pm
May 4, 2009 at 1:57 pm
Jules Bonnot (5/4/2009)
David my point is to use the fail over as a place to roll out changes before switching the secondary to be the master. So avoiding the load on the live. Ya get me?
If that's your goal, then mirroring and log-shipping are not going to work for you. You'd be better off looking at some type of replication to keep the data up to date, and manually applying the DDL scripts to your secondary server.
May 4, 2009 at 1:59 pm
Jules Bonnot (5/4/2009)
David my point is to use the fail over as a place to roll out changes before switching the secondary to be the master. So avoiding the load on the live. Ya get me?
No, how could you use your secondary server to roll out changes no that's not possible!!?......The basic concept itself of maintaining HA to replicate the changes to the secondary server.
May 4, 2009 at 2:09 pm
Krishna is correct. No form of high availability allows you to modify the schema on the secondary/offline database. There is a very limited amount of changes you can do with replication, but replicated tables should be modified on the publisher first.
May 4, 2009 at 2:15 pm
May 4, 2009 at 2:17 pm
I would think you would want to roll the changes out to a "QA" or "Staging" environment first.
May 4, 2009 at 2:18 pm
Jules Bonnot (5/4/2009)
ok so for a big roll out the options are take the live box down for a while or run the scripts on live and hope the testers did their jobs properly? I was hoping i could run them on a warm standby and bring that live if it looked like everything had worked ok.
I would recommend that you create a QA environment, where you can apply these types of scripts; your testers would then run their tests against that environment. Once everyone is satisfied, then run those same scripts against your production database.
May 4, 2009 at 2:21 pm
May 4, 2009 at 2:24 pm
Backup and restore is the most commonly used technology. Backup production, restore it to staging/QA, run your scripts/rollout process, and test. If all looks okay, repeat the rollout in production.
May 4, 2009 at 2:28 pm
May 4, 2009 at 2:32 pm
What do you mean by "losing the transaction"? If you mean that it won't be getting hit with live data, that is correct and no there is no elegant way to do it.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply