SQL Server를 통해 Access 사용

비즈니스 연속성 제공

Access 솔루션의 경우 중단을 최소화하여 계속 실행하려고 하지만, Access 백엔드 데이터베이스에 대한 옵션은 제한되어 있습니다. Access 데이터베이스 백업은 데이터를 보호하는 데 반드시 필요하지만, 사용자를 오프라인으로 전환해야 합니다. 그런 다음 하드웨어/소프트웨어 유지 관리 업그레이드, 네트워크 또는 정전, 하드웨어 장애, 보안 위반 또는 사이버 공격으로 인해 발생하는 예기치 않은 가동 중지 시간이 있습니다. 가동 중지 시간과 업무에 끼치는 영향을 최소화하기 위해 사용 중인 SQL Server 데이터베이스를 백업할 수 있습니다. 또한 SQL Server는 고가용성(HA) 및 재해 복구(DR) 전략을 제공합니다. 이 두 가지 통합 기술을 HADR이라고 합니다. 자세한 내용은 비즈니스 연속성 및 데이터베이스 복구 및 SQL Server를 통한 비즈니스 연속성 제공(전자책)을 참조하세요.

사용 중 백업

SQL Server는 데이터베이스를 실행하는 동안 발생할 수 있는 온라인 백업 프로세스를 사용합니다. 전체 백업, 부분 백업 또는 파일 백업을 수행할 수 있습니다. 백업은 데이터와 트랜잭션 로그를 복사하여 완전한 복원 작업을 보장합니다. 특히 온-프레미스 솔루션에서 간단한 복구 옵션과 전체 복구 옵션 사이의 차이와 트랜잭션 로그 증가에 미치는 영향을 알고 있어야 합니다. 자세한 내용은 복구 모델을 참조하세요.

파일 관리와 데이터베이스 축소 작업을 제외 하고, 대부분의 백업 작업은 즉시 수행됩니다. 반대로, 백업 작업이 진행되는 동안 데이터베이스 파일을 만들거나 삭제하려는 경우 작업이 실패합니다. 자세한 내용은 백업 개요를 참조하세요.

HADR

높은 가용성과 비즈니스 연속성을 실현하는 가장 일반적인 두 가지 방법은 미러링과 클러스터링입니다. SQL Server는 미러링 및 클러스터링 기술과 "항상 장애 조치(Failover) 클러스터 인스턴스" 및 "항상 가용성 그룹"을 통합합니다.

미러링은 별도의 하드웨어에 대기 데이터베이스, 전체 복사본 또는 미러를 유지 관리하여 거의 즉각적인 장애 조치를 지원하는 데이터베이스 수준의 연속성 솔루션입니다. 들어오는 트랜잭션이 모든 서버에 동시에 전송되는 동기(높은 보안) 모드로 작동하거나, 들어오는 트랜잭션을 활성 데이터베이스로 커밋한 다음 미리 결정된 일부 지점에서 미러에 복사되는 비동기(높은 성능) 모드로 작동할 수 있습니다. 미러링은 데이터베이스 수준 솔루션이며 전체 복구 모델을 사용하는 데이터베이스에만 적용됩니다.

클러스터링은 서버를 단일 인스턴스처럼 보이는 단일 데이터 저장소에 서버를 결합하는 서버 수준 솔루션입니다. 사용자가 인스턴스에 연결하고 해당 인스턴스에서 현재 활성 상태에 있는 서버를 알 필요가 없습니다. 하나의 서버에 오류가 발생하거나 유지 관리를 위해 오프라인으로 전환해야 하는 경우 사용자 환경이 변경되지 않습니다. 클러스터에 있는 각 서버는 하트비트를 사용하는 클러스터 관리자가 모니터링하므로, 전환이 될 때 시간 지연이 발생하는 경우에도 클러스터에 있는 활성 서버가 오프라인 상태가 되고 클러스터의 다음 서버로 원활하게 전환하려고 할 때를 감지합니다.

자세한 내용은 항상 장애 조치(Failover) 클러스터 인스턴스 및 항상 가용성 그룹: 고가용성 및 재해 복구 솔루션을 참조하세요.

맨 위로 이동

SQL Server 보안

보안 센터를 사용하고 데이터베이스를 암호화하여 Access 데이터베이스를 보호할 수 있지만, SQL Server는 더 향상된 보안 기능을 제공합니다. Access 사용자에게 표시되는 세 가지 기능을 살펴보겠습니다. 자세한 내용은 SQL Server 보안을 참조하세요.

데이터베이스 인증

SQL Server에는 네 가지 데이터베이스 인증 방법이 있는데, ODBC 연결 문자열에서 각각을 지정할 수 있습니다. 자세한 내용은 Azure SQL Server 데이터베이스에서 데이터에 연결 또는 데이터 가져오기를 참조하세요. 각 방법에는 고유한 이점이 있습니다.

통합된 Windows 인증    사용자 유효성 검사, 보안 역할, 기능 및 데이터에 사용자 제한에 대해 Windows 자격 증명을 사용합니다. 응용 프로그램에서 도메인 자격 증명을 활용하고 사용자 권한을 손쉽게 관리할 수 있습니다. 선택적으로 SPN(Service Principal Name)을 입력합니다. 자세한 내용은 인증 모드 선택을 참조하세요.

SQL Server 인증    사용자가 세션에서 데이터베이스에 처음 액세스할 때 로그인 ID와 암호를 입력하여 데이터베이스에 설정된 자격 증명으로 연결해야 합니다. 자세한 내용은 인증 모드 선택을 참조하세요.

