Visual Studio 2005에는 기본 데이터베이스로 SQL Server 2005 Express 버전이 설치됩니다. 2003 버전에서는 특별한 데이터 베이스가 설치되지 않은 반면 2005에서는 SQL Server 2005 Express 버전이 데스크톱 데이터베이스로 설치되는 것이지요. SQL Server 2005 Express는 기본적으로 SQL Server 2005와 같은 엔진이 사용됩니다. 그러나 사용할 수 있는 CPU, 메모리, 데이터베이스 크기, 기타 데이터베이스 엔진 기능의 제약 등이 있지만 기본적인 데이터베이스 작업(뷰, 저장 프로시저 등)에는 아무런 지장이 없기 때문에 무거운 데이터베이스 설치에 따른 부담을 줄일 수 있지요.

CPU가 빵빵하고 메모리가 충분하다면 SQL Server 2005 Express 버전을 설치하지 않고 SQL Server 2005 Developer Edition을 설치해도 됩니다만 SQL Server 2005 버전은 이전 버전(SQL Server 2000)에 비해 상당히 무거워진 것 같습니다. 간단한 쿼리 몇 개만 날려도 데이터베이스가 차지하는 메모리가 100MB를 훌쩍 넘겨버리곤 하니까요(요즘같이 데스크톱도 2GB 메모리를 꼽는 판에 이정도는 껌으로 볼 수도 있겠슴니다만...). 그래서 노트북이나 1GB 메모리보다 적은 메모리를 가진 데스크톱 등을 가진 컴퓨터는 SQL Server Express가 상당히 매력적이라고 볼 수 있습니다.

SQL Server Express는 보안적인 이유 때문에 기본적으로 네트워크 접속이 Disable 되어 있습니다. 그래서 이전에 SQL Server 2000을 쓰던 생각을 하고 원격 컴퓨터에서 접근하려고 하면 접속이 안 되지요. 이번 포스트는 SQL Server Express에서 원격 접속을 설정하는 방법과 기타 sa 계정 설정, NorthWind 예제 데이터베이스 설치 등을 다루어 볼까 합니다.

Configuration of SQL Server 2005 Express

SQL Server 2005 Express는 SQL Server 2005에 대한 익스프레스 버전 혹은 라이트 버전으로서 SQL Server 2005가 갖는 특징을 대부분 사용할 수 있다. 예를 들어, 일반 저장 프로시저, 트리거 등등은 물론이요, 닷넷 프로그래밍언어로 저장 프로시저를 작성한다던가 새로이 추가된 T-SQL 기능등을 모두 사용할 수 있다. 참고로... SQL Server 2000에 대한 익스프레스 버전은 MSDE(Microsoft Desktop Engine) 2000이라는 버전이 존재한다.

SQL Server 2005 Express 버전(이하 SQL Express)의 의의는 데스크톱 데이터베이스 엔진으로서 RDBMS의 기능을 충분히 다한다는 것이다. 비록 데이터베이스의 크기나 CPU, 사용할 수 있는 메모리의 제약이 있지만 공짜(마이크로소프트의 SQL Server 사이트에서 다운받을 수 있다)로 설치할 수 있고, 많은 메모리를 사용하지 않기 때문에 부담이 적다.

Connecting SQL Server 2005 Express

SQL Express 버전은 보안상의 이유로 원격 접속이 Disable 된 상태로 설치된다. 이렇게 되면, 로컬 컴퓨터(SQL Express가 설치된 컴퓨터)에서 SQL Express를 액세스하는 데는 아무런 문제가 없지만, 원격 컴퓨터에서 SQL Express를 액세스 할 수 없는 것이다. 대개의 경우엔 이러한 설정이 문제가 없을 수 있지만, 공동 개발을 할 때 SQL Express를 데이터베이스로 사용할 때는 문제가 될 수 있다. 자, 그럼 SQL Express를 원격에서 접속할 수 있도록 설정을 바꾸어 보자.

