December 16, 2019 at 1:05 am
Hi Everyone,
I have a database (~50GB in size) with content that is constantly changing, the challenge is that ideally, I need to have a copy of this live database in geographical local proximity; at a minimum in 3 primary zones, America, Europe, and Asia, but depending on the complexity, in further locations.
I know there's a number of ways to accomplish this with differing trade-offs, but wondering the thoughts of the community and how others might have met this challenge in the past? What methods were successful for you or how best would you tackle this problem?
In essence, the requirement is that data cannot be stale (e.g. updated in one location but not in the other) and transactions need to complete as quickly as they can.
Thanks
Anubis.
December 16, 2019 at 10:54 am
Your requirement is just to maintain the copies of the DB or you are also thinking to implement HA/DR solution out of it? Also, mention the version and edition of SQL you are using. How much data (in MB) is written on an average daily ?
December 16, 2019 at 2:03 pm
Do you have to be able to both read and write in each of these locations? If so, that radically complicates the issue. If all writes are to a single location (with failover possibilities) and reads from multiple locations (a very common scenario with lots of choices in implementation), you can look at something like Availability Groups. Azure also has a great solution within the SQL Database through GeoReplication.
If you need to do multiple writes to multiple locations and then sync all that up across all the locations... woof! You will have to engineer that yourself. There is an old, barely supported, method called merge replication, but good luck with that. It will require that you completely reengineer your data store. If you're doing that, you may as well look to new technologies like CosmosDB instead. They are predicated on the very idea of a fully distributed system like you're talking about.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2019 at 1:30 pm
Hi Grant and Brahmanand,
Thank you for the replies; the amount of data written daily would be multi-GB across many thousands of transactions, so quite a lot.
Certainly, HA is important, the application this DB supports needs to be online at all times.
As to the read/write ability, yes, the ultimate idea would be to have these each being read/writable but I would like to understand the trade-off if reads were possible but writes were only to one. I'm assuming this would be replication and subordinates would be 'behind' the primary database by a timeframe of replication?
Thanks.
Anubis.
December 18, 2019 at 1:35 pm
Not necessarily replication. I'm very hesitant to ever suggest using that technology because it just doesn't get adequate support from Microsoft. No, instead, if you can write once and then distribute for reads, I'd go with Availability Groups or Georeplication in Azure (a completely different technology than traditional replication). Those offer the most stable support for what you want.
To get to where you can write anywhere and read anywhere, you must build it yourself or look to see if a third party solution exists. There isn't a native solution that does this well within SQL Server (yes, merge replication, but I've heard nothing but complaints from those who use it, see my initial comment on replication).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2019 at 10:19 pm
This was removed by the editor as SPAM
December 19, 2019 at 6:31 am
Thanks Grant for the reply. I'll take a look at Availability Groups as Azure isn't really an option as all business logic exists within stored procedures and I understand from reading on the Azure site that the SP feature isn't available.
Thanks.
Anubis.
December 19, 2019 at 7:45 am
Grant has suggested 2 features.
1) Availability Group : is available both in SQL Server as well as Azure (Azure SQL is also SQL Server. You will be able to leverage most of the functionalities of SQL Server in Azure SQL as well including the Stored Procedure)
2) Geo Replication : is specifically available in Azure SQL only.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply