April 24, 2017 at 3:41 pm
Hello,
We have developed four azure databases. As we are planning the production environment, I have been given some requirements.
1. Highly available.
2. We need a copy in a data center on the west coast and a copy of the database in a data center on the east coast. We are located in central.
My thought is to create SQL Server VM, implement high availability groups with one secondary geo located to a data center on west and east coast. I will use one secondary as my source database to be used in my data warehouse. I didn't want ETL processes query the primary database.
Is this a good thought? Any other options and/or ideas. I am new to then azure environment but becoming more familiar with this environment.
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
April 24, 2017 at 3:57 pm
SQL Server in VMs in Azure or Azure SQLDB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2017 at 5:04 pm
If it's Azure SQL Database, no, setting up VMs is a bad choice. You want to take advantage of Geo-Replication. That's designed for exactly what you're looking for. If you just built regular databases within a VM, but the VMs are hosted on Azure, that's different. In that case, you'd probably want to look at Availability Groups. You can set up an asynchronous group across data centers. I would suggest researching that though. I think there's a relatively high cost for VMs across data centers (not sure about that though, hence the research).
"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 24, 2017 at 9:21 pm
I was thinking of setting up SQL Server in Azure VMs. But after Grant's reply, I may need to rethink that.
If I understand right, we are located in the central region. I would like to set up secondaries on the east and west coast.
In addition to that I need to set up a secondary for reporting. This was the reason that I was going to set up SQL server on a Azure VM. This way I could create high availability groups and use a secondary as reporting database.
However, I don't believe the above situation gives me the secondaries with designated geo regions on east and west coast.
Thanks.
Things will work out. Get back up, change some parameters and recode.
April 24, 2017 at 9:37 pm
After doing some studying, it may be that active geo replication would be a solution.
1.Create a primary database in central region.
2. create a secondary database in east coast region.
3. Create another secondary database in west coast region.
4. Create another secondary database in the central region to be used for reporting.
Is this better than setting up SQL Server on an Azure VM and configuring availability groups with one primary and three secondaries?
Thanks
Things will work out. Get back up, change some parameters and recode.
April 25, 2017 at 6:49 am
I wouldn't say it's better. It's different. Easier for one thing. The first question is, can you work within the Platform as a Service offering of Azure SQL Database? If so, then yeah, that's a very viable set up. If you need to have the full functionality of SQL Server (picking a random unsupported function, filestream for example), then you'll need to explore the VM route.
"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 25, 2017 at 7:45 am
I do like the active geo replication, but one finally question on that.
How do I access the secondary reporting when the secondary has the same name as the primary?
Another key benefit is that the secondary databases are readable and can be used to offload read-only workloads such as reporting jobs. If you only intend to use the secondary database for load-balancing, you can create it in the same region as the primary.
If I create a secondary database in the same region, how do use it for reporting. It seems to be the name is the same as the primary.
Thanks. Almost there.
Things will work out. Get back up, change some parameters and recode.
April 25, 2017 at 8:26 am
WebTechie - Tuesday, April 25, 2017 7:45 AMI do like the active geo replication, but one finally question on that.How do I access the secondary reporting when the secondary has the same name as the primary?
Another key benefit is that the secondary databases are readable and can be used to offload read-only workloads such as reporting jobs. If you only intend to use the secondary database for load-balancing, you can create it in the same region as the primary.
If I create a secondary database in the same region, how do use it for reporting. It seems to be the name is the same as the primary.
Thanks. Almost there.
In your connection string, you specify "application intent=readonly". The listener will route your query to the secondary replica.
April 25, 2017 at 8:36 am
to put a secondary in the same data center, you have to create a second server. That server is the connection string you would use. Putting a secondary in other data centers, again, you're creating new servers. Connecting to the database is through those other servers.
"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 25, 2017 at 10:18 am
If applications are looking at a database name and server and we fail over to the north region data center, the name of the server changes. Does that mean the application goes offline? With Windows clustering, there is one name that is used. Not the case with this Geo Replication.
Thanks.
Things will work out. Get back up, change some parameters and recode.
April 25, 2017 at 11:17 am
WebTechie - Tuesday, April 25, 2017 10:18 AMAlright, I let management know of my plans and they gave me the ok. The only question that came out of the presentation was what happens when a fail over occurs.If applications are looking at a database name and server and we fail over to the north region data center, the name of the server changes. Does that mean the application goes offline? With Windows clustering, there is one name that is used. Not the case with this Geo Replication.
Thanks.
No. It fails over & the connection string remains. You can use what was posted earlier for the connection string too. In fact, that's probably preferred.
"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 25, 2017 at 12:39 pm
Grant,
Thanks for all of your help.
I appreciate it!
Tony
Things will work out. Get back up, change some parameters and recode.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply