SQL Server Express

 SQL Server Express

Scale limits

Feature	Express with Advanced Services	Express
Maximum compute capacity used by a single instance - SQL Server Database Engine1	Limited to lesser of 1 socket or 4 cores	Limited to lesser of 1 socket or 4 cores
Maximum compute capacity used by a single instance - Analysis Services or Reporting Services	Limited to lesser of 1 socket or 4 cores	Limited to lesser of 1 socket or 4 cores
Maximum memory for buffer pool per instance of SQL Server Database Engine	1410 MB	1410 MB
Maximum memory for Columnstore segment cache per instance of SQL Server Database Engine	352 MB	352 MB
Maximum memory-optimized data size per database in SQL Server Database Engine	352 MB	352 MB
Maximum memory utilized per instance of Analysis Services	N/A	N/A
Maximum memory utilized per instance of Reporting Services	4 GB	N/A
Maximum relational database size	10 GB	10 GB

database size 10GB (the limit is on the data file, it doesn't include log files)

Editions and supported features of SQL Server 2019 (15.x)

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15

Microsoft® SQL Server® 2019 Express

https://www.microsoft.com/en-us/download/details.aspx?id=101064

SQL Server 下載| Microsoft

https://www.microsoft.com/zh-tw/sql-server/sql-server-downloads

SQL Server tempdb

 What is the TempDB database used for in SQL Server?

• User Objects
• User-defined tables and indexes
• System tables and indexes
• Global temporary tables and indexes
• Local temporary tables and indexes
• Table variables
• Tables returned in table-valued functions
• Internal Objects
• Work tables for CURSOR or SPOOL operations and temporary large object (LOB) storage.
• Work files for HASH JOIN or HASH aggregate operations.
• Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.
• Version Stores
• Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels. (Snapshot Isolation and Read-Committed Snapshot Isolation (If long running transaction exist cause tempdb growing up))
• Row versions that are generated by data modification transactions for features such as: Online Index Operations, Multiple Active Result Sets (MARS), and AFTER triggers.

tempdb檔案設定最佳化：

(1) 為tempdb交易紀錄檔設定足夠大的大小，以避免自動成長。

(2) 為每一個 CPU 建置一個 Tempdb 的資料檔案，或是以 CPU 個數的一半建置 (除以 2 的倍數)。

(3) 每個資料檔案大小必須相同。

(4) 每個資料檔案必須預留足夠的交易量空間。例如觀察平時 Tempdb 使用量約 3GB的話，建議設定總共 4GB 空間。(也就是所有tempdb資料檔加起來4GB，例如建立4個1GB資料檔)。

(5) 符合上述的配置條件，每個 CPU 可以獨立取用對應的 Tempdb 資料檔案，可以增進效能及併發性。

Optimizing tempdb Performance

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175527(v=sql.105)?redirectedfrom=MSDN

Recommendations to reduce allocation contention in SQL Server tempdb database

https://docs.microsoft.com/en-us/troubleshoot/sql/performance/recommendations-reduce-allocation-contention

Troubleshooting Insufficient Disk Space in tempdb

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms176029(v=sql.105)

Capacity Planning for tempdb

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms345368(v=sql.105)

SQL Server Disk Architecture (SQL Server磁碟架構規劃)

SQL Server Disk Architecture

SQL Server磁碟架構規劃

SQL Server Linked server to Oracle

 

1.在SQL Server主機安裝Oracle Client，建議先裝Oracle client 32bit，在安裝Oracle client 64bit

https://bradctchen.blogspot.com/2019/07/oracle-client-183-installation-oracle.html

2.確認環境變數Path，Oracle client 64bit路徑位置在32bit路徑位置上面

若修改位置，建議重新啟動SQL Server服務或重新開機 (非必要)

3.使用SSMS修改 OraOLEDB.Oracle provider屬性，將Allow in-process勾選

4.建立Linked server時，資料來源輸入c:\app\twpspdbadmin\product\11.2.0\client_1\network\admin\tnsnames.ora裡面定義的 oracle連線別名，例如以下的mydb

mydb =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

資料來源輸入

5.建立Linked server時，伺服器選項，將 RPC out(RPC輸出)改為true

Azure Synapse Analytics 2020

Azure Access Reviews (存取權審核)

Azure AD

Azure AD (Azure Active Directory)

功能:

Feature	Free	Office 365 Apps (舊稱Basic)	P1	P2
Self-Service Password change	V
Self-Service Password reset		V
MFA		V
Self-Service Password reset and Password writeback			V
Password writeback			V
Security Reports			V
Conditional Access policy(General)			V
Conditional Access policy(Risk-Based)				V
Identity protection(Risk-Based Management)				V
Access Review (Privileged Identity Management (PIM))				V

Azure provides a number of options for Domains

1.Azure AD (and B2B, B2C)

• B2B 用在有其他的合作夥伴，部分用戶存取我們的Azure資源，為這些用戶設定quest account對應這些用戶的email位置
• B2C 用於社群帳號名或社群email帳號，對應Azure AD帳號

2.Hybrid ADDS and Azure AD (混合式身分識別)

 ADDS(on-promise AD)同步Account, group, contact到Azure AD
 
Azure AD 達到混合式身分識別，透過Azure AD Connect設定有三種作法：

1. 密碼雜湊同步處理 Password Hash Synchronization(PHS) : Default選取,可整合無縫式 SSO
2. 傳遞式驗證 Pass-through Authentication(PTA) : 用戶密碼驗證的請不會存在Azure AD，用戶密碼驗證請求會傳送到ADDS驗證(可整合無縫式 SSO)
3. 同盟 Active Directory Federation Services(AD FS) : 同盟有自己的SSO，可整合certificate, smard card, 內部MFA, 3rd 同盟 (但不能整合無縫式 SSO)

無縫式單一登入(Seamless Single Sign-On):

如果需要達成無縫式單一登入(Seamless Single Sign-On)

已登入ADDS(on-promise AD)去存取Azure資源時，不會在跳出驗證視窗

1.需要Azure AD Connect 設定  Password Hash Synchronization(PHS)或Pass-through Authentication(PTA)

2.Seamless SSO is not applicable to Active Directory Federation Services (ADFS)AD同盟

3.Azure AD Domain Services 

   Azure ADDS是PaaS服務
   (1)提供如同on-promise AD的能力(domain-join, group policy, LDAP, Kerberos authentication功能與on-promise AD相容，但不能同盟，且是獨立Domain，不能讓on-promise AD延伸到Azure ADDS)
 Azure Active Directory Domain Services (Azure AD DS) provides managed domain services such as domain join, group policy, LDAP, Kerberos/NTLM authentication that is fully compatible with Windows Server Active Directory

  若要跟Azure AD整合，則是將Azure AD帳號同步到Azure Domain Services


什麼是 Azure Active Directory 的混合式身分識別？
https://docs.microsoft.com/zh-tw/azure/active-directory/hybrid/whatis-hybrid-identity