Τι είναι το collation και πως αλλάζει στον SQL Server
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Με μία πρόταση το collation είναι ο τρόπος που κωδικοποιούνται οι χαρακτήρες σε μία βάση δεδομένων. Ορίζετε δηλαδή αν θα είναι case sensitive (κεφαλαία, μικρά), σε τι γλώσσα είναι τα πεδία varchar και text.
Πεδία όπως nvarchar που είναι σε Unicode μορφή δεν επηρεάζονται από την κωδικοποίηση των χαρακτήρων, αλλά επηρεάζει το sort αφού ανάλογα το collation μπορεί να υπάρχει διαφορετική σειρά γραμμάτων ή να επηρεάζεται από το case sensitive.
Το collation στις system databases και στις user databases μπορούσε να είναι διαφορετικό μεταξύ τους από τον SQL Server 2000 και έπειτα.
Πώς μπορούμε να δούμε τι collation έχουν όλες οι βάσεις δεδομένων με query
select name,databasepropertyex(name,'COLLATION'),databasepropertyex(name,'Recovery'),databasepropertyex(name,'STATUS') from master.dbo.sysdatabases
Το collation στη master database ορίζει το collation και στις υπόλοιπες συστημικές βάσεις. Αυτό ενδέχεται να επηρεάσει συστημικές λειτουργίες όπως login στη βάση και sort στην tempdb.
Το collation στις user databases επηρεάζει όπως αναφέραμε πρώτα από όλα σε τι γλώσσα βρίσκονται οι non-unicode χαρακτήρες και εκεί υπάρχει ένα βασικό πρόβλημα όταν αποφασίσουμε να το αλλάξουμε. Ότι ότι δεδομένα είχαν περαστεί μέχρι τη στιγμή της αλλαγής θα παραμείνουν με το collation που είχαν.
Ακόμα και λάθος collation να έχουμε σε μία βάση, μπορούμε να διαβάσουμε το πεδίο με διαφορετικό collation π.χ. σε μία βάση που είναι σε SQL_Latin1_General_CP1253_CI_AI με το κάτωθι query μπορούμε να διαβάσουμε τους Ελληνικούς χαρακτήρες:
select cast (Onoma as varchar(100)) collate Greek_CI_AS from Pelatis
Για να αλλάξουμε το collation στη βάση
Για να αλλάξει το collation στις user databases θα πρέπει να γίνουν τα κάτωθι βήματα ώστε να μην έχουμε το πρόβλημα που αναφέραμε πιο πάνω και να μην έχουν όλες οι εγγραφές στη βάση το ίδιο collation (καλό είναι να τις έχουμε κάνει backup πριν ξεκινήσουμε):
1 – Κάνουμε export το generate script της βάσης (τη δομή της) :
Δεξί κλικ στη βάση, Tasks, Generate scripts… , Script entire database and all database objects
2 – Με το παρακάτω script που έχω φτιάξει κάνει generate τα statements ώστε να μπορούμε να κάνουμε bcp out και bcp in όλη τη βάση. Το εκτελούμε και κρατάμε και τις δύο στήλες.
declare @user varchar(50), @pass varchar(50), @pathout varchar(200), @pathin varchar(200) set @user = 'username' set @pass = 'password' set @pathout = 'c:\backups\' set @pathin = 'c:\backups\' SELECT 'bcp ' --bcp + QUOTENAME(DB_NAME())+ '.' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.' -- + QUOTENAME(name) + ' out ' +@pathout + REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' + REPLACE(name,' ','') + '.txt -S' + REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','') --server name +' -U'+@user+' -P'+@pass+' ' --username pass +'-n -t[cdel] -r[rdel]' , AS BCP_OUT, 'bcp ' --bcp + QUOTENAME(DB_NAME())+ '.' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.' + QUOTENAME(name) + ' in '+@pathin + REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' + REPLACE(name,' ','') + '.txt -S' + REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','') --server name +' -U'+@user+' -P'+@pass+' ' --username pass +'-w -n' +' -e'+@pathin +'logbcpin.txt' +' -t[cdel] -r[rdel]' , AS BCP_IN from sys.tables
3 – Εκτελούμε το bcp out σε command prompt από το βήμα 2.
4 – Σβήνουμε την βάση.
drop database [AdventureWorks2017 ]
5 – Εκτελούμε το script που κάνει create τη βάση από το βήμα 1.
6 – Αλλάζουμε το collation στη βάση.
ALTER DATABASE AdventureWorks2017 GREEK_CI_AI ;GO
7 – Εκτελούμε το bcp in σε command prompt από το βήμα 2. (αφού πρώτα κάνουμε disable τα constraints)
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
8 – Όταν ολοκληρωθεί το bcp in κάνουμε enable τα constraints.
EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"
Για να αλλάξουμε το collation στις συστημικές βάσεις
Για να αλλάξει το collation στις συστημικές βάσεις χρειάζεται rebuild η master και αυτό έχει σαν αποτέλεσμα:
- Να χαθούν όλα τα jobs από την msdb
- Να χαθούν όλα τα logins kai oi ρόλοι
- Να χαθούν τα dblinks
- Να χαθούν όλα τα δικαιώματα που έχουν περαστεί στους ρόλους όπως π.χ. στον public
- Να γίνουν detach όλες οι user databases
- Να χαθούν όλοι οι παράμετροι του instance (που είχαμε στο sp_configure) όπως max_memory, max_dop κλπ.
Η διαδικασία για την αλλαγή του collation στις συστημικές είναι η εξής:
1 – Backup των βάσεων master, msdb, model (με την ανάλογη τροποποίηση των paths στο κάτωθι script που έχω φτιάξει)
DECLARE @date CHAR(8)
SET @date = (SELECT CONVERT(char(8), GETDATE(), 112))
DECLARE @backup_loc VARCHAR(125)
SET @backup_loc = 'X:\backups\' -- Σε ποιά τοποθεσία θα παρθούν τα backups
DECLARE @rest_dbf VARCHAR(125)
SET @rest_dbf = 'D:\Databases\' -- Σε ποιά τοποθεσία θα γίνουν restore τα datafiles
DECLARE @rest_log VARCHAR(125)
SET @rest_log = 'L:\Logfiles\'; -- Σε ποια τοποθεσία θα γίνουν restore τα logfiles
WITH CTE ( DatabaseName, Npath )
AS ( SELECT DISTINCT
DB_NAME(database_id) ,
STUFF((SELECT ' ' + CHAR(13)+', MOVE ''' + name + ''''
+ CASE Type
WHEN 0 THEN ' TO ' +@rest_dbf
ELSE ' TO ' +@rest_log
END
+ REVERSE(LEFT(REVERSE(physical_name),
CHARINDEX('\',
REVERSE(physical_name),
1) - 1)) + ''''
FROM sys.master_files sm1
WHERE sm1.database_id = sm2.database_ID
FOR XML PATH('') ,
TYPE).value('.', 'varchar(max)'), 1, 1, '') AS Npath
FROM sys.master_files sm2
)
--select * from CTE
SELECT
'BACKUP DATABASE ' + name + ' TO DISK = ''' + @backup_loc + '' + name + '_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5' as Backup_Commands_onSource,
'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @backup_loc + '' + name + '_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' + CTE.Npath as Restore_Commands_onTarget
FROM sys.databases d
INNER JOIN CTE ON d.name = cte.databasename
WHERE d.name in('master','model','msdb') --DBs
GO
2 – Backup των job (πατάμε F7 στο SSMS να ανοίξει το object explorer details , επιλέγουμε όλα τα jobs και δεξί click, script job as, create to, file.. όπως στην κάτωθι εικόνα):
3 – Backup logins, users, roles με το query του Greg Ryan:
--Script All Logins / Users / and Roles
/****************************************************************
This Script Generates A script to Create all Logins, Server Roles
, DB Users and DB roles on a SQL Server
Greg Ryan
10/31/2013
****************************************************************/
SET NOCOUNT ON
DECLARE
@sql nvarchar(max)
, @Line int = 1
, @max int = 0
, @@CurDB nvarchar(100) = ''
CREATE TABLE #SQL
(
Idx int IDENTITY
,xSQL nvarchar(max)
)
INSERT INTO #SQL
( xSQL
)
SELECT
'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
+ QUOTENAME(name) + ''')
' + ' CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
+ sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
+ sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE='
+ QUOTENAME(COALESCE(default_database_name , 'master'))
+ ', DEFAULT_LANGUAGE='
+ QUOTENAME(COALESCE(default_language_name , 'us_english'))
+ ', CHECK_EXPIRATION=' + CASE is_expiration_checked
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ', CHECK_POLICY='
+ CASE is_policy_checked
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + '
Go
'
FROM
sys.sql_logins
WHERE
name <> 'sa'
INSERT INTO #SQL
( xSQL
)
SELECT
'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
+ QUOTENAME(name) + ''')
' + ' CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
+ 'DEFAULT_DATABASE='
+ QUOTENAME(COALESCE(default_database_name , 'master'))
+ ', DEFAULT_LANGUAGE='
+ QUOTENAME(COALESCE(default_language_name , 'us_english'))
+ ';
Go
'
FROM
sys.server_principals
WHERE
type IN ( 'U' , 'G' )
AND name NOT IN ( 'BUILTIN\Administrators' ,
'NT AUTHORITY\SYSTEM' );
PRINT '/*****************************************************************************************/'
PRINT '/*************************************** Create Logins ***********************************/'
PRINT '/*****************************************************************************************/'
SELECT
@Max = MAX(idx)
FROM
#SQL
WHILE @Line <= @max
BEGIN
SELECT
@sql = xSql
FROM
#SQL AS s
WHERE
idx = @Line
PRINT @sql
SET @line = @line + 1
END
DROP TABLE #SQL
CREATE TABLE #SQL2
(
Idx int IDENTITY
,xSQL nvarchar(max)
)
INSERT INTO #SQL2
( xSQL
)
SELECT
'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', '
+ QUOTENAME(R.name) + ';
GO
'
FROM
sys.server_principals L
JOIN sys.server_role_members RM
ON L.principal_id = RM.member_principal_id
JOIN sys.server_principals R
ON RM.role_principal_id = R.principal_id
WHERE
L.type IN ( 'U' , 'G' , 'S' )
AND L.name NOT IN ( 'BUILTIN\Administrators' ,
'NT AUTHORITY\SYSTEM' , 'sa' );
PRINT '/*****************************************************************************************/'
PRINT '/******************************Add Server Role Members *******************************/'
PRINT '/*****************************************************************************************/'
SELECT
@Max = MAX(idx)
FROM
#SQL2
SET @line = 1
WHILE @Line <= @max
BEGIN
SELECT
@sql = xSql
FROM
#SQL2 AS s
WHERE
idx = @Line
PRINT @sql
SET @line = @line + 1
END
DROP TABLE #SQL2
PRINT '/*****************************************************************************************/'
PRINT '/*****************Add User and Roles membership to Indivdual Databases********************/'
PRINT '/*****************************************************************************************/'
--Drop Table #Db
CREATE TABLE #Db
(
idx int IDENTITY
,DBName nvarchar(100)
);
INSERT INTO #Db
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name NOT IN ( 'Master' , 'Model' , 'msdb' , 'tempdb' )
ORDER BY
name;
SELECT
@Max = MAX(idx)
FROM
#Db
SET @line = 1
--Select * from #Db
--Exec sp_executesql @SQL
WHILE @line <= @Max
BEGIN
SELECT
@@CurDB = DBName
FROM
#Db
WHERE
idx = @line
SET @SQL = 'Use ' + @@CurDB + '
Declare @@Script NVarChar(4000) = ''''
DECLARE cur CURSOR FOR
Select ''Use ' + @@CurDB + ';
Go
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +
mp.[name] + '''''')
CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +
''GO'' + CHAR(13)+CHAR(10) +
''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']'''';
Go''
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
OPEN cur
FETCH NEXT FROM cur INTO @@Script;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @@Script
FETCH NEXT FROM cur INTO @@Script;
END
CLOSE cur;
DEALLOCATE cur;';
--Print @SQL
Exec sp_executesql @SQL;
--Set @@Script = ''
SET @Line = @Line + 1
END
DROP TABLE #Db
4 – Backup db links, certificates (αν υπάρχουν). Μπορεί να γίνει εύκολα με το γραφικό του SSMS.
5 – Κάνουμε detach τις βάσεις με το κάτωθι script μπορούμε να το αυτοματοποιήσουμε:
USE [master];
GO
DECLARE @database NVARCHAR(200) ,
@cmd NVARCHAR(1000) ,
@detach_cmd NVARCHAR(4000) ,
@attach_cmd NVARCHAR(4000) ,
@file NVARCHAR(1000) ,
@i INT ,
@whichdb INT=0,
@dbname nvarchar(200)='%%',
@Drive_Now nvarchar(10)='',
@Drive_New nvarchar(10)='';
create table #tempattach(attach_script varchar(max));
create table #tempdetach(detach_script varchar(max));
--set 4 for only userdbs, 0 for all dbs
set @whichdb = 4
--if u want to attach db on new drive with same path uncomment with the correct drive letters
--set @Drive_Now = 'C:\' set @Drive_New = 'Z:\'
--uncomment if u pick only one DBname
--set @dbname = '%iDBA%'
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT RTRIM(LTRIM([name]))
FROM sys.databases
WHERE database_id > @whichdb
and name like @dbname;
OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;
SET @attach_cmd =
'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);
-- Change skip checks to false if you want to update statistics before you detach.
SET @detach_cmd =
'EXEC sp_detach_db @dbname = ''' + @database
+ ''' , @skipchecks = ''true'';' + CHAR(10);
-- Get a list of files for the database
DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT physical_name
FROM sys.master_files
WHERE database_id = DB_ID(@database)
ORDER BY [file_id];
OPEN dbfiles_cur
FETCH NEXT FROM dbfiles_cur INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN
SET @attach_cmd = @attach_cmd + ' ,@filename'
+ CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + ''''
+ CHAR(10);
SET @i = @i + 1;
FETCH NEXT FROM dbfiles_cur INTO @file
END
CLOSE dbfiles_cur;
DEALLOCATE dbfiles_cur;
insert into #tempattach values(REPLACE(@attach_cmd,@Drive_Now,@Drive_New));
insert into #tempdetach values(@detach_cmd);
FETCH NEXT FROM dbname_cur INTO @database
END
CLOSE dbname_cur;
DEALLOCATE dbname_cur;
select * from #tempdetach
select * from #tempattach
drop table #tempattach
drop table #tempdetach
6 – Διπλό click στο ISO του SQL Server του οποίου είναι το instance π.χ. 2016
7 – Στο command prompt σαν administrator κάνουμε cd στο drive που έγινε mount το ISO και τρέχουμε την κάτωθι εντολή, αφού πρώτα βάλουμε τα σωστά instance name, windows authentication, και το collation στο οποίο θέλουμε να πάμε:
F:\>cd F: Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=INSTANCENAME\SA /SAPWD=KWDIKOS /SQLCOLLATION=SQL_Latin1_General_CP1253_CI_AI
8 – Η αλλαγή ολοκληρώθηκε οπότε πρέπει να ξαναπεράσουμε τα jobs, logins, παραμέτρους κτλ.
9 – Attach τις user databases. (από τα αποτελέσματα του βήματος 5)
Υπάρχει λάθος. Δεν χρειάζονται τα κόμματα στην ονομασία των στηλών (,AS)
Έχουν δοκιμαστεί τα queries. Λειτουργούν κανονικά.
Πρέπει να είμαστε πιο προσεκτικοί στο τι προτείνουμε διότι μπορεί να παρασύρουμε σε μη αντιστρέψιμα λάθη. Η δομή της βάσης στο σύνολό της δεν γίνετε με αυτό που προτείνετε (SCRIPT DATABASE AS—>File). Αυτό κάνει script τα χαρακτηριστικά της βάσης και όχι τα αντικείμενα αυτής (Tables κλπ). Το αποτέλεσμα είναι ότι όταν κάποιος πάει μετά να περάσει τα δεδομένα δεν υπάρχουν τα αντίστοιχα tables!!! Αν λοιπόν κάποιος δεν είχε κάνει backup ολόκληρη τη βάση τότε θα πρέπει να ξαναφτιάξει τη δομή της από την αρχή με το χέρι!!!
Εκ παραδρομής είχα αναφέρει την επιλογή Script Database αντί του Generate Scripts η οποία εξάγει ολόκληρο το σχήμα της βάσης μαζί με το σχήμα όλων των πινάκων οπότε τα βήματα που αναφέρονται ισχύουν κανονικά.