網路城邦
上一篇 回創作列表 下一篇 　 字體：小 中 大
從本地 SQL 資料庫遷移到雲端 SQL Database 的注意事項
2014/03/09 21:27:17瀏覽640｜回應0｜推薦0

上週將一個開發完成的網站部署到 Windows Azure 網站，在部署 SQL Database 的時候原本以為還蠻簡單的，因為 SQL Server 2012 的 Management Studio 都已經把 GUI 介面寫好了，但過程中卻遇到了一些惱人的問題，因此特別撰文紀錄解決的過程。

首先我先介紹從 SQL LocalDB 資料庫遷移到雲端 SQL Database 的過程：

1. 先在 Windows Azure 建立一個新的 SQL 資料庫伺服器，並設定登入名稱與密碼

   

2. 設定 SQL 資料庫伺服器允許的 ip 位址，並儲存設定

3. 取得資料庫伺服器的位址

   

4. 開啟 Management Studio 並如下圖執行「將資料庫部署到 SQL Azure」

   

5. 設定伺服器連接，然後設定建立資料庫的相關設定 (資料庫名稱、資料庫版本與大小)，然後按下一步

   

6. 確認設定後，按下 [完成] 按鈕

    image

感覺就快要完成了，但此時，才是夢靨的開始！

總而言之，由於雲端 SQL 資料庫的特性與本地資料庫不太一樣，有些本地資料庫才有的特性，因為在雲端 SQL 資料庫不支援，所以轉移上去時，會發生不相容的情況，會要求你先做出改變，然後才能把資料庫部署到雲端 SQL 資料庫上面。這裡我整理了三個常見的錯誤：

  • 移除多餘的本地資料庫使用者 (不要讓使用者擁有任何結構描述)
  • 移除 dbo.sysdiagrams 系統表格
  • 移除擴充屬性 (全部都要移除)

這三點的解決方式分別說明如下：

1. 移除多餘的本地資料庫使用者

若有任何存在於本地的資料庫使用者，建議都先移除，以免無法遷移資料庫。

2. 移除 dbo.sysdiagrams 系統表格 ( 雲端 SQL 資料庫不支援資料庫圖表 )

只要執行以下 T-SQL 即可將資料庫圖表的資訊全部移除：

DROP TABLE [dbo].[sysdiagrams]

3. 移除擴充屬性 (全部都要移除) ( 雲端 SQL 資料庫不支援擴充屬性 )

由於我們經常把資料庫文件寫在資料庫的擴充屬性中，因此只要有發佈到雲端之前，都要手動把所有擴充屬性刪除才行。

刪除所有表格上的擴充屬性，可參考以下 T-SQL 指令：

select 'EXEC sp_dropextendedproperty 
 @name = ''MS_Description''
 ,@level0type = ''schema''
 ,@level0name = ' + object_schema_name(extended_properties.major_id) + '
 ,@level1type = ''table''
 ,@level1name = ' + object_name(extended_properties.major_id)
from sys.extended_properties
where extended_properties.class_desc = 'OBJECT_OR_COLUMN'
and extended_properties.minor_id = 0
and extended_properties.name = 'MS_Description'

刪除所有欄位上的擴充屬性，可參考以下 T-SQL 指令：

select 'EXEC sp_dropextendedproperty
 @name = ''MS_Description''
 ,@level0type = ''schema''
 ,@level0name = ' + object_schema_name(extended_properties.major_id) + '
 ,@level1type = ''table''
 ,@level1name = ' + object_name(extended_properties.major_id) + '
 ,@level2type = ''column''
 ,@level2name = ' + columns.name
from sys.extended_properties
join sys.columns
on columns.object_id = extended_properties.major_id
and columns.column_id = extended_properties.minor_id
where extended_properties.class_desc = 'OBJECT_OR_COLUMN'
and extended_properties.minor_id > 0
and extended_properties.name = 'MS_Description'

