[MS SQL Server]如何在Replication中新增Article時只建立新增的Article的Snapshot

在 MS SQL Server 中, 已建立好的 Replication , 若是要新增 article 時, 可以參考這篇: http://tim.diary.tw/2009/02/27/adding-articles-to-existing-publications/ , 比較討厭的地方就是若是原來的 publication 內容多或是資料量大時, 在重做一個新的可用的 snapshot 會很久, 而且佔用很大的空間, (雖然只會將新增的 article 同步到 subscriber).

其實有個參數可以讓新增 article 到 publication 時, 只建立新增 article 的 snapshot, 這樣就會省時又省空間多了, 參數就是在 publication 中的 @allow_anonymous 及 @immediate_sync, 預設在建立 publication 時, 這兩個參數是 true, 我們可以利用 sp_changepublication 這個 stored procedure 來調整已建立的 publication 將參數改為 false 即可, 如下:

[code]

EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘allow_anonymous’ ,
@value = ‘false’
GO

EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘immediate_sync’ ,
@value = ‘false’
GO

[/code]

如此一來, 在新增 article 到publication時, 就可以大幅減少建立整個 snapshot 的時間與空間了.

參考資料: http://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/

如何在沒有distributor及subscriber的狀況下, 移除publication (MS SQL Server)

有時候, 在測試環境下(當然生產環境下也有可能發生), 會因為某些原因, 在一組架構好的 replication 下, 沒有了 distributor 及 subscriber 的狀況下, 但 publication 還在時, 如何能移除該 publication.

使用 UI 操作的狀況下, 會回應沒有 distributor, 所以移除失敗等狀況, 所以得使用指令的方式來進行, 使用方式如下:

[code]

sp_replicationdboption @dbname= ‘dbname’,
@optname=  ‘Publish’,
@value= ‘false’,
@ignore_distributor=  1

[/code]

