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

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

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

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

    ![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/95b565b9abb48c4e6786-image_2417.png)

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

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

    ![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/590b23b7b1bd873092ba-image_2418.png)

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

    ![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/a3069ffed003c6cd9800-image_2419.png)

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

    ![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/ce6acdaca9185ab80a1e-image_2420.png)

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

    ![image](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/a21d705a74f9bb965b59-image_2421.png "image")

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

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/e94d9edad82e71e15e5f-image_2422.png)

總而言之，由於雲端 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_propertieswhere extended_properties.class_desc = 'OBJECT_OR_COLUMN'and extended_properties.minor_id = 0and 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.namefrom sys.extended_propertiesjoin sys.columnson columns.object_id = extended_properties.major_idand columns.column_id = extended_properties.minor_idwhere extended_properties.class_desc = 'OBJECT_OR_COLUMN'and extended_properties.minor_id > 0and extended_properties.name = 'MS_Description'
```

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

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

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

![](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/da40361412d349a43035-image_2423.png)

**相關連結**

-   [sp\_dropextendedproperty (Transact-SQL)](http://technet.microsoft.com/en-us/library/ms178595.aspx)
-   [Migrating a Database to SQL Azure using SSDT - SQL Server Data Tools Team Blog](https://learn.microsoft.com/en-us/archive/blogs/ssdt/migrating-a-database-to-sql-azure-using-ssdt)
-   [Migration from MS SQL Server to MS SQL Azure: Challenges and Solutions](https://web.archive.org/web/20160407173338/http://www.dbbest.com:80/blog/migration-from-ms-sql-server-to-ms-sql-azure-challenges-and-solutions/)