The Will Will Web

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

從本地 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. 確認設定後,按下 [完成] 按鈕

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

/* 
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'

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

相關連結