從本地 SQL 資料庫遷移到雲端 SQL Database 的注意事項

上週將一個開發完成的網站部署到 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. 確認設定後，按下 [完成] 按鈕

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

總而言之，由於雲端 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 提供的這份 )，透過這裡提供的指令，可以刪除大部分物件的擴充屬性，寫的還蠻完整的。以下是轉貼的副本，方便各位取用：

/* 
This script will generate calls to sp_dropextendedproperty for every 
extended property that exists in your database. 
Actually, a caveat: I don't promise that it will catch each and every  
extended property that exists, but I'm confident it will catch most of them! 

It is based on this:  
http://blog.hongens.nl/2010/02/25/drop-all-extended-properties-in-a-mssql-database/  
by Angelo Hongens. 

Also had lots of help from this: 
http://www.sqlservercentral.com/articles/Metadata/72609/ 
by Adam Aspin 

Adam actually provides a script at that link to do something very similar 
but when I ran it I got an error: 
Msg 468, Level 16, State 9, Line 78 
Cannot resolve the collation conflict between "Latin1_General_100_CS_AS" and "Latin1_General_CI_AS" in the equal to operation.

So I put together this version instead.  

Use at your own risk. 

Jamie Thomson 
2012-03-25 
*/ 
/*Are there any extended properties? Let's take a look*/ 
/*
SELECT *, 
       Object_name(major_id) 
FROM   sys.extended_properties xp 
*/

/*Now let's generate sp_dropextendedproperty statements for all of them.*/ 
--tables 
SET nocount ON; 

SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = ''' 
       + Object_schema_name(xp.major_id) 
       + ''' ,@level1type = ''table'' ,@level1name = ''' 
       + Object_name(xp.major_id) + '''' 
FROM   sys.extended_properties xp 
       JOIN sys.tables t 
         ON xp.major_id = t.object_id 
WHERE  xp.class_desc = 'OBJECT_OR_COLUMN' 
       AND xp.minor_id = 0 
UNION 
--columns 
SELECT 'EXEC sp_dropextendedproperty @name = ''' + sys.extended_properties.name 
       + ''' ,@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 
UNION 
--check constraints 
SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = ''' 
       + Object_schema_name(xp.major_id) 
       + ''' ,@level1type = ''table'' ,@level1name = ''' 
       + Object_name(cc.parent_object_id) 
       + ''' ,@level2type = ''constraint'' ,@level2name = ''' + cc.name + '''' 
FROM   sys.extended_properties xp 
       JOIN sys.check_constraints cc 
         ON xp.major_id = cc.object_id 
UNION 
--check constraints 
SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = ''' 
       + Object_schema_name(xp.major_id) 
       + ''' ,@level1type = ''table'' ,@level1name = ''' 
       + Object_name(cc.parent_object_id) 
       + ''' ,@level2type = ''constraint'' ,@level2name = ''' + cc.name + '''' 
FROM   sys.extended_properties xp 
       JOIN sys.default_constraints cc 
         ON xp.major_id = cc.object_id 
UNION 
--views 
SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = ''' 
       + Object_schema_name(xp.major_id) 
       + ''' ,@level1type = ''view'' ,@level1name = ''' 
       + Object_name(xp.major_id) + '''' 
FROM   sys.extended_properties xp 
       JOIN sys.views t 
         ON xp.major_id = t.object_id 
WHERE  xp.class_desc = 'OBJECT_OR_COLUMN' 
       AND xp.minor_id = 0 
UNION 
--sprocs 
SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = ''' 
       + Object_schema_name(xp.major_id) 
       + ''' ,@level1type = ''procedure'' ,@level1name = ''' 
       + Object_name(xp.major_id) + '''' 
FROM   sys.extended_properties xp 
       JOIN sys.procedures t 
         ON xp.major_id = t.object_id 
WHERE  xp.class_desc = 'OBJECT_OR_COLUMN' 
       AND xp.minor_id = 0 
UNION 
--FKs 
SELECT 'EXEC sp_dropextendedproperty @name = ''' + xp.name + ''' ,@level0type = ''schema'' ,@level0name = ''' 
       + Object_schema_name(xp.major_id) 
       + ''' ,@level1type = ''table'' ,@level1name = ''' 
       + Object_name(cc.parent_object_id) 
       + ''' ,@level2type = ''constraint'' ,@level2name = ''' + cc.name + '''' 
FROM   sys.extended_properties xp 
       JOIN sys.foreign_keys cc 
         ON xp.major_id = cc.object_id 
UNION 
--PKs 
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' 
+ SCH.name 
+ '], @level1type = ''TABLE'', @level1name = [' 
+ TBL.name 
+ '] , @level2type = ''CONSTRAINT'', @level2name = [' 
+ SKC.name + '] ,@name = ''' 
+ Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''') 
+ '''' 
FROM   sys.tables TBL 
       INNER JOIN sys.schemas SCH 
               ON TBL.schema_id = SCH.schema_id 
       INNER JOIN sys.extended_properties SEP 
                  INNER JOIN sys.key_constraints SKC 
                          ON SEP.major_id = SKC.object_id 
               ON TBL.object_id = SKC.parent_object_id 
