Collation Change SQL Server

Collation Change SQL Server

When you need to install a new SQL Server instance you normally use the default configuration provided by the installer. However, this configuration could create some problems down the line, especially when restoring a database. If the new instance’s collation does not match the original one there can be problems with the relationships between tables, with stored procedures, or even with basic queries. One possible solution to this is reinstalling the instance with a new collation, which is a time-consuming process, since each database of the instance needs to be backed up and restored in the new installation. There is another option, which is to change the instance’s collation using the command prompt tools. We’ll illustrate this process in the remainder of this article.

First, let us look at the current configuration of the instance and the test database. If we look up the instance properties we can observe that, in this case, the server collation is “Modern_Spanish_CI_AS” (see image 2) and the database collation is “Latin1_General_100_CS_AS (see  image 3).

NOTE: it is important to note that the collation change we are describing affects every database found in the instance.

imagen1

imagen2

imagen3

Once we verify that both collations are not identical we must follow several steps. First of all, we must stop the SQL Server service. To do this we open a command prompt window and run the following command:

sc queryex type= service state= all | find /i “SQL Server”

This command searches for all services with the name “SQL Server”. As you can see in the image below, there are several instances running on our example server. The specific instance we need to modify is “SQL Server (QASERVER)”.

imagen4

In order to stop the service, we run the following command(replacing “DISPLAY NAME” with the name of the instance you wish to stop):

NET STOP “DISPLAY_NAME ”

In this case, the command looks as follows:

NET STOP “SQL Server (QASERVER)”

imagen5

Once you run the command, the system will warn you that SQL Server Agent depends on this service, so it must also be stopped. Type “Y” both times to stop the respective tasks. After the services have stopped, we must go to the directory where the instance is installed, and navigate to the Binn folder. In this case:

cd “c:\Program Files\Microsoft SQL Server\MSSQL11.QASERVER\MSSQL\Binn”

imagen6

You must now run the following command:

sqlservr -m -T4022 -T3659 -s”INSTANCE_NAME” -q”NEW_COLLATION”

Where you must replace “INSTANCE_NAME” with the name of the instance you want to modify. If the server only has one instance installed you won’t need to include this parameter. You must also replace “NEW_COLATION” with the specific collation you need, in this case, “Latin1_General_100_CS_AS”. Considering this, our command looks as follows:

sqlservr -m -T4022 -T3659 -s”QASERVER” -q”Latin1_General_100_CS_AS”

Running this command may take a few minutes, depending on the size of the databases, since the server has to rebuild all the indexes using the new collation. It could also take up significant disk space, since the database logs need to store lots of information.

imagen7

Once the process is finished the system will display a message announcing the recovery is complete, as pictured in the image above. We close the command prompt window and open a new one to restart the SQL Server services using the following command:

NET START “SQL Server (INSTANCE_NAME)”

You must replace “INSTANCE_NAME” with the name of your instance; in this case, “QASERVER”.  When you run this command you will see the following result.

imagen8

Once the service is running again, we only need to verify that the intended changes were correctly performed. We do this by browsing the instance properties as shown in the image below. The Server Collation property is now “Latin1_General_100_CS_AS”

imagen9

Thus, we complete the instance collation change process. However, we recommend performing one more step, i.e, eliminating the transaction logs for all the databases affected by the change, since they could easily be several Gigabytes in size. Please note that we are affecting all these changes in a non-productive environment. You would probably be more careful about handling this logs in production.

To perform this pruning you must run the following script in the database, replacing DBNAME with the name of your database, and DBNAME.log with the name of your log file, usually the same as the database with a “_log” suffix. If you are unsure you can check the database properties in the Files section, where you can find the file’s name.

You must repeat this step for each database affected by the collation change.

imagen10

USE DBNAME;

GO

— The log file is truncated by changing the recovery method to SIMPLE

ALTER DATABASE DBNAME

SET RECOVERY SIMPLE;

GO

— The file is reduced to 1 MB

DBCC SHRINKFILE (DBNAME_log, 1);

GO

— Reassign the recovery method to FULL.

ALTER DATABASE DBNAME

SET RECOVERY FULL;

GO


 

Cuando se debe instalar una nueva instancia de SQL, normalmente utilizamos la configuración por defecto que trae el instalado. Sin embargo, en algunas ocasiones utilizar esta configuración podría traernos problemas más adelante. En especial cuando se restaura una base de datos, si los collation no coinciden puede generar problemas en las relaciones entre tablas, los procedimientos o incluso en las consultas. Una de las posibles soluciones pasa por reinstalar la instancia y cambiar el collation. Esta solución conlleva bastante trabajo, ya que si se tiene varias bases en la instancia habría que respaldar cada una de estas y volver a levantarlas en la nueva instancia. Existe otra posibilidad, que es cambiar el collation de instancia mediante comando en CMD. Es esta última forma la que explicaremos a continuación.

