Managed Instance vs Azure Database - is this a good option?

  • We're currently in Azure, however as we had a very tight migration timeline when we moved, we've been using SQL Server on Azure VMs for the last several years.  There's a push in the organization to go "cloud native," and on the database side, well, I see some potential headaches.

    My question can be boiled down to, based on the situation below, would Managed Instance be a reasonable move rather than going to Azure Database?

    What we have is several applications that talk to multiple databases currently on the same SQL Server instance on a VM.  Now, we don't have anything like "trustworthy" turned on, instead the same login exists in multiple databases on this instance.  Now as I understand it, if we went to Azure Database, getting this functionality to work would be a fair bit of code change on the application side, as (I believe) currently the inter-database stuff is in various stored procedures, which would not work in Azure Database.  So migrating these DBs into Managed Instance would avoid having to move the stored procedure code into the application code.

    The other part of the situation is more a "the future is murky and who knows what may come" sort of thing.  My concern is that, at some point in the future, the higher-ups may send down a directive that "we're moving to AWS / Google Cloud / Oracle Cloud and you have 30 days to make it happen."  Well, if we've moved all the databases into Azure Database, the only way out is to extract the data, schemas, stored procedures, etc, and recreate it all in a new database at the new host.  If, however, we migrate to Managed Instance and set it to "SQL Server 2025" and use a "User defined Key" for TDE in the Managed Instance, we CAN backup our databases to Azure Blob storage, then restore them to an on-premise SQL Server 2025 instance (I know this, for I have tested it today.  Yes, I was surprised it worked, too)

    While Azure Database might be the less expensive option, if we take advantage of serverless and auto-shutdown, a fair number of our applications are 24x7 or close to it.  Our test environment, well, we'd probably set the Managed Instance(s) there to auto-shutdown in the evening, and auto-start in the mornings on the weekdays to keep costs down some.

  • Jobs? Agent?

    Azure Database can have different performance characteristics than a VM, so be aware. There are some limits here. That being said, you can use serverless and get some scalability, even without the shutoff.

    Managed Instance, under load, esp log load, has had some funny performance things for people.  FYI: https://kendralittle.com/2024/12/18/azure-sql-managed-instance-storage-regularly-slow-60-seconds/ Not the only report I've seen.

    To your points:

    • same login in multiple databases - not really an issue. db is a db.
    • inter-database code - if you try to "switch" databases (use) or query across them, then this is a little harder. I don't think this needs to be in app code. Elastic queries can work, but they do take some code changes (https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical?view=azuresql)
    • move clouds - If you can have an outage, or your boss doesn't care about your time being spent on data sync, you can bacpac out stuff form AzureSQL DB and load it anywhere. Not always recommended, and not transactionally consistent, but it works. I worry less here about moving clouds, as a move to AWS might not be a VM move. It might be PaaS, so RDS/CloudSQL are different beasts as well and your restore might not work anyway. I'd be less worried here, as there is a path, albeit a labor intensive (And headache inducing) path.

    All this being said, I know people happy and productive in either environment. Id' run some PoCs, maybe some load tests (seen this: linkedin.com/posts/bobwardms_sqlserver-azuresql-sqlsimtools-activity-7516113185146499072-Eusv) and some cost analysis.

  • Thank you for reminding me of the performance issues some people run into with MI, I'd forgotten about that.

    As to the rest (except for the last, we'll come back to that,) sounds like I've been incorrect on my understanding of Azure SQL DB and the limitations.  So for the inter-database query stuff, the biggest headache would be working with the Dev to determine which DBs would need to go together (although, in the case of what I expect would be the problem child, it'll boil down to "all of them")  I also didn't know that BACPACs could be transactionally inconsistent, which would be a problem.

    On the last point, moving to a different cloud, I still remember the "fun" our team had in 2020 when we were told "you've got 30-days to move production from our current cloud environment (VMs) to Azure, after which said cloud provider will be shutting down and there will be no backups by the provider."  /Insert Leslie Nielson here "Good luck, we're all counting on you."

    Sounds like I've got a "pros / cons / risks" document in my future.

  • bacpac - https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?view=sql-server-ver17

    If you quiesce it is, but a bacpac is essentially this: https://www.sqlservercentral.com/scripts/bcp-out-all-table-data-in-your-database

    • for each table in sys.objects

      • bcp out <table name>

    I get the cloud move, and certainly it's a reason why random-local-cloud provider is a risk. I've had a few moves around Denver early on with this site for those reasons, including from a friend's basement when his provider was shutting down in 2000. With the big vendors, it's likely more a contract issue, which is going to just take time. If someone wants to do this, remind them of egress fees, which might overwhelm some of the "savings" elsewhere.

    I think you have a few things to test here. I wouldn't go the schema route, and MI has worked well for plenty of friends. Just be aware of potential issues, and you  might need better app retries if you move from a LAN/WAN to the cloud. That can be some work for app devs.

  • Adding to steves points

    better to go for azure Managed instance

    1. Multiple database and inter connection. as steve mentioned need a different connectivity for inter connection.
    2. Application code changes minimal or not required.
    3. Lift-and-shift migration is easier
    4. You have handy agent to help too, for azure database again need a different approach with Elastic agent.

     

    Regards
    Durai Nagarajan

Viewing 5 posts - 1 through 5 (of 5 total)

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