In Part I, we examined the on-premise and cloud upgrade options available for SQL Server 2008 as it reaches EOL. For scenarios where memory management challenges or database operations have proved difficult, Snowflake is also a strong option, which is increasingly the default choice for many data warehouse and data lake offerings. Snowflake has been built from the ground up as a cloud-native data warehouse. The service separates both compute and storage, thereby allowing the two to independently scale. Snowflake can natively load and optimize both structured and semi-structured data and make them available via SQL. With fast, flexible, and easy to work workflows powering simple and advanced functions, users can run a highly scalable database. By natively running on AWS EC2 and S3 instances and offering compatibility with Azure services such as Azure Blob Storage and Compute options (introduced in 2018), Snowflake offers users optimal choices while freeing them from legacy upgrades.
Prior to examining the SQL Server 2008 to Snowflake migration prerequisites, let us briefly examine the major upgrade considerations. Snowflake is a true serverless cloud-based database and scales as resource needs like CPU, memory, and storage change. There’re various articles on the technical capabilities of Snowflake and why it is a viable cloud database to replace legacy databases.
- SQL Compatibility—combing through the official supported SQL command reference guide, it’s important to note the support for the most common standardized version of ANSI SQL. Basically, the most common operations are usable within Snowflake. Furthermore, advanced Windowing type functions improve with every Snowflake release by their responsive support team.
- DevOps friendly—less DBA tasks are a welcome response to using Snowflake, but there’re several database DevOps features that’re realized with features such as schema change management and continuous data protection.
- Big Data Compatibility minus the complexity—in addition to relational data, several datatype options are available to store XML, csv, and JSON files. This doesn’t require any Map Reduce code either!
- Performance and upgrades—performance tuning is a thing of the past. A micro-partition architecture supersedes the need for performance tuning. There’s also no software or service to upgrade. Recurring software upgrades, security, and maintenance occurs transparent to users.
- Snowflake is available via the Azure Markeplace as a cloud computing option to run its cloud-built data warehouse. Azure AD can be used to manage user access and enable single sign-on. Enterprise Single Sign-On is also available via Azure AD, which supports enterprise-class SSO with Snowflake out of the box.
SQL Server 2008 to Snowflake Prerequisites
Let’s examine the prerequisites for migrating from SQL Server to Snowflake.
- Data Prep: (why save the best for last) users don’t need to define a schema in advance when loading JSON or XML data into Snowflake! However, based on the data structures, there might be a need to prepare data before loading it. Check the data types reference for Snowflake and make sure that data mappings are correct.
- Data Load: the Overview of Data Loading page details the Bulk vs. Continuous loading options, several commands (PUT for staging, COPY for bulk loading), and flat file ingestion from AWS S3 or Azure Blob Storage.
- Custom Scripting: there are scenarios where performance is a prime consideration and S3 or Azure Blog storage might not be applicable. Fear not, custom scripts are still a viable option. Consider using Python and the Pandas framework for loading larger datasets by dividing them in to smaller sets/chunks.
- Tools Based Migration: if writing custom ETL is not appealing or if a visual approach is required to data ingestion and migration, there are tools such as Stitch and Fivetran. Some of these data pipeline tools extract SQL Server data via the API and structure it for optimal analysis. However, licensing costs, schema misalignments, and an upfront analysis is a must before heading this route.
Migration to Snowflake is much easier with Trianz’s SHIFT - a unique, highly automated migration technology. SHIFT produces a 95% migration of legacy data platforms to Snowflake’s state-of-the-art, cloud architecture with proven reliability and quality. Watch Video. In a recent use case, a customer had a 250TB box for redundancy and failover. The previous solution had a lot of expensive data sitting on it and Snowflake, Azure or AWS can act as a lower cost offload with equal performance in some cases for data and queries on other data platforms such as Teradata. If clients are still under contract with legacy vendors, Trianz can help you optimize your hybrid environment. As part of SHIFT, we can analyze and help select workloads that are better suited to Snowflake to free up your powerful TD boxes for heavy workloads.
For all your data footprint and migration conversations, you can reach out to us at firstname.lastname@example.org.
Director of Analytics Practice
Kireet Kokala is a senior data technologist leader in the Data and Analytics Practice at Trianz who helps clients with digital transformation and data monetization. The Data and Analytics Practice works with enterprises to achieve significant competitive advantage via modern cloud technologies, with a particular focus on the Snowflake Computing ecosystem.