SQL Dosya Tutsun Ama Backup’ ta Yer kaplamasın! Konumuz SQL FileStream

SQL Dosya Tutsun Ama Backup’ ta Yer kaplamasın! Konumuz SQL FileStream

Herkese Merhabalar; Yazdığımız uygulamalarda bir şekilde dosya(Resim,PDF,Rar,Zip vs) kaydetmemiz gerekiyor. Eğer uygulama web ise işimiz kolay dosyayı bir yere koyup Path(yol) unu kaydetmek gayet güzel bir seçim, çünkü o dosyayı kullanacak uygulama ile dosyalar aynı bilgisayarda. Ancaaaak 🙂 bir windows uygulaması yazıyorsak ve birden fazla kullanıcı dosya ekleyecek ve dosyalara erişecekse işler karışıyor. Dosya bir bilgisayara kaydedildiğinde diğer bilgisayarlar o dosyayı göremiyor ve dosyanın herkes için çoğaltılması ihtiyacı doğuyor. bunun için basit gibi görünen ama baş belası yöntemler de yok değil, örneğin ağ klasörü (tabi tüm bilgisayarlarda aynı yere denk getirmenin zorluğunu saymazsak)

Halbuki MSSQL bunun için bize güzel bir nimet sunuyor FileStream yapısı. bu yapı şu anlama gelir: Sen dosyayı SQL e Kaydeder gibi kaydet. ama ben o dosyayı mdb dosyama koymayim böylelikle mdb dosyası şişmesin. bunun yerine bana bir klasör göster ben oraya dosyaları istifleyeyim. eğer yedeğin içinde dosyalarını da görmek istersen dahil edebilirsin ama dosyalarla kalabalık yapma dersen sadece datanı yedek olarak alabilirsin.

İşte bu yaklaşım sayesinde dosyalarımız yedekleri ve .mdb dosyalarımızı şişirmeden bir klasörde dururlar. bununla birlikte SQL sorgusu ile de çağırılırlar. Bence mükemmel bir birleşim.

öyleyse hadi bunu yapalım; öncelikle SQL Server üzerinde ayar yapmak gereklidir. aşağıdaki şekilde yapın

Öncelikle SQL Server üzerinde ayar yapmalıyız

  1. Bilgisayarıma Sağ tık

  2. Yönet

  3. Hizmetler Uygulamalar

  4. SQL Server Configuration

  5. SQL Server Services

  6. SQL Server (MSSQLSERVER) Sağ Tık

    sizin instance isminiz farklı olabilir. örneğin benimki BOTANIKSQL

  7. Özellikler

  8. FILESTREAM Tabı

  9. Enable FileStream for Transact-SQL acces

    Seçili hale getirelim. Bu sql server klasörlere erişebilir demek olacak.

  10. Enable FileStream for file I/O access

    Seçili hale getirelim. Bu Dosyayı Windows üzerinden de okunabilsin diye

  11. Allow remote clients access to FileStream data

    Seçili Hale getirelim. Diğer bilgisayarlardan gelen remote kullanıcılar da dosyaya erişebilsin diye.

Bu işlemleri yaptıktan sonra SQL Server için FileStream ayarını değiştirmemiz gerekiyor bunun için şu komutu çalıştırın:

EXEC sp_configure filestream_access_level, 2  
RECONFIGURE  

Satırları ayrı ayrı çalıştırın (garanti olsun)

SQL Server’a Restart atmanız da gerekli.

Eğer kodla yazmak ilginç bir şekilde zor geliyorsa SQL Management üzerinden Server’a sağ tıklayıp Özellikler diyerek Advenced sekmesinden FileStream adımındaki modu Full Access Enabled yapabilirsin. (ben tembelim kod kolayıma geliyor)

bu sayede SQL Server FileStream kullanabilecektir. Sırada Database ayarları var. Farzedelim zaten Database oluşturmuştunuz (çünkü hep öyle olur) ve sonradan aa! dosya tutmak gerek dediniz. işlemlerimiz şöyle olacak:

1) Veritabanınıza Sağ Tıklayın ve Özellikler deyin

2) Filegroups Sekmesine gidin

3) FileStream sekmesinde File Group ekleyin

Default seçili olmalı

Files Sekmesine gidin

Add diyerek bir file oluşturun

Bu file farklı olarak FileType’ı FileStream olacak ve konum olarak Dosyaların bulunacağı klasöre seçeceğiz. bizim seçitiğimiz klasörün içine kendisi bir klasör açacak

Ok diyerek kaydedin.

buraya kadar başınıza bir iş gelmemişse güzel gidiyoruz demektir

Tabi bu oluşturduğumuz filestream ayarlarını artık bir tabloda kullanabilmeliyiz. Burada hassas iki konu var.

  1. Tablo içinde uniqueidentifier bir kolon içermeli bu alan unique olarak işaretlenmeli ve RowGuidCol olarak işaretlenmeli. ve NOT NULL olmalı
  2. Dosyayı tutacak olan kolon varbinary(max) olmalı ve FileStream olarak işaretlenmeli

Bu özellikleri verebilmek için tablonuzu kod ile oluşturmalısınız.

