0

I'm not an expert in replication for ms sql. For our project we need to upgrade database from time to time without downtime. Is there some kind of clustering/replication for such kind of needs.

E.g. we need to add some tables to database or even change/alter some data structure or add some data(rows) to tables. And during this update database should be up and responsive ?

Pondlife
  • 15,992
  • 6
  • 37
  • 51
snayko
  • 91
  • 1
  • 5
  • 1
    Can you define what you mean by "without downtime" and "responsive"? The database is never "down" (offline) when you're changing it, but individual objects may be locked. How do you make these changes now and what specific problems do you have with that approach? What version and edition of SQL Server do you have? – Pondlife Mar 27 '12 at 11:34

1 Answers1

0

when you altering the objects in the MS SQL Server database, database is up and responses for queries. But when the changes to object applies, this object can be locked until changes are finished. Another problem is that other users can lock the objects by their own transactions, and in this case your update script may be finished with time-out or deadlock error.

I cannot understand why you talking about the cluster, maybe I have wrong understand your question?

Alex_L
  • 2,658
  • 1
  • 15
  • 13
  • Yes it's my fault. It's mainly about mirroring database during updates. – snayko Mar 27 '12 at 09:59
  • So to get rid of locks, is that possible to roll upgrades on e.g one node (database) so another node would apply them as soon as it's unlocked and not busy. – snayko Mar 27 '12 at 10:06
  • No, mirroring and failover clustering cannot help with this task: mirror database is inaccessible when mirroring works (it is accessible only after removing of mirroring and recovering the mirror database, so it cannot help), and failover clustering has only one copy of database at a time: database file is used by single active node of a cluster. But I'm not ready to say something about your task and replication: I cannot see how it can help, but may be the solution with replication exists? - I have not enoygh expirience with replication. – Alex_L Mar 27 '12 at 12:39