We have a 1tb database that use information from other 4 or 5 databases. It has hundreds of procedures using external databases to calculate and etc. we have 0 problems inside SQL Server.

I would like to know if it is a good idea to migrate a database like this, to azure.

I know we cant migrate procedures with [database]..[...] inside the code or with windows users and thats why I would like to know.

as I see, azure is like a contained database and theres no way to use linked servers or even SQL agent.

  • By the way, One of them is a CRM MIcrosoft database for example. – Racer SQL Jul 7 at 11:52

You are correct that Azure SQL Database does not support linked servers (it does support elastic queries which is similar), and scheduled jobs have to be scheduled somewhere else (although elastic jobs may be out of preview now...).

The next tier up in Azure is Managed Instances and they are very nearly feature equivalent with traditional SQL while still bringing in a host of benefits (assuming you can get past the learning curve). We run our entire business in Managed Instances and it works great.

Final option, of course, is to just migrate your stuff to a server running in the cloud. You are probably already running on a VM so does it matter where it lives?

Question from OP in comments:

Difference between Managed Instance and VM:

Absolutely there is a difference. Feature-wise they are nearly equivalent except that with Managed Instances you don't have to add care and feeding of an OS. Also, you can't do some of the more exotic things because you simply do not have access to the OS either. Off the top of my head:

  • Managed Instances have a truncated restore syntax.
  • Working with managed backups and doing cross environment restores is different.
  • Disk speed (IOPS) is related to database size. So growing your files will give you more performant disks with Managed Instances.
  • Managed Instances have an easy (YMMV) way of getting redundancy
  • VM's can be shut down when not needed, while Managed Instances must be deleted.
  • while you can take backups from a Managed Instance to a blob, you CANNOT restore them anywhere except to another managed instance. MI's will take a backup (only full) from an on-prem server and can restore.
  • MI's are constantly updated... which can be problematic as we discovered the day that MSFT suddenly (from our perspective) shutdown the ability for MI's to have a linked server to older versions of SQL (I don't want to talk about the SQL 2008 box we have running the cloud tied to our flagship application)

Development / Testing cost:

I think with some variations of visual studio licenses you get $X of spend in Azure as a bonus. There are many low powered versions of the various services and offerings as well that can be perfectly adequate for proof of concept work. Unfortunately, Managed Instances are one of those that they simply don't have. They are expensive offerings with even the lowest performing one costing quite a bit.

If you are serious about moving to the cloud, I know that MSFT has partnerships with various consulting firms that will help you plan and spec out the migration. I'm not completely sure on the financial side of it, but I think it's of no to little cost to you directly and you get experienced folks helping you plan out your migration and find the best fit for your requirements. Reach out to your MSFT representative and they should be able to give you more details on that.

| improve this answer | |
  • And for a 1TB database they'll probably need more than the minimum size of a Managed Instance (4 vCores), in which case there's no cost difference between Managed Instance and Single Database. – David Browne - Microsoft Jul 7 at 13:27
  • Thanks @jonathan. Yes we currently use some VMs to host some SLQ Server. Is there a difference between a SQL MANAGED INSTANCE and a VM hosted in the cloud? I mean, we only use these VMs to host SQL Instances with aprox 5 databases each and I have some jobs created in SQL Agent, to send reports and Etc. And by the way, is there a way to test azure stuff for free before paying for it? like a sql developer edition for example...I dont want to create an environment with our business account before testing it. – Racer SQL Jul 7 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.