SQL Server 2005 혹은 SQL Express가 설치되면 SQL Server Configuration Manager란 MMC 스냅인이 설치된다. 이 SQL Server Configuration Manager을 이용하여 SQL Server가 어떤 네트워크 프로토콜을 사용할 것인지, 또한 어떤 포트를 사용할 것인지를 설정할 수 있다. SQL Server Configuration  Manager를 수행시키면 화면1과 같이 SQL Server의 네트워크 설정을 확인할 수 있다. 오른쪽 패널에는 기본적으로 Shared Memory를 제외한 Named Pipes, TCP/IP, VIA 가 모두 Disable 상태로 보일 것인데, 이중 원하는 클라이언트 프로토콜(TCP/IP가 가장 무난하며 가장 권고된다)을 Enable 시키면 원격 클라이언트의 접속이 가능하다. 참고로...TCP/IP가 Enable 되지 않은 상황에서도 로컬 컴퓨터에서 SQL Server(Express 포함)를 액세스할 수 있는 이유는 Shared Memory(혹은 프로파일러에는 LPC로 표시된다)를 통해 액세스 하기 때문인 것이다.

화면1. SQL Server Configuration Manager

이렇게 설정하고 SQL Express를 다시 시작해야만 효과가 나타나므로, SQL Express를 다시 시작하면 이제 SQL Express는 가변 포트를 통해 TCP 연결을 리스닝 하기 시작할 것이다.

SQL Server Dynamic Port & SQL Browser Service

SQL Server Instances

SQL Server는 하나의 컴퓨터에 대해 여러 인스턴스가 설치될 수 있다. 즉, 하나의 컴퓨터에 서로 다른 데이터베이스를 보유하는 SQL Server를 여러 번 설치할 수 있다는 얘기이며, 각각의 인스턴스는 자신만의 데이터베이스를 보유하게 된다. 이들 SQL Server 인스턴스는 디폴트 인스턴스와 명명된 인스턴스(named instance)로 나누어 볼 수 있는데, 디폴트 인스턴스는 대개 최초로 설치되는 SQL Server 인스턴스를 말하며 특별한 이름이 붙지 않는다. 디폴트 인스턴스가 설치된 이후에 추가로 설치되는 SQL Server는 디폴트 인스턴스와 구분하기 위해 각 인스턴스 마다 이름이 붙게 된다. 이렇게 이름이 붙은 SQL Server 인스턴스를 명명된 인스턴스라고 한다. 예를 들어 보자. 필자의 데스크톱에는 SQL Server 2000, SQL Express, SQL Server 2005가 모두 설치되어 있다. 이 중 최초로 설치한 SQL Server 2000이 디폴트 인스턴스이며, SQL Express와 SQL Server 2005는 각각 이름이 SQLEXPRESS, SQL2005 인 명명된 인스턴스이다. 그리고 이들 인스턴스들이 모두 동시에 수행될 수 있다. 다시 말하자면 sqlservr.exe 프로세스가 여러개 수행 중일 수 있다는 말이다.

디폴트 인스턴스와 명명된 인스턴스는 설치 시에 결정된다. SQL Server를 설치할 때, 해당 인스턴스를 디폴트 인스턴스로 설치할 것인지, 명명된 인스턴스로 설치할 것인지 결정할 수 있다. 대개, 첫 번째 SQL Server를 설치할 때, 설치 프로그램은 디폴트 인스턴스로 설치하곤 하지만 반드시 그렇게 해야만 하는 것은 아니다. 컴퓨터에 달랑 1개의 SQL Server 인스턴스를 설치하더라도 명명된 인스턴스로 설치가 가능함에 유의하자.

SQL Express의 경우, 항상 명명된 인스턴스로 설치된다. 설치 프로그램은 SQL Express를 설치할 때 SQLEXPRESS 라는 이름을 사용하여 데이터베이스를 설치하며, 이미 이 이름이 존재하는 경우 뒤에 숫자가 붙게 된다.

Referencing SQL Server Instances

그렇다면 하나의 컴퓨터에 여러 SQL Server가 존재할 때, 각각의 인스턴스를 참조하는 방법은 무엇일까? 디폴트 인스턴스는 이름을 갖지 않기 때문에 해당 컴퓨터의 컴퓨터 이름 혹은 IP 로서 참조된다. 반면 명명된 인스턴스는 컴퓨터 이름(혹은 IP)과 인스턴스 이름을 백슬래쉬(\) 문자로 구분하여 모두 명시해 주어야 한다. 예를 들어, DBServer 란 컴퓨터(IP가 10.1.1.10 이라 가정)에 SQL Server 2000 이 디폴트 인스턴스이며, SQL Express 그리고 SQL Server 2005가 SQLEXPRESS, SQL2005란 이름으로 설치되었다고 가정해 보자. 먼저 디폴트 인스턴스인 SQL Server 2000을 참조하기 위해서는 단순히 DBServer(혹은 IP 10.1.1.10)로만 참조하면 디폴트 인스턴스에 접근할 수 있다. SQL Express에 접근하기 위해서는 DBServer\SQLEXPRESS 로, SQL Server 2005에 접근하기 위해서는 DBServer\SQL2005 라는 SQL Server 이름을 사용해야 한다.

Static Port & Dynamic Port

원격 클라이언트가 SQL Server에 접속하기 위해서는 SQL Server가 클라이언트에게 허용한 네트워크 프로토콜 중 하나를 써야만 할 것이다. 가장 많이 사용되고, 권장되는 프로토콜은 당연히 TCP/IP 이다. Named Pipe를 생각해 볼 수 있겠지만, 방화벽이나 라우팅 등등을 고려하면 역시 TCP/IP 이다. 이제 하나의 컴퓨터에 2개 이상의 SQL Server가 설치되어 있고 클라이언트가 이들 중 하나에 접속하고자 할 때, TCP 포트 중 어떤 포트를 써야 할까? 우리가 익히 알고 있는 1433 포트는 설치된 SQL Server 인스턴스 중 어떤 녀석이 사용할까?

우리가 SQL Server가 사용하는 것으로 알고 있는 1433 포트는 디폴트 인스턴스가 사용하는 것이 일반적이다. 그렇다면 다른 명명된 인스턴스는 어떤 포트를 사용할까? 그건 설정에 따라서 다르다. 특정 고정 포트(static port)를 사용하도록 설정할 수 있으며, 임의의 포트(dynamic port)를 사용하도록 설정할 수도 있다. 이러한 설정은 앞서 언급한 SQL Server Configuration Manager를 통해 설정할 수 있다(SQL Server 2000의 경우, "서버 네트워크 유틸리티"를 통해서 설정할 수 있으니 참고 바란다).

화면1에서 TCP/IP 프로토콜의 속성(컨텍스트 메뉴에서 properties 선택) 메뉴를 선택하면 TCP/IP 등록정보(화면2)가 나타나고 여기서 IP Address 탭을 선택하면 서버가 사용할 포트를 지정할 수 있다. 컴퓨터에 설치된 네트워크 카드에 따라 여러 개의 IP에 대해 각각 포트를 다르게 지정하거나 전체(IP All)에 대해 포트를 지정할 수 있다. TCP Dynamic Port 항목의 값을 0으로 설정하면 동적 포트가 할당되고 이 값을 공백으로 지우고, TCP Port 항목에 특정 포트 값을 명시하면 고정된 정적 포트가 사용된다. 이렇게 설정된 포트는 SQL Server가 시작될 때 적용(레지스트리에 기록됨)되므로 값을 수정한 후에는 SQL Express(SQL Server 2005 동일)을 재시작 해야만 한다.

화면2. SQL Express(SQL Server 2005) TCP 포트 설정 대화상자

일반적으로 디폴트 인스턴스가 1433 포트를 그리고 명명된 인스턴스는 동적 포트 혹은 다른 정적 포트를 사용하는 것이 일반적이지만 반드시 그래야만 하는 것은 아니다. 컴퓨터에 하나의 SQL Server 인스턴스만을 설치하면서 이것을 명명된 인스턴스로 설치할 수 있으며, 이 인스턴스가 1433 포트를 사용하도록 설정할 수 있다. 이러한 설정은 Windows 2000/2003 클러스터 환경에서 많이 사용되는 설정이다. 물론, 디폴트 인스턴스와 명명된 인스턴스를 모두 설치하면서, 명명된 인스턴스가 1433 포트를, 디폴트 인스턴스가 다른 포트를 사용하도록 설정 역시 가능하다.

