Veritabanı ve mevcut tablolarının, kolonlarının collation’ınını değiştirmek

Bazı durumlarda veritabanı collation değişikliği gerekebilir. Birçok kişi veritabanı seviyesinde collation’ı değiştirmenin durumu düzelteceğini düşünür. Fakat veritabanı bazında collation’ı değiştirdiğinizde mevcut tablolardaki kolonların collation’ı değişmez. Bu makalede bu sorunu nasıl çözeceğimizi bulabilirsiniz.

Öncelikle verinizin güvenliği için backup almayı ihmal etmeyin.

Aşağıdaki kod yardımıyla veritabanı bazında collation’ ı değiştirebilirsiniz.

USE master
GO
SELECT DATABASEPROPERTYEX('test','COLLATION')
GO
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE test 
GO    
ALTER DATABASE test COLLATE Turkish_CI_AI
GO
SELECT DATABASEPROPERTYEX('test','COLLATION')
GO

ALTER DATABASE test SET MULTI_USER

Fakat veritabanı collation değişikliği mevcut tablolar ve kolonlar için geçerli değildir. Eğer mevcut tablolar ve tüm kolonlar için de aynı değişikliği yapmak isterseniz yukarıdaki script’i çalıştırdıktan sonra aşağıdaki script’i çalıştırmalısınız.

declare @NewCollation sysname
set @NewCollation = 'Turkish_CI_AS'
select
    'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(stable.schema_id)) + '.' + QUOTENAME(stable.name) +
    ' ALTER COLUMN ' + QUOTENAME(scol.name) + ' ' + stype.name + '(' +
    CASE WHEN scol.max_length = -1 THEN 'max' ELSE CONVERT(varchar(10),scol.max_length) END +
    ') collate ' + @NewCollation + '
    go
    '
from sys.columns scol inner join sys.tables stable    on scol.object_id = stable.object_id
        inner join sys.types stype on scol.user_type_id = stype.user_type_id

where  scol.collation_name is not null and OBJECTPROPERTY(stable.object_id,N'IsMSShipped')=0

Veritabanını silme işleminin uzun sürmesini engellemek

Veritabanını silerken aşağıdaki resimde görüldüğü gibi Delete backup and restore history information for databases sekmesindeki tik işaretini kaldırıp, Close existing connections sekmesini işaretlerseniz veritabanı silme işlemi hızlı bir şekilde gerçekleşecektir.

Veritabanı file(dosya)’larının disklerini değiştirmek

Bazı durumlarda veritabanı dosyalarının olduğu disklerde yer kalmayabilir. Bu nedenle veritabanı dosyalarından bazılarını ya da hepsini başka disklere aktarmanız gerekebilir. Bu işlemin birkaç yolu vardır. Biz bu makalede en çok kullanılan 2 yolundan bahsedeceğiz.

1.Yöntem:

Detach işlemini yapmadan önce veritabanı dosyalarının hangi path’lerde olduğunu kaydetmek için aşağıdaki script’in sonucunu bir yere kaydetmeniz gerekir.

use AdventureWorks2012
GO
select * from sys.database_files

Aşağıdaki resimde görüldüğü üzere veritabanı üzerinde sağ tıklayarak task sekmesinden detach’i seçerek öncelikle veritabanının sql server ile bağlantısını kesmemiz gerekir.

Detach sekmesine tıkladığımızda veritabanı üzerinde connection varsa bu connection’ların detach işlemi öncesinde sonlandırılması için aşağıdaki gibi Drop Connections’ı seçmemiz gerekir.

Detach işlemi yine gerçekleşmediyse yukarıdaki resimde Script yazan yeri tıklayarak SQL Server’ın oluşturduğu script’in üst kısmına aşağıdaki script’i ekleyerek çalıştırmalısınız.

ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Detach işlemini yapmadan önce uygulamaya herhangi bir istek gelmediğinden emin olmanız gerekir.

Bundan emin olmanın en iyi yöntemi uygulamacı ile görüşüp uygulamayı durdurmasını istemek ve sonrasında

Login’i disable etmektir.

SQL Server Management Studio üzerinden Security->Logins tabından uygulamayı kullanan login üzerinde sağ tıklayarak properties diyoruz ve daha sonra açılan sekmeden aşağıda görüldüğü gibi Status sekmesinden Disabled’ı seçiyoruz ve ok’e basıyoruz.

