#眉標=SQL Server 2008、SQL Profiler、SSMS
#副標=實用SQL Server 2008(7)
#大標=SQL Server 2008交易式複寫(下)
#作者=文／圖 胡百敬


===<反灰>=============
use Northwind
go
exec sp_publication_validation 
@publication='test',
@rowcount_only=2, -- 執行資料列計數及二進位總和檢查碼
@full_or_fast=2 --先嘗試從 sysindexes.rows 快速方法來執行條件式快速計數，若不同，再利用 COUNT(*)執行完整計數
==<end>==============




===<反灰>=============
tablediff -sourceserver . -sourcedatabase northwind -sourcetable t2 -sourceschema dbo -destinationserver . -destinationdatabase d -destinationtable t2 -destinationschema dbo -f C:\temp\tabledifferencepeer.sql -o C:\temp\tablediffoutput.txt
==<end>==============




===<反灰>=============
●tabledifferencepeer.sql檔案內容：
-- Host: .
-- Database: [nwind]
-- Table: [dbo].[customers]
DELETE FROM [dbo].[customers] WHERE [CustomerID] = N'a    '
UPDATE [dbo].[customers] SET [Country]=N'a' WHERE [CustomerID] = N'ALFKI'

●tablediffoutput.txt檔案內容：
Table [northwind].[dbo].[customers] on . and Table [nwind].[dbo].[customers] on . have 2 differences.
Fix SQL written to C:\temp\tabledifferencepeer.sql.
Err	CustomerID	Col
Dest. Only	N'a    '	
Mismatch	N'ALFKI'	Country 
The requested operation took 0.3750214 seconds.
==<end>==============




===<反灰>=============
--查詢結果要用文字顯示，而非預設的grid顯示方式
--且參數是數值型，轉成文字會變成亂碼
select command_id,
CONVERT(nvarchar(max),
case when datalength(command) > 6 
then right(command,datalength(command)-6) 
else command end) 搭配參數呼叫預存程序,
command 
from distribution.dbo.MSrepl_commands 
where xact_seqno= 0x0000003900000182000D00000000 and command_id=1
==<end>==============





===<反灰>=============
exec distribution.dbo.sp_browsereplcmds
@xact_seqno_start = '0x00000046000000F9000800000000',
@xact_seqno_end = '0x00000046000000F9000800000000' 
==<end>==============





===<反灰>=============
sp_MSdel_<結構描述與資料表名稱> <主鍵值>
==<end>==============




===<反灰>=============
--要在提供Publication的資料庫下執行，而非Distribution
exec sp_reinitsubscription 
  @publication = 'test',
  @article ='Customers',
  @subscriber ='byronsub',
  @destination_db ='nwind',
  @for_schema_change =1,
  @invalidate_snapshot =1 
==<end>==============




===<反灰>=============
--建立個版本較舊的Distribution DB備份
BACKUP DATABASE Distribution TO DISK='C:\temp\Distribution.bak'  WITH INIT 
--停掉執行散發的job，讓散發先不做
EXEC msdb.dbo.sp_stop_job @job_name=N'BYRONSUB-Northwind-test-BYRONSUB-3' 

INSERT Northwind.dbo.Customers(CustomerID,CompanyName) VALUES('a','a') 
--確認需要散發代理程式執行的命令已經在Distribution DB
select a.agent_id,
a.UndelivCmdsInDistDB,
a.DelivCmdsInDistDB,
b.name,
b.publisher_db,
b.publication
from distribution.dbo.MSdistribution_status a 
join distribution.dbo.MSdistribution_agents b
on a.agent_id=b.id 
where
b.publication='test' and
b.name=
'BYRONSUB-Northwind-test-BYRONSUB-3'
==<end>==============




===<反灰>=============
--為Restore做準備，刪掉所有連接在Distribution DB的連接
SELECT * FROM sys.sysprocesses WHERE dbid=DB_ID('Distribution')
KILL 52
…
--假設Distribution系統資料庫因故毀掉
--以先前的備份回復，導致Disbribution DB的版本較舊
RESTORE DATABASE Distribution FROM DISK='C:\temp\Distribution.bak'
==<end>==============




===<反灰>=============
--訊息18752，層級16，狀態2，程序sp_repldone，行1
--每次只有一個記錄讀取器代理程式或記錄檔相關程序(sp_repldone、sp_replcmds與sp_replshowcmds)可以連接到資料庫。如果您執行了記錄檔相關程序，請卸除用以執行程序的連接，或在啟動記錄讀取器代理程式之前在該連接執行sp_replflush，或執行另一個記錄檔相關程序。
==<end>==============




===<反灰>=============
USE Northwind
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 
==<end>==============





===<反灰>=============
backup log northwind to disk='c:\temp\nwind.trn'
==<end>==============





===<反灰>=============
--備份訂閱端資料庫
backup database nwind to disk='c:\temp\nwind.bak' with init

--對發行項新增資料
insert Northwind.dbo.Customers(CustomerID,CompanyName) values('1','1')
insert Northwind.dbo.Customers(CustomerID,CompanyName) values('2','2')

--確定訂閱端已經帶入新資料
select * from nwind.dbo.Customers

--砍掉存取訂閱端資料庫的連接
select * from master.dbo.sysprocesses where dbid=DB_ID('nwind') 
--這導致Distributeion Agent 在下一次要傳遞紀錄給訂閱端時
--因無法連接到目標端，而自動重起Distribution Agent
kill 57
…
go

--故意模擬災難後，從備分回到從前
--但Distribution 還保有交易資料
restore database nwind from disk='c:\temp\nwind.bak'
with replace 
--確認資料
select * from nwind.dbo.Customers

--故意發生一個動作，要Distribution Agent 傳遞
delete Northwind.dbo.Customers where CustomerID='1'
==<end>==============





===<反灰>=============
exec sp_executesql N'select hashid = 
case datalength(transaction_timestamp) 
when 16 then isnull(substring(transaction_timestamp, 16, 1), 0) 
else 0 end, 
transaction_timestamp, subscription_guid 
from MSreplication_subscriptions 
where UPPER(publisher) = UPPER(@P1) 
and publisher_db = @P2 and publication= @P3 
and subscription_type = 0 ',
N'@P1 nvarchar(8),@P2 nvarchar(9),@P3 nvarchar(4)',N'BYRONSUB',N'Northwind',N'test'
==<end>==============