SQL Server 데이터베이스 프로젝트를 통한 효율적인 데이터베이스 관리
Visual Studio에서 제공하는 SQL Server 데이터베이스 프로젝트를 사용하면 테이블, 뷰, 저장 프로시저 등의 데이터베이스 처리 구문을 하나의 프로젝트 단위로 관리할 수 있습니다. 이는 SQL 파일을 별도로 관리하던 기존 방식에 비해 더욱 편리하며, 버전 관리를 통해 각 SQL 구문의 이력을 쉽게 관리할 수 있도록 합니다.
미리보기 실습 - SQL Server 데이터베이스 프로젝트 사용하기
DotNetSale 솔루션에 SQL Server 데이터베이스 프로젝트 만들고 테이블 추가 및 LocalDB에 게시하기
Visual Studio에서 SQL Server 데이터베이스 프로젝트를 만들고 관리하는 것은 개발 프로세스를 체계화하고 효율화하는 데 큰 도움이 됩니다. 이 가이드는 DotNetSale 솔루션에 새로운 SQL Server 데이터베이스 프로젝트를 만들고, 로그 정보를 기록하는 LogBases 테이블을 추가한 후 LocalDB에 게시하는 전체 과정을 단계별로 설명합니다.
1. 프로젝트 생성
- Visual Studio 2022를 열고,
파일 > 새로 만들기 > 프로젝트를 선택합니다. - 프로젝트 유형으로
SQL Server 데이터베이스 프로젝트를 검색하고 선택한 후다음을 클릭합니다. - 프로젝트 이름을
DotNetSale.SqlServer로 지정하고, 위치를C:\DotNetSale\src\DotNetSale로 설정한 후만들기를 클릭하여 프로젝트를 생성합니다.
2. 테이블 스크립트 추가
- 솔루션 탐색기에서
DotNetSale.SqlServer프로젝트를 확장하고,dbo폴더 아래에Tables폴더를 마우스 오른쪽 버튼으로 클릭하여추가 > 새 항목을 선택합니다. SQL 테이블 스크립트를 선택하고 파일 이름을LogBases.sql로 지정한 후추가를 클릭합니다.- 생성된
LogBases.sql파일에 다음 SQL 스크립트를 입력합니다:CREATE TABLE [dbo].[LogBases] ( [Id] INT Identity(1, 1) NOT NULL PRIMARY KEY, -- 일련번호 [Message] NVARCHAR(MAX) NOT NULL, -- 로그 메시지 [Timestamp] DATETIMEOFFSET(7) DEFAULT (SYSDATETIMEOFFSET()) -- 시간 ) GO
3. LocalDB에 프로젝트 게시
- 솔루션 탐색기에서
DotNetSale.SqlServer프로젝트에 마우스 오른쪽 버튼을 클릭하고게시를 선택합니다. - 게시 마법사에서
대상 데이터베이스 설정을 클릭하여 LocalDB 인스턴스(localdb)\MSSQLLocalDB를 선택합니다. 데이터베이스 이름을 입력하거나 기존 데이터베이스를 선택한 후게시를 클릭하여 데이터베이스와 테이블을 LocalDB에 게시합니다.
4. 게시 확인
- Visual Studio 내에서
SQL Server 개체 탐색기를 열고 LocalDB 인스턴스를 확장합니다. - 게시된
DotNetSale.SqlServer데이터베이스를 확장하여테이블섹션에서dbo.LogBases테이블이 성공적으로 생성되었는지 확인합니다.
이렇게 SQL Server 데이터베이스 프로젝트를 만들고 테이블을 추가한 후 LocalDB에 성공적으로 게시함으로써, 효과적인 데이터베이스 관리 및 개발을 진행할 수 있습니다. 이 과정은 팀 내에서의 협업을 용이하게 하고, 버전 관리를 통해 변경 사항을 체계적으로 관리할 수 있게 도와줍니다.
16.1 SQL Server Data Tools (SSDT)
SQL Server Data Tools (SSDT)은 Visual Studio 설치 시 함께 제공되는 도구로, SQL Server 개체 탐색기(Object Explorer) 및 SQL Server 데이터베이스 프로젝트 템플릿을 제공합니다. 이를 통해 SQL Server 데이터베이스를 프로젝트 단위로 프로그래밍하고 게시하는 모든 절차를 일괄적으로 관리할 수 있으며, Azure DevOps와 Git/GitHub 등을 이용한 버전 제어가 가능합니다. 또한, SQL Server 개체 탐색기를 통해 모든 형태의 SQL Server에 연결하여 데이터베이스를 관리할 수 있습니다.
- 그림 16.1: SQL Server 데이터베이스 프로젝트 생성 템플릿
<참고> SQL Server Data Tools 업데이트
SQL Server Data Tools는 Visual Studio 설치 시 기본으로 내장되어 있으며, 최신 버전은 다음 경로에서 다운로드할 수 있습니다:
16.2 SQL Server 데이터베이스 프로젝트의 특징
SQL Server 데이터베이스 프로젝트의 주요 장점은 하나의 솔루션에서 모든 데이터베이스 관련 기능을 통합 관리할 수 있다는 점입니다. 이를 통해 버전 제어, 분기 및 병합이 용이하며, 팀 멤버 간에 독립적으로 개발이 가능합니다. 각 개발자는 독립된 데이터베이스에서 작업을 진행하고, 필요시에 통합 기능을 이용할 수 있습니다.
16.3 SQL Server 데이터베이스 프로젝트의 주요 기능
SQL Server 데이터베이스 프로젝트는 스키마 비교 도구를 제공하여 실제 데이터베이스의 변경 사항을 프로젝트로 가져오거나 반대로 프로젝트의 변경 사항을 데이터베이스로 내보낼 수 있습니다. 리팩터링 기능은 손쉽게 개체 이름을 변경하거나 SQL 문의 와일드카드를 구체적인 필드 명으로 변환하는 등의 기능을 제공합니다. 또한, 정적 코드 분석을 통해 기본 규칙에 어긋나는 SQL 코드에 대해 경고 또는 에러로 표시하여 개선할 수 있습니다.
16.4 데이터베이스 프로젝트 지원 버전
SQL Server 데이터베이스 프로젝트는 SQL Server 2005 이상의 모든 버전을 지원합니다. 저도 다음과 같은 다양한 데이터베이스에서 SQL Server 데이터베이스 프로젝트를 사용해 보았습니다:
- Microsoft Azure SQL Database
- SQL Server 2019
- SQL Server 2017
- SQL Server 2016
- SQL Server 2014
- SQL Server 2012
- SQL Server 2008 / R2
- SQL Server 2005
16.5 Visual Studio Data Tool
Visual Studio Data Tool은 Visual Studio에 포함된 SQL Server Management Studio(SSMS)인 SQL Server 개체 탐색기(Object Explorer)를 의미합니다. 이 도구는 SQL Server의 SSMS 개체 탐색기와 유사하며, 서버 탐색기(Server Explorer)를 통해서도 간단한 DB 관리가 가능합니다. 데이터베이스를 연결하려면 다음과 같은 다양한 데이터베이스 인스턴스명을 사용할 수 있습니다:
(local)
(localdb)\MSSQLLocalDB
(local)\SQLExpress
그림 16.2: SQL Server 개체 탐색기
16.6 [실습] SQL Server 데이터베이스 프로젝트 생성 및 관리
16.6.1 소개
Visual Studio 2019에서 제공하는 SQL Server 데이터베이스 프로젝트 템플릿을 사용하여 SQL Server 데이터베이스를 하나의 프로젝트 단위로 관리할 수 있습니다. 이 방법을 통해 생성한 SQL 개체를 원하는 대상 데이터베이스에 쉽게 게시할 수 있으며, 이 과정을 통해 얻는 장점들을 실습을 통해 학습해보겠습니다.
<참고>
이번 실습 과정은 "SQL Server 데이터베이스 프로젝트를 사용하여 DB 관리"라는 제목의 동영상 강좌로도 제공되고 있습니다. 관련 내용은 다음 링크에서 확인할 수 있습니다:
16.6.2 따라하기
(1) SQL Server 데이터베이스 프로젝트 생성
Visual Studio 2019를 열고, <파일> > <새로 만들기> > <프로젝트> 메뉴로 이동하여 'SQL Server' 카테고리에서 'SQL Server 데이터베이스 프로젝트' 템플릿을 선택합니다. 프로젝트 이름을 'DotNetNote.Database'로 설정하고, C:\ASP.NET 폴더에 저장합니다. 이 프로젝트는 모든 테이블, 뷰, 저장 프로시저 등을 관리합니다.
- 그림 16.3: 데이터베이스 프로젝트 생성
(2) SQL Server 데이터베이스 프로젝트 생성 완료 후의 모습
프로젝트 생성 후 <솔루션 탐색기>를 확인하면, 다른 프로젝트와 달리 SQL Server 데이터베이스 프로젝트 관련 항목만 포함되어 있습니다.
- 그림 16.4: SQL Server 데이터베이스 프로젝트 생성 완료
(3) 프로젝트 속성 설정
프로젝트에 마우스 오른쪽 버튼을 클릭 후 <속성>을 선택하고, <프로젝트 설정> 탭에서 <대상 플랫폼>을 선택할 수 있습니다. 이 설정은 SQL Server 2005 이상의 데이터베이스 대부분에 프로젝트를 게시할 수 있도록 합니다.
- 그림 16.5: 대상 플랫폼 설정 메뉴
(4) 데이터베이스 스키마 가져오기
프로젝트에 마우스 오른쪽 버튼을 클릭하고, <가져오기> > <데이터베이스>를 선택하여 특정 데이터베이스의 스키마를 프로젝트로 가져옵니다.
- 그림 16.6: 데이터베이스 가져오기
(5) 데이터베이스 가져오기 설정
<데이터베이스 가져오기> 화면에서 <연결 선택> 버튼을 클릭하면, SQL Server 정식 버전, 익스프레스 버전, 로컬 DB 버전, SQL Azure 버전 등에서 데이터베이스를 선택할 수 있습니다.
- 그림 16.7: 데이터베이스 가져오기 설정 화면
(6) 최신 연결 정보
<연결 선택> 후 나타나는 <연결 속성> 창에서, 최근 연결 정보를 <기록> 탭에서 선택할 수 있습니다.
- 그림 16.8: 최신 연결 정보
(7) 로컬 DB에 연결
로컬 DB를 선택하고, <데이터베이스 이름> 항목에서 이전에 사용한 DevADO.NET 데이터베이스를 선택 후 <연결> 버튼을 클릭합니다.
- 그림 16.9: 데이터베이스 선택
(8) 데이터베이스 가져오기 설정 완료 후의 모습
<가져오기 설정> 부분을 기본값으로 구성한 후, <시작> 버튼을 클릭하여 DevADONET 데이터베이스의 구조를 프로젝트로 가져옵니다.
- 그림 16.10: 데이터베이스 가져오기 설정 화면
(9) 데이터 가져오기 요약
<데이터 가져오기> 진행 화면에서 학습 목적으로 간단한 데이터베이스의 테이블 1개를 빠른 시간 내에 에러 없이 가져옵니다. <마침> 버튼을 클릭하여 데이터 가져오기를 종료합니다.
- 그림 16.11: 데이터베이스 가져오기 요약
(9) 데이터베이스 가져오기 후의 솔루션 탐색기 모습
<솔루션 탐색기>에서는 '스키마\개체유형' 규칙에 따라 dbo 폴더가 생성되고, 이 폴더 내에 Tables, Stored Procedures 등의 폴더가 만들어지며, 데이터베이스에 구성된 테이블을 sql 파일로 가져옵니다.
- 그림 16.12: 데이터베이스 가져오기 후의 모습
(10) 새 폴더 및 테이블 추가
기존에 데이터베이스가 없다면 프로젝트에 마우스 오른쪽 버튼을 클릭하여 <추가> > <새 폴더>를 선택해 dbo 폴더를 만듭니다. 이 폴더 내에 Tables 폴더를 추가하고, 그 안에 <추가> > <테이블>을 선택해 Memos 이름으로 테이블을 추가합니다.
- 그림 16.13: 테이블 추가
(11) Memos 테이블 스크립트 구성
Memos 테이블을 직접 생성하거나 기존 데이터베이스에서 가져오기를 선택하여 구성할 수 있습니다. 이 테이블은 컬럼이 여섯 개 있으며, 디자인 보기와 T-SQL 보기에서 모두 작업할 수 있습니다. Identity 속성은 직접 입력하거나 속성(F4) 창에서 설정할 수 있습니다.
--[!] 메모 저장용 테이블
CREATE TABLE [dbo].[Memos] (
[Num] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (25) NOT NULL,
[Email] NVARCHAR (100) NULL,
[Title] NVARCHAR (150) NOT NULL,
[PostDate] DATETIME DEFAULT (getdate()) NULL,
[PostIp] NVARCHAR (15) NULL,
PRIMARY KEY CLUSTERED ([Num] ASC)
);
- 그림 16.14: Memos 테이블의 스크립트 구성
(12) 게시 메뉴 선택
SQL Server 데이터베이스 프로젝트에 구성된 데이터베이스 스키마를 외부 데이터베이스에 게시하기 위해, 프로젝트에 마우스 오른쪽 버튼을 클릭하고 게시 메뉴를 선택합니다.
- 그림 16.15: 게시 메뉴
(13) 데이터베이스 게시 설정
데이터베이스 게시 메뉴에서, 게시할 데이터베이스 위치와 데이터베이스를 선택합니다. '편집' 버튼을 클릭하여 데이터베이스 연결 설정을 진행합니다.
- 그림 16.16: 데이터베이스 게시 설정 화면
(14) 데이터베이스 연결 설정
'편집' 버튼 클릭 후, 연결 창이 나타납니다. 여기서 LocalDB를 선택하거나, '찾아보기' 탭에서 다시 구성할 수 있습니다. 데이터베이스 이름에 "DotNetNote"를 입력하고, 서버 이름과 인증 정보를 입력한 후 '테스트 연결' 버튼을 클릭하여 연결 성공 여부를 확인합니다.
- 그림 16.17: 데이터베이스 연결 설정 완료
(15) 대상 데이터베이스 설정 완료
데이터베이스 게시 화면으로 돌아와서, 대상 데이터베이스 설정 영역이 채워진 것을 확인할 수 있습니다. 바로 '게시' 버튼을 클릭하여 게시할 수도 있고, 설정을 저장해 놓을 수도 있습니다.
- 그림 16.18: 대상 데이터베이스 설정 완료
(16) 프로필 저장
'다른 이름으로 프로필 저장' 버튼을 클릭하여 현재 프로젝트에 원하는 이름으로 현재 설정에 대한 프로필을 저장합니다. 예를 들어 LocalDB 사용 시, 프로필 이름을 DotNetNote.Database.LocalDB.publish.xml로 저장합니다.
- 그림 16.19: 프로필 저장
(17) 게시 완료
프로필 저장 후, '게시' 버튼을 클릭하면 설정된 데이터베이스에 스크립트가 실행되면서 데이터베이스가 생성되거나 업데이트 됩니다.
- 그림 16.20: 게시 완료 화면
(18) 데이터베이스 생성 확인
Visual Studio에서 SQL Server 개체 탐색기를 열고, 로컬 DB에서 DotNetNote 데이터베이스를 열어 Memos 테이블
이 생성된 것을 확인합니다.
- 그림 16.21: 데이터베이스 생성 완료 확인
(19) 프로필 로드 및 재게시
테이블의 내용이 변경되거나 추가된 경우, '게시' 메뉴를 선택하고 저장된 프로필을 '프로필 로드' 버튼으로 불러와서 데이터베이스를 재게시할 수 있습니다.
- 그림 16.22: 저장된 프로필 로드
(20) 새 데이터베이스 연결 및 게시
한 번 설정된 프로젝트를 새로운 데이터베이스에 동일한 사양으로 게시할 수 있습니다. Test 접미사를 붙여 새로운 데이터베이스에 연결하여 게시합니다.
- 그림 16.23: 새 데이터베이스 연결
(21) Test 프로필 저장
Test 데이터베이스에 게시하기 위한 설정 정보를 '다른 이름으로 프로필 저장' 버튼을 클릭하여 저장합니다. 이름에 Test를 포함시켰습니다.
- 그림 16.24: Test 프로필 저장
(22) 데이터베이스 게시
Test 프로필을 저장한 후, '게시' 버튼을 클릭하여 해당 데이터베이스에 게시합니다.
- 그림 16.25: 데이터베이스 게시
(23) 데이터베이스 두 개 게시
프로젝트 하나에 데이터베이스 두 개를 게시했습니다. 이를 통해 개발, 테스트, 운영 등 다양한 데이터베이스 환경을 구축하여 같은 데이터베이스 스키마를 관리하고 개발할 수 있습니다.
- 그림 16.26: 데이터베이스 두 개 게시
(24) 전체 프로젝트 구성 완료
SQL Server 데이터베이스 프로젝트를 통해 Memos 테이블을 만들고 두 개의 서로 다른 데이터베이스에 게시했습니다. 모든 테이블, 뷰, 저장 프로시저 등의 SQL 개체는 이 프로젝트에서 지속적으로 관리됩니다.
- 그림 16.27: 전체 프로젝트 구성 후의 모습
16.6.3 마무리
ASP.NET 웹 응용 프로그램에서 사용되는 모든 데이터베이스 관련 작업은 SQL Server 데이터베이스 프로젝트를 통해 관리됩니다. 이 책의 모든 데이터베이스 관련 기능은 이 프로젝트에서 진행되며, 개체 단위의 SQL 관리의 장점을 충분히 느낄 수 있습니다.
16.7 SQL Server 데이터베이스 프로젝트 게시 중 오류 처리
제 강의와 책에서는 모든 데이터베이스 개체(테이블, 뷰, 저장 프로시저)를 SQL Server 데이터베이스 프로젝트를 통해 관리합니다. 이 방법은 SQL Server Management Studio(SSMS), Azure Data Studio, Visual Studio의 SQL Server 개체 탐색기 또는 서버 탐색기에서 직접 테이블을 수정하는 대신, SQL Server 데이터베이스 프로젝트의 [게시] 기능을 통해 일관되게 데이터를 관리하고 버전 제어(GitHub, Azure DevOps 등)의 이점을 활용할 수 있게 합니다.
게시 과정에서, 기존에 게시된 동일 테이블에 데이터 손실을 초래할 수 있는 변경이 포함된 경우, 시스템은 경고성 오류를 발생시켜 자동으로 게시가 중단됩니다. 이런 경우, 수동으로 문제를 수정하고 최종 게시를 진행해야 합니다. 예를 들어, 테이블에 새 컬럼을 추가하는 변경은 문제 없이 게시되지만, BigInt 형식의 컬럼을 Int로 변경하는 것처럼 큰 변경은 대부분 오류를 발생시킵니다. 이런 변경은 SSMS를 사용하여 수동으로 처리해야 하며, 특히 기존 데이터가 2,200,000,000(22억)을 초과하는 경우 BigInt에서 Int로의 변경은 불가능합니다.
결국, SQL Server 데이터베이스 프로젝트를 통한 관리는 새로 추가되는 부분에 대해 문제 없이 게시할 수 있지만, 기존 데이터에 영향을 미치는 변경이 포함되어 게시될 때는 주의가 필요합니다. 사용자는 이러한 오류를 인지하고 적절히 대처하여 데이터베이스 프로젝트를 관리해야 합니다.
16.8 PostDeployment: SQL Server 데이터베이스 프로젝트의 추가 활용
SQL Server 데이터베이스 프로젝트에서 PostDeployment 스크립트를 활용하면 초기 설정 값을 손쉽게 구성할 수 있습니다. 특히, 프로젝트가 처음 빈 데이터베이스에 게시될 때, Administrators 그룹과 Administrator 계정을 'Pa$$w0rd'라는 암호로 생성하는 과정이 중요합니다. 이 과정에서 Domains, UserProfiles, Membership 테이블 등이 중요하게 사용됩니다. 또한, ValueAndRange 테이블에는 초기 값들이 설정됩니다.
중요한 스크립트 파일로는 \Dashboard.Database\00_초깃값\All.PostDeployment.sql이 있습니다. 이 파일은 데이터베이스의 스키마(테이블, 뷰, 저장 프로시저, 함수 등)를 생성 후 실행하며, 다음의 PostDeployment 스크립트 파일들을 순서대로 실행합니다:
.\Domains.KePOP.PostDeployment.sql.\UserProfiles.PostDeployment.sql.\Membership.PostDeployment.sql.\ValueAndRange.PostDeployment.sql(이 스크립트는 초기 '할인율'과 '이자율' 설정을 담당합니다.)
새로운 기본값을 추가할 필요가 있을 때는 ValueAndRange.PostDeployment.sql 파일에 새로운 데이터 입력 코드를 추가하면 됩니다. 이 구조는 이미 입력된 데이터를 유지하면서 새 데이터만 추가하는 방식으로 구성되어 있어, 웹 화면을 통한 데이터 수정에 영향을 주지 않습니다.