星期五, 1月 05, 2018

研究一下SQL Server 高可用架構的Connection String寫法

1.AlwaysOn Availability Group.NET Connection String寫法:

 

The following is an example connection string, where the [AvailabilityGroupListenerName] is the Listener DNS Name that was configured when replicas were created:

 Data Source=[AvailabilityGroupListenerName];Initial Catalog = AdventureWorks2008R2; ApplicationIntent=ReadOnly

 

2.Database Mirroring 架構下的.NET Connection String寫法:

To automatically redirect connections when a database mirroring failover occurs.

"Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;"

 

3.Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

 

4.Connection to a SQL Server instance

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;Connection Timeout=120;

 

5.Connection to a SQL Server Failover Cluster Instance

Server=ClusterName\SQLInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;Connection Timeout=120;

 

ref:

其他各種寫法(ODBC/ OleDB) :

 http://www.dofactory.com/reference/connection-strings

https://www.connectionstrings.com/sql-server/

https://dba.stackexchange.com/questions/61977/how-to-connect-client-applications-to-sql-server-2012-cluster

星期二, 12月 12, 2017

FWD MySQL 5.6 GTID 如果遇到資料衝突的解決方法

MySQL 5.6 GTID 如果遇到資料衝突, 早期使用的SQL_SLAVE_SKIP_COUNTER方式不再適用

需要通過設置gtid_nextgtid_purged完成

Ref:

http://dbaplus.cn/news-11-857-1.html

 

Slave : show slave status \G;訊息中看到

那麼可以透過兩種方式跳過

1.@Slave DB

set gtid_next='e10c75be-5c1b-11e6-ab7c-000c296078ae:6';

begin;

commit;

set gtid_next='AUTOMATIC';

start slave;

 

或是已經知道是primary key duplicated, 可以跳過多個transaction.

 

2.@Slave DB

reset master;

set GLOBAL gtid_purged='gtid xxxxxxx : 1-10';

show master status;

start slave;

 

3.或是重新透過mysqldump進行Slave DB initial

[root@node1 ~]# mysqldump --all-databases --single-transaction --routines --events --host=127.0.0.1 --port=3306 --user=root > dump.sql

 

生成的dump.sql檔裡包含了設置gtid_purged的語句

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

SET @@SESSION.SQL_LOG_BIN= 0;

...

SET @@GLOBAL.GTID_PURGED='e10c75be-5c1b-11e6-ab7c-000c296078ae:1-10';

...

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

 

恢復資料前需要先通過reset master清空gtid_executed變數

[root@node2 ~]# mysql -h127.1 -e 'reset master'

[root@node2 ~]# mysql -h127.1 <dump.sql

星期四, 11月 30, 2017

FW:SQL SERVER index 何時應該重建

索引重建的時機

 

1.檢查 External fragmentation 部分

  avg_fragmentation_in_percent 的值大於 15

2.檢查 Internal fragmentation 部分

  avg_page_space_used_in_percent 的值小於 60

 

他可以自動幫你算出哪些索引需要被重建或重組,而且直接幫你把 ALTER INDEX T-SQL 都寫好,程式碼如下:

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +

       CASE

              WHEN ps.avg_fragmentation_in_percent > 15

              THEN 'REBUILD'

              ELSE 'REORGANIZE'

       END +

       CASE

              WHEN pc.partition_count > 1

              THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))

              ELSE ''

       END,

       avg_fragmentation_in_percent

FROM   sys.indexes AS ix

       INNER JOIN sys.tables t

       ON     t.object_id = ix.object_id

       INNER JOIN sys.schemas s

       ON     t.schema_id = s.schema_id

       INNER JOIN

              (SELECT object_id                   ,

                      index_id                    ,

                      avg_fragmentation_in_percent,

                      partition_number

              FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)

              ) ps

       ON     t.object_id = ps.object_id

          AND ix.index_id = ps.index_id

       INNER JOIN

              (SELECT  object_id,

                       index_id ,

                       COUNT(DISTINCT partition_number) AS partition_count

              FROM     sys.partitions

              GROUP BY object_id,

                      index_id

              ) pc

       ON     t.object_id              = pc.object_id

          AND ix.index_id              = pc.index_id

WHERE  ps.avg_fragmentation_in_percent > 10

   AND ix.name IS NOT NULL

 

ref:

https://blog.miniasp.com/post/2009/01/18/Let-SQL-Server-Tell-You-Which-Indexes-to-Rebuild-or-Reorganize.aspx

