19 abril 2012

Alterando o Collate Default do Servidor SQL Server



Pessoal, aqui vai uma dica muito legal para quem instalou o SQL Server em um Collate e quer mudar para outro, migrando suas tabelas junto, vale a pena conferir...


Mas antes, o que é COLLATE??


COLLATE é uma propriedade do SQL SERVER que se refere ao tratamento dos dados que o SQL vai manipular.
É nele que dizemos se o SQL diferenciará maiúsculas de minúsculas (case-sensitive), acentuação (accent-sensitive), etc.
Com a definição padrão do SQL se fizermos uma consulta por "sebastiao" em uma tabela, o SQL só nos trará os registros que possuírem "sebastiao".
Com o COLLATE modificado para SQL_Latin1_General_CP1_CI_AI por exemplo ele trará todos os registros que tiverem " sebastião"," sebastiao "," Sebastiao","SEBASTIÃO", etc...



Ou seja para o SQL, a = ã, ä, A, á.... e vice versa.


Uma dúvida muito comum nos fóruns de discussões é: Como alterar o collate de um servidor ou banco de dados? Muitos optam por reinstalar o SQL Server do zero, outros fazem o rebuild da master e demais bancos de sistema, mas aqui faremos diferente:



Nota: Ressalto que o procedimento apresentado aqui é um procedimento não documentado e sua execução é por sua conta e risco. O nosso colega Sérgio Monteiro (que foi quem postou esta solução no fórum do MSDN Brasil) disse já utilizar este procedimento a alguns anos e nunca teve problemas. No entanto, cada ambiente é um ambiente e não posso garantir que isto não lhe trará problemas futuros.
Para tranqüilizar, só posso dizer que este é o mesmo procedimento utilizado pelo utilitário rebuildm.exe quando usado para alterar o collate de uma instância SQL Server 2000 ou ainda o mesmo utilizado quando se faz o REBUILD de uma instância do SQL Server 2005/2008/2008R2 (não testado no 2012) utilizando o procedimento abaixo, conforme documentando do Books Online no tópico Setting and Changing the Server Collation.




Como Executar o Procedimento

1. O primeiro passo é obviamente executar um bakup de todas as bases, incluindo as de sistema master,msdb e model.
2. Certifique-se que você não possui nenhuma base de dados em readonly, restoring ou qualquer outro status que impeça sua alteração e garanta que as bases não estão sem espaço livre. Já tive erros neste procedimento porque algumas bases estavam sem espaço livre.
3. Pare o serviço do SQL Server. É possível usar vários métodos, um deles é parar os serviços usando o utilitário SQL Server Configuration Manager (sqlservermanager10.msc) como faço na figura abaixo.





4. Abra um prompt de comando e navegue até o diretório de instalação do SQL Server. Localize o arquivo sqlservr.exe e execute sqlservr -m -T4022 -T3659 -q"novo_collate".

Duas observações importantes: O -T deve ser maiúsculo e se a instalação o SQL Server estiver utilizando um nome de instância, a opção -s<nome_da_instância> deve ser adicionada.


4. Neste ponto o SQL Server será iniciado em single user mode e alterará o collate do servidor e todas as bases para o collate especificado na opção -q. Detalhe, lembre-se que este procedimento é NÃO Documentado, então não procure pelo parâmetro -q na lista de parâmtros do sqlservr.exe /? pois você não irá encontrá-lo :)




5. Ao final do processo tecle Ctrl+C e reinicie os serviços do SQL Server. Por exemplo utilizando o SQL Server Service Manager.







Para exemplificar, alterei o collate de SQL_Latin1_General_Cp1_CI_AI para SQL_Latin1_General_Cp1_CS_AS


Como pode ser visto nas imagens acima, se tiverem seguido os procedimentos de forma correta, neste momento a instância do SQL Server, todas as bases de dados e suas respectivas tabelas e colunas do tipo caracter estarão com o novo collation.
Compatibilidade
O procedimento foi testado com sucesso nas seguintes versões do SQL Server: SQL Server 2000, SQL Server 2005 e SQL Server 2008 e SQL Server 2008 R2. Embora não tenha sido testado com o SQL Server 7.0 e SQL Server 2012, acredito que deva funcionar sem problemas.

Obs: Como eu destaquei no início este é um procedimento não documentado, então, a utilização deste procedimento é por sua conta e risco. 


Abraços a todos!

6 comentários:

  1. Kra eu obrigado pelo POST mas eu estou com um enorme problema.
    Eu tenho uma tabela do banco de dados que usa o SQL_Latin1_General_1857_CI_AI e com isto os caracteres dentro do banco de dados estão estranho e resolvi passar eles para o SQL_Latin1_General_Cp1_CI_AI.
    Mas a questão é que os caracteres que estão inseridos no banco de dados continuam com as acentuações muitos estranhas.
    Sabe como faço para alterar os caracteres dos registros dentro do banco de dados?

    ResponderExcluir
    Respostas
    1. Olá Prof. Baco. Você fez o procedimento como usuário sa ou como o login do windows? Tente repetir o processo com o sa.
      Os campos que estão esses caracteres são VARCHAR ou BLOB?

      Excluir
  2. Obrigado por salvar minha vida. No desespero não quis nem saber se é um procedimento documento ou não, se a Microsoft recomenda ou não. Funcionou direitinho. Parabéns!!!

    ResponderExcluir
  3. Realizei o procedimento no SQL 2012 e funcionou perfeitamente....obrigado pelo POST

    ResponderExcluir
  4. Realizei o procedimento no SQL 2012 e funcionou perfeitamente.....obrigado pelo POST

    ResponderExcluir
  5. Funcionou para mim também no SQL 2012. Valeu !!!

    ResponderExcluir