이번 포스트는 간단한 팁으로서 Visual Studio 2010 이나 SQL Server Management Studio 에서 SQL Server Express 기반의 .mdf 파일 데이터 베이스에 접속하는 방법을 소개하고자 합니다. 적어도 저에게는 상당한 편리한 팁이었습니다만 독자 분들께는 어떨지 모르겠네요….

Connecting SQL Server Express .mdf File Based DB

이번 포스트는 쉬어가는(응? 쭉 많이 쉬었자나!) 포스트로써 Visual Studio 2010 이나 SQL Server Management Studio에서 SQL Server Express .mdf 파일 데이터 베이스에 접속하는 방법을 살펴보도록 하겠다. 대형 떡밥을 투척하는 것이 블로그의 인기를 올리는데 도움이 되겠지만 요즘 필자도 숏나게 바쁜 관계로… (아 잠이 부족해…)

개념: SQL Server 사용자 인스턴스

SQL Server Express는 사용자 인스턴스(User Instance)라고 하는 기능을 SQL Server 2005 Express부터 제공해 왔다. 사용자 인스턴스라고 하니깐 조낸 있어 보이고 첨 들어보는 것 같아 보이지만 여러분들은 이미 한번쯤 사용자 인스턴스를 사용해 보았거나 지금도 사용하고 있을지도 모른다.

쉽게 말해서 SQL Server 데이터베이스 파일인 .mdf 파일을 SQL Server에 연결(attach)하지 않고도 어플리케이션에서 직접 사용하는 것을 말한다. Visual Studio에서 ASP.NET 웹 프로젝트 템플릿을 통해 웹 어플리케이션 프로젝트를 만들면 이 웹 어플리케이션의 멤버십 프로바이더가 App_Data 디렉터리에 ASPNETDB.MDF 파일을 만들고 테이블을 생성하는데 바로 요 ASPNETDB.MDF 파일이 SQL Server Express의 사용자 인스턴스 기능을 활용하는 것이 되겠다. 쉽게 이야기 하면 .mdf 파일을 자동으로 연결(attach)하는 SQL Server 인스턴스(sqlserver.exe)가 구동된다는 말이 되겠다.

SQL Server 사용자 인스턴스는 오직 Express 에디션에서만 제공하는 기능으로써 반드시 SQL Server Express가 설치되어 있고 수행 중이어야만 한다. SQL Server 사용자 인스턴스는 이 기능을 사용하는 사용자의 계정으로 SQL Server 인스턴스(sqlserver.exe 라니깐)가 수행되므로 관리자 권한이 없는 상황에서 유용하게 써먹거나 데이터베이스 배포를 초큼이나마 편리하게(SQL Server Express가 설치되어 있다고 가정하면 꼴랑 .mdf, .ldf 파일만 배포하면 된다) 할 수 있다. SQL Server 사용자 인스턴스에 대한 좀 더 상세한 내용은 다음 링크를 꾸욱 눌러 보자.

비관리자용 사용자 인스턴스

위 자료에는 사용자 인스턴스가 무엇이고 왜 사용자 인스턴스를 사용하면 좋은 가 등등이 훌륭하게 설명되어 있다.

편리한 도구로 .mdf 데이터베이스에 접속하고 시포…

간단한 어플리케이션이나 테스트 코드를 작성할 때에는 사용자 인스턴스는 상당히 편리하다. 파일 기반이지만 몇 가지 제약을 제외하면 분산 트랜잭션을 포함하여 SQL Server의 거의 모든 기능을 사용할 수 있기 때문에 간단한 코드를 위한 데이터베이스를 만들기에 편리하고, 나중에 이 데이터베이스 스키마를 Express 사용자 인스턴스가 아닌 실제 SQL Server로 옮기기도 편리하다. 필자도 Entity Framework 나 LINQ to SQL 과 같은 기능을 테스트 할 때 즐겨 사용하고 있다.

