Same DB, geographically diverse?

  • SystemlordAnubis

    Old Hand

    Points: 364

    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.

  • Brahmanand Shukla

    SSC Eights!

    Points: 938

    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 ?

  • Grant Fritchey

    SSC Guru

    Points: 396716

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • SystemlordAnubis

    Old Hand

    Points: 364

    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.

  • Grant Fritchey

    SSC Guru

    Points: 396716

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • This was removed by the editor as SPAM

  • SystemlordAnubis

    Old Hand

    Points: 364

    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.

  • Brahmanand Shukla

    SSC Eights!

    Points: 938

    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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply