Webinar Recap
I recently participated in a webinar on Consolidating Your SQL Server Environment to the Cloud with Rick Lowe (@DataFlowe), an independent performance DBA/developer, and Dustin Dorsey (@SQLByDustin), managing database engineer for LifePoint.
The webinar was geared toward data professionals who are facing server sprawl and need to consolidate their on-premises SQL Server estate to either Amazon Web Services (AWS) or Microsoft’s cloud (Azure). Here’s what we covered:
- Introduction to the cloud, both AWS and Azure
- Licensing costs and alternatives, plus hybrid cloud implementations
- Issues of downtime, high availability, and parallel processing while transitioning from on-premises to the cloud
- Practical tips and tricks when conducting the consolidation, such as when NOT to move to the cloud
- Steps to take in advance of a move to the cloud due to different costing structure
If you missed the webinar, you can view it here.
During the webinar we received a lot of questions, which are captured below in case they might be of help to you.
Webinar Q&A
Q: What is the track record of Azure SQL Server Migration Assistant? What is the most effective means of migrating a SQL Server database to Azure?
A: I have had success with SQL Server Migration Assistant, but it is not the only option. The best option is going to depend on several factors, such as whether you need an online migration with very large amounts of data being moved. Some other options are replication, Availability Groups (AGs), DACBAC\BACPAC, PowerShell scripts, etc.
Q: I have moved 300 SQL databases to Azure SQL PaaS using elastic pools. I now find there is no PaaS version of SSRS offered by Microsoft. Why is that and what are the options for reporting?
A: My broad understanding is that Microsoft is encouraging us to move to Power BI Enterprise rather than SQL Server Reporting Services (SSRS). Alternately, you might run SSRS on-premises against databases in Azure or run Azure virtual machines (VMs) running SSRS as a standard, non-cloud application.
Q: How do you recommend DBAs deal with over-allocation that we may have had with on-premises servers and then all of a sudden we are now dealing with the same databases living on much less resources on AWS RDS Instances?
A: Over allocation is a little easier to get away with on-premises, but for the cloud you will want to try to allocate what you actually need the best you can, because you are paying for what you are allocating. In a nutshell, you need monitoring. (If you’re interested, check out in how SentryOne monitors Amazon RDS for SQL Server). You need something that tells you exactly the amount billable resources being consumed. Otherwise, you’re flying blind.
Q: It seems like the larger databases may have hidden some of our poorly written queries, stored procs, as well as poor indexes.
A: That is the norm. Consequently, performance monitoring is very important in the cloud because it can reveal if you are overprovisioning and thus overpaying and/or have a lot of poorly written queries that, with a bit of tuning, could run faster and cheaper in the cloud. Basically, bad code costs you more, potentially a lot more.
Q: What are some of the complexities of having a presence in both AWS and Azure and enabling these to be hybrid for your business?
A: If you’re running hybrid and the servers in both environments are independent of each other (not talking back and forth), then you probably won’t have a lot of issues, if any. If they are talking back and forth and interacting with each other, then the big considerations would be network bandwidth and egress charges. Remember, it’s more expensive to have data coming out than data going in. On the other hand, it’s worth pointing out that some of the biggest projects running on SQL Server in the cloud are intentionally spread across both AWS and Azure so that if there is ever a major blackout at one of the vendors, the other can act as a failover. That is a business continuity plan of the highest order and is commensurately expensive, but it is an indicator that your question is one that has been explored by other organizations.
Q: Would you recommend Azure for production systems? Seems like you do not recommend AWS for production.
A: Sure, I recommend Azure for production systems. Both Azure and AWS run loads of production systems and we fully support such architectures. While there are some gotchas, there are a ton of benefits of moving to a cloud-hosted solution. To determine which one makes the most sense, examine your business requirements and the benefits of each cloud vendor, then decide if those make sense to your organization.
Q: If you have an on-premises VMware vSphere environment, are there reasons to move anything to AWS or Azure?
A: Amazon has a full VMware hosting environment. So, if you’re heavily investing in VMware, you can literally lift and shift to the AWS cloud without changing a thing. Why consider it?
1) Your workloads might be cheaper in the cloud (especially if you can turn them off from time to time).
2) Your team currently has to fuss with physical hardware or networking.
3) You need a lot of flexibility and elasticity. And there are many more reasons.
Q: Have you heard any discussion around AWS’s consideration to create something like Azure Managed Instances?
A: No, sorry. Haven’t heard of anything like that on the horizon.
Q: With cloud databases, is there any reason to have Availability Groups?
A: Absolutely. Cloud databases are simply a means of implementing a relational database to support your business requirements. If your high availability (HA) business requirements include failover within 2 to 3 minutes of an outage, you will still want to have an AG to keep the business running.
Q: Do you think Amazon will ever introduce read-only replicas for SQL Server running on RDS? The current options are using AGs on SQL Server running purely on EC2 or using a plugin product called Cloud Basic to replicate databases from SQL Server on RDS to SQL Server on EC2.
A: Amazon does not have a program like the Microsoft MVP program, so we don’t have any insight into future plans from Amazon. Having said that, Amazon is amazingly driven when it comes to rolling out new features that can help sell their services. So, I wouldn’t be surprised if something like that might happen in 2020. But we can’t say with any certainty.
Q: In respect to security and government contracts, how does patching vary for SQL Server in cloud? On-premises, if you have your own servers/database and license, you can get patches and install within government timelines which are often 30 days after release for critical patches. How does AWS/Azure compare in that respect to patches being available on PaaS or Database as Service versus if you are using IaaS and can patch using your licenses? For Oracle, I heard on AWS Innovate that it may be 30-60 days before database patches are available.
A: When you are using VMs in AWS/Azure (IaaS), then patching and release management falls entirely on your shoulders. If you’re using Azure SQL Database (PaaS), then Microsoft keeps SQL Server patched even before on-premises patches are released. There are also special data centers in Azure that are specifically dedicated to high-security government organizations, such as the DoD. Amazon RDS, on the other hand, has details for their patching process posted here.
Q: Are you aware of any tools to map existing CPUs on premises to Azures ACU measurement?
A: I’m not aware of a tool that allows you to map on-premises CPU to a vCPU in the cloud from a performance perspective. I would recommend trying to run baseline tests on both to get an idea of what performance looks like. The demo in this webinar will show you a solution to look at converting usage to DTUs. If you don’t have SentryOne, then you can still use the scripts for the DTU calculator.
Q: In Azure SQL there is an option to do a subscription based on eDTUs or Vcores. If I am currently running based on eDTUs how do I size the same workload based on Vcores and will the price change?
A: The price will change. The ability to use Vcores with Database as a Service is relatively new. There are some pros\cons to using either\or so when moving a new database, I recommend running tests to see which one makes sense. I have not actually converted anything using DTUs currently to Vcore, but it could be cost effective to change.
Q: In Azure SQL Database, how do you know if you are in a group that has a noisy neighbor that is taking more than their share of the resources? On the flip side, how do you know if you are the noisy neighbor for others?
A: Short answer is you don’t. There’s a lot of technology and really smart people behind the scenes that make everything work. I have not personally seen any issues where this has been a problem, but I have heard from others that it does happen Long story made short, you get what you pay for. If your performance is suffering and you suspect a noisy neighbor, it’s probably on a lower tier server and should be upgraded.
Additional Information
If you have a cloud migration in your future, check out the following SentryOne resources:
- Cloud Migration Guide: SQL Server to Azure
- Cloud Migration Guide: SQL Server to AWS
- Azure SQL Database Managed Instance: A New Path to the Cloud
- Key Considerations for a Successful Data Platform Cloud Migration
Also feel free to schedule a one-on-one demo with a SentryOne expert to see firsthand how our solutions and tools can support your journey to the cloud.