SQL Browser Service

자, 이제 숨 좀 돌리고... SQL Server가 TCP/IP 상에서 사용하는 포트가 어떤 것인지 알아보았으니... 이제, 클라이언트 측면에서 좀 마빡을 굴려보자. 하나의 컴퓨터에 2개 이상의 SQL Server 인스턴스가 설치되었고 이들 중 하나는 1433을 다른 하나는 동적 포트를 사용한다고 가정해 보자. 이때 클라이언트가 동적 포트를 사용하는 인스턴스에 접속하려고 한다면, 클라이언트는 이 인스턴스가 사용하는 TCP 포트를 어떻게 알아낼까? 그리고, 동적 포트를 사용하는 인스턴스는 서버가 재시작 할 때마다 포트가 바뀔 가능성이 농후하다. 매번 클라이언트가 서버의 포트를 알아내는 방법이 없을까?

SQL Server 2005 (Express 포함)에는 정확히 이러한 문제를 해결하기 위해 SQL Browser 서비스가 설치된다. 이 서비스는 레지스트리 등을 검사하여 컴퓨터에 설치된 SQL Server 인스턴스들이 어떤 TCP 포트, Named Pipe를 리스닝 하는가를 검사한다. 그리고 클라이언트가 특정 인스턴스에 대한 접속정보를 요구하면 해당 인스턴스에 접속하기 위한 TCP 포트 혹은 Named Pipe 종점을 알려주게 된다. SQL Server 2000에는 이러한 작업이 SQL Server 서비스 자체에서 이루어졌지만 2005 버전부터는 별도의 SQL Browser 서비스에서 이 작업을 수행하도록 바뀌었다. 이러한 서버 접속 정보를 얻기 위해 SQL Server 2000, 2005는 UDP 1434 포트를 사용한다는 것도 알아 두면 좋다.

Recommendation

동적 포트의 사용은 명명된 SQL Server 인스턴스들에 대한 디폴트 설정이다. 보안을 위해 그렇게 설정한다고 치지만, 매번 변하는 포트는 방화벽을 설정하는데도 애로가 있을 뿐더러, 클라이언트를 작성하는데도 가변적인 면이 있다는 점에서 조금 껄적지근 하다. 또 하나 필자가 동적포트에 부정적인 이유는, 포트를 알아내기 위해 UDP 액세스를 1회 더 해야 한다는 점이다(SQL Native Client가 포트 정보를 캐시할 수도 있겠지만 말이다...). 로컬 네트워크라면 UDP 호출이 빠르게 이루어 지겠지만 원격 네트워크에 존재하는 가변 포트의 SQL Server는 문제를 일으킬 가능성이 대략 농후하다고 할 수 있다.

필자의 권고는 이렇다. 가급적 고정 포트를 사용하도록 SQL Server를 설정하는 것이 설정상 변동이 없이 명확하며, 방화벽 설정이나, 접속하는데 소요되는 시간을 아주 쬐금이나마 줄이는 방법이라고 할 수 있다. 그리고 컴퓨터에 설치된 모든 SQL 인스턴스들이 고정 포트를 사용한다면 SQL Browser Service는 더 이상 필요 없으므로 서비스를 중단해도 된다. 사실 불필요한 서비스가 작동하는 것을 좋아하지 않는 필자로서 가변 동적 포트를 사용하지 않는 주요 이유다. (결벽증인가? -_-)

ADO.NET Connection String