Azure Active Directory 통합 인증    Azure Active Directory를 사용하여 Azure SQL Server Database에 연결합니다. Azure Active Directory 인증을 구성한 경우 추가 로그인 및 암호가 필요하지 않습니다. 자세한 내용은 Azure Active Directory 인증을 사용하여 SQL Database에 연결을 참조하세요.

Active Directory 암호 인증    로그인 이름 및 암호를 입력하여 Azure Active Directory에 설정된 자격 증명으로 연결합니다. 자세한 내용은 Azure Active Directory 인증을 사용하여 SQL Database에 연결을 참조하세요.

팁    위협 감지를 사용하여 Azure SQL Server 데이터베이스의 잠재적인 보안 위협을 나타내는 비정상 데이터베이스 활동에 대한 알림을 받을 수 있습니다. 자세한 내용은 SQL Database 위협 감지를 참조하세요.

응용 프로그램 보안

SQL Server에는 Access를 사용하여 활용할 수 있는 두 가지 응용 프로그램 수준 보안 기능이 있습니다.

동적 데이터 마스킹    권한이 없는 사용자로부터 중요한 정보를 마스킹하여 해당 정보를 숨깁니다. 예를 들어, 주민 등록 번호를 부분적으로 또는 전체적으로 마스킹할 수 있습니다.

부분적인 데이터 마스킹

전체적인 데이터 마스킹

여러 가지 방법으로 데이터 마스크를 정의하 고 다양한 데이터 형식에 적용할 수 있습니다. 데이터 마스킹은 정의된 사용자 집합에 대한 테이블 및 열 수준의 정책 기반이며 실시간 쿼리에 적용 됩니다. 자세한 내용은 동적 데이터 마스킹을 참조하세요.

행 수준 보안    행 수준 보안을 사용하여 사용자 특성을 기반으로 하는 중요한 정보가 포함된 특정 데이터베이스 행에 대한 액세스를 제어할 수 있습니다. 데이터베이스 시스템에서 이 액세스 제한을 적용하므로 보안 시스템의 안정성과 보안성이 높아집니다.

보안 조건에는 다음과 같은 두 가지 유형이 있습니다.

• 필터 조건은 쿼리에서 행을 필터링합니다. 필터가 투명하므로 최종 사용자가 필터링을 인식하지 못합니다.

• 차단 조건은 인증되지 않은 작업을 방지하고 작업을 수행할 수 없는 경우 예외를 발생합니다.

자세한 내용은 행 수준 보안을 참조하세요.

암호화를 사용하여 데이터 보호

데이터베이스 성능에 영향을 주지 않고도 미사용, 전송 중 및 사용 중인 데이터를 보호합니다. 자세한 내용은 SQL Server 암호화를 참조하세요.

미사용 암호화    실제 저장소 계층에서 개인 데이터를 오프라인 미디어 공격에 대해 보호하려면 미사용 암호화(TDE(Transparent Data Encryption)라고도 함)를 사용합니다. 즉, 실제 매체가 도난당하거나 잘못 삭제된 경우에도 데이터가 보호됩니다. TDE는 응용 프로그램을 변경하지 않고도 데이터베이스, 백업 및 트랜잭션 로그의 실시간 암호화와 암호 해독을 수행합니다.

동적 암호화    "스누핑과 “중간자 공격"을 방지하기 위해 네트워크에서 전송되는 데이터를 암호화할 수 있습니다. SQL Server는 고도의 보안 통신을 위해 TLS(Transport Layer Security) 1.2를 지원합니다. TDS(Tabular Data Stream) 프로토콜은 신뢰할 수 없는 네트워크를 통한 통신을 보호하는 데 사용됩니다.

클라이언트에서 사용 중 암호화    사용 중인 개인 데이터를 보호하려면 “항상 암호화”가 적합한 기능입니다. 데이터베이스 엔진에 암호화 키를 노출하지 않고도 클라이언트 컴퓨터에서 드라이버가 개인 데이터를 암호화하고 해독합니다. 따라서 암호화된 데이터는 해당 데이터의 관리 책임자만 볼 수 있으며 액세스 권한이 없는 기타 높은 권한을 부여받은 사용자에게는 표시되지 않습니다. 선택 된 암호화 유형에 따라, 항상 암호화를 사용하면 암호화된 열의 검색, 그룹화 및 인덱싱과 같은 일부 데이터베이스 기능이 제한될 수 있습니다.

맨 위로 이동

개인 정보 문제 처리

개인 정보 문제가 너무 일반화되어 EU(유럽 연합)은 GDPR(General Data Protection Regulation)을 통해 법적 요구 사항을 정의했습니다. 다행히 SQL Server 백엔드는 이러한 요구 사항에 대응하는 데 적합합니다. 3단계 프레임워크에서 GDPR을 구현한다고 생각해 보세요.

1단계: 규정 준수 위험 평가 및 관리

GDPR을 사용하려면 테이블과 파일에 포함된 개인 정보를 식별하고 목록을 만들어야 합니다. 이 정보는 이름, 사진, 전자 메일 주소, 은행 세부 정보, 소셜 네트워킹 웹 사이트의 게시물, 의료 정보 또는 IP 주소와 같은 항목일 수 있습니다.

SQL Server Management Studio에 빌드된 새 도구인SQL 데이터 검색 및 분류를 사용하면 두 개의 메타 데이터 특성을 열에 적용하여 중요한 데이터를 검색하고 분류 하며 레이블을 지정하여 보고할 수 있습니다.

• 레이블    데이터의 민감도를 정의합니다.

