Upgrading SQL Server can be a complicated and nuanced operation, it can’t be simply performed with a few mouse clicks. In many ways, upgrading SQL Servers follows a similar process to more general database migration projects. Because of the similarities, we can apply our 12-step database migration methodology to great effect. From analyzing the existing database architecture to consolidating resources for operational cost reduction, DB Best has the tools and know-how to ensure your SQL Server platforms are up to date.
In the following video, we will show you how DB Best executed a SQL Server upgrade project for one of Europe’s leading telecommunications companies.
Overview of the customer’s system
When the customer approached us, their server environment included three SQL Server 2005 databases, as well as one SQL Server 2000 database. Starting in April of 2016, Microsoft ended Extended Support for SQL Server 2005. This was also the case for SQL Server 2000, as Extended Lifecycle Support ended in April of 2013. Because of these two major inhibitors, the customer requested an upgrade to more powerful and modern versions of SQL Server.
Key points of the database upgrade process
This SQL Server database upgrade project required three key components: schema conversion, data migration, and testing. These three tasks, from a technical point of view, come to define a successful upgrade project. Let’s take a look at these three tasks in more detail:
Database schema conversion
As we mentioned previously, the customer’s original source database environment was entirely based on discontinued versions of SQL Server. Because of this, we couldn’t simply upload backups of the original database schemas into a new environment. In order to successfully upgrade to the latest SQL Server versions, we used Visual Studio 2015 to update the original database schemas. At this stage, we created a separate database for each one in the original environment. We then used Visual Studio 2015 Database project to create separate .sql files for each table, function, stored procedure, and other metadata objects for each source database. We then ran an analysis to determine compatibility issues, while also looking for deprecated and discontinued syntax, in order to prevent possible data loss during the later data migration phase.
Data Migration
The second key task to upgrading SQL Servers is migrating the data contained within the various databases. To facilitate this, we created Microsoft PowerShell scripts to migrate data from the source databases into the new target databases. The PowerShell scripts create a blind copy using the Microsoft.SqlServer.Management.SMO.Transfer interface. These scripts were used during testing, and then later in the production environment. This ensured any modified data in the production environment would be included in the final release. The PowerShell scripts also saved the identity columns from the source database, which is one of the cornerstones of this SQL Server data migration project.
As a result, we migrated around 10GB of data from the SQL Server 2000 database, which is impressive considering the age of that SQL Server version. In the remaining three SQL Server 2005 databases, we migrated an insufficient amount of data: around 1GB in total.
Testing
During the testing phase, we worked with our customer to create a test plan based on the code modifications made by the customer to ensure the applications functioned correctly. Once we received the application updates, we tested the applications to ensure proper connectivity, functionality and performance based on the customers business requirements. Once testing was complete in the test environment, we helped deploy the new SQL Server 2014 databases into their production environment.
As a result, our customer can now take full advantage of four new databases running a modern version of SQL Server. Our customer doesn’t need to worry about a database upgrade for the next decade!