事實上，還有許多資料庫物件，都可以添加擴充屬性，如果你還是無法成功將資料庫遷移上雲端 SQL 資料庫的化，你也可以透過 Query to drop all extended properties containing a certain name 這篇問題的第二份解答 ( 由 Steve Cole 提供的這份 )，透過這裡提供的指令，可以刪除大部分物件的擴充屬性，寫的還蠻完整的。以下是轉貼的副本，方便各位取用：

view plaincopy to clipboardprint?
  1. /*   
  2. This script will generate calls to sp_dropextendedproperty for every   
  3. extended property that exists in your database.   
  4. Actually, a caveat: I don't promise that it will catch each and every    
  5. extended property that exists, but I'm confident it will catch most of them!   
  6.   
  7. It is based on this:    
  8. http://blog.hongens.nl/2010/02/25/drop-all-extended-properties-in-a-mssql-database/    
  9. by Angelo Hongens.   
  10.   
  11. Also had lots of help from this:   
  12. http://www.sqlservercentral.com/articles/Metadata/72609/   
  13. by Adam Aspin   
  14.   
  15. Adam actually provides a script at that link to do something very similar   
  16. but when I ran it I got an error:   
  17. Msg 468, Level 16, State 9, Line 78   
  18. Cannot resolve the collation conflict between "Latin1_General_100_CS_AS" and "Latin1_General_CI_AS" in the equal to operation.  
  19.   
  20. So I put together this version instead.    
  21.   
  22. Use at your own risk.   
  23.   
  24. Jamie Thomson   
  25. 2012-03-25   
  26. */   
  27. /*Are there any extended properties? Let's take a look*/   
  28. /*  
  29. SELECT *,   
  30.        Object_name(major_id)   
  31. FROM   sys.extended_properties xp   
  32. */  
  33.   
  34. /*Now let's generate sp_dropextendedproperty statements for all of them.*/   
  35. --tables   
  36. SET nocount ON;   
  37.   
  38. SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = '''   
  39.        + Object_schema_name(xp.major_id)   
  40.        + ''' ,@level1type = ''table'' ,@level1name = '''   
  41.        + Object_name(xp.major_id) + ''''   
  42. FROM   sys.extended_properties xp   
  43.        JOIN sys.tables t   
  44.          ON xp.major_id = t.object_id   
  45. WHERE  xp.class_desc = 'OBJECT_OR_COLUMN'   
  46.        AND xp.minor_id = 0   
  47. UNION   
  48. --columns   
  49. SELECT 'EXEC sp_dropextendedproperty @name = ''' + sys.extended_properties.name   
  50.        + ''' ,@level0type = ''schema'' ,@level0name = '''   
  51.        + Object_schema_name(extended_properties.major_id)   
  52.        + ''' ,@level1type = ''table'' ,@level1name = '''   
  53.        + Object_name(extended_properties.major_id)   
  54.        + ''' ,@level2type = ''column'' ,@level2name = ''' + columns.name + ''''   
  55. FROM   sys.extended_properties   
  56.        JOIN sys.columns   
  57.          ON columns.object_id = extended_properties.major_id   
  58.             AND columns.column_id = extended_properties.minor_id   
  59. WHERE  extended_properties.class_desc = 'OBJECT_OR_COLUMN'   
  60.        AND extended_properties.minor_id > 0   
  61. UNION   
  62. --check constraints   
  63. SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = '''   
  64.        + Object_schema_name(xp.major_id)   
  65.        + ''' ,@level1type = ''table'' ,@level1name = '''   
  66.        + Object_name(cc.parent_object_id)   
  67.        + ''' ,@level2type = ''constraint'' ,@level2name = ''' + cc.name + ''''   
  68. FROM   sys.extended_properties xp   
  69.        JOIN sys.check_constraints cc   
  70.          ON xp.major_id = cc.object_id   
  71. UNION   
  72. --check constraints   
  73. SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = '''   
  74.        + Object_schema_name(xp.major_id)   
  75.        + ''' ,@level1type = ''table'' ,@level1name = '''   
  76.        + Object_name(cc.parent_object_id)   
  77.        + ''' ,@level2type = ''constraint'' ,@level2name = ''' + cc.name + ''''   
  78. FROM   sys.extended_properties xp   
  79.        JOIN sys.default_constraints cc   
  80.          ON xp.major_id = cc.object_id   
  81. UNION   
  82. --views   
  83. SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = '''   
  84.        + Object_schema_name(xp.major_id)   
  85.        + ''' ,@level1type = ''view'' ,@level1name = '''   
  86.        + Object_name(xp.major_id) + ''''   
  87. FROM   sys.extended_properties xp   
  88.        JOIN sys.views t   
  89.          ON xp.major_id = t.object_id   
  90. WHERE  xp.class_desc = 'OBJECT_OR_COLUMN'   
  91.        AND xp.minor_id = 0   
  92. UNION   
  93. --sprocs   
  94. SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = '''   
  95.        + Object_schema_name(xp.major_id)   
  96.        + ''' ,@level1type = ''procedure'' ,@level1name = '''   
  97.        + Object_name(xp.major_id) + ''''   
  98. FROM   sys.extended_properties xp   
  99.        JOIN sys.procedures t   
  100.          ON xp.major_id = t.object_id   
  101. WHERE  xp.class_desc = 'OBJECT_OR_COLUMN'   
  102.        AND xp.minor_id = 0   
  103. UNION   
  104. --FKs   
  105. SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = '''   
  106.        + Object_schema_name(xp.major_id)   
  107.        + ''' ,@level1type = ''table'' ,@level1name = '''   
  108.        + Object_name(cc.parent_object_id)   
  109.        + ''' ,@level2type = ''constraint'' ,@level2name = ''' + cc.name + ''''   
  110. FROM   sys.extended_properties xp   
  111.        JOIN sys.foreign_keys cc   
  112.          ON xp.major_id = cc.object_id   
  113. UNION   
  114. --PKs   
  115. SELECT   
  116. 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = ['   
  117. + SCH.name   
  118. + '], @level1type = ''TABLE'', @level1name = ['   
  119. + TBL.name   
  120. + '] , @level2type = ''CONSTRAINT'', @level2name = ['   
  121. + SKC.name + '] ,@name = '''   
  122. + Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''')   
  123. + ''''   
  124. FROM   sys.tables TBL   
  125.        INNER JOIN sys.schemas SCH   
  126.                ON TBL.schema_id = SCH.schema_id   
  127.        INNER JOIN sys.extended_properties SEP   
  128.                   INNER JOIN sys.key_constraints SKC   
  129.                           ON SEP.major_id = SKC.object_id   
  130.                ON TBL.object_id = SKC.parent_object_id   
  131. WHERE  SKC.type_desc = N'PRIMARY_KEY_CONSTRAINT'   
  132. UNION   
  133. --Table triggers   
  134. SELECT   
  135. 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = ['   
  136. + SCH.name   
  137. + '], @level1type = ''TABLE'', @level1name = ['   
  138. + TBL.name   
  139. + '] , @level2type = ''TRIGGER'', @level2name = ['   
  140. + TRG.name + '] ,@name = '''   
  141. + Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''')   
  142. + ''''   
  143. FROM   sys.tables TBL   
  144.        INNER JOIN sys.triggers TRG   
  145.                ON TBL.object_id = TRG.parent_id   
  146.        INNER JOIN sys.extended_properties SEP   
  147.                ON TRG.object_id = SEP.major_id   
  148.        INNER JOIN sys.schemas SCH   
  149.                ON TBL.schema_id = SCH.schema_id   
  150. UNION   
  151. --UDF params   
  152. SELECT   
  153. 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = ['   
  154. + SCH.name   
  155. + '], @level1type = ''FUNCTION'', @level1name = ['   
  156. + OBJ.name   
  157. + '] , @level2type = ''PARAMETER'', @level2name = ['   
  158. + PRM.name + '] ,@name = '''   
  159. + Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''')   
  160. + ''''   
  161. FROM   sys.extended_properties SEP   
  162.        INNER JOIN sys.objects OBJ   
  163.                ON SEP.major_id = OBJ.object_id   
  164.        INNER JOIN sys.schemas SCH   
  165.                ON OBJ.schema_id = SCH.schema_id   
  166.        INNER JOIN sys.parameters PRM   
  167.                ON SEP.major_id = PRM.object_id   
  168.                   AND SEP.minor_id = PRM.parameter_id   
  169. WHERE  SEP.class_desc = N'PARAMETER'   
  170.        AND OBJ.type IN ( 'FN', 'IF', 'TF' )   
  171. UNION   
  172. --sp params   
  173. SELECT   
  174. 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = ['   
  175. + SCH.name   
  176. + '], @level1type = ''PROCEDURE'', @level1name = ['   
  177. + SPR.name   
  178. + '] , @level2type = ''PARAMETER'', @level2name = ['   
  179. + PRM.name + '] ,@name = '''   
  180. + Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''')   
  181. + ''''   
  182. FROM   sys.extended_properties SEP   
  183.        INNER JOIN sys.procedures SPR   
  184.                ON SEP.major_id = SPR.object_id   
  185.        INNER JOIN sys.schemas SCH   
  186.                ON SPR.schema_id = SCH.schema_id   
  187.        INNER JOIN sys.parameters PRM   
  188.                ON SEP.major_id = PRM.object_id   
  189.                   AND SEP.minor_id = PRM.parameter_id   
  190. WHERE  SEP.class_desc = N'PARAMETER'   
  191. UNION   
  192. --DB   
  193. SELECT 'EXEC sys.sp_dropextendedproperty @name = '''   
  194.        + Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''')   
  195.        + ''''   
  196. FROM   sys.extended_properties SEP   
  197. WHERE  class_desc = N'DATABASE'   
  198. UNION   
  199. --schema   
  200. SELECT   
  201. 'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = ['   
  202. + SCH.name + '] ,@name = '''   
  203. + Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''')   
  204. + ''''   
  205. FROM   sys.extended_properties SEP   
  206.        INNER JOIN sys.schemas SCH   
  207.                ON SEP.major_id = SCH.schema_id   
  208. WHERE  SEP.class_desc = N'SCHEMA'   
  209. UNION   
  210. --DATABASE_FILE   
  211. SELECT   
  212. 'EXEC sys.sp_dropextendedproperty @level0type = N''FILEGROUP'', @level0name = ['   
  213. + DSP.name   
  214. + '], @level1type = ''LOGICAL FILE NAME'', @level1name = '   
  215. + DBF.name + ' ,@name = '''   
  216. + Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''')   
  217. + ''''   
  218. FROM   sys.extended_properties SEP   
  219.        INNER JOIN sys.database_files DBF   
  220.                ON SEP.major_id = DBF.file_id   
  221.        INNER JOIN sys.data_spaces DSP   
  222.                ON DBF.data_space_id = DSP.data_space_id   
  223. WHERE  SEP.class_desc = N'DATABASE_FILE'   
  224. UNION   
  225. --filegroup   
  226. SELECT   
  227. 'EXEC sys.sp_dropextendedproperty @level0type = N''FILEGROUP'', @level0name = ['   
  228. + DSP.name + '] ,@name = '''   
  229. + Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''')   
  230. + ''''   
  231. FROM   sys.extended_properties SEP   
  232.        INNER JOIN sys.data_spaces DSP   
  233.                ON SEP.major_id = DSP.data_space_id   
  234. WHERE  DSP.type_desc = 'ROWS_FILEGROUP'  

調整完成後，資料庫就可以順利遷移了！ ^_^

相關連結
( 知識學習｜其他 )
回應 推薦文章 列印 加入我的文摘
上一篇 回創作列表 下一篇

引用
引用網址：https://classic-blog.udn.com/article/trackback.jsp?uid=allya&aid=11619744