September 5, 2018 at 9:28 am
Hi,
we plan to migrate several database application to Azure managed instances.
Users will be using it for transaction and reporting
Curretly reporting data transfered from database A and B to database C (subsriber) using transaction replication and in addition we use triggers at subsriber to recalculate transfered data and insert into few extra tables
So
database A and B used 90% for transaction processing and database C 90% for reporting
Original idea was migrate to everything to azure SQL database since it has "read scale out" starting with Premium tier
Read Scale-Out allows you to load balance Azure SQL Database read-only workloads using the capacity of read-only replicas(https://docs.microsoft.com/en-us/azure/sql-database/sql-database-read-scale-out)
After evaluation this concept was droped since it azure database does not support triggers and cross database references possible but extremely slow.
I started evaluation of "General Purpose" SQL managed instance
1.triggers supported
2.cross database references also supported
I tried to setup transaction replication on same instance but this future annonced but not working as of now
"This edition of SQL Server cannot act as a Publisher or Distributor for replication"
(https://social.msdn.microsoft.com/Forums/azure/en-US/84c2c5e0-4117-4a69-b044-61e600dc539a/managed-instance-errorthis-edition-of-sql-server-cannot-act-as-a-publisher-or-distributor-for?forum=ssdsgetstarted)
Currently I see 2 but expensive ways to migrate databases
I might be missing some important setting in "General Purpose" since it create 5 (five) copies of database prevent corruption and power outage (analog of high availability),but I did not find single documentation line
saying that any of those five copied could be used for read as part of load balancing
September 7, 2018 at 5:24 am
Availability groups and read only secondaries are, I think, supported on managed instances. That would be a solution.
"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
September 7, 2018 at 12:54 pm
"Availability groups" functionality integrated with managed instance, dba/user has no control over it. copies of database created for redundancy only.
I had conversation with MS support
based of MS support answers/suggestions
1. managed instance will be generally available in October
2.even if transaction replication was working between db A and B on same server in Azure managed instance DO not do it, you will double up I/O and slow perfomance since all databases in single managed instance placed on same set of drives
2. geo -replication (business tier with readable replica) announced but the is no data for preview at this moment.
3. price of managed instance is not changing ,you paying for instance you build and not for traffic compare to Azure sql database( I saw articles when people were trying create/modify indexes on azure sql database ,since you have to pay for each table "scan")
250$ free subscription will run out in 5 days once you create managed instance(General Purpose (800 GB, 8 vCores), then you will start paying even for evaluation
September 8, 2018 at 4:22 am
Have you given any thought to using Azure BLOB Storage, Azure Data Lake Store or Azure Data Warehouse with Azure Data Factory copying the data?
For example, you can setup Azure Data Factory to copy data between data sources. One example should be database to database, another is database to BLOB storage and then from there, populate multiple databases. Yet another is using Azure Data Lake Store Gen2, which runs on BLOB storage for cheaper the costs and then you can use Azure Data Lake Analytics for the transformation Data Factory lacks back into BLOB storage, which then populates all your databases.
I work mostly on the data warehousing side, so I don't work too much with transactional replication. I would assume the above solutions would only be bad if the speed of how they capture and update that history is too slow for you. But other than that, creating a table that just has transactional history that Data Factory copies every whatever frequency to another database might be a good option. Then you just refresh the other databases based on that history if that's possible.
September 8, 2018 at 11:30 pm
I don't know where you heard this. It's not true. You pay for storage in Azure SQL Database. You pay for the amount of processing power you want (either DTU or vCore). You don't pay more for scans and less for seeks. If you move the data out of Azure, you pay for that, but you'd pay for that with Managed Instance too.
If the primary concern is reporting, within Azure SQL Database, you can connect to your database, but use secondaries for read only reporting. Read more about it here. I would expect this will come to Managed Instance as well, but I don't know for sure.
"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
September 9, 2018 at 10:19 am
ebooklub - Friday, September 7, 2018 12:54 PM3. price of managed instance is not changing ,you paying for instance you build and not for traffic compare to Azure sql database( I saw articles when people were trying create/modify indexes on azure sql database ,since you have to pay for each table "scan")
I don't know where you heard this. It's not true. You pay for storage in Azure SQL Database. You pay for the amount of processing power you want (either DTU or vCore). You don't pay more for scans and less for seeks. If you move the data out of Azure, you pay for that, but you'd pay for that with Managed Instance too.
If the primary concern is reporting, within Azure SQL Database, you can connect to your database, but use secondaries for read only reporting. Read more about it here. I would expect this will come to Managed Instance as well, but I don't know for sure.
You don't think that processing scans isn't going to cost you more in DTUs if a seek will do?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2018 at 5:02 am
Jeff Moden - Sunday, September 9, 2018 10:18 AMYou don't think that processing scans isn't going to cost you more in DTUs if a seek will do?
You can literally calculate cost savings as you tune queries. WHOOP!!
However, I stand by my statement. They're not charging you for a scan or a seek. You're charged for vCore or DTU limits. Scan below the DTU limit, you don't pay more or less than a seek below the DTU limit. You pay the same no matter how good or bad your queries are. Now, tune your queries and maybe you can get to a lower DTU limit and save money (or avoid moving to a higher one). That's still not a charge for a scan.
"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
September 10, 2018 at 6:16 am
Grant Fritchey - Monday, September 10, 2018 5:02 AMYou can literally calculate cost savings as you tune queries. WHOOP!!
However, I stand by my statement. They're not charging you for a scan or a seek. You're charged for vCore or DTU limits. Scan below the DTU limit, you don't pay more or less than a seek below the DTU limit. You pay the same no matter how good or bad your queries are. Now, tune your queries and maybe you can get to a lower DTU limit and save money (or avoid moving to a higher one). That's still not a charge for a scan.
They also auto-tune for you if you like. Totally forgot about this feature when I went to check the small app database I created to power my custom ETL for my data warehouse. The system is analyzing every query and how much DTU it's using and it applied recommended indexes to help improve performance on some of my queries, which actually worked out well.
September 10, 2018 at 6:27 am
Grant Fritchey - Monday, September 10, 2018 5:02 AMYou can literally calculate cost savings as you tune queries. WHOOP!!
However, I stand by my statement. They're not charging you for a scan or a seek. You're charged for vCore or DTU limits. Scan below the DTU limit, you don't pay more or less than a seek below the DTU limit. You pay the same no matter how good or bad your queries are. Now, tune your queries and maybe you can get to a lower DTU limit and save money (or avoid moving to a higher one). That's still not a charge for a scan.
I get that but it does take some CPU to read through data doesn't it?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2018 at 7:11 am
Jeff Moden - Monday, September 10, 2018 6:27 AMGrant Fritchey - Monday, September 10, 2018 5:02 AMJeff Moden - Sunday, September 9, 2018 10:18 AMYou don't think that processing scans isn't going to cost you more in DTUs if a seek will do?
You can literally calculate cost savings as you tune queries. WHOOP!!
However, I stand by my statement. They're not charging you for a scan or a seek. You're charged for vCore or DTU limits. Scan below the DTU limit, you don't pay more or less than a seek below the DTU limit. You pay the same no matter how good or bad your queries are. Now, tune your queries and maybe you can get to a lower DTU limit and save money (or avoid moving to a higher one). That's still not a charge for a scan.
I get that but it does take some CPU to read through data doesn't it?
Yeah, absolutely. But you don't pay for each CPU call. You pay for a limit then you work within that limit.If that is millions of beautiful seeks or one giant ugly scan, pay is the same. You don't pay more for a scan.
"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
September 10, 2018 at 7:14 am
It does take CPU, which is bundled into your DTU limit cost. You don't incur more cost for worse queries, unlike storing more data. However, more DTUs/query does impact performance, and if you need to scale to a higher DTU level, it does cost. Code still matters here unless you have a somewhat known/capped workload of sorts.
If you are trying for real time reporting from database C, you are going to have some limitations in Azure. You really have an ETL issue, and rather than stick with replication when moving to the cloud, I'd consider other ETL type solutions. If you regularly move data to the database C, why not alter that database to include CDC, change tracking, or simple methods of knowing what needs to ETL and then use a tool to do that? Then you can pick data lake, Azure SQL DB, Azure data warehouse, etc.
September 10, 2018 at 7:16 am
Grant Fritchey - Monday, September 10, 2018 7:11 AMYeah, absolutely. But you don't pay for each CPU call. You pay for a limit then you work within that limit.If that is millions of beautiful seeks or one giant ugly scan, pay is the same. You don't pay more for a scan.
But, on an individual basis, you would end up paying more for a scan than a well built seek followed by a range scan of only the data you're trying to get to simply because the seek/range scan takes fewer clock cycles on the "core" than the code that does the full scan, correct?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2018 at 7:21 am
Jeff Moden - Monday, September 10, 2018 7:16 AMBut, on an individual basis, you would end up paying more for a scan than a well built seek followed by a range scan of only the data you're trying to get to, correct?
No. You pay X amount for X amount of DTU/vCore. How you use that amount is up to you. There is no higher costs or lower costs for the operations within the system. As Steve says though, bad code (bad structures, bad statistics, all the rest of it) will cause poor performance and if you want to throw hardware at the problem, you can by paying for more DTU/vCore. So, we can measure "cost" of a query through DTU and we can tune it to make it "cost" less. However, unless you change your DTU limits, you don't pay a penny less despite making that improvement. You pay for the DTU/vCore limit that you want. Then all operations occur within that.
"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
September 10, 2018 at 7:28 am
Grant Fritchey - Monday, September 10, 2018 7:21 AMNo. You pay X amount for X amount of DTU/vCore. How you use that amount is up to you. There is no higher costs or lower costs for the operations within the system. As Steve says though, bad code (bad structures, bad statistics, all the rest of it) will cause poor performance and if you want to throw hardware at the problem, you can by paying for more DTU/vCore. So, we can measure "cost" of a query through DTU and we can tune it to make it "cost" less. However, unless you change your DTU limits, you don't pay a penny less despite making that improvement. You pay for the DTU/vCore limit that you want. Then all operations occur within that.
I get that... what I'm suggesting is that because a scan would use more DTU, it's effectively more expensive because it IS poor performing code compared to the much more effective seek/range scan, no?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2018 at 7:55 am
Jeff Moden - Monday, September 10, 2018 7:28 AMI get that... what I'm suggesting is that because a scan would use more DTU, it's effectively more expensive because it IS poor performing code compared to the much more effective seek/range scan, no?
Strictly speaking, no. Again, we pay for a limit. What we do within that limit doesn't affect the limit.
Don't get me wrong. I'm not advocating for bad code. I'm not saying we shouldn't tune or that tuning is not a net positive. I'm being very specific. The OP said "you have to pay for each table scan" and that single statement is untrue.
Let's take an example. I have a database on my portal right now. It's Standard, S0, $15/month with a 10 DTU limit. If today I run a query that does a seek, it takes up, let's say, 1 DTU. I pay $15 for the month. If tomorrow I run a query that does a scan and it takes up 10 DTU. I pay $15 for the month. Scans or seeks, nothing changed. If I don't use any DTU, the cost is $15/month. And remember, the DTU is a transaction limit, like the size or number of cores (vCore is more precise in this regard). So, even if one query uses all 10 DTU, that doesn't mean no more queries can run. It's a DTU limit, not a bucket. I can use no more than 10 DTU at a time, but I can use 10 DTU, all day long, all month long, scan, seeks, whatever, my cost will be $15 for that month. Not counting, of course, storage.
"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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply