MSSQL Database Server collation change to Modern_Spanish_CI_AS
About the Project
Client is running an engineering and logistic service. They execute projects safely, meeting quality standards and delivery times, providing the guarantee for their work. They advise on the power quality of electrical systems. They also Design and assembly of grounding systems. Public and ornamental lighting projects. He is one of the oldest clients and using several of our services from last 5 years and purchased Cloud-based VPS as well as Domains from us.
THE PROBLEM
Client was running MSSQL Express Edition on his cloud VPS where he stores his data and information. He had raised a query via our support ticket system and asked us to change the MSSQL Server collation to Modern_Spanish_CI_AS. Collation is a set of rules that ask database engine how to compare and sort the character data in SQL Server. SQL Server database collation can't be a change for existing SQL server.
OUR SOLUTION
Upon receiving clients' query we asked him, whether he wants to change the collation of a single database or entire SQL database engine. We received his response and he wanted it for the entire db engine.
Default SERVER COLLECTION can't be changed once the SQL Server is installed. You can change the database collation while installing the MSSQL server and it was the only way to change collation in this case as well. To make such changes, we always recommend clients to take backups of all the databases. Client agreed with this and he had taken the database backup at his end.
Before starting the reinstallation process, we have taken the backup of the entire cloud server. In case, anything goes wrong, we can restore the entire vps from backup.
Backup is taken and we proceed for the MSSQL reinstallation process. At first step, our support team removed existing MSSQL 2016 from his server and rebooted the server.
Once the server is up after reboot, we started the MSSQL installation process. At the server configuration step of the installation process, there was a tab called Collation and we were able to select the Modern_Spanish_CI_AS collation from the customize button. Modern_Spanish_CI_AS collation which was client requirement for this mssql server.
MSSQL Database installation is completed now with the client required DB collation.
THE RESULTS
Changes related to MSSQL Database is always critical and it is required to do this very carefully. Accuwebhosting with a highly experienced technical team handles this kind of task very efficiently. Here, we had taken DB backup as well as entire server backup to overcome any unwanted issue. After our changes, the client was happy and appreciated our efforts for changing the SQL Database collation.