[LocalDB] SQL2012 LocalDB의 Collation 문제 해결

 

 

MSSQL 2012의 LocalDB 사용 중 Join, Where명령에 대해서 아래와 같은 오류가 발생했다.

 

Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS" and "Korean_Wansung_CS_AS" in the equal to operation.

 

 

데이터베이스의 데이터정렬 형식이 서로 맞지 않아서 작업을 수행할 수 없다는 메시지이다. 

왜 이러한 현상이 나타났는가?

MSSQL 2012  LocalDB 인스턴스는 기본적으로 'SQL_Latin1_General_CP1_CI_AS' 형식으로 셋팅되며 이를 변경할 수 없다.

 

http://technet.microsoft.com/en-us/library/hh510202.aspx

 

... 

The instance collation for LocalDB is set to SQL_Latin1_General_CP1_CI_AS and cannot be changed. Database-level, column-level, and expression-level collations are supported normally. Contained databases follow the metadata and tempdb collations rules defined by Contained Database Collations.

....

 

 

그래서 LocalDB에 데이터베이스를 생성 할 때에 옵션의 데이터정렬값을 설정해 주지 않으면 기본값인 SQL_Latin1_General_CP1_CI_AS 로 설정된다.

이 데이터정렬셋은 한글 데이터가 들어오면 ???? 과 같이 깨져서 들어오고  제대로 처리를 해주지 못한다.

데이터베이스에서 한글을 쓰려면  데이터베이스 생성시 Korean_Wansung_CS_AS 등으로 선택해 주어야 한다.

 

물론 데이터베이스가 생성된 이후에도  데이터정렬값을 바꿔줄 수는 있는데, 그 전에 생성한 테이블의 컬럼값들 중 string형(nVarchar, varchar 등) 컬럼들은  여전히 SQL_Latin1_General_CP1_CI_AS 로 설정되어 있고, 데이터정렬 설정 이후에 만들어진 테이블, 컬럼에만 Korean_Wansung_CS_AS 이 적용된다.

 

이 포스팅에서는 이미  SQL_Latin1_General_CP1_CI_AS 로 만들어진 테이블과 컬럼의 Collation 설정을 변경하는 방법을 알아본다.

 

 

 

 

sp_help TABLENAME

 

테이블의 정보를 조회할 수 있다. 두번째 결과테이블에 Column리스트업되고 Collation값을 확인할 수 있다.

숫자형 컬럼은 NULL 이고 문자열타입 컬럼에만 현재 Collation값이 보여진다.

 

 

이제 변경할 컬럼을 찾았으니 값을 변경해 준다.

 

ALTER TABLE T_TYPE
ALTER COLUMN 유형명 NVARCHAR(800) COLLATE Korean_Wansung_CI_AS;

ALTER TABLE T_TYPE
ALTER COLUMN 유형텍스트 NVARCHAR(1000) COLLATE Korean_Wansung_CI_AS;

ALTER TABLE T_TYPE
ALTER COLUMN USE_YN NVARCHAR(5) COLLATE Korean_Wansung_CI_AS;

 

 

Collation이 잘못 들어간 다른 테이블의 컬럼도 모두 찾아서 위와 같은 방법으로 바꿔준다.

 

물론 데이터베이스를 생성할 때 미리 Collation값을 지정해 줬다면 위와 같이 번거로운 작업을 하지 않아도 된다.

 

저작자표시 동일조건

'MS-SQL' 카테고리의 다른 글

[ACCESS] Round 함수의 결과값 반환에 대한 이해.  (0) 2013.07.12
[ACCESS] 엑세스의 Round 함수의 버그? 오작동 사례.  (0) 2013.07.04
써보면 편리한 MSSQL Split 함수 세 개.  (3) 2012.12.29
MS-SQL Server 2012 – LocalDB를 사용해보자. (ADO Connection)  (0) 2012.11.03
오류 : 사용자 'sa'의 계정이 현재 잠겨 있으므로 로그인하지 못했습니다. 시스템 관리자가 잠금을 해제할 수 있습니다.  (0) 2011.12.29

   

Microsoft SQL Server 2012 Express 에는 새롭게 LocalDB 제품이 포함되었다. 

 

 Express 제품군의 새로운 버전인 LocalDB는 모든 프로그래밍 기능을 포함하지만 사용자 모드에서 실행되며 구성이 필요 없는 빠른 설치가 가능하고 필수 구성 요소가 적은 새로운 경량 버전의 Express입니다. 이 제품을 사용하면 코드를 통해 쉽게 데이터베이스를 만들고 데이터베이스에 대한 작업을 수행할 수 있습니다. 이 제품은 Visual Studio와 같은 데이터베이스 개발 도구 및 응용 프로그램과 함께 제공되거나 로컬 데이터베이스가 필요한 응용 프로그램에 포함될 수 있습니다.

 

로컬에서만 사용할 응용 프로그램에 DB가 필요할 때,
Access나 Excel 파일을 DB 대용으로 사용하기엔 좀 버거울 경우 사용하면 좋을 것 같다.

 

LocalDB는 무료이면서도 복잡한 설치과정이 없고 기존 MS-SQL Server의 MDF와의 호환성도 완벽하게 보장되는 로컬 데이터베이스이다.

 

가장 좋은 점은 뭐니뭐니 해도 기존의 MS-SQL 파일 포맷인 MDF와 LDF를 100% 그대로 가져다 쓸수 있다는 점일 것이다.

 

   

또한 Express Edition보다 설치과정이 쉽고 간단하다.

약 33MB(64bit 기준) 정도의 SqlLocalDB.msi 파일과 5MB(64bit) 정도의 SQL Native Client 만 설치하면 일반 PC에서도 강력한 MS-SQL Server의 기능을 마음껏 활용할 수 있게 된다.

단, LocalDB 자체가 윈도우XP는 지원하지 않기 때문에... Vista 이상의 운영체제를 사용해야 한다.

 

설치파일은 MS 공식 다운로드 사이트에서 받을 수 있다.
http://www.microsoft.com/ko-kr/download/details.aspx?id=29062

   

 

설치 자체는 매우 간단하다. 다운로드 받은 파일을 실행하면 별다른 과정 없이 설치가 완료된다.

 

기존의 서버류 제품들과는 다르게, LocalDB는 작업 인스턴스가 실행되지도 않고, 서비스 등록을 하지도 않는다.

응용 프로그램에서 LocalDB 커넥션 스트링을 호출하는 순간에 sqlservr.exe 프로세스가 자동으로 실행되면서 DB와 연결이 된다.

이로 인해 PC를 켜고나서 최초로 LocalDB 응용 프로그램을 실행할 때에는 약간의 지연시간이 발생하게 된다.

 

MSDN에도 이 부분에 대해 노트를 해 두었다.

 

참고

컴퓨터에서 사용자가 처음으로 LocalDB에 연결하려고 시도하는 경우 자동 인스턴스가 생성되고 시작되어야 합니다. 인스턴스를 만드는 추가 시간으로 인해 시간 초과 메시지와 함께 연결 시도가 실패할 수 있습니다. 이 경우 만들기 프로세스가 완료되도록 몇 초 정도 기다린 후에 다시 연결하십시오.

 

 

뭐 실행해본 결과 체감시간에 그다지 큰 변화는 없었다. Sqlservr.exe 파일은 C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn 에 설치가 되어 있으며, 크기가 187KB로 상당히 컴팩트하다.

 

최초 실행시 인스턴트 생성으로 인한 로스나 패널티는… 무시해도 되는 수준이라고 생각된다.

 

 

 

이제 이 상태에서는 Visual Stutio를 통한 .NET 개발을 진행할 수 있다.

(다만, .net Framework가 SqlClient 제공자를 알고 있어야 커넥션이 가능하기 때문에 닷넷프레임워크 4.0을 업데이트 해줘야 한다.)

Microsoft .NET Framework 4용 업데이트 4.0.2 - 런타임 업데이트(KB2544514)

 

 

VS에서 localdb를 커넥션하고 이용하는 방법은 마이크로소프트 2012년 1월호에 자세히 나와 있어서 생략한다.(이곳에서 확인 가능)

 

 

여기서는 일반적인 ADO를 이용해 접속하는 방법에 대해 알아보겠다.

 

