Tabloyu Online Alter Etme

Tabloyu Online Alter Etme

Tabloda kesinti (downtime) oluşturmadan kolon eklemek için bir yöntem.

Şema üzerinde değişiklik yapmak kaçınılmaz bir şey. Mesela integer bir kolonu bigint yapmak zorunda kalabiliriz ya da tabloya yeni bir kolon ekleyebiliriz vs... Microsoft her ne kadar bazı operasyonların (index rebuild gibi) online yapılmasına imkan tanısa da yukarıda bahsettiklerime benzer değişiklikler obje veya şema bazlı kesintilere (downtime) yol açabilir.

Peki herhangi bir downtime olmadan tabloları nasıl alter edebiliriz? Michael J. Swart bir yazı dizisinde bu tür işlemlerin online yapılmasını sağlayan bir yöntemden bahsetmiş. Senaryomuzda AdventureWorks2012 veritabanınındaki Sales.SalesOrderHeader tablosuna Rowversion (not null) kolonunu ekleyeceğiz.

1- Öncelikle birisini yedek tablomuz, diğerini switch işleminden önce kullanmak üzere 2 tane şema oluşturalım:

use AdventureWorks2012
go
create schema staging;
go
create schema obsolete;
go

2- Ardından SalesOrderHeader tablosunun aynısını (yeni ekleyeceğimiz kolon ile beraber) Staging şemasında oluşturalım:


use AdventureWorks2012
 
-- Yeni Tablo
CREATE TABLE Staging.SalesOrderHeader(
	SalesOrderID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	RevisionNumber tinyint NOT NULL,
	OrderDate datetime NOT NULL,
	DueDate datetime NOT NULL,
	ShipDate datetime NULL,
	[Status] tinyint NOT NULL,
	OnlineOrderFlag dbo.Flag NOT NULL,
	SalesOrderNumber  AS (isnull(N'SO'+CONVERT(nvarchar(23),SalesOrderID),N'*** ERROR ***')),
	PurchaseOrderNumber dbo.OrderNumber NULL,
	AccountNumber dbo.AccountNumber NULL,
	CustomerID int NOT NULL,
	SalesPersonID int NULL,
	TerritoryID int NULL,
	BillToAddressID int NOT NULL,
	ShipToAddressID int NOT NULL,
	ShipMethodID int NOT NULL,
	CreditCardID int NULL,
	CreditCardApprovalCode varchar(15) NULL,
	CurrencyRateID int NULL,
	SubTotal money NOT NULL,
	TaxAmt money NOT NULL,
	Freight money NOT NULL,
	TotalDue  AS (isnull((SubTotal+TaxAmt)+Freight,(0))),
	Comment nvarchar(128) NULL,
	rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
	ModifiedDate datetime NOT NULL,
	[Rowversion] rowversion NOT NULL, -- Eklemek istediğimiz kolon!!
	constraint PK_SalesOrderHeader_SalesOrderID
		primary key clustered (SalesOrderID ASC),
	constraint FK_SalesOrderHeader_Address_BillToAddressID
		foreign key(BillToAddressID) references Person.[Address] (AddressID),
	constraint FK_SalesOrderHeader_Address_ShipToAddressID
		foreign key(ShipToAddressID) references Person.[Address] (AddressID),
	constraint FK_SalesOrderHeader_CreditCard_CreditCardID
		foreign key(CreditCardID) references Sales.CreditCard (CreditCardID),
	constraint FK_SalesOrderHeader_CurrencyRate_CurrencyRateID
		foreign key(CurrencyRateID) references Sales.CurrencyRate (CurrencyRateID),
	constraint FK_SalesOrderHeader_Customer_CustomerID
		foreign key(CustomerID) references Sales.Customer (CustomerID),
	constraint FK_SalesOrderHeader_SalesPerson_SalesPersonID
		foreign key(SalesPersonID) references Sales.SalesPerson (BusinessEntityID),
	constraint FK_SalesOrderHeader_SalesTerritory_TerritoryID
		foreign key(TerritoryID) references Sales.SalesTerritory (TerritoryID),
	constraint FK_SalesOrderHeader_ShipMethod_ShipMethodID
		foreign key(ShipMethodID) references Purchasing.ShipMethod (ShipMethodID),
	constraint CK_SalesOrderHeader_DueDate
		check (DueDate>=OrderDate),
	constraint CK_SalesOrderHeader_Freight
		check (Freight>=0.00),
	constraint CK_SalesOrderHeader_ShipDate
		check (ShipDate>=OrderDate or ShipDate is null),
	constraint CK_SalesOrderHeader_Status
		check ([Status]>=0 and [Status]=0.00),
	constraint CK_SalesOrderHeader_TaxAmt
		check (TaxAmt>=0.00)
)
 
-- Default Constraintler
alter table Staging.SalesOrderHeader add
constraint DF_SalesOrderHeader_RevisionNumber
	default 0 for RevisionNumber,
constraint DF_SalesOrderHeader_OrderDate
	default getdate() for OrderDate,
constraint DF_SalesOrderHeader_Status
	default 1 for [Status],
constraint DF_SalesOrderHeader_OnlineOrderFlag
	default 1 for OnlineOrderFlag,
constraint DF_SalesOrderHeader_SubTotal
	default 0.00 for SubTotal,
constraint DF_SalesOrderHeader_TaxAmt
	default 0.00 for TaxAmt,
constraint DF_SalesOrderHeader_Freight
	default 0.00 for Freight,
constraint DF_SalesOrderHeader_rowguid
	default newid() for rowguid,
constraint DF_SalesOrderHeader_ModifiedDate
	default getdate() for ModifiedDate
 
	-- Non clustered indexler