• 정보 유형    열에 저장된 데이터 형식에 대한 더 세밀한 기능을 제공합니다.

사용할 수 있는 또 다른 검색 메커니즘으로는 SELECT 문에 사용할 CONTAINS 및 FREETEXT 조건과 CONTAINSTABLE 및 FREETEXTTABLE과 같은 행 집합 함수의 사용을 포함하는 전체 텍스트 검색이 있습니다. 전체 텍스트 검색에서 표를 검색하여 단어, 단어 조합 또는 단어 변형(예: 동의어 또는 굴절형)을 찾을 수 있습니다. 자세한 내용은 전체 텍스트 검색을 참조하세요.

2단계: 개인 정보 보호

GDPR을 사용하려면 개인 정보를 보호하고 해당 정보에 대한 액세스를 제한해야 합니다. 사용자 네트워크 및 리소스(예: 방화벽 설정)에 대한 액세스를 관리하기 위해 수행하는 표준 단계 외에, SQL Server 보안 기능을 사용하여 데이터 액세스를 제어할 수 있습니다.

• 사용자 ID를 관리하고 무단 액세스를 방지하기 위한 SQL Server 인증

• 행 수준 보안을 사용하여 사용자와 해당 데이터 사이의 관계를 기준으로 테이블의 행에 대한 액세스를 제한합니다.

• 동적 데이터 마스킹은 권한이 없는 사용자로부터 개인 데이터를 마스킹하여 해당 정보의 노출을 제한합니다.

• 암호화는 개인 데이터를 전송 및 저장 중에 보호하고 서버 쪽을 비롯한 손상에 대해 보호합니다.

자세한 내용은 SQL Server 보안을 참조 하세요.

3단계: 요청에 효과적으로 대응

GDPR을 사용하려면 개인 데이터 처리의 레코드를 유지 관리하고 요청 시 관리 기관에서 이 레코드를 사용할 수 있도록 해야 합니다. 우발적 데이터 릴리스 발생과 같은 문제가 있는 경우, 보호 제어를 사용하여 신속하게 응답할 수 있습니다. 보고가 필요한 경우 데이터를 신속하게 사용할 수 있어야 합니다. 예를 들어, GDPR에 따르면 개인 데이터 위반이 “발견되면 72시간 이내에 감독 당국”에 보고해야 합니다.

SQL Server 2017는 여러 가지 방법으로 보고 작업에 도움을 줍니다.

• SQL Server 감사를 사용하여 데이터베이스 액세스의 지속적인 레코드와 처리 활동을 보장합니다. 이는 데이터베이스 활동을 추적하는 세밀한 감사를 수행하여 잠재적인 위협, 의심스러운 남용 또는 보안 위반을 이해하고 식별하는 데 도움이 됩니다. 데이터 포렌식을 쉽게 수행할 수 있습니다.

• SQL Server 임시 테이블은 데이터 변경 내용에 대한 전체 기록을 유지할 수 있도록 설계 된 시스템 버전 관리 사용자 테이블입니다. 간단한 보고 및 시점 분석에 이 기능을 사용할 수 있습니다.

• SQL 취약성 평가는 보안및 사용 권한 문제를 감지하는 데 도움이 됩니다. 문제가 감지되면 데이터베이스 검사 보고서로 드릴다운하여 해결을 위한 작업을 찾을 수도 있습니다.

자세한 내용은 신뢰 플랫폼 만들기(전자책) 및 GDPR 준수 여정을 참조하세요.

맨 위로 이동

데이터베이스 스냅샷 만들기

데이터베이스 스냅샷은 한 시점의 SQL Server 데이터베이스에 대한 읽기 전용 정적 보기입니다. Access 데이터베이스 파일을 복사하여 데이터베이스 스냅샷을 효과적으로 만드는 경우에도 Access에서 SQL Server로 기본 제공 방법론을 사용하지 않습니다. 데이터베이스 스냅샷을 만드는 시점의 데이터를 기반으로 보고서 작성에 데이터베이스 스냅샷을 사용할 수 있습니다. 기록 데이터를 유지 관리하기 위해 데이터베이스 스냅샷(예: 종료 보고서를 롤업하는 데 사용하는 각 재무 분기용)을 사용할 수도 있습니다. 다음과 같은 모범 사례를 수행하는 것이 좋습니다.

• 스냅샷 이름 지정    각 데이터베이스 스냅샷에는 고유한 데이터베이스 이름이 필요합니다. 알아보기 쉽도록 이름에 목적과 시간을 추가합니다. 예를 들어, 하루 24 시간 기준으로 오전 6시와 오후 6시 사이에 6시간 간격으로 하루에 세 번 AdventureWorks 데이터베이스의 스냅샷을 만들려면 AdventureWorks_snapshot_0600, AdventureWorks_snapshot_1200 및 AdventureWorks_snapshot_1800으로 이름 지정합니다.

• 스냅샷 수 제한    각 데이터베이스 스냅샷은 명시적으로 삭제될 때까지 유지됩니다. 각 스냅샷은 계속해서 확장되므로 새 스냅샷을 만든 후에는 오래된 스냅샷을 삭제하여 디스크 공간을 절약하는 것이 좋습니다. 예를 들어, 일일 보고서를 작성하는 경우에는 데이터베이스 스냅샷을 24시간 동안 보존한 다음 새 스냅샷으로 대체합니다.

• 올바른 스냅샷에 연결    데이터베이스 스냅샷을 사용하려면 Access 프런트 엔드에서 올바른 위치를 알고 있어야 합니다. 기존 스냅샷을 새 스냅샷으로 대체하는 경우 새 스냅샷으로 Access를 리디렉션해야 합니다. 올바른 데이터베이스 스냅샷에 연결되는지 확인하기 위해 Access 프런트 엔드에 논리를 추가합니다.