(參考 sp_replicationdboption 指令用法: http://msdn.microsoft.com/zh-tw/library/ms188769.aspx )

該資料庫(db)在沒有其他發行集(publication)的狀況下, 直接使用這個指令即可, 可以快速地將 publication 移除, 方便又好用!

SQL Server的Replication中的Article必須要有Primary Key

這個是朋友請我查的資料, 其實邏輯上還蠻容易的, 因為 SQL Server的Transactional Replication是參考table的primary key來進行新增刪除修改的, 若是該table沒有primary key的話, 是沒有辦法加入到publication的article裡的.

資料可以參考這裡:

http://msdn.microsoft.com/en-US/library/ms152559%28v=SQL.90%29.aspx

其中的:

Limitations on Publishing Objects

  • The maximum number of articles and columns that can be published differs by publication type. For more information, see the “Replication Objects” section of Maximum Capacity Specifications for SQL Server 2005.
  • Stored procedures, views, triggers, and user-defined functions that are defined as WITH ENCRYPTION cannot be published as part of SQL Server replication.
  • XML schema collections can be replicated but changes are not replicated after the initial snapshot.
  • Tables published for transactional replication must have a primary key. If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication.
  • Bound defaults created with sp_bindefault (Transact-SQL) are not replicated (bound defaults are deprecated in favor of defaults created with the DEFAULT keyword of ALTER TABLE or CREATE TABLE).

 

上面紅字部分就是說明這個限制.

 

SQL Server Optimization

這篇文章寫得十分有用, 建議有在做資料庫效能調校的朋友們可以讀一下.

http://msdn.microsoft.com/en-us/library/aa964133%28SQL.90%29.aspx

其中最後有還蠻精要的整理, 也就是這段落: Optimization Rules of Thumb

對於 index 等的設計都有蠻好的解說及經驗, 利用執行計劃 (execution plan) 的數據資料來檢視索引建立的狀況是否良好, 而且也應該適時更新統計資料(statistics), 利用這個指令: update statistics <TableName>, 更新好統計資料的 table , 再來檢視執行計劃, 會更準確.

另外查詢時, 儘量不要對資料欄位進行操作, 也就是以原本的資料來做比對及判斷, 查詢引擎才會使用對應的索引, 若利用了函數或一些轉型, 讓原始欄位改變的查詢條件, 查詢引擎就沒有辦法使用索引帶來的好處了, 如文章中舉的例子, 像是:

cast(DateField as varchar(20)) = @dateString

應該調整為 @dateString 轉型成為 DateField 的型態, 再來進行查詢, 肯定效果會更好, 另外還有這個例子(不是文章中提到的)也是很經典, 像是

Left(VarCharField, 1) = ‘k’

應該要改為

VarCharField like ‘k%’

這樣也是較函數來得佳, 都是可以改善查詢效能的.

試看看, 應該都有空間可以再精進!

SQL Server 中的 Pivot 語法

從 SQL Server 2005 起, 支援了 Pivot 語法及 UnPivot 語法.

利用 Pivot 語法, 可以做出一些轉置及特殊報表使用的查詢結果(對於要分析資料時特別有用).

先來建立範例資料, 這個資料表是一個流水號(logid, 一個會員編號 userno, 一個投票標的號 voteid, 一個時間欄位 createtime), 為能將資料表模擬真實的狀況, 建立一些資料來供應用, script 如下:

[sql]

CREATE TABLE VOTELOG (logid int identity(1,1), userno int, voteid int, createtime datetime default getdate())
insert into VOTELOG (userno, voteid, createtime) values (1, 1, ‘2010/7/1 1:15’)
insert into VOTELOG (userno, voteid, createtime) values (2, 1, ‘2010/7/1 3:27’)
insert into VOTELOG (userno, voteid, createtime) values (4, 2, ‘2010/7/1 3:29’)
insert into VOTELOG (userno, voteid, createtime) values (15, 2, ‘2010/7/1 16:31’)
insert into VOTELOG (userno, voteid, createtime) values (35, 1, ‘2010/7/1 17:31’)
insert into VOTELOG (userno, voteid, createtime) values (24, 1, ‘2010/7/1 17:38’)
insert into VOTELOG (userno, voteid, createtime) values (8, 2, ‘2010/7/1 20:18’)
insert into VOTELOG (userno, voteid, createtime) values (7, 2, ‘2010/7/2 2:22’)
insert into VOTELOG (userno, voteid, createtime) values (32, 1, ‘2010/7/2 2:36’)
insert into VOTELOG (userno, voteid, createtime) values (9, 1, ‘2010/7/2 10:09’)
insert into VOTELOG (userno, voteid, createtime) values (25, 2, ‘2010/7/2 13:35’)
insert into VOTELOG (userno, voteid, createtime) values (19, 2, ‘2010/7/2 18:19’)
insert into VOTELOG (userno, voteid, createtime) values (16, 2, ‘2010/7/2 19:28’)
insert into VOTELOG (userno, voteid, createtime) values (6, 1, ‘2010/7/2 23:06’)
insert into VOTELOG (userno, voteid, createtime) values (36, 2, ‘2010/7/3 0:42’)
insert into VOTELOG (userno, voteid, createtime) values (18, 2, ‘2010/7/3 2:19’)

[/sql]

假如現在我們需要分時的每日投票狀況的報表, 可以使用如下的 pivot查詢:

[sql]

select dates, [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23]
from (select convert(varchar, createtime, 111) as dates, datepart(hh, createtime) as hours from votelog ) as cum
pivot (count(hours) for hours in ( [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23] )) as pv

[/sql]

查詢結果如下:

這樣可以很清楚地呈現出, 每日分時的統計狀況, 當然, 若是要查的是分開兩個候選人的結果, 僅需要在中間的子查詢(cum)的裡面多加入 where 條件即可.

另外多提一個部分, 這樣的查詢, 可以在子查詢先做集總, 再進行 pivot, 效果會更好(尤其在資料量愈大時), 語法如下:

[sql]

select dates, [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23]
from (select convert(varchar, createtime, 111) as dates, datepart(hh, createtime) as hours, count(*) as counts from votelog group by convert(varchar, createtime, 111), datepart(hh, createtime) ) as cum
pivot (count(counts) for hours in ( [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23] )) as pv

[/sql]

也就是在子查詢中(cum), 先 group 好, 再進行 pivot 的查詢(但記得要用sum前面count結果), 這樣的效果會更有效率.

pivot 及 unpivot 語法可以參考以下資料:

使用 PIVOT 和 UNPIVOT: http://technet.microsoft.com/zh-tw/library/ms177410%28SQL.90%29.aspx

另外有人整理好的 cross-table stored procedure也很方便, 可以參考這篇: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

SQL Server中的索引離散狀況

SQL Server 資料庫中的索引是用來加速查詢效率的一種方式, 利用了空間換取時間的方法來將查詢的速度增加.

既然是利用空間換取時間的方法, 代表著該存放的空間, 資料的排列狀況就會隨著時間的過去, 有資料的新增, 刪除, 修改時, 造成的索引離散狀況, 若是索引離散時, 代表著存取這些索引的 IO 量會增加, 也代表著效率會下降的狀況會發生.

如何查詢這個資料, 可以參考這篇文章: http://sharedderrick.blogspot.com/2010/02/index-fragmentation.html 有詳細的說明, 利用該查詢指令, 若是要查找特定 table 的話, 可以這樣下(SQL Server 2005以上版本):

SELECT sch.name N’結構描述’, obj.name N’資料表’,
inx.name AS N’索引名稱’,
index_type_desc N’索引類型’,
avg_fragmentation_in_percent ‘片段(%)’,
avg_page_space_used_in_percent N’頁面飽和度(%)’,
fragment_count,
avg_fragment_size_in_pages,
page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(‘TABLE_NAME’),NULL, NULL, ‘SAMPLED’) AS phy
INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> ‘HEAP’  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 5 DESC

