locked
SQL Server Migration to Azure SQL Database RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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