alexwhite19800
asked on
SQL Replication question
Hello
I have an application that has a database, app server, and front end component. All of the information for the app is held in SQL database.
We have four data centers within our organisation. Two datacenters on the East Coast (NY/NJ) and two in the West Coast (LA and Burbank).
The latency between the NY/NJ is 2ms and the latency between LA and BU is 2ms. The latency between the east and west cost is approximately 50ms.
I was thinking of having the following:
Note: A SQL Cluster is implied to be a two node Active/Passive Windows cluster.
Solution A:
One SQL Cluster (SQL_WEST) across NY-NJ and one SQL cluster (SQL_EAST) across LA and BU. There would be SQL replication between SQL_WEST and SQL_EAST using Always On or something similar. SQL_WEST would be the Preference 1, with SQL_EAST being Preference 2.
Question1: Can Always On support latency of 50ms
Solution B:
One SQL cluster in each location, i.e 4 SQL clusters/ 8 SQL Servers. Always On replication between all.
NY: Preference1, NJ Preference 2, LA Preference 3, BU Preference 4.
Solution C:
One SQL server in each location, i.e. 4 SQL servers, with Always On replication between all.
We'll drill deeper into this with our technical teams, but which off the options sounds the best? And is any particular version of SQL or Windows (e.g. 2016) needed to achieve the best architecture?
I have an application that has a database, app server, and front end component. All of the information for the app is held in SQL database.
We have four data centers within our organisation. Two datacenters on the East Coast (NY/NJ) and two in the West Coast (LA and Burbank).
The latency between the NY/NJ is 2ms and the latency between LA and BU is 2ms. The latency between the east and west cost is approximately 50ms.
I was thinking of having the following:
Note: A SQL Cluster is implied to be a two node Active/Passive Windows cluster.
Solution A:
One SQL Cluster (SQL_WEST) across NY-NJ and one SQL cluster (SQL_EAST) across LA and BU. There would be SQL replication between SQL_WEST and SQL_EAST using Always On or something similar. SQL_WEST would be the Preference 1, with SQL_EAST being Preference 2.
Question1: Can Always On support latency of 50ms
Solution B:
One SQL cluster in each location, i.e 4 SQL clusters/ 8 SQL Servers. Always On replication between all.
NY: Preference1, NJ Preference 2, LA Preference 3, BU Preference 4.
Solution C:
One SQL server in each location, i.e. 4 SQL servers, with Always On replication between all.
We'll drill deeper into this with our technical teams, but which off the options sounds the best? And is any particular version of SQL or Windows (e.g. 2016) needed to achieve the best architecture?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Solution B: sounds OK if it is what your RTO-RPO based on SLA
AG does not need Sql server to be "Clustered" - > regular install on "clustered Windows Nodes is normally what you need
AG for DBs (see it as DB(s) "Mirror" on steroids
Sql cluster is for a sql server "instance" level HA (as a main idea)
AG does not need Sql server to be "Clustered" - > regular install on "clustered Windows Nodes is normally what you need
AG for DBs (see it as DB(s) "Mirror" on steroids
Sql cluster is for a sql server "instance" level HA (as a main idea)
ASKER
AG does not need Sql server to be "Clustered" - > regular install on "clustered Windows Nodes is normally what you need
AG for DBs (see it as DB(s) "Mirror" on steroids
Sql cluster is for a sql server "instance" level HA (as a main idea)
I guess what I'm getting at is, if I have an AG in NY datacenter, is this:
A: One SQL server with a SQL database installed that is replicated elswhere
or
B: Two SQL servers, using Windows clustering, with a SQL instance as active on one of the two servers
AG for DBs (see it as DB(s) "Mirror" on steroids
Sql cluster is for a sql server "instance" level HA (as a main idea)
I guess what I'm getting at is, if I have an AG in NY datacenter, is this:
A: One SQL server with a SQL database installed that is replicated elswhere
or
B: Two SQL servers, using Windows clustering, with a SQL instance as active on one of the two servers
if AG -- based on different Data center's Windows clustered nodes (AG) sql server ( they do not need to be installed as Clustered SQL servers -> they just need to be on a Clustered OS server)
Thus
Depends on your goal (back to your original post)
A-- can be good solution for you -- just you can tell
Thus
Depends on your goal (back to your original post)
A-- can be good solution for you -- just you can tell
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Vitor
Ok so if I had a SQL cluster in NY / NJ and wanted replication to LA / BU, what is the best technology to have?
Pref HA but DR if not.
Open to anything providing its SQL not third party.
Ok so if I had a SQL cluster in NY / NJ and wanted replication to LA / BU, what is the best technology to have?
Pref HA but DR if not.
Open to anything providing its SQL not third party.
Usually for HA you'll need an high performance network when using regional clusters.
Because data lost is acceptable for DR, a more slow network should be ok.
The issue with AG is that the transactions needs to be committed on Primary and Replica and with high network latency it may cause performance issue on your application.
Other options are (only for DR):
Log shipping
Disk Storage Replication
Because data lost is acceptable for DR, a more slow network should be ok.
The issue with AG is that the transactions needs to be committed on Primary and Replica and with high network latency it may cause performance issue on your application.
Other options are (only for DR):
Log shipping
Disk Storage Replication
test -test-test-> select the best what works for you to meet RTO -RPO
Measuring Availability Group synchronization lag
August 9, 2016 by Derik Hammer
http://www.sqlshack.com/measuring-availability-group-synchronization-lag/
An overview of High Availability and Disaster Recovery solutions available for SQL Server
https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/15/an-overview-of-high-availability-and-disaster-recovery-solutions-available-for-sql-server/
Measuring Availability Group synchronization lag
August 9, 2016 by Derik Hammer
http://www.sqlshack.com/measuring-availability-group-synchronization-lag/
An overview of High Availability and Disaster Recovery solutions available for SQL Server
https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/06/15/an-overview-of-high-availability-and-disaster-recovery-solutions-available-for-sql-server/
ASKER
Of the multiple options, which would you see as best?
Secondly,
When there is an AG, is the SQL database clustered or standalone? For example, in the Diagram here
https://msdn.microsoft.com/en-us/library/ff877931(v=sql.110).aspx under Supported Availability Modes
Are the SQL replicas one server or can they be two servers with a cluster for HA?