데이터베이스 스냅샷을 만드는 방법은 다음과 같습니다.

CREATE DATABASE AdventureWorks_dbss1800 ON  
( NAME = AdventureWorks_Data, FILENAME =   
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_snapshot_0600' )  
AS SNAPSHOT OF AdventureWorks;  

자세한 내용은 데이터베이스 스냅샷(SQL Server)을 참조하세요.

맨 위로 이동

동시성 제어

많은 사용자가 데이터베이스의 데이터를 동시에 수정하려고 하는 경우 한 사용자가 수정한 내용이 다른 사용자의 수정 사항에 불리하게 영향을 끼치지 않도록 제어 시스템이 필요합니다. 이를 동시성 제어라고 2개의 기본 잠금 전략(비관적 및 낙관적)이 있습니다. 잠금 기능을 사용하면 다른 사용자에게 영향을 주는 방식으로 데이터를 수정하지 못하도록 할 수 있습니다. 잠금 기능은 예기치 않은 결과가 발생할 수 있는 쿼리의 데이터베이스 무결성을 보장하는 데도 도움이 됩니다. Access와 SQL Server에서 이러한 동시성 제어 전략을 구현하는 방식에는 중요한 차이점이 있습니다.

Access에서 기본 잠금 전략은 낙관적이며 레코드에 쓰려고 시도하는 첫 번째 사용자에 게 잠금의 소유권을 부여합니다. Access는 동시에 동일한 레코드에 쓰려고 하는 다른 사용자에게 쓰기 충돌 대화 상자를 표시합니다. 충돌을 해결하려면 다른 사용자가 레코드를 저장하거나, 클립보드로 복사하거나, 변경 내용을 삭제할 수 있습니다.

RecordLocks 속성을 사용하여 동시성 제어 전략을 변경할 수도 있습니다. 이 속성은 폼, 보고서 및 쿼리에 영향을 주며 세 가지 설정이 있습니다.

• 잠그지 않음    한 폼에서 동시에 동일한 레코드를 편집할 수 있지만, 쓰기 충돌 대화 상자가 나타날 수 있습니다. 보고서에서 보고서를 미리 보거나 인쇄하는 동안에는 레코드가 잠기지 않습니다. 쿼리에서 쿼리가 실행되는 동안에는 레코드가 잠기지 않습니다. 이는 낙관적 잠금을 구현하는 Access 방식입니다.

• 모든 레코드    폼 보기 또는 데이터시트 보기에 폼이 열려 있는 동안, 보고서를 미리 보거나 인쇄하는 동안 또는 쿼리가 실행되는 동안 기본 테이블 또는 쿼리의 모든 레코드가 잠깁니다. 사용자가 잠금 중에 레코드를 읽을 수 있습니다.

• 편집한 레코드    폼 및 쿼리의 경우, 사용자가 레코드의 필드 편집을 시작하는 즉시 레코드 페이지가 잠기고 해당 사용자가 다른 레코드로 이동할 때까지 잠긴 상태가 유지됩니다. 결과적으로 한 번에 한 명의 사용자만 레코드를 편집할 수 있습니다. 이는 비관적 잠금을 구현하는 Access 방식입니다.

자세한 내용은 쓰기 충돌 대화 상자 및 RecordLocks 속성을참조하세요.

SQL Server에서 동시성 제어는 다음과 같은 방식으로 동작합니다.

• 비관적    잠금이 적용되는 작업을 수행한 후에는 소유자가 해당 잠금을 해제할 때까지 다른 사용자가 이 잠금과 충돌하는 작업을 수행할 수 없습니다. 이 동시성 제어는 데이터 경합이 많은 환경에서 주로 사용됩니다.

• 낙관적    낙관적 동시성 제어에서는 사용자가 데이터를 읽을 때 데이터를 잠그지 않습니다. 사용자가 데이터를 업데이트할 때 시스템은 다른 사용자가 데이터를 읽은 후 변경했는지 확인합니다. 다른 사용자가 데이터를 업데이트한 경우 오류가 발생합니다. 일반적으로 오류를 수신한 사용자가 트랜잭션을 롤백하고 처음부터 다시 시작합니다. 이 동시성 제어는 데이터 경합이 낮은 환경에서 주로 사용됩니다.

SET TRANSACTION 문을 사용하여 다른 트랜잭션에서 만든 수정 사항의 트랜잭션에 대한 보호 수준을 정의하는 여러 트랜잭션 격리 수준을 선택하여 동시성 제어 유형을 지정할 수 있습니다.

 SET TRANSACTION ISOLATION LEVEL
 { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ  
    | SNAPSHOT
    | SERIALIZABLE
 }

자세한 내용은 트랜잭션 잠금 및 행 버전 관리 가이드를 참조하세요.

맨 위로 이동

쿼리 성능 개선

Access 통과 쿼리가 작동하는 경우 SQL Server를 더욱 효율적으로 실행할 수 있는 정교한 방법을 활용하세요.

Access 데이터베이스와 달리, SQL Server는 마이크로프로세서(CPU)가 두 개 이상인 컴퓨터에 대해 쿼리 실행과 인덱스 작업을 최적화하는 데 병렬 쿼리를 제공합니다. SQL Server는 여러 시스템 작업자 스레드를 사용하여 쿼리 또는 인덱스 연산을 병렬로 수행할 수 있기 때문에 작업을 빠르고 효율적으로 완료할 수 있습니다.

쿼리는 데이터베이스 솔루션의 전반적인 성능을 개선하는 데 중요한 구성 요소입니다. 잘못된 쿼리는 무제한으로 시간 초과하며 리소스(예: CPU, 메모리 및 네트워크)를 소진합니다. 이는 중요한 비즈니스 정보를 사용하지 못하도록 합니다. 잘못된 쿼리가 하나인 경우에도 데이터베이스에 심각한 성능 문제가 발생할 수 있습니다.

자세한 내용은 SQL Server를 통한 보다 빠른 쿼리(전자책)를 참조하세요.

쿼리 최적화

다양한 도구를 함께 사용하여 쿼리 성능을 분석하고 개선하는 데 도움을 줍니다. 쿼리 최적화 프로그램, 실행 계획, 쿼리 저장소가 있습니다.

쿼리 최적화 프로그램

쿼리 최적화 프로그램은 SQL Server의 가장 중요한 구성 요소 중 하나입니다. 쿼리 최적화 프로그램을 사용하여 쿼리를 분석하고 필요한 데이터에 가장 효율적으로 액세스하는 방법을 결정합니다. 쿼리 최적화 프로그램에 대한 입력은 쿼리, 데이터베이스 스키마(테이블 및 인덱스 정의), 데이터베이스 통계로 구성됩니다. 쿼리 최적화 프로그램의 결과물은 실행 계획입니다.

자세한 내용은 SQL Server 쿼리 최적화 프로그램을 참조하세요.

실행 계획

실행 계획은 원본 테이블을 액세스할 수 있도록 순서를 정의하고 각 테이블에서 데이터를 추출하는 데 사용되는 메소드입니다. 최적화는 잠재적인 가능성이 많은 계획에서 하나의 실행 계획을 선택하는 프로세스입니다. 사용할 수 있는 각 실행 계획에는 사용된 컴퓨팅 리소스 용량으로 관련 비용이 포함되어 있으며, 쿼리 최적화 프로그램은 가장 낮은 예상 비용이 포함된 항목을 선택합니다.

데이터베이스의 변경하는 조건에 맞춰서 SQL Server에서 동적으로 조정해야 합니다. 쿼리 실행 계획에서 문제가 재발하면 성능이 크게 저하될 수 있습니다. 데이터베이스의 변경으로 인해 데이터베이스의 새 상태를 기준으로 실행 계획이 비효율적으로 되거나 유효하지 않을 수 있습니다. SQL Server는 실행 계획을 무효화하는 변경 내용을 감지하여 계획을 유효하지 않음으로 표시합니다.

그런 다음 쿼리를 실행하는 다음 연결에 대해 새 계획을 다시 컴파일해야 합니다. 계획을 무효화하는 조건은 다음과 같습니다.

• 쿼리에서 참조하는 테이블 또는 보기의 변경 내용(ALTER TABLE 및 ALTER VIEW).

• 실행 계획에 사용되는 인덱스의 변경 내용

• UPDATE STATISTICS를 비롯한 문에서 명시적으로 또는 자동으로 생성되는 실행 계획에서 사용하는 통계에 대한 업데이트입니다.

자세한 내용은 실행 계획을 참조하세요.

쿼리 저장소

쿼리 저장소는 실행 계획 선택 및 성능에 대 한 통찰력을 제공니다. 실행 계획 변경으로 인해 발생하는 성능 차이를 신속하게 찾을 수 있도록 지원하여 성능 문제 해결을 단순화합니다. 쿼리 저장소는 쿼리 기록, 계획, 런타임 통계, 대기 통계 등 원격 분석 데이터를 수집합니다. ALTER DATABASE 문을 사용하여 쿼리 저장소를 구현합니다.

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

자세한 내용은 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 140;

쿼리 방법

SQL Server에서 여러 가지 방법으로 쿼리를 사용할 수 있으며, 각각의 방법에는 이점이 있습니다. 장점을 파악해야 Access 솔루션에 적절한 항목을 선택할 수 있습니다. TSQL 쿼리를 만드는 가장 좋은 방법은 SSMS(SQL Server Management Studio) Transact-SQL 편집기를 사용하여 대화형으로 편집하고 테스트하는 것입니다. 이 편집기에 있는 IntelliSense를 통해 올바른 키워드를 선택하고 구문 오류를 확인할 수 있습니다.

보기

SQL Server에서, 보기는 하나 이상의 테이블 또는 다른 보기에서 볼 수 있는 가상 테이블과 비슷합니다. 그러나 보기는 쿼리의 테이블과 동일하게 참조할 수 있습니다. 보기를 통해 쿼리의 복잡성을 숨기고 행 및 열 집합을 제한하여 데이터를 보호할 수 있습니다. 다음은 간단한 보기의 예입니다.

CREATE VIEW HumanResources.EmployeeHireDate AS  
SELECT p.FirstName, p.LastName, e.HireDate  
FROM HumanResources.Employee AS e JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;

최적의 성능을 유지하고 보기 결과를 편집하려면 테이블처럼, 데이터베이스에 계속 유지되는 인덱싱된 보기를 만들고 여기에 스토리지를 할당하여 테이블처럼 쿼리할 수 있습니다. Access에서 이 기능을 사용하려면 테이블에 연결하는 것과 같은 방식으로 보기에 연결 합니다. 인덱싱된 보기의 예는 다음과 같습니다.

CREATE VIEW Sales.vOrders  
WITH SCHEMABINDING  
AS  
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,  
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT  
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o  
    WHERE od.SalesOrderID = o.SalesOrderID  
    GROUP BY OrderDate, ProductID;  

CREATE UNIQUE CLUSTERED INDEX IDX_V1   
    ON Sales.vOrders (OrderDate, ProductID);  

그러나 제한 사항이 있습니다. 두 개 이상의 기본 테이블에 영향을 주거나 보기에 집계 함수나 DISTINCT 절이 포함된 경우 데이터를 업데이트할 수 없습니다. SQL Server가 삭제할 레코드를 모른다는 오류 메시지를 반환하는 경우 보기에 삭제 트리거를 추가해야 할 수 있습니다. 마지막으로, Access 쿼리를 사용하는 경우와 마찬가지로 ORDER BY 절을 사용할 수 없습니다.

자세한 내용은 보기 및 인덱싱된 보기 만들기를 참조하세요.

저장된 프로시저

저장된 프로시저는 입력 매개 변수를 사용하고, 출력 매개 변수를 반환하며 상태 값으로 성공 또는 실패를 나타내는 하나 이상의 TSQL 문 그룹입니다. Access 프런트 엔드와 SQL Server 백 엔드 사이의 중간 계층 역할을 합니다. 저장된 프로시저는 SELECT 문처럼 간단할 수도 있고, 프로그램처럼 복잡할 수도 있습니다. 예를 들면 다음과 같습니다.

CREATE PROCEDURE HumanResources.uspGetEmployees   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   
    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  

Access에서 저장 프로시저를 사용하는 경우 일반적으로 폼 또는 보고서로 결과 집합을 반환합니다. 그러나 DDL 또는 DML 문과 같이 결과를 반환하지 않는 다른 작업을 수행할 수도 있습니다. 통과 쿼리를 사용하는 경우에는 레코드 반환 속성을 적절하게 설정해야 합니다.

자세한 내용은 저장된 프로시저를참조하세요.

일반적인 테이블 식

CTE(Common Table Expressions)는 명명 된 결과 집합을 생성하는 임시 테이블과 유사 합니다. 단일 쿼리 또는 DML 문을 실행하는 경우에만 존재합니다. CTE는 SELECT 문 또는이를 사용하는 DML 문과 동일한 코드 줄에 빌드됩니다. 하지만 임시 테이블이나 보기를 만들고 사용하는 것은 일반적으로 2단계 프로세스입니다. 예를 들면 다음과 같습니다.

-- Define the CTE expression name and column list.  
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
AS  
-- Define the CTE query.  
(  
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
)  
-- Define the outer query referencing the CTE name.  
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
FROM Sales_CTE  
GROUP BY SalesYear, SalesPersonID  
ORDER BY SalesPersonID, SalesYear;

CTE에는 다음과 같은 몇 가지 이점이 있습니다.

• CTE는 투명하므로, 보기처럼 영구 데이터베이스 개체로 만들 필요가 없습니다.

• 쿼리 또는 DML 문에 동일한 CTE를 두 번 이상 참조하여 코드를 관리하기 쉽습니다.

• CTE를 참조하는 쿼리를 사용하여 커서를 정의할 수 있습니다.

자세한 내용은 WITH common_table_expression를 참조하세요.

사용자 정의 함수

UDF(사용자 정의 함수)를 사용하여 쿼리 및 계산을 수행하고 스칼라 값이나 데이터 결과 집합을 반환할 수 있습니다. 이는 매개 변수를 허용하고 계산과 같은 작업을 수행하며 해당 작업의 결과를 값으로 반환하는 프로그래밍 언어로 된 함수와 동일합니다. 예를 들면 다음과 같습니다.

CREATE FUNCTION dbo.ISOweek (@DATE datetime)  
RETURNS int WITH SCHEMABINDING -- Helps improve performance
WITH EXECUTE AS CALLER  
AS  
BEGIN  
     DECLARE @ISOweek int;  
     SET @ISOweek= DATEPART(wk,@DATE)+1  
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');  
-- Special cases: Jan 1-3 may belong to the previous year  
     IF (@ISOweek=0)   
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1   
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;  
-- Special case: Dec 29-31 may belong to the next year  
     IF ((DATEPART(mm,@DATE)=12) AND   
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))  
          SET @ISOweek=1;  
     RETURN(@ISOweek);  