WHERE  SKC.type_desc = N'PRIMARY_KEY_CONSTRAINT' 
UNION 
--Table triggers 
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' 
+ SCH.name 
+ '], @level1type = ''TABLE'', @level1name = [' 
+ TBL.name 
+ '] , @level2type = ''TRIGGER'', @level2name = [' 
+ TRG.name + '] ,@name = ''' 
+ Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''') 
+ '''' 
FROM   sys.tables TBL 
       INNER JOIN sys.triggers TRG 
               ON TBL.object_id = TRG.parent_id 
       INNER JOIN sys.extended_properties SEP 
               ON TRG.object_id = SEP.major_id 
       INNER JOIN sys.schemas SCH 
               ON TBL.schema_id = SCH.schema_id 
UNION 
--UDF params 
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' 
+ SCH.name 
+ '], @level1type = ''FUNCTION'', @level1name = [' 
+ OBJ.name 
+ '] , @level2type = ''PARAMETER'', @level2name = [' 
+ PRM.name + '] ,@name = ''' 
+ Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''') 
+ '''' 
FROM   sys.extended_properties SEP 
       INNER JOIN sys.objects OBJ 
               ON SEP.major_id = OBJ.object_id 
       INNER JOIN sys.schemas SCH 
               ON OBJ.schema_id = SCH.schema_id 
       INNER JOIN sys.parameters PRM 
               ON SEP.major_id = PRM.object_id 
                  AND SEP.minor_id = PRM.parameter_id 
WHERE  SEP.class_desc = N'PARAMETER' 
       AND OBJ.type IN ( 'FN', 'IF', 'TF' ) 
UNION 
--sp params 
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' 
+ SCH.name 
+ '], @level1type = ''PROCEDURE'', @level1name = [' 
+ SPR.name 
+ '] , @level2type = ''PARAMETER'', @level2name = [' 
+ PRM.name + '] ,@name = ''' 
+ Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''') 
+ '''' 
FROM   sys.extended_properties SEP 
       INNER JOIN sys.procedures SPR 
               ON SEP.major_id = SPR.object_id 
       INNER JOIN sys.schemas SCH 
               ON SPR.schema_id = SCH.schema_id 
       INNER JOIN sys.parameters PRM 
               ON SEP.major_id = PRM.object_id 
                  AND SEP.minor_id = PRM.parameter_id 
WHERE  SEP.class_desc = N'PARAMETER' 
UNION 
--DB 
SELECT 'EXEC sys.sp_dropextendedproperty @name = ''' 
       + Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''') 
       + '''' 
FROM   sys.extended_properties SEP 
WHERE  class_desc = N'DATABASE' 
UNION 
--schema 
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' 
+ SCH.name + '] ,@name = ''' 
+ Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''') 
+ '''' 
FROM   sys.extended_properties SEP 
       INNER JOIN sys.schemas SCH 
               ON SEP.major_id = SCH.schema_id 
WHERE  SEP.class_desc = N'SCHEMA' 
UNION 
--DATABASE_FILE 
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''FILEGROUP'', @level0name = [' 
+ DSP.name 
+ '], @level1type = ''LOGICAL FILE NAME'', @level1name = ' 
+ DBF.name + ' ,@name = ''' 
+ Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''') 
+ '''' 
FROM   sys.extended_properties SEP 
       INNER JOIN sys.database_files DBF 
               ON SEP.major_id = DBF.file_id 
       INNER JOIN sys.data_spaces DSP 
               ON DBF.data_space_id = DSP.data_space_id 
WHERE  SEP.class_desc = N'DATABASE_FILE' 
UNION 
--filegroup 
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''FILEGROUP'', @level0name = [' 
+ DSP.name + '] ,@name = ''' 
+ Replace(Cast(SEP.name AS NVARCHAR(300)), '''', '''''') 
+ '''' 
FROM   sys.extended_properties SEP 
       INNER JOIN sys.data_spaces DSP 
               ON SEP.major_id = DSP.data_space_id 
WHERE  DSP.type_desc = 'ROWS_FILEGROUP'

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

相關連結

The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

從本地 SQL 資料庫遷移到雲端 SQL Database 的注意事項

留言評論

相關文章

留言評論