Detach işlemi tamamlandıktan sonra veritabanı dosyalarından taşımak istediklerinizi istediğiniz diskte windows üzerinden taşıma işlemini copy paste yöntemiyle gerçekleştirmeniz gerekiyor.

Taşıma işlemi bittikten sonra aşağıda görüldüğü gibi SSMS üzerinden Databases’e sağ tıklayarak Attach diyoruz.

Add diyerek veritabanımızın mdf file’ının olduğu path’e giderek mdf file’ına çift tıklıyoruz. Mdf file’ı seçtikten sonra SQL Server otomatik olarak diğer file’ları eski path’lerini gösterecek şekilde listeleyecektir.  CurrentFilePath sekmesinden dosyaları kopyaladığımız yeni path’i göstererek ok’e tıklıyoruz. Ve daha sonra login’i disable ettiğimiz yerden tekrar enable ederek işlemi tamamlamış oluyoruz. Bu yöntem her ne kadar basit gözükse de büyük veritabanlarında kesinti süresi çok uzun süreceği için tercih edilmez.

2.Yöntem

Bu yöntemde kesinti süresi çok azdır.

Öncelikle veritabanının full backup’ını aşağıdaki şekilde yeterli alan olan bir diske alıyoruz.

Veritabanı üzerine sağ tıklayarak Tasks->backup diyoruz. Destination kısmında add diyerek backup alacağımız path’i ve  backup ismini belirleyip ok diyoruz.

Media options’dan Reliability kısmında Verify when backup finished’ı tıklarsanız backup tamamlandıktan sonra backup’ın restore edilebilirlik testini yapacaktır.

Ayrıca Backup Options’dan Compression kısmında Compress Backup’ı seçerseniz backup’ınızı sıkıştıracak ve boyutunu küçültecektir.

Backup bittikten sonra Databases sekmesinden sağ tıklayarak Restore Databases’ı seçiyorsunuz. Buradan Device’ı seçerek Device’ın sağ tarafında 3 nokta olan buton’a tıklıyorsunuz. Açılan sekmeden Add diyerek backup’ı aldığınız path’e giderek aldığınız backup dosyasına çift tıklıyarak ok diyorsunuz.

SQL Server back dosyasını görüp restore edilebilir bir backup olup olmadığına bakıyor ve size Backup sets to Restore kısmında backup’a ait bilgileri listeliyor. Destination kısmında Database ismi otomatik geliyor. Fakat bunu değiştirmelisiniz. Örneğin aşağıdaki resimdeki gibi sonuna Yeni kelimesini ekleyebilirsiniz.

Sonraki aşamada Yukarıdaki resimdeki Files sekmesine geçiyorsunuz. Restore as kısmına gelerek veritabanı dosyalarını hangi diske taşımak istiyorsanız disk path’ini o şekilde değiştiriyorsunuz.

Daha sonra options sekmesine gelerek recovery state kısmında restore with norecovery’i seçiyorsunuz ve ok’e tıklıyorsunuz. Küçük veritabanlarında işlem yaparken ok’e tıklayabilirsiniz fakat büyük veritabanları üzerinde herhangi bir işlem yaparken SSMS üzerinden ok’e tıklamamanız gerekir. Script tuşuna bastığınızda size o işleme ait tüm script’i oluşturacaktır ve daha sonra o script’i çalıştırabilirsiniz.

Burada önemli nokta şu: Restore with no recovery.  Bu seçeneği seçerek veritabanını kullanıma açmadık ve üzerine fark backup’ı yükleyebilir halde bırakmış olduk.

Bir sonraki aşamada Veritabanı üzerinde sağ tıklayarak Task->Backup’ı seçiyoruz. Bu seger Backup Type kısmından TransactionLog’u seçiyoruz ve full backup alırken yaptığımı diğer işlemleri tekrarlayarak log backup’ını alıyoruz. Farklı olarak backup dosyasının uzantısı full backupta .BAK iken Log Backup’ta .trn dir.

Veritabanını kullanan login’i 1.yöntemde anlattığım şekilde disable ediyoruz.