create unique nonclustered index AK_SalesOrderHeader_rowguid
	on Staging.SalesOrderHeader(rowguid ASC)
 
create unique nonclustered index AK_SalesOrderHeader_SalesOrderNumber
	on Staging.SalesOrderHeader(SalesOrderNumber)
 
create nonclustered index IX_SalesOrderHeader_CustomerID
	on Staging.SalesOrderHeader(CustomerID)
 
create nonclustered index IX_SalesOrderHeader_SalesPersonID
	on Staging.SalesOrderHeader(SalesPersonID)
GO

3- Data kopyalamaya geçmeden önce eski tabloda (Sales.SalesOrderHeader) yapılan değişlikleri yeni tablomuza (Staging.SalesOrderHeader) uygulamak için trigger oluşturalım:

use AdventureWorks2012;
go
 
create trigger t_i_SalesOrderHeader
on Sales.SalesOrderHeader
after insert
as
	set identity_insert Staging.SalesOrderHeader on;
 
	insert Staging.SalesOrderHeader(SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
	select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
	from inserted
 
	set identity_insert Staging.SalesOrderHeader off;
go
 
create trigger t_u_SalesOrderHeader
on Sales.SalesOrderHeader
after update
as
	-- assuming pk values are never altered.
	update Staging.SalesOrderHeader
	set
		RevisionNumber = i.RevisionNumber,
		OrderDate  = i.OrderDate,
		DueDate  = i.DueDate,
		ShipDate  = i.ShipDate,
		[Status]  = i.[Status],
		OnlineOrderFlag  = i.OnlineOrderFlag,
		PurchaseOrderNumber  = i.PurchaseOrderNumber,
		AccountNumber  = i.AccountNumber,
		CustomerID  = i.CustomerID,
		SalesPersonID  = i.SalesPersonID,
		TerritoryID  = i.TerritoryID,
		BillToAddressID  = i.BillToAddressID,
		ShipToAddressID  = i.ShipToAddressID,
		ShipMethodID  = i.ShipMethodID,
		CreditCardID  = i.CreditCardID,
		CreditCardApprovalCode = i.CreditCardApprovalCode,
		CurrencyRateID  = i.CurrencyRateID,
		SubTotal  = i.SubTotal,
		TaxAmt  = i.TaxAmt,
		Freight  = i.Freight,
		Comment  = i.Comment,
		rowguid  = i.rowguid,
		ModifiedDate = i.ModifiedDate
	from Staging.SalesOrderHeader s
	join inserted i
		on s.SalesOrderID = i.SalesOrderID;
go
 
create trigger t_d_SalesOrderHeader
on Sales.SalesOrderHeader
after delete
as
	delete Staging.SalesOrderHeader
	from Staging.SalesOrderHeader s
	join deleted d
		on d.SalesOrderID = s.SalesOrderID ;
go

4- Artık kopyalamaya başlayabiliriz:

use AdventureWorks2012;
go

set nocount on;
set transaction isolation level serializable;
 
declare @i int = 0;
declare @new_i int = 0;
declare @rowcount int = -1;
declare @minBatchSize int = 5000
 
set identity_insert Staging.SalesOrderHeader on;
 
while @rowcount <> 0
begin
	select top (@minBatchSize) @new_i = SalesOrderID
	from Sales.SalesOrderHeader
	where SalesOrderID > @i
	order by SalesOrderID asc;
 
	merge Staging.SalesOrderHeader as target
	using (
			select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
			from Sales.SalesOrderHeader
			where SalesOrderID > @i
			and SalesOrderID <= @new_i
		) as source (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
	on target.SalesOrderID = source.SalesOrderID
	when not matched then
		insert (SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
		values (source.SalesOrderID, source.RevisionNumber, source.OrderDate, source.DueDate, source.ShipDate, source.[Status], source.OnlineOrderFlag, source.PurchaseOrderNumber, source.AccountNumber, source.CustomerID, source.SalesPersonID, source.TerritoryID, source.BillToAddressID, source.ShipToAddressID, source.ShipMethodID, source.CreditCardID, source.CreditCardApprovalCode, source.CurrencyRateID, source.SubTotal, source.TaxAmt, source.Freight, source.Comment, source.rowguid, source.ModifiedDate);
 
	set @rowcount = @@ROWCOUNT
 
	set @i = @new_i
 
end
 
set identity_insert Staging.SalesOrderHeader off;

Şimdi elimizde farklı şemalarda bulunan ve bir kolon dışında birbirinin aynısı iki tablo var.

Şu anki durum

5- Kopyalama bittiğinde Sales şemasındaki canlı tabloyu ilk adımda oluşturduğumuz Obsolete şemasına, Staging şemasındaki yedek tablomuzu da Sales şemasına transfer ediyoruz ve 3. adımda eklediğimiz trigger'ları siliyoruz:

ALTER SCHEMA obsolete TRANSFER Sales.SalesOrderHeader;
ALTER SCHEMA Sales TRANSFER staging.SalesOrderHeader;
 
drop trigger obsolete.t_i_SalesOrderHeader;
drop trigger obsolete.t_u_SalesOrderHeader;
drop trigger obsolete.t_d_SalesOrderHeader;

Şema değişikliğinden sonraki durum.

Böylece tablomuza yeni kolonu eklemiş olduk. Son olarak eski tablodaki Foreign Key'leri silmeli, yeni tablodaki trigger'ları yeniden oluşturmalıyız.

İyi Çalışmalar.

 

Yorum Yaz

Yorum yazabilmek için üye girişi yapmanız gerekiyor!

Yukarı Git