# SQL Server skill
###### tags: `SQL Server` `Microsoft` `SQL` `Data Warehouse` ` Learning`

## SQL Server安裝
| Sn | Type | 技能 |
| -- | -------- | -------- |
| 1 | Stand-alone | SQL Server - Database Engine only |
| 2 | Tools | SQL Server Management Studio|
| 3 | Stand-alone | SQL Server - Database Engine, SSIS, SSRS, SSAS |
| 4 | Stand-alone | SQL Server - R, Python |
| 5 | FCI | SQL Server Failover Cluster Instance |

## SQL Server服務管理
| Sn | Type | 技能 |
| -- | -------- | -------- |
| 1 | Process | Start and stop SQL Server and SQL Server Agent service |
| 2 | Protocol | Enable or Disable TCP\IP |
| 3 | Port | Change listen port from 1433 to 14330 |

## 資料庫管理與開發
| Sn | Type | Ojbect |技能 |
| -- | -------- | -------- | -------- |
| 1 | Developer | Database | Create a TESTDB1 DB and TESTDB2 DB on specific drive with multi-data file using SSMS GUI and T-SQL, |
| 2 | Management | Database | Backup and restore database using SSMS GUI and T-SQL|
| 3 | Management | Database | Restore a sample database(AdventureWorks) using SSMS GUI|
| 4 | Management | Database | Backup database regularly using Maintainance plan |
| 5 | Management | Database | 了解 Database Recovery Models 與 備份類型 |
| 6 | Management | Database | 最佳實務備份設定 using Maintainance plan |
| 7 | Management | Database | 查看所有資料庫清單與空間使用(UI or TSQL) |
| 8 | Management | Database | Increase or reduce database file size( mdf,ndf,ldf) |
| 9 | Management | Database | Move tempdb to different locaiton |
| 10 | Developer | Table | create a table on TESTDB1 |
| 11 | Developer | Table | 了解File Group |
| 12 | Developer | Table | create a table on different File group in TESTDB2 |
| 13 | Management | Table | 如何查看所有資料表(Tableu)清單與空間使用(UI or TSQL)|
| 14 | Developer | View | create a view on TESTDB1 |
| 15 | Management | View | 如何查看所有檢視(view)清單(UI or TSQL)|
| 16 | Developer | Procedure | Create a stored procedure on TESTDB1 |
| 17 | Developer | Procedure | create a stored procedure with parameter TESTDB1|
| 18 | Management | Procedure | 如何查看所有預存程序(stored procedure)清單(UI or TSQL)|
| 19 | security | Login | 了解Logins and Roles|
| 20 | security | Login | create a login1 資料庫最大權限 on TESTDB1 |
| 21 | security | Login | create a login3 只能SELECT一個table或view on TESTDB1 |
| 22 | security | Login | create a login4 只能執行一個SP on TESTDB1 |
| 23 | Management | Login | 如何查看所有登入帳號(login)清單(UI or TSQL) |
| 24 | Management | Login | 了解 Login and User and Permission UI/DMV |
| 25 | Developer | Job | Create a job for run a stored procedure regularly |
| 26 | Developer | Job | 如何查看所有作業(Job)清單(UI or TSQL) |
| 27 | Management | Job | Monitor Job status (Job activity, job history or TSQL) |
| 28 | Management | Job | 了解與設定Database Mail、SQL Agent Alert system、操作員 Operator 與 警示 Alert|
| 29 | Management | Job | configiure email notification when job finish on Job1 |
| 30 | Management | Job | how to start/stop/disable/enable a Job with SSMS GUI or TSQL |
| 31 | Data | SSIS | Import a csv/xlsx to Table, Export table to csv using Import and Export Wizard |
| 32 | Data | Job/SSIS | Schedule and execute a SSIS Package to import csv to Table |
| 33 | Data | Linked Server | Linked Server 用途、建議、與設定方法 |
| 34 | Data | Linked Server | create a linked server to another SQL Server(sqlserver2) and query data on SQL2 from Linked server |
| 35 | Data | Linked Server | create a linked server to another SQL Server(sqlserver2) and query data on SQL2 from Linked server |
| 36 | Developer | Procedure | how to send a mail(Database Mail) with TSQL |

## Monitor and Troubleshooting
| Sn | name |
| -------- | -------- |
| 1 | 無法連線與基本連線問題疑難排解 Service/Port/netstat/pid/activity monitor/sp_who/sp_who2/DMV/kill/rollback/dbcc checkdb |
| 2 | 如何查詢當下用戶端連線狀態(user session) with SSMS GUI/TSQL |
| 3 | 如何使用Standard dashboard |
| 4 | 如何查詢SQL Server ERRORLOG |
| 5 | 如何設置Data Collection |
| 6 | SQL Server Trace |
| 7 | SQL Server Extended Event |

