Brassai Kao’s Tech Weave

Tag: Microsoft SQL Server

  • 定期將遠端SQL SERVER資料庫備份還原到本地端SQL SERVER

    以下為透過Windows網路芳鄰實現自動化異機備份及還原的方案。(網際網路的情境可搭配VPN實現)

    1. 在遠端設定排程以自動化備份資料庫到固定位置，這裡可透過維護計畫或是批次檔搭配工作排程器來實現。
    2. 參考以下批次檔指令，將遠端的備份檔案複製到本地端，再還原到SQL SERVER。

    net use \\{SOURCE_SERVER_IP} /user:{SOURCE_SERVER_ACCOUNT} {SOURCE_SERVER_PASSWORD}
    copy /y "\\{DBBACKUP_FILE_FULL_NETWORK_SOURCE_PATH}" "{DBBACKUP_FILE_FULL_LOCAL_TARGET_PATH}"
    SqlCmd -U {SQL_SERVER_ACCOUNT} -P {SQL_SERVER_PASSWORD} -Q "ALTER DATABASE {DB_NAME} SET single_user WITH rollback immediate"
    SqlCmd -U {SQL_SERVER_ACCOUNT} -P {SQL_SERVER_PASSWORD} -Q "RESTORE DATABASE {DB_NAME} FROM DISK='{DBBACKUP_FILE_FULL_LOCAL_TARGET_PATH}' WITH REPLACE"
    SqlCmd -U {SQL_SERVER_ACCOUNT} -P {SQL_SERVER_PASSWORD} -Q "ALTER DATABASE {DB_NAME} SET multi_user"
    ::[option command for restore source db account]    SqlCmd -U {SQL_SERVER_ACCOUNT} -P {SQL_SERVER_PASSWORD} -Q "USE {DB_NAME};EXEC sp_change_users_login 'Update_One','{ACCOUNT_MAP_ON_THE_SOURCE_DB}','{ACCOUNT_MAP_ON_THE_TARGET_DB}'"
    del "{DBBACKUP_FILE_FULL_LOCAL_TARGET_PATH}"

  • 使用SQL Server Profiler捕獲特定數據庫的SQL命令

    以下為標準的操作說明，不過在資料庫的篩選上，常常還是要放寬篩選條件，不然可能還是無法有效抓到想要的資料

    1. 開啟SQL Server Profiler:

    ◦ 在SQL Server Management Studio (SSMS) 中，點擊 “選單” 選項。

    ◦ 選擇 “SQL Server Profiler”。

    2. 在 “SQL Server Profiler” 中，點擊 “設定” 選項，然後選擇 “新建追蹤”。

    3. 在 “新建追蹤” 對話框中，為追蹤會話指定一個名稱，並選擇 “使用默認配置” 或根據需要進行自定義配置。（勾選顯示所有資料行）

    4. 在 “事件選擇” 選項卡中，展開 “TSQL” 節點。

    5. 在 “TSQL” 節點下選擇以下事件：

    ◦ “SQL:BatchCompleted”：捕獲SQL批處理執行完成的事件。

    ◦ “SQL:BatchStarting”：捕獲SQL批處理開始執行的事件。

    ◦ “SP:Completed”：捕獲存儲過程執行完成的事件。

    ◦ “SP:Starting”：捕獲存儲過程開始執行的事件。

    6. 也可以使用 “過濾器” 選項卡來指定要捕獲的數據庫。在 “選單” 選項卡中，點擊 “數據庫ID” 並選擇要監視的數據庫的ID。您可以通過查詢以下系統表來查找數據庫的ID：

    SELECT name, database_id FROM sys.databases

    1. 在 “事件選擇” 和 “選單” 配置完成後，點擊 “確定” 創建追蹤會話。

    2. 在 “SQL Server Profiler” 中，點擊 “開始” 圖標開始捕獲SQL命令。

    3. 當完成監視時，可以停止追蹤會話，並查看捕獲的SQL命令。

  • [Solved] .Net Core 在 Linux(Ubuntu) 連線 SQL 發生 SSL Handshake Error

    # 問題描述 Issue #
    .Net Core 5 程式使用 SqlClient 在 Linux (Ubuntu 20.04) 上連線較舊版本的 SQL Server 時，出現以下類似的錯誤訊息：
    Error Microsoft.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 - Encryption(ssl/tls) handshake failed) 

    # 問題原因 Reason #
    1) 較舊版本的 SQL Server 不支援 TLS 1.2 或更新版本的傳輸加密，而 Linux 所使用的 OpenSSL 預設為 TLS 1.2 或更新版本。並進而 導致連線 Sql Client 無法與 SQL Server 進行加密傳輸。
    2) .Net Core 5 程式使用的 Sql Client 經測試，在 Linux 上無法透過 Connection String 的 Encrypt=false 參數來強制關閉加密。

    # 解決方法 Solution #
    1) 編輯 OpenSSL 設定：sudo vi /etc/ssl/openssl.cnf
    2) 在最上面(讓其他設定以此為基礎)加入：openssl_conf = default_conf
    3) 接著加入以下設定：
    [default_conf]
    ssl_conf = ssl_sect

    [ssl_sect]
    system_default = system_default_sect

    [system_default_sect]
    MinProtocol = TLSv1
    CipherString = DEFAULT:@SECLEVEL=1

    4) 在 Connection String 中加入 TrustServerCertificate=true 以自動信任 SQL Server 的憑證。
    5) 重啟 OpenSSL 服務

  • Aras Innovator – Server Method for Client

    /*
    Use parameters that send from other method and Aras PLM permission to query the sql server.
    */

    var inn = this.getInnovator();
    var classification = this.getPropertyAttribute("Item","classification");
    var item_number = this.getPropertyAttribute("Item","item_number");

    Aras.Server.Security.Identity plmIdentity = Aras.Server.Security.Identity.GetByName("Aras PLM");
    bool permissionWasSet = Aras.Server.Security.Permissions.GrantIdentity(plmIdentity);
    try {
        var callframe = inn.newItem("SQL", "SQL PROCESS");
        callframe.setProperty("name", "GetLatestVersion");
        callframe.setProperty("PROCESS", "CALL");
        callframe.setProperty("ARG1", classification);
        callframe.setProperty("ARG2", item_number);
        callframe.setProperty("ARG3", year);
        var specialNumber = callframe.apply();
        return specialNumber;
    }
    finally {
        if (permissionWasSet) {
            Aras.Server.Security.Permissions.RevokeIdentity(plmIdentity);
        }
    }

  • SSRS – 解決「不支援功能: “向外延伸部署”」問題

    Report Services 在 SQL Server 2005 之後的企業版，新增了能串連多個Server的「向外延伸部署」的功能，且該功能會透過ReportServer資料庫內的Keys table來進行管理。

    但在一個多Report Services的環境中，有可能因操作不慎的關係(如電腦名稱更改後有cache殘留)，導致不小心在Keys資料表內多新增一筆Server資料，並造成非企業版的Server出現系統在使用不支援功能的錯誤。

    刪除Keys table內多餘的資料即可解決該問題。

  • SQL Server – 修改電腦名稱後的處置

    USE master

    sp_dropserver '<original computer name>';  
    GO  
    sp_addserver '<new computer name>', local;  
    GO
    ALTER LOGIN "<original computer name>\<windows login account>"
    WITH NAME="<new computer name>\<windows login account>"

  • SQL Server XML欄位存取

    以下為範例:

    INSERT INTO report_temp_data
