複寫疑難排解 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

Log Shipping Monitoring and Troubleshooting

Log Shipping Monitoring and Troubleshooting 交易紀錄傳送監控與疑難排解 1.Log Shipping status View the Log Shipping Report (SQL Server Management Studio) https://msdn.microsoft.com/en-us/library/ms181149.aspx To display the Transaction Log Shipping Status report on a server instance Connect to a monitor server, primary server, or secondary server. Right-click the server instance in Object Explorer, point to Reports, and point to Standard…

0

AlwaysOn Availability Groups learning resources

Overview of AlwaysOn Availability Groups (SQL Server) http://msdn.microsoft.com/en-us/library/ff877884.aspx Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) http://msdn.microsoft.com/en-us/library/ff878487.aspx SQL AlwaysOn Team Blog http://blogs.msdn.com/b/sqlalwayson/ SQL Server Customer Advisory Team http://blogs.msdn.com/b/sqlcat/ MSDN Blogs  >  Brad Chen’s SQL Server Blog   >  All Tags  >  alwayson http://blogs.msdn.com/b/bradchen/archive/tags/alwayson/ AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution…

0

How to create a failover cluster

How to create a failover cluster 如何新增一個容錯移轉叢集 1.In the Failover Cluster Manager snap-in, confirm that Failover Cluster Manager is selected and then, under Management, click Create a Cluster. 2.Follow the instructions in the wizard to specify: The servers to include in the cluster. The name of the cluster. Any IP address information that is not…

0

Setting Up SQL Server 2008 R2 Database Mirroring in a Workgroup

1.Verify SQL Server service account and configure SQL Server Login for Database Mirroring (1)SQL Server service account Principal Server SQL Server service account is local account name “SQLServer” Mirroring Server SQL Server service account is local account name “SQLService” (2)create a local account “SQLservice” on Principal Server create a login for local account “SQLservice” (3)create a…

2

Setting Up SQL Server 2008 R2 Database Mirroring with Certificate step by step in a Workgroup

Setting Up SQL Server 2008 R2 Database Mirroring with Certificate step by step in a Workgroup LAB VM Principal Server: SQL2008R2M1 Mirroring Server: SQL2008R2M2 Witness Server: SQL2008R2M3   [Step by Step] 1.Create Database Master key, certificate and endpoint on Principal Instance(SQL2008R2M1) USE master GO SELECT * FROM sys.symmetric_keys GO CREATE MASTER KEY ENCRYPTION BY PASSWORD=’P@ssw0rd’;…

3

Setting Up SQL Server 2008 R2 Log Shipping step by step

Setting Up SQL Server 2008 R2 Log Shipping step by step 1.Prerequisites (1)確認資料庫復原模式recovery model為[完整](Full)或[大量記錄](bulk-logged) (2)建立一個交易紀錄備份用目錄並設定分享,如果只有兩台SQL Server通常建立在主要伺服器Primary Server上,此目錄也可以建立在第三台主機上。 此範例的目錄建立在主要伺服器上,C:\LogShip 並給予主要伺服器Primary Server的SQL Agent啟動帳戶SQLservice有寫入與讀取權限   (3) 建立一個放置要還原的交易紀錄檔目錄並設定分享,此目錄建立在次要伺服器secondary Server主機上。 此範例的目錄建立在次要伺服器上,C:\LogShipRestore 並給予次要伺服器secondary Server的SQL Agent啟動帳戶SQLservice有寫入與讀取權限 2.Prepare a Secondary Database for Log Shipping (1)在主要伺服器上對要設定log shipping的資料庫執行完整備份。 (2)並執行一次交易紀錄檔備份Log Backup。(非必要Optional) (3)複製備份檔到次要伺服器。 (4)在次要伺服器,以WITH NORECOVERY參數還原完整備份與交易紀錄檔備份。 還原後資料庫狀態為(正在還原中)(Restoring)   3.開始設定Log Shipping (1)在主體伺服器設定主體資料庫屬性 (2)在選取頁面點選[交易紀錄傳送],右邊勾選[將此啟用為記錄傳送組態的主要資料庫] Under Select a page, click Transaction Log…

2

Setting Up SQL Server 2008 R2 Database Mirroring step by step

Setting Up SQL Server 2008 R2 Database Mirroring step by step 1.Prepare a Mirror Database for Mirroring (1)在主體伺服器,對要設定鏡像的資料庫執行完整備份。(Create either a full database backup or a differential database backup of the principal database.)   (2)執行交易紀錄檔備份(Take a log backup) ,為何需要Log Backup可以參考這篇文件 Typically, you need to take at least one log backup on the principal database. However, a…

1

Install SQL Server 2012 Failover Cluster with Command Line and Configuration File

Install SQL Server 2012 Failover Cluster with Command Line and Configuration File   1.安裝設定好Windows 2008 R2 Failover Cluster。 可參考這一篇的前半部 Build a two-node SQL Server 2008 R2 Cluster with iSCSI 2.安裝SQL Server Failover Cluster之前,必須在兩個節點先安裝.Net Framework 3.5.1。 可以透過Server Manager或PowerShell的方式來安裝。 (1)使用Server Manager安裝。   (2)使用PowerShell安裝。 A.以系統管理員身分啟動PowerShell命令提示字元。 B.輸入以下命令。     Import-Module ServerManager     Add-WindowsFeature NET-Framework-Core How to install/enable .Net 3.5 SP1 on…

0