VS에서 닷넷 프레임워크를 4.0.2로 업데이트 한 것과 마찬가지로, 일반 ADO에서 커넥션을 하려면 공급자를 설치해 줘야 한다.

localDB는 [sqlncli11] 공급자가 필요한데, SQL Server Native Client 패키지 안에 포함된 sqlncli.msi 를 설치하면 된다.

아래 링크에서 아래쪽으로 쭉 내려가다 보면 Native Client가 보인다.

 

http://www.microsoft.com/ko-kr/download/details.aspx?id=29065

 

Microsoft® SQL Server® 2012 Native Client

Microsoft SQL Server Native Client(SQL Server Native Client)는 SQL OLE DB Provider와 SQL ODBC 드라이버를 포함하는 단일 DLL(동적 연결 라이브러리)입니다. 여기에는 네이티브 코드 API(ODBC, OLE DB 및 ADO)를 사용하여 Microsoft SQL Server 2005, 2008, 2008 R2 및 SQL Server 2012에 연결하는 응용 프로그램에 대한 런타임 지원이 포함되어 있습니다. SQL Server Native Client는 새로운 SQL Server 2012 기능 활용에 필요한 새로운 응용 프로그램을 작성하거나 기존 응용 프로그램을 향상시키는 데 사용됩니다. SQL Server Native Client에 대한 이러한 재배포 가능 설치 관리자는 새로운 SQL Server 코드 이름 'Denali' 기능을 활용하기 위해 런타임에 필요한 클라이언트 구성 요소를 설치하고, SQL Server Native Client API를 사용하는 응용 프로그램을 개발하는 데 필요한 헤더 파일을 설치하기도 합니다.

X86 패키지(sqlncli.msi)
X64 패키지(sqlncli.msi)

 

X86 패키지는 약 3.2MB, x64 패키지는 5.1MB이다. SqlLocalDB.msi와 마찬가지로 설치는… 별다른 설정 없이 그냥 하면 된다. SSMS만 깔려고 해도 이것저것 서비스팩이며 뭐며 귀찮은데.. Express버전도 설치할라치면 온갖 설정과 계정 등 복잡한 요소가 많은데.. 정말 단순해서 좋다.

 

고객들에게 프로그램을 제공해 줄 때 이것저것 잔뜩 설치하라고 하지 않아도 되니까..

 

여기까지 설치가 되었으면 이제 ADO 커넥션을 위한 모든 준비가 완료되었다.

 

 

 

VB등의 프로그램에서 기존에 OLEDB로 SQL Server에 연결하던 대로 참조를 걸어준다. – (Microsoft ActiveX Data Object 2.6 Library – 2.8도 있고 6.0도 있지만 난 그냥 그나마 오류가 덜 나고 하위호환이 잘 되는 것 같은 2.6을 사용한다.. 사실 별 차이를 모르겠음 -..-)

 

 

 

커넥션 스트링을 만들고 접속해본다.

Dim mydb As ADODB.Connection

Dim myrs As ADODB.Recordset

 

mydsn = ""

mydsn = mydsn & "Provider=SQLNCLI11;Data Source=(localdb)\v11.0;Integrated Security=SSPI;"

 

Set mydb = New ADODB.Connection

mydb.Open (mydsn)

 

mydb.state 를 찍어봐서 1이 나오면 정상적으로 연결된 것이다.

 

나의 경우, Sql Server에서 돌리고 있던 MDF와 LDF를 그대로 내 하드디스크로 복사해 왔다.

기존에 SQL SERVER를 커넥트 하던 솔루션에서 커넥션 스트링만 아래처럼 변경해 준 후 돌려보니 에러메시지 한줄 없이 그대로 LocalDB를 이용해 정상 실행 되었다.

 

mydsn = ""

mydsn = mydsn & "Provider=SQLNCLI11;Data Source=(localdb)\v11.0;Integrated Security=SSPI;"

mydsn = mydsn & "AttachDbFileName=C:\LocalDBFile\ LEEJUNGNAM_ACA.mdf"

 

정말 완벽하게 100% 호환이 되는것일까? 해서 SSMS로 접속을 해 보았다.

MSDN에서 본 대루.. 서버이름은 (localdb)\v11.0

저 서버이름의 v11.0은 명명된 네임스페이스에 속하는 특수한 인스턴스 이름 패턴으로, 꼭 저대로 사용해야만 한다고 한다.