SQL Server에 접속할 때 명시적으로 포트를 주지 않는 경우 디폴트 1433 포트를 사용하게 된다. 이것이 실패하면 다른 포트를 찾기위해 UDP 1434 포트를 사용하는 등의 액션을 취하게 될 것이다. SQL Server가 1433 포트가 아닌 고정된 다른 포트를 사용할 때, 이것을 명시해 줄 수 있는 방법이 있다면 접속 오류를 피하거나 서버 사이의 라운드 트립(round-trip)을 줄이는 방법이 될 것이다. 일반적인 SQL Server Client 모듈은 서버에 대한 별칭(alias)를 기록해 둘 수 있다. SQL Server 2000의 경우 "클라이언트 네트워크 유틸리티"에서 이러한 별칭을 만들 수 있으며, 별칭을 만들 때 접속할 TCP 포트를 명시할 수 있다.

별칭을 사용하지 않는 경우, ADO.NET 연결 문자열에 특정 포트를 지정하는 것 역시 가능하다. 연결 문자열에는 서버를 나타내기 위해 Server 혹은 Data Source 항목을 표시한다. 이 항목에는 SQL Server 이름(컴퓨터 이름\인스턴스 이름)을 명시하는데 여기에 추가로 TCP 포트를 지정해 줄 수 있다. 지정 방법은 서버 이름 뒤에 콤마를 찍고 포트 번호를 명시해 주면된다. 예를 들어 DBServer 란 컴퓨터에 설치된 SQL Express가 TCP 21433 포트를 사용한다면 연결 문자열은 다음과 같이 설정이 가능하다(System.Data.SqlClient 네임스페이스 사용 시).

Connecting SQL Express using SQL Server Authentication

SQL Express를 사용할 때 한가지 불편한 점은, 기본 설치가 Windows 인증만을 허용한다는 것이다. 쉽게 말해서 sa 계정 같은 SQL Server 고유 계정으로는 접속이 안 된다는 것이다. 사실 Windows 인증만을 사용했을 때 보안상 얻을 수 있는 장점은 많다. 하지만 액티브 디렉토리가 사용되지 않는 한 Windows 인증은 오히려 불편한 점을 많이 동반한다. 때문에 SQL Server 인증 모드를 대부분 선호하게 된다.

SQL Express의 기본 셋업 상태는 Windows 인증만을 허용하기 때문에 이것을 바꾸어 줄 필요가 있다. 바꾸는 방법은 레지스트리 값을 수정해 주면 된다. 문제는 SQL Express의 설정 값을 저장하는 레지스트리 키 인데 이것이 그때 그때 다르기 때문에 약간의 주의가 필요하다. 디폴트 인스턴스의 경우에는 이 설정은 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer 키에서 LoginMode 값(DWORD)을 2로 설정하면 되지만 앞서 언급한 대로 SQL Express는 명명된 인스턴스 이기 때문에 레지스트리 키 위치가 다르다.

명명된 인스턴스들은 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.XX\MSSQLServer 에 로그온 모드가 기록되는데, MSSQL.XX는 인스턴스들이 몇 개 설치되있고 그 순서에 따라서 달라진다. 정확한 SQL Express의 레지스트리 값을 알기 위해선 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names 키의 하위 값을 참조하여 SQLExpress가 어떤 MSSQL.XX에 대응되는지 알아내고 해당 레지스트리 키에 접근하여 LoginMode 값을 2로 설정해야 한다.

필자의 노트북에는 SQL Server 2000이 디폴트 인스턴스이고 SQL Express가 SQLExpress란 이름을 가진 명명된 인스턴스이므로 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer 레지스트리 키의 LoginMode 값을 2로 수정하면 되었다.

이제 sa 계정에 대해 패스워드를 설정하면 된다. 적절한 도구 (Query Analyzer, SQL Server Management Studio, osql 등)를 이용하여 윈도우 인증으로 SQL Express에 접속한다. master 데이터베이스 상에서 다음 T-SQL 문장으로 sa 암호를 설정하면 된다.

새로운 사용자 계정을 만들거나 암호를 변경하는 등의 작업은 직접 T-SQL 문장을 구사 하던가(오오... 상당히 빡실 것으로 예상된다), 아니면 SQL Server 클라이언트 도구를 사용하면 된다.

SQL Express Client Tools

