SQL Server Tools default Location

SQL Server Tools default Location SQL Server工具預設位置   Tools SQL Server versions Location sqlcmd.exe, bcp.exe SQL 2008 R2 C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ SQL 2012 C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ SQL 2014 C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\ SQL 2016 C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\ Integration Services component dtexec.exe SQL 2008 R2 C:\Program Files\Microsoft SQL Server\100\ DTS\Binn\ C:\Program…

0

How to reset forgotten sa password

How to reset forgotten sa password 如何重設sa密碼  或 忘記sa密碼的情況下加入一個SQL管理員帳戶 如果忘記sa密碼,有2個方法解決 方法一 不需要重啟SQL Server服務,但Login裡面必須要原本就有NT AUTHORITY\SYSTEM帳戶,且此帳戶要有sysadmin Role 1.下載Sysinternals的psexec.exe工具 https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx 2.啟動命令提示字元,用psexec.exe來啟動SSMS,就可以用NT SERVICE\SYSTEM身分Windows驗證登入 PsExec.exe -s -i “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe” SQL Server Managment Studio default locations 方法二 處理過程會有downtime,因為需重新啟動SQL Server服務。 1.停止SQL Server服務 2.啟動第1個命令提示字元,以Single user mode啟動SQL Server Start the SQL Server instance using single user mode (or minimal configuration which…

0

SQL Server Management Studio Keyboard Shortcuts

SQL Server Management Studio Keyboard Shortcuts 常用快速鍵 SQL Server Management Studio Keyboard Shortcuts https://msdn.microsoft.com/en-us/library/ms174205.aspx Action SQL Server 2016 SQL Server 2008 R2 Select text from the cursor to the beginning of the document CTRL+SHIFT+ HOME CTRL+SHIFT+ HOME Select text from the cursor to the end of the document CTRL+SHIFT+END CTRL+SHIFT+END Select text from the cursor…

0

SQL Server Learning Resource

SQL Server 學習資源 中英文官方線上課程 Microsoft Virtual Academy Database Development Course https://mva.microsoft.com/training-topics/database-development#!jobf=Developer&lang=1033 必學的 SQL Server 2014 資料庫管理技巧 https://mva.microsoft.com/zh-tw/training-courses/-sql-server-2014–11125 SQL Server 管理入門 (一):安裝、設定與連線 https://mva.microsoft.com/zh-tw/training-courses/sql-server–11718?l=Pym6n7iEB_9804984382 SQLPASS http://www.sqlpass.org/ 線上研討會 研討會錄影檔會放到youtube High Availabilty Disaster Recovery Virtual Chapter https://www.youtube.com/channel/UCVXM_bmBuTEln3K4GHeuBAQ SQLPASS Taiwan https://www.facebook.com/groups/sqlpasstaiwan/ 每個月舉辦研討會 Super SQL Server https://www.facebook.com/groups/222546864546011/ 不定期舉辦線上研討會課程 Tutorials SQL Server 2008 R2 https://msdn.microsoft.com/en-us/library/ms167593(v=sql.105).aspx SQL Server技術文件 SQL Server 2008 R2 -…

0

Database Corruption Challenge – Steve Stedman

2016年9月13日 PASS Database Corruption Challenge – Steve Stedman Presented by Steve Stedman https://www.youtube.com/watch?v=5oBA5nL0uRc&feature=youtu.be&a 1.備份資料庫,還原至測試機來進行修復工作(確保還原程序遭遇問題時,可以重頭再來修復一次) 2.如果決定用allow data loss,嘗試先找出可能遺失的資料並事先嘗試SELECT INTO保留下來 3.執行之前尋求是否有其他人的意見   定期執行 osql -E -Q”DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS” -oC:\outputfile.txt sqlcmd -E -Q”DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS” -o C:\outputfile.txt   PS.WITH NO_INFOMSGS只顯示錯誤訊息,一般的information不顯示   基本流程 1.將有損毀的資料庫備份檔還原到測試機,切換成single_user mode。 ATLER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK_IMMEDIATELY; 2.取得錯誤訊…

0

DBA Fundamentals-SQL Server Performance Tuning

2016/9/21 PASS online event DBA Fundamentals: SQL Server Performance Tuning Presented by Pinal Dave 1.資料庫相容性層級影響基數估計(Cardinality Estimation) 提升資料庫相容性層級就可以改善效能 The New and Improved Cardinality Estimator in SQL Server 2014 https://blogs.technet.microsoft.com/dataplatforminsider/2014/03/17/the-new-and-improved-cardinality-estimator-in-sql-server-2014/ Cardinality Estimation (SQL Server) https://msdn.microsoft.com/en-us/library/dn600374.aspx CE(Cardinality Estimation)會預測您的查詢可能傳回的資料列數目。 查詢最佳化工具使用基數預測,來產生最佳查詢計劃。 CE 愈精確,查詢計劃通常愈理想。 In 1998, a major update of the CE was part of Microsoft SQL Server 7.0, for which…

0

Distributed query vs OPENQUERY

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…

0

SQL Server Performance study resources

SQL Server Performance study resources SQL Server效能-學習資源 查詢處理架構: 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 Topic 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…

0

SQL Server 2008 R2 Performance Dashboard Reports

SQL Server 2008 R2 Performance Dashboard Reports SQL Server 2008 R2效能儀表板報表 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…

1

Configure Performance Log collection for SQL Server Performance Baseline

Configure Performance Log collection for SQL Server Performance Baseline 設定收集效能計數器記錄檔,建立SQL Server 效能基準線 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啟動 9.在Stop Condition頁籤,設定停止條件,例如勾選Overall Duration 16 hours,在Limit限制的區塊,勾選 Restart the data collector set at limits.,勾選 Maximum Size:設定300MB…

0