April 25, 2017 at 12:42 pm
Hello,
Through listening to the various youtube classes, I heard that we could create a database and have it sitting in two data centers. I wasn't sure if the speaker was referring to an elastic pool or not.
However, it would be great to have the users in the North region using a datacenter in their region. Then a user in the south region could hit the database from a datacenter in the south region.
I wish the speaker went into more detail on how this is done.
Anyone have an idea or can point me in the right direction?
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
April 25, 2017 at 3:39 pm
It's not data pools. That's a way of managing and paying for Azure SQL Database, not something like the Data Lake (completely, utterly different technology), which is distributed storage. Within Azure SQL Database, it's Geo-Replication that does this.
"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
April 26, 2017 at 6:43 am
Grant,
As I have been studying GEO replication, I see it as mainly a failover situation.
1. If you have a primary in the north region, you can set up a secondary in the south region. I know you can also set up a secondary in the north region on a separate server .
2. However, users of the database would always use the north region data center unless there is a failover to the south region.
I am not sure if with geo replication you set up users in the north region to use the primary in the north region. And then have the users of the same database that are in the south region use a secondary in the south region. From my understanding, those in the south region would still use the database from the datacenter in the north region if it is primary.
Is it possible to set up a configuration so that users of the database in the north region automatically get the data center in the north region. At the same time, users of the database in the south region would use the database from a south region datacenter?
Thanks.
Things will work out. Get back up, change some parameters and recode.
April 26, 2017 at 6:59 am
Read this overview article. The primary purpose is absolutely for failover and continuity. However:
Another key benefit is that the secondary databases are readable and can be used to offload read-only workloads such as reporting jobs.
Because the secondaries are readable, you can do what you're describing.
"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
April 26, 2017 at 8:27 am
Thanks Grant. I really appreciate your insight.
No, I get it with the readable secondary, I can have my reports look to a readable secondary. That is a big help.
However my manager wanted a user in the north region to be able to update (webapp) his/her information using a north region data center. He wanted a user in the south region to be able to update his/her information using a south region data center.
I think the webapp is pointed to the primary (updateable) database. Which data center is used is not something we can designate, based on user location. I think it is determined by the primary role. If the primary is in north region data center, it will use that. If we fail over to the south region, the primary is there. I don't think we can designate two data centers. From all of my research, in order to meet my manager's request, we would need we need is two primaries in two data centers and some sort of traffic manager.
Ok, you are coming from the North region, so you use the primary in the north data center.
Ok, you are coming from the South region, so you use the primary in the south data center.
And of course, two primaries do not exist.
Things will work out. Get back up, change some parameters and recode.
April 26, 2017 at 8:52 am
That's no good. You're basically talking merge replication here. It's a nightmare. You'll need to move to VMs and you'll have to completely redesign and rebuild your databases. Or, you get into distributed systems with eventual consistency. Assuming we're not talking banking, that can work. Best bet remains to do the writes in one location. For a web app, the latency shouldn't be that bad for someone in the continent. It's only when you start hopping oceans that latency goes to crap.
"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
April 26, 2017 at 10:02 am
Again, I really appreciate your insight. I really think the geo replication is going to work for us.
Thanks.
Things will work out. Get back up, change some parameters and recode.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply