There was a day when you didn’t have many decisions to make about a new SQL Server instance. You had to choose from a few editions and two licensing models: per proc or server cal. It wasn’t long ago that SQL Server would almost always be installed on a physical server on premises, and the DBA had full control and responsibility over the operating system, storage, processors and RAM. Those days are long gone.
Today, most SQL Server instances are virtualized. That seemed to happen overnight about seven or eight years ago. You don’t even have to host the VMs yourself. There are plenty of hosting companies willing to do it for you, and you can create VMs on Azure or Amazon.
Eventually, Microsoft made a database-as-a-service offering available on Azure, which is now called Azure SQL Database. The biggest benefit when it was originally introduced was that you could have a database up and running in minutes with no need for hardware or even a virtual machine. The bad news, back then, anyway, was that the maximum database size was small and quite a bit of functionality was missing. Again, things have changed, and Azure SQL Database will generally have new features in place well before they appear in traditional SQL Server instances. The maximum database size in the Azure SQL Database Premium service tier is four terabytes at the time of this writing.
About the same time that Microsoft introduced the database-as-a-service, they also came out with Parallel Data Warehouse (PDW). PDW was an appliance meant for multi-terabyte data warehouses running on premises. Just getting started with PDW cost a couple of million dollars or more for the hardware and SQL Server licenses. Today, PDW is renamed Microsoft Analytics Platform System (APS) and also supports querying data stored on Hadoop by way of PolyBase. Don’t have a few million sitting around to buy the appliance? You can be up and running in the cloud with the Azure SQL Data Warehouse, the latest data warehouse offering, in minutes without that initial investment in hardware. While you must pay for storage costs continuously, you pay for compute by the hour only when running.
For years, SQL Server ran only on Windows. SQL Server 2017 can run on Linux and in Docker containers. You can connect to the instance with SSMS or with the new SQL Operations Studio. Either way, you may want to dust off those Bash scripting skills...
Don’t want the responsibility of VM, but your app isn’t compatible with Azure SQL Database? Microsoft recently announced Azure SQL Managed Instances (in preview). You get almost 100% compatibility with traditional SQL Server instances but without dealing with patching, backups, high-availability, and disaster recovery. Amazon’s Relational Database Service (RDS) will also allow you to run a managed instance of SQL Server.
Keeping up with all the ways that you can run SQL Server can be overwhelming. My advice is to figure out what you want to accomplish, and which options will meet your goals. You have a lot of choices!