locked
SQL Server Migration to Azure SQL Database RRS feed

  • Question

  • Hi,

    I have created Azure SQL database but unfortunately I am unable to migrate my SQL server instance database to Azure SQL database because the collation level is different. How can I change the collation level to Windows Latin1_General_CI_AS on Azure SQL Database? 

    Also what is the best way to migrate 10 database to Azure SQL database instance?

    Thanks,
    Darshan Dave

    Thursday, March 2, 2017 11:23 AM

Answers

  • Hello,

    You cannot change the collation at the server level on SQL Azure, but you can change the collation of your SQL Azure database using below statement.

    -- Run on master database

    ALTER DATABASE yourdatabase COLLATE new_collation



    Make sure there are no connections to the database.

    select * from sys.sysprocesses where dbid=db_id('your db name')



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by Dan Rediske Thursday, March 2, 2017 5:40 PM
    Thursday, March 2, 2017 2:26 PM

All replies

  • Hello,

    You cannot change the collation at the server level on SQL Azure, but you can change the collation of your SQL Azure database using below statement.

    -- Run on master database

    ALTER DATABASE yourdatabase COLLATE new_collation



    Make sure there are no connections to the database.

    select * from sys.sysprocesses where dbid=db_id('your db name')



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by Dan Rediske Thursday, March 2, 2017 5:40 PM
    Thursday, March 2, 2017 2:26 PM
  • And, you cannot migrate a server or a master database, only a user database. Use the ALTER statement from Alberto

    carl rabeler

    Thursday, March 2, 2017 4:33 PM
  • Ok So if I cannot change the collation at server level on SQL Azure i.e. Windows Latin1_General_CI_AS so what is the equivalent collation level that SQL Azure can support for my databases? 

    Thursday, March 9, 2017 1:42 PM
  • Thanks Alberto for your response. 

    Just checking, so if I cannot change the collation at server level on SQL Azure and the database I am migrating have were on collation level Windows Latin1_General_CI_AS so what is the equivalent collation level that SQL Azure can support for my databases? 

    Please help?

    So the only solution to keep the same collation is to create a VM and install SQL server ( my own license ) ?


    Thursday, March 9, 2017 1:44 PM
  • Hello,

    The default collation is SQL_Latin1_General_CP1_CI_AS.

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, March 9, 2017 1:46 PM
  • Ok but this will affect my database columns if I change the collation of the database and performance right?
    Thursday, March 9, 2017 7:58 PM
  • Hello,

    Yes, usually specifying the collation on queries using COLLATE generates more reads and it will certainly have an impact on query performance.

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, March 9, 2017 8:13 PM