I was in a client meeting the week before last, and the discussion gravitated towards Microsoft SQL Database on Microsoft Azure.
SQL Database is Microsoft's Platform as a Service (PaaS) offering that provides so many benefits to the user that are well documented and YouTubed. This blog post is really geared around those of you who are intrigued, like what you've read and heard about SQL Database.... "BUT"...
A theme you pickup is is that there's a "BUT", not an overwhelming "drop your entire SQL Server farm and move it here". So, what's the catch?
Let's talk about the elephant in the room.
Microsoft made a branding mis-step
When we say "SQL" in conversation, we typically think of our servers where we've installed the SQL Server Standard or Enterprise software. We begin to deploy many databases, then we get concerned about the loss of data and begin to replicate those databases to that if the first one goes down, we have redundancy in our environments. We do a lot to "care and feed" those servers, at the Windows host operating system, SQL Server engine and application database level. We also like to run jobs to ensure that those servers are operating at peak efficiency and swapping information for our line of business applications. It's also pretty swell to keep those databases backed up
To me, that is where Microsoft made a mis-step. People hear "SQL" and the natural assumption that they are somewhat interchangeable. SQL Database is "like" SQL Server. but it's NOT SQL Server.
If it were up to me, I would name it "Azure Database". Because "SQL" is misleading.
What sql database is
Microsoft SQL Database is an online system that is deployed in a multi-tenant environment on an individual database basis. This comes with best-of-breed architectures baked right in. This means that all that "care and feeding" can be given to our new best friends at Microsoft.
what sql database is not
SQL Database allows tables, stored procedures, stored functions, views, indexes and above all handles SQL statements that we have all grown to love and appreciate.
Because SQL Database is not a SQL Server, and is running in a multi-tenant environment there are some key differences and limitations that you must be aware of:
- TempDB still exists, but you can only create Local temp tables. Global Temp tables are not permitted.
- Master, MSDB, and Model databases are not available.
- Single database queries only. On SQL Server, your queries can span multiple databases. Not so on SQL Database.
- No Agent, therefore no agent jobs.
- No Active Directory security.
- Certain column storage data types are not supported.
There are other limitations, such as depreciated support for certain T-SQL keywords.
so, when should I use sql database?
There are two traditional use-cases for any SQL workload (Business Intelligence not included for now):
- Application Storage
- Data Warehousing -- i.e. ETL data sets
Because SQL Database was architected from the ground up to be a Platform as a Service. The thinking behind SQL Database aligns extremely well with a Software as a Service offering. If you were the maker of a software application, where variable demand and massively parallel transactional processing and computation were needed, SQL Database is the ideal platform for this new type of non-traditional workload.
In a lot of cases, an Infrastructure as a Service (IaaS) option may just be "easier". What this means is you select a Windows Server with SQL Server from the Azure Marketplace (convenient pre-built templates), and then simply migrate your data files and you're done. When considered on a one-for-one basis, we don't see a lot of value for IaaS migration unless it's performed at a farm level. An IaaS platform means you still have to patch, maintain and may need to backup those VMs on Azure. Where's the upside? If you can eliminate your on premises hypervisor infrastructure and storage infrastructures, you have a very compelling cost argument.
So, for our traditional on premises workloads, what's the path for Platform as a Service, or is it even possible?
It is possible. But it takes a little footwork first.
Migration of Application Storage Workloads to SQL Database
The main constraints of application storage workloads are, of course, the applications themselves.
Fundamentally, database applications would need to be measured for technical compatibility at a query level. As stated before, SQL Database doesn't support 100% of the same keywords that SQL Server performs (~95% ish). Some coding styles may need to change to conform to a multi-tenant PaaS environment (elimination of global temp tables, the way the application queries multiple databases, etc).
These workloads are typically higher up-front risk to convert to PaaS compatibility. That's why AnyKey's developed processes to evaluate applications at that detail level to mitigate those risks with proprietary tools and utilities we developed, in conjunction with Microsoft tool kits to evaluate the approximate impacts to make an application PaaS-compatible.
Applications developed and maintained by 3rd party companies are essentially out of your control. Contact AnyKey with your application vendor's details and we can broker a conversation about getting the application onto a PaaS roadmap. We have expertise in making applications SQL Server 2016 and SQL Database compatible. We work with the vendors to get their applications ready for prime time and help them pass their Microsoft Certifications with the new database standards and capabilities.
migration of data warehousing workloads to sql database
The main constraint of data warehousing workloads come down to the mechanics of getting data into that environment, and ensuring that the data types are in a supported data type.
These workloads are far less risky and less complex, when compared to Application Storage workloads. There are two architectures that we see being popular in the real world:
- ON PREM TO CLOUD. Source systems are on premises (such as ERP or other line-of-business application databases), and SQL Server SSIS packages are created to "push" the data to SQL Database for downstream consumption in Business Intelligence workloads and third party sharing.
- CLOUD TO PREM TO CLOUD. This is a bit of a newer strategy that provides the added advantage of segregated sharing of your "inbound" data systems from 3rd parties (partners, clients, customers) into their own SQL Database that is an isolate source system outside your environment. On Premises jobs download from that "Inbound" SQL Database, consolidate, process, ETL the data on premises, then outbound the data and results to a different SQL Database for downstream consumption.
We are in love with architecture option 2 because it eliminates VPNs, bandwidth and security risks. External 3rd parties can beat up SQL Databases on the cloud without any risks or impacts to giving On Premises access to your own infrastructure. How great is that?
The first stop on the PaaS migration path for Data Warehousing workloads involves an intitial inventory of the workloads for technical compatibility with PaaS data type storage and analysis of impacts arising from changes to ETL and SSIS jobs on premises. AnyKey's proprietary tools in combination with Microsoft tools streamline that migration and reduce the risks and simplify your migration.
You can read more about Microsoft's SQL Database on Microsoft.com by clicking here.
Update ! Microsoft is offering 5 new courses specific to SQL Database at a very reasonable $ 49 USD per course. Signup early, these tend to go very fast! Click here for more.
Call AnyKey to Continue
We'd love to hear your feedback and thoughts on this post!