Azure Virtual Network

Azure Virtual Network

SQL Server Learning Resources (SQL Server 學習資源) 2020

SQL Server Installation安裝

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