SQL Express의 기본 설치에는 기존 엔터프라이즈 관리자나 쿼리 분석기 같은 클라이언트 도구를 포함하고 있지 않다. 서버에 사용자 계정하나 만들려고 해도 osql.exe 같은 커맨드라인 도구를 써서 T-SQL로 사용자 계정을 만드는 작업을 해야 한다. 익숙한 사용자라면 별 문제 없겠지만... 필자 같은 호좁은 같은 일을 하는데도 시간이 많이 소요되기 때문에(도움말 졸라 뒤져야 한다... -_-), GUI 환경을 좀 더 선호하게 된다. SQL Express를 위한 클라이언트 도구는 없을까? 왜 없어 !!! -_-;

SQL Server 2005의 설치 CD에는 클라이언트 도구를 포함하고 있다. 비록 SQL Server 2005 데이터베이스 엔진을 설치하지 않더라도 클라이언트 도구만을 설치하면 SQL Server 2005에 대한 서버 관리, 프로파일(profile), 쿼리 분석 등을 수행할 수 있다. 클라이언트 도구가 이전 SQL Server 2000 버전과 다른 점은 이전에는 엔터프라이즈 관리자(Enterprise Manager)와 쿼리 분석기(Query Analyzer)가 하나로 통합되어 SQL Server Management Studio란 도구가 제공된다는 점이다. 이 SQL Server Management Studio 를 통해 SQL Server 2005 데이터베이스 서버를 관리하거나 쿼리 분석을 위한 도구로 사용할 수 있다. 물론 이 SQL Server 2005 클라이언트 도구들은 모두 SQL Express를 대상으로도 사용할 수 있다.

SQL Server 2005를 구할 수 없는 경우에는 아직 베타이지만 SQL Server Management Studio Express를 다운로드 하여 사용할 수도 있다. 이 도구는 SQL Server 2005 클라이언트 처럼 프로파일링 등의 작업은 할 수 없지만, SQL Express를 관리하고 쿼리를 작성하는 등 기존 엔터프라이즈 관리자 + 쿼리 분석기의 기능을 포함하기 때문에 아쉬운 대로(공짜니깐) 사용할 수 있을 것이다. 하지만 필자는 SQL Server 2005의 클라이언트 도구를 설치하길 권한다.

Installing Northwind Database

필자는 아주 오래 전부터 예제 데이터베이스로 사용된 Northwind 에 관련된 예제 코드를 아주 많이 가지고 있다. 그래서 로컬 컴퓨터에 설치된 SQL Server가 이 데이터베이스를 갖고 있지 않으면, 많은 필자의 예제 코드가 작동하지 않는다. 그래서 SQL Server 2005와 SQL Express에 Northwind 데이터베이스를 설치하곤 한다.

설치 방법은 간단하다. SQL Server 2000의 설치 폴더(대개 C:\Program Files\Microsoft SQL Server\MSSQL 이다)의 install 하위 폴더를 살펴보면 instnwnd.sql 이란 파일이 존재한다. 이 2MB 짜리 텍스트 파일이 Northwind 데이터베이스를 설치하는 SQL 스크립트이다. 이 스크립트의 내용을 복사하여 별도의 스크립트를 만든다. 약간 수정을 해야 하기 때문이다.

이 스크립트의 13-15 라인을 보면 데이터베이스 파일을 작성할 디렉토리를 읽어오는 쿼리가 있는데 이것이 SQL Express나 SQL Server 2005에서 제대로 작동하지 않는다. 따라서 이 세 라인을 다음과 같이 수정해 준다.

디렉토리의 위치는 편한 대로 설정하면 된다. 필자의 경우, SQL Express 의 데이터베이스 기본 폴더로 설정하였다.

---

가볍게 시작한 글이 상당히 길어졌네요... 항상 강조하지만... 데이터베이스에 대한 글은, 제가 전문가가 아니기 때문에 잘못되었을 수 있습니다. 이러한 내용이 발견되면 곧바로 저에게 고자질 해 주세요... 항상 많은 관심 가져주시는 여러 독자분께 감사드립니당...

---

---