Learn more about migrating MySQL to SQL Server
Typical challenges that we see
Database migration is a daunting task with a lot of hidden issues and possible problems. With our extensive experience, we can overcome most of the database migration challenges.
Challenge | Solution |
|---|---|
Once I have migrated my database, how do I validate that it is working at full capacity? | Prior to the migration, you should have a good understanding of your database workload including any performance data and frequency of heavy load. Key to a proper comparison is a way to replay a load such as through Distribute Replay Controller or your own load harness. Once under load, you can evaluate your new technology by monitoring pressure on CPU, Memory, IO Operations, and network traffic. By monitoring the times the system is under pressure you can determine if your technology is properly sized. ABCloudz can help you profile your existing environment and then grade your migration against that profile. |
Migrating away from your current data solution | Traditionally, this means that a switch from one data platform to another. However, just switching the nameplate on the back end database server may not be the best solution. Some data patterns and use cases may have a better fit for NoSQL or Data Lake solutions. ABCloudz architects are trained to spot these patterns and discuss with the customer another approach that may be a better fit or be able to leverage new technology such as IoT devices. |
Why migrate your MySQL applications to SQL Server?
There could be a time when your MySQL database solution evolves into a mission-critical solution. Suddenly, service level agreements for areas like security, high availability, disaster recovery, scalability, data governance, become an issue. Likewise, the solution may require integration with new capabilities like machine learning, data lake, graph, and in-memory computing.
Microsoft SQL Server powers your entire data estate by supporting structured and unstructured data sources. It builds on previous versions of SQL Server, which have been industry-leading for four years in a row. Also, previous versions of SQL Server have held a leading spot in TPC-E benchmark testing. SQL Server scales to accommodate petabytes of data through PolyBase by using T-SQL over any data. SQL Server has also been the least vulnerable database over the last seven years.
When evaluating your rationale for migrating, consider the detail provided in the following sections.
Security
According to the National Institute of Standards and Technology (NIST) public security board, SQL Server has the lowest number of reported security vulnerabilities across the major database vendors (NSIT, 2016).
Security features provide server-side security measures that vastly simplify the process of keeping data safe from unauthorized access without the need to modify existing client applications. These features include:
- Auditing, which enables teams to monitor access and track potentially suspicious activity
- Transparent Data Encryption, which protects data at rest at a file level
- Row-Level Security, which enables developers to implement fine-grained access control over rows in a database table
- Dynamic Data Masking, which enables customers to designate how much of the sensitive data to reveal with minimal impact on the application layer
- Always Encrypted, which secures data in motion and at rest
Performance
SQL Server holds the top:
- TPC-E performance benchmarks for transaction processing
- TPC-H performance benchmarks for data warehousing
- Performance benchmarks with leading business applications
In addition, in April 2017 Hewlett Packard Enterprise published a new TPC-H 1TB world record with SQL Server 2017 running on Linux.
These key SQL Server 2017 features that make this possible include:
- Columnstore, which provides column-based data storage and processing to achieve up to 10 times the query performance and data compression of row-based storage.
- In-memory OLTP, which brings transaction processing to memory-optimized tables at more than 2.5 times the speed of disk-based tables.
The power of SQL Server now on Linux and Container Instances
SQL Server offers the best performance and security features, and they are now available on Windows, Linux, and Azure Container Instances.
With these capabilities available on all editions of SQL Server, organizations can choose their deployment environment by operational need rather than desired features.
Adaptive query processing feature family
New in SQL Server 2017, Adaptive Query Processing introduces additional capabilities that allow the SQL Server query processor to modify plan choices based on runtime characteristics. During query processing and optimization, the cardinality estimation (CE) process is responsible for estimating the number of rows processed at each step in an execution plan. Inaccurate estimates can cause slow query response time, excessive resource utilization (CPU, Memory, IO), and reduced throughput and concurrency. To improve CE techniques, SQL Server 2017 introduces a new feature family: adaptive query processing, which improves handling of the more intractable CE issues.
Automatic plan correction
SQL Server 2017 also includes new features designed to detect plan choice regressions and provide recommendations on how to fix problems. These automatic plan correction features help to maintain the performance of data queries, even when application changes occur.
SQL Graph
Customers want more than the ability to manage large volumes of data; they also need to analyze existing data more effectively to understand its relationships and patterns. Querying data from a relational schema by using traditional SQL queries can be a complex task. SQL Server 2017 introduces SQL Graph to make modeling and analyzing relationships easier by allowing users to handle relationships in a more flexible and agile way.
Resumable Online Index Rebuild
Planning, maintaining, and managing a large index online can be challenging. The bigger the index, the more difficult index maintenance becomes. Reorganizing and rebuilding indexes can be especially cumbersome. Resumable Online Index Rebuild in SQL Server 2017 enables continuation of an online index rebuild operation after a failure (such as a failover to a replica or insufficient disk space). Resumable Online Index Rebuild also enables pausing and later resuming an online index rebuild operation. For example, you might need to temporarily free up system resources to execute a high-priority task or complete the index rebuild at another time if the available maintenance window is too short for a large table. Finally, Resumable Online Index Rebuild does not require significant log space, which allows you to perform log truncation while the resumable rebuild operation is running.
In-Memory OLTP enhancements in SQL Server 2017
First appearing as a feature in SQL Server 2014, in-memory technology for SQL Server dramatically improves the throughput and latency of SQL Server OLTP capabilities. This functionality is designed to meet the requirements of the most demanding transaction processing applications, and Microsoft has worked closely with several companies to prove these gains. SQL Server 2017 adds features to improve the performance and supportability of In-Memory OLTP workloads. In addition, we have removed many limitations on tables and stored procedures to make it easier to migrate applications and take advantage of the benefits of In-Memory OLTP.
Getting started
Sometimes you need a helping hand to get you started. We know what it takes to convert MySQL to SQL Server, and we know all the possible issues and the workarounds you may need. Here are some of our offers to help with assessments, using SQL Server Migration Assistant, or developing a future-state roadmap.
Supporting your SQL Server solutions after the migration
As part of our migration project, we offer support, consulting, and managed services for your SQL Server solutions. If you don’t want to go through the hassle of maintaining the day-to-day operations of your databases and applications, be sure to check out our Operational Data Management service.