보미네

아래와 같이 Distributed Transaction을 사용하려면 ?

EXEC master.dbo.sp_addlinkedserver @server = N'a', @srvproduct=N'',@provider = 'sqlncli',@datasrc = '10.10.60.17,1433'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'a',@useself=N'False',@locallogin=NULL,@rmtuser=N'admin',@rmtpassword='adminP@ss0rd'




SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRANSACTION
-- 서버A 작업
INSERT INTO bdb.dbo.tbly (idx) VALUES (1);
-- 서버B 작업
INSERT INTO a.adb.dbo.tblx (idx) VALUES (2);
COMMIT TRANSACTION

아래 설정이 양쪽에 동일하게 잘 설정 되어야 동작함

service process

C:\>sc query msdtc

hosts

admin notepad c:\windows\system32\drivers\etc\hosts

dcomcnfg

net stop msdtc
net start msdtc

port

C:\Program Files\Smart Storage Administrator\ssacli\bin>ssacli ctrl slot=0 ld all show status
   logicaldrive 1 (10.17 TB, RAID 5): OK

C:\Program Files\Smart Storage Administrator\ssacli\bin>ssacli ctrl slot=0 pd all show status
   physicaldrive 1I:1:1 (port 1I:box 1:bay 1, 1.9 TB): OK
   physicaldrive 1I:1:2 (port 1I:box 1:bay 2, 1.9 TB): OK
   physicaldrive 1I:1:3 (port 1I:box 1:bay 3, 1.9 TB): OK
   physicaldrive 1I:1:4 (port 1I:box 1:bay 4, 1.9 TB): OK
   physicaldrive 1I:1:5 (port 1I:box 1:bay 5, 1.9 TB): OK

declare @dbName nvarchar(1000) ='storageTest'
declare @incSizeGigaBytes int = 10

-- internal variable 
declare @currentGigaBytes bigint 
, @tobeGigaBytes bigint 
, @message nvarchar(4000) = N''
, @mdfFileName nvarchar(1000) = N''
, @fileName nvarchar(1000) = N''
, @sql nvarchar(max)


select @currentGigaBytes = (size * 8) / power(2,20) , @fileName = name 
from master.dbo.sysaltfiles
where dbid = db_id(@dbName)
	and fileid = 1 

select @tobeGigaBytes = @currentGigaBytes + @incSizeGigaBytes 

select @message = N'currentGigaBytes : ' + cast (@currentGigaBytes as nvarchar(4000)) + ', tobeGigaBytes : ' +  cast (@tobeGigaBytes as nvarchar(4000)) 
select @message 

-- log
RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
set @sql = 'ALTER DATABASE '+@dbName+' MODIFY FILE ( NAME = N'''+@fileName+''', SIZE = '+cast(@tobeGigaBytes as nvarchar(100))+'GB, FILEGROWTH = 512MB)'
print @sql 

-- cmd 
exec (@sql)