END;  
GO  
SET DATEFIRST 1;  
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';  

UDF에는 몇 가지 제한 사항이 있습니다. 예를 들어, 명확하지 않은 특정 시스템 함수를 사용하거나, DML 또는 DDL 문을 수행하거나, 동적 SQL 쿼리를 수행할 수 없습니다.

자세한 내용은 사용자 정의 함수를 참조하세요.

맨 위로 이동

키 및 인덱스 추가

사용하는 데이터베이스 시스템에 상관 없이, 키와 인덱스는 함께 전달됩니다.

키

SQL Server에서 각 테이블에 대한 기본 키와 각 관련 테이블에 대한 외래 키를 만들어야 합니다. Access AutoNumber 데이터 형식에 대한 SQL Server의 해당 기능은 IDENTITY 속성입니다. 이 속성을 사용하여 키 값을 만들 수 있습니다. 모든 숫자 열에 이 속성을 적용하면 읽기 전용이 되고 데이터베이스 시스템에서 유지 관리됩니다. IDENTITY 열을 포함하는 테이블에 레코드를 삽입하는 경우 IDENTITY 열 값이 1에서 시작하여 1씩 자동으로 증분하지만, 이 값을 인수를 사용하여 제어할 수 있습니다.

자세한 내용은 테이블, IDENTITY(속성) 만들기를 참조하세요.

