Answered by:
SQL Server Migration to Azure SQL Database

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 DaveThursday, 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 databaseALTER 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 databaseALTER 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.comThursday, 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.comThursday, March 9, 2017 8:13 PM