August 25, 2014 at 11:59 am
Dear All,
We are having SQL Server 2012 which is configured in Mirroring. Both are Standard Editions.
These servers are configured in customer environment and we do process frequent releases on these servers. Each release includes Master data changes, Table changes, Schema Changes (some times) etc.
Usually we will take a downtime or hour or two to complete the release process. Now, our customer is asking for a ZERO Downtime setup. They are ready to invest additional hardware if needed.
Can anyone suggest how to design an architecture to achieve ZERO Downtime with NO data loss.
Thanks.
August 26, 2014 at 2:44 am
Actually, I do not know what you want to do.
migration or upgradation ?
August 26, 2014 at 3:03 am
This was removed by the editor as SPAM
August 26, 2014 at 11:39 am
Thanks Guys for your response.
Let me be more clear. This Database is in production and mirroring is working as expected. There is no issue with the database or Mirroring.
We have to process weekly releases on this database. During this release time we do lot of changes to database schema and master data. While we are deploying these changes we take the application offline as we do not want the users to have access - Because of the dynamic work flow configurations, application admin users are able to change the master data which may disrupt the changes what we are doing in the back end.
Is there any way, if I take a full backup of production database and Restore as another database then Deploy the changes on restored database - Then merge / replicate these changes to Production database.
Thanks,
Balu Kalepu
August 26, 2014 at 6:05 pm
A magic merge? Perhaps an SSIS package to migrate last minute data? If only some activities cause issues and are okay to disable a short time, that would be easier.
Are the changes every week very similar? Perhaps they could be broken into phases while upgrading in place:
create new tables
load new tables (Use a read-only snapshot to prevent blocking? I'm assuming this is the slow part of the upgrade)
load new tables again - picks up new changes made during prior load (fast? repeat?)
restrict access - start of production migration
load new tables yet again to get any last second changes - fast
swap table names or update views (with original table name) to use new table names - fast
apply other DDL changes - fast stuff only
allow access - end of migration
Only one process might be used for the load where only changes are processed. It should get very fast after the bulk is processed in the first load. If not, it might be a option to cancel the upgrade before using the new tables.
I'm assuming that everything is scripted in advance. If not, scripting the changes might be enough to reduce the down time to an acceptable amount.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 26, 2014 at 6:25 pm
BTW, a zero downtime application would be hard to design. You probably need to nail down the requirements to get at what the client needs rather than wants. ZERO is different than a minute or two. Are you allowed to restrict access or break connections? How long will multiple configurations be used at the same time?
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply