Cluster service verification failed

Cluster service verification failed 如果在Windows 2012 R2 failover cluster上安裝SQL Server 2008 R2 cluster將會遇到此錯誤 Rule “Cluster service verification” failed.   解決方法是 事先啟用 Failover Cluster Automation Server功能 Using SQL Server in Windows 8 and later versions of Windows operating system https://support.microsoft.com/en-us/kb/2681562 Resolution To resolve this issue, do one of the following: Enable the Failover Cluster Automation Server…

0

Install SQL 2017 CU4 non-English locale without internet access(離線安裝SQL 2017 CU4)

Install SQL 2017 CU4 without internet access (離線安裝SQL 2017 CU4) 週日接到微軟一位Machine Learning專家James Chang訊息,詢問如何離線安裝SQL 2017 CU4 了解狀況後,原來是SQL 2017 CU4在安裝過程中,需要下載Machine Learning(R Server and Python Server)相關cab檔,因為沒有網際網路,所以一直無法成功。 Error log出現要下載下列幾個檔案: SRO_3.3.3.300_1028.cab SRS_9.2.0.400_1028.cab SPS_9.2.0.400_1028.cab SPO_9.2.0.24_1028.cab 經過來回確認與research,最後的解決方法是 1.download Cab file 透過這篇官方文件,下載需要的cab檔 Installing machine learning components without internet access 2.rename cab file 由於這些cab檔案名稱是1033(English語言環境),如果SQL Server是繁體中文版,在CU4的安裝過程中指定這些cab檔案的所在目錄會無法辨識,需手動將cab檔案名稱更名,將1033改為1028 3.provide the location of the correct version of the…

0

複寫疑難排解 Replication Troubleshooting

複寫疑難排解 Replication Troubleshooting   從TLog讀取 sp_replcounters (Transact-SQL) Returns replication statistics about latency, throughput, and transaction count for each published database. This stored procedure is executed at the Publisher on any database. sp_replcounters sp_repltrans (Transact-SQL) Returns a result set of all the transactions in the publication database transaction log that are marked for replication but have…

0

複寫監控 Monitor Replication

複寫監控 Monitor Replication Measure Latency and Validate Connections for Transactional Replication 使用Tracer Token監控複寫延遲 保留延遲紀錄作法請參考我這篇   Programmatically Monitor Replication–Programmatically Monitor Replication USE [distribution] GO sp_replmonitorhelppublisher GO sp_replmonitorhelppublisher (Transact-SQL) USE [distribution] GO sp_replmonitorhelppublication @publisher = N’SQLDEV1′ GO sp_replmonitorhelppublication (Transact-SQL) USE [distribution] GO sp_replmonitorhelpsubscription @publisher = N’SQLDEV1′, @publication_type = 0 GO sp_replmonitorhelpsubscription (Transact-SQL) USE [distribution] GO sp_replmonitorsubscriptionpendingcmds @publisher…

0

SQL Server 2008 R2 Management Studio – Darker Shades of Blue Theme

SQL Server 2008 R2 Management Studio – Darker Shades of Blue Theme SSMS 2008 R2 套用Visual Studio色彩佈景主題環境設定檔 SQL 2012 SSMS以上才支援匯出與匯入環境設定檔 SQL 2008 R2 SSMS需自行調整字型與色彩,以下方法可以直接套用已經設定好的色彩佈景主題   1.Download: CurrentSettings-2016-07-12.vssettings 2.覆蓋以下檔案 預設路徑 C:\Documents and Settings\{user name}\Documents\SQL Server Management Studio\Settings\ CurrentSettings-2016-xx-xx.vssettings 3.重開SSMS就可以套用成功 Reference: SQL Server 2012 Management Studio – Darker Shades of Blue Theme Making SSMS Pretty : My…

0

Filter content in ERRORLOG log file過濾log檔案的內容

Filter content in ERRORLOG log file 過濾log檔案的內容 當你在Troubleshooting調查一個錯誤與問題時,如果ERRORLOG有大量不需要的紀錄,你可以使用以下方法來過濾。PS.當然如果你使用SSMS UI介面也是可以過濾,但用Command速度比較快一點。   1.Create a exclude text file(E:\Temp\Log\exclude.txt) with the contain below. 建立一個文字檔,填入要過濾的關鍵字,例如以下文字(每一行為一組關鍵字) Error: 18456 Login failed for user 2.Start a Command Prompt as an Administrator and run the command below. 執行以下命令產生過濾後的ERRORLOG檔 type E:\Temp\Log\ERRORLOG | findstr /i /v /g:E:\Temp\Log\exclude.txt > E:\Temp\Log\ERRORLOG_Filtered.log Reference: Findstr https://technet.microsoft.com/en-us/library/bb490907.aspx  

