Experts, I'm back with my daily question 
Can a SQL 2012 on-prem database be replicated (with Transactional Replication) to an Azure instance?
Would the Azure instance be SQL 2019? The db is small 20 GB.
Thanks!
What kind of Azure instance? Your question is really - can my Azure instance subscribe to on-prem resources?
To answer that, you need to know what kind of instance.
See here: What is SQL Data Sync for Azure? - Azure SQL Database | Microsoft Docs
Some updated info and a demo here:
Edit: Regarding Azure SQL DB versions, they always support the latest version of SQL Server, including features that may not yet be in the on-prem SKUs. Some features may only be relevant to a cloud environment.
You can always set your Azure SQL DB to an appropriate compatibility level, like SQL 2012, if you need strict compatibility.
If this is an Azure SQL Managed Instance - then SQL Data Sync isn't supported (yet). That is why I asked what type of Azure instance.
Also review the limitations - there are quite a few and some that can be problematic.
Thanks for the replies. We have decided on Azure but have not yet determined which flavor.
Our primary need is to get an always-current SQL db hosted in the Cloud to be used for reporting.
As I understand it these are the options:
Azure SQL Managed Instance - (Can't use SQL Data Sync)
or Azure SQL Database (PaaS)
or SQL Server on Azure VM (IaaS)
Some thoughts/opinion on some options:
-
Managed Instance may not perform well on General, you may need Business Critical which is >2x as expensive. MS has re-engineered the underlying infrastructure in the past year or so, and it may perform better now. Check that Youtube channel I posted, they have a number of videos on choosing the right Azure database. (Keep in mind though that it's an MS channel and they tend to gloss over or completely avoid downsides, especially costs)
-
There's a feature in SQL 2016 and higher called stretch database, it's not meant for cloud replication but that's what it does. It can be exceptionally expensive, so in case anyone suggests it, steer clear of it. If you're staying with SQL 2012 then it won't be an issue, but keep in mind SQL 2012 is no longer supported by MS, except in a Managed Instance.
-
MS offers some hybrid configurations, including reduced Azure pricing if you have on-prem licenses with Software Assurance. This would benefit Azure VM IaaS more, you'll have to dig through the pricing pages. You also get discounts for reserved instances. Be careful though on your storage setup, the disk pricing is not included in those discounts.
-
The hybrid licensing may include/involve a gateway between your on-prem and cloud. This may require an ExpressRoute (or whatever it's called now) Azure resource that will cost additional money, and may not be subject to discount as it's not specifically a SQL Server feature.
-
Azure VM will give you the most options for replicating from on-prem. You could in theory set up an Availability Group with your Azure VM as a replica. That would probably give you the lowest overall latency, but you absolutely have to keep it asynchronous. It also requires Enterprise Edition if you're going to read from it. Otherwise you could try transactional replication, and if your latency requirements are in the 5-minute range, or longer, log shipping may be an option (although you'd have to roll-your-own log shipping, the built-in feature doesn't work for hybrid).
Regarding Azure pricing, the calculators are a good start but you will ALWAYS have additional costs. Suggest doubling whatever the calculator reports as a baseline, and monitor your actual Azure bill/cost dashboard frequently. Things like network egress charges, disks, etc. are underreported and you almost never get the full cost until the bill is generated.
Also curious as to what reporting tool you're using, and what benefit that cloud hosted data will provide.
2 Likes
Thanks, Rob.
The reporting tool is called iDashboard.
The on-prem instance is 2012 Standard. so I believe that eliminates the AlwaysOn availability group option, am I correct?
Correct - Standard Edition has BAG (Basic Availability Group) which can have one secondary - for one database that cannot be set to read-only.
Hmmmm, well that pretty much eliminates all the options I mentioned. 
Log shipping won't work with a Azure SQL DB or managed instance either, you only get one chance to restore an MI to a readable state, and it also makes it writable.
You'll either have to use replication, or look at change tracking or change data capture and write some custom sync code, if the Azure Data Sync feature isn't an option.
JeffW: You said "Standard Edition has BAG (Basic Availability Group) which can have one secondary - for one database that cannot be set to read-only."
Did you mean "can only be set to read-only"?
Thanks
No, a BAG cannot be set to read only.
jeffw8713 Thanks.
The Infrastructure group has installed SQL Server on Azure VM. But it doesn't look like an Azure instance to me: The image below is incorrect the SP is at CU 12, not RTM. But my question remains.
And I connect to it just like I connect to my on-prem instances. Does this look like a true Azure instance to you?
Thanks
I really couldn't say - but it really depends on what type of Azure instance. There are 3 (I believe) - one where you host a VM in Azure that has SQL Server installed, one that is a SQL Managed Instance and an Azure SQL Database.
It looks to me like this is just a VM hosting SQL Server.
1 Like
That's exactly how it looks to me, jeffw8713. A SQL 2019 Standard instance on an on-prem VM. But the client says otherwise. In meetings, we agreed to install SQL Server on Azure VM (IaaS Infrastructure as a Service). I didn't do the install so I'm not sure what was done. I wish I could confirm before I go to them and say it's not an Azure instance, then have egg on my face.