인덱스

언제나처럼, 인덱스를 선택하면 쿼리 속도와 업데이트 비용 사이에서 균형을 유지할 수 있습니다. Access에는 한 가지 유형의 인덱스가 있지만, SQL Server에는 12가지 유형이 있습니다. 다행히 쿼리 최적화 프로그램을 사용하여 가장 효율적인 인덱스를 확실하게 선택할 수 있습니다. Azure SQL에서 자동 조정 기능인 자동 인덱스 관리를 사용하여 인덱스를 추가하거나 제거하는 것이 좋습니다. Access와 달리, SQL Server에서 외래 키에 대한 인덱스를 직접 만들어야 합니다. 인덱싱된 보기에서 인덱스를 만들어 쿼리 성능을 개선할 수도 있습니다. 인덱싱된 보기의 단점은 보기의 기본 테이블에서 데이터를 수정하는 경우 보기도 업데이트되므로 오버헤드가 증가하는 것입니다. 자세한 내용은 SQL Server 인덱스 아키텍처 및 설계 가이드 및 인덱스를 참조하세요.

맨 위로 이동

트랜잭션 수행

Access를 사용하는 경우에는 OLTP(온라인 트랜잭션 프로세스)를 수행하는 것이 어렵지만, SQL Server를 사용하면 상대적으로 쉽습니다. 트랜잭션은 성공한 경우 모든 데이터 변경 내용을 커밋하고 실패한 경우 변경 내용을 롤백하는 단일 작업 단위입니다. 트랜잭션에는 ACID라는 4가지 속성이 있어야 합니다.

• 원자성    트랜잭션은 원자적 작업 단위이므로, 모든 데이터 수정 내용이 수행되거나 아무런 작업도 수행되지 않습니다.

• 일관성    작업이 완료되면 트랜잭션에 모든 데이터가 일관된 상태로 유지되어야 합니다. 즉, 모든 데이터 무결성 규칙이 적용됩니다.

• ISOLATION    동시 트랜잭션에서 수행한 변경 내용은 현재 트랜잭션에서 격리됩니다.

• 내구성    트랜잭션이 완료되면 시스템 오류가 발생하는 경우에도 변경 내용이 영구적으로 적용됩니다.

트랜잭션을 사용하여 데이터 무결성(예: ATM 현금 인출 또는 자동 입금)을 보장할 수 있습니다. 명시적, 암시적 또는 일괄 처리된 트랜잭션을 수행할 수 있습니다. 다음은 두 가지 TSQL 예제입니다.

-- Using an explicit transaction

BEGIN TRANSACTION;  
DELETE FROM HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT;  

-- the ROLLBACK statement rolls back the INSERT statement, but the created table still exists.

CREATE TABLE ValueTable (id int);  
BEGIN TRANSACTION;  
       INSERT INTO ValueTable VALUES(1);  
       INSERT INTO ValueTable VALUES(2);  
ROLLBACK;

자세한 내용은 트랜잭션을 참조하세요.

맨 위로 이동

제약 조건 및 트리거 사용

모든 데이터베이스에 데이터 무결성을 유지 관리하는 방법이 있습니다.

제한

Access에서는 외래 키-기본 키 페어링, 연속 업데이트 및 삭제 및 유효성 검사 규칙을 사용하여 테이블 관계에 참조 무결성을 적용합니다. 자세한 내용은 테이블 관계 가이드 및 유효성 검사 규칙을 사용하여 데이터 입력 제한을 참조하세요.

SQL Server에서 UNIQUE 및 CHECK 제약 조건을 사용하며, 이 제약 조건은 SQL Server 테이블에서 데이터 무결성을 강화하는 데이터베이스 개체입니다. 다른 테이블에서 값이 유효한지 확인하려면 외래 키 제약 조건을 사용합니다. CHECK 제약 조건을 사용하여 열의 값이 특정 범위 내에 있는지 확인할 수 있습니다. 이 개체는 첫 번째 방어선이며 효율적인 작업을 위해 설계되었습니다. 자세한 내용은 Unique 제약 조건 및 Check 제약 조건을 참조하세요.

트리거

Access에 데이터베이스 트리거가 없습니다. SQL Server에서는 트리거를 사용하여 복잡 한 데이터 무결성 규칙을 적용하고 서버에서이 비즈니스 논리를 실행할 수 있습니다. 데이터베이스 트리거는 특정 작업이 데이터베이스 내에서 발생하는 경우 실행되는 저장 프로시저입니다. 트리거는 테이블에 레코드를 추가하거나 삭제하는 등의 이벤트이며 저장된 프로시저를 실행합니다. Access 데이터베이스는 사용자가 데이터를 업데이트하거나 삭제하려고 할 때 참조 무결성을 보장할 수 있지만, SQL Server에는 복잡한 트리거 집합이 있습니다. 예를 들어 대량으로 레코드를 삭제하고 데이터 무결성을 보장하는 트리거를 프로그래밍할 수 있습니다. 테이블과 보기에도 트리거를 추가할 수 있습니다.

자세한 내용은 트리거 - DML, 트리거 - DDL 및 T-SQL 트리거 설계를 참조하세요.

맨 위로 이동

계산된 열

Access에서 계산된 열을 쿼리에 추가하고 식을 빌드하여 계산된 열을 만들고 다음과 같은 예가 있습니다.

Extended Price: [Quantity] * [Unit Price]

SQL Server에서 등가의 기능을 계산된 열이라고 합니다. 이는 PERSISTED로 표시되는 경우를 제외하고는 테이블에 물리적으로 저장되지 않는 가상 열입니다. 계산된 열과 같이, 계산된 열에서는 식에 있는 다른 열의 데이터를 사용합니다. 계산된 열을 만들려면 테이블에 추가합니다. 예:

