Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wesdev
Starting Member
1 Post |
 Posted - 2009-03-18 : 05:48:23
|
| We have a distrubuted production environment where there are 3 linked servers on separated boxes and thus separate instances. This has a symetrical set up on the fall back side. We plan to migrate from SQL2000 to 2005. As the fall back side of the environment is on a different domain we had planned to use synonyms to set up an abstraction layer in order to mask the fully qualified names [server.dbname.owner.object_name]. Initially, we plan to try and set up peer to peer replication from Prod to Fallback with bi-directional replication so that we could point our web application at either the leg should either we need to do some work whilst having 0% down time. Since, then another team member has been looking at mirroring? Just a few quick questions. 1. Are synonyms replicated in Peer-to-Peer replication or can you specify which objects to replicate? I suppose the same goes for system owned tables? e.g linked servers?2. Is peer to peer the way to go is mirroring a better option? Could we invoke mirroring on the fallback side for DR purposes?is there anything else we should be wary of?Any help is greatly appreciated as i'm fairly new to all this...RegardsWesJack Bauer of all trades... |
|
|
zzzbla
Starting Member
13 Posts |
Posted - 2009-04-03 : 07:39:06
|
| Hi Wes,I don't understand why you'd use synonyms for fallback - if the production server is down and you fallback to your DR server, how would synonyms help you? I don't think it has to do with the DR environment being on a different domain.As for mirroring vs. replication, the bottom line is that it depends on your requirements and budget:P2P Replication is an enterprise edition only feature (you have to pay for license per node as all nodes are active). It allows you to work with 2 or more SQL instances. With P2P replication all selected articles (tables) are replicated among all nodes. All nodes allow both read and write operations. The changes are propagated asynchronously and thus if you use it as a DR solution, some data loss is possible. In SQL 2005, there's hardly any conflict resolution (what happens if two rows with the same PK are inserted in two of the nodes, or what happens if the same data is changed in two nodes). There are some improvements with regard to conflict resolution in SQL 2008. In SQL 2005, in order to add or drop a peer, you must stop all activity on the other nodes (no longer required in SQL 2008). Data changes are propogated, and also table schema changes (mostly columns). Indexes modification (create/alter/drop) are not propogated, and the same goes for stored code (views, sprocs, functions) if I remember correctly.Mirroring comes in two flavors - synchronous (high safety) and asynchronous (high performance). The first is available in both standard and enterprise editions, the second only in the enterprise edition. You only need licensing for the active node (unless you use database snapshots to read from the mirror). It allows you to use only 2 servers (principal and mirror) and the data in the mirror is not available to read or write unless until you failover to it, or set up database snapshots over it (another enterprise edition only feature). In high safety mode, you get two-phase-commit - the transaction is not committed in the principal before it is committed in the mirror, so there's no data loss. In high performance mode you may lose some data. If you use db-mirroring, everything within the database is mirrored including changes to all data and objects (including index rebuild for example). Look up the subjects on books online for a lot more information.Hope that helps,S. Neumann |
 |
|
|
|
|
|
|
|
Peer to Peer vs Mirroring, Synonyms?