[SQL]安裝SQL Server 2012 Cluster - 1(環境準備)
https://dotblogs.com.tw/rainmaker/archive/2013/11/11/127644.aspx
[SQL]安裝SQL Server 2012 Cluster - 2(Windows容錯移轉)
https://dotblogs.com.tw/rainmaker/archive/2013/11/11/127658.aspx
[SQL]安裝SQL Server 2012 Cluster - 3(SQL容錯移轉)
https://dotblogs.com.tw/rainmaker/archive/2013/11/11/127670.aspx
SQL Failover Cluster without Shared Storage – SQL Server 2012 and SMB
https://www.sqlskills.com/blogs/jonathan/failover-clustering-without-a-san-sql-server-2012-and-smb-for-shared-storage/
[SQL SERVER]SQL2016-啟用和設定Alwayson AG
https://dotblogs.com.tw/ricochen/2017/01/27/123048
SQL Server 2016 DTC Support In Availability Groups
With SQL 2016 we now support distributed transactions in availability groups.
https://blogs.technet.microsoft.com/dataplatform/2016/01/25/sql-server-2016-dtc-support-in-availability-groups/
-----------------------------------------------------------------------------------------------------------------------------------
SQL 2016 STANDARD EDITION AVAILABILITY GROUPS FEATURE:
1.企業版的次要複本(Secondary DB)可被讀取In Enterprise Edition, you can set this up as a Readable Secondary
2.標準版的次要複本(Secondary DB)上沒有讀取權限。Since Standard Edition AGs don't allow for Readable Secondaries, and the fact that this feature is supposed to eliminate the aging Database Mirroring feature.
3.僅支援同步複製。Synchronous Commit means every update, insert, delete, alter, etc. gets committed to all secondaries before the transaction is reported to the client application as complete.
4.支援一個可用性資料庫。
5.標準版本設定之基本可用性群組(BAG)無法直接升級至進階可用性群組。需要重新設定(Configure)
ref:
https://www.moserit.com/blog/sql-2016-standard-edition-availability-groups-the-build
https://docs.microsoft.com/zh-tw/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups
-----------------------------------------------------------------------------------------------------------------------------------
監控AlwaysOn Availability Group同步之SQL語法
Measuring Availability Group synchronization lag
https://www.sqlshack.com/measuring-availability-group-synchronization-lag/
在3 Nodes 的 FCI + AlwaysOn 架構中, FCI 有兩個Node : Active-Standby 的Standby 不須計價, AG : Node 3 - Secondary Replica 如果是Truly Passive也不計價, 然而Secondary Replica如果要提供Query的Scenario需購買license
https://www.starwindsoftware.com/blog/cost-and-license-considerations-between-always-on-availability-groups-and-always-on-basic-availability-groups
-----------------------------------------------------------------------------------------------------------------------------------
Youtube
DB Mirroring角色切換步驟 SQL Server Database Mirroring with Simple Steps (Video) : https://www.youtube.com/watch?v=Kb9AHcCMf7Q
如何修改DB Mirroring IP Address : https://blogs.msdn.microsoft.com/benjones/2009/09/25/changing-the-listener_ip-address-in-a-database-mirroring-configuration/
Stop application activity
Remove mirroring (SET PARTNER OFF)
Stop Mirroring endpoints (on principal and mirror)
Alter Mirroring endpoints to use new IP addresses e.g. ALTER ENDPOINT SET LISTENER_IP =
Start endpoints on principal and mirror
Enable mirroring (ALTER DATABASE <dbname> SET PARTNER = TCP://x.x.x.x)
SQL SERVER DATABASE MIRRORING PROS AND CONS
http://www.mssqlfix.com/2010/09/sql-server-database-mirroring-pros-and.html
Point 1: Database mirroring can be configured along with replication, log shipping, and database snapshots.
Point 2: In SQL Server 2008 compresses the Transaction Log at Principal Server before it is transferred to mirror server so it saves lot of traffic bandwidth.
Point 3: System databases can not be mirrored.
Point 4: Database which needs to be mirrored it must be a FULL recovery mode, mirroring doesn't work on other recovery models.
Point 5: High Safety Mode (Synchronous operation) Log are committed on Principal and Mirror when databases are synchronized.
Point 6: High Performance Mode (Asynchronous operation) Principal commits and Mirror tries to keep up with received log records from Principal.
Point 7: The mirrored server can not be used during mirroring operation, it always be on recovery mode.
Point 8: We can take the snapshot of the mirrored database, for reporting purpose.
Point 9: Automatic Failover is only possible when high safety mode is configured with automatic failover.
Point 10: Database mirroring has automatic server failover and client failover capabilities.
Point 11: Configuration of database mirroring is simpler than any other high availability like log shipping and replication.
Point 12: Mirroring has built-in network encryption support (AES algorithm).
Point 13: Database mirroring supports full-text catalogs.
Point 14: If you are configuring witness server as well then additional SQL Server instances may be required.
-----------------------------------------------------------------------------------------------------------------------------------
Create an alert on SQL Database Mirroring Status
ref1 https://support.solarwinds.com/Success_Center/Server_Application_Monitor_(SAM)/Create_an_alert_on_SQL_Database_Mirroring_Status
ref2 https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-mirroring-transact-sql
ref3 https://www.mssqltips.com/sqlservertip/3664/sql-server-database-mirroring-report/
For Solarwind - Utilize Orion.APM.SqlDatabase Package:
SELECT SqlDatabase.Uri, SqlDatabase.DisplayName FROM Orion.APM.SqlDatabase AS SqlDatabase
INNER JOIN Orion.APM.SqlDatabaseMirroring as Mirroring ON SqlDatabase.DatabaseID=Mirroring.DatabaseID
WHERE Mirroring.State != '4'
For MS-SQL :
--http://shamas-saeed.blogspot.tw/2012/05/finding-current-database-mirroring.html
SELECT d.name,m.database_id,m.mirroring_state,mirroring_state_desc,mirroring_role_desc,mirroring_partner_instance,mirroring_witness_name,mirroring_witness_state,mirroring_witness_state _desc FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where mirroring_state_desc is not null;
or SSMS -> [Databases] -> [System] -> [Database Properties] -> [Mirroring] Tab
-----------------------------------------------------------------------------------------------------------------------------------
Other reference link:
SQL Server還原模式 : Full recovery/ bulk Load/ Simple(自動收回記錄空間)
Database Mirroring : restore database norecovery (incremental forever的機制)
在高效能模式工作階段中，逾時期限一律為 10 秒。 這通常足以避免假性失敗。 https://docs.microsoft.com/zh-tw/sql/database-engine/database-mirroring/possible-failures-during-database-mirroring
ALTER DATABASE <dbname> SET PARTNER TIMEOUT 20;
Database Log Shipping : restore database with recovery , restore log with norecovery, restore database with recovery
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-transaction-log-backup-sql-server
Role Switching During a Database Mirroring Session (SQL Server)
After a role switch, jobs that ran on the former principal database must be recreated on the new principal server to run there
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/role-switching-during-a-database-mirroring-session-sql-server
使用憑證來建立Database Mirroring架構
http://udayarumilli.com/sql-server-database-mirroring-with-screenshots/
How to Remove SQL Server witness server from an existing Database Mirroring Configuration
As we know, the witness server is mandatory for automatic failover
https://www.mssqltips.com/sqlservertip/4254/how-to-remove-sql-server-witness-server-from-an-existing-database-mirroring-configuration/
沒有留言:
張貼留言