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 |
|
reaand
Starting Member
3 Posts |
 Posted - 2014-02-12 : 15:34:21
|
| Hello, I'm new to this forum and almost a newbie to SQL Server Administration so probably I lack the right terminology. Anyways, I have to build an application able to read and write to different remote databases and I'm looking for the best solution.I thought to mirror them all in one place, but I read that mirroring has been deprecated, so thanks to this forum I found out about the peer-to-peer transactional replication. Or alternatively the wider Always On solution. The former seems to befit better my needs, having in mind that the priority is that both the enterprise applications and my application will read and write on the database, the former on the primary and the latter on the replica. Is it the right solution?A minor concern I have is that I read somewhere that peer-to-peer replication could be difficult to work with. And I'm counting also the possibility that it will also be deprecated.Where instead the Always On technologies seem to be the main solution from here onward. I didn't explored the possibility to set-up linked servers because at first glance it didn't seem a good solution. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-12 : 17:22:14
|
| AlwaysOn is a great feature if your schema is the same on the read and write side. We use AlwaysOn for our reporting and failover solution on our most critical application.We have 6 nodes in the cluster. 3 nodes are at our primary site, 3 are at our DR site. You can only have 5 replicas, so one of the nodes at the DR site just sits there (we could setup log shipping to it). At the DR site, the two replicas are asynchronous. At the primary site, all 3 replicas are synchronous. One replica is for the writes, one replica is for the reads. The third replica is in case we lose one of the other replicas, which actually did happen a couple of weeks ago. All I had to do was remove the failed node from the Availability Group, make the extra replica a readable secondary and then changed the read-only routing list so that read-intent traffic would flow to that replica.This solution works great for us and our needs. It's probably overboard for most applications, but it is what we need for this one. You likely only need two nodes and would have one of the nodes be either a read-intent only replica or a readable secondary replica. Our applications are using database drivers that support read-intent, however SCOM (Microsoft monitoring solution) can't handle it and causes all sorts of errors in the Error Log so we changed our Availability Group to use readable secondaries and thus allow older drivers or software that doesn't support it to connect to the underlying SQL instance.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
reaand
Starting Member
3 Posts |
Posted - 2014-02-12 : 18:11:15
|
| Thank you for your extended answer. Yes, the schema is the same. I need perfect replicas. So If I got it right, I should set up a single cluster with 2 nodes, having the two synchronous replicas sited in a different geographical location (is that not a problem?). Surely this solution seems the more appealing from many point of views. Even if the redundance is not my priority right now the fact to have it is quite tempting. May I ask you if setting up replicas as readable works even for load-balance or that's not the case? The only downside I can foresee is that the know-how needed to set up and manage a cluster is far greater than the one for a peer-to-peer replication. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-12 : 19:05:06
|
| If you are going to put the second node in a different location, you will need to make sure your network is super fast as network latency will play a part in the transaction speed as it's a two-phase commit. Ours is a three-phase commit since we have three synchronous replicas. Also, if the two locations are on different subnets, you'll have to implement the multi-subnet architecture. This can be troublesome for applications that are not using the proper database driver. With a synchronous AlwaysOn replica, the read-only side is real-time which was highly desirable for my critical system. We were previously using SQL Server 2005 with Transaction Replication and would have severe latency at times due to large transactions or a huge spike in data changes.The cluster setup is minimal, in my opinion. You use standalone SQL instances one each node that point to their own storage. AlwaysOn isn't a load-balancing technology. You can have two readable replicas, but it's not load balanced. Quote from Technet: "Read-intent connection requests are routed to the first available readable secondary on the read-only routing list of the current primary replica. There is no load balancing."But you sort of are load balancing by separating your read workload from the write workload.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
reaand
Starting Member
3 Posts |
Posted - 2014-02-13 : 03:42:31
|
| Sorry if the question sounds trivial, but so basically with this configuration if one node went off-line for some reason all the cluster will stop working? That's something I'd like to avoid. Letting the two nodes operate independently. Instead what if I set up the nodes as asynchronous, may I choose a frequency of synchronization? I have to start studying the documentation quickly. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-13 : 16:52:42
|
| The cluster continues to work if you lose a node. Maybe I didn't explain things well enough.If you lose a node, SQL Server will run the read-intent workload on the primary replica. We had a hardware failure 2 weeks ago on the primary replica. The readable secondary now was the primary replica and also servicing the read-intent workload. Not an issue for us as our hardware can support it, but we altered the Availability Group to bring in our 3rd replica to handle the read-intent workload.I don't know what you mean about frequency of synchronization. You can have scripts to go from async to sync and back if you'd like. This might help when a large transaction runs such as a large index rebuild. But if you have the hardware to support it (network, I/O, CPU and memory), the overhead of the 2-phase or 3-phase commit should be minimal.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Mirroring, peer-to-peer or AlwaysOn Groups