어쨌든… 접속.

 

안된다. SSMS 2008에서는 (localdb)\v11.0 이라는 서버의 존재를 인식하지 못한다.

 

해서 SSMS 2012버전을 설치했다. LocalDB 를 다운로드 했던 페이지에 같이 있다.

헌데.. SSMS 2008은 200메가 남짓이더니 2012는 600메가가 넘는 파일을 다운로드 하네.. 괜히 싫다..

 

아무튼 설치를 마치고 다시 접속을 시도.

 

연결이 잘 된다. 개체탐색기를 보니 아래와 같이 한번 커넥션 했던 개체들에 대해서 자동으로 데이터베이스 등록이 되어있다.

저장 프로시저와 함수, 테이블구조, 데이터, id 시드값, 심지어 다이어그램까지 모든 환경이 100% 그대로 호환된다.

 

헌데, SQL Server를 이용해서 잘 만들어져 있는 프로그램을 왜 굳이 로컬DB로 바꾸는 것일까?

고객은 자신의 데이터가 자기의 PC가 아닌 다른 곳에 존재하고, 누군가가 그걸 들여다볼 수 있다는 사실에 꺼림칙해 한다.

그렇다고, 매 고객마다 비싼 MS-SQL Server를 호스팅받아서 마련하라고 할 수도 없는 노릇이다.

고객이 MS-SQL Server를 마련한다고 해도, 어차피 프로그램이 구동되려면 커넥션스트링에 DB 아이디와 비번이 들어가야 하므로 개발/공급한 사람은 언제든지 DB에 접근할 수 있다.

또 개발해준 입장에서는 DB 스키마나 프로시저 등 고유한 솔루션의 소스코드까지 몽땅 권리를 넘겨준 것이 아니므로,, 고객의 SQL Server에 이 프로그램을 구축해주기가 찜찜한건 마찬가지다.

또 이상하게도, 자기PC에 파일을 보관하는 것보다 IDC에 존재하는 SQL Server의 데이터 안정성이 훨씬더 높은데도 불구하고, 그 호스팅하는 업체가 망하거나(-_-), DB가 따운되면 프로그램을 이용하지 못하는게 아니냐는 걱정을 한다.. 거 참..-_-;;

그래서.. LocalDB를 이용하면 위와 같은 문제에 대해서 고객과 개발사 양쪽이 어느정도 합의가능한 도출점을 찾기가 쉬워진다.

고객은 자신이 원하는 바 대로 로컬PC에 자신들의 데이터를 보존할 수 있고,(물론 안전성 측면에서는 몇배 취약하지만..)

외부의 다른 접근이 불가능 하므로 보안을 유지할 수도 있다.

개발사의 입장에서는 개발이 용이한 SQL 솔루션을 그대로 유지하고 별다른 마이그레이션이나 작업을 할 필요가 없다.

물론 고객이 SSMS를 설치해서 DB구조나 소스를 볼 수는 있다. localDB는 기본적으로 윈도우 사용자인증이기 때문에..

하지만 권리를 넘겨 준 만큼 책임에서도 자유로워 질 수 있다.

고객이 DB를 접근하고, 제어하는 순간 그 뒤 발생하는 모든 오류나 문제에 대해서 책임지지 않아도 된다.

데이터 보관 및 백업에 대한 책임도 없다.

 

매 번 localDB를 사용할 일은 없겠지만,

필요에 따라 사용하기엔 더할나위없이 좋은 기능이라 생각된다.

'MS-SQL' 카테고리의 다른 글

[ACCESS] Round 함수의 결과값 반환에 대한 이해.  (0) 2013.07.12
[ACCESS] 엑세스의 Round 함수의 버그? 오작동 사례.  (0) 2013.07.04
써보면 편리한 MSSQL Split 함수 세 개.  (3) 2012.12.29
오류 : 사용자 'sa'의 계정이 현재 잠겨 있으므로 로그인하지 못했습니다. 시스템 관리자가 잠금을 해제할 수 있습니다.  (0) 2011.12.29
[SQL Query] 차집합의 실 성능 비교.  (0) 2011.12.08

+ Recent posts

티스토리툴바