FW: 如何移轉SQL Server Login/ Role/ Job 到其他資料庫 (Apply to SQL 2000 to 2016)

除了以往的SP_help_revlogin, 現在有新的powershell去做這件事

 

Copy-SqlLogin Export-SqlLogin 是針對這個工作的powershell cmdlet

 

而最可貴的是他從Sql2000sql 2016都有支援到, 大大簡化了流程的複雜度

 

1.

#export all of your logins to a file, complete with SID, hashed password, roles, permission sets, securables

Export-SqlLogin -SqlServer sql2005 -FileName C:\temp\sql2005-logins.sql 

 

2.

#To Live login migration from SQL Server 2000 to SQL Server 2016

Copy-SqlLogin -Source sqlsvr2000 -Destination newsql2016 

 

3.

#Syncs the server & db permission sets, as well as the server & db roles and job ownership.

Sync-SqlLoginPermissions -Source sql2005 -Destination sql2016 

 

ref:

https://social.technet.microsoft.com/Forums/zh-TW/22d9b985-101f-4cad-b0e1-22d4d51e1fb5?forum=sqlservermanagementzhcht

https://blog.netnerds.net/2016/06/its-2016-why-is-sp_help_revlogin-a-thing/

星期三, 11月 29, 2017

MSSQL 各版本研究筆記

[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/

星期二, 11月 28, 2017

MSSQL LDF 意外損毀(因為磁區壞軌)的救援方式

RiCo 兄提供了當 LDF 意外損毀(因為磁區壞軌)的救援方式
ref:
https://dotblogs.com.tw/ricochen/archive/2010/01/11/12962.aspx
http://blog.xuite.net/tolarku/blog/41937899-%5BSQL%5D+%E4%BA%A4%E6%98%93%E8%A8%98%E9%8C%84%E6%AA%94+LDF+%E5%A4%AA%E5%A4%A7

1.確認原本資料庫檔案路徑位置
2.建立新的資料庫(名稱需與原資料庫名稱相同)
3.停止SQL server (不能單純用 detach 的嗎?)
4.將原本的MDF,覆蓋新資料庫的MDF檔案
5.將舊的LDF檔刪除
6.啟動 SQL Server
7.查看資料庫狀態「select state_desc from sys.databases where name='DB_Name'」,因為LOG檔找不到,所以狀態為「Recovery_Pending」
8.將資料庫設為「Emergency」狀態「ALTER DATABASE DB_Name SET SINGLE_USER; Go;」、「ALTER DATABASE DB_Name SET EMERGENCY; Go;」、「DBCC CHECKDB (DB_Name, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; Go;」
9.再確認資料庫狀態 「select state_desc from sys.databases where name='DB_Name'」
10.切換單一使用者模式去檢查DB [DBCC checkdb('DB_Name')]
11.到 Table 去查看資料正常與否

星期五, 11月 24, 2017

MySQL 5 on CentOS 7 解決 Could not increase number of max_open_files to more than 1024 (request: 1024000)

在CentOS 7 除了 1.要調整/etc/security/limits.conf 的nproc, nofile 以外 mysql soft nofile 65535 mysql hard nofile 65535 mysql soft nproc 65535 mysql hard nproc 65535 2.要加兩個參數到mysql.service設定檔 LimitNOFILE=infinity LimitMEMLOCK=infinity 3.重啟動daemon systemctl daemon-reload systemctl restart mysql.service
才不會在mysql.err 看到

[Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 1024000)

這樣的錯誤訊息 ref: https://www.securityinet.com/cloudlinux-7-mysql-open_files_limit-and-max_open_files-setup/

星期四, 11月 23, 2017

FW: 12c 如何移動aud$ table 到特定tablespace

12c 如何移動aud$ table 到特定tablespace
主要是運用了DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION 這個套件來做調整

或是可參考我以前寫的文章
https://jaychu649.blogspot.tw/2012/02/oracle-11g-auditing-sysaud-disable-db.html


ref:
http://oracle-help.com/oracle-12c/oracle-12cr2/move-aud-table-another-tablespace-using-dbms_audit_mgmt/?lipi=urn%3Ali%3Apage%3Ad_flagship3_feed%3BSMvvrWj8Rxa5bKThzjrryg%3D%3D



Use steps to move AUD$.
Use the dbms_audit_mgmt to move the tablespace.
check whether tablespace has been moved from system to AUDIT_DATA or not.

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...