June 19, 2008 at 1:05 am
Hi all,
We need an architecture in which one office is in pune and other in mumbai,
principal server is in pune and mirrored in mumbai,
now we need an architecture in which mumbai user will performing read only operation from mumbai server (local server )and write operation in pune server(remote server)
and pune user will be performing read and write operation in pune server(local server)
now when link between pune and mumbai goes down, mumbai users will not be having acess to pune server so at that time mumbai user will be performing read and write operation in mumbai server (local server) and when link goes up we want mumbai server to get updated with pune server.
how this is possible or any other different solution to this, what mandatory is we want mumbai users to perform read operation from mumbai server ONLY.
Regards,
Vinit Fichadia
June 19, 2008 at 5:24 am
Hi,
You can configure mirroring in asynchronous mode, so that when the link between principal and mirror disconnects failover won't automatically happen and when the connection reestablishes transactions from primary will flow to mirror automatically!
In mirroring you won't be able to access mirrored database, you need to take snapshot to access the database. FYI.
Regards..Vidhya Sagar
SQL-Articles
June 20, 2008 at 12:43 am
For mirroring you need to test network speed, if you want it the synchronous way.
Even assync your transaction load will need to meet the timeoutcriteria to couver the 100mi within reasonable time.
Maybe another replication topology (e.g. logshipping) may couver you needs. It does not have the "failover partner" technology for the connections, but you can have the database in read only mode.
Keep in mind, when using db-mirroring, if you want the data available at the mirror instance, you'll need enterprise edition because that can only be done with a db-snapshot. (refresh frequency, cleanup,...)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 20, 2008 at 1:52 am
We've looked at this type of problem before also.
The problem with using a snapshot against the mirror is... when do you update it? Secondly, to refresh the snapshot you need to drop and recreate it...which involves disconnecting your users for at least a few minutes.
My suggestion would be;
- if mumbai users can work with a snapshot and do not need 24x7 access, then use mirroring
- if your mumbai user profile is more variable, use peer-to-peer replication.
June 20, 2008 at 2:43 am
When the link between Pune and Mumbai goes down, do users update both databases (Pune users update their local DB, and Mumbai users update their local DB)?
If yes, then database mirroring will not work for you, as there will be no easy way to resynchronise the 2 databases when the link is back up.
In this case, some form of Replication (Merge, Transactional, Peer-to-Peer) will probably do the job.
Andy
June 20, 2008 at 2:59 am
AndyD (6/20/2008)
When the link between Pune and Mumbai goes down, do users update both databases (Pune users update their local DB, and Mumbai users update their local DB)?If yes, then database mirroring will not work for you, as there will be no easy way to resynchronise the 2 databases when the link is back up.
In this case, some form of Replication (Merge, Transactional, Peer-to-Peer) will probably do the job.
Andy
- With dbmirroring, at least one of both databases will be in "mirror-restoring" state.
- Only one db can be principal.
- if the linke is lost, and both db are taken online, they live their own lives, no sync can be done (automaticaly)
To resync the mirroring, you'll have to rebuild it from scratch ! in that case.
In normal usage, if the link goes down, the primary db just logs the transactions and if the link comes up again, the mirror will catch up the work (may take some time !).
I've tested a scenario having my laptop hosting the db, another desktop hosting the partner db and a third one being Witness.
Because of the witness topology, when if launched sqlsever at home (hence the laptop), the db stayed offline because it was unable to get a quorum with the witness.
When I got back at the office, it got the mirror state and catched up the work done during the periode I was at home.
Connected applications were able to work whilest my laptop was offline.
(only needed to reconnect at the time i shut down my laptop (for the first time))
I still have to test the scenario without the witness.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 20, 2008 at 3:12 am
Hi ALZDBA,
Thanks for your input. But isn't that what I said? I didn't dig deeper as to whether a Witness (and thus automatic failover) was involved, or whether the mirrored DB would remain in a offline state, etc, etc.
I simply asked the question of the original poster, as to whether both DBs would need to be updated when the link is down. If that is the case, then Database Mirroring won't fit the bill.
June 20, 2008 at 3:18 am
Oh I'm sorry. :blush:
I've forgotten to remove the quoted section.
I started with a quoted reply, but just wanted to add to your arguments.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 20, 2008 at 3:25 am
ahhh, that must be due to a Transaction Delay threshold being exceeded. I get that a lot with Database Mirroring. Coffee normally helps 😛
June 20, 2008 at 3:35 am
Yep, coffee did the trick :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 21, 2008 at 12:57 am
thank you all for your kindest responses.
but our requirement has been slightly changed i.e.
when link between pune and mumbai goes down, pune users will updating localdb and mumbai users will be waiting for link to get online, all the transaction will always be done at pune office and mumbai users will be performing read only operation from mumbai server and write operation to pune server
2 servers i.e, pune(primary), mumbai(secondary)
all read write operations at pune server will be done by pune user
all read operation by mumbai users will be done from mumbai server
all write operation by mumbai user will be done at pune server
when the link between pune and mumbai goes down, pune user will be performing read write operation at pune server only, and now mumbai user can do nothing except reading from mumbai server
now with this situation which architecture would be most feasible.
thanks
Vinit
June 24, 2008 at 2:14 am
A "strange" consideration.
In my test without a witness, but synchronized, it I simulated a principal failure (by shutting down its instance (instance01) ), I needed to perform
a Alter database [TheDb] set partner FORCE_SERVICE_ALLOW_DATA_LOSS;
even with the synchronized setup !!
The former mirrordb became in state (Principal, Disconnected).
After I started instance01 again, Instance02 got notice of this event and the database became in state (Principal, Suspended).
The instance01 db got state (mirror, suspended/restoring).
I needed to reactivate mirroring using: ALTER DATABASE [TheDb] SET PARTNER RESUME;
As you've stated your topology, this will scenario will not play in your case, but I just wanted to add this finding.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 24, 2008 at 8:12 am
You need to consider how "out of date" the database at Mumbai is allowed to get. Plus how often the link is liable to go down; for example, if the link goes down, is manual intervention required or should everything be automated?
I would suggest that Database Mirroring is not what you require though. Replication (eg. Transactional) or Log Shipping might suit your needs. Otherwise, a simple full DB backup, followed by a full restore at Mumbai, might suffice.
Andy
ALZDBA (6/24/2008)
Alter database [TheDb] set partner FORCE_SERVICE_ALLOW_DATA_LOSS
I think this is normal operation for a Database Mirror without Witness. Have a read of "Forced Service (with Possible Data Loss)" in BOL. When the old Principal (instance01) is brought back online, it automatically becomes the Mirror and the Mirroring Session is instantly suspended (without synchronising). This allows you, the DBA, to decide what to do. The risk is that data loss may have occurred between the old Principal failing and the old Mirror assuming the Principal role. At this point, you can decide whether to try and recover data which may still exist in the old Principal, or accept that the new Principal is ok and force the old Principal to become the Mirror.
erm, hope that makes sense!
June 24, 2008 at 12:05 pm
I would implement a transactional replication.
been pune the (publisher, distributor) and mumbai the suscriber. I would chosse continous update.
The read and Write operation would be routed in the appication with two diferent connection object.
Hope this help.
June 25, 2008 at 7:16 am
AndyD (6/24/2008)
...I think this is normal operation for a Database Mirror without Witness. Have a read of "Forced Service (with Possible Data Loss)" in BOL. When the old Principal (instance01) is brought back online, it automatically becomes the Mirror and the Mirroring Session is instantly suspended (without synchronising). This allows you, the DBA, to decide what to do. The risk is that data loss may have occurred between the old Principal failing and the old Mirror assuming the Principal role. At this point, you can decide whether to try and recover data which may still exist in the old Principal, or accept that the new Principal is ok and force the old Principal to become the Mirror.
erm, hope that makes sense!
You're right on top.
In a topology where both servers can be up and running, without being able to connect to eachother, the dba needs to determine which way to go after the connection has been restored.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply