Distributed query vs OPENQUERY

Distributed query vs OPENQUERY
from my MSDN blog - September 19, 2016

Distributed query (Four-part name) and OPENQUERY

	OpenQuery	Distributed Query
速度	一般來說 OPENQUERY較快一點	也很快
Query optimizer	遠端產生執行計畫	本地產生執行計畫 產生local query與remote query
連線數量	只會產生1條連線到遠端取回資料	會產生2條連線 第1條連線先取回統計資訊 第2條連線再取回資料
優點		可以在一個Query裡面JOIN多個不同SQL Server的Table
可能出現issue		如果太多連線使用Distributed Query，則會造成大量等待SOSHOST_MUTEX wait
缺點		雖然有WHERE條件，但SQL Server可能會送出SELECT * FROM the remote table，然後等資料回到本地端才進行filter
權限	只需要設定SELECT資料表的權限	為了產生最佳執行計畫，remote login account必須有以下權限，才能取得完整的統計資訊，若沒有以下權限，則查詢效能則會比較差 To create the best query plans the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server. https://msdn.microsoft.com/en-us/library/ms175537.aspx 但SQL 2012開始則不需要此權限

Best Performer: Distributed query (Four-part) or OPENQUERY when executing linked server queries in SQL Server
https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/
Security for Linked Servers
http://msdn.microsoft.com/en-us/library/ms175537.aspx
Guidelines for Using Distributed Queries
https://msdn.microsoft.com/en-us/library/ms175129.aspx
OPENQUERY (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms188427.aspx
Optimizing Distributed Queries
https://technet.microsoft.com/en-us/library/ms180972(v=sql.105).aspx
Improving the Performance of Distributed Queries
http://sqlmag.com/database-performance-tuning/improving-performance-distributed-queries

Asus external Blu-Ray/DVD external drive not working in Windows 10

Solution from
SOLVED: Asus external Blu-Ray/DVD external drive not working in Windows 10
https://mightbeuseful.wordpress.com/2015/12/05/solved-asus-external-blu-raydvd-external-drive-not-working-in-windows-10/


1.Plug external drive into USB Port.
2.In the Device Manager, find Initio Default Controller and disable it under Intio Combo Device Class
3.Now right-click on it again and click ‘Properties’, in "Driver" tab,  Click on "Uninstall"
4.Unplug the external drive from the USB port.
5.Shut down your computer (be sure to shut it down fully, don’t just restart it).
6.plug in the external drive to the USB ports.
7.Power-on the computer

SQL Server Performance study resources (SQL Server效能-學習資源)

SQL Server Performance study resources
SQL Server效能-學習資源
from my MSDN blog - September 7, 2016

查詢處理架構:

Query Processing Architecture

https://technet.microsoft.com/en-us/library/cc280362(v=sql.105).aspx

效能監控與分析:

Monitor and Tune for Performance

https://msdn.microsoft.com/en-us/library/ms189081.aspx

opic	Task
Monitor SQL Server Components	Required steps to monitor any SQL Server component.
Performance Monitoring and Tuning Tools	Lists the monitoring and tuning tools available with SQL Server.
Establish a Performance Baseline	How to establish a performance baseline.
Isolate Performance Problems	Isolate database performance problems.
Identify Bottlenecks	Monitor and track server performance to identify bottlenecks. Monitor CPU Usage Monitor Disk Usage Monitor Memory Usage
Server Performance and Activity Monitoring	Use SQL Server and Windows performance and activity monitoring tools. Open Activity Monitor (SQL Server Management Studio) 活動監視器 https://msdn.microsoft.com/en-us/library/ms175518.aspx Microsoft® SQL Server® 2012 Performance Dashboard Reports https://www.microsoft.com/en-us/download/details.aspx?id=29063
Display and Save Execution Plans	Display and save execution plans to a file in XML format.

Query Tuning(語法優化，前面2篇很重要)

Chapter 14 — Improving SQL Server Performance

https://msdn.microsoft.com/en-us/library/ff647793.aspx

SQL Server

Optimizing SQL Server Query Performance

Maciej Pilecki

https://technet.microsoft.com/en-us/library/2007.11.sqlquery.aspx

Query Tuning

https://technet.microsoft.com/en-us/library/ms176005(v=sql.105).aspx

Understanding how SQL Server executes a query

August 1st, 2013

http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/

目前正在執行的Query

Determine the Currently Executing Statement in a Long Running SQL Stored Procedure

https://blogs.msdn.microsoft.com/taylaf/2010/01/25/determine-the-currently-executing-statement-in-a-long-running-sql-stored-procedure/

SELECT  requests.session_id,

         requests.status,

         requests.command,

         requests.statement_start_offset,

         requests.statement_end_offset,

         requests.total_elapsed_time,

         details.text

 FROM    sys.dm_exec_requests requests

 CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) details

 WHERE   requests.session_id > 50

 ORDER BY total_elapsed_time DESC