그런데… 일반 SQL Server 나 SQL Server Express의 데이터베이스와 달리 Management Studio에서 .mdf 파일의 사용자 인스턴스에 접속하기가 막막했다. Visual Studio의 Server Explorer에서 제공하는 조낸 허접한 쿼리 디자이너를 쓰자니 너무 불편하기도 하고 Visual Studio 2010에는 Transaction-SQL Editor(Data 메뉴에 있다)까지 제공하기도 하는데 Management Studio에서 당췌 사용자 인스턴스에 접속하는 방법을 찾을 수 없었다. 약간의 구글질 후에 시간이 없어서 지지 치고 잊었다가 한 두어 달 전 필자가 엔티티 프레임워크에 잠시 삘이 꼽혔을 때에 겨우 방법을 찾아 냈다. 방법은 다음 링크에 상세히 설명되어 있다.

방법: 사용자 인스턴스에 연결

링크 클릭하고 읽어보는 것 조차 귀찮아 하는 독자들을 위해서 간단히 설명하자면 이렇다. 먼저 최소한 한번은 사용자 인스턴스를 구동해야 한다. SQL Server Express에 접속하고 다음 쿼리를 수행한다.

Use Master;

GO

Select owning_principal_name, instance_pipe_name from sys.dm_os_child_instances;

GO

쿼리 결과에서 instance_pipe_name 칼럼 값을 사용하여 Management 연결할 인스턴스의 이름을 지정하기만 하면 된다. 예를 들어, 쿼리 결과가 \\.\pipe\48661DC5-1A80-40\tsql\query 이라면 연결할 인스턴스의 이름은 np:\\.\pipe\48661DC5-1A80-40\tsql\query 가 된다. 다음은 인스턴스 이름을 사용하여 Management Studio에서 접속하는 화면을 보여주고 있다. (Windows 인증만이 허용됨에 유의)

Visual Studio 2010의 Transaction-SQL Editor에서도 동일한 인스턴스 이름을 사용하여 접속이 가능하다. 일단 접속이 성공하면 Management Studio와 Transaction-SQL Editor가 인스턴스 이름을 기억해 두기 때문에 매번 쿼리를 수행하는 작업을 수행할 필요가 없어서 편리하다. 필자는 SQL Server 구성 관리자를 사용하여 이 연결 방식에 대한 별칭을 만들어 놓고 사용하고 있다. 아래 그림처럼 별칭을 만들어 두면 이제부터 그냥 localfile 이란 “서버 이름”을 사용할 수 있다.

대충 이론적인 설명을 하자면 사용자 인스턴스는 오직 명명된 파이프(named pipe)를 통해서만 접속이 가능하기 때문에 최초로 사용자 인스턴스를 구동하면 SQL Server Express는 사용자 별로 랜덤한 명명된 파이프를 “생성”하고 명명된 파이프의 이름을 데이터 베이스에 기록해 둔다. 그리고 나중에 사용자가 사용자 인스턴스를 구동하고자 하면 이 명명된 파이프를 사용하여 클라이언트의 접속을 받는 것이다. 명명된 파이프의 이름은 사용자마다 다르며 컴퓨터 마다 다름에 유념하자.

마치며……

날로 포스트 하나를 쪄먹으려고 했는데 생각보다 조낸 길어져 버렸다. 그리고 결정적인 것 하나! 필자가 사용자 인스턴스를 소개하면서 제시한 링크를 클릭해 본 독자라면 알겠지만(수고스럽게 링크 달아논 사람의 성의가 괘씸해서라도 제발 쫌 클릭 좀 해보길……) 사용자 인스턴스는 다음 버전에는 없어진단다… ㅡ.,ㅡ

그래도 다음 버전 나올 때까진 시간이 많이 남아 있을 것이고 SQL Server의 경우 새 버전 나온다고 곧장 새 버전을 설치하진 않으니깐 필자는 당분간 사용자 인스턴스의 기능을 충분히 사용해 먹으려고 한다……

아. 졸립다…… 이젠 가서 자야지……