0

Use Hyper-V differencing disks

Use Hyper-V differencing disks 使用Hyper-V差異虛擬磁碟 現在安裝個Windows Server虛擬機器(VM)通常要10到20GB的磁碟空間,若再安裝SQL Server則會更大,當我們需要建立多個VM時,可以使用Hyper-V的差異虛擬磁碟來節省磁碟空間。 差異虛擬磁碟(differencing disks)是共用一個parent disk(VHD or VHDX)作為base disk,差異虛擬磁碟只儲存與parent disk的差異,所以,每個VM若使用差異虛擬磁碟,就可以省下大量磁碟空間。 以下我們使用Windows 10 Pro Hyper-V的環境,設定一個Windows 2016 VM的差異parent disk來使用這個功能 1.首先新增一個暫時的VM,這個VM最後會刪除,我們只需要保留VHD檔 2.安裝作業系統,例如Windows Server 2016,可以參考下面這篇 Install Windows Server 2016 3.Windows Update到最新hotfix。 4.安裝其他需要的軟體,例如 7-zip等工具。(之後透過差異磁碟所建立的VM就不需要再安裝一次) 5.執行Sysprep工具,清除SID…等設定,sysprep執行完成後會自動關機。 %windir%\system32\sysprep\sysprep.exe (Enter system out-of-box experience, enable generalize, shutdown option: shutdown) Sysprep (System Preparation) Overview https://technet.microsoft.com/en-us/library/hh825209.aspx 6.刪除這個暫時VM。(Hyper-V的刪除動作只會刪除VM設定檔,所以VHD檔(or VHDX檔)會保留下來) 7.將VHD檔搬移到適當的位置,並設定Properties成唯讀(Read-only)。(此VHD檔(or VHDX檔)將作為差異磁碟的Parent磁碟,所以不可以被修改) 例如,我們將檔案搬移到…

0

Install Windows Server 2016

Install Windows Server 2016 安裝Windows 2016 1.如果是在Hyper-V的VM安裝windows 2016則會先出現這個畫面 2.選擇語系,時間貨幣格式,與輸入法 3.按下Install Now開始安裝 4.選擇Windows版本 PS.若要安裝有GUI管理介面,請選擇(Server with Desktop Experience)的版本 5.選擇安裝類型,若是全新安裝,請選擇第2個選項 Custom: Install Windows only (advanced) 6.選擇要安裝在哪一個硬碟 7.開始安裝 8.安裝完成,自動重新開機之後,輸入一組Administrator自訂密碼,就大功告成了 9.(Option)建議第一件事就是Windows update PS.若是VM,大約會占用17GB。 Reference: Windows Server 2016 https://docs.microsoft.com/en-us/windows-server/windows-server-2016 Install Server with Desktop Experience https://docs.microsoft.com/en-us/windows-server/get-started/getting-started-with-server-with-desktop-experience

0

Create a template VM and Create a new VM from template VM

Create a template VM and Create a new VM from template VM 建立範本虛擬機器,透過範本虛擬機器來建立新的虛擬機器 1.Create a template VM (建立一個範本VM) Install guest OS, enable roles and features and then apply the needed Windows patches and hotfix. 2.Run SYSPREP (執行Sysprep工具) Login VM, then run SYSPREP %windir%\system32\sysprep\sysprep.exe (Enter system out-of-box experience, enable generalize, shutdown option: shutdown) Sysprep (System Preparation)…

0

Virtual Machine software and image file

Virtual Machine host software 虛擬機器軟體與虛擬機器映像檔 1.Microsoft 微軟虛擬化軟體 Windows Virtualization Software 虛擬化軟體 Windows 7 Windows Virtual PC https://www.microsoft.com/zh-tw/download/details.aspx?id=3702需要下載後安裝 Windows 8 Windows 8.1 Windows 2012 Windows 2012 R2 Step-By-Step: Enabling Hyper-V for use on Windows 8.1 https://blogs.technet.microsoft.com/canitpro/2014/03/10/step-by-step-enabling-hyper-v-for-use-on-windows-8-1/內建功能,直接在控制台>程式與功能>開啟或關閉 Windows 功能,勾選[Hyper-V]Install Hyper-V and create a virtual machinehttps://technet.microsoft.com/en-us/library/hh846766(v=ws.11).aspx   Windows 10 在 Windows 10 上安裝 Hyper-V https://docs.microsoft.com/zh-tw/virtualization/hyper-v-on-windows/quick-start/enable-hyper-v內建功能,直接在控制台>程式與功能>開啟或關閉 Windows 功能,勾選[Hyper-V] Windows…

0