Lo primero, veremos la configuración actual de la instancia y de la base de datos de prueba. Si vamos a las propiedades de la instancia podemos ver que en este caso el collation del server es “Modern_Spanish_CI_AS” (Ver Imanen 2) y la de la base de datos es “Latin1_General_100_CS_AS” (Ver Imagen 3)

NOTA: Importante destacar que el cambio del Collation de la manera que vamos a mostrar se realiza para todas las bases de datos que se encuentren en la instancia.

imagen1

imagen2

imagen3

Una vez comprobado que los Collation no son iguales, debemos seguir los siguientes pasos. Lo primero es detener el servicio de SQL, para esto abrimos una ventana de cmd como administrador. Una vez en la terminal, ejecutamos el siguiente comando

sc queryex type= service state= all | find /i “SQL Server”

Con este comando estamos buscando todos los servios con nombre “SQL Server”. Como se puede ver en la imagen a continuación en el servidor de ejemplo se encuentran varias instancias corriendo. La instancia que necesitamos cambiar es “SQL Server (QASERVER)”

imagen4

Para detener el servicio ejecutamos el siguiente comando, donde se debe cambiar “DISPLAY_NAME” por el nombre de la instancia que se desea detener.

NET STOP “DISPLAY_NAME ”

Para nuestro caso el comando quedaría de la siguiente manera

NET STOP “SQL Server (QASERVER)”

imagen5

Cuando se ejecuté el comando, advertirá que el Agente depende del servicio, por lo cual también deberá detenerse. Se responde “Y” y se detendrán ambos servicios. Una vez detenido los servicios, debemos dirigirnos al directorio donde se encuentra instalada la instancia, una vez en la raíz de la instalación, debemos ir a la carpeta Binn. En nuestro caso

cd “c:\Program Files\Microsoft SQL Server\MSSQL11.QASERVER\MSSQL\Binn”

imagen6

Ahora se debe ejecutar el siguiente comando

sqlservr -m -T4022 -T3659 -s”INSTANCE_NAME” -q”NEW_COLLATION”

Donde INSTANCE_NAME debe cambiarse por el nombre de la instancia que queremos modificar. Si solo existe una instancia no es necesario incluir este parámetro. NEW_COLLATION debe cambiarse por el Collation que se necesita, en nuestro caso “Latin1_General_100_CS_AS”. Por lo cual, nuestro comando quedaría de la siguiente manera.

sqlservr -m -T4022 -T3659 -s”QASERVER” -q”Latin1_General_100_CS_AS”

Una vez ejecutado el comando, se demora unos minutos ya que vuelve a crear todos los índices con el nuevo Collation

Nota: Dependiendo del tamaño de las bases de datos, el proceso puede tomar varios minutos y usar bastante espacio en disco, ya que los logs de cada base de datos guardan mucha información.

imagen7

Una vez terminado el proceso, se mostrará un mensaje avisando que la recuperación se completó, como se ve en la imagen anterior. Cerramos la ventana de CMD y abrimos una nueva para volver a subir los servicios de SQL con el siguiente comando.

NET START “SQL Server (INSTANCE_NAME)”

Donde hay que reemplazar “INSTANCE_NAME” por el nombre de la instancia, para nuestro caso “QASERVER”. Al ejecutar se mostrará algo como lo siguiente.

imagen8

Una vez que el servicio esté corriendo, solo nos falta revisar que los cambios se realizaron correctamente, para esto vamos a las propiedades de la instancia y como se ve en la imagen de más adelanta, ahora el COLLATION es “Latin1_General_100_CS_AS”

imagen9

Con estos pasos se finaliza el proceso de cambio de COLLATION, sin embargo, recomiendo ejecutar un paso más que es eliminar los logs de transacciones de las bases de datos a la cuales se les realizó el cambio, ya que podría llegar a pesar varios Gigas. Notar que estos procesos los estamos realizando en ambientes no productivos, ya que eliminar todo el logs transaccional podría ser un riesgo en ambientes productivos.

Para realizar esta reducción, se debe ejecutar el siguiente script en la base de datos, reemplazando DBNAME por el nombre de la base correspondiente y DBNAME_log por el nombre del archive de log, normalmente corresponde al nombre de la base seguido por “_log”. Para asegurarse, se debe ir a las propiedades de la base en la sección “FILEs” y ver el nombre lógico del archivo. Este proceso se debe realizar por cada base de datos a la cual se aplicó el cambio de COLLATION.

imagen10

USE DBNAME;

GO

— Se trunca el archivo de log cambiando el metodo de recuperacion a SIMPLE.

ALTER DATABASE DBNAME

SET RECOVERY SIMPLE;

GO

— Se reduce el archivo a 1 MB.

DBCC SHRINKFILE (DBNAME_log, 1);

GO

— Se vuelve a asignar el metodo de recuperacion a FULL.

ALTER DATABASE DBNAME

SET RECOVERY FULL;

GO

Share This
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn