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

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

SQL Server and SQL Server Agent Service Account(Startup Account) and Permissions

SQL Server and SQL Server Agent service account(Startup Account) and Permissions SQL Server與SQL Server Agent 服務帳戶(啟動帳戶)與權限 可設定的種類 1.Domain User Account (使用一個網域使用者帳戶) 2.Local User Account (使用一個本機使用者帳戶) 3.Local Service Account (NT AUTHORITY\LOCAL SERVICE) 本機服務帳戶 4.Network Service Account (NT AUTHORITY\NETWORK SERVICE) 本機網路帳戶 5.Local System Account (NT AUTHORITY\SYSTEM) 本機系統帳戶 6.Virtual Accounts (start from Windows 7 and Windows Server 2008…

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

Query SQL Server backup history and restore history records

SQL Server備份還原紀錄 1.使用以下TSQL語法查詢備份檔紀錄 SELECT bs.backup_set_id, bs.database_name, bs.backup_start_date, bs.backup_finish_date, CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ‘ ‘ + ‘MB’ AS [Size], CAST(DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS VARCHAR(4)) + ‘ ‘ + ‘Seconds’ [TimeTaken], CASE bs.[type] WHEN ‘D’ THEN ‘Full Backup’ WHEN ‘I’ THEN ‘Differential Backup’ WHEN ‘L’ THEN ‘TLog Backup’ WHEN ‘F’ THEN ‘File or filegroup’ WHEN…

4

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

Service Accounts and Database Mirroring

Service Accounts (Configure Database Mirroring Security Wizard)http://msdn.microsoft.com/en-us/library/ms189434.aspx When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains). If all the server instances use the same domain account or use certificate-based authentication, leave the…

0

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