利用了 OBJECT_ID() 函數, 把 table 的 object id 找出來, 便能指定查找特定 table 中的索引離散狀況.

根據微軟官方的資料: http://technet.microsoft.com/zh-tw/library/ms189858.aspx , 離散狀況在 5% 以下不需要調整, 在 5% ~ 30%之間, 可以利用 ALTER INDEX REORGNIZE 來進行重組, 而大於 30% 時, 可以使用 ALTER INDEX REBUILD (ONLINE = ON) 來進行索引重建.

不過若系統效能允許的狀況下, 其實也是可以直接使用 DROP INDEX, CREATE INDEX 的方式來重建的.

其他相關文章一併整理如下:

Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://technet.microsoft.com/en-us/library/cc966523.aspx

sys.dm_db_index_physical_stats (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms188917.aspx

ALTER INDEX (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188388.aspx

Table and Index size in SQL Server
http://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server

SQL Server Indexes
http://odetocode.com/articles/70.aspx

SQL Server全文檢索資料庫的復原

一般資料庫我們備份後, 由於全文檢索技術, 和資料庫引擎不同, 是使用檔案及全文檢索引擎架構的一種方式, 所以在回復到異地後, 資料庫上線了, 但 fulltext catalog 內的資全文檢索資料不能用的問題, 這時候, 只需要再做一次 rebuild 即可, 因為所有的資料庫及相關欄位等設定其實都在, 但路徑上及檔案結構上的問題, 導致會有無法讀取的狀況. 這時候, 於 restore 資料庫後, 做一次 fulltext catalog 的 rebuild, 問題就能順利解決了.

若是原地復原, 則沒有這個問題.

SQL Server如何新增article到publication

在使用 Replication 時, 若是使用 Transaction 的方式, 若有要新增 article 到 publication 時, 其實很容易.

基本上就只要在原來的 publication 的 property 裡的 articles, 多加上需要 replication 出來的article, 完成後, 他不會自動進行後續, 接下來的動作就是做 snapshot 就行了, 這裡比較討厭的是若是原來的publication資料多, 而加入的 article資料少, 其實很不划算, 不過做完 snapshot 後, replication 機制會開始將該新發行的article schema傳給 subscriber, 讓 subscriber 將沒有的資料寫入, 這樣就完成了.

其實只會做新的部分, 不過因為還是要一個 schema及起始資料, 所以還是得做一個 snapshot 是比較吃資源的地方, 和 alter table的狀況又不太一樣了. 不過操作上還是很直覺也很方便!

參考SQL Server 2005 help 資料: http://msdn.microsoft.com/en-us/library/ms152493(SQL.90).aspx

其中比較重要的是這段:

After adding an article to a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters). The Distribution Agent or Merge Agent then copies the schema and data for the new article to the Subscriber (it does not reinitialize the entire publication).

也就是說, 新增完了之後, 要做一次 snapshopt, 不過 agent 會將新的 article 送到 subscriber, 而不會整個 reinitialize!

SQL Server Replication下的異動資料表

若是SQL Server已設定完成Replication的Article時, 進行資料表異動, 其實會透過 DDL 傳送的方式, 將異動的指令也透過複寫的方式送出, 並進行同步. 可以參考相關文章: http://www.replicationanswers.com/AlterSchema2005.asp

不過若是異動的欄位是 Primary Key時, 將會觸發 exception, 發出如下的訊息:

Msg 4929, Level 16, State 1, Line 2
Cannot alter the table ‘tbltest’ because it is being published for replication.

如此一來便無法使用這種 ddl 傳遞的方式將 Replicated Table 的異動送出. 若是要執行這樣的異動需求, 得先解除掉發行及訂閱此 table, 才能進行調整, 調整完成後才能再重新設定回發行及訂閱, 接下來再做 snap shot 進行遞送出去.

SQL Server Transactional Replication注意參數(max text repl size)

在使用 SQL Server Replication 發行資料時, 有個重要的參數必須要特別注意的地方, 就是 max text repl size, (在 SQL 2005也會影響 varchar(MAX) 這種型態的欄位).

參考這篇 最大文字覆寫大小選項 的設定方法來進行調整(利用 sp_configure).

另外在這篇 交易式複寫考量 也有說明在使用 Replication 時, 要注意覆寫資料的 size 上限, 若是在寫入資料大於該設定的 size 時, 將會發生 error, 內容如下:

Length of LOB data (??????) to be replicated exceeds configured maximum 65536

如此一來便會造成寫入失敗而無法成功寫入資料, 這在沒有使用 交易式複寫 時不會發生的問題. 這在使用交易式複寫時要特別注意的地方!