Introduction
Enterprise will have so many databases running in its existing systems. If those systems are legacy the databases might have all the legacy features which SQL Azure (The PaaS offering not the SQL VM in Azure) does not support. How to move such databases to SQL Azure as part of Azure adoption. If anyone wonder why the SQL Azure is not backward compatible with standalone SQL Server versions welcome to PaaS.
Research & Solution
If we google, we can get so many options on how we can move an on-premise database to SQL Azure. Some of those are as below.
After doing good research, the best option found to be the .bacpac mechanism using SQLPackage utility. As we can see in any production databases, the file groups will be all over the place to increase performance. the bacpac mechanism using SQLPackage will eliminate the file groups issue in it's latest versions.
Problems
But it may not be the easy and hurdle free migration road. Below are some issues.
SQLPackage fails on large tables
The SQLPackage.exe has its own timeouts. When there are large tables the timeouts may hit and it will error out. When it error out, there could be a message as follows.
Processing Table '[dbo].[large tables with millions of rows]'.
*** A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Solution
The message tells some network issue not specifically on timeout. But after trial and error this seems to be related to timeouts of SQLPackage.exe utility. It has some params to control the timeout. The usage is as follows
sqlpackage.exe /Action:Import /tsn:tcp:<databaseserver>.database.windows.net /tdn:<database name> /tu:<user> /tp:<password> /sf:<local path to file.bacpac> /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P15 /p:Storage=File /p:CommandTimeout=0 /TargetTimeout:600
The highlighted are 2 params worked out. The value depends on the size of the database. /p:Storage=File is must when we deal with large databases anyway. It cannot be the other option where memory is used which may drain out quickly.
SQL Azure Server supports only one collation
If the on-premise application is serving globally the collation of the database might have different than the collation supported by the SQL Azure Server.If anyone is confused on whether there is a SQL Server for PaaS offering, yes there is one. This easily makes us think that there would be real VMs running behind he SQL PaaS offering.
Coming back to the problem, we can have database in SQL Azure with different collation as of SQL Azure instance. But if there are stored procedures which needs to access the system objects, they fail. The default answer would be to change the collation of SQL Azure Server. But unfortunately it is not supported. SQL Azure instance is kind of hard coded to 'SQL_Latin1_General_CP1_CI_AS' collation. Microsoft has their own reason it seems. But as user what we can do?
Solution
Modify our SQL code to include collation or change the collation of our database to the collation of SQL Azure instance which is 'SQL_Latin1_General_CP1_CI_AS'. It is simple to say change the collation but in enterprise its sequence of approvals especially if the collation is set as standard across multiple applications.Conclusion
It is nice and good to use Azure. It works well when the application is from scratch and cloud native. But when it comes to migration of existing applications, its nightmare. Sometimes feels like the Azure is not matured for enterprise.
https://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx
https://github.com/Microsoft/vsts-tasks/issues/1441
https://social.msdn.microsoft.com/Forums/en-US/3dd204b3-603d-4c88-9f85-083f69323cd1/sqlpackage-publish-timeout?forum=ssdt
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues
https://stackoverflow.com/questions/16089321/restore-a-bacpac-file-to-sql-azure-with-large-database-size-sqlpackage-exe
https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/01/31/using-sqlpackage-to-import-or-export-azure-sql-db/
3 comments:
Good article about bacpac.
Greetings of the day. As a learner, I understand the idea: "Azure @ Enterprise - Moving from SQL VM to SQL Azure." This topic describes research and solutions clearly, and the links provided are helpful. These are the most informative ideas and are too good. I appreciate you for giving me the information!
Post a Comment