CREATE TABLE Dosyalar(
	Id int primary key identity(1,1),
	RowGuid uniqueidentifier ROWGUIDCOL unique  NOT NULL,
	Dosya varbinary(max) filestream NULL
)

Burada Id kolonunu eklemek sizin elinizde. ben bir Id kolonu oluşturup bunu tablolarıma koymayı daha kullanışlı bulduğum için bu şekilde yaptım. Zira Guid çok yer kaplıyor ve gözle seçmesi zor

Tablomuz da hazır olduğuna göre C# üzerinde bu data nasıl gelecek ve nereye kayıt olacak bunu değerlendirelim. Ado ile kayıt yapıyorsanız aslında yapmanız gereken tek şey byte[] olarak datayı parametrelere eklemek.

SqlConnection con = new SqlConnection("Server=.;Database=KuzeyYeli;user=sa;pwd=123");
            SqlCommand insert = new SqlCommand("Insert Dosyalar values(@guid,@dosya)", con);
            OpenFileDialog opf = new OpenFileDialog();
            if (opf.ShowDialog() == DialogResult.OK)
            {
                using (FileStream fs = File.OpenRead(opf.FileName))
                {
                    byte[] buffer = new byte[fs.Length];
                    fs.Read(buffer, 0, buffer.Length);
                    insert.Parameters.AddWithValue("@dosya", buffer);
                    insert.Parameters.AddWithValue("@guid", Guid.NewGuid());
                }
                con.Open();
                insert.ExecuteNonQuery();
                con.Close();
            }

Kod kısa olsun diye bir çok konuyu atladık ancak işin özü budur. bu kaydı datagridview içinde çektiğiniz zaman

 SqlConnection con = new SqlConnection("Server=.;Database=KuzeyYeli;user=sa;pwd=123");
            SqlDataAdapter adp = new SqlDataAdapter("Select * from dosyalar", con);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            dataGridView1.DataSource = dt;

Göreceğiniz şudur

Ben kendi resmimi atmıştım kadrajdan bu kadarı göründü

kayıt olarak ise şu şekilde görünecektir

Dosyamızın binary olarak sorguda geldiğini görüyoruz

Ancak burda sevindirici olan kısım şu ki dosya aslında .mdb üzerinde tutulmadı buda gb larca dosyamız bile olsa mdb mizin kabarmayacağı anlamına geliyor. Örneğin bu seçtiğim dosya 9.18 mb boyutunda idi. bunu sorgu ile çağırabildim sanki tabloya kayıtlıymış gibi ancak db boyutuna baktığımda 4,28 mb olduğunu görüyorum. ancak data klasörüne yeni bir klasör oluşturdu ve içine dosyaları yerleştirdi.

Dosyamızın 9.28 mb daha büyük olması gerekirken orjinal boyutunda kalmış. ancak FileStream klasörü oluşmuş

oysa ki KuzeyYeli_FileStream klasörüne girdiğimizde görüyoruz ki guid bazı klasörler oluşturulmuş ve bunların içinde bir dosya oluşturulmuş bu dosyanın boyudu tam da 9,18 mb. işte bizim dosyamız. sorguda bu dosyayı bize listede getiriyor olsa da aslında mdb dışında bir alanda saklıyor.

Yukarıdaki yoldan da göreceğimiz gibi guid bazı klasörler içinde bizim dosyamız duruyor.

artık bu tabloya ekleyeceğimiz her dosya bu klasör içinde birikecek. ve sorgu ile çağırdığımızda buradan okunup bize teslim edilecek. Tabi bu durumun tablo sorgusunu yavaşlatacağı aşikar. bu yüzden tablonuzu sorgularken sürekli tüm dosyaları yanında istemeyin ki sorgu boyutu büyümesin ve yavaşlama olmasın.

bunun bize en büyük faydası yedeklerde olacak. günlük olarak yedeklenen bir Database miz var ise yedek boyutları dosya sayısıyla şişecektir. ancak bu yöntemde sadece veriyi yedekle diyebiliyoruz. Nasıl mı? FileGroup oluşturduğumuzu hatırlamışsınızdır. yedek alırken PRIMARY filegroup seçilir ise dosyaların bulunduğu “dosyalar filegroup” dışarda kalır ve yedek dosyasının içine alınmaz. tüm DB yi dosyalarla taşımak isterseniz full yedek alabilirsiniz. Aradaki farkı görelim

Tüm Database backup aldığımız zaman dosyalar da dahil oluyor ve Dosya boyutu 13 mb oluyor
Backup Component ten Filegroup seçip Primary grubunu seçersek. Dosyaları yedeklemiyor ve yedek boyutu 4,5 mb oluyor

SQL Express kullanıcıları için kötü haber ise FileGroup seçimi yapılamıyor. (Soldaki Resimde görebilirsiniz.)

Evet özetle bu zamana kadar DB de dosya tutmayı önermiyorduk ancak MSSQL buna çözüm üretmiş ve bunu kullanarak dosyalarınızı DB içinde saklayabilir ve sorgularınızda çağırabilirsiniz.

Habip Oğuz

Yorumunuz... ( 1 )
  1. Vefa Cabbarlı
    Ekim 12, 2019 at 5:25 am
    Cevap ver

    Güzel bilgi için çok teşekkür ederiz 🙂

Cevap yaz