CREATE TABLE dbo.Products   
(  
    ProductID int IDENTITY (1,1) NOT NULL  
  , QtyAvailable smallint  
  , UnitPrice money  
  , InventoryValue AS QtyAvailable * UnitPrice  
);  

자세한 내용은 테이블에서 계산된 열 지정을 참조하세요.

맨 위로 이동

데이터에 타임스탬프 작성

경우에 따라 레코드를 만들 때 타임스탬프를 기록하는 테이블 필드를 추가하여 데이터 입력을 기록할 수 있습니다. Access에서 =Now()의 기본값을 사용하여 날짜 열을 간단히 만들 수 있습니다. SQL Server에서 날짜 또는 시간을 기록하려면 SYSDATETIME()을(를) 기본값으로 하여 datetime2 데이터 형식을 사용합니다.

참고    데이터에 타임스탬프를 추가하여 rowversion을 혼동하지 않도록 합니다. SQL Server에서 키워드 타임스탬프는 rowversion의 동의어이지만, rowversion 키워드를 사용해야 합니다. SQL Server에서 rowversion은 데이터베이스 내에서 자동으로 생성된 고유한 이진수를 표시하는 데이터 형식이며, 일반적으로 버전 스탬프 테이블 행의 메커니즘으로 사용됩니다. 그러나 rowversion 데이터 형식은 증가하는 값이고 날짜 또는 시간을 보존하지 않으며 행에 타임스탬프를 만들도록 설계되지 않았습니다.

자세한 내용은 rowversion를 참조하세요. Rowversion을 사용하여 레코드 충돌을 최소화하는 방법에 대한 자세한 내용은 Access 데이터베이스를 SQL Server로 마이그레이션을 참조하세요.

맨 위로 이동

대형 개체 관리

Access에서는 첨부 파일 데이터 형식을 사용하여 파일, 사진, 이미지 등의 구조화되지 않은 데이터를 관리합니다. SQL Server 용어로 비구조적 데이터를 Blob (Binary Large Object)이라고 하며 이를 사용하는 작업 방법이 몇 가지 있습니다.

FILESTREAM    Varbinary(max) 데이터 형식을 사용하여 구조화되지 않은 데이터를 데이터베이스 대신 파일 시스템에 저장합니다. 자세한 내용은 Transact-SQL을 사용한Access FILESTREAM 데이터를 참조하세요.

FileTable    FileTables라는 특별한 테이블에 Blob을 저장하며, 파일 시스템에 저장된 것처럼 클라이언트 응용 프로그램을 수정하지 않고 Windows 응용 프로그램과의 호환성을 제공합니다. FileTable에는 FILESTREAM을 사용해야 합니다. 자세한 내용은 FileTables를 참조하세요.

BLOB 저장소(RBS) 제거    서버에 직접 저장하지 않고 필수품 저장소 솔루션에 BLOB(Binary Large Object)를 저장합니다. 그러면 공간이 절약되고 하드웨어 리소스를 줄여줍니다. 자세한 내용은 BLOB(Binary Large Object) 데이터를 참조하세요.

맨 위로 이동

계층적 데이터 작업

Access와 같은 관계형 데이터베이스가 매우 유연하지만, 계층적 관계를 사용하여 작업하는 것은 예외이며 복잡한 SQL 문이나 코드가 필요한 경우도 종종 있습니다. 계층 구조 데이터에는 조직 구조, 파일 시스템, 언어 용어 분류 및 웹 페이지 사이의 링크 그래프가 있습니다. SQL Server에는 기본 제공 hierarchyid 데이터 형식과 계층적 함수 집합이 제공되어 계층적 데이터를 간편하게 저장하고 쿼리하며 관리할 수 있습니다.

자세한 내용은 계층적 데이터 및 자습서를 참조하세요. Hierarchyid 데이터 형식을 사용합니다.

맨 위로 이동

JSON 텍스트 조작

JSON(JavaScript Object Notation)은 사람이 읽을 수 있는 텍스트를 사용하여 비동기 브라우저-서버 통신에서 데이터를 속성-값 쌍으로 전송하는 웹 서비스입니다. 예:

{
"firstName": "Mary",
"lastName": "Contrary",
"spouse": null,
"age": 27
}

Access에는 JSON 데이터를 관리할 수 있는 기본 제공 방법이 없지만, SQL Server에서는 JSON 데이터를 원활하게 저장, 인덱싱, 쿼리 및 추출할 수 있습니다. 테이블의 JSON 텍스트를 변환하여 저장하거나 데이터의 형식을 JSON 텍스트로 지정할 수 있습니다. 예를 들어, 웹 앱에 맞게 쿼리 결과의 형식을 JSON으로 지정하거나 행 및 열에 JSON 데이터 구조를 추가하는 것이 좋습니다.

참고    VBA에서는 JSON이 지원되지 않습니다. 다른 방법으로는 MSXML 라이브러리를 사용 하 여 VBA에서 XML을 사용할 수 있습니다.

자세한 내용은 SQL Server의JSON 데이터를 참조하세요.

맨 위로 이동

리소스

이번이 SQL Server 및 Transact SQL(TSQL)에 대해 자세히 알아볼 좋은 기회입니다. 지금까지 살펴본 바와 같이, Access와 같은 기능도 많지만, Access에 없는 기능도 있습니다. 다음 단계를 살펴보려면 몇 가지 학습 리소스를 참조하세요.

맨 위로 이동