## SQL Server觀念
| Sn | name |
| -------- | -------- |
| 1 | 能夠說明資料庫的2種實體檔案的類型與作用 |
| 2 | 能夠說明資料庫復原模型的差異 |
| 3 | 能夠說明資料庫相容性層級用途 |

## Tools
| Sn | name |
| -------- | -------- |
| 1 | SQL Server Profiler |
| 2 | Database Engine Tuning Advisor |
| 3 | SQL Server Migration Assistant (SSMA) |
| 4 | SQL Server Upgrade Advisor and Data Migration Assistant (DMA) |

## 商務持續性與資料庫復原(高可用性)設定
| Sn | Type | 技能 |
| -- | -------- | -------- |
| 1 | availability group | 教學課程：在 Azure 虛擬機器上的 SQL Server 上建立可用性群組 https://docs.microsoft.com/zh-tw/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-prerequisites-tutorial |
| 2 | FCI | AlwaysOn 容錯移轉叢集執行個體 (SQL Server) https://docs.microsoft.com/zh-tw/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15 PS.SQL Server安裝的第5項 |
| 3 | Database Mirroring | 資料庫鏡像 (SQL Server) https://docs.microsoft.com/zh-tw/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-ver15 |
| 4 | Log Shipping | 關於記錄傳送 (SQL Server) https://docs.microsoft.com/zh-tw/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-ver15 |
| 5 | Replication | SQL Server 複寫 https://docs.microsoft.com/zh-tw/sql/relational-databases/replication/sql-server-replication?view=sql-server-ver15 |

## 其他
SQL Server Reporting service install and configure and design a report using report builder and publish to Report server (60分鐘)

Performance analysis: PSSDIAG and sqlnexus (cpu, memory bottleneck)

Other Features
1.Audit
2.Policy
3.Data Collection
4.Polybase

Advanced service
1.SSIS developer
2.SSRS
3.SSAS
4.R
5.Python

Performance
1.Index (Row-base stored Index, Columnstored index) 建立移除與分析，儲存大小
2.Performance counter

資料倉儲與整合初階開發 SSIS
| Sn | name |
| -------- | -------- |
| 1 | Foreach Loop Container |
| 2 | File System Task |
| 3 | Execute SQL Task |
| 4 | Data Flow Task - Flat File Source |
| 5 | Data Flow Task - Derived Column |
| 6 | Data Flow Task - OLE DB Source |
| 7 | Data Flow Task - OLE DB Destination |
| 8 | Data Flow Task - Data Conversion |
| 9 | Precedence Constraint Editor |
| 10 | Connection Managers |
| 11 | Expression |
| 12 | Error Handle |
| 13 | 版控 |
| 14 | Package Configurations |

SQL Server開發人員(基礎)
| Sn | Type | Ojbect |技能 |
| -- | -------- | -------- | -------- |
| 1 | Management | Database | restore a sample database AdventureWorks using SSMS GUI|
| 2 | Management | Database | create a database TESTDB1 using SSMS GUI and T-SQL |
| 3 | Management | Database | create a database TESTDB2 on specific drive with multi-data file using SSMS GUI and T-SQL |
| 4 | Management | Database | Backup and restore database with SSMS GUI and T-SQL |
| 5 | Developer | Table | create a table on TESTDB1 |
| 6 | Developer | Table | create a table on different File group in TESTDB2 |
| 7 | Developer | View | create a view on TESTDB1 |
| 8 | Developer | Procedure | Create a stored procedure on TESTDB2 |
| 9 | Developer | Procedure | create a stored procedure with parameter TESTDB2|
| 10 | security | Login | create a login1 資料庫最大權限 on TESTDB1 |
| 11 | security | Login | create a login3 只能SELECT一個table或view on TESTDB2 |
| 12 | security | Login | create a login4 只能執行一個SP on TESTDB2 |
| 13 | Developer | Job | create a Job1, execute a sql script for insert data from table1 to table2, and configure a schedule run on every day |
| 14 | Management | Job | how to start/stop/disable/enable a Job with SSMS GUI or TSQL |
| 15 | Management | Job | how to how to view job history with SSMS GUI or TSQL|
| 16 | Data | SSIS | Import a csv to Table, Export table to csv using Import and Export Wizard |
| 17 | Data | Job/SSIS | Schedule and execute a SSIS Package to import csv to Table |