Status:

sys.dm_exec_sessions (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms176013.aspx

Status of the session. Possible values:

 Running - Currently running one or more requests

 Sleeping - Currently running no requests

 Dormant – Session has been reset because of connection pooling and is now in prelogin state.

 Preconnect - Session is in the Resource Governor classifier.

Is not nullable.

sys.dm_exec_requests (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms177648.aspx

Status of the request. This can be one of the following:

Background

Running

Runnable

Sleeping

Suspended

Is not nullable.

Different Status of a SPID in SQL Server and What do they mean

https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/08/different-status-of-a-spid-in-sql-server-and-what-do-they-mean/

How It Works: What is a Sleeping / Awaiting Command Session

https://blogs.msdn.microsoft.com/psssql/2008/04/21/how-it-works-what-is-a-sleeping-awaiting-command-session/

Wait Type:

Wait statistics, or please tell me where it hurts

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Doctor, this SQL Server appears to be sick….

https://blogs.msdn.microsoft.com/psssql/2009/11/24/doctor-this-sql-server-appears-to-be-sick/

Case Study: Part 1: CXPACKET Wait Stats & ‘max degree of parallelism’ Option: Introduction to Using Wait Stats to Identify & Remediate Query Parallelism Bottlenecks

https://blogs.msdn.microsoft.com/jimmymay/2008/11/28/case-study-part-1-cxpacket-wait-stats-max-degree-of-parallelism-option-introduction-to-using-wait-stats-to-identify-remediate-query-parallelism-bottlenecks/

Troubleshooting ASYNC_NETWORK_IO, NETWORKIO

https://blogs.msdn.microsoft.com/joesack/2009/01/08/troubleshooting-async_network_io-networkio/

What is WRITELOG waittype and how to troubleshoot and fix this wait in SQL Server

https://blogs.msdn.microsoft.com/sqlsakthi/2011/04/16/what-is-writelog-waittype-and-how-to-troubleshoot-and-fix-this-wait-in-sql-server/

What’s that HTDELETE wait type?

https://blogs.msdn.microsoft.com/ialonso/2014/07/23/whats-that-htdelete-wait-type/

Meditation on SQL Trace performance Impact and Wait types

https://blogs.msdn.microsoft.com/sqlmeditation/2012/12/12/meditation-on-sql-trace-performance-impact-and-wait-types/

REPL_SCHEMA_ACCESS wait type

https://blogs.msdn.microsoft.com/psssql/2014/06/03/repl_schema_access-wait-type/

sys.dm_os_wait_stats (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms179984.aspx

The SQL Server Wait Type Repository…

https://blogs.msdn.microsoft.com/psssql/2009/11/02/the-sql-server-wait-type-repository/

Best Performer: Distributed query (Four-part) or OPENQUERY when executing linked server queries in SQL Server

https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/

issues where too many connections running distributed queries end up in a SOSHOST_MUTEX wait while SQL Server is collecting data distribution statistics from the remote server.

透過SQL Profiler trace分析:

Troubleshooting and Analysis with Traces

https://msdn.microsoft.com/en-us/library/cc293616.aspx

Troubleshooting and Analysis with Traces.pdf

書名：SQL Server效能調校

書號：PG21414  作者：陳祥輝、陳臆如、黃浩 ISBN： 978-986-201-902-3

定價：NT$450元  印刷：單色 頁數：272頁

書籍規格：17*23 上市日：2014/3/30 譯者：(無)

http://www.drmaster.com.tw/Bookinfo.asp?BookID=PG21414

SQL Server Performance Tuning 效能調校

http://www.delightpress.com.tw/book.aspx?book_id=SKUD00026

分類｜ 資料庫

作者｜ 胡百敬‧姚巧玫‧周妙謙  日盛金控  劉承修

出版日期｜ 2014-06-06

SQL Server Performance Dashboard Reports

SQL Server Performance Dashboard Reports 效能儀表板報表

from my MSDN blog - September 6, 2016

Performance Dashboard

https://learn.microsoft.com/en-us/sql/relational-databases/performance/performance-dashboard?view=sql-server-ver16

SQL Server Management Studio version 17.2 and later includes the Performance Dashboard

新版SSMS內建Performance Dashboard Report，所以無需執行以下安裝步驟

請注意Expensive queries的計算TSQL如下，與直接查詢sys.dm_exec_query_stats的結果會不同

exec sp_executesql @stmt=N'SELECT 
	text as query_text, 
	master.dbo.fn_varbintohexstr(query_hash) as  query_hash, 
	master.dbo.fn_varbintohexstr(sql_handle) as sql_handle,
	statement_start_offset,
	statement_end_offset,
	querycount, 
	queryplanhashcount, 
	execution_count,
	total_elapsed_time,
	min_elapsed_time, 
	max_elapsed_time,
	average_elapsed_time,
	total_CPU_time, 
	min_CPU_time, 
	max_CPU_time, 
	average_CPU_time,
	total_logical_reads, 
	min_logical_reads, 
	max_logical_reads, 
	average_logical_reads,
	total_physical_reads, 
	min_physical_reads, 
	max_physical_reads, 
	average_physical_reads, 
	total_logical_writes, 
	min_logical_writes, 
	max_logical_writes, 
	average_logical_writes,
	total_clr_time, 
	min_clr_time, 
	max_clr_time, 
	average_clr_time,
	max_plan_generation_num,
	earliest_creation_time,
	query_rank,
	charted_value,
	master.dbo.fn_varbintohexstr(plan_handle) as plan_handle
	FROM   (SELECT s.*, 
				   Row_number() OVER(ORDER BY charted_value DESC) AS query_rank 
			FROM   (SELECT CASE @OrderBy_Criteria 
							 WHEN ''Logical Reads'' THEN SUM(total_logical_reads) 
							 WHEN ''Physical Reads'' THEN SUM(total_physical_reads) 
							 WHEN ''Logical Writes'' THEN SUM(total_logical_writes) 
							 WHEN ''CPU'' THEN SUM(total_worker_time) / 1000 
							 WHEN ''Duration'' THEN SUM(total_elapsed_time) / 1000 
							 WHEN ''CLR Time'' THEN SUM(total_clr_time) / 1000 
						   END AS charted_value, 
					   query_hash, 
					   MAX(sql_handle_1)				sql_handle, 
					   MAX(statement_start_offset_1)    statement_start_offset, 
					   MAX(statement_end_offset_1)      statement_end_offset, 
					   COUNT(*)							querycount, 
					   COUNT (DISTINCT query_plan_hash) queryplanhashcount, 
					   MAX(plan_handle_1)			plan_handle,
					   MIN(creation_time)				earliest_creation_time,
                 
					   SUM(execution_count)             execution_count, 
					   SUM(total_elapsed_time)          total_elapsed_time, 
					   min(min_elapsed_time)            min_elapsed_time, 
					   max(max_elapsed_time)            max_elapsed_time,
					   SUM(total_elapsed_time)/SUM(execution_count) average_elapsed_time, 
                       
					   SUM(total_worker_time)           total_CPU_time, 
					   min(min_worker_time)             min_CPU_time, 
					   max(max_worker_time)            max_CPU_time, 
					   SUM(total_worker_time)/SUM(execution_count) average_CPU_time, 

                       SUM(total_logical_reads)         total_logical_reads, 
                       min(min_logical_reads)           min_logical_reads, 
                       max(max_logical_reads)           max_logical_reads, 
                       SUM(total_logical_reads)/SUM(execution_count) average_logical_reads, 
                       
                       SUM(total_physical_reads)        total_physical_reads, 
                       min(min_physical_reads)         min_physical_reads, 
                       max(max_physical_reads)          max_physical_reads, 
                       SUM(total_physical_reads)/SUM(execution_count) average_physical_reads, 
                       
                       SUM(total_logical_writes)        total_logical_writes, 
                 
                       min(min_logical_writes)          min_logical_writes, 
                       max(max_logical_writes)          max_logical_writes, 
                       SUM(total_logical_writes)/SUM(execution_count) average_logical_writes, 
                       
                       SUM(total_clr_time)              total_clr_time, 
                       SUM(total_clr_time)/SUM(execution_count) average_clr_time, 
                       min(min_clr_time)                min_clr_time, 
                       max(max_clr_time)                max_clr_time, 
                       
                       MAX(plan_generation_num)         max_plan_generation_num
                FROM (
					-- Implement my own FIRST aggregate to get consistent values for sql_handle, start/end offsets of 
					-- an arbitrary first row for a given query_hash
                    SELECT 
						CASE when t.rownum = 1 THEN plan_handle ELSE NULL END as plan_handle_1,
						CASE WHEN t.rownum = 1 THEN sql_handle ELSE NULL END AS sql_handle_1, 
						CASE WHEN t.rownum = 1 THEN statement_start_offset ELSE NULL END AS statement_start_offset_1, 
						CASE WHEN t.rownum = 1 THEN statement_end_offset ELSE NULL END AS statement_end_offset_1, 
						* 
					FROM   (SELECT row_number() OVER (PARTITION BY query_hash ORDER BY sql_handle) AS rownum, * 
							FROM   sys.dm_exec_query_stats) AS t) AS t2 
					GROUP  BY query_hash
               ) AS s 
			WHERE  s.charted_value > 0
        ) AS qs
         
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
	where query_rank <= 20
	order by charted_value desc

',@params=N'@OrderBy_Criteria NVarChar(max)',@OrderBy_Criteria=N'CPU'

============================

SQL Server Management Studio version 17.2以下的版本才需要執行以下安裝步驟

Microsoft® SQL Server® 2012 Performance Dashboard Reports
https://www.microsoft.com/en-us/download/details.aspx?id=29063

Supported Operating System
Windows 7, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2

Works with the following SQL Server versions: SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014
1.安裝

2.設定

Getting Started With the Performance Dashboard Reports
1. In each SQL Server instance, run the script %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard\setup.sql

2. In the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports.  Browse to the installation directory and open the performance_dashboard_main.rdl file.

Default Path: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl

Default Path:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\performance_dashboard_main.rdl

3.使用

安裝完成之後，在上一個步驟按下run，就會開啟Performance Dashboard Reports

Configure Performance Log collection for SQL Server Performance Baseline 設定收集效能計數器記錄檔，建立SQL Server 效能基準線

Configure Performance Log collection for SQL Server Performance Baseline
設定收集效能計數器記錄檔，建立SQL Server 效能基準線
from my MSDN blog - September 5, 2016

1.啟動效能監視器

Perfmon

2.新增收集器
Data Collector Sets>User Defined>New>Data Collector Set

3.輸入名稱 (例如 SQLPerfLogBaseline)並選擇 Create from a template (Recommended)

4.範本Template，選擇System Performance

5.指定blg檔存放的根目錄

6.選擇Open Properties for this data collector set

7.按下Finish之後，則出現此視窗

8.在schedule頁籤，新增一個排程，例如每天上午08:00啟動 或 每天 上午12:00啟動

9.在Stop Condition頁籤，設定停止條件，例如勾選Overall Duration 16 hours，在Limit限制的區塊，勾選 Restart the data collector set at limits.，勾選 Maximum Size:設定300MB

可以考慮改用Task Schedule來取代此設定，請參考這篇

Taming Perfmon: Data Collector Sets

也就是在Stop Condition頁籤都不要設定，改去Task Scheduler

Task Scheduler Library > Microsoft > Windows > PLA 找到你的Performance log排程，將If the task is already running, then the following rule applies: 設定為Stop the existing instance就可以自動每天重啟收集。

10.按下OK之後，回到主視窗，若不需要可將NT Kernel trace刪除

11.在Performance Counter按右鍵選擇 Properties

12.修改預設選取的Performance counters，只留下需要的Performance Counter

建議最少需要收集以下效能計數器

\Processor(*)\% Processor Time

\Processor(*)\% User Time

\System\Processor Queue Length

\Memory\Available Mbytes 

\Memory\Pages/sec

\Memory\Pages Input/sec

\Memory\Page Reads/sec

\Process(*)\% Processor Time

\Process(sqlservr)\Private Bytes 

\Process(sqlservr)\Working Set 

\Process(sqlservr)\Working Set Peak 

\Process(sqlservr)\Virtual Bytes Peak 

\Process(sqlservr)\Virtual Bytes 

\SQLServer:Buffer Manager\Buffer cache hit ratio

\SQLServer:Buffer Manager\Page life expectancy

\SQLServer:Buffer Manager\Total pages 

\SQLServer:Buffer Manager\Target pages 

\SQLServer:Buffer Manager\Database pages 

\SQLServer:Buffer Manager\Reserved pages 

\SQLServer:Buffer Manager\Stolen pages 

\SQLServer:Memory Manager\Target Server Memory (KB) 

\SQLServer:Memory Manager\Total Server Memory (KB) 

\SQLServer:General Statistics\Logins/sec

\SQLServer:General Statistics\User Connections

\SQLServer:General Statistics\Transactions

\SQLServer:Databases(*)\Percent Log Used

\SQLServer:Databases(*)\Transactions/sec

\SQLServer:Access Methods\Full Scans/sec

\SQLServer:Access Methods\Index Searches/sec

\SQLServer:Access Methods\Page Splits/sec

\SQLServer:SQL Statistics\Batch Requests/sec

\SQLServer:SQL Statistics\SQL Compilations/sec

\SQLServer:SQL Statistics\SQL Re-Compilations/sec

\SQLServer:Plan Cache(*)\Cache Hit Ratio

\SQLServer:Plan Cache(*)\Cache Pages

\SQLServer:Plan Cache(*)\Cache Object Counts

\SQLServer:Plan Cache(*)\Cache Objects in use

\SQLServer:Transactions\Free Space in tempdb (KB)

\LogicalDisk(*)\% Disk Time

\LogicalDisk(*)\Avg. Disk Queue Length

\LogicalDisk(*)\Avg. Disk sec/Transfer

\LogicalDisk(*)\Avg. Disk sec/Read

\LogicalDisk(*)\Avg. Disk sec/Write

\LogicalDisk(*)\% Idle Time

\PhysicalDisk(*)\Current Disk Queue Length

\PhysicalDisk(*)\% Disk Time

\PhysicalDisk(*)\Avg. Disk Queue Length

\PhysicalDisk(*)\Avg. Disk sec/Transfer

\PhysicalDisk(*)\Avg. Disk sec/Read

\PhysicalDisk(*)\Avg. Disk sec/Write

\PhysicalDisk(*)\% Idle Time

\Network Interface(*)\Bytes Total/sec

\Network Interface(*)\Packets Received/sec

\Network Interface(*)\Packets Sent/sec

\Network Interface(*)\Bytes Received/sec

\Network Interface(*)\Bytes Sent/sec

\Network Interface(*)\Current Bandwidth

Tempdb相關

\SQLServer:Databases(tempdb)\Data File(s) Size (KB)

\SQLServer:Databases(tempdb)\Log File(s) Size (KB)

\SQLServer:Databases(tempdb)\Log File(s) Used Size (KB)

\SQLServer:Transactions\Free Space in tempdb (KB)

13.修改與確認blg黨與報表檔保留期間
在SQLPerfLogBaseline右鍵選擇Data Manager

14.修改限制設定

15.在Action頁籤，調整成自己要想要保留的規則，就完成所有設定

PS.這裡第一個設定預設為1 Day(s)就會把blg包成cab檔並刪除blg檔，所以若要用Performance Monitor來看Reports時就會看不到Report，可以考慮改成2 Day(s)，這樣就最少有一天的blg檔可以直接透過Reports節點來檢視。

16.可將目前的設定另存成XML的範本檔template

17.時間到了就會自動啟動，也可以手動按右鍵啟動，就會開始收集。

Reference:
Creating Data Collector Sets
https://technet.microsoft.com/en-us/library/cc749337(v=ws.11).aspx

Use SQL Server Objects
https://technet.microsoft.com/en-us/library/ms190382.aspx

SQL Server Rowset Trace

SQL Server Rowset Trace
from my MSDN blog - September 3, 2016

SQL Server Trace分為2種
1.Server-Side Trace with file provider(File Provider)
2.Rowset Trace(Rowset Provider)

Server-Side Trace with file provider(File Provider)

如果是Server-Side Trace是使用file provider

select * from sys.traces where is_rowset = 0 and path is not NULL
PS. The file provider is designed with a guarantee that no event data will be lost.

Rowset Trace(Rowset Provider)

SQL Server Profiler連線到SQL Server啟動的就是Rowset Trace (使用rowset provider)

select * from sys.traces where is_rowset = 1 and path is NULL
PS. The rowset provider, on the other hand, is not designed to make any data loss guarantees.
If data is not being consumed quickly enough and its internal buffers fill, it waits up to 20 seconds before it begins jettisoning events in order to free buffers to get things moving. The SQL Server Profiler client tool will send a special error message if events are getting dropped, but you can also find out if you’re headed in that direction by monitoring SQL Server’s TRACEWRITE wait type, which is incremented as threads are waiting for buffers to free up.
SQL Server Profiler pulls these events from the rowset provider via a call to sp_trace_getdata and performs a “pivot” to produce the human-readable output we’re used to seeing. This is yet another reason that the rowset provider can be less efficient than the file provider—sending so many rows can produce a huge amount of network traffic.
If you do require rowset provider-like behavior for your monitoring needs, you luckily will not need to figure out how to manipulate this data. SQL Server 2005 ships with a series of managed classes in the Microsoft.SqlServer.Management.Trace namespace, designed to help with setting up and consuming rowset traces. The use of these classes is beyond the scope of this chapter, but they are well documented in the SQL Server TechCenter on TechNet, and readers should have no trouble figuring out how to exploit what they offer.

Reference:
Server-Side Tracing and Collection
https://msdn.microsoft.com/en-us/library/cc293613.aspx
SQL Trace Architecture and Terminology
https://msdn.microsoft.com/en-us/library/cc293610.aspx
SQL SERVER – Server Side and Client Side Trace
http://blog.sqlauthority.com/2015/12/15/sql-server-server-side-and-client-side-trace/

Visual Studio(SSDT, BIDS) for SQL Server

Visual Studio(SSDT, BIDS) for SQL Server

開發Integration Service, Reporting Service, Analysis Service

例如開發SQL Server 2019 SSIS Package

1.先安裝Visual Studio 2019 Community

2.啟動VS 2019，針對 Analysis Services、Integration Services 或 Reporting Services 專案，請從 Marketplace 或 [工具] > [延伸模組與更新] 安裝適當的延伸模組，也就是搜尋Integration Service，然後按下安裝。

或

直接下載安裝Microsoft.DataTools.IntegrationServices.exe

https://ssis.gallerycdn.vsassets.io/extensions/ssis/sqlserverintegrationservicesprojects/3.6/1587347563940/Microsoft.DataTools.IntegrationServices.exe

或

從這裡下載安裝SQL Server Integration Services Projects

SQL Server Integration Services Projects
https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects

This project may be used for building high performance data integration and workflow solutions, including extraction, transformation, and loading (ETL) operations for data warehousing. Currently the supported target server version starts from SQL Server 2012 up to 2019.

Download SQL Server Data Tools (SSDT) for Visual Studio
https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15

Supported SQL versions in Visual Studio 2022

Project Templates	SQL Platforms Supported
Relational databases	SQL Server 2016 (13.x) - SQL Server 2022 (16.x) Azure SQL Database, Azure SQL Managed Instance Azure Synapse Analytics (dedicated pools only)
Analysis Services models Reporting Services reports	SQL Server 2016 - SQL Server 2022
Integration Services packages	SQL Server 2019 - SQL Server 2022

SSDT for Visual Studio 2019
SSDT for Visual Studio 2017

Supported SQL versions in Visual Studio 2019

Project Templates	SQL Platforms Supported
Relational databases	SQL Server 2012 - SQL Server 2019 Azure SQL Database, Azure SQL Managed Instance Azure Synapse Analytics (dedicated pools only)
Analysis Services models Reporting Services reports	SQL Server 2008 - SQL Server 2019
Integration Services packages	SQL Server 2012 - SQL Server 2022

Previous releases of SQL Server Data Tools (SSDT and SSDT-BI)

https://learn.microsoft.com/en-us/sql/ssdt/previous-releases-of-sql-server-data-tools-ssdt-and-ssdt-bi?view=sql-server-ver15

Supported SQL versions

TABLE 1

Project Templates	SQL Platforms Supported
Relational databases	SQL Server 2005* - SQL Server 2017 (use SSDT 17.x or SSDT for Visual Studio 2017 to connect to SQL Server on Linux) Azure SQL Database Azure SQL Data Warehouse (supports queries only; database projects aren't yet supported) * SQL Server 2005 support is deprecated, move to an officially supported SQL version
Analysis Services models Reporting Services reports	SQL Server 2008 - SQL Server 2017
Integration Services packages	SQL Server 2012 - SQL Server 2019

SSDT: SQL Server Data Tool

BIDS: Business Intelligence Development Studio

SQL Server	Visual Studio Version
SQL Server 2005	透過SQL Server安裝程式勾選安裝 Visual Studio 2005 8.0.50727.42 (RTM.050727-4200)
SQL Server 2008	透過SQL Server安裝程式勾選安裝 Visual Studio 2008 9.0.30729.1 SP Visual Studio 2008含有SP1 Actions that are required before you install SQL Server 2008 on a computer that has Visual Studio 2008 or the prerelease version of SQL Server 2008 installed http://support.microsoft.com/kb/956139/en-us
SQL Server 2008 R2	透過SQL Server安裝程式勾選安裝 Visual Studio 2008 9.0.30729.4462 QFE Visual Studio 2008含有SP1，但是畫面沒有SP字眼 SQL Server Data Tools - 2012 年 12 月版的更新 SQL Server 2008所安裝的Visual Studio 2008含有SP1 http://support.microsoft.com/kb/956139/en-us 如果在SQL 2008 R2安裝(含BIDS)完成之後，再安裝Visual Studio 2008 SP1也是可以安裝成功。 最後安裝的結果如下: Exe (D:\vs90sp1\VC_x86Runtime.exe) succeeded. Log File: dd_VC_x86RuntimeMSI543A.txt Log File: dd_VC_x86RuntimeUI543A.txt Exe (D:\vs90sp1\VC_x64Runtime.exe) succeeded. Log File: dd_VC_x64RuntimeMSI5447.txt Log File: dd_VC_x64RuntimeUI5447.txt D:\vs90sp1\VC_IA64Runtime.exe - Exe installer's log file/hint (%temp%\dd_VC_IA64Runtime*.txt|%temp%\..\dd_VC_IA64Runtime*.txt) does not exist or is invalid. Exe (D:\vs90sp1\VC_IA64Runtime.exe) succeeded. Patch (D:\vs90sp1\VS90sp1-KB945140-X86-ENU.msp) install succeeded on product (Microsoft Visual Studio 2008 Shell (integrated mode) - ENU). Msi Log: Microsoft Visual Studio 2008 SP1_20160902_092951310-Microsoft Visual Studio 2008 Shell (integrated mode) - ENU-MSP0.txt Final Result: Installation completed successfully
SQL Server 2012	Visual Studio 2010 (SQL Server Data Tools) 10.0.40219.1 SP1Rel 含有Visual Studio 2010 SP1 Visual Studio 2010 SSDT的更新 SQL Server Data Tools - 2012 年 12 月版的更新 http://msdn.microsoft.com/zh-tw/data/jj650014
SQL Server 2014	需自行下載 2014-10-27 Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013http://www.microsoft.com/zh-tw/download/details.aspx?id=42313 2014-06-26 Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012http://www.microsoft.com/zh-tw/download/details.aspx?id=36843

Download SQL Server Data Tools (SSDT)
https://msdn.microsoft.com/en-us/library/mt204009.aspx