SAS Institute Inc., a global leader in data and AI gains agility, scalability, and security with SQL Managed Instance

SAS Institute Inc. is a global leader in data and AI, including industry-specific solutions. SAS helps organizations transform data into trusted decisions faster by providing knowledge in the moments that matter. The particular customer-centric solution which is the focus of this success story has nearly 1,000 databases and has enabled customers to create and migrate databases on demand. To maximize the utilization of the data, SAS wanted to spend less time managing databases and more time realizing the value provided by them. SAS selected SQL Managed Instance for this solution as it provided the capability to migrate existing SQL Servers to Microsoft Azure without application change while at the same time allowing SAS to expand its cloud footprint and take full advantage of enhanced scalability and availability offerings.

SAS is a cloud native data and AI company with award-winning industry solutions that are mission critical to many businesses and their bottom lines.

“We chose SQL Managed Instance as one of our target platforms due to its rich feature set, speed of deployment, and ease of management. Additionally, when looking to migrate from on-prem to the cloud, SQL Managed Instance offered us the greatest compatibility allowing for a simple lift and shift,” says Brian Smyk, Principal DBA at SAS.

Choosing the right SQL platform

SAS has been using Microsoft SQL Server on-premises for many years. SAS has spent considerable time and effort on developing and integrating with SQL Server. To maximize the utilization of the data, SAS wanted to spend less time managing the database and more time realizing the value provided by them. On-premises databases can be time intensive to deploy, manage, and scale. With SAS’s customer-centric approach—some solutions containing nearly 1,000 databases being created, dropped, and migrated regularly—and an ever-growing customer base, the company sought to further improve processes and experience. They leveraged an automated solution combining SQL auditing, Log Analytics Workspaces, and Automation Runbooks. Considering all these requirements, SQL Managed Instance was chosen as the right target.

Automating Azure SQL MI Custom Policy

“Important to any DBA, SQL Managed Instance provides plenty of monitoring, visibility, and alerting natively. Advanced auditing through integration with Log Analytics equipped with automatic retention is also available and easy to configure,” says Brian Smyk.

The new Azure-based solution helped SAS automate this process in a seamless and effective manner. When a new database is detected in a subscription, SAS receives an alert, and a runbook automatically sets up auditing and backup retention policies. This was a two-facet solution, but at the core it works on the concept of Kusto alerts which query either the Azure Activity Log or Azure Log Analytics Service. There are two unique events generated based on how a database is created. If using PowerShell or Portal, that event is logged in the Azure Activity logs. If using SSMS, one must first enable Diagnostic Settings at the Instance level, target auditing to Log Analytics, then create a SQL Server Audit Specification to capture the database creation event. This can easily be done via automation when a new SQL Managed Instance is deployed. When a new database is created on an instance, no matter what method is used, an alert is triggered, and an action group is called which executes PowerShell code using Azure Automation Runbooks to set a custom pre-defined Auditing and Long-Term Retention (LTR) policies to meet the compliance needs for SAS.

Benefits of migrating to Azure SQL MI

By moving to Azure SQL Managed Instance, SAS benefited in below areas:

• Cost saving: SAS realized significant savings cost saving (with respect to cost of ownership). SQL MI business critical (memory optimized) deployments cost 57% less than comparable SQL on-prem with HA. SQL MI general purpose (memory optimized) deployments cost 55% less than comparable SQL on-prem with HA. SQL MI business critical (memory optimized) deployments cost 13% less than comparable SQL on-prem without HA.

• Improved business agility: SAS can scale seamlessly to meet the changing demands of business with optimal levels of performance, availability, and functionality. The deployment timing has been reduced by 80% and SAS is spending 20% less time administering.

“Regarding backups and recovery, we have complete control over retention settings and comprehensive insights into backup history. This allows us to respond quickly to any restore requests, which is not something us DBAs are used to,” says Brian Smyk.

With the overall success SAS has experienced migrating to SQL MI, SAS pivoted their SQL Server business model from deploying on-prem SQL on VM or SQL on Azure VM to exclusively SQL Managed Instance or Azure SQL DB.