SQL Server Gentleman Services 資料庫紳士密令


  1. 說明
    1. Login
    2. User
    3. Permissions
    4. Database
    5. Objects, Table & Data
      1. 欄位長度調整
      2. Restart Database
    6. Database Backup
    7. Database Restore
    8. 資料庫正在還原問題
      1. Restore To Availability Groups Database

SQL Server Gentleman Services 🎩

SQL Server Logo

說明

Login

建立 Login 需要考慮使用者的驗證方式

  • Windows 驗證
  • SQL 驗證
USE [master]
GO
-- SQL Login
CREATE LOGIN [loginUser] WITH PASSWORD = N'********',
  DEFAULT_DATABASE=[master],
  CHECK_EXPIRATION=OFF,
  CHECK_POLICY=OFF

-- Windows Login
CREATE LOGIN [domain\loginUser] FROM WINDOWS
  WITH DEFAULT_DATABASE = [master]

Create Login 需要注意保持不同 Availability Group 之間的一致。

因為 Login 是伺服器物件,所以同步建立必須自行完成,可以先於其中一個 Group 完成建立後,使用 EXEC sp_help_revlogin 將 Login 建立到另其他的 Group,安全性不需要重新設定。

SQL Server Restore With Orphaned User Missing User (孤兒 / 孤立使用者)

User

建立 User 需要有對應的 Login:

USE [AdventureWorks]
GO
CREATE USER [userName] FOR LOGIN [loginUser]
ALTER ROLE [db_datareader] ADD MEMBER [userName]
ALTER ROLE [db_datawriter] ADD MEMBER [userName]

Permissions

授權的公式:

  • 授予 登入/使用者 資料庫伺服器.資料庫.物件 權限項目
  • 授予 登入/使用者 資料庫伺服器.資料庫 角色
  • 授予 登入/使用者 資料庫伺服器 角色

🚫 絕不任意給予 db_owner, db_accessadmin, db_securityadmin, db_backoperator Roles

權限依照最小授權的方式進行,例行性的權限可以用 role 的方式給予,例如 db_datareader 與 db_datawriter

ALTER ROLE [db_datareader] ADD MEMBER [userName]
ALTER ROLE [db_datawriter] ADD MEMBER [userName]

對於單一資料庫物件的讀寫權限包含 SELECT, DELETE, UPDATE 及 INSERT:

GRANT SELECT ON dbo.TableName TO User
GRANT DELETE ON dbo.TableName TO User
GRANT UPDATE ON dbo.TableName TO User
GRANT INSERT ON dbo.TableName TO User

如果有需要執行預存程序 (Stored Procedures) 的權限,則針對物件給予 Execute:

GRANT EXECUTE ON OBJECT::dbo.uspSP1 TO User

⭐特殊權限的代理人設定方式 Truncate Permissions Only

Database

建立資料庫需要考慮下列事情:

  • 資料庫命名規則一致性
  • 正式區資料庫不提供 db_owner
  • 開發區資料庫提供 db_owner
  • 應用程式連線帳號僅提供 db_datareader 以及 db_datawriter 權限
  • 資料庫建立後應立即完整備份,作為排程交易紀錄備份的起點
  • 檢查排程備份規則是否包含新資料庫

Objects, Table & Data

  • 資料表設計新增、異動
  • 使用工具同步資料 (Import & Export Wizzard)
  • 建立與修改 View, Stored Procedures, User Functions

SQL Server 批次資料匯入匯出 (BCP, Bulk Insert & OPENROWSET)
SQL Server Table To Table Copy Data 資料表對資料表複製

欄位長度調整

ALTER TABLE Employee ALTER COLUMN EmpName NVARCHAR(400)

Restart Database

ALTER DATABASE DatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE

WAITFOR DELAY '00:00:30'

ALTER DATABASE DatabaseName SET ONLINE

原理是將資料庫離線在重新上線,以清除所有關於該資料庫的連線,可以在搭配排程達到自動化功能。

Database Backup

如果有使用差異備份,須注意臨時的完整備份必須使用 In-Copy-Only 避免破壞差異備份的基準。

Copy Only 的使用時機

Database Restore

  • 注意是否要覆蓋掉原本的資料庫
  • 注意還原的路徑、檔名 (來源及目的)
  • 還原後須注意孤立使用者的問題

SQL Server Restore With Orphaned User Missing User (孤兒 / 孤立使用者)

資料庫正在還原問題

正在還原其實不是問題,而是資料庫的狀態,可以使用 sys.databasesstate_desc 欄位看到。其中正常服役的資料庫為 ONLINE,處於正在還原的資料庫則會視 RESTORING

處於正在還原狀態的資料庫,可以持續接受交易紀錄還原,但無法被讀取。使用下列指令,可以將 RESTORING 的資料庫狀態變更為 ONLINE

RESTORE DATABASE [DatabaseName] WITH RECOVERY

Restore To Availability Groups Database

  • 停止來源端資料庫
USE [master]
ALTER DATABASE [DatabaseSource] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
  • 來源端資料庫完整備份
  • 還原至可用性群組資料庫主節點
  • 解決孤立使用者,將 Login 加入至各可用性群組節點
  • 進行首次完整備份,作為交易紀錄基準