Bu makale önceki makalelere göre biraz uzun olacak çünkü sıfırdan canlı bir sistemde partition işlemini nasıl yapıldığını ele alacağım. Sıfır Tabloda sanki hiçbir işlem yokmuş gibi yapacağım baştan sonra her şeyi kendi kuracağım. Dipnot olarak gerçek bir sistemde nasıl senaryolar önünüze gelebilir ona değineceğim.
1.ADIM
Bir veritabanı oluşturuyorum çünkü partition işlemi veritabanı altında bulunan tablo bazlı yapılır. Şu gerçek karşımıza çıkmaktadır veritabanı oluştururken kullanıcıdan detaylı veritabanı bazlı bilgi alındığında baştan tablo partition yapıda oluşturulur ve bu gibi sorunlarla uğraşılmaz.
Aşağıda bulunan örneğimde canlı bir sistemdeki kendim oluşturmuş olduğum tabloyu gözlemleyerek işlemlerime koyuluyorum. Eğer elinizde partition yapacağınız bir tablo varsa bu başlangıç işlemlerinin hepsini atlayabilirsiniz.
İlk başta bir veritabanı oluşturuyoruz.
Bu veritabanı altında bir tablo oluşturulur ve içine sürekli verinin geleceği şekilde ama önceden bu tabloya farklı tarihlerde veri aktarılır çünkü partition’da ilgili file group’a yazılsın diye.
use [PARTITIONTEST]
create table canliveri(
sırano int identity(1,1) not null,
tarih datetime not null,
CONSTRAINT [Bilgi] PRIMARY KEY CLUSTERED
(
sırano ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_ROW_LOCKS = ON, FILLFACTOR = 80)
)
Daha sonra bu tabloya tarih kolununa nonclustered indexs tanımlanır. Partition yapımız bu koluna göre indexs oluşturacak. Tarih kolunu bizim için tekil olduğu için aynı değeri tekrardan insert etmesin diye.
USE [PARTITIONTEST]
GO
CREATE UNIQUE NONCLUSTERED INDEX [tarih_nonclustered_indexs] ON [dbo].[canliveri]
(
[tarih] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Şimdi oluşturmuş olduğum bu canlıveri tablosuna 82 tane manuel tarih insert ettim ilk başta manuel veriler atalım daha sonra bir job tanımlayarak canlı veriler atmaya başlayalım. 2020 yılından başlayarak atmaya başladım. Resimde görüldüğü gibi
insert into dbo.canliveri([tarih]) values('2020-01-08T09:10:15.000')
insert into dbo.canliveri([tarih]) values('2021-01-08T09:10:15.000')
insert into dbo.canliveri([tarih]) values('2022-01-08T09:10:15.000')
insert into dbo.canliveri([tarih]) values('2023-01-08T09:10:15.000')
insert into dbo.canliveri([tarih]) values('2024-01-08T09:10:15.000')
Sıra sıra yıllara göre sanki kullanıcı bazlı işlem yapılmış gibi tarihe göre sıralı bir şekilde veri attım. Zaten sıralı atmasamda nonclustered onu kendi içinde diziyor. Manuel yaptığım işlemlerden sonra bunu bir job’a bağlayıp sanki bir canlı sistemmiş ve kullanıcılar’dan sürekli veri geliyormuş gibi senaryomu oluşturdum. Aşağıda job tanımlama adımlarım
declare @tarih datetime
set @tarih=getdate()
insert into dbo.canliveri([tarih]) values(@tarih)
Yukardaki kod parçacığımı bir job’a bağlayarak 10 saniyede bir veri atmaya başladım. Canlı sistemimiz çalışmaya devam etsin.
Buraya kadar ne yapmaya çalıştım elimde örnek canlı sistemde bir tablo olmadığı için kendim bir senaryo oluşturup benzer bir biçimde yapmaya çalıştım. Tablomuza değerler atılıyor içinden 4 yılı ait 82 veri ve son yılın ise 10 saniyede bir güncel değerini atmaktadır.
2.ADIM
Not: Eğer partition oluşturacağımız veritabanında önceden oluşturulmuş parititon’a dahil etmek istediğimiz herhangi bir file group varsa ve bu file group read-only modundaysa partition yapılabilmesi için Readonly moddan Read-Write moda çekilmesi gerekmektedir. Önceden FG2019 backup için oluşturulmuş bunu kullanmaya bilirdikte ama biz kullanmayı tercih ettik. Bu işlem için servis restart’ına gerek yoktur. Sadece veritabanında herhangi bir aktif connection olmaması yeterli. Zaten single user modda bunu sağlıyor. Gerçek sistemde partition işlemleri için kesinti yapılır.
Büyük sistemlerde ön adımların hepsi tanımlanıp senaryo oluşturulduktan sonra ilgili veritabanı altında bulunan tüm kullanıcılar disable moduna çekilir kesinti olur ve partiton işlemleri yapıldıktan sonra hemen enable edilir script hazır bulundurulur.(Read-Write modda bu zaman aralığında yapılır.)
ALTER LOGIN [x1] DISABLE
GO
ALTER LOGIN [x2] DISABLE
GO
ALTER LOGIN [x3] DISABLE
GO
ALTER LOGIN [x1] ENABLE
GO
ALTER LOGIN [x2] ENABLE
GO
ALTER LOGIN [x3] ENABLE
GO
Msg 5070, Level 16, State 2, Line 6
Database state cannot be changed while other users are using the database ‘PARTITIONTEST’
use master
go
alter database [PARTITIONTEST] set single_user with rollback immediate
USE [PARTITIONTEST]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'FG2019'
if(@readonly=1)
ALTER DATABASE [PARTITIONTEST] MODIFY FILEGROUP [FG2019] READWRITE
GO
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'FG2019'
if(@readonly=1)
ALTER DATABASE [PARTITIONTEST] MODIFY FILEGROUP [FG2019] READWRITE
GO
use master
go
alter database [PARTITIONTEST] set multi_user with rollback immediate
3.ADIM
Yukarıdaki sorunu çözdükten sonra partition işlemi için yeni oluşturacağım filr group’ları ve data file’ları tanımlama işlemlerine geçebiliriz. Yukarıdaki FG2019 file group’u silip tekrardan oluşturabilirdik.
Bu işlemleri SSMS arayüzünden yapıyoruz create scpritlerini alıyoruz. Son olarak tablomuzda kaç veri var kontrol edelim.
Database’in file group ekranına gelip tanımlamalarımızı yapıyoruz.
Bizim tarih değerimiz 2020 yılında başlıyor bu yüzden function’daki ilk tarihimi 2021 yapıp bundan önceki değeri 2020 file group’a koy diyebiliriz. Ama ben daha sonra tabloma 2019 yılı ve öncesindeki bir değeri import etmek ve 2020 tarihindeki işlemlerin sadece FG2020’de olmasını istersem function’daki ilk tarihimi 2020 yapacağım(FG2019’a yazacak) yani bu değerden önceki değerleri file group 2019 yaz diyeceğim örneğin 2023-2024 yılı için FG2023 file group eklemem gerekmektedir. Bu olmazsa olmazımız.
File group’ları oluşturduktan sonra data file sekmesine gelip ilgili file group’a ilgili data file tanımlanır. Create script’leri alınır adım adım çalıştırılır.
USE [master]
GO
ALTER DATABASE [PARTITIONTEST] ADD FILEGROUP [FG2019]
GO
ALTER DATABASE [PARTITIONTEST] ADD FILE ( NAME = N'FG2019', FILENAME = N'C:\FG\FG2019.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG2019]
GO
GO
ALTER DATABASE [PARTITIONTEST] ADD FILEGROUP [FG2020]
GO
ALTER DATABASE [PARTITIONTEST] ADD FILE ( NAME = N'FG2020', FILENAME = N'C:\FG\FG2020.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP
[FG2020]
GO
GO
ALTER DATABASE [PARTITIONTEST] ADD FILEGROUP [FG2021]
GO
ALTER DATABASE [PARTITIONTEST] ADD FILE ( NAME = N'FG2021', FILENAME = N'C:\FG\FG2021.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP
[FG2021]
GO
GO
ALTER DATABASE [PARTITIONTEST] ADD FILEGROUP [FG2022]
GO
ALTER DATABASE [PARTITIONTEST] ADD FILE ( NAME = N'FG2022', FILENAME = N'C:\FG\FG2022.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP
[FG2022]
GO
GO
ALTER DATABASE [PARTITIONTEST] ADD FILEGROUP [FG2023]
GO
ALTER DATABASE [PARTITIONTEST] ADD FILE ( NAME = N'FG2023', FILENAME = N'C:\FG\FG2023.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP
[FG2023]
GO
GO
ALTER DATABASE [PARTITIONTEST] ADD FILEGROUP [FG2024]
GO
ALTER DATABASE [PARTITIONTEST] ADD FILE ( NAME = N'FG2024', FILENAME = N'C:\FG\FG2024.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP
[FG2024]
GO
GO
ALTER DATABASE [PARTITIONTEST] ADD FILEGROUP [FG2025]
GO
ALTER DATABASE [PARTITIONTEST] ADD FILE ( NAME = N'FG2025', FILENAME = N'C:\FG\FG2025.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP
[FG2025]
GO
Bu işlemlerden sonra function ve scheme tanımlamadan önce elimizde ne kadar veri var kontrol edelim.
4.ADIM
Sırada elimizde function ve scheme’mızı oluşturmak. İlk başta function oluşturulur. Bu işlem veritabanı bazında yapılır.
CREATE PARTITION FUNCTION [pf_Tarih](datetime) AS RANGE RIGHT FOR VALUES (N'2020-01-01T00:00:00.000', N'2021-01-01T00:00:00.000', N'2022-01-01T00:00:00.000', N'2023-01-01T00:00:00.000', N'2024-01-01T00:00:00.000', N'2025-01-01T00:00:00.000')CREATE PARTITION SCHEME [ps_Tarih] AS PARTITION [pf_Tarih] TO ([FG2019], [FG2020], [FG2021], [FG2022], [FG2023], [FG2024], [FG2025])Şimdi burada 2020 yılı verileri FG2019’a yazılır her yıl bir önceki FG’ye yazılır. 20 ile 21 arasındaki veriler FG2020’ye yazılır.
5.ADIM
Bundan sonraki adımlar partition işlemi yapılacağı için önceki partition’suz tablomuzla aynı özelliklere sahip bir partition’lı bir tablo oluşturmak daha sonra partition işlemi yapıldıktan sonra tablo isimlerimiz rename yapıp canlı verilerimizin yeni tabloya gelmesini sağlayacağız bu süre zarfında veritabanı erişilemez olacaktır.(Tekrar belirteyim read-only file group varsa veritabanı erişilmez durumdayken rad-only moddan çıkılır.)
Sırasıyla işlemlerimize başlayalım şimdi partition’lı bir tablo oluşturmak.
use [PARTITIONTEST]
create table canliveri23(
sırano INT IDENTITY(1,1) NOT NULL,
tarih datetime NOT NULL,
CONSTRAINT [Bilgi23] PRIMARY KEY CLUSTERED
(
sırano ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)on [ps_Tarih]([tarih])
)on [ps_Tarih]([tarih])
Bu şekilde bir hata aldım. Bunun sebebi partition scheme’mız FG üzerine tanımlı ama tarih function’nıyla ilişkili unique indexs’in içinde tarih kolunun tanımlamazsam hata alırım. Bunu partition’lı bu tablomuzda hangi unique indexs’i oluşturursak yapmamız lazım.
Önceki tablomda hangi indexs varsa yeni tablomdada oluşturuyorum. Eski tablomun üzerindeki non clustered index’lerin aynısını yeni tabloma alıyorum.
USE [PARTITIONTEST]
GO
CREATE UNIQUE CLUSTERED INDEX [tarih_nonclustered_indexs] ON [dbo].[canliveri23]
(
[tarih] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)on [ps_Tarih]([tarih])
GO
Şimdi kurulumların hepsini yaptığıma göre canlı veriyi diğer tablodan devam ettirme işlemine. Kesintinin olacağı kısım çünkü tablo isimleri değişiyor.
Burada tablomuzun ismini değiştireceğiz ve veriler gelmeyecek eski tablomuzdaki en son değeri yeni tabloya insert edeceğim bazı komutlarla veriler oradan başlayacak job aslında tablo ismi değiştiği için veri gelmeyecek yani bizim aslında dış taraftaki kullanıcılarımıza benzetebiliriz.
EXEC sp_rename 'canliveri', 'canliveri_eski' Bu komut ile canlı olan tablo ismini değiştirdim kullanıcılar bağlanamasın diye bizim burada jobımız artık veri yazamıyor. Eski tablomuzun ismi Canlıveri_eski oldu.
Hemen bazı script’lerle yukarıda ismini değiştirdiğim tablonun son kaydına baktım
select count(*)from canliveri_eski
Sayı az olduğu için count çektim yoksa max(sırano) komutu ile işlemi yapabiliriz. max sıra no daha mantıklı gibi count ile max(sırano) arasında benim sayılar farklı oldu. Emin olmak için denemelisiniz.
Son kaydı gördükten sonra son kaydı önceden oluşturmuş olduğum tabloya insert işlemi gerçekleştirdim.
set identity_insert canliveri23 on
insert into [dbo].[canliveri23](sırano,tarih)
select sırano,tarih from canliveri_eski where sırano=686
set identity_insert [PARTITIONTEST].[dbo].[canliveri23] off
Burada önceden oluşturmuş olduğum tablonun identity özelliğini pasif etmek için on-of yaptım.
Daha sonra canliveri23’ün(Yeni oluşturmuş olduğum gerçek tablonun ismi) ismini değiştiriyorum.(canliveri olarak) Veriler tekradan yazılmaya başlıyor.
EXEC sp_rename 'canliveri23', 'canliveri' Yukarıdaki kodda [dbo]].[canliveri_eski] tablosundaki son değeri canlıveri23 tablosuna atmıştım önceden sırano 686 olan değeri.
Tabloma tekrar select çektiğimde artık kullanıcıların verileri yazdığını görmüş oldum(aslında job)
select*from canliveri
Önceden tanımladığım job aracılığıyla veriler akmaya devam etti. Tanımlamış olduğum job aşağıda
Son verimiz eski tabloda durmaya devam ediyor bunu silmek için dublice veri olmasının önüne geçmek için önceki tablomuzdaki son kaydı siliyoruz.
delete from canliveri_eski where sırano=686
SELECT count(*) as sondeger
FROM [PARTITIONTEST].[dbo].[canliveri_eski]
Son olarak tablolarımızın ismi bu şekilde
Şimdi partition tablomuzda kontrol işlemlerimizi yapalım.
use [PARTITIONTEST]
SELECT
OBJECT_NAME(si.object_id) AS object_name
,pf.NAME AS pf_name
,ps.NAME AS partition_scheme_name
,p.partition_number
,ds.NAME AS partition_filegroup
,rv.value AS range_value
,(
CASE pf.boundary_value_on_right
WHEN 0
THEN 'RAGE_LEFT'
ELSE 'RANGE_RIGHT'
END
) AS range_direction
,SUM(CASE
WHEN si.index_id IN (
1
,0
)
THEN p.rows
ELSE 0
END) AS num_rows
FROM sys.destination_data_spaces AS dds
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
AND dds.destination_id = CASE pf.boundary_value_on_right
WHEN 0
THEN rv.boundary_id
ELSE rv.boundary_id + 1
END
LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
AND si.index_id = p.index_id
AND dds.destination_id = p.partition_number
LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
AND p.partition_id= dbps.partition_id
WHERE si.object_id = OBJECT_ID('[dbo].[canliveri]')
GROUP BY ds.NAME
,p.partition_number
,pf.NAME
,pf.type_desc
,pf.fanout
,pf.boundary_value_on_right
,ps.NAME
,si.object_id
,rv.value
ORDER BY p.partition_numberBu komut partition’lı olan FG’leri görmekteyiz son yılın verisi ilgili file froup’a canlı bir şekilde akmaktadır.
Verilerimiz şuan FG2024 içinde artıyor şuan zaten canlı veri tablosuna baksak bu değerde olduğunu göreceğiz
Bazı kullanıcılar bu adımlardan sonra partition işlemine kalkışabilir. Şimdi bu kalkışmadan sonra ne gibi durumlarla karşılaşılır neler yapmak gerekir onu ele alalım.
Burada yapmış olduğum hata parition’lı tablomu boş bir şekilde oluşturduk kullanıcılarımı disable ettikten sonra partition switch işlemini yapacaktık.
Switch işlemini yaptığım için bana bu hatayı vermiş oldu.
Msg 4939, Level 16, State 1, Line 2
ALTER TABLE SWITCH statement failed. index ‘PARTITIONTEST.dbo.canliveri_eski.Bilgi23’ is in filegroup ‘PRIMARY’ and partition 1 of index ‘PARTITIONTEST.dbo.canliveri.Bilgi23’ is in filegroup ‘FG2019’.
Bu hatayı vermesi doğru çünkü partition switch işlemini yapabilmemiz için önceden partition’sız oluşturulmuş tablomuzunda partition yapısında olması gerekiyor. Partition switch işlemimiz için iki tablomuzunda partition yapıda olması şart. Partition’sız tablomuzu partition’lı yapıya getirmek mümkün bunun için üzerinde bulunan indexs’lerin yeni baştan partition scheme’ya göre dizayn edilmesi gerekiyor. Buda büyük tablolarda tablomuza uzun bir süre erişilemez olmasına sebep olacaktır bu yüzden tercih edilmez. Bunun için aşağıdaki yapının en kısa sürede kesinti ile yapılması bizim için daha avantajlıdır.
insert into dbo.canliveri([tarih]) values('2020-11-08T09:10:23.000')
insert into dbo.canliveri([tarih]) values('2021-11-08T09:10:23.000')
Sonradan önceki yıllara ait bir değer insert edersek yanlış yapmış oluruz çünkü bizim bu tablomuz manuel değer insert edemez güncel tarihe göre geliyor ama test ortamı olup partition’ın çalışıp çalışmadığını kontrol ettim ve çalıştığını gördüm.
Bu yüzden bu değerleri siliyorum.
O yüzden yukarıdaki 1372-71 katılarını siliyorum.
Tüm değerleri eski tablodan yeni tabloya insert ediyorum
SET IDENTITY_INSERT [PARTITIONTEST].[dbo].[canliveri] ON
INSERT INTO [PARTITIONTEST].[dbo].[canliveri] ([sırano], [tarih])
SELECT [sırano], [tarih] FROM [PARTITIONTEST].[dbo].[canliveri_eski]
SET IDENTITY_INSERT [PARTITIONTEST].[dbo].[canliveri] OFF
Switch işlemimiz yapıldı. Şimdi bir select çekelim bakalım veriler doğru sıralamada gidiyor mu?
Verilerimiz doğru bir şekilde gelmiş oldu.Eski tablomuzda 5-6 ay sonra silinebilir.
Partition data kontrol ile hangi partition’da kaç veri olduğunu bulabiliriz.
SELECT *
FROM [dbo].[canliveri]
WHERE $PARTITION.[pf_Tarih]([tarih]) =2
ORDER BY [tarih] DESC ;
SELECT *
FROM [dbo].[canliveri]
WHERE $PARTITION.[pf_Tarih]([tarih]) =6
ORDER BY [tarih] DESC ;
SELECT *
FROM [dbo].[canliveri]
WHERE $PARTITION.[pf_Tarih]([tarih]) =5
ORDER BY [tarih] DESC ;
36 değerini sonradan eklediğim için tarih kolununa göre indexs tanımlı olduğu için bir düzenleme yapmış kendi içinde normalde eklenmez ben deneme işlemi yaptım sadece ilgili file group altına yazıyor mu yazmıyor mu diye.
Yukarıdaki işlemlerin doğruluğunuda bu tablodan kontrol edebiliriz.
Şunuda anlayabiliriz partition işlem için switch partition şart değil. Başka bir makalede görüşmek üzere.
“İman edip dünya ve âhiret için yararlı işler yapanlara gelince, onları da nimetlerle dolu, içinde ebedî kalacakları cennetler bekliyor. Bunu Allah gerçek olarak vaad etmiştir. O azîzdir, hakîmdir.”Lokman-8-9
1 thought on “MSSQL Server Canlı Sistemde Partition Nasıl Yapılır veya Yapılmalı”