分類: Azure

在Apple Mac M1 Azure Sql Edge的Docker中Restore MSSQL Bak

在Apple Mac M1 Azure Sql Edge的Docker中Restore MSSQL Bak

想從一些舊的Sql Server的Bak檔案來備份與還原DB是再自然不過的事了,但殊不知,目前試著在Parallel Desktop上的Windows 上裝 Sql Server還是有遇到Apple Silicon晶片相容的問題,所以只好寄望看看Mac下的Docker版本的Sql Edge有沒有機會可以載入Bak。

剛好找到一片影片教學(在最後的reference)

在此再記錄一下 這個簡單的步驟,先在Docker建立 mcr.microsoft.com/azure-sql-edge的容器

docker run \
  --name "/azuresqledge" \
  --runtime "runc" \
  --log-driver "json-file" \
  --restart "no" \
  --cap-add "SYS_PTRACE" \
  --publish "0.0.0.0:1433:1433/tcp" \
  --network "bridge" \
  --hostname "ce6067cab6e1" \
  --expose "1401/tcp" \
  --expose "1433/tcp" \
  --env "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin" \
  --env "MSSQL_RPC_PORT=135" \
  --env "CONFIG_EDGE_BUILD=1" \
  --env "PAL_BOOT_WITH_MINIMAL_CONFIG=1" \
  --env "PAL_ENABLE_PAGE_ALIGNED_PE_FILE_CREATION=1" \
  --env "LD_LIBRARY_PATH=/opt/mssql/lib" \
  --env "ACCEPT_EULA=1" \
  --env "MSSQL_SA_PASSWORD=自訂密碼" \
  --label "com.azure.dev.image.build.sourceversion"="ce89b6c967e193696164e69929c3341c38ba9c7e" \
  --label "com.azure.dev.image.system.teamfoundationcollectionuri"="https://dev.azure.com/tigerdid/" \
  --label "com.microsoft.product"="Microsoft SQL Server" \
  --label "com.microsoft.version"="15.0.2000.155916" \
  --label "org.opencontainers.image.ref.name"="ubuntu" \
  --label "org.opencontainers.image.version"="18.04" \
  --label "vendor"="Microsoft" \
  --detach \
  --entrypoint "/opt/mssql/bin/permissions_check.sh" \
  "mcr.microsoft.com/azure-sql-edge" \
  "/bin/sh" "-c" "/opt/mssql/bin/launchpadd -usens=false -enableOutboundAccess=true -usesameuser=true -sqlGroup root -- -reparentOrphanedDescendants=true -useDefaultLaunchers=false & /app/asdepackage/AsdePackage & /opt/mssql/bin/sqlservr" 

測試一下使用Mac上強大的Azure Data Studio,可以連線進去

接著建立目錄並copy檔案

docker exec -it azuresqledge mkdir /var/opt/mssql/backup 
docker cp '/Users/paul_huang/Downloads/ExampleDB.bak' azuresqledge:/var/opt/mssql/backup

成功的話會像這樣
Successfully copied 202MB to azuresqledge:/var/opt/mssql/backup

Azure Data Studio跟Visual Studio Code一樣,可以找延伸模組來安裝,彈性還不錯

像 Profiler, Schema Compare,都可以再外掛進來

接著到 localhost的HOME目錄,可以找的到 “Restore”

選擇一下剛剛用Docker複製進去的檔案

剩下的匯入流程就沒啥問題,Docker的Azure Sql Edge確實也可以還原bak檔!

跨平台,讚啦,Mac無極限~

Reference Video

[踩雷] Login failed for user ”. Reason: An attempt to login using SQL authentication failed

[踩雷] Login failed for user ”. Reason: An attempt to login using SQL authentication failed

最近在Mac 上架了Docker起了一台mcr.microsoft.com/azure-sql-edge

用Azure Data Studio可以透過 Connection String登入

以下為範例1

Server=localhost;Trusted_Connection=False;Integrated Security=False;User ID=sa;Password=XXXXXXXXXX;Database=wexflow_netcore;trustservercertificate=true",

興高采烈的把這個連線字串配置到Config中,結果跑CLI過不了,得到Docker Logs資訊如下

2023-08-23 02:58:28.50 Logon       Login failed for user ”. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: 192.168.215.1]

他給的訊息是說Server is configured for Integrated authentication only.整整誤導了我1個小時

Try了老半天,最後竟然是發現那個可惡的User Id的d要給我小寫就過了!

如下

Server=localhost;Trusted_Connection=False;Integrated Security=False;User Id=sa;Password=XXXXXXXXXX;Database=wexflow_netcore;trustservercertificate=true"

總覺得這個不是很意外的錯誤,但竟然有種被 誤導的羞恥感…

謹記!

上傳大檔到Azure Blob進行SQL的Bulk Insert

上傳大檔到Azure Blob進行SQL的Bulk Insert

Download AzCopy and Unzip to C:(for Example)

下載網址:https://docs.microsoft.com/zh-tw/azure/storage/common/storage-use-azcopy-v10

注:Blob要先開啟Share的簽章設定:Shared access signature

azcopy make "https://xxxxxxx.blob.core.windows.net/dbfile?sv=2020-02-10&ss=bfqt&srt=sco&sp=rwdlacupx&se=2021-04-26T23:26:15Z&st=2021-04-26T15:26:15Z&spr=https&sig=1asdfasdbasdfasdbqasdfasdfasdf%3D"

azcopy.exe copy "C:\test.csv" "https://xxxxxxx.blob.core.windows.net/dbfile?sv=2020-02-10&ss=bfqt&srt=sco&sp=rwdlacupx&se=2021-04-26T23:26:15Z&st=2021-04-26T15:26:15Z&spr=https&sig=1asdfasdbasdfasdbqasdfasdfasdf%3D"

In Query Editor(Sql Azure)

--資料庫密碼
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!asdkfajd;skfjasldjkfalksdjflasd@' ;

--建立權杖(use Blob SAS Token)
CREATE DATABASE SCOPED CREDENTIAL AccessAzureSecret
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = 'sv=2020-02-10&ss=bfqt&srt=sco&sp=rwdlacupx&se=2021-04-26T23:44:32Z&st=2021-04-26T15:44:32Z&spr=https&sig=1asdfasdbasdfasdbqasdfasdfasdf%3D' ;
CREATE EXTERNAL DATA SOURCE dbFile
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://xxxxxxx.blob.core.windows.net/dbfile'
 , CREDENTIAL= AccessAzureSecret)


BULK INSERT [dbo].[learning_records] FROM 'stage_learning_record.csv' WITH (
    DATA_SOURCE = 'dbFile',
    FIELDTERMINATOR=',',rowterminator = '\n', KEEPNULLS);

註:
csv裡面,欄位是NULL的話,要先把欄位NULL取代成空字串(不是””), 若日期長度有問題的話,就也要先取代掉

若bulk insert執行發生超過10次錯誤,就會被中斷哦