(report_id,report_data)VALUES('<row>

<IdInvernadero>8</IdInvernadero>

<IdProducto>3</IdProducto>

<Name>Fred</Name>

</row>

<row>

<IdInvernadero>81</IdInvernadero>

<IdProducto>13</IdProducto>

<Name>Alan</Name>

</row>')

SELECT * FROM report_temp_data

SELECT *
, report_data.value('(/row/IdInvernadero)[1]','int') AS IdInvernadero
, report_data.value('(/row/IdProducto)[1]','int') AS IdProducto
, report_data.value('(/row/Name)[1]','nvarchar(50)') AS [Name]
FROM report_temp_data
WHERE report_id='test'

  • SQL Server – Mapping登入帳號到資料庫內的帳號

    SQL Server 實體和其掛載的資料庫，都有個別的帳號管理清單。如果掛載的資料庫是在該SQL Server建置的話，資料庫的帳號和SQL Server實體的帳號預設情況下便會自動建立對應。

    以Aras Innovator系統為例，由於資料庫的建立預設是用Innovator而不是dbo，因此在做備份資料庫還原時，就必須處理。

    (more…)

  • SQL Server – 好用的TRY_CONVERT()

    SQL Server 每次改版或多或少都會增加一些有助於程式開發的指令，而自SQL Server 2012之後，新增了延伸自既有CONVERT()指令的TRY_CONVERT()。

    (more…)