Database migration to Microsoft SQL Server 2019 brings your software data management to a new level. It makes your application more secure, scalable, and customizable. Unfortunately, 44% of digitized businesses state that the lack of technical understanding is one of the main obstacles to successful database modernization. How can they overcome this problem?
The solution requires a comprehensive approach and a full understanding of the best practices. We strongly advise to take advantage of the latest version of SQL Server 2019. In some cases, you would consider switching from legacy SQL Server 2000, 2005 or 2008 solutions running on preceding hardware and operating systems to a newer supported version of SQL Server. Either way, thorough step-by-step approach is essential.
We employ our proven Migration Methodology to help you upgrade your SQL Server. It involves a 12-step approach which consists of in-depth assessment, conversion of database schemas, applications, scripts, performance testing, and, in some cases, SQL Server data migration.
Read our new blog post which describes basic 12 SQL Server upgrade steps. Let’s get started.
1. Envisioning and assessment
The initial step – envisioning and assessment – sets the scope for the entire project. This is the moment to define the business case for the upgrade and choose the right approach. During this stage, ABCloudz software specialists use internal tools and SQL scripts to capture system metrics. Collected data and strong industry expertise help us provide definite answers to many questions. Can you consolidate databases in a virtualized environment? Is the system a proper candidate for moving to the cloud? ABCloudz solution architects create a plan for an upgrade which brings the maximum technological advantage and cost-efficiency.
Other important topics that need to be addressed are as follows:
- Determine whether your third-party vendors work with the newer version of SQL Server;
- Identify maintenance needs and schedule;
- Investigate which projects are impacted by an upgrade;
- Determine your downtime tolerance;
- Define a required level of security access;
- Identify strategies and tech stack for disaster recovery and high availability
- Confirm if a comprehensive test plan is developed for your application.
You may choose among a broad range of options, including an in-place upgrade or upgrade to a new server. Another popular solution is upgrading the server to the cloud. In this case, you may use SQL Server on an Azure virtual machine or SQL Server on an AWS EC2 virtual machine. There is also an option to implement managed services like Azure SQL Database and SQL Server on AWS RDS. Note that you can transfer some applications, such as ASP.NET websites, to the cloud as well.
Utilizing specific tools
The assessment phase involves various tools. These tools allow you to determine database incompatibilities and other key obstacles to a successful upgrade. We recommend using Microsoft Data Migration Assistant (DMA) tool for such assessments. It enables you to analyze the database thoroughly and helps determine the deprecated syntax as well as establish potential issues with the database migration or upgrade. However, you still need a method to collect SQL server traffic between your older applications and servers.
At ABCloudz, we identify incompatibilities with the in-house tools that help us analyze the network traffic without using a profiler trace. We also use an internal tool that scans existing databases for data transformation services (DTS) and SQL Server Integration Services (SSIS) packages. This helps us estimate the time and effort required for the upgrade more precisely. In addition, we utilize the same tool to create an architecture diagram showing all the components that connect to the system. This allows us to determine network connectivity issues and similar incompatibilities that may disrupt the upgrade of your SQL server.
Once the preliminary assessment is complete, you are on a right track with your upgrade. Remember that the more information you collect during assessment, the smoother the remaining 11 SQL Server upgrade steps will be for you.
2. Database schema conversion
You should start database schema conversion with fixing the views, triggers, user-defined functions, and stored procedures that are using deprecated syntax. Just like in a previous step Microsoft Data Migration Assistant will be very helpful for that.
In most cases, database schema conversion involves backing up the application and then restoring it. We strongly recommend you to perform conversion in a development/test environment running a newer version of SQL Server. Make sure to complete backing up and restoring prior to making your corrections.
At this stage, you may want to get familiar with some helpful features of the newer SQL Server. SQL Server 2019 enables Transparent Data Encryption that fortifies the database. There is also an option to use the Always Encrypted feature for encrypting sensitive data. One more security feature of SQL Server 2019 is Row-Level Security. It allows you to develop a simple system of role data permissions. Dynamic Data Masking, which enables you to redact data based on role permissions, is also very helpful. You might also use a failover protection solution, such as AlwaysOn Availability Groups, which successfully replaces the Database Mirroring. Finally, you can enhance query reporting performance by applying columnstore indexes which use column-based data storage and query processing.
3. Application conversion and remediation
To convert and remediate your application, you must understand your syntax. First, identify where the application is sending any discontinued or deprecated syntax along with breaking changes. Then, make sure to use the supported syntax to update the application. In addition, you need to review the applications connectivity to the database for the transition to the newer versions of SQL Server. Our in-house automated tools can assist in identification of the application code and speed up the entire process.
4. Scripts conversion
At this step, you should analyze batch scripts. They are not directly related to the application but facilitate ETL processes, database maintenance, disaster recovery, and other scripts. If you want your upgraded database to run smoothly, make sure that the scripts will work with the new version of SQL Server. The best solution is to convert your scripts into a more flexible and efficient scripting language, such as PowerShell.
5. Integration with third-party applications
Avoid losing interoperability with your third-party business intelligence and ETL tools as a result of the upgrade. Identify all third-party applications running with your software and determine whether they will continue working with the newer version of SQL Server. If there are issues with the integration, consider changing or upgrading the third-party tools as well.
6. Data migration
SQL Server data migration is one of the most important steps in the upgrade process. Make sure to backup and restore full and transaction logs to prevent the painful data losses. A popular solution is applying some type of data replication scheme leading. It synchronizes your pre-deployment database version with the current production version. You might also use SQL Server Central Log Shipping to automate the process. ABCloudz developed in-house software engineering tools to identify data changes and differences in tables resulting from migration prior to production cutover.
7. Functional testing of the entire system
This step is often time-consuming, especially if you have implemented substantial database or application changes. Typically, there will be an immense number of functions that should be tested and reviewed. How could this process become more time-efficient? The best practice is to develop a comprehensive plan for functional testing and execute it well. You may need to engage the upgrade team to collaborate with the application developers. Joint effort will help systematize their activities and develop high-quality plans for software functional testing.
8. Performance testing
Improved performance is one of the most anticipated outcomes of the upgrade. The newer versions of SQL Server and Windows Server come with performance enhancements that make your server much more productive. You may also facilitate performance improvements by using newer hardware configurations. Measuring existing application performance and running comparison against the new environment is the key indicator for evaluating the upgrade’s impact on your application performance. We recommend you to focus on measuring the performance for critical areas of the application. Also, review the impact of SQL Server capabilities, such as in-memory OLTP and columnstore indexes on your applications productivity.
9. Rollout planning and deployment
Before switching over to production, make sure to develop a plan that will help you roll back to the original state. Account for this scenario to ensure business continuity. We recommend to test the rollout scenario in order to be prepared for the potential rollout issues. It is always a good practice to test such a plan in your pre-production environment when you have more flexibility.
10. Training and knowledge transfer
Training will help you and your employees take maximum advantage of the upgrade. There are two subject areas in which training is beneficial. The first one involves training on the topics of operational changes to the upgraded system. The second subject area is about taking advantage of various SQL Server features, including SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.
11. Control over versions and documentation
Make sure to review whether you have all the documentation required for your upgraded system. A good industry practice is to use a version control system moving forward to control changes. For example, ABCloudz DevOps engineers use Visual Studio Team Foundation Server to ensure better control over the project. We store all source scripts and tools for the upgrade and may share them with you at any stage of the upgrade project.
12. Post-production support
Once 11 SQL Server upgrade steps are completed, you still must have the plan and the resources to manage the upgraded system. If your resources are limited, ABCloudz Data Management Services will help you efficiently manage the most demanding applications and reach your goals with the use of best industry practices.
ABCloudz can help
To thrive in digital business you must employ the most up-to-date technologies. Many applications and systems developed just a few years ago have already been outdated. They run on legacy architecture and older versions of software and hardware which may cause multiple issues related to increased security risk, data management failures, and low application efficiency.
How could you solve this problem? The key approach here is to analyze your existing applications and create a roadmap defining how to modernize them and meet industry standards of consistency, security, and time- and cost-efficiency. ABCloudz can help to conduct this process.
We use a broad range of tools (internal and third-party), high expertise, and best technology practices in our modernization methodology. This allows us to successfully deliver hundreds of projects for Tier 1 customers across the globe.
Contact us to get started! Our experts would be happy to help you take full advantage of the technology upgrade!