Sonuna Yeni eklediğimiz ve restoring mode da olan veritabanı üzerine sağ tıklayarak Task->Restore->TransactionLog’u seçiyoruz. Açılan sekmeden From device ı seçip sağ taraftaki 3 noktaya tıklıyoruz ve aldığımız log backup’ı seçiyoruz. Daha sonra Restore yazısının altındaki kutucuğu seçerek ok’e basıyoruz. Eğer başka restore edeceğimiz bir log backup dosyası daha olsaydı options sekmesinden restore with norecovery’i seçerek log dosyamızı restore etmemiz gerekirdi. Sadece son log dosyasının restore’unda restore with recovery ile işlemi gerçekleştiriyoruz. Bu işlemde sadece bir logbackup dosyasını restore edeceğimiz için biz hiçbir şey seçmeden bu işlemi gerçekleştirdik.

Veritabanımızın dosyalarını backup restore yöntemiyle gerçekleştirdik fakat hala eski veritabanımız duruyor ve restore ettiğimiz veritabanının isminin sonunda Yeni kelimesi var. Aşağıdaki script yardımıyla da bu işlemi gerçekleştiriyoruz.

ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_renamedb 'AdventureWorks2014','AdventureWorks2014Eski'
GO
ALTER DATABASE AdventureWorks2014Eski SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE AdventureWorks2014Eski SET READ_ONLY
 
 
 
ALTER DATABASE AdventureWorks2014Yeni SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_renamedb 'AdventureWorks2014Yeni','AdventureWorks2014'
GO
ALTER DATABASE AdventureWorks2014 SET MULTI_USER WITH ROLLBACK IMMEDIATE

Ve script çalıştıktan sonra eski veritabanımızı silip Login’imizi yeniden enable ediyoruz.

Bu yöntemle veritabanı ne kadar büyük olursa olsun çok kısa bir kesintiyle veritabanımızın diskini değiştirebiliriz.

Log Dosyasının Truncate ya da Shrink Olmaması ve Diski Doldurması Durumu(CDC)

Normal şartlarda full recovery model veritabanınızda düzenli olarak log backup alıyorsanız, alınan her log backup’tan sonra sql server log dosyasını truncate eder ve bu şekilde log dosyasının büyümesi engellenmiş olur.

Eğer veritabanınızda CDC(Change Data Capture) enable edildiyse aldığınız log backuplar sonrasında log dosyası truncate olmayacak ve disklerinizi doldurmaya başlayacaktır.

Change Data Capture hakkında detaylı bilgi almak için “Change Data Capture(CDC)” isimli makaleyi okumak isteyebilirsiniz.

Normalde başka koşullarda da log dosyası backup aldıktan sonra truncate olmayabilir. Log dosyasının neden truncate olmadığını aşağıdaki sorgu yardımıyla öğrenebiliriz.

USE mydatabase
GO
select log_reuse_wait_desc from sys.databases where database_id=DB_ID()

Eğer sonuç olarak  “LOG_BACKUP” geliyorsa veritabanının log backup’ı alınmamış demektir. Log dosyasını shrink etmek için log bakcup almalısınız. Eğer full recovery model kullanıyorsanız düzenli olarak log backup almak için bir job oluşturmalısınız. “Veritabanı Recovery Modelleri” isimli makalemi okumak isteyebilirsiniz.

Eğer sonuç olarak “replication” geliyorsa ve veritabanı üzerinde herhangi bir replication yapılmamışsa bu veritabanınızda CDC’nin enable edildiği anlamına gelir.

Eğer sonuç olarak “ACTIVE_TRANSACTION” geliyorsa “Always ON’daki Primary Veritabanının Log Dosyasının ACTIVE_TRANSACTION Nedeniyle Dolması” isimli makaleyi okumalısınız.

CDC’yi disable etmek için aşağıdaki komutu kullanmalısınız.

USE mydatabase
GO
EXEC sys.sp_cdc_disable_db
GO

CDC’yi disable ettikten sonra bir süre log dosyasını shrink edemeyeceksiniz.

Bunun sebebi CHECKPOINT işleminin henüz gerçekleşmemesidir.

CHECKPOINT işlemi gerçekleştiğinde log_reuse_wait_desc kolonunda nothing yazacaktır ve log dosyası shrink edilebilir hale gelecektir.

CHECKPOINT işleminin detaylarını “Database Checkpoint Nedir” isimli makalemde bulabilirsiniz.

Eğer log dosyasında oluşan problemi önceden tespit edip gerekli önlemleri almak isterseniz “SQL Server Log Dosyası Problem Tespiti” isimli makaleyi okumanızı tavsiye ederim.