Need Active-Active Clustering structure

Question

question

adkalavadia asked Nov 26, '12

Need Active-Active Clustering structure

Dear All, I need a favour. Currently i have one server with SQL 2008 R2 Enterprise and our applications (Telecom Billing applications). Application Details : ( All Application are data driven)
5+ Application Running 24*7
15+ application will run on demmand
Web Site with 100 concurrent user
Main Databases : ConfigureDB - 20 GB
ClientsDB - 90 GB
CDRDB - 1.25 TB (It grows around 40 GB every day. Partitioning already done)
ReportingDB - 800 GB (It grows around 10 GB every day. Partitioning already done)
ApplicationTempDB - 10 GB
All above apps and DB server is running on one server. Now we want to distribute this load to two server with HA. We can distribute our application between two server. For DB server, We want identical databases on both side. We need Active-Active for ConfigureDB, Active-Active (Read Only ons side) for ClientsDB and Active-Passive for all other DB. 1) How can i make Active-Active for ConfigureDB? clustering or Replication?
2) How can i make Active-Active (Read Only ons side) for ConfigureDB?
3) Active-Passive for CDRDB,ReportingDB? If we Upgraded to SQL SERVER 2012, How should we get benifit from it in our scenario?

sql-server-2008-r2 sql-server-2012 replication mirroring clustering

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-11-26T18:49:27Z

Gianluca Sartori answered Nov 26, '12

With SQL Server 2012 you could easily scale-out your setup with [AlwaysOn][1].
You could have different copies of the same database in read-only or read-write mode, depending on application intent (it has to be declared in the connection string).
Using SQL Server 2008 you can't achieve the exact same thing, but you have to stick to clustering and/or mirroring. Clustering is not available in the active-active flavour, not in the true meaning of it. When you use "active-active" referred to SQL Server failover clustering you always mean that you don't have a pure standby node, but there's another instance running on the passive node.
Mirroring in SQL Server 2008 can achieve a subset of the features found in SQL Server 2012 AG, but the main difference is in readable secondary replicas, without the need to use snapshots.
A technology that allows having two copies of the same database in read-write mode is peer-to-peer replication. Be warned that P2P replicas undergo severe limitations (no schema modifications allowed, just to name one).
Summing it up:
1) No active-active clustering is available in SQL Server. P2P replication is the technology that resembles to that the most.
2) SQL Server 2012 AlwaysOn AG with readable secondary replicas allows having a read-write copy of the database and multiple read-only replicas.
3) Seems to me no different from 2)

Hope this helps
Gianluca [1]: http://msdn.microsoft.com/en-us/library/ff877884.aspx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question