Upgrade and modernization projects for SQL Server should not be taken lightly. You might want to take advantage of the latest versions of SQL Server 2016. You might need to take your aging SQL Server 2000 or 2005 solutions running on older hardware and operating systems and get them onto a supported version of SQL Server. In either case, having a programmatic approach to upgrading your applications and databases is critical to keeping your business running. At DB Best Technologies, we have a 12-step approach for upgrade projects based on our proven Migration Methodology. This blog post provides you an overview of the DB Best 12-step upgrade process to help you prepare for your upgrade. Let’s get started.
1 Envisioning and assessment
The envisioning and assessment phase is the most important phase of an upgrade project as it establishes the business case for upgrading and defines the approach that you will take. DB Best uses an internal tool called DBMSys that captures system metrics that helps you decide on what the target platform should be. For example, can you consolidate databases on a virtualized environment? Is it a candidate for moving to the cloud? DB Best creates a financial model that can help you decide on the future state for the deployment. You also need to look at other aspects of the application and database. Examples include: Does your third-party vendor support the newer version of SQL Server? What is your downtime tolerance? What are the times that you can’t touch the system for maintenance? Are there other projects that might interfere with the upgrade? What level of security access is needed? Do you have an existing test plan for the application? What technologies are you using for high availability and disaster recovery? Factors like these help in defining the overall project and deciding on the upgrade approach: in-place, side-by-side, upgrade to a new server, upgrade to the cloud using SQL Server on an Azure virtual machine, SQL Server on an AWS EC2 virtual machine, or managed services like Azure SQL Database and SQL Server on AWS RDS. You might also want to look at how you can move your applications, like ASP.NET web sites, to the cloud as well.
In the assessment phase, we look for potential incompatibilities with the database using tools like the SQL Server Upgrade Advisor. Although the Upgrade Advisor helps you to analyze the database, you need a way to collect SQL Server traffic between your applications and your older servers to look for deprecated syntax. DB Best uses an internal tool called SQL Sniffer and Replayer to analyze the network traffic without the overhead of using a profiler trace to identify incompatibilities. You will also need to understand what data transformation services (DTS) packages or other extract, transform, and load (ETL) services, like SQL Server Integration Services (SSIS) packages, need upgrading. At DB Best, we have an internal tool that scans existing databases for DTS and SSIS packages to help scope the upgrade effort. For complex systems, it helps to create an architecture diagram to show all the components that connect to the system to look for other incompatibilities such as network connectivity that may not work with newer versions of SQL Server.
With all of the information available, you can now put together a project plan for traditional project management or use the sizing of the effort for Agile development efforts. You also have insight as to how much effort you need to spend on the remaining 11 steps.
2 Database schema conversion
For the database schema conversion step, the first thing you do is to fix and store procedures, views, triggers, and other database objects that are using deprecated syntax identified using the Upgrade Advisor. Typically, you will want to back up your application database and then restore on a development/test environment running a newer version of SQL Server before making your corrections.
You then want to look at newer features of SQL Server to see how they might help you improve security, performance, and high availability. For example, Transparent Data Encryption encrypts data files at rest. SQL Server 2016 includes additional features like Always Encrypted for encrypting column data from applications that don’t have the proper security credentials, Row-Level Security to limit access to data based on a security policy, and Dynamic Data Masking to redact data based on role permissions. You will also want to look at AlwaysOn Availability Groups to replace Database Mirroring for failover protection. For performance, you might want to look at how you can incorporate columnstore indexes for improved data warehouse and query reporting performance.
3 Application conversion and remediation
For the application conversion and remediation step, you need to identify where the application is sending any deprecated or discontinued syntax along with breaking changes and update the application using the supported syntax. In addition, you need to see how the application connects to the database for the switch over to the newer versions of SQL Server. This is where tools like the SQL Sniffer can help identify the application code to expedite the process.
4 Scripts conversion
The script conversion step looks at the batch scripts that are used for ETL processes, database maintenance, disaster recovery, and other scripts not related directly to the applications using the database to make sure they will work with the new versions of SQL Server. In addition, you will want to look at how newer scripting languages like PowerShell can improve automation processes.
5 Integration with third-party applications
It’s important that other applications like third-party business intelligence and ETL tools as well as other utilities continue to work after the upgrade process. In this step, you need to identify them and make sure they continue to work with the newer versions of SQL Server. You may need to look at upgrading the third-party tools as well.
6 Data migration
Typically, you perform the data migration step using tools to backup and restore full and transaction logs. You may have a need to apply some type of data replication scheme leading to keep your pre-deployment version of the database in sync with the current production version of the database. Tools like the DB Best SQL Server Central Log Shipping scripts provide a way to automate the process. In addition, you can use tools like DB Best’s Database Compare Suite to look for data changes and look up tables to ensure they are in sync with the production database before cutting over.
7 Functional testing of the entire system
Function testing will be the most time-consuming process depending on the changes needed to the database and applications. Having an existing test plan will certainly help in the process. If not, the upgrade team will need to work closely with the application development team to develop test plans and execute them in a pre-production environment.
8 Performance testing
The good news is that overall performance should improve with the combination of general performance enhancements that have taken place over the years with newer versions of SQL Server and Windows Server. In addition, you can expect performance improvements with newer hardware configurations. Ideally, you will want to measure performance for critical areas of the application and then compare them against the new environment. In this step, you will also want to look at how you can take advantage of the new SQL Server capabilities such as in-memory OLTP and columnstore indexes.
9 Integration and deployment
Once you have tested everything in your pre-production environment and you are ready to switch over to production, it’s important to have a plan for what to do in the case you need to roll back changes to the database and associated applications so that you can meet your business continuity requirements. You will want to test this plan in your pre-production environment as well so that the team is ready for any problems than can occur ruing the rollout.
10 Training and knowledge transfer
There are two types of training and knowledge transfer you want to consider during this phase. The first is focused on the operational changes to the upgraded system. The second has to do with taking advantages of the latest SQL Server features to shorten maintenance cycles or improve your disaster-recovery processes using features like the SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.
11 Documentation and version control
In this phase, you need to make sure that you have the necessary documentation in place to support the upgraded system. In addition, you might want to consider using a version control system moving forward to control changes as part of a solid development practice. At DB Best Technologies, we use tools like Visual Studio Team Foundation Server to maintain all source scripts and tools used for the upgrade project and share them with our clients at all phases of the project as needed.
12 Post-production support
In this final step, you want to make sure you have the plan and the resources to manage your upgraded system. If you happen to lack the resources to maintain the upgraded system, DB Best Data Management Services can help you reach your goals, even on the most demanding applications.
DB Best can help
Having your infrastructure and data environment secure and up-to-date is crucial for any organization. The reason for that is not only the constantly growing need to obtain more insights from your data: Multiple applications and systems developed 5–10 years ago are running on older versions of hardware, software, and legacy architecture, posing security and data management risks that stem from both the termination of support of the outdated software and the malfunctioning of aging technological solutions.
The easiest way to get engaged is to perform analysis/assessment of the existing applications (including databases, software, hardware, etc.) and come up with a roadmap on how to modernize these apps in a fast, cost-effective, and reliable way with mitigated risk.
We use our modernization methodology and automated tools (internal and third-party) combined with our manual efforts, techniques, and best practices to successfully deliver hundreds of project for Tier 1 customers worldwide.
We would be happy to help you with your technological needs!
