How do I keep the cost of an Azure SQL Database down?

  • First, I'll give some background and I'll try to keep this short.

    Like many companies, pre-pandemic we didn't have any cloud deployments. Then COVID hit, everyone went home and suddenly we starting to move what we could to the cloud. However, for us that hasn't been very successful. I believe the reason why it failed so badly is because we didn't have the experience/skills to migrate databases and applications to the cloud. Additionally, the concept of going to the cloud (regardless of which cloud) was only thought of as lift-and-shift. i.e.: Infrastructure as a Service. I believe the reason for this is because the operations people have lots of experience managing hundreds of VMs on-prem. And that is the ONLY way they want to manage the cloud. The IaaS approach is, so I understand, one of the most expensive ways of doing cloud. I've advocated for a long time that we need to modernize our applications and use a Platform as a Service (PaaS) approach, which, so I understand, is cheaper than an IaaS approach. However, my single voice is overwhelmed by the very large multitude of operations people, managers, developers, etc. who only know how "to do what we've always done". As a consequence, our new CIO is considering abandoning the cloud (Azure in our case) because of cost.

    Personally, and professionally, I don't believe this approach will be good for my career. Going back to doing things as they were done 20 years ago isn't of any interest to me at all. I've done that; I want to move on. So, I've thought that what I could do is migrate one of the apps I'm responsible for, into an Azure instance, using my Visual Studio Subscription monthly credits. Four or five years ago I had migrated a different SQL Server database to Azure SQL, with a similar idea in mind. However, that's all been lost. (Long story, which I won't go into. Just accept the fact that all the resource groups I'd created with everything in them, have permanently disappeared.) So, I'm having to start over again. I'm sure that the approach I took 5 years ago no longer works or at least has been superseded by some better approach. This time around I want to migrate a smaller app and database to Azure, starting with the database. This database is small. It is less than 25 MB in size. However, it's on an old SQL Server instance; it is in SQL Server 2012. I know I'm going to have to convert that to something newer. I forget what it was called, but I simply cannot do a backup, then copy the .bak to Azure Storage and restore into an Azure SQL database. What do I do, in order to prepare this database (I'll make a copy of it) to migrate to Azure SQL? (This is a warehouse database, so no PII or PHI.)

    Second, how do I keep my cost down? When I tried doing this before I put a larger database into Azure SQL, and it would easily burn through more than half my monthly VS Subscription with me doing nothing at all with it. That could happen to me now. There are times when my job requires a lot of attention, so I have to ignore this database for weeks at a time. At the moment, I have the time to work on it, but that isn't always the case. Is there a way of putting an Azure SQL Database to sleep, or inactive, until I'm ready to work on it?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Does this give you what you need? It sounds like General Purpose tier serverless autoscaling databases will turn off automatically after a certain period of inactivity and they can be scheduled. I understand the caution, I set up a Red Shift cluster in AWS and forgot about it until it used up my free account limit and I received my first invoice.

    https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql&tabs=general-purpose

  • Rod at work wrote:

    So, I've thought that what I could do is migrate one of the apps I'm responsible for, into an Azure instance, using my Visual Studio Subscription monthly credits.

    My recommendation is to NOT do that.  First, it's not YOUR data. You have no right to move it anywhere.  You need to ask permission with the good reasoning that it will be a test and, if the test doesn't work out, you'll have a plan to move it back.

    Otherwise, it could be an event that would require you to find employment elsewhere.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Bringing things directly across on a lift and shift - I don't think I have ever seen any client save anything, costs went up at least a little bit instead, most of the time costs went up a lot.

    For most of my clients, the cost of cloud/onprem wasn't the primary concern. Getting out of maintaining a data center, network core, etc and easier integrations for DR and offsite backups were. Some initially saved money by shutting servers down outside of business hours, but they usually ended up getting rid of that after having something cause the VMs to not boot up when they were supposed to.  It is also really rough for SQL servers that back ended any kind of reporting load, they end up being extremely slow every single morning.

    I have had a small number clients move from the cloud back on-prem after they realized that because of the way they did business, the intended greater availability and other integrations were not accessible to them; if something serious enough happened to knock their main datacenter out, they weren't going to be operating whether or not the servers were still available and they added failure domains running on the cloud instead of in the office.

    A big problem with IaaS is that the cost per unit of performance is high compared to running on-prem. A big problem with SaaS is that the performance patterns available tend to be very limited and it is not very tolerant of inefficient code.

    I have also had clients who recognized both of these things and did projects to prefer for cloudifying their network, by pruning processes that were no longer useful, tuning queries and applications, removing sprawl, etc so that their footprint would be smaller going to the cloud. They all were able to economize so much by reducing SQL, Exchange, SharePoint, UCS and NetAPP licensing and hardware that finishing the cloud project became unjustifiable for its the amount of savings it would provide compared to how much more they would need to spend.

    The best cloud projects I have worked on, all achieved something new over what they had on prem that provided value back to the organization, more than only a savings objective. If the organization doesn't want to take advantage of what is available to reduce costs, don't want to improve certain things like DR, availability, compliance, etc and costs are their only motivation, I am not sure there is anything that can be done to persuade them to stay.

  • You could start with the Azure SQL basic tier and scale up / down when required.

  • Ed B wrote:

    Does this give you what you need? It sounds like General Purpose tier serverless autoscaling databases will turn off automatically after a certain period of inactivity and they can be scheduled. I understand the caution, I set up a Red Shift cluster in AWS and forgot about it until it used up my free account limit and I received my first invoice.

    https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql&tabs=general-purpose%5B/quote%5D

    Yes, I think it does give me what I need. A small database in the cloud and add to that re-engineering a very poorly written ASP application which is over bloated to serve up only 12 pages, including reports. I don't know what the original developer had in mind. He left long before I was hired, and he didn't bother to document anything.

    Anyway, I don't want to burn through my monthly allotment when I can't work on the app. It will go dormant, if I do leave it unattended, then when the new month starts, I'll get to start over again.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • @createindexnonclustered, I was thinking more along the lines of a PaaS approach, rather than a SaaS approach. I'd like to re-write the old ASP.NET project, simplifying it. A SaaS approach might work; I don't know enough about SaaS platforms to say for sure. That and I confess I'm tired of just administering GitHub. I'm a software developer, so after 6+ months of migrating repos out of TFS into GH, I'm ready to get back to coding.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • By SaaS I was referring to Azure SQL DB/MI, not the whole application given that both of them provide SQL complete as a service to you with backups, availability and most configurations done for you. It appears Microsoft now officially declares those platforms are PaaS.

  • Here is a possible approach.

    1. Get the code for your DB into a VS database project and check that into a DevOps Git repo.
    2. Set the database project to target an Azure SQL database and make sure that it builds
    3. Set up an empty Azure SQL database
    4. Create build and deployment pipelines in DevOps to build and deploy your DB from your Git repo.

    Next step: transfer the data over using your tool of choice (you might need to disable constraints and faff about with IDENTITY columns to make this work properly and fast).

     

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • CreateIndexNonclustered wrote:

    By SaaS I was referring to Azure SQL DB/MI, not the whole application given that both of them provide SQL complete as a service to you with backups, availability and most configurations done for you. It appears Microsoft now officially declares those platforms are PaaS.

    Ah, thank you for clarifying.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Phil Parkin wrote:

    Here is a possible approach.

    1. Get the code for your DB into a VS database project and check that into a DevOps Git repo.
    2. Set the database project to target an Azure SQL database and make sure that it builds
    3. Set up an empty Azure SQL database
    4. Create build and deployment pipelines in DevOps to build and deploy your DB from your Git repo.

    Next step: transfer the data over using your tool of choice (you might need to disable constraints and faff about with IDENTITY columns to make this work properly and fast).

    Good idea, Phil, thanks!

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Jeff, I've taken your suggestion. I didn't migrate one of our databases to Azure. Instead, I created an Azure SQL database using one of the AdventureWorks databases. It's small, I've configured it to only use about $5/month. That's good enough for my purposes.

    Rod

  • Doctor Who 2 wrote:

    Jeff, I've taken your suggestion. I didn't migrate one of our databases to Azure. Instead, I created an Azure SQL database using one of the AdventureWorks databases. It's small, I've configured it to only use about $5/month. That's good enough for my purposes.

    Sounds perfect to me!  It'll also keep you from having to update your resume. 🙂

    Thank you for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply