'MS-SQL'에 해당되는 글 59건

  1. 2020.04.29 SQL2016 AlwaysOn Basic 구성 가이드(AD없음) 2
  2. 2020.04.28 MS-SQL DMA(Data Migration Assistant) 가이드
  3. 2017.07.13 MSSQL2014 악성쿼리분석(ClearTrace)
  4. 2017.07.13 MSSQL2005 데이터베이스 복제
  5. 2017.07.13 MS SQL2008R2 Cluster IP 변경 절차 1
  6. 2011.10.06 MS-SQL2005 비정상적으로 분리된 .mdf .ldf파일만 있는 데이터 베이스 복구
  7. 2009.11.27 Read80Trace (sql2k) / ReadTrace (sql2k5)
  8. 2009.11.27 SQL Server 구성 옵션 - default trace enabled
  9. 2009.11.27 Read80Trace 사용법
  10. 2009.11.20 SQL2005 미러링 AD구성없이 인증서 사용구성
  11. 2009.11.20 데이터베이스 미러링의 아웃바운드 연결에 대한 인증서 사용 허용(Transact-SQL)
  12. 2009.03.31 MSSQL2005에서 특정 사용자의 DB만 보이게 하는 방법
  13. 2009.03.31 SQL Injectin 공격 방어를 위한 DB 설정 변경
  14. 2009.02.09 SQL injection 방어대책
  15. 2008.12.30 sql injection DB대량변조 삽입 스크립트 제거
  16. 2008.12.30 SQL Injection으로인한 DB대량 변조시 조치 방법 입니다
  17. 2008.11.25 SQL 트랜잭션 로그가 가득 차는 원인
  18. 2008.08.08 MS-SQL table 복사
  19. 2008.08.06 MS-SQL 테이블 개체소유자 변경
  20. 2008.08.06 MS-SQL 데이터정렬 변경하기
  21. 2008.08.06 MS-SQL 테이블별 용량체크
  22. 2008.07.23 중소기업을 위한 SQL Server 기본 모니터링 - 3
  23. 2008.07.23 중소기업을 위한 SQL Server 기본 모니터링 - 2
  24. 2008.07.23 중소기업을 위한 SQL Server 기본 모니터링 - 1
  25. 2008.07.16 MS-SQL 데이터정렬 변경하기
  26. 2008.07.16 [MS-SQL] 사용자 로그인 이름이 없어진 경우
  27. 2008.07.16 DB개체의 모든 소유자 변경
  28. 2008.02.27 SQL서버 진단을 위한 주요 성능카운터
  29. 2007.11.12 MS-SQL 성능향상을 위한 시스템상태 점검사항 및 설정사항
  30. 2007.10.25 SA계정 패스워드 변경하기

SQL2016 AlwaysOn Basic 소개

SQL2016 이전 버전의 SQL가용성 그룹은 Enterprise Edition의 기능이었으며 Standard Edition 에서는 사용할 수 없었습니다. 하지만 SQL Server 2016의 Standard Edition에서는 Basic 가용성 그룹을 지원합니다.

Enterprise보다는 기능 제약이 있고 Active/Active 고가용성을 지원하지 않으나 공유 스토리지없이 MSCS와 유사한 Active/Stanby(자동FaileOver)의 SQL고가용성을 제공하며 AD없이 구축 가능 합니다.

하지만 1개의 데이터베이스만 고가용성 지원하는 문제점이 있습니다.

제약 사항은 다음과 같습니다.

  • 2개의 Replica만 가능(Primary 1대, Secondary 1대)

  • 보조 복제본에서 읽기 불가능

  • 보조 복제본에서 백업 불가능

  • 1개의 데이터베이스만 가능

다음과 같은 기능은 지원합니다.

  • Listener 지원

  • 자동 Failover

  • 동기/비동기 선택 가능

소규모로 사용하는 경우나, standard edition에서 1개의 데이터베이스 이중화 고려시 비용이 유리합니다.

Windows2016 서버 구성

Test서버 구성 현황(2Node로 구성)

항목

Node1

Node2

서버

VM

VM

OS

Windows2016 Standard Edition

Windows2016 Standard Edition

CPU

4vCore

4vCore

메모리

8GB

8GB

Nic

2Port

2Port

HDD

200GB(동적)

200GB(동적)

설치 역할 및 기능

.Net Framework 3.5

장애조치(Failover) 클러스터링

SQL2016 Standard Edition SP2

.Net Framework 3.5

장애조치(Failover) 클러스터링

SQL2016 Standard Edition SP2

IP

Public : 203.231.238.139

Private : 192.168.1.101

Public :203.231.238.140

Private : 192.168.1.102

Cluster ip

sqlbag : 192.168.1.110

sqlbag : 192.168.1.110

 

  • Node1, Node2 Windows2016 Standard Edition 설치

- 200GB C:드라이브 설정

- 서버구성후 Windows Update는 최신버전으로 적용(Test시는 미적용함)

  • 네트워크 설정

- Publc IP설정(Node1,2)

- Private IP설정(Node1,2)

  • 컴퓨터 이름 및 DNS 접미사 변경

- AD를 사용하지 않으므로 클러스터 구성을 위해 변경작업후 서버 재시작

  • Host파일 수정 Node1, Node2

  • Node1, Node2의 Powershell 관리자 권한 으로 실행후 아래 명령어 수행

 

new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1

 

  • 이 정책을 설정하지 않으면 기본 제공 관리자 계정을 사용하여 클러스터를 만들려고 할 때 다음 오류가 표시됩니다.

  • Node1에서 클러스터 생성

 

new-cluster -name sqlbag –Node node1,node2 -StaticAddress 192.168.1.110 -NoStorage –AdministrativeAccessPoint DNS

 

  • Node1, Node2에서 SQL2016 Standard Edition 설치

- Node1의 인스턴스는 MSSQLSERVER è Node1 로 변경

- Node2의 인스턴스는 MSSQLSERVER è Node2 로 변경

- 설치도중 Data디렉토리는 두 서버모두 동일하게 설정

예) C:\Program Files\Microsoft SQL Server\MSSQL13.SQL\MSSQL\DATA\

  • Node1, Node2에서 관리도구설치(SQL Server Management Studio) 설치

  • Node1, Node2 SQL구성관리자 è SQL Server 서비스 è SQL Server(MSSQLSERVER) 속성 è AlwaysOn 고가용성에서 사용 체크

 

SQL2016서버 설정

- Node1의 SSMS에서 마스터키, 인증서, 끝점 생성 및 인증서 생성후 C:\Temp폴더에 백업

 

  • USE master

  • GO

  •  

  • -- Create a database master key

  • CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!'

  • GO

  •  

  • -- Create a new certificate

  • CREATE CERTIFICATE SQLBAG_Certificate_Node1_Private

  • WITH SUBJECT = 'SQLBAG_Certificate_Private - Node 1',

  • START_DATE = '20180912'

  • GO

  •  

  • -- Backup the public key of the certificate to the filesystem

  • BACKUP CERTIFICATE SQLBAG_Certificate_Node1_Private

  • TO FILE = 'c:\temp\SQLBAG_Certificate_Node1_Public.cert'

  • GO

  •  

  • -- Create an endpoint for the Availability Group

  • CREATE ENDPOINT SQLBAG_Endpoint

  • STATE = STARTED

  • AS TCP

  • (

  •  LISTENER_PORT = 5022

  • )

  • FOR DATABASE_MIRRORING

  • (

  •  AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_Node1_Private,

  •  ROLE = ALL,

  •  ENCRYPTION = REQUIRED ALGORITHM AES

  • )

  • GO

 

  • Node2의 SSMS에서 마스터키, 인증서, 끝점 생성 및 인증서 생성후 C:\Temp폴더에 백업

 

  • USE master

  • GO

  •  

  • -- Create a database master key

  • CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd1!'

  • GO

  •  

  • -- Create a new certificate

  • CREATE CERTIFICATE SQLBAG_Certificate_Node2_Private

  • WITH SUBJECT = 'SQLBAG_Certificate_Private - Node 2',

  • START_DATE = '20180912'

  • GO

  •  

  • -- Backup the public key of the certificate to the filesystem

  • BACKUP CERTIFICATE SQLBAG_Certificate_Node2_Private

  • TO FILE = 'c:\temp\SQLBAG_Certificate_Node2_Public.cert'

  • GO

  •  

  • -- Create an endpoint for the Availability Group

  • CREATE ENDPOINT SQLBAG_Endpoint

  • STATE = STARTED

  • AS TCP

  • (

  •  LISTENER_PORT = 5022

  • )

  • FOR DATABASE_MIRRORING

  • (

  •  AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_Node2_Private,

  •  ROLE = ALL,

  •  ENCRYPTION = REQUIRED ALGORITHM AES

  • )

  • GO

 

  • Node1의 SSMS에서 사용자 생성후 끝점 연결 설정

 

  • -- Create login for the other node

  • CREATE LOGIN Node2Login WITH PASSWORD = 'passw0rd1!'

  • GO

  •  

  • -- Create user for the login

  • CREATE USER Node2User FOR LOGIN Node2Login

  • GO

  •  

  • -- Import the public key portion of the certificate from the other node

  • CREATE CERTIFICATE SQLBAG_Certificate_Node2_Public

  • AUTHORIZATION Node2User

  • FROM FILE = 'c:\temp\SQLBAG_Certificate_Node2_Public.cert'

  • GO

  •  

  • -- Grant the CONNECT permission to the login

  • GRANT CONNECT ON ENDPOINT::SQLBAG_Endpoint TO Node2Login

  • GO

 

  • Node2의 SSMS에서 사용자 생성후 끝점 연결 설정

 

  • -- Create login for the other node

  • CREATE LOGIN Node1Login WITH PASSWORD = 'passw0rd1!'

  • GO

  •  

  • -- Create user for the login

  • CREATE USER Node1User FOR LOGIN Node1Login

  • GO

  •  

  • -- Import the public key portion of the certificate from the other node

  • CREATE CERTIFICATE SQLBAG_Certificate_Node1_Public

  • AUTHORIZATION Node1User

  • FROM FILE = 'c:\temp\SQLBAG_Certificate_Node1_Public.cert'

  • GO

  •  

  • -- Grant the CONNECT permission to the login

  • GRANT CONNECT ON ENDPOINT::SQLBAG_Endpoint TO Node1Login

  • GO

 

  • Node1의 SSMS에서 Database생성 및 백업

 

  • USE master

  • GO

  •  

  • -- Create a new database

  • CREATE DATABASE TestDatabase1

  • GO

  •  

  • -- Use the database

  • USE TestDatabase1

  • GO

  •  

  • -- Create a simple table

  • CREATE TABLE Foo

  • (

  •  Bar INT NOT NULL

  • )

  • GO

  •  

  • -- Make a Full Backup of the database

  • BACKUP DATABASE TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.bak'

  • GO

  •  

  • USE master

  • GO

  •  

  • -- Create a new Availability Group with 2 replicas

  • CREATE AVAILABILITY GROUP TestAG

  • WITH

  • (

  •  AUTOMATED_BACKUP_PREFERENCE = PRIMARY,

  •  BASIC,

  •  DB_FAILOVER = OFF,

  •  DTC_SUPPORT = NONE

  • )

  • FOR DATABASE [TestDatabase1]

  • REPLICA ON

  • 'NODE1' WITH

  • (

  •  ENDPOINT_URL = 'TCP://node1.test.com:5022',

  •  FAILOVER_MODE = AUTOMATIC,

  •  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

  •  SECONDARY_ROLE

  •  (

  •               ALLOW_CONNECTIONS = NO

  •  )

  • ),

  • 'NODE2' WITH

  • (

  •  ENDPOINT_URL = 'TCP://node2.test.com:5022',

  •  FAILOVER_MODE = AUTOMATIC,

  •  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

  •  SECONDARY_ROLE

  •  (

  •               ALLOW_CONNECTIONS = NO

  •  )

  • )

  • GO

 

  • Node2의 SSMS에서 TestAG 가용성 그룹에 연결

 

  • ALTER AVAILABILITY GROUP [TestAG] JOIN

  • GO

 

  • Node1의 SSMS에서 trn백업

 

  • -- Make a TxLog Backup of the database

  • BACKUP LOG TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.trn'

  • GO

 

  • 백업받은 .bak, .trn 파일 Node2로 이동하여 복원하고 해당 Database 가용성 그룹으로 이동

 

  • -- Restore the Full Backup with NORECOVEY

  • RESTORE DATABASE TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.bak' WITH NORECOVERY

  • GO

  •  

  • -- Restore the TxLog Backup with NORECOVERY

  • RESTORE LOG TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.trn' WITH NORECOVERY

  • GO

  •  

  • -- Move the database into the Availability Group

  • ALTER DATABASE TestDatabase1 SET HADR AVAILABILITY GROUP = TestAG

  • GO

 

  • SSMS에서 AlwaysOn 고가용성 구성화면

  • 장애 조치(Failover)의 역할 소유자 노드

  • 장애 조치(Failover)의 노드의 작동상태

 

가용성 그룹 DB Restore

  • DB복구를 위해 가용성 그룹에서 데이터베이스 제거를 선택합니다.

  • 확인을 눌러 제거완료

  • 데이터베이스 속성에서 ‘Sngle_user’로 엑세스 제한 변경

  • 데이터베이스 복원

  • 복원 옵션 기존 데이터베이스 덮어쓰기 선택후 확인하여 복원 완료

  • 복원 완료후 가용성 그룹에 데이터 베이스 추가를위해 데이터베이스 추가 선택

  • 다음선택

  • 데이터베이스 선택

  • 기존 보조 복제본에 연결. 연결선택후 인증

  • 데이터 동기화 기본설정에서 초기 데이터 동기화 건너뛰기 선택

  • 유효성 검사 다음선택

  • 요약

  • 완료

  • Node1서버에서 데이터베이스 풀백업 및 로그백업 수행후 Node2로 복사

 

BACKUP DATABASE TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.bak'

GO

BACKUP LOG TestDatabase1 TO DISK = 'c:\temp\TestDatabase1.trn'

GO

 

  • Node2서버에서 데이터베이스 풀백업 및 로그백업본을 복원

 

RESTORE DATABASE TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.bak' WITH NORECOVERY

GO 

RESTORE LOG TestDatabase1 FROM DISK = 'c:\temp\TestDatabase1.trn' WITH NORECOVERY

GO

 

  • Node2 서버에서 데이터베이스 가용성 그룹에 조인

 

ALTER DATABASE TestDatabase1 SET HADR AVAILABILITY GROUP = TestAG

GO

 

가용성 그룹 작동 확인 및 Failover Test

  • 가용성 그룹 TestAG 우클릭후 대시보드 표시선택

  • 가용성 그룹 정상작동 상태 확인

  • Failover Test를 위해 가용성 그룹 우클하여 장애 조치 선택

  • 장애조치시 실시간마이그레이션 적용되어 서비스 끊김에 체감은 없으나 외부 Ping Test시 1칸 빠짐

 

 

Posted by 시스템매니아
,
MS-SQL DMA(Data Migration Assistant) 소개
 
SQL2005, SQL2008등의 기술지원 종료이슈로 보안취약점등에 Windows Update가 제공되지않으므로 SQL Server Migration이 요구되는 상황입니다. SQL Server Migration시 DMA(Data Migration Assistant)를 이용하여 이슈사항 점검하고 진단합니다.
주요 점검 및 진단내용 :
  • 마이그레이션할 SQL Server 인스턴스를 평가 합니다.
  • 마이그레이션 차단 문제: 호환성 문제는 마이그레이션SQL Server 데이터베이스를 검색 합니다. DMA는 해당 문제를 해결 하기 위한 권장 사항을 제공 합니다.
  • 부분적으로 지원 되거나 지원 되지 않는 기능: 현재 원본 SQL Server 인스턴스에 사용 되는 부분적으로 지원 되거나 지원 되지 않는 기능을 검색 합니다. DMA는 마이그레이션 프로젝트에 통합할 수 있습니다 사용 가능한 대체 방법 권장 사항 집합을 제공 합니다.
  • SQL server 업그레이드에 영향을 줄 수 있는 문제를 검색 합니다. 이러한 호환성 문제를 설명 하 고 다음 범주로 구성 됩니다.
  • 주요 변경 내용
  • 동작 변경 내용
  • 사용되지 않는 기능
  • 데이터베이스는 업그레이드 후에 활용할 수 있는 대상 SQL Server 플랫폼의 새로운 기능을 검색 합니다. 이러한 기능 권장 사항으로 설명 하 고 다음 범주로 구성 됩니다.
  • 성능
  • 보안
  • 저장소
SQL지원버전 :
 
원본
대상
SQL버전
SQL Server 2005
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
Windows의 SQL Server 2017
SQL Server 2012
SQL Server 2014
SQL Server 2016
Windows 및 Linux의 SQL Server 2017
Azure SQL 데이터베이스
 
DMA(Data Migration Assistant)설치
 
  • 설치완료
  • 동의선택후 다음선택
  • 설치선택
  • 설치진행중
  • 설치완료
  • DMA 실행
  • ‘+’ 선택하여 신규 프로젝트 생성. 생성시 원본과 대상버전 선택
  • Report Type 선택
  • 원본 SQL정보 입력하여 연결
  • 연결된 원본 SQL서버에서 진행할 DB선택
  • ‘Start Assessment’ 선택하여 분석 시작
  • 분석진행중이며 DB사이즈에 따라 변동
  • 분석완료
 
진달결과 분석
  • 실제 운영중인 SQL2008R2의 DB를 분석진행한 결과
Behavior changes : Procedure:
Impact
 
This rule checks stored procedures, functions, views and triggers for use of ORDER BY clause specifying ordinal column numbers as sort columns. A sort column can be specified as a nonnegative integer representing the position of the name or alias in the select list, but this is not recommended. An integer cannot be specified when the order_by_expression appears in a ranking function. A sort column can include an expression, but when the database is in SQL 90 compatibility mode or higher, the expression cannot resolve to a constant.
이 규칙은 순서 열 번호를 정렬 열로 지정하는 ORDER BY 절 사용을 위해 저장 프로 시저, 함수, 뷰 및 트리거를 검사합니다. 정렬 열은 선택 목록에서 이름이나 별명의 위치를 나타내는 음이 아닌 정수로 지정할 수 있지만 권장하지는 않습니다. order_by_expression이 순위 지정 함수에 표시되면 정수를 지정할 수 없습니다. 정렬 열에는 표현식이 포함될 수 있지만 데이터베이스가 SQL 90 호환 모드 이상일 때 표현식은 상수로 해석 될 수 없습니다.
 
Recommendation
 
Specify the sort column as a name or column alias rather than hard coding the ordinal
순서 열을 하드 코딩하는 대신 이름 또는 열 별칭으로 정렬 열을 지정하십시오.
 
의견 :
 
Order By절 사용시 서수를 하드 코딩하는 대신 이름 또는 열 별칭으로 정렬 열을 지정하십시오.
 
Information issues : Column
Impact
 
These data types are checked as deprecated. In some cases, using TEXT, IMAGE or NTEXT might harm performance.
이 데이터 유형은 사용되지 않는 것으로 확인됩니다. 경우에 따라 TEXT, IMAGE 또는 NTEXT를 사용하면 성능이 저하 될 수 있습니다.
 
Recommendation
 
Deprecated data types are marked to be discontinued on next versions of SQL Server, should use new data types such as: (varchar(max), nvarchar(max), varbinary(max) and etc.)
사용되지 않는 데이터 형식은 다음 버전의 SQL Server에서 더 이상 지원되지 않으므로 (varchar (max), nvarchar (max), varbinary (max) 등의 새 데이터 형식을 사용해야 함)
 
의견 :
 
 ntext , text  image 데이터 형식은 SQL2008이후 버전의 SQL Server에서 제거됩니다새로운 개발 작업에서는 이러한 데이터 유형을 사용하지 말고 현재 사용중인 응용 프로그램을 수정하십시오대신 nvarchar (max) , varchar (max)  varbinary (max)를 사용하십시오.
 
 
 
Posted by 시스템매니아
,

  • 악성쿼리를 수집하고자 하는 SQL서버의 SSMS2014를 실행후 연결합니다.

  • 메뉴 상단 도구èSQL Server Profiler 실행è 추적 속성 입력후 실행
  • 실서비스중인 서버에서 부하가 상당할수 있으므로 수집시 주의해야함

  • Profiler에서 쿼리 수집중

  • trc파일이 너무 커질수 있으므로 5분정도 수집후 중지 .trc파일 확인

  • 다운받은 파일 압축해제

  • ClearTrace실행후 상단 메뉴 Tools è Options선택하여 Trace분석에 사용할 DB생성

  • 분석할 .trc파일을 지정하고 Import Files 선택

  • 분석이 완료되고 CPU순으로 적용된 분석결과 화면

  • CPU사용률 및 Duration이 높은 ITEM을 떠블클릭하여 Sample 쿼리 확인

 
 
Posted by 시스템매니아
,

 
4.    데이터베이스 복제 소개

하나의 서버에 집중된 메인 프레임/터미널 구조에서 사용자는 터미널을 통하여 간단한 명령을 내리고 결과를 얻었다. 컴퓨터의 가격이 내려가고 서버와 대등한 능력을 가진 데스크탑PC 대중화 되면서 클라이언트 PC 많은 처리 기능을 두고, 서버에서는 데이터를 처리하는 클라이언트/서버 환경이 발달 되면서 데이터 베이스 역시 분산 환경으로 전환되었다. 오늘날 기업용 애플리케이션들이 분산 환경에서 작동되도록 변환되고 있는 것도 하나의 추세라고 할수 있다.

오늘날 데이터베이스 관리자들이 처리해야 문제 중의 하나가 이기종 시스템 간에 거대한 양의 데이터를 적절한 시간 안에 분산시키는 방법인데, SQL 서버군 이기종 간의 데이터베이스 분산방법으로 SQL 서버에서는 복제 방법을 제공하고 있다. 복제는 같은 정보를 여러 복사하여 다수의 데이터베이스에 분산되도록 데이터베이스의 일관성을 유지하도록 하기위해 동기화 하는 과정이다.

4.1  데이터베이스복제구성
SQL 서버의 복제 모델은 기본적으로 출판 모델에 기인하고 있다. 출판 모델이란 신문이나 잡지, 도서의 유통
모델이라는 의미이다. 책을 만들어서 판매 유통하는 경우를 살펴보자. 이라는 데이터베이스를 가지고 책을
성하는 사람은 출판업자이다. 출판업자는 Publisher이다. 책을 구매하여 읽는 소비자는 바로 구독자이다. 구독
자가 Subscriber이다. 출판사로부터 소비자가 책을 직접 구매하는 경우는 많지 않고, 보통은 서점을 이용한다.
점은 출판사로부터 책을 요청해서 소비자에게 전달하는 일종의 배포자이다. 배포자가 Distributor이다. 이렇게 3
구성 요소들간에 행위가 발생된다. 아티클(Article) 유통되는 책을 의미한다. 게시(Publication), 출판사가
책은 완성됐으므로 판매 준비가 되었다고 고시를 하는 것을 의미하고, 실제 데이터베이스에서는 어떤 데이터
베이스가 가져가도 좋다고 게시되는 것을 말한다. 구독(Subscription) 소비자가 유통상인 서점에 가서 책을
넣는 행위를 일컫는다.

     배포자

배포자는 하나 이상의 게시자와 연결된 복제별 데이터에 대한 저장소 역할을 하는 데이터베이스 인스턴스입니다. 게시자는 배포자에서 단일 데이터베이스(배포 데이터베이스) 연결되어 있습니다. 배포 데이터베이스는 복제 상태 데이터와 게시에 대한 메타데이터를 저장하고 경우에 따라서는 게시자에서 구독자로 이동하는 데이터에 대한 역할을 합니다. 대부분의 경우 단일 데이터베이스 서버 인스턴스는 게시자와 배포자 역할을 모두 합니다. 이를 로컬 배포자라고 합니다. 게시자 배포자가 서로 다른 데이터베이스 서버 인스턴스에 구성되어 있는 경우에는 배포자를 원격 배포자라고 합니다.  
게시자

게시자는 복제를 통해 데이터를 다른 위치에서 사용할 있도록 만드는 데이터베이스 인스턴스입니다. 게시자는 각각 논리적으로 관련된 개체 집합 복제할 데이터를 정의하는 게시를 하나 이상 가질 있습니다.

 

구독자

구독자는 복제된 데이터를 수신하는 데이터베이스 인스턴스입니다. 구독자는 여러 게시자 게시로부터 데이터를 수신할 있습니다. 선택한 복제 유형에 따라 구독자는 데이터 변경 내용을 게시자에 다시 전달하거나 데이터를 다른 구독자로 다시 게시할 수도 있습니다.

 

게시

게시는 하나의 데이터베이스에서 하나 이상의 아티클을 모은 것입니다. 여러 아티클을 게시로 그룹화하면 논리적으로 관련된 데이터베이스 개체 집합 단위로 복제된 데이터를 쉽게 지정할 있습니다.

 

아티클

 

아티클은
게시에 포함된 데이터베이스 개체를 식별합니다. 게시는 테이블, , 저장 프로시저 기타 개체를 포함한 여러 유형의 아티클을 포함할 있습니다. 테이블이 아티클로 게시되면 필터를 사용하여 구독자로 보낼 데이터의 행을 제한할 있습니다.

 

구독

구독은 구독자에게 게시 복사본을 배달해 것을 요청하는 것입니다. 구독은 어떤 게시를 언제 어디서 받을 것인지를 정의합니다. 밀어넣기와 끌어오기의 가지 구독 유형이 있습니다.

 

 

 

 

 
5.    데이터베이스 복제 유형 소개

5.1  스냅샷 복제

스냅숏 복제는 많은 양의 데이터가 변경되지만 자주 변경되지는 않을 때 가장 적합합니다. 예를 들어 한 판매 조직이 제품 가격 목록을 유지 관리하면서 일년에 한 번이나 두 번 가격을 동시에 업데이트한다면 데이터 전체 스냅숏이 변경된 후 복제하는 것이 좋습니다. 특정 유형의 데이터에 대해서는 스냅숏을 더 자주 복제하는 것이 적합할 수도 있습니다. 예를 들어 게시자에서 비교적 작은 테이블이 낮에 업데이트되었지만 어느 정도의 대기 시간이 허용되는 경우에는 변경 내용을 밤마다 스냅숏으로 배달할 수 있습니다.

증분 변경 내용은 추적되지 않으므로 스냅숏 복제에는 게시자에 트랜잭션 복제보다 낮은 연속 오버헤드가 있습니다. 그러나 복제 중인 데이터 집합이 아주 큰 경우에는 스냅숏을 생성하고 적용하는 데 상당히 많은 리소스가 필요합니다. 그러므로 스냅숏 복제 사용 여부를 평가할 때 전체 데이터 집합의 크기와 데이터 변경 빈도를 고려하십시오.  

 

스냅샷 복제는 다음과 같은 경우에 유용합니다.

-      데이터가 자주 변경되지 않습니다.

-      게시자 측에서 최신이 아닌 데이터 복사본을 일정 기간 동안 보유할 수 있습니다.

-      소량의 데이터를 복제합니다.

-      짧은 기간 동안 많은 양의 데이터가 변경됩니다.

 

5.2  트랜잭션 복제

트랜잭션 복제는 일반적으로 게시 데이터베이스 개체 및 데이터의 스냅숏으로 시작됩니다. 일반적으로 초기 스냅숏이 사용되자마자 게시자에서의 후속 데이터 변경 내용 및 스키마 수정 내용이 구독자로 배달됩니다. 이러한 작업은 거의 실시간으로 수행됩니다. 데이터 변경 내용은 게시자에서 발생한 것과 같은 순서 및 같은 트랜잭션 경계 내에서 구독자에 적용되므로 게시 내에서는 트랜잭션 일관성이 보장됩니다.

기본적으로 변경 내용은 게시자로 다시 전파되지 않기 때문에 트랜잭션 게시에 대한 구독자는 읽기 전용으로 취급됩니다. 그러나 트랜잭션 복제는 구독자의 업데이트를 허용하는 다양한 옵션을 제공합니다.  

 

트랜잭션 복제는 일반적으로 다음 경우에 사용됩니다.

-      증분 변경 내용을 발생과 동시에 구독자로 전파하려고 합니다.

-      응용 프로그램이 게시자에서 변경이 수행된 시점과 해당 변경 내용이 구독자에 도달한 시점 간의 짧은 대기 시간이 필요합니다.

-      응용 프로그램이 중간 데이터 상태에 액세스해야 합니다. 예를 들어 한 행이 5번 변경될 경우 트랜잭션 복제를 사용하면 응용 프로그램은 행의 실질적인 데이터 변경만이 아닌 모든 변경(: 트리거 실행)에 응답할 수 있습니다.

-      게시자가 많은 양의 삽입, 업데이트 및 삭제 작업을 수행합니다.

-      게시자 또는 구독자가 Oracle과 같은 비-SQL Server(Non-SQL Server) 데이터베이스입니다.

5.3  병합 복제

병합 복제는 트랜잭션 복제와 마찬가지로 일반적으로 게시 데이터베이스 개체 및 데이터의 스냅숏으로 시작합니다. 게시자 및 구독자에서 발생한 후속 데이터 변경 및 스키마 수정은 트리거로 추적합니다. 구독자는 네트워크에 연결될 때 게시자와 동기화하여 마지막 동기화 이후 게시자와 구독자 간에 변경된 모든 행을 교환합니다.

병합 복제를 사용하면 여러 사이트에서 자율적으로 작업한 후 나중에 하나의 균일한 결과로 업데이트를 병합할 수 있습니다. 업데이트는 둘 이상의 노드에서 수행되므로 게시자 및 둘 이상의 구독자가 같은 데이터를 업데이트할 수 있습니다. 따라서 업데이트가 병합될 때 충돌이 발생할 수 있으며 병합 복제는 충돌을 처리하는 다양한 방법을 제공합니다.

 

병합 복제는 다음 경우에 유용합니다.

-      여러 구독자가 다양한 시간에 동일한 데이터를 업데이트하고 그 변경 내용을 게시자 및 다른 구독자에 전파할 수 있습니다.

-      구독자는 데이터를 받아 오프라인 상태에서 변경하여 나중에 게시자 및 다른 구독자와 변경 내용을 동기화해야 합니다.

-      각 구독자에 서로 다른 데이터 파티션이 필요합니다.

-      충돌이 발생할 수 있으며 충돌이 발생하면 충돌을 감지하여 해결할 수 있어야 합니다.

-      응용 프로그램이 중간 데이터 상태가 아닌 순수한 데이터 변경 내용만 필요로 합니다. 예를 들어 구독자가 게시자와 동기화하기 전에 구독자에서 행이 5번 변경된 경우 게시자에서는 행이 한 번만 변경되어 최종 데이터 변경 내용(5번째 값)을 반영합니다
 

 

 

 
6.    데이터베이스 복제 구성

6.1  복제 구성전 확인사항

1. 모든 서버의 MSSQLServer 서비스와 SQLServerAgent 서비스의 시작 계정이 동일한 Domain Admins 그룹에 있는 계정으로 구성하는 것이 권장방안.... 시작 계정을 변경했다면, 서비스를 재시작.

 

2. 본 문서의 복제구성 TestDomain 이 구성되지않은 환경에서 진행되며 모든구성원의 로컬Administrator계정 및 패스워드 와 sa계정의 패스워드는 동일하게 구성함

 

3. SSMS에 서버를 등록하고, 가능하면 복제 구성원은 연결된 서버(Linked Server)로 미리 등록.

 

4. SQLServerAgent 서비스는 자동 시작으로 설정.

 

5. Snapshot Agent가 스냅샷을 시도할 때 사용하는 스냅샷 폴더는 ReplData. 복제가 진행되는 동안 이 곳에 데이터와 스키마 스크립트가 저장됨. 이 폴더는 복제 도중에UNC 경로명(\\SQL서버명\드라이브$\~~\~~\ReplData)을 이용하여 다른 서버에서 액세스하기 때문에 관리 공유가 필히 되어 있어야 함. 폴더 지정시에 액세스 위반 메시지가 발생한다면, 이 부분을 점검할 것.)\\SQL2005\Repldata

 

6. 복제 상태를 점검할 땐, 복제 모니터를 이용할 것.

 

7. Transaction 복제는 Primary Key가 없는 Table은 복제할 수 없음.

 

8. 복제 구성원 생성 순서 : 배포자, 게시자, 구독자 순...

복제 삭제의 순서 : 구독자, 게시자, 배포자 순... (배포자를 삭제하면 바로 복제 삭제., 모든 구성원이 네트워크에 연결되어 있어야 함.)

 

 

 

 

 

 

6.2  복제 구성전 설정 사항

ReplData 폴더의 공유설정을 위해 배포서버의 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

repldata 경로로 이동하여repldata폴더의 속성을 선택합니다.(설치에 따라 드라이브경로가  다를수 있습니다.)


공유 탭으로 이동하여 이폴더를 공유를 체크합니다.


사용 권한을 클릭후 Everyone에 모든권한 허용을 체크합니다.


 

네트워크드라이브 경로로 repldata폴더의 접근이 가능한지 확인합니다.


 

6.3  복제 구성원 선정 및 구성

배포자 : SQL2005 

게시자 : SQL2005

구독자 : SQL2005-2

게시할 DB : Test

구독 방식: 밀어넣기구독

 

복제 구성 Test에 사용될 DBtest Table생성 스크립트

게시서버측에 아래 스크립트를 이용하여 미리 생성

/****** 데이터파일 경로는 SQL구성에 맞게 수정해야 할수있습니다.******/

CREATE DATABASE [test] ON  PRIMARY

( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

USE [test]

GO

/****** 개체:  Table [dbo].[test]    스크립트 날짜: 07/13/2011 10:06:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[test](

             [idx] [int] IDENTITY(1,1) NOT NULL,

             [name] [nvarchar](50) NULL,

 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED

(

             [idx] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

 

 

게시자 서버의 MSSMS상에 구독 대상서버를 등록 합니다.


 

MSSMS에서 게시서버를 마우스 오른쪽 버튼으로 클릭하여 연결을 선택하고. 서버이름을 입력후 연결을 클릭합니다.


 

 

 

 

 

 

 

 

 

MSSMS에서 게시서버와 구독서버가 같이 등록이 되어있는 것을 확인 있습니다.


 

데이터베이스 복제위해 배포서버 설정을 진행합니다. 배포자 서버인 SQL2005 복제 è 배포 구성을 선택합니다.


 

 

 

 

 

 

 

배포 구성 마법사 창이 뜨면 다음을 누릅니다.


 

배포자 선택 창에서 배포자를 선택 합니다. SQL2005서버가 배포자로 선택되었는지 확인 합니다. 다음을 누릅니다.


 

배포 게시에 필요한 스냅샷이 저장되는 폴더를 지정합니다. 네트워크드라이브로 연결을 해야하므로 \\SQL2005\repldata 지정하고 다음을 누릅니다.


 

배포 데이터베이스의 이름 배포 데이터베이스 파일 로그가 저장될 폴더를 지정합니다.


게시자를 지정하는 창이 뜹니다. 게시자는 데이터베이스 SQL2005입니다. 서버 선택이 맞는지 확인하고 다음을 누릅니다.


 

배포 구성에 체크후 다음을 클릭합니다.


마법사 완료 창에서 마지막으로 요약된 정보가 맞는지 확인을 하고 마침을 누릅니다.


 

배포 구성이 완료되었습니다.


 
7.    복제 유형 별 데이터베이스 게시

복제 유형은 세 가지, 스냅샷 복제, 트랜잭션 복제병합 복제가 있습니다.

7.1   스냅샷복제

게시서버 설정을 위해 SSMS SQL2005서버의 복제 è 로컬 게시 è게시를클릭합니다.


 

게시 마법사창에서 다음을 클릭합니다.


게시 데이터베이스 선택 창입니다. 게시할 데이터베이스를 선택하고 다음 누릅니다.


 

게시 유형 선택 창입니다. 복제 유형은 세 가지 스냅샷 복제, 트랜잭션 복제병합 복제가 있다고 언급했습니다. 스냅샷 복제를 하기 위해서 스냅샷 게시를 선택합니다. 다음 누릅니다.


게시할 데이터베이스의 아티클을 지정합니다. 아티클은 복제를 위해 지정한 데이터 테이블, 데이터 파티션 또는 데이터베이스 개체입니다. 여기서는 모든 아티클을 게시 하도록 선택하겠습니다. 다음을 클릭합니다.


 

필요 없는 행을 제외시킬수 있는 테이블 행 필터입니다. 기본값에서 다음을 클릭합니다


게시 이름과 설명 선택 창입니다. 게시 이름은 게시할 데이터베이스와 동일한 이름으로 합니다. 다음을 누릅니다.

스냅샷 에이전트 일정 설정 창입니다. 스냅샷 에이전트가 정해진 시간에 작업을 하도록 설정을 합니다.   변경 버튼을 누릅니다.스냅샷 에이전트의 작업일정이 제대로 설정이 되었는지 확인하고 다음을 클릭합니다. (매일 12시수행)


스냅숏 에이전트 보안설정창입니다. 보안 설정을 클릭합니다.


스냅숏 에이전트 프로세스 실행계정에 SQL Server 에이전트 서비스 계정으로 실행 체크

게시자에 연결에 sa계정과 패스워드를 입력합니다.


에이전트 보안이 설정되었습니다. 다음을 클릭합니다.


 

마법사 완료시 게시만들기를 체크하고 다음을 클릭합니다.


게시 이름을 입력하고 다음을 클릭합니다.


 

 

게시작업이 완료됩니다.


 

SSMS SQL2005서버에 test게시가 생성되었습니다.


 

 

 

 

 

스냅숏복제의 구독설정을 위해 SSMS SQL2005-2 서버의 복제 è 로컬 구독 è구독을클릭합니다.


 

구독마법사가 실행됩니다. 다음을 클릭합니다.


 

 

 

 

 

 

 

게시 창이 나타나며 게시자 부분을 SQL2005서버로 변경해줍니다.


 

게시자를 SQL2005 선택후 게시자가 나타납니다. 다음을 클릭합니다.


 

밀어넣기 구독을 선택후 다음을 클릭합니다.


 

구독 데이터베이스 설정부분. 현재 데이터 베이스가 생성되지않았으므로 데이터베이스를 선택합니다.


 

test라는 이름으로 데이터베이스를 생성합니다.(데이터 정렬이나 DB호환성수준이 SQL서버 버전과 다를때는 게시측 서버의 DB Full Backup하여 구독측 서버에 복원하는 것이 좋습니다.)


 

구독 데이터 베이스가 선택되었습니다.


배포 보안에이전트 설정을 위해


 

빨간 테두리의 상자를 클릭합니다.


 

배포 에이전트 프로세스 실행계정에 SQL Server 에이전트 서비스 계정으로 실행 체크

구독자에 연결에 sa계정과 패스워드를 입력합니다.


배포 에이전트 보안설정이 완료되었습니다. 다음을 클릭합니다.


에이전트 일정을 계속실행에 설정후 다음을 클릭합니다.


 

구독 초기화를 번째 동기화 시로 선택후 다음을 클릭합니다.


 

구독 만들기 체크후 다음 클릭


 

설정내용을 확인후 다음을 클릭합니다.


 

스냅숏 복제의 구독만들기가 완료되었습니다.


 

복제구성후 실제 복제는 예정된 시간은 12시에 진행됩니다. Test 위해 SSMS SQL2005서버 복제 è 로컬 게시 ètest게시를 마우스 클릭후 스냅숏에이전트 상태보기를 클릭합니다.


스냅숏 에이전트의 현재 상태가 나타납니다. 시작버튼을 클릭합니다.

스냅숏 에이전트를 실행하면 게시DB 스냅숏이 생성됩니다. 생성시 DB용량이 클경우 서버에 상당한 로드가 발생하므로 서비스중인 서버에서는 사용량이 적은 시간에 실행하는 것이 안전합니다.)


에이전트가 실행됩니다.


스냅숏이 생성되었습니다.


 

\\SQL2005\repldata 폴더에 아래와 같이 스냅숏이 생성됩니다.


구독설정시 스냅숏이 생성되면 바로 동기화 되게 설정하였으므로 구독서버측에 아티클이 복제되었는지 확인합니다.


 

게시서버측에 DB 데이터를 업데이트 하여 복제동작을 Test합니다. SSMS SQL2005선택후 메뉴의 쿼리클릭하여

아래 스크립트 실행

use test

insert into dbo.test values('test1')

insert into dbo.test values('test2')

insert into dbo.test values('test3')

스냅샷복제의 경우 복제가 바로 이루어지지않고 게시설정시 하루 1 지정한 시간인 24시에 한번실행됩니다.

Test 위해 SSMS SQL2005서버 복제 è 로컬 게시 ètest게시를 마우스 클릭후 스냅숏에이전트 상태보기를 클릭하여 스냅숏 에이전트를 시작합니다.에이전트가 실행됩니다.


스냅숏이 생성되었습니다.


구독설정시 스냅숏이 생성되면 바로 동기화 되게 설정하였으므로 구독서버측에 아티클이 복제되었는지 확인합니다.


7.2   트랜잭션복제

게시서버 설정을 위해 SSMS SQL2005서버의 복제 è 로컬 게시 è게시를클릭합니다.


 

게시 마법사창에서 다음을 클릭합니다.


 

게시 데이터베이스 선택 창입니다. 게시할 데이터베이스를 선택하고 다음 누릅니다.


 

게시 유형 선택 창입니다. 복제 유형은 세 가지 스냅샷 복제, 트랜잭션 복제병합 복제가 있다고 언급했습니다.

스냅샷 복제를 하기 위해서 스냅샷 게시를 선택합니다. 다음 누릅니다.


게시할 데이터베이스의 아티클을 지정합니다. 아티클은 복제를 위해 지정한 데이터 테이블, 데이터 파티션 또는 데이터베이스 개체입니다. 여기서는 모든 아티클을 게시 하도록 선택하겠습니다. 다음을 누릅니다.


\

필요 없는 행을 제외시킬수 있는 테이블 행 필터입니다. 기본값에서 다음을 클릭합니다.


게시 이름과 설명 선택 창입니다. 게시 이름은 게시할 데이터베이스와 동일한 이름으로 합니다. 다음을 누릅니다.

스냅샷 에이전트 일정 설정 창입니다. 스냅샷 에이전트가 정해진 시간에 작업을 하도록 설정을 합니다.   트랜잭션복제의경우특정시간이아닌계속복제가이우어져야하므로즉시스냅숏을만들고구독초기화에사용할있도록유지합니다를체크합니다


 

스냅숏 에이전트 보안설정창입니다. 보안 설정을 클릭합니다.


스냅숏 에이전트 프로세스 실행계정에 SQL Server 에이전트 서비스 계정으로 실행 체크

게시자에 연결에 sa계정과 패스워드를 입력합니다.


 

에이전트 보안이 설정되었습니다. 다음을 클릭합니다.


마법사 완료시 게시만들기를 체크하고 다음을 클릭합니다.


 

게시 이름을 입력하고 다음을 클릭합니다.


 

게시작업이 완료됩니다. SSMS SQL2005서버에 test게시가 생성되었습니다.


트랜잭션복제의 구독설정을 위해 SSMS SQL2005-2 서버의 복제 è 로컬 구독 è구독을클릭합니다.


구독마법사가 실행됩니다. 다음을 클릭합니다.


게시 창이 나타나며 게시자 부분을 SQL2005서버로 변경해줍니다.


 

게시자를 SQL2005 선택후 게시자가 나타납니다. 다음을 클릭합니다.


 

밀어넣기 구독을 선택후 다음을 클릭합니다.


 

구독 데이터베이스 설정부분. 현재 데이터 베이스가 생성되지않았으므로 데이터베이스를 선택합니다.


 

test라는 이름으로 데이터베이스를 생성합니다.(데이터 정렬이나 DB호환성수준이 SQL서버 버전과 다를때는 게시측 서버의 DB Full Backup하여 구독측 서버에 복원하는 것이 좋습니다.)


 

구독 데이터 베이스가 선택되었습니다.


배포 보안에이전트 설정을 위해


빨간 테두리의 상자를 클릭합니다.

 


 

배포 에이전트 프로세스 실행계정에 SQL Server 에이전트 서비스 계정으로 실행 체크

구독자에 연결에 sa계정과 패스워드를 입력합니다.


배포 에이전트 보안설정이 완료되었습니다. 다음을 클릭합니다.


에이전트 일정을 계속실행에 설정후 다음을 클릭합니다.


 

구독 초기화를 즉시로 선택후 다음을 클릭합니다.


 

구독 만들기 체크후 다음 클릭


 

설정내용을 확인후 다음을 클릭합니다.


 

스냅숏 복제의 구독만들기가 완료되었습니다.


 

복제구성후 트랜잭션 복제의 경우 바로 구독서버로 복제가 시작됩니다. SQL2005-2 구독 서버측의 test DB Table 데이터가 복제되었는지 확인합니다.


 

 

 

7.3   병합복제

게시서버 설정을 위해 SSMS SQL2005서버의 복제 è 로컬 게시 è게시를클릭합니다.


 

게시 마법사창에서 다음을 클릭합니다.


 

게시 데이터베이스 선택 창입니다. 게시할 데이터베이스를 선택하고 다음 누릅니다.


게시 유형 선택 창입니다. 복제 유형은 세 가지 스냅샷 복제, 트랜잭션 복제, 업데이트할 수 있는 구독이 있는 트랜잭션 복제, 병합 복제가 있다고 언급했습니다.

병합 복제를 하기 위해서 스냅샷 게시를 선택합니다. 다음 누릅니다.


구독자 유형을 선택한뒤 다음을 클릭합니다.


 

게시할 데이터베이스의 아티클을 지정합니다. 아티클은 복제를 위해 지정한 데이터 테이블, 데이터 파티션 또는 데이터베이스 개체입니다. 여기서는 모든 아티클을 게시 하도록 선택하겠습니다. 다음을 누릅니다.


필요 없는 행을 제외시킬수 있는 테이블 행 필터입니다. 기본값에서 다음을 클릭합니다.


 

스냅샷에이전트 일정 설정 창입니다. ‘즉시 스냅숏을 만들고 구독 초기화에 사용할 있도록 유지합니다.’

체크를합니다


스냅숏 에이전트 보안설정창입니다. 보안 설정을 클릭합니다.


 

스냅숏 에이전트 프로세스 실행계정에 SQL Server 에이전트 서비스 계정으로 실행 체크

게시자에 연결에 sa계정과 패스워드를 입력합니다.


에이전트 보안이 설정되었습니다. 다음을 클릭합니다.


 

마법사 완료시 게시만들기를 체크하고 다음을 클릭합니다.


 

게시 이름을 입력하고 다음을 클릭합니다.


 

게시작업이 완료됩니다.


 

SSMS SQL2005서버에 test게시가 생성되었습니다.


병합복제 구독설정을 위해 SSMS SQL2005-2 서버의 복제 è 로컬 구독 è구독을클릭합니다.


구독마법사가 실행됩니다. 다음을 클릭합니다.


게시 창이 나타나며 게시자 부분을 SQL2005서버로 변경해줍니다.


 

게시자를 SQL2005 선택후 게시자가 나타납니다. 다음을 클릭합니다.


 

밀어넣기 구독을 선택후 다음을 클릭합니다.


 

구독 데이터베이스 설정부분. 현재 데이터 베이스가 생성되지않았으므로 데이터베이스를 선택합니다.


 

test라는 이름으로 데이터베이스를 생성합니다.(데이터 정렬이나 DB호환성수준이 SQL서버 버전과 다를때는 게시측 서버의 DB Full Backup하여 구독측 서버에 복원하는 것이 좋습니다.)


 

구독 데이터 베이스가 선택되었습니다.


배포 보안에이전트 설정을 위해


 

빨간 테두리의 상자를 클릭합니다.


 

배포 에이전트 프로세스 실행계정에 SQL Server 에이전트 서비스 계정으로 실행 체크

구독자에 연결에 sa계정과 패스워드를 입력합니다.


 

배포 에이전트 보안설정이 완료되었습니다. 다음을 클릭합니다.


에이전트 일정을 계속실행에 설정후 다음을 클릭합니다.


 

구독 초기화는 즉시를 선택합니다.


 

구독 유형을 선택합니다.


 

구독 만들기 체크후 다음 클릭


 

설정내용을 확인후 다음을 클릭합니다.


 

스냅숏 복제의 구독만들기가 완료되었습니다.


 

Test 위해 SSMS SQL2005서버 복제 è 로컬 게시 ètest게시를 마우스 클릭후 스냅숏에이전트 상태보기를 클릭합니다.


 

스냅숏 에이전트의 현재 상태가 나타납니다. 스냅숏 생성이 완료되었습니다.


 

 

 

 

\\SQL2005\repldata 폴더에 아래와 같이 스냅숏이 생성됩니다.


 

구독설정시 스냅숏이 생성되면 바로 동기화 되게 설정하였으므로 구독서버측에 아티클이 복제되었는지 확인합니다.


 

 

게시서버측에 DB 데이터를 업데이트 하여 복제동작을 Test합니다. SSMS SQL2005선택후 메뉴의 쿼리를 클릭하여 아래 스크립트 실행

use test

insert into dbo.test (name) values('test1')

insert into dbo.test (name) values('test2')

insert into dbo.test (name) values('test3')

 

병합복제의 경우 복제가 실시간으로 이루어지지않으며 1 미만의 시간이 복제에 소요됨

구독서버측에 아티클이 복제되었는지 확인합니다.


 

구독서버측에 DB 데이터를 업데이트 하여 복제동작을 Test합니다. SSMS SQL2005-2 선택후 메뉴의 쿼리를 클릭하여 아래 스크립트 실행

use test

insert into dbo.test (name) values('test4')

insert into dbo.test (name) values('test5')

insert into dbo.test (name) values('test6')

 

 

 

 

 

병합복제의 경우 복제가 실시간으로 이루어지지않으며 1 미만의 시간이 복제에 소요됨

구독서버측에 아티클이 복제되었는지 확인합니다.


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
8.    트랜잭션 복제 구성후 복제 Table추가

8.1   Table 추가 방안

Test를 위해 트랜잭션 복제가 구성된 서버중 게시서버에서 test라는이름의 새로운 Table을 추가합니다.


 

복제è로컬게시è해당 DB의 게시로 이동하여 마우스 우클릭후 속성을 선택합니다.


 

 

 

속성창 왼편 메뉴중 아티클을 선택후 선택 표시된 개체만 목록에 표시를 언체크합니다.


 

새로 복제에 추가할 test Table을 체크후 확인버튼을 클릭합니다.


 

복제è로컬게시è해당 DB의 게시로 이동하여 마우스 우클릭후 모든 구독 다시 초기화를 선택합니다.


 

새 스냅숏 사용 및 지금 새 스냅숏 생성을 체크후 다시 초기화 표시를 클릭합니다.

(배포서버 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\unc 폴더에 스냅샷이 생성되며 디스크용량이 커질수있으므로 주의하시기바랍니다.)

(스냅숏 초기화시 시스템 부하가 높습니다. 사용량이 적을때 적용하시기바랍니다.)


 

 

 

 

 

 

 

 

복제è로컬게시è해당 DB의 게시로 이동하여 마우스 우클릭후 스냅숏 에이전트 상태 보기를 선택합니다.


 

[100%] ***스내숏이 생성되었습니다라는 메시지가  완료된 상태입니다


 

 

 

 

 

 

 

 

복제è로컬게시è해당 DB의 게시로 이동하여 마우스 우클릭후 복제 모니터 시작을 선택합니다.


 

복제 모니터에서 게시와 구독이 에러없이 동작중인지 확인합니다.


 

 

실행중인 구독 마우스 우 클릭후 자세히보기를 선택하시면 상세정보를 확인하실수 있으며 에러발생시 에러메시지를 확인할수 있습니다.


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
9.    복제 모니터링

9.1   복제모니터링이필요한이유

 

- 복제 시스템이 정상적으로 동작하고 있는가?

- 에이전트가 실행되지않는 이유는 무엇인가?

- 시스템이 이렇게 느리지?

- 복제하는데 시간이 얼마나 걸리는가?

- 어디에 잠재적인 문제가 발생하고 있는가?

- 문제 상황을 사전에 인식하고 조치를 취할 수는 없는가?

 

 

9.2   복제 모니터의 특징

복제 모니터의 특징

내용

성능 임계값 설정

잠재적인 성능 문제를 사전에 식별하기 위해 기준이 되는 조건과 임계 값을 정의하면 해당 상황에서 경고를 발생시키고 모니터링을 할 수 있습니다.

 

모니터링 부하 최소화

복제 모니터는 많은 컴퓨터를 효율적으로 모니터링 하도록 설계 되었습니다. 복제 모니터가 사용하는 쿼리는 정기적으로 캐시되며 새로 고쳐집니다. 캐싱을 사용하므로 여러 페이지를 볼 때 불 필요한 쿼리 및 계산 작업을 줄일 수 있습니다. 기본적으로 주 복제 모니터 창은 자동으로 5초마다

 

병합 복제 모니터링

병합 복제의 각 처리 단계(변경 내용 업로드, 다운로드 등)에 소요된 시간을 포함해서 동기화 중에 처리된 각 아티클에 대한 자세한 통계를

 

트랜잭션 복제 모니터링

트랜잭션 복제는 이제 트랜잭션의 전 단계의 걸쳐 실 시간의 추적이 가능합니다. 이는 게시자에서 배포자로 그리고 하나 이상의 구독자에 대해

 

 

 

 

 

 

 

 

 

9.3    복제 모니터링 사용 방법

개체 탐색기 è 해당 서버 è 복제 에서 팝업 메뉴를 띄우고, 복제 모니터 시작 메뉴를 선택합니다.
복제 모니터에서 해당 게시 정보를 선택하면 관련된 정보를 모니터링 할 수 있습니다. 우선 모든 구독 탭에서 모든 구독자의 실행 정보와 관련된 작동 상태 및 성능 문제 여부 등을


모든 구독탭의 동기화중인 구독을 더블클릭하면 상세한 동기화 상태를 모니터링 할수 있습니다.


추적 프로그램 토큰 탭에서는 추적 프로그램 삽입 버튼을 이용해서 트랜잭션 복제 로그에 추적 프로그램 토큰을 추가하도록 지정하고 해당 토큰의 복제 경로를 따라 복제 수행에 걸리는 각 영역별(게시->배포, 배포->구독) 수행 시간 및 지연 시간 정보 등을 모니터링할 수 있습니다.


 

경고 에이전트 탭에서는 성능 문제 임계 값을 설정하거나 게시 관련된 에이전트와 작업에 대한 상세 정보를 모니터링 있습니다.

Posted by 시스템매니아
,

MS SQL2008R2 Cluster IP 변경 절차
SQL Server 2008R2 Cluster 환경에서 각 노드의 Public Network IP 및 클러스터 리소스인 MSDTC, Quorum, SQL 리소스 IP를 변경하는 절차에대하여 정리합니다.  Windows Server 2008 Hyper-V 에 SQL Server 2008R2 Cluster 를 구성하였으며 이
테스트 환경에서 변경 절차를 진행합니다.

[시나리오]
기존 네트워크 구성 정보
AD
192.168.0.1
SNODE1
192.168.0.2
SNODE2
192.168.0.3
Cluster
192.168.0.4
MSDTC
192.168.0.5
SQL
192.168.0.6
Heartbeat
10.10.0.2 / 10.10.0.3
변경 네트워크 구성 정보
AD
192.168.1.1
SNODE1
192.168.1.2
SNODE2
192.168.1.3
Cluster
192.168.1.4
MSDTC
192.168.1.5
SQL
192.168.1.6
Heartbeat
10.10.0.2 / 10.10.0.3
진행 내용
제목
SQL2008R2 MSCS IP 변경 절차
총 소요시간(분)

작업 지원 인원

설치 정보
구분
AD
NODE1
NODE22
서버 환경
Windows server 2008R2 /  MS SQL Server 2008R2
Host Name
AD.test.com
Snode1.test.com
Snode2.test.com
Real IP
192.168.0.1
192.168.0.2
192.168.0.3
도메인 가입 여부
Domain controller in the Node
설치 도메인 계정
Test\administrator
Cluster name / IP

cluster / 192.168.0.4
MSDTC name /IP

MSDTC / 192.168.0.5
가상 SQL 서버

Sqlsluter
인스턴스 명

MSSQL$sqlsluter
가상 SQL 서버 IP

192.168.0.6
사전 점검
순번
항목
비 고                   
소요시간(분)
1
NIC 점검
Public/Private NIC 설정 확인

2
스토리지 점검
디스크 관리자에서 공유 디스크에 대한 확인

3
이벤트 로그 점검
특이 사항 유무 확인

4
보안 업데이트 점검
최신 보안 업데이트까지 적용 여부 확인

5
드라이버/펌웨어 확인
NIC 등 최신 드라이버/펌웨어 적용 여부 확인

총 소요 시간

작업절차(순번)
설 명
1
Snode2 서버 Cluster Services 중지
2
Snode2 서버 Iscsi Target 제거
3
Snode2 Public IP변경
4
Snode2 서버 종료
5
Snode1 서버 Cluster Services 중지
6
Snode1 서버 Iscsi Target 제거
7
Snode1 Public IP변경
8
Snode1 서버 종료
9
AD서버 IP변경
10
AD서버 StarWind IP변경
11
AD서버 종료                                   
12
서버 이전후(가정) AD서버 ON
13
Snode1 서버 ON
14
Snode1 서버 iSCSI 에서 디스크 연결
15
Snode1 서버 Cluster Service 시작
16
클러스터 관리자에서 Cluster 리소스의 IP변경
17
Cluster 리소스 온라인으로 전환                      
18
클러스터 관리자에서 MSDTC 리소스의 IP변경
19
MSDTC 리소스 온라인으로 전환
20
클러스터 관리자에서 SQL Server 리소스의 IP변경
21
SQL Server 리소스 온라인으로 전환
22
Snode2 서버 ON
23
Snode2 서버 iSCSI 에서 디스크 연결
24
Snode2 서버 Cluster Service 시작
25
클러스터 관리자에서 node이동하여 fail over 시 SQL 및 정상작동 Test진행
[작업절차]
1. Snode1, Snode2 각 노드가 온라인 상태에서 아래 작업을 진행합니다.
  1. Snode2 노드 Cluster Services 중지(사용안함으로 설정)

2.Iscsi Target 제거(MSDTC, Quorum, SQL 디스크 모두 Logoff) 스토리지시 건너뜀

3.Snode2 Public IP변경(AD의 DNS주소 포함)

4.Snode2 서버종료
5.Snode1 노드 Cluster Services 중지(사용안함으로 설정)

6.Iscsi Target 제거(MSDTC, Quorum, SQL 디스크 모두 Logoff) 스토리지시 건너뜀

7.Snode1 Public IP변경

8.Snode1 서버종료
9.AD서버 IP변경

10.StarWind의 IP변경

11.AD서버 종료
12.서버 이전후 AD서버 ON
13.Snode1 서버 ON
14.iSCSI 에서 디스크 연결

15.Cluster Service 시작
16.장애조치 클러스터 관리자 시작
17.해당 클러스터 è 네트워크 è 클러스터 네트워크 3번 마우스 우클릭후 속성 선택하여 클라이언트가 이 네트웍을 통해연결할 수 있음 선택

18.해당 클러스터 선택후 중앙 하위 클러스터 코어 리소스창에 클러스터 IP주소 마우스 우클릭

19.Cluster 리소스의 IP변경(변경확인 적용안될시 다시 변경)

20.IP변경 확인후 해당 IP리소스 온라인으로 전환

21.Cluster 리소스 온라인으로 전환

22.서비스 및 응용프로그램으로 이동하여 MSDTC IP변경작업 진행(작업 동일)

23.IP 변경 작업 및 리소스 온라인 전환 작업 진행

24.SQL Server 동일 IP변경 및 리소스 온라인 전환 작업 진행

25.Snode2 서버 ON
26.iSCSI 에서 디스크 연결

27.Cluster Service 시작
28.node이동 fail over 시 SQL 및 정상작동 Test진행

Posted by 시스템매니아
,

1.     기존 DB이름(논리적이름포함)과 동일하게 새로운 DB를 생성한다.

2.     SQL 서비스를 중지 한다.

3.     새로 생성된 MDF, LDF 파일을 삭제 후 기존 파일과 교체 한다.

4.     SQL 서비스를 시작 후 EM을 실행한다.

5.  DB가 주의대상 상대로 변경되었다.

6.     다시 SQL 서비스를 중지 후 ldf 파일을 삭제 한다.

7.     SQL 서비스를 시작한다.

8.  DB에 접근 할 수 없는 상태가 된다

9.     DB를 응급모드로 변경한다.
alter database 디비명 set emergency

 10.     로그파일을 다시 생성한다.
 alter database 디비명 rebuild log on
 (name=로그이름,filename='ldf경로\로그파일명.ldf')

11.     DB를 온라인 상태로 변경
 alter database 디비명 set online

12.   DB선택-> 마우스오른쪽 -> 속성-> 옵션 -> 엑세스 제한을 multiple로 변경

13.   DBCC 로 DB의 오류를 검사한다.
dbcc checkdb(디비명)

Posted by 시스템매니아
,
Posted by 시스템매니아
,

‘default trace enabled’ 옵션은 SQL Server 2005에서 새롭게 지원되는 구성 옵션으로서 관리자가 알아 두면 유용합니다.

‘default trace enabled’ 옵션을 1로 설정하면 기본 추적이 실행됩니다. 이 옵션의 기본 설정은 1이며 추적 기능 활성화를 의미하며, 값을 0으로 변경하면 추적 기능이 해제됩니다. 기본 추적 기능은 주로 구성 옵션과 관련된 변경 내용과 작업에 대한 다양하고 영구적인 로그를 제공하기 때문에, 문제 발생 시 문제를 진단하는데 유용합니다.

기본 추적 로그는 기본적으로 롤오버 추적 파일을 사용하여 \MSSQL\LOG 디렉터리에 저장되며, 기본 추적 로그 파일의 기본 파일 이름은 log.trc입니다. 5개의 20 MB 파일이 생성되며 최대 100MB 내에서 계속 순환됩니다. 기본 추적 로그는 SQL Server 프로파일러에서 열어서 검사하거나 fn_trace_gettable 시스템 함수를 사용하여 Transact-SQL로 쿼리할 수 있습니다.

 

기본 추적 구성 옵션 확인 설정하기:

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

-- 기본값이 1입니다.

EXEC sp_configure 'default trace enabled'

GO

EXEC sp_configure 'show advanced options', 0

GO

RECONFIGURE

GO

 

기본 추적 로그 파일 확인 테이블로 복사하기:

SELECT *

FROM fn_trace_gettable

('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_20.trc', DEFAULT);

GO

 

SELECT t.StartTime, t.EventClass, e.name,

                           t.DatabaseName, t.ObjectName, t.ObjectID, t.IndexID

FROM fn_trace_gettable

('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_21.trc',0) t

              JOIN sys.trace_events e

ON e.trace_event_id = t.eventclass

ORDER BY t.StartTime;

GO

 

Posted by 시스템매니아
,

Read80Trace 사용법

MS-SQL 2009. 11. 27. 11:29

William_read80Trace.tdf 를 sql server\80\toolls\template\SqlProfiler 에 복사한다

 

추적설정

===============

프로파일러 시작

추적이름 A

템플릿 William_read80Trace로 선택

C:\trace 폴더에 저장

파일 롤오버 사용 선택제거

실행

 

workload수집

=================

쿼리를 아무거나 실행한다..

 

rml 설치 

========================

RML.exe실행

기본 설치 폴더에 설치.

setup.exe실행

기본 설치 폴더에 설치

파일중 Read80Trace.exe와 ostress.exe 파일을 C:\trace 폴더에 저장

 

Read80Trace 실행

=================

C:\trace 에서 컴맨드 창에서 Read80Trace /I.A.trc 실행

 

index.htm 나온다

[출처] Read80Trace 사용법|작성자 macro3


Posted by 시스템매니아
,


host파일 수정
192.168.0.1 sqlm1.test.com
192.168.0.2 sqlm2.test.com
192.168.0.3 monitor.test.com

sqlm1에서 DB 풀백업
backup database test to disk='c:\test.bak'
backup log test to disk='c:\test_log.bak'

sqlm2에서 DB 복원
RESTORE DATABASE test
FROM DISK='c:\test.bak' WITH NORECOVERY
RESTORE LOG test
FROM DISK='c:\test_log.bak'  WITH FILE=1, NORECOVERY


각 서버별 인증서 파일생성

sqlm1-----------
create master key encryption by password ='*****'

create certificate database_a_cert with subject='catabase a certificate', start_date='2009/1/1', expiry_date='2100/12/31'

create endpoint endpoint_mirroring state=started
as tcp(listener_port=5022, listener_ip=all)
for database_mirroring(authentication=certificate database_a_cert, encryption=required, role=all)

backup certificate database_a_cert to file='c:\database_a_cert.cer'

sqlm2--------------
create master key encryption by password ='*****'

create certificate database_b_cert with subject='catabase b certificate', start_date='2009/1/1', expiry_date='2100/12/31'

create endpoint endpoint_mirroring state=started
as tcp(listener_port=5022, listener_ip=all)
for database_mirroring(authentication=certificate database_b_cert, encryption=required, role=all)

backup certificate database_a_cert to file='c:\database_b_cert.cer'

monitor--------------
create master key encryption by password ='*****'

create certificate database_c_cert with subject='catabase c certificate', start_date='2009/1/1', expiry_date='2100/12/31'

create endpoint endpoint_mirroring state=started
as tcp(listener_port=5022, listener_ip=all)
for database_mirroring(authentication=certificate database_c_cert, encryption=required, role=all)

backup certificate database_a_cert to file='c:\database_c_cert.cer'

생성한 인증서를 각 서버의 C:\에 모두 복사

로그인 계정 생성 및 Endpoint설정
sqlm1-----------
create login administrator with password='*******'

create user administrator from login administrator

create certificate database_b_cert
authorization administrator
from file='c:\database_b_cert.cer'

create certificate database_c_cert
authorization administrator
from file='c:\database_c_cert.cer'

grant connect on endpoint::endpoint_mirroring to administrator

sqlm2-----------
create login administrator with password='*******'

create user administrator from login administrator

create certificate database_a_cert
authorization administrator
from file='c:\database_a_cert.cer'

create certificate database_c_cert
authorization administrator
from file='c:\database_c_cert.cer'

grant connect on endpoint::endpoint_mirroring to administrator

monitor-----------
create login administrator with password='*******'

create user administrator from login administrator

create certificate database_a_cert
authorization administrator
from file='c:\database_a_cert.cer'

create certificate database_b_cert
authorization administrator
from file='c:\database_b_cert.cer'

grant connect on endpoint::endpoint_mirroring to administrator

미러링 설정(마법사 설정가능)

Posted by 시스템매니아
,

SQL Server 2008 온라인 설명서(2009년 1월)
방법: 데이터베이스 미러링의 아웃바운드 연결에 대한 인증서 사용 허용(Transact-SQL)

이 항목에서는 인증서를 사용하여 데이터베이스 미러링의 아웃바운드 연결을 인증하도록 서버 인스턴스를 구성하는 단계를 설명합니다. 아웃바운드 연결을 구성한 후 인바운드 연결을 설정할 수 있습니다.

참고:
서버 인스턴스의 모든 미러링 연결에는 단일 데이터베이스 미러링 끝점이 사용됩니다. 따라서 단일 데이터베이스 미러링 끝점을 만들 때 서버 인스턴스의 인증 방법을 지정해야 합니다.

아웃바운드 연결 구성은 대개 다음과 같은 단계로 진행됩니다.

  1. master 데이터베이스에서 데이터베이스 마스터 키를 만듭니다.
  2. master 데이터베이스에서 서버 인스턴스의 암호화된 인증서를 만듭니다.
  3. 서버 인스턴스의 인증서를 사용하여 해당 인스턴스의 끝점을 만듭니다.
  4. 인증서를 파일에 백업하고 안전한 방법으로 다른 시스템에 복사합니다.

파트너와 미러링 모니터 서버 각각에 대해 이러한 단계를 수행해야 합니다.

다음 절차에서 이러한 단계를 자세히 설명합니다. 각 단계에서는 HOST_A라는 시스템에서 서버 인스턴스를 구성하는 예를 제공합니다. 예 섹션에서는 HOST_B라는 시스템에 또 다른 서버 인스턴스를 구성하는 동일한 단계를 보여 줍니다.

  1. master 데이터베이스에 데이터베이스 마스터 키가 없으면 새로 만듭니다. 데이터베이스의 기존 키를 보려면 sys.symmetric_keys 카탈로그 뷰를 사용합니다.

    데이터베이스 마스터 키를 만들려면 다음 Transact-SQL 명령을 사용합니다.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
    GO

    고유하고 강력한 암호를 사용하고 안전한 곳에 암호를 기록해 둡니다.

    자세한 내용은 CREATE MASTER KEY(Transact-SQL)를 참조하십시오.

  2. master 데이터베이스에서 데이터베이스 미러링을 위한 서버 인스턴스의 아웃바운드 연결에 사용할 암호화된 인증서를 서버 인스턴스에 만듭니다.

    예를 들어 HOST_A 시스템의 인증서를 만들려면 다음 코드를 사용합니다.

    USE master;
    CREATE CERTIFICATE HOST_A_cert 
       WITH SUBJECT = 'HOST_A certificate for database mirroring';
    GO

    자세한 내용은 CREATE CERTIFICATE(Transact-SQL)를 참조하십시오.

    master 데이터베이스에 있는 인증서를 보려면 다음 Transact-SQL 문을 사용합니다.

    USE master;
    SELECT * FROM sys.certificates;

    자세한 내용은 sys.certificates(Transact-SQL)를 참조하십시오.

  3. 서버 인스턴스마다 데이터베이스 미러링 끝점이 있는지 확인합니다.

    서버 인스턴스의 데이터베이스 미러링 끝점이 이미 있으면 서버 인스턴스에서 설정하는 다른 모든 세션에 해당 끝점을 다시 사용해야 합니다. 서버 인스턴스에 데이터베이스 미러링 끝점이 있는지 확인하고 해당 구성을 보려면 다음 문을 사용합니다.

    SELECT name, role_desc, state_desc, connection_auth_desc, encryption_algorithm_desc FROM sys.database_mirroring_endpoints;

    끝점이 없으면 아웃바운드 연결에는 이 인증서를 사용하고 다른 시스템의 확인에는 이 인증서의 자격 증명을 사용하는 끝점을 만듭니다. 이 끝점은 서버 인스턴스가 참여하는 모든 미러링 세션에 사용되는 서버 차원의 끝점입니다.

    예를 들어 HOST_A에 예제 서버 인스턴스의 미러링 끝점을 만들려면 다음 코드를 사용합니다.

    CREATE ENDPOINT Endpoint_Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7024
          , LISTENER_IP = ALL
       ) 
       FOR DATABASE_MIRRORING ( 
          AUTHENTICATION = CERTIFICATE HOST_A_cert
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       );
    GO

    자세한 내용은 CREATE ENDPOINT(Transact-SQL)를 참조하십시오.

  4. 인증서를 백업하고 다른 시스템에 복사합니다. 다른 시스템에서 인바운드 연결을 구성하는 데 이 인증서가 필요합니다.

    BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';
    GO

    자세한 내용은 BACKUP CERTIFICATE(Transact-SQL)를 참조하십시오.

    안전한 방법으로 이 인증서를 복사합니다. 인증서 보안에 각별한 주의가 필요합니다.

앞 단계의 예제 코드에서는 HOST_A에 아웃바운드 연결을 구성합니다.

이제 HOST_B에 대해 동일한 아웃바운드 단계를 수행해야 합니다. 이러한 단계는 다음 예 섹션에 설명되어 있습니다.

다음 예에서는 아웃바운드 연결을 위한 HOST_B 구성을 보여 줍니다.

USE master;
--Create the database Master Key, if needed.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';
GO
-- Make a certifcate on HOST_B server instance.
CREATE CERTIFICATE HOST_B_cert 
   WITH SUBJECT = 'HOST_B certificate for database mirroring';
GO
--Create a mirroring endpoint for the server instance on HOST_B.
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   ) 
   FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE HOST_B_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO
--Backup HOST_B certificate.
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOST_B_cert.cer';
GO 
--Using any secure copy method, copy C:\HOST_B_cert.cer to HOST_A.

안전한 방법으로 인증서를 다른 시스템에 복사합니다. 모든 인증서를 안전하게 보관하는 데 많은 주의를 기울여야 합니다.

중요:
아웃바운드 연결 설정 후에는 각 서버 인스턴스에서 다른 서버 인스턴스에 대한 인바운드 연결을 구성해야 합니다. 자세한 내용은 방법: 데이터베이스 미러링의 인바운드 연결에 대한 인증서 사용 허용(Transact-SQL)을 참조하십시오.

Transact-SQL 예를 포함하여 미러 데이터베이스를 만드는 방법은 방법: 미러 데이터베이스의 미러링 준비(Transact-SQL)를 참조하십시오.

성능 우선 모드 세션을 설정하는 Transact-SQL 예는 예: 인증서를 사용하여 데이터베이스 미러링 설정(Transact-SQL)을 참조하십시오.

네트워크 보안을 보장할 수 없는 경우 데이터베이스 미러링 연결에 암호화를 사용하는 것이 좋습니다.

인증서를 다른 시스템에 복사할 때는 안전한 복사 방법을 사용하십시오.

Posted by 시스템매니아
,

 Question
디비를 여러게 사용하고 있는데요  
Microsoft SQL Server Management Studio로 접속하면 권한이 없어도 해당 디비명이 다 보입니다.  
기존 MSSQL2000에서는 sp_MSdbuseraccess를 변경해서 자기 디비만 보이도록 설정을 했었는데  
MSSQL2005에서는 설정하는 방법이 없나요?

 

 

다음과 같은 방식으로 SSMS에서 자신의 데이터베이스만 SSMS에 나타나도록 설정할 수 있습니다.

 

-- 테스트용 사용자 데이터베이스 생성

CREATE DATABASE User1DB

GO

CREATE DATABASE User2DB

GO

 

-- 데이터베이스에 해당하는 사용자 계정 생성

CREATE LOGIN User1 WITH PASSWORD='AAA', DEFAULT_DATABASE=User1DB

GO

CREATE LOGIN User2 WITH PASSWORD='AAA', DEFAULT_DATABASE=User2DB

GO

 

--public 대해 DATABASE VIEW 권한 취소

REVOKE VIEW ANY DATABASE FROM public

GO

 

-- DB 대해 Owner 설정

USE USER1DB

GO

sp_changedbowner 'User1'

GO

 

USE USER2DB

GO

sp_changedbowner 'User2'

GO

 

 

 

 

, 위의 방법은 해당 DB에 대한 dbowner를 변경하는 방식이기 때문에 데이터베이스 당 하나의 로그인만 지정할 수 있습니다.

(혹시 다른 방법이 있을까요??)

출처 :  http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005Admin&intPage=1&intCategory=0&strSearchCategory=|s_name|s_subject|&strSearchWord=&intSeq=1319


Posted by 시스템매니아
,

SQL Injection 공격이 여전히 활발하게 들어오고 있습니다. 최근에는 MSSQL이건 Oracle이건 가리지 않는 속성을 보여주더군요. 요즘의 SQL Injection공격은 DB를 변조하는 방식으로 진행되었지만, 아직도 일부 SQL Injection 공격은 여전히 시스템 장악을 목적으로 사용됩니다. 가장 대표적인 공격이 바로 xp_cmdshell 이겠지요.

xp_cmdshell와 같은 명령어는 잘쓰면 좋지만, 아무나 사용할 수 있다면 최악의 상황으로 이어질 수 있습니다.

type c:\boot.ini 대신에 del c:\boot.ini를 했다면?

 이러한 공격을 막는 방법으로 서버에 도달하기 이전에 웹방화벽에서 필터링을 하고, 웹소스 레벨에서 필터링을 하고, DB에서 적절한 권한 부여라는 모든 단계를 거치는 것이 좋습니다. 이 방법을 모두 동원할 수 없다 할지라도, 적어도 두개 이상의 방법을 선택하여 진행하여야 안전하다고 할 수 있습니다.

  • 웹방화벽이 완벽을 말할 수 없는 이유는 학습하는데 걸리는 시간이 있기 때문이며(WebKnight와 같이 지능형 장비가 아닌 경우는 패턴 추가하기 전까지는 답이 없습니다),
  • 웹소스 레벨의 필터링은 개발자의 실수가 있을 수 있기 때문이며,
  • DB에서의 권한 부여는 기본값 그대로 쓰기에는 충분히 위험하기 때문입니다.

 위에 나열한 3가지 필터링할 수 있는 부분 중에서 어느 한 곳만이라도 확실히 막기만 한다면, 웹 취약점을 이용한 공격은 생각보다 많이 막을 수 있습니다, 그 중에서 DB의 권한 제어를 위해 금지 또는 조심해야 할 확장 프로시저는 다음과 같은 형식으로 제어할 수 있습니다.

USE master
DENY  EXECUTE  ON [master].[dbo].[xp_availablemedia] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_dirtree] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_fileexist] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_fixeddrives] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_getfiledetails] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regaddmultistring] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regdeletekey] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regdeletevalue] TO [guest] CASCADE 
DENY  EXECUTE  ON [master].[dbo].[xp_regread] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regremovemultistring] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regwrite] TO [guest] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_subdirs] TO [guest] CASCADE

DENY  EXECUTE  ON [master].[dbo].[xp_availablemedia] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_dirtree] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_fileexist] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_fixeddrives] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_getfiledetails] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regaddmultistring] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regdeletekey] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regdeletevalue] TO [public] CASCADE 
DENY  EXECUTE  ON [master].[dbo].[xp_regread] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regremovemultistring] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_regwrite] TO [public] CASCADE
DENY  EXECUTE  ON [master].[dbo].[xp_subdirs] TO [public] CASCADE

USE msdb
REVOKE ALL ON dbo.mswebtasks FROM public
REVOKE EXECUTE ON sp_add_job FROM public
REVOKE EXECUTE ON sp_add_jobserver FROM public
REVOKE EXECUTE ON sp_add_jobstep FROM public
REVOKE EXECUTE ON sp_enum_dtspackages FROM public
REVOKE EXECUTE ON sp_get_dtspackage FROM public
REVOKE EXECUTE ON sp_get_sqlagent_properties FROM public
REVOKE EXECUTE ON sp_start_job FROM public

 위에 있는 쿼리는 SQL 서버에 쿼리 분석기로 연결한 후 한번 실행해주면 간단하게 할 수 있는 부분입니다만, 실제 서버에 바로 적용하면 돌이킬 없는 상황이 되버릴 수도 있습니다. 특히 스케줄 부분에 대한 확장 프로시저를 건드리기 때문에 복잡한 구성을 가진 SQL 서버라면 적용시 장애로 이어질 수도 있습니다.

 더구나, 위에 있는 쿼리를 실행한다고 해서 서버가 완벽하게 보호되는건 아닙니다. SELECT 나 UPDATE 같은 쿼리를 통해서 DB의 내용 자체를 변조하는 공격을 막을 수는 없기 때문입니다. 

 

출처 : http://www.ntfaq.co.kr/4307

Posted by 시스템매니아
,

SQL injection 방어대책.

1) 특수문자 처리
- ‘ (작은 따옴표) 의 처리
     ex) Login.asp 의 경우 id=replace(id,"'","''") - ‘를 ’‘로 바꾸어줌.
- ; (세미콜론) 의 처리
     ex) Login.asp 의 경우 id=replace(id,";","") - ;을 NULL값으로 바꾸어줌.

2) 주석처리
-주석을 입력받을 경우 mon=replace(mon,"--","") - --을 NULL값으로 바꾸어줌.

3) 입력 값의 길이 제한
- maxlength 을 이용한 방법
   ex) <input type=text maxlength=10> - 최대입력 값을 10으로 설정.

4) DB와 연동하는 스크립트의 모든 파라미터들을 점검하여 사용자의 입력값이
   SQL injection을 발생시키지 않도록 수정.

4) 사용자 입력시 특수문자(‘ “ / \ ; : space - +)등이 포함되어 있는지 검사.

5) SQL Server의 에러메세지를 사용자에게 보여주지 않도록 설정.
   - 공격시 리턴되는 에러메세지를 분석하여 공격에 성공할수 있는 SQL injection
      스트링을 알아낼수 있음.

6) 웹 애플리케이션에 사용하는 DB 사용자의 권한을 제한.
   - 일반 사용자 권한으로 모든 System stored procedures에 접근하지 못하도록
      하여 DB전체에 대한 제어권을 얻거나 DB를 운용중인 Server에 대한 접근이
      불가능하도록 함.

7) ‘허용 방식’ 을 사용하여 인자를 검색
- 데이터 유형 (문자열, 정수형, 실수형) 에 대한 검증.
- 허용된 문자셋 (character set) 의 검증.
- 최대 / 최소길이
- NULL 값 허용 여부
- 필수 매개변수, 불필요 매개변수
- 중복 허용 여부 (ex: ID 입력시)
- 숫자의 범위 (날짜의 1-12월)
- 타당한 것으로 지정된 값 (열거형 - enumeration)
- 타당한 패턴의 정규표현식 (이메일 입력시: @)
Posted by 시스템매니아
,


declare @tab varchar(255), @col varchar(255), @owner varchar(255), @type int

declare table_cursor cursor for

select so.name, sc.name, sc.xtype, su.name

from sysobjects so inner join syscolumns sc on so.id = sc.id

     inner join sysusers su on so.uid = su.uid

where so.xtype='u' and (sc.xtype=99 or sc.xtype=35 or sc.xtype=231 or sc.xtype=167)

open table_cursor

fetch next from table_cursor into @tab, @col, @type, @owner

while(@@fetch_status=0)

Begin

-- <<악성코드>> 부분을실제삭제하려는악성코드로수정해주세요. (예: )

 if (@type = 35 or @type = 167)  -- varchar나text 데이터타입일경우(SQL2005 환경이라면varchar(max) 사용)

    exec('update ' + @owner + '.[' + @tab + '] set [' + @col + '] = left(
            convert(varchar(8000), ['+@col+']),
            len(convert(varchar(8000), ['+@col+'])) - 6 -
            patindex(''%tpircs<%'',
                      reverse(convert(varchar(8000), ['+@col+'])))
            )
      where ['+@col+'] like ''%<script%</script>'''
      );


 else   -- nvarchar나ntext 데이터타입일경우(SQL2005 환경이라면nvarchar(max) 사용)                   

     exec('update ' + @owner + '.[' + @tab + '] set [' + @col + '] = left(
            convert(varchar(4000), ['+@col+']),
            len(convert(varchar(4000), ['+@col+'])) - 6 -
            patindex(''%tpircs<%'',
                      reverse(convert(varchar(8000), ['+@col+'])))
            )
      where ['+@col+'] like ''%<script%</script>'''
      );


 print '[' + @col + ']' + ' column of ' + @owner + '.' + @tab + ' has been updated.'

 fetch next from table_cursor into @tab, @col, @type, @owner

End

close table_cursor

deallocate table_cursor

Posted by 시스템매니아
,

* Script 삽입 공격을 당했는지 확인하는 쿼리

-------------------------------------------------------------
DECLARE @T varchar(255), @C varchar(255);
DECLARE Table_Cursor CURSOR FOR
SELECT a.name, b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167);
OPEN Table_Cursor;
FETCH NEXT FROM Table_Cursor INTO @T, @C;
WHILE (@@FETCH_STATUS = 0) BEGIN

exec ('select ['+@C+'] from ['+@T+'] where ['+@C+'] like ''%<script%</script>''');
-- print 'select ['+@C+'] from ['+@T+'] where ['+@C+'] like ''%<script%</script>'''

  FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;
---------------------------------------------------------------------------------

* 위의 공격을 당했을 때 복원하는 쿼리 (100% 다 되는 것은 아님 - 별도 확인 필요)

* 해킹 시 길이가 긴 경우에는 짤리고 들어가는 현상이 발생함 - 이 경우에는 복원을 해도 원상복구가 안됨

* 백업 받은 것을 복원하는 수 밖에는 없음

---------------------------------------------------------------------------------
DECLARE @T varchar(255), @C varchar(255);
DECLARE Table_Cursor CURSOR FOR
SELECT a.name, b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167);
OPEN Table_Cursor;
FETCH NEXT FROM Table_Cursor INTO @T, @C;
WHILE (@@FETCH_STATUS = 0) BEGIN
  EXEC(
    'update ['+@T+'] set ['+@C+'] = left(
            convert(varchar(8000), ['+@C+']),
            len(convert(varchar(8000), ['+@C+'])) - 6 -
            patindex(''%tpircs<%'',
                      reverse(convert(varchar(8000), ['+@C+'])))
            )
      where ['+@C+'] like ''%<script%</script>'''
      );
  FETCH NEXT FROM Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE Table_Cursor;
----------------------------------------------------------------------------------

Posted by 시스템매니아
,
출처 : http://support.microsoft.com

요약
SQL Server 트랜잭션 로그가 가득 차면 데이터베이스에서 CHECKPOINT를 비롯하여 UPDATE, DELETE 또는 INSERT 작업을 더 이상 할 수 없습니다. 이러한 상황은 아래와 같은 오류 1105로 나타납니다.

Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
master 또는 tempdb를 비롯한 어떤 데이터베이스에서나 이러한 상황이 발생할 수 있습니다. 본 문서에서는 오류 1105를 일으키는 문제점에 대한 가능한 원인과 해결 방법을 설명합니다. 트랜잭션 로그가 가득 차서 오류 1105가 발생한 경우에는 DUMP TRANSACTION 문을 사용하여 로그를 비워야 합니다. DUMP TRANSACTION의 사용 방법에 대한 자세한 내용은 SQL Server 설명서를 참조하십시오.
추가 정보
Microsoft SQL Server 같은 전형적인 관계형 데이터베이스의 기본 특성은 트랜잭션 무결성을 보장하는 것입니다. 모든 트랜잭션은 시스템 오류가 발생한 경우에도 변경 내용이 모두 적용되거나 전혀 적용되지 않는다는 점에서 완전한 원자성(즉, 개별적으로 기능을 수행)을 가져야 합니다. 사용자 정의 트랜잭션에서 BEGIN TRANSACTION 문과 COMMIT TRANSACTION 문 사이에 있는 모든 문은 모두 적용되거나 또는 전혀 적용되지 않습니다. 암시적 트랜잭션에서는 각각의 개별 SQL 문을 원자 단위로 간주합니다.

이러한 기능 때문에 SQL Server는 프로덕션 단계에서 전원 공급이 차단되거나 운영 체제가 충돌하는 등의 문제가 발생한 다음 다시 시작되고 나면 사용자의 개입 없이 자동으로 데이터베이스를 일관된 상태로 복구합니다. 이와는 대조적으로 비 관계형 시스템에서는 시스템 오류가 발생하면 데이터베이스의 일관성 문제를 검사하기 위해 오랜 시간에 걸친 수동 작업이 필요한 경우가 많습니다.

이러한 기능은 바로 트랜잭션 로그 메커니즘에 의해 제공됩니다. 트랜잭션 무결성은 SQL Server의 기본 기능, 즉 본질적인 특성으로 간주되기 때문에 로깅 설정을 해제할 수 없습니다. 특정 유틸리티를 사용하거나 빠른 BCP 또는 SELECT INTO 같은 유지 관리 작업을 통해 최소 로깅을 수행할 수 있지만 이러한 최소 로깅에서도 나중에 롤백할 수 있도록 익스텐트 할당을 기록합니다.

로깅에는 상당히 많은 공간이 필요할 수 있습니다. 예를 들어, 대개의 경우 업데이트되는 각 데이터 행의 업데이트 이전 및 이후 이미지뿐만 아니라 영향을 받는 모든 인덱스 행의 이미지도 기록해야 합니다. 로그에 기록되는 각 행에 대해 일정 양의 트랜잭션 레코드 오버헤드를 기록해야 하기 때문에 업데이트되는 데이터와 소모되는 로그 공간의 비율은 행 너비에 따라 상당히 다를 수 있습니다. 좁은 행인 경우에는 특정 UPDATE, DELETE 또는 INSERT 작업에 소모되는 로그 공간이 소모되는 데이터 공간의 10배가 될 수 있습니다. 행이 넓으면 소모되는 로그 공간이 상대적으로 작아집니다. 트랜잭션 무결성을 제공하기 위해서는 로그 공간 소모를 피할 수 없습니다. 데이터베이스 관리자는 설치 시 충분한 로그 공간을 제공해야 합니다.

필요한 로그 공간은 여러 가지 요인에 따라 달라질 수 있기 때문에 정확하게 예측하기는 어렵습니다. 처음에 로그 크기를 데이터베이스 크기의 15-30% 크기로 할당하는 것이 적절하다는 일반적인 규칙이 있지만, 실제 상황에서는 이 규칙도 매우 다를 수 있습니다. 성공적인 SQL Server 설치에서는 사용할 특정 데이터 및 응용 프로그램에 필요한 로그 공간을 대략적으로 예측하기 위해 간단한 실증적 테스트를 수행한 다음 그 예측을 바탕으로 로그 크기를 정합니다. 테스트 없이 계산만을 바탕으로 로그 크기를 정하기는 어려우며 정확하지 않을 수 있습니다.

예측하기 어려운 여러 가지 요인이 로그 공간 소모량의 변화에 영향을 미칠 수 있습니다. 그러한 요인 중 하나는 쿼리 최적화 프로그램(Query Optimizer)입니다. 특정 SQL 데이터 수정 문의 경우 액세스 계획은 데이터의 통계적 분포에 따라 시간이 가면서 달라질 수 있습니다. 액세스 계획마다 서로 다른 크기의 로그 공간을 소모할 수 있습니다. 또 따른 요인은 피할 수 없는 내부 데이터베이스 조각화로서 이는 수행되는 페이지 분할의 수에 영향을 미칠 수 있습니다. SQL Server는 사용자를 대신하여 데이터를 자동으로 관리하기 때문에 이 프로세스를 조사하거나 이 프로세스에 영향을 주기 위해 사용자가 할 수 있는 일이나 해야 할 일은 없습니다.

간단한 테스트 예제로 DBCC CHECKTABLE(syslogs)을 실행할 수 있습니다. 그러면 데이터 수정 쿼리에 대한 대표 예제를 실행하기 전과 실행한 후에 로그에 있는 2048바이트 데이터 페이지 수가 반환됩니다. 이 방법으로 이 유형의 쿼리에 필요한 로그 공간 크기를 대략적으로 알 수 있습니다. SQL Server 같은 관계형 데이터베이스에 로그 공간이나 데이터 디스크 공간을 제공할 때는 일반적으로 공간을 과다하게 할당하는 것이 최선의 방법입니다.

SQL Server 7.0 및 2000 클래스 서버는 필요에 따라 트랜잭션 로그를 확장하는 기능이 있습니다. 확장되는 크기를 사용자가 조정할 수도 있고 사용 가능한 디스크 공간을 모두 이용하도록 지정할 수도 있습니다. 로그 파일은 여러 개의 가상 로그 파일로 구성됩니다. 이 가상 로그 파일의 개수와 크기는 SQL Server에서 자동으로 결정하며 사용자가 구성할 수 없습니다. 데이터베이스를 처음 생성할 때 각 실제(Physical) 로그 파일은 최소한 두 개의 가상 로그 파일을 가집니다. 데이터베이스 관리자는 로그 공간 부족이 발생하지 않도록 하기 위해 데이터베이스의 "검사점에서 로그 자름(Truncate log on checkpoint)" 옵션을 사용 가능하게 설정하기도 합니다. 이 옵션의 목적은 주로 백업을 위해 로그 덤프에 의존하지 않는 개발용 또는 테스트용 데이터베이스를 위해 자동으로 로그를 자르는 방법을 제공하는 것입니다. 이 옵션을 설정해도 로깅 또는 트랜잭션 무결성의 설정이 해제되지는 않습니다. 이 옵션을 설정하면 단지 검사점(Checkpoint) 처리기가 약 60초 간격으로 로그를 자를 뿐입니다. "검사점에서 로그 자름(Truncate log on checkpoint)" 옵션이 설정된 데이터베이스에 수동 검사점(Checkpoint) 명령을 실행하면 로그가 잘리지 않습니다. tempdb 데이터베이스에 대해서는 이 옵션이 항상 설정되어 있지만 sp_help 저장 프로시저(stored procedure) 출력의 상태 열에는 나타나지 않습니다.

"검사점에서 로그 자름(Truncate log on checkpoint)" 옵션을 설정하더라도 여러 가지 요인에 의해 로그 공간이 부족할 수 있습니다. 이러한 각각의 요인을 아래에서 설명합니다.
대량 원자 트랜잭션, 특히 일괄적인 UPDATE, INSERT 또는 DELETE 작업 트랜잭션: 각각의 단일 SQL 문은 완전히 적용되거나 전혀 적용되지 않아야 하는 원자 단위로 간주됩니다. 따라서 모든 행 교체가 로그에 기록되어야 하며 수행되는 동안에는 트랜잭션이 잘리지 않습니다. 예를 들어, 실행하는 데 5분이 걸리는 대량 일괄 INSERT가 실행될 경우 이 시간 동안은 해당 트랜잭션에 소모되는 로그가 잘리지 않습니다. 데이터베이스 관리자는 예상되는 가장 큰 일괄 작업을 수행하는 데 충분한 로그 공간을 제공하거나 일괄 작업을 좀더 작은 그룹으로 나누어 수행해야 합니다.
커밋되지 않은 트랜잭션: 로그는 가장 오래된 커밋되지 않은 트랜잭션 이전까지만 잘립니다. 커밋되지 않은 트랜잭션의 원인은 몇 가지가 있을 수 있지만 대개는 응용 프로그램 오류가 그 원인입니다. 원인은 아래와 같습니다.
일괄 트랜잭션: 위에서 설명한 것처럼 대량 일괄 트랜잭션이 수행되는 동안에는 트랜잭션에 의해 생성되는 로그 레코드가 잘리지 않습니다. 그러나 그러한 트랜잭션 때문에 같은 기간에 커밋을 수행하는 좀더 짧은 다른 트랜잭션의 로그도 잘리지 않습니다.

예를 들어, 데이터베이스 관리자가 가능한 가장 큰 일괄 트랜잭션을 수행하는 데 충분한 크기의 로그를 할당했다고 가정합니다. 그러나 이 트랜잭션이 실행되는 동안 좀더 짧은 다른 데이터 수정 문도 역시 로그 공간을 소모할 수 있습니다. 대량 일괄 트랜잭션이 먼저 시작되었고 그것이 가장 오래된 커밋되지 않은 트랜잭션이기 때문에 로그 공간은 잘리지 않습니다. 관리자는 대량 일괄 트랜잭션의 동시성과 로그에 미치는 영향을 인식하고 적절하게 로그 크기를 조정해야 합니다.
사용자 정의 트랜잭션 내에서 사용자 입력 또는 시간이 오래 걸리는 다른 작업을 허용하는 잘못 설계된 응용 프로그램: 응용 프로그램이 BEGIN TRANSACTION을 실행한 후 사용자의 동작에 따라 시간이 오래 걸릴 수도 있는 입력을 사용자에게 요구하는 경우를 예로 들 수 있습니다. 이 경우, 사용자가 응답하고 응용 프로그램이 COMMIT을 실행할 때까지는 로그를 자를 수 없습니다.
트랜잭션이 커밋되지 않는 응용 프로그램 오류: 이 오류의 일반적인 원인은 사용자 정의 트랜잭션 내에서 DB-Library 호출 dbcancel()을 잘못 처리하는 것입니다. dbcancel()을 사용하여 쿼리를 취소할 경우 현재 실행 중인 SQL 문은 중단되고 롤백되지만 다른 트랜잭션은 계속 실행됩니다. 응용 프로그램에서 이를 인식하고 필요한 ROLLBACK TRANSACTION 또는 COMMIT TRANSACTION 문을 실행하여 트랜잭션을 닫아야 합니다. 그렇지 않으면 아래와 같은 오류 3902가 발생합니다.

The commit transaction has no corresponding BEGIN TRANSACTION.
응용 프로그램에서 SELECT @@TRANCOUNT를 보내어 어떤 트랜잭션 중첩 수준이 있는지 확인하는 것이 유용할 수도 있습니다. 그러나 응용 프로그램에서 무조건 SELECT @@TRANCOUNT를 보낸 다음 COMMIT/ROLLBACK을 실행하여 @@TRANCOUNT=0을 얻을 수는 없습니다. @@TRANCOUNT가 응용 프로그램이 예상하는 것과 다르면 응용 프로그램이 트랜잭션 중첩 수준을 추적하는 데 실패했음을 의미하므로 응용 프로그램 설계에 오류가 있는 것입니다. 이 시점에서 COMMIT/ROLLBACK을 실행하면 응용 프로그램은 어떤 트랜잭션이 의도하지 않은 트랜잭션 수준이 되었는지 알지 못하기 때문에 의도하지 않은 트랜잭션이 적용되거나 중단될 수도 있습니다. 대신에 프로그래머는 응용 프로그램 및 관련된 저장 프로시저(stored procedure)를 디버깅하여 의도하지 않은 트랜잭션 수준의 원인을 알아내야 합니다.
네트워크 연결 끊김을 SQL Server에 알리지 않는 네트워크 오류: 사용자 정의 트랜잭션이 실행되는 동안 클라이언트 워크스테이션이 응답하지 않거나 다시 부팅되거나 종료되면 네트워크 계층에서 SQL Server에 이 사실을 알려야 합니다. 네트워크가 적절하게 이 사실을 알려주지 않으면 SQL Server에게는 클라이언트가 여전히 존재하는 것처럼 보이기 때문에 클라이언트가 열어 놓은 트랜잭션이 계속 유지됩니다. 이는 네트워크 문제이므로 네트워크를 조사해야 합니다. 관리자가 sp_who, sp_lock 또는 네트워크 유틸리티를 사용하여 여전히 존재하는 클라이언트 세션을 확인한 다음 수동으로 제거함으로써 이 문제를 해결할 수 있습니다.
차단으로 인해 커밋되지 않은 트랜잭션: 다중 사용자 환경에서는 열려 있는 트랜잭션이 다른 프로세스가 설정한 잠금(Lock)에 의해 차단될 수 있습니다. 이 경우 트랜잭션이 계속 열려 있어 로그를 자르지 못하게 합니다. 이 문제를 확인하려면 프로그래머 또는 데이터베이스 관리자가 sp_who, sp_lock 또는 기타 도구를 사용하여 동시성 환경을 분석해야 합니다. 대부분의 경우 적절한 쿼리, 인덱스 및 데이터베이스 설계를 통해 차단 문제를 줄이거나 없앨 수 있습니다.
데이터 수정 쿼리를 취소하는 데 실패: 응용 프로그램이 dbcancel()을 실행했으나 네트워크 또는 SQL 문제로 인해 쿼리가 취소되지 않으면 쿼리는 계속 실행되고 트랜잭션은 열린 상태를 유지합니다. 이 문제가 의심스러우면 sp_who를 사용하여 쿼리가 취소되었는지 확인합니다. TCP/IP 소켓 클라이언트에서 취소를 시도하는 경우에는 명명된 파이프(Named Pipe) 클라이언트에서 테스트를 수행하거나 로컬 파이프를 사용하여 서버 컴퓨터에서 클라이언트 응용 프로그램을 실행합니다. 이 방법은 쿼리가 취소되지 않은 것이 네트워크 문제 때문인지 SQL 문제 때문인지 구분하는 데 도움이 됩니다.
검사점(Checkpoint) 처리기 잘라내기 대역폭 초과: 로그는 60초마다 잘리지만 이 잘라내기 속도는 제한적입니다. 이 시나리오는 일반적이지 않으므로, 먼저 기타 가능한 로그 오버플로의 원인을 고려하여 배제한 후 이 시나리오에 해당하는지 검사해야 합니다. 그러나, 많은 클라이언트가 동시에 대량 업데이트를 수행하면 최대 잘라내기 속도보다 로그가 차는 속도가 더 빠를 수 있습니다. 이는 액체를 일정한 속도로만 배출할 수 있는 깔대기를 사용할 때 배출하는 동안에도 액체가 넘칠 수 있는 것과 유사합니다. 이 시나리오에 해당하면 업데이트되는 행의 수를 줄이도록 응용 프로그램을 다시 구성할 수 있습니다. 업데이트되는 행의 수를 줄이는 것은 항상 모든 관계형 데이터베이스의 기본 설계 목표가 되어야 합니다.

이 방법으로 해결할 수 없으면 스트라이핑, 추가 컨트롤러 등을 통해 디스크 I/O 대역폭을 늘리도록 시스템을 재구성할 수 있습니다. 이 경우에는 일반적으로 검사점(Checkpoint) 처리기 프로세스가 로그 잘라내기를 계속 시도하면서 DUMP TRANSACTION 상태에서 점점 더 많은 시간을 소모하는지 확인합니다. 잘라내기 임계값을 초과하고 나면(아래 참조) 로그를 지울 때까지 검사점(Checkpoint) 처리기가 해당 데이터베이스에서 잘라내기를 시도하는 것을 보지 못할 수도 있습니다.
잘라내기 임계값 초과: 검사점(Checkpoint) 처리기는 기본적으로 DUMP TRANSACTION WITH TRUNCATE_ONLY를 수행합니다. 로그가 이미 특정 지점까지 가득 찼으면 수동으로 실행했을 때처럼 덤프가 항상 성공하지는 않습니다. 예를 들어, 검사점(Checkpoint) 처리기가 로그를 잘라낸 후 다음에 다시 잘라내기 전에 많은 업데이트 작업이 몰려 로그가 95%까지 채워질 수 있습니다. 검사점(Checkpoint) 처리기가 잘라내기를 시도할 때는 로그가 완전히 채워지지는 않았지만 거의 가득 차서 잘라내기가 불가능할 수도 있습니다. 이 때 잘라내기를 수행할 수 없는 이유는 로그 잘라내기 자체를 로그에 기록해야 하기 때문입니다. 이 경우 유일한 해결 방법은 DUMP TRANSACTION WITH NO_LOG를 사용하여 수동으로 로그를 잘라내는 것입니다. NO_LOG 옵션을 사용하는 것은 로그에 기록되지 않는 작업이기 때문에 이 작업을 하는 동안 시스템에 오류가 발생하면 데이터베이스 오류로 이어질 수 있으므로, 반드시 필요한 경우가 아니면 NO_LOG 옵션을 사용하지 않는 것이 좋습니다.
위 요인들 간 상호 작용: 예를 들어, 업데이트가 많은 환경인 경우 정상 조건에서는 검사점(Checkpoint) 처리기 잘라내기 속도에서 로그가 가득 채워지지 않습니다. 예를 들어, 잠금(Lock) 경쟁 같은 위 조건 중 하나에 의해 일시적으로 열린 트랜잭션이 로그를 50%까지 채운다고 가정하면 다른 업데이트 상황을 처리하기 위한 공간이 줄어들어 자동 잘라내기가 불가능한 지점인 잘라내기 임계값까지 도달할 가능성이 더욱 커집니다. tempdb 데이터베이스에서의 트랜잭션은 다른 데이터베이스의 경우와 마찬가지로 로그에 기록됩니다. TRUNCATE LOG ON CHECKPOINT는 tempdb에 있기 때문에 대부분의 경우 로그가 잘리고 오버플로가 발생하지 않습니다. 그러나 위에 언급한 상황으로 인해 tempdb 로그가 가득 채워질 수 있습니다. Tempdb는 일반적으로 혼합된 로그 및 데이터(sysusages.segmap=7)를 위해 구성되므로 데이터 작업과 로그 작업이 사용 가능한 동일 공간을 차지하기 위해 경쟁합니다. GROUP BY, ORDER BY DESC 등과 같은 특정 Transact-SQL 구성 요소는 작업 공간을 확보하기 위해 자동으로 tempdb를 요구합니다. 이 트랜잭션은 또한 작업 공간을 위해 tempdb에서 암시적 BEGIN TRANSACTION 레코드를 발생합니다. 이 tempdb 트랜잭션은 사용자 데이터베이스에서 트랜잭션 기간 동안 계속되며 이로 인해 이 기간 동안 tempdb 로그 잘라내기가 지연될 수 있습니다. 잠금(Lock)에 의한 차단 또는 응용 프로그램이 dbnextrow()를 완전히 처리하지 않는 등의 문제로 인해 사용자 데이터베이스에서 트랜잭션이 중단되면 tempdb에서의 트랜잭션이 열린 상태로 남게 되어 tempdb 로그 잘라내기를 방해할 수 있습니다. 프로그래머는 응용 프로그램을 디버깅하고 이 문제를 유발한 동시성 문제를 해결해야 합니다.
SQL Server 7.0 및 2000 클래스 서버에서 트랜잭션 로그 잘라내기는 가상 로그 파일(VLF)을 잘라냄으로써 수행됩니다. 활성 로그의 일부분이 특정 VLF에 있으면 해당 VLF를 잘라낼 수 없습니다. 활성 로그가 모든 VLF에 있으면 해당 로그를 잘라낼 수 없습니다. autogrowth가 사용 가능하게 설정되어 있고, 트랜잭션 로그가 있는 볼륨에 공간이 있으며 최대 파일 크기에 도달하지 않았으면 트랜잭션 로그는 로그 파일 등록 정보에 지정된 크기까지 커집니다.
아래에서는 TRUNCATE LOG ON CHECKPOINT의 설정 여부에 따라 SQL을 시작할 때의 로그 잘라내기 동작을 설명합니다.
TRUNCATE LOG ON CHECKPOINT를 설정했고 시작할 때 로그가 가득 차있으면 no_log를 사용하여 로그가 자동으로 덤프됩니다.
마스터 데이터베이스의 로그를 별도의 장치에 넣으면 로그가 로드될 수 없기 때문에 이제 마스터 데이터베이스에서는 TRUNCATE LOG ON CHECKPOINT가 기본값입니다. 가능한 옵션은 로그가 가득 찼을 때 삭제하는 것 뿐입니다.
TRUNCATE LOG ON CHECKPOINT를 설정하지 않았고 시작할 때 로그가 가득 차지 않았으면 복구는 완료되지만 최종 검사점(Checkpoint)이 작성되지 않습니다. 관리자는 데이터베이스에 액세스하여 no_truncate로 로그를 덤프하여 데이터를 저장한 다음 no_log로 덤프하여 로그를 제거하거나 덤프 작업 없이 바로 제거할 수 있습니다.

Posted by 시스템매니아
,

MS-SQL table 복사

MS-SQL 2008. 8. 8. 11:01
전체 table복사

insert into aaa_new
select * from aaa

조건 이용 부분 table복사
insert into aaa_new
select top 34500 * from aaa where date_rec >= '2003-08-01' order by date_rec
Posted by 시스템매니아
,

개체소유자변경
-해당 DB에 테이블의 소유자는 전부 dbo로 변경됨
SP_MSFOREACHTABLE 'sp_changeobjectowner ''?'' , ''dbo'''

Posted by 시스템매니아
,

Collaction 정보역시 마찬가지 인데요 이것은, SQL Server의 기본 데이터 정렬을 설정하는 옵션 입니다. MSSQL 을 설치할 때 대부분 Korean_Wansung_CI_AS 으로 설정을 하게 됩니다. 이것의 의미는 "한국어_완성형 문자" 라는 의미 입니다.

테이블에 데이타를 입력할경우, 특정칼럼 기준으로 정렬할경우 완성형으로 비교 판단 하게 되겠지요.

SELECT * FROM ::fn_helpcollations()
GO

쿼리분석기에서 위의 명령을 내리면, MSSQL에서 지원하는 Collaction 정보들이 나오게 됩니다.

Collaction 정보를 변경하고자 한다면,

ALTER DATABASE CollateDb
COLLATE Korean_Wansung_CI_AI
GO

이렇게 하시면 됩니다.

확인은 아래처럼 하시구요...

EXEC sp_helpdb 'CollateDb'
GO

이렇게 했을때 오류가 발생할 수도 있습니다.

"칼럼 'xxx'는 데이터베이스 데이타 정렬에 종속되어 있습니다."

위의 오류가 나타나면, 데이터베이스에 있는 개체들이 데이터베이스의 정렬에 종속되어 있는 경우에 발생되는 오류입니다. 따라서 해당 개체들을 다른 Collation으로 변경을 한 다음에 데이터베이스 Collation으로 변경을 하셔야 합니다.

아래는 Collaction 의 변경에 대한 구문입니다.

Korean_Wansung_CI_AI  으로의 변경

ALTER TABLE 테이블명
ALTER COLUMN 칼럼명 VARCHAR(50) COLLATE Korean_Wansung_CI_AI
GO

ALTER TABLE 테이블명
ALTER COLUMN 칼럼명 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS 
GO

sp_help 테이블명 을 통해서 결과를 확인해 보시면 되겠습니다.

만약  아래와 같은 메시지가 나왔다면,

-------------------------

서버: 메시지 5074, 수준 16, 상태 8, 줄 1
개체 'pk_테이블명은'은(는) 열 '칼럼명'에 종속되어 있습니다.
서버: 메시지 4922, 수준 16, 상태 1, 줄 1
하나 이상의 개체가 이 열을 액세스하기 때문에 ALTER TABLE ALTER COLUMN '칼럼명'이(가) 실패했습니다.

-------------------------

온라인 설명서에서 "데이터 정렬 변경"이라는 제목의 문서를 보시기 바랍니다.

애러의 원인은 다음과 같습니다.

------------------------

현재 다음 항목에서 참조하는 열의 데이터 정렬은 바꿀 수 없습니다.

계산된 열
인덱스
자동으로 또는 CREATE STATISTICS 문에 의해 생성된 배포 통계
CHECK 제약 조건
FOREIGN KEY 제약 조건
------------------------

 기본키 제거 -> Collation 변경 -> 기본키 재생성 의 순서에 따라서 Collation의 정보를 변경 하시면 됩니다.

Posted by 시스템매니아
,

select table_name = convert(varchar(30), min(o.name)), table_size = ltrim(str(sum(reserved) * 8192 / 1024.,15,0) + 'KB')
from sysindexes i inner join sysobjects o on (o.id = i.id)
where i.indid in (0, 1, 255) and o.xtype = 'U'
group by i.id
go

Posted by 시스템매니아
,

출처 : dbguide.net 에 올려진 SSA 성대중 책임 컨설턴트 님의 글

해결! DB D&A>> 중소기업을 위한 SQL Server 기본 모니터링 - 3

악성 쿼리 진단

 

연재 순서

1 시스템 진단(백업 시스템 현황, 디스크 현황)  /  2 서버 점검 운영 진단  /  3 쿼리 진단(악성 쿼리) 기타 논의 사항

 

4. 쿼리 진단

<전문가> 애플리케이션 성능의 경우 애플리케이션에서 데이터베이스로 호출할 문제가 있는데, 부분은 파워빌더로 개발하면서 오류가 발생한 같다. 이것은 직접 수정하기는 어려운 상황이니까, 쿼리 상에서 오류점이 있는지 살펴보고 수정을 하도록 하겠다.

<전문가> attention 되는 부분을 중심으로 살펴보면, 테이블 디자인이 잘못 되어 있는 것이 있다. tb_szd20 테이블의 경우, 모든 컬럼이 varchar(510)으로 설정되어 있다. 물론 실제 저장된 데이터는 이보다 작은 값으로 저장되어 있지만, 만약 전체 칼럼의 합이 8000Bytes 이상이 되면, 저장시 오류가 발생한다. 부분은 개발과정에서 담당자의 실수로 인한 것으로 판단되기 때문에, 적절하게 수정되어야 한다. 또한 인덱스를 생성하기 위해서도 컬럼의 길이를 조정해야 필요가 있다. PGM_ID 칼럼을 varchar(10) 변경하고, 인덱스를 추가하면 성능에 도움이 것이다.

해결 쿼리 1>>

select PGM_ID, PGM_NM FROM comm.dbo.tb_szd20 WHERE pgm_id ='saa02'

 

증상)

테이블 스캔 발생

 

문제원인)

PK 없음, 데이터모델링 오류(모든 칼럼이 VARCHAR(510)으로 되어 있음)

 

문제해결)

PK 추가

alter table tb_szd20 add constraint PK_tb_szd20 PRIMARY KEY (PGM_ID)

 

칼럼 데이터형식 변경

alter table tb_szd20 alter column PGM_ID varchar(10) not null

alter table tb_szd20 alter column PGM_NM varchar(50) not null

 

<전문가> 출하 업무 부분에서 출하 부분(수주 번호) 인덱스를 가지고 있는데, 검색시 날짜 조건을 요구하다 보니 프로세싱이 원활하지 않다. 검색 조건을 지원하기 위한 인덱스를 추가해야 한다.

<신청자> 프로그램에서 검색 목록을 선택할 , 어떤 목록을 선택하느냐에 따라 속도가 많이 차이 난다.

해결 쿼리 2>>

SELECT  v11.SAL_DT             AS            SAL_DT,

                                                     v11.SAL_NO            AS            SAL_NO,

                                                     v11.CUST_CD         AS            CUST_CD,

                                                     … (중간생략)

                                                     v11.REMARK           AS            REMARK

FROM      TB_SAA20               v11,

                                                     TB_SZA10                v12

                   WHERE v11.SAL_DT             >=                               '20070501'

                     AND   v11.SAL_DT             <=                               '20070521'

                     AND   v11.CUST_CD         LIKE         RTRIM(LTRIM('')) + '%'

                     AND   v11.CUST_CD         *=                               v12.CUST_CD

ORDER BY               v11.SAL_NO DESC

 

증상)

테이블 스캔 발생

 

문제원인)

검색 조건에 지원하기 위한 인덱스 누락(SAL_NO 에만 인덱스 존재)

 

문제해결)

인덱스 추가

create index IX_SAL_DT ON TB_SAA20(SAL_DT,CUST_CD)

 

<전문가> 현재 시스템에서 가장 많은 I/O 발생시키는 쿼리를 찾기 위해, 프로필러에서 Read 컬럼이 1000보다 쿼리를 추적하였다. 가장 문제가 되는 쿼리를 보면, ITEM_NAME() 이라는 사용자정의 함수를 SELECT 절에서 반복 호출하고 있다. 또한 ITEM_NAME() 사용자정의함수의 내부 코드를 분석한 결과, 쿼리를 지원하기 위한 인덱스가 전혀 없는 상태이고, 테이블의 칼럼의 데이터형도 잘못 설정되어 있는 상태이다.

<신청자> 거래처 공통 코드 불러오는 부분이다.

<전문가> 잘못된 칼럼 길이를 수정해서라도, 인덱스를 만들어 주면 성능을 개선할 있다. TB_SZC10 테이블의 GRP_CD 칼럼과 KND_CD 칼럼에 인덱스를 생성해 주면 된다.

해결 쿼리 3>>

SELECT  v11.SAL_NO                              AS            SAL_NO,

                  v11.SAL_SQ                              AS            SAL_SQ,

                  v11.SAL_DT                              AS            SAL_DT,

                  … (중간생략)

                  SALE.DBO.ITEM_NAME(V11.ITEM_CD, '1')          AS            STY_NM,

                  RTRIM(LTRIM(SALE.DBO.ITEM_NAME(V11.ITEM_CD, '2'))) + ' * ' +

                  RTRIM(LTRIM(SALE.DBO.ITEM_NAME(V11.ITEM_CD, '3')))                 AS            ITEM_SIZE,

                  SALE.DBO.ITEM_NAME(V11.ITEM_CD, '4')                            AS            COLO_NM,

                  SALE.DBO.ITEM_NAME(V11.ITEM_CD, '5')                            AS            GRAD_NM,

                  … (중간생략)

                  v11.REMARK                             AS            REMARK

  FROM  TB_SAA21               v11,

                  TB_SZA10                v12

                   WHERE v11.SAL_NO            =                                 '200705020003'

                     AND   v11.CUST_CD         *=                               v12.CUST_CD

  ORDER BY           v11.SAL_NO,

                                                     SUBSTRING(v11.ITEM_CD, 1, 1) +

                                                     SUBSTRING(v11.ITEM_CD, 12, 2) +

                                                     SUBSTRING(v11.ITEM_CD, 2, 10)

 

증상)

과도한 IO 발생

 

문제원인)

SELECT 절에 반복적으로 사용되는 dbo.ItemName() 사용자정의함수의 문제

 

문제원인)

PK 없음, 데이터모델링 오류(모든 칼럼이 NVARCHAR(510)으로 되어 있음)

 

문제해결)

칼럼 데이터형식 변경

alter table TB_SZC10 alter column GRP_CD nvarchar(4) not null

alter table TB_SZC10 alter column KND_CD nvarchar(20) not null

PK 추가

alter table TB_SZC10 add constraint PK_TB_SZC10 PRIMARY KEY (grp_cd,knd_cd)

 

성능이 20 이상 개선됨

개선사항>> 현재 데이터베이스에 있는 데이터 모델에 칼럼이 시스템 설계서와 다르게 구성되어 있는 부분도 많고, 인덱스 전략도 누락된 부분이 있는 것으로 판단된다. 또한 불필요한 사용자정의 함수의 반복호출로 인해 과도한 IO 발생하고 있다. 이에 대해서는 시간을 가지고 접근해야 하는 상황으로 보인다. 우선, 월말 결산 시점의 쿼리를 수집하여 추가적인 점검을 해야 하는 상황이라고 판단된다. 이를 위해 템플릿으로 5월말 결산시점의 쿼리를 수집하여 결과를 전달해 주기를 요청했다.

 

5. 기타 논의 사항

<전문가> 문제 상황을 진단하는 방법에 대해서는 일단 설명을 했지만, 문제 상황을 파악한 이를 직접 해결하는 것은 좀더 교육이 필요할 같다. 일단 비전문가이지만, 업무를 맡은 이상 기본적인 교육을 받고, 유지 관리를 하는 것이 바람직할 같다.

<신청자> 그래도 점이 고민이 된다. DBA 없는 상황에서, 교육을 받는 것이 좋을지, 아웃소싱을 하는 것이 좋을지.

<전문가> 아웃소싱을 하더라도 회사 내에 DB 대한 관리를 있는 주체는 필요하다. 아웃소싱으로 들어오는 인력들이 내부 담당자만큼 업무를 파악하거나 설계상의 문제까지 고려해서 관리해주긴 힘들다. 적어도 어떤 문제가 있고, 이것이 어떤 방법들로 해결될 있는지를 아웃소싱 담당자와 협의해서 결정할 있는 내부의 주체는 필요하다. 그러려면 아웃소싱을 하더라도 기본적인 교육을 통한 지식 획득이 필요하다고 본다.

<신청자> 다른 회사들은 주로 어떻게 관리하나. DBA 있나?

<전문가> 몇몇 대기업을 제외하곤, DBA 있는 경우가 드물다. DBA 있더라도 해당 업무 DB 전담하는 경우도 별로 없다. 결국 전산 관리자가 DB 관리를 책임지는 경우가 대부분이라고 있다.

<신청자> 어떻게 관리를 하면 좋을지. 평소에 관리할 시간은 부족하고, 그러다 문제가 발생하면 어디부터 손을 대야 할지 막막하다.

<전문가> 얘기한 사항이 사실 상충되는 부분이다. 평소에 관리를 하면, 문제가 생길 것이고, 문제가 생기더라도 어떤 부분이 문제인지 빨리 찾아 해결할 있다. 하지만 평상시에 방치하면, 당연히 문제 지점을 찾기가 어렵다. 지금까지 설명한 순서대로 적어도 2(마감 /) 사전 모니터링을 하면, 원인을 찾기 힘든 문제는 발생하지 않을 것이다.

Posted by 시스템매니아
,

출처 : dbguide.net 에 올려진 SSA 성대중 책임 컨설턴트 님의 글  

해결! DB D&A>> 중소기업을 위한 SQL Server 기본 모니터링 - 2

SQL 서버 설정과 테이블 컬럼 점검

 

연재 순서

1 시스템 진단(백업 시스템 현황, 디스크 현황)  /  2 서버 점검 운영 진단  /  3 쿼리 진단(악성 쿼리) 기타 논의 사항, 전문가 총평

 

2. SQL 서버 설정 점검

2-1. 데이터베이스 관리 기능 점검

<전문가> 애플리케이션 성능 이슈를 제기했는데, 스펙으로 봐선 시스템 상의 문제는 아닌 같다. DB D&A 신청서에는 미처 적지 못한, 성능상의 구체적인 문제점이 무엇이었나.

<신청자> 입출고 관리 프로그램에서 수행되는 테이블 목록 중에서 수불과 마감 프로세스가 느리다. 수불에서 데이터를 가져와서 마감을 하는 프로세스인데, 이때 속도가 많이 느려졌다. 테이블에서 데이터 수집할 때도 느렸었는데 부분은 하드디스크 증설과 윈도우 재설치 후에 좋아졌다.   

<전문가> 그럼 실제 SQL 서버에 접속해서 설정이 제대로 되어 있는지, 쿼리의 문제가 있는 부분이 있는지 같이 하나씩 살펴보자.

<신청자> 데이터베이스는 comm sale 있으며, comm 시스템의 기준정보를 저장하기 위한 목적으로 사용되고, 실제 트랜잭션 정보는 sale 데이터베이스에 저장되어 있다. Sale 데이터베이스는 500MB 크기이며, 전체 복구 모델을 사용하고 있다.

<전문가> 보니까 전체 DB 사이즈가 400MB(데이터베이스 파일 375MB, 로그파일 40MB)이고, 디스크 C 저장하고 있다. 자동축소 기능을 쓰고 있는데, 기능은 해제해야 한다. 자동 축소 기능은, 인터벌 타임마다 용량을 체크해서 여유 공간이 나오면 자동으로 줄여 버린다. 상태에서 다시 데이터가 늘어나면 자동증가 하게 되고 다시 자동증가한 크기가 자동축소 임계값 기준으로 여유공간이 남게 되면 자동축소 되는 과정이 반복되는 문제 발생의 원인이 있다. 또한 자동축소나 자동증가가 일어나는 동안에는 해당 영역에 대해서는 잠금이 설정되기 때문에 I/O 병목현상의 원인이 있다. 실행 DB 환경에는 기능은 해제하고 사용하는 것이 좋다. 기능은 바로 해제하도록 하겠다.

<전문가> 그리고 계정은 sa 계정을 사용하고 있는데, 보안 때문에 별도 관리 계정을 만드는 것이 좋다. 파일 증가를 400MB 제한하도록 설정했는데, 특별한 이유가 있는지.

<신청자> 특별한 이유가 있는 것은 아니고, 인터넷에서 기본 설정 가이드라인이 그렇게 되어 있는 것을 봤다.

<전문가> 데이터는 늘어나기 마련이기 때문에 파일 증가는무제한으로 두는 것이 바람직하다. 현재 시스템환경으로 봤을 , 데이터 파일의 경우 500MB, 로그 파일은 200MB 정도로 늘려주는 것이 좋다. , 백업할 파일 크기를 기억하고 있다가 복원할 , 디스크상에 백업할 당시 공간이 남아 있어야 복원이 가능하다는 것에는 유념해야 한다. 그리고 백업의 경우엔 데이터와 로그 백업을 F 받아서, 데이터 파일이나 로그 원본 파일과는 분리해서 저장하는 좋을 같다.

<신청자> 인터넷으로 보기로는 로그 파일은 트랜잭션 백업을 하면 비워진다고 해서, 작게 잡았다.

<전문가> 작게 잡은 것이 문제가 되는 것은 아니다. 하지만 저장 공간이 많이 여유로운 상황이 아니기 때문에 좀더 탄력적으로 운영할 필요가 있다. 성능 개선을 위해 인덱스 최적화 등을 권고할 예정이어서 여유 공간이 좀더 필요하기도 하다. 실제 월말 마감 작업을 경우에는 전날 정도 로그 파일 백업 용량을 줄여줬다가 마감 후에 늘리는 식으로 탄력적으로 활용하면 도움이 것이다.  

개선사항 1>> sale 데이터베이스에 자동축소(IsAutoShrink) 옵션이 설정되어 있다. 자동축소가 설정되어 있으면, 주기적으로 데이터베이스의 여유공간을 체크하여 설정한 임계값보다 많은 여유공간이 남은 경우, 데이터베이스를 자동으로 축소하게 된다. 이는 데이터가 계속 증가하는 운영 데이터베이스에서는 권장되지 않는 옵션이기 때문에 설정 해제할 것을 권고한다.  

개선사항 2>> 계속 증가하는 데이터베이스이기 때문에 자동 증가가 업무시간에 발생하지 않도록 하기 위해 데이터베이스 파일 크기를 500MB, 로그파일의 크기를 200MB 정도로 충분히 늘려주는 것을 고려해야 한다.

개선사항 3>> 최대 파일 크기를 제한하는 것보다는 무제한으로 설정하고, 파일 크기를 주기적으로 모니터링하여 디스크 공간부족이 발생하는지 여부를 체크하는 것이 바람직하다.

--확인방법

exec sp_helpdb sale

--실행결과

sale              404.88 MB        sa             7 04 16 2007 Status=ONLINE, Updateability=READ_WRITE,

UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=Korean_Wansung_CI_AS,

SQLSortOrder=0,

IsAutoShrink, IsTornPageDetectionEnabled,

IsAutoCreateStatistics, IsAutoUpdateStatistics       80

 

1  C:\Data\MSSQL\Data\sale_Data.MDF    PRIMARY    375168KB     716800KB    102400KB     data only

2  C:\Data\MSSQL\Data\sale_Log.LDF    NULL     39424KB     410624KB     102400KB     log only

 

2-2. 파일 사이즈 점검

<전문가> 현재 파일 사이즈를 보면, 366MB 할당된 데이터 파일 사이즈이고, 306MB 실제 사용되고 있는 것을 확인할 있다.

<신청자> 그럼 지금 너무 많이 사용되고 있는 건가?

<전문가> 할당된 영역 90% 정도 사용되고 있는 상태니까, 여유 공간이 부족하다고 있다. 이런 경우엔 100MB 자동 증가하게 되어 있으니까, 데이터 파일 사이즈를 500MB 정도까지 미리 늘려놓으면 충분할 것이다.

--확인방법

USE SALE

GO

DBCC SHOWFILESTATS

GO

--실행결과

Fileid        FileGroup                     TotalExtents         UsedExtents          Name           

1               1                   5862                  4909                  sale           

 

--계산방법(1 Extent = 8 Pages = 64 KB)

select 5862*8*8/1024. as Reserved

     , 4909*8*8/1024. as Used

, (4909*8*8/1024.)/(5862*8*8/1024.)*100.

 

Reserved                  Used         Usage(%)

366.375000              306.812500     83.7(%)

 

2-3. 테이블 컬럼 점검

<전문가> 사이즈에 대해선 정도 체크를 하고, 테이블을 살펴보도록 하자. 100MB 넘는 테이블이 있는지를 검색해볼 텐데, 별로 없을 같긴 하다. , tb_sfa10(제품 수불) 테이블이 검색되는데, 보면 58 6400건의 데이터가 있고 사이즈가 500MB 된다. 기본적으로 테이블의 전체 데이터 20% 변경되어야 자동통계업데이트가 되기 때문에, 테이블에 저장된 행수가 많아지면 성능에 문제가 있다. DBCC UPDATEUSAGE 명령을 사용하면, sysindexes 저장되어 있는 정보를 최신상태로 업데이트해 준다. 업데이트 결과를 보면, 270MB 낮춰진 것을 확인할 있다. 전에 전체 데이터 사이즈가 400MB인데, tb_sfa10 테이블만 500MB 조회되어서 깜짝 놀랐는데, 이제 정상화가 됐다.

 Tip>> 사용자 지정 메뉴에서 자주 쓰는 명령어를 11개까지 지정할 있다.

<전문가> 여기서 전체 컬럼을 보면 49개나 되고, 행의 크기(전체 컬럼 길이의 ) 524byte 된다.

<신청자> 그럼 데이터페이지 하나에 저장하지 할텐데?

<전문가> 물론 행의 크기가 커지면 데이터베이스 하나에 저장하지 못하기 때문에, 성능에 문제를 있다. 특히 문자열 칼럼의 길이가 실제 필요한 데이터보다 과도하게 설정되어 있는 경우가 많은 것으로 보여진다. 이에 대해서는 성능의 문제가 된다면 수정할 필요가 있다. 현재 3 정도의 신규 데이터가 존재하고 있고. 쿼리를 보면서 다시 살펴보겠지만, 쿼리를 지원하는데 필요한 인덱스가 부족하지 않나 싶다. 부분은 조금 후에 실제 쿼리들을 살펴보면서 인덱스 추가기 필요한 부분은 수정하는 것이 좋을 같다.  

<전문가> 지금 사용되고 있는 현황을 살펴보면, , 근데 아무도 접속이 되어 있네.

<신청자> 오전에 1, 2시간만 사용하고, 오후엔 거의 사용 한다.

<전문가> , 그럼 프로필러를 돌려보자. 프로필러는 익숙하게 사용하나?

<신청자> 지난번에 문제가 있을 프로필러를 알게 됐고, 요즘엔 한번씩 돌려본다. 아직 기능을 많이는 모른다.

<전문가> 기본적인 작업의 50%, 문제를 찾아내는 것의 대부분이 프로필러를 이용해서 이뤄지기 때문에, 툴은 좀더 익숙해지도록 노력할 필요가 있을 같다. 한가지 Tip으로 얘기하면, 금번 작업을 위해, 제공된 템플릿을 사용하면 기본적인 모니터링을 수행할 있다. 앞으로 성능 문제가 있을 때도 이를 실행해서 템플릿을 잡으면, 어떤 부분이 문제가 있는지를 있다. 그리고 데이터베이스ID 대해서 필터를 설정하는 것이 좋다. 1-4까지는 시스템 데이터베이스이기 때문에 5 이상으로 필터조건을 설정하는 것이 효율적일 것이다.

--확인방법

USE SALE

GO

-- 사전에 sysindexes 있는 정보를 최신상태로 업데이트

DBCC UPDATEUSAGE(0)

GO

select object_name(id),name, rows,  rowcnt, rowmodctr, reserved, used, dpages,*

from sysindexes

where id > 100000 and name not like '_WA_Sys%'

order by reserved desc

 

-- tb_sfa10 테이블 정보

테이블명   행수()      할당량(KB)   사용량(KB)   인덱스(KB)   잔량(KB)

tb_sfa10     586,489      270,408        268,704       1,672           32

 

인덱스

tb_sfa10_pk     clustered, unique, primary key located on PRIMARY     CLS_DT,  ITEM_CD,   UNT_QTY

 

칼럼길이합

select sum(length) from syscolumns where id = object_id('tb_sfa10')

524 byte

 

데이터확인

select CLS_DT, count(*) from tb_sfa10 group by CLS_DT

월별로 평균 3 건의 데이터 저장됨

 

3. 운영 진단

<전문가> 이제 유지관리와 관련해서 살펴보자. 백업이 어떻게 되고 있는지 로그를 잠깐 살펴보면, 전체 백업을 2시간마다 수행하고, 1주일간의 백업 데이터를 유지하고 있고, 로그 백업은 10 단위로 하고 있고, 2주일간의 백업 데이터를 유지하고 있다.

<신청자> 인터넷에 보니까, 전체 백업을 1시간에 한번, 10분에 한번씩 한다는 사람들이 많았다. 그래서 이렇게 자주해야 할까 하면서도, 간격을 줄였다. 개발자가 요청해서, 테이블의 데이터를 텍스트 파일 형태로 내보내기 하고 있다.

<전문가> 지금 설정된 것은, 너무 빈번하게 백업을 하고 있는 같다 또한, 백업할 때마다 DBCC CHECKDB 명령이 자동 실행되도록 설정되어 있는 상태이다. 이는 사용자 쿼리에 비해, 유지보수 작업을 위한 DBCC CHECKDB 명령을 위한 작업 부하가 오히려 많은, 배보다 배꼽이 상황이 같다.

<신청자> 처음엔 1시간에 1번씩 백업을 받았었다. 그러다 보니까 백업 용량이 디스크를 채워서, 그나마 2시간으로 늘린 것이다. 

<전문가> 불필요하게 자주 백업을 받고 있다. 전체백업을 1/1, 로그백업을 1/1시간으로 조정해도 충분할 것이다. 백업과 인덱스 재생성 작업을 점심시간에 하는 것은 이유가 있는 것인가. 보통은 야간에 작업을 하는데.

<신청자> 야간엔 사용자가 없기 때문에, 서버를 중단(Shutdown)한다. 오전에만 잠깐 사용자가 몰리기 때문에, 보통 점심 시간에 사용자가 없어서 1시간 정도씩 진행을 한다.  

개선사항 1>> 불필요하게 자주 백업을 진행하고 있으며, 백업을 수행할 때마다 DBCC CHECKDB 통해 시스템의 일관성 검사를 반복 수행하고 있어서, 현재 데이터베이스에 수행되는 쿼리 가장 많은 부하를 발생시키는 쿼리가 되고 있다. 이를 개선하기 위해 전체 백업은 1 1, 로그 백업은 1시간 간격으로 조정할 것을 권고한다.

개선사항 2>> 또한 DBCC CHECKDB 백업시마다 수행할 필요는 없다고 판단돼, 데이터베이스 유지관리 마법사의 해당 옵션은 해제할 것을 권고한다. 또한, 현재 백업본의 관리기간이 서로 다르기 때문에 1주로 통일하면 백업본을 관리하기 위한 디스크 공간을 절약할 있다.

개선사항 3>> 인덱스 재생성 작업은 사용자의 접근을 제한하기 때문에, 향후 데이터베이스의 크기가 커지거나 사용자가 늘어나면 별도의 유지관리 시간을 야간 시간으로 조정하여 수행할 것을 권고한다. 또한 tb_sfa10 테이블과 같은 대용량 테이블에 대해서는 수동 통계 업데이트 정책도 적용할 필요가 있다.

Posted by 시스템매니아
,

출처 : dbguide.net 에 올려진 SSA 성대중 책임 컨설턴트 님의 글

해결! DB D&A>> 중소기업을 위한 SQL Server 기본 모니터링 -1  

백업과 디스크 현황 점검부터 시작

도스코 영업부의 송구현 주임은 비전산 담당자지만, 약 1년 전부터 사내 입출과 관리 프로그램의 관리를 맡고 있다. 최근 성능 저하 현상이 나타나는 애플리케이션에 어떤 문제가 있는지, 기본적으로 SQL Server를 유지 관리하는 방법이 무엇인지를 몰라 답답하던 상황이다. SSA(SQL Server Academy)의 성대중 책임 컨설턴트가 직접 안산 반월공단의 도스코를 방문해 기본적인 SQL Server 설정부터 쿼리 분석, 인덱스 추가에 이르는 방법론을 제시했다.

  • 일시: 2007 5 25
  • 신청자: 도스코 영업부 송구현(song92) 주임
  • 전문가: SSA 성대중 책임 컨설턴트

 




요청사항

1. 애플리케이션 성능 점검 - 프로그램 오류인지 DB 부하 문제인지 검토(DB 문제일 경우 권고안 제시)

2. 데이터베이스 유지관리 - DB 점검 관리 항목과 방법 제안

 

연재 순서

1 시스템 진단(백업 시스템 현황, 디스크 현황)  

2 서버 점검 운영 진단  

3 쿼리 진단(악성 쿼리) 기타 논의 사항, 전문가 총평

 

                 

<전문가> 영업부 주임이신데, 어떻게 SQL 서버 관리를 맡고 있나? 

<신청자> 원래 개발자가 있었는데, 담당자가 2006년에 사내 입출고 관리 프로그램을 개발한 후에 장기간 해외 근무를 하고 있다. 가지 가이드라인이나 조언을 주긴 하는데, DBA 전산 담당자 없이 영업부 소속의 비전문가가 관리하다 보니 어려움이 크다.

<전문가> 구체적으로는 주로 어떤 문제점이 발생했나?

<신청자> 사실 가장 문제점은 지금 제대로 운영되고 있는지 조차 불안하다는 것이다. 업무 시스템이 월말 마감 외에는 오전에 1, 2시간만 주로 사용되는, 사실 그렇게 사용량이 많은 편이 아니다. 그런데도 HDD 용량을 미처 체크하지 못해서 다운된 적이 있었고, 백업을 해놓은 상태여서 복구하는데 어려움이 있었다.

<전문가> 문제가 발생했을 이제까지는 주로 어디를 통해 정보를 얻었었나?

<신청자> 인터넷이다. 네이버카페를 주로 이용했고, 최근에 DBGuide.net 알게 됐다. 하지만 정보들이 지금 우리 시스템 환경과 맞지 않고, 비전문가이다 보니 적용 또는 응용하는 것도 쉽지 않다.

 

1. 시스템 진단

1-1. 서비스팩/패치 점검

<전문가> 우선 SQL Server 운영 상황에 대한 진단을 하고, 요청했던 애플리케이션 성능 점검과 데이터베이스 유지 관리 방안을 전달하는 것이 좋을 같다. 사전에 모니터링 툴을 전달해서, 그에 대한 결과 값을 받았으나 서비스팩과 누적핫픽스가 적용되지 않은 이전 버전(2000.80.194RTM)이어서 모니터링 툴이 제대로 값을 산출하지 못했다. 어차피 DB 관리의 기본 작업이진단 있기 때문에, 우선 진단 과정부터 함께 천천히 살펴보는 것이 좋겠다.

<신청자> 성능에 문제가 있어서 얼마 전에 윈도우 환경을 포맷한 , 패치와 서비스팩 업데이트를 잊고 있었다. 그래도 어제 패치를 업데이트하고, 서비스팩도 SP4(2039) 버전으로 업그레이드했다.

개선 사항>> 최근 Rollup 패키지인 2187 버전의 HotFix 추가로 설치해야 하는 상태이며, 아래의 주소를 참조해 다운로드 받아서 설치할 있다.

http://www.microsoft.com/downloads/details.aspx?displaylang=ko&FamilyID=9c9ab140-bdee-44df-b7a3-e6849297754a

--확인방법

select @@version

--실행결과

Microsoft SQL Server 2000 - 8.00.2039(Intel X86)  May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack )

*<주의> 서비스팩의 적용 HotFix 적용은 기존 애플리케이션의 동작에 영향을 미칠 있기 때문에 운영환경에 적용하기 전에 반드시 테스트 환경에서 테스트되어야 한다.

 

1-2. 백업 시스템 현황 점검

<전문가> 그럼 같이 상황 점검을 해보자. 백업은 어떻게 하고 있는지. 유지관리 메뉴에 보면, 전체 백업과 트랜잭션 로그 백업이라는 2가지 옵션이 있는데 어떻게 사용하고 있나.

<신청자> 테이블 백업을 텍스트로 하고 있다. 개발자가 원하는 사항이다. 그리고 정확한 주기는 설정한지 오래 되어서, 기억이 나지만 전체 백업과 트랜잭션 로그 백업 모두 시행하고 있다.

<전문가> 그럼 복구 모델은 전체 복구 모델을 사용하고 있는 상태이고, 사용 환경은 하이퍼쓰레딩이 가능한 CPU 2개로, 논리적으로는 4개로 있고, 메모리는 1GB. 여유공간이 몇백MB 있는 것으로 봐서 메모리가 부족한 상태는 아니다.

개선사항>> 향후 시스템의 리소스가 부족하게 되면 메모리는 추가할 필요가 있는 상태라고 판단된다. 참고로, 현재 SQL Server 2000 Standard Edition 사용하고 있기 때문에 SQL Server 최대 2GB까지 메모리를 할당할 있다.

--확인방법

Exec master..xp_msver

--실행결과

1               ProductName          NULL       Microsoft SQL Server

2               ProductVersion        524288    8.00.2039

3               Language                 1042        한국어

4               Platform  NULL       NT INTEL X86

5               Comments               NULL       NT INTEL X86

6               CompanyName      NULL       Microsoft Corporation

7               FileDescription        NULL       SQL Server Windows NT

8               FileVersion               NULL       2000.080.2039.00

9               InternalName          NULL       SQLSERVR

10             LegalCopyright       NULL       1988-2004 Microsoft Corp. All rights reserved.

11             LegalTrademarks   NULL       Microsoft?? is a registered trademark of Microsoft Corporation.

12             OriginalFilename    NULL       SQLSERVR.EXE

13             PrivateBuild             NULL       NULL

14             SpecialBuild             133627904              NULL

15             WindowsVersion     248381957              5.2 (3790)

16             ProcessorCount      4               4

17             ProcessorActiveMask              15             0000000f

18             ProcessorType        586           PROCESSOR_INTEL_PENTIUM

19             PhysicalMemory     1023        1023 (1073164288)

20             Product ID                NULL       NULL

 

1-3. 디스크 운영 환경 점검

<전문가> 그러면 하드디스크는 어떻게 사용하고 있는지.

<신청자> 디스크 서브시스템은 현재 RAID5 구성된 C,D 드라이브와, 최근에 공간 부족 문제를 해결하기 위해 추가한 E,F 드라이브로 구성되어 있다. 현재 C 드라이브에 데이터베이스 파일 로그 파일이 저장되어 있으며, D 드라이브에는 스크립트 기반으로 테이블을 텍스트 파일로 내보내기 파일을 저장하고, E 드라이브에는 백업 파일을, F 드라이브에는 기타 관련 파일을 저장하고 있다

<전문가> 기본적으로 디스크는 OS 위한 공간, 데이터베이스 파일을 위한 공간, 로그 파일을 위한 공간, 백업 파일을 위한 공간으로 분리해서 사용하는 것이 일반적인 권고사항이다. 가능하다면, 물리적으로 분리된 디스크 상에 위치하는 것이 바람직하며, 특히 데이터베이스 파일 로그 파일과 백업파일은 별도의 디스크에 위치시키는 것이 좋다. 왜냐하면, 디스크 장애발생시에 데이터베이스 관련 파일과 백업 파일이 같은 디스크상에 존재하면, 복구할 없기 때문이다. 현재로서는 사용량이 많지 않아서 괜찮을 있지만, 이후 점검 후에 디스크 용량 조정이 필요하다면 조치를 취해야 같다.

개선사항>> 현재로서는 사용량이 많지 않기 때문에, 특별한 조치가 필요한 상태는 아니다. 하지만 현재 시스템 볼륨(운영체제가 설치된 볼륨) C 드라이브에 데이터베이스 파일과 로그 파일이 함께 저장되어 있다. 이를 별도의 드라이브로 분산시키면 디스크의 병목현상을 해결할 있다. 또한 가능하다면, 데이터베이스 파일과 로그 파일도 분리하는 것이 바람직하다.

--확인방법

exec master..xp_fixeddrives

--실행결과

C               20266 - Data/Log

D               26685

E               17623 - Backup

F               4570


Posted by 시스템매니아
,
데이터베이스의 Collation 정보를 말합니다.

MSSQL DBMS 는 WINDOWS OS 와 상호작용을 하여 처리하는 경우가 많이 있습니다.

Collaction 정보역시 마찬가지 인데요 이것은, SQL Server의 기본 데이터 정렬을 설정하는 옵션 입니다. MSSQL 을 설치할 때 대부분 Korean_Wansung_CI_AS 으로 설정을 하게 됩니다. 이것의 의미는 "한국어_완성형 문자" 라는 의미 입니다.

테이블에 데이타를 입력할경우, 특정칼럼 기준으로 정렬할경우 완성형으로 비교 판단 하게 되겠지요.

SELECT * FROM ::fn_helpcollations()
GO

쿼리분석기에서 위의 명령을 내리면, MSSQL에서 지원하는 Collaction 정보들이 나오게 됩니다.

Collaction 정보를 변경하고자 한다면,

ALTER DATABASE CollateDb
COLLATE Korean_Wansung_CI_AI
GO

이렇게 하시면 됩니다.
Posted by 시스템매니아
,
MS SQL 2000 에서

사용자 로그인 이름이 없어진 경우

사용자는 존재하는데, 로그인 이름 정보가 나타나질 않는 현상

( 데이터 베이스를 mdf 파일을 가져와서 연결한 경우 위와 같은 현상이 나타나기도 함 )

다음의 시스템 프로시져를 실행해준다.

sp_change_users_login 'UPDATE_ONE','업데이트 하고자 하는 사용자이름','로그인이름'
Posted by 시스템매니아
,
이전 소유자와 새 소유자를 넘겨주면 이전 소유자의 개체들을 찾아 자동으로 새 소유자로 변경해 줍니다.

/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This stored procedure can be used to run through all of a specific 
database's objects owned by the 'oldowner' and change the old
owner with the new one.
You should pass the old owner name and the new owner name, 
as in the example below:

EXEC ChangeAllObjOwner @oldowner = 'John', @newowner = 'Alex'
*/

IF OBJECT_ID('ChangeAllObjOwner') IS NOT NULL DROP PROC ChangeAllObjOwner
GO

CREATE PROCEDURE ChangeAllObjOwner (
  @oldowner sysname,
  @newowner sysname
)
AS
DECLARE @objname sysname
SET NOCOUNT ON

--check that the @oldowner exists in the database
IF USER_ID(@oldowner) IS NULL
  BEGIN
    RAISERROR ('The @oldowner passed does not exist in the database', 16, 1)
    RETURN
  END
--check that the @newowner exists in the database
IF USER_ID(@newowner) IS NULL
  BEGIN
    RAISERROR ('The @newowner passed does not exist in the database', 16, 1)
    RETURN
  END

DECLARE owner_cursor CURSOR FOR 
  SELECT name FROM sysobjects WHERE uid = USER_ID(@oldowner)

OPEN owner_cursor
FETCH NEXT FROM owner_cursor INTO @objname
WHILE (@@fetch_status <> -1)
BEGIN
  SET @objname = @oldowner + '.' + @objname
  EXEC sp_changeobjectowner @objname, @newowner
  FETCH NEXT FROM owner_cursor INTO @objname
END

CLOSE owner_cursor
DEALLOCATE owner_cursor
GO
Posted by 시스템매니아
,
MS-SQL서버의 성능을 모니터링할수 있는 각 성능카운터에대한 설명입니다.

Processor\%Processor Time : % Processor Time은 프로세서가 비유휴 스레드를 실행하는 데 소비하는 시간의 백분율입니다. 이것은 프로세서가 각 샘플 간격 동안 유휴 스레드를 실행하는 데 소비한 시간을 측정하여 간격 기간에서 그 값을 뺀 것입니다. 각 프로세서에는 유휴 스레드가 있는데 이것은 다른 어떤 스레드도 실행되지 않을 때 사이클을 소비하는 스레드입니다. 이 카운터는 프로세서 동작의 주요 표시기이며 샘플 간격 동안 관찰되는 사용 시간의 평균 백분율을 표시합니다. 이것은 서비스가 비활성인 시간을 모니터링하여 100%에서 그 값을 뺀 것입니다.
즉 쉽게 말해서, 1시간 동안 CPU상태를 관찰하였고, 1시간 중에서 30분간 CPU가 일을 하였다면, %Processor Time은 50% 입니다. 일반적으로 %Processor Time이 80%이상 사용되고 있으면, 관심을 가지고 사용량의 변화를 모니터 하셔야 합니다.

Processor\%Privileged Time : % Privildged Time은 프로세스 스레드가 특권 모드에서 명령을 실행하면서 경과된 시간을 백분율로 표시한 것입니다. Windows 시스템 서비스가 호출되면, 서비스는 시스템 전용 데이터를 액세스하기 위해 흔히 특권 모드에서 실행됩니다. 그러한 데이터는 사용자 모드에서 실행되는 스레드가 액세스하지 못하도록 보호됩니다. 시스템 호출은 페이지 오류 또는 인터럽트가 발생할 때처럼 명시적이거나 암시적입니다. 일부 초기 운영 체제와는 달리 Windows는 사용자 및 특권 모드의 일반적인 보호뿐만 아니라 하위 시스템을 보호하기 위해 프로세스 경계를 사용합니다. 응용 프로그램을 대신하여 Windows에서 수행한 일부 작업은 프로세스의 특권 시간 및 다른 하위시스템 프로세스에서도 나타납니다. 쉽게 말해서, 운영체제가 사용한 시간의 백분율 값입니다.

Memory\Available Bytes : Available Bytes는 컴퓨터에서 실행되는 프로세스에 사용할 수 있는 실제 메모리의 양(바이트)입니다. 이것은 0으로 채워 있거나 비어 있거나 대기 중인 메모리 목록에 있는 공간을 합해 계산합니다. 빈 메모리는 사용할 준비가 된 메모리이고, 0으로 채워진 메모리는 다음 프로세스가 이전 프로세스에서 사용된 데이터를 보지 못하도록 0으로 채운 메모리 페이지로 구성되며, 대기 메모리는 프로세스의 작업 집합(실제 메모리)에서 제거되어 디스크로 라우트되었지만 다시 호출되어 사용될 수 있는 메모리를 말합니다. 이 카운터는 최근에 관찰된 값만 표시하며 평균값은 아닙니다. 사용 가능한 여유 메모리는 많이 있으면 좋겠지만, 그렇지 않다면, 최소한 5Mbyte이상은 남아 있어야 합니다.

Memory\Page Faults/sec : Page Faults/sec는 프로세스가 사용하는 메모리 공간(Working Set)에 존재하지 않는 코드나, 데이터를 요청할 경우에 발생합니다. 이 때 요청된 코드나 데이터를 다른 메모리 공간에서 찾으면 Soft Page Fault라고 하며, 디스크에서 찾게되면 Hard Page Fault라고 합니다. Page Faults/sec은 초당 발생한 Soft Page Fault와 Hard Page Fault의 합입니다.
Soft Page Fault값은 Page Faults/sec값에서 Pages/sec값을 빼면 됩니다.
따라서,  Page Faults/sec의 값은 작을수록 좋다고 말 할 수 있겠으며, 상황에 따라서 그 임계치는 다르므로, 평상시에 모니터하여 적정 값을 파악해 두셔야 합니다.

Memory\Pages/sec : Pages/sec는 하드 페이지 부재를 해결하기 위해 디스크에서 읽거나 디스크로 쓴 페이지의 비율입니다. 이것은 Memory\\Pages Input/sec과 Memory\\Pages Output/sec의 합입니다. 메모리 공간이 부족하게 되면, 디스크의 페이징 파일로 메모리의 내용을 옮겨서 메모리의 여유공간을 확보하여 사용하게 되며, 또 필요시 페이징 파일에서 데이터를 메모리로 로드하여 처리하는 과정을 반복하게 되므로 성능이 저하되게 됩니다.
Pages/sec 카운터 값이 20이상 지속되면 메모리 이상을 의심해야 합니다.

PhysicalDisk\%Disk Read Time : 디스크가 읽기 작업을 수행한 시간의 백분율입니다.

PhysicalDisk\%Disk Write Time : 디스크가 쓰기 작업을 수행한 시간의 백분율입니다.

PhysicalDisk\%Disk Time : 디스크가 읽기 및 쓰기 작업을 수행한 시간의 백분율이며, 이 값은 Disk Read Time과 Disk Write Time의 합입니다.

PhysicalDisk\Avg. Disk Queue Length : 디스크의 읽기 및 쓰기 작업을 위해 대기중인 실제 디스크 큐 길이 입니다. 이 값은 디스크당 2를 초과하게 되면, 디스크 쪽 부하를 점검해야 합니다.

PhysicalDisk\Current Disk Queue Length : 현재 시점의 디스크 읽기 및 쓰기 작업을 위해 대기중인 디스크 큐 길이 입니다. 이 값 역시 2를 초과하면, 디스크 쪽 부하를 점검해야 합니다.

Network Interface\Current Bandwidth : 네트워크 인터페이스의 현재 대역폭입니다. 사용하는 네트워크 어댑터 카드의 지원 가능 대역이 100Mbps인 경우에 Current Bandwidth값이 10Mbps라면 네트워크 어댑터 카드의 속성 세팅이 잘 못 되었을 가능성이 큽니다.

Network Interface\Packets Outbound Errors : 이 항목은 오류로 인해서 외부로 반출할 수 없는 패킷의 수를 나타냅니다.

Network Interface\Packets Received Errors : 이 항목은 상위 계층의 프로토콜로 전달되지 못하도록 하는 오류를 포함하고 있는 외부로부터 유입되는 패킷의 수를 나타냅니다.

Server\Bytes Total/sec : 이 항목은 초당 서버가 네트워크에서 주고 받은 바이트 수 입니다. 동일 네트워크에 존재 하는 서버들의 각각의 Bytes Total/sec 합이 네트워크 대역폭보다 크다면, 네트워크 분리를 고려하셔야 합니다.

SQLServer:Buffer Manager\Buffer cache hit ratio : SQL서버가 데이터를 디스크에서 읽지 않고 버퍼 풀에서 찾은 페이지 비율입니다. 이 값은 90% 이상 유지되어야 하며, 대부분의 시스템에서  98% 정도 유지 됩니다. 이 값이 90보다 낮다면 버퍼 공간이 부족하다고 판단할 수 있습니다.

SQLServer:Buffer Manager\Page life expectancy : 이 항목은 페이지가 참조되지 않고 얼마나 오랫동안 버퍼에 존재할 수 있는가를 초 단위로 나타냅니다. 이 값이 300초 이하이면, 성능향상을 위해  SQL서버에 추가적인 버퍼 공간이 필요하다고 볼 수 있습니다.

SQLServer:Buffer Manager\Page reads/sec : 모든 데이터베이스에 대해서 발생한 물리적 데이터 페이지의 초당 읽기 수를 나타냅니다. 물리적 I/O는 상대적으로 비용이 많이 발생하므로, 더 큰 데이터 캐시를 사용하거나, 인덱스 및 쿼리를 효율적으로 작성하거나, 데이터베이스 모델링을 다시하여 물리적 I/O비용을 줄여야 합니다.

SQLServer:Buffer Manager\Stolen Page : 윈도우 시스템이 SQL서버가 아닌 다른 응용 프로그램의 요구를 충족시키기 위하여 얼마나 많은 페이지들이 SQL Server 데이터 캐시로부터 제거되었는지를 나타낸다. min Server memory를 지정하여 SQL서버가 지정한 만큼의 SQL서버 전용으로 메모리를 할당하게 할 수 있지만, 그 만큼 다른 프로그램이 적은 메모리로 운영되게 되므로 페이징이 발생하게 됩니다. 따라서 메모리 증설을 고려해야 합니다.

SQLServer:Databases\Log Flushes/sec : 초당 발생한 로그 플러시 수를 나타냅니다. 하나의 로그플러시는 하나의 트랜잭션이 커밋되어 로그 파일에 기록되는 것을 의미 합니다.

SQLServer:Databases\Transactions/sec : 선택한 데이터 베이스에서 발생한 초당 발생하는 트랜잭션 수를 나타냅니다.

SQLServer:General Statistics\User Connections : 시스템에 연결된 사용자 수를 나타냅니다.

SQL Server:Latches\Average Latch Wait Time(ms) : 요청된 래치에 대한 평균 래치 대기 시간(ms)

SQL Server:Latches\Latch Waits/sec : 즉시 서비스 될 수 없고 자원 해제를 위해 대기해야 하는 래치 요청 수

SQL Server: Locks\Average Wait Time(ms) : 리소스에 잠금을 걸기 위해  대기하였던 평균 시간(ms)을 나타냅니다.

SQL Server: Locks\Lock Timeouts/sec : 리소스에 잠금을 얻기 위해 대기하면서 타임 아웃된 잠금 요청 횟수를 나타냅니다.

SQL Server: Locks\Lock Waits/sec : 즉시 충족시킬 수 없고 잠금을 허가하기 위해서 호출자가 기다려야 하는 잠금 요청 수

SQL Server: Locks\Number of Deadlocks/sec : 교착 상태를 일으킨 잠금 요청 수

SQL Server: Memory Manager\Memory Grants Pending : 작업 영역 메모리 허가를 위해 대기하고 있는 현재의 프로세스 수

SQL Server: Memory Manager\Target Server Memory(KB) : SQL Server가 사용할 수 있는 전체 메모리 양

SQL Server: Memory Manager\Total Server Memory(KB) : SQL Server가 사용하고 있는 전체 메모리 양

SQL Server: SQL Statistics\Batch Requests/sec : 초당 요청 받은 SQL 배치 요청 수

SQL Server: SQL Statistics\SQL Compilations/sec : 초당 SQL Server가 SQL문을 컴파일 한 수

SQL Server: SQL Statistics\Re-Compilations/sec : 초당 SQL Server가 SQL문을 재 컴파일 한 수
Posted by 시스템매니아
,

1. 네트워크
네트워크 인터페이스의 추가
서브네트워킹 작업
네트워크 업그레이드
클러스터링방안 및 기가모듈의 사용
 
2. CPU
성능모니터링을 이용한 CPU 모니터링
% Processor Time  - 80%  이하가 정상
% Privileged Time  - 80%  이하가 정상
% User Time   - 80%  이하가 정상
Processor Queue Length - 2 이하가 정상
대책 : 정상수치가 아닐 때 해결방법
     프로세서  추가, 더빠른  프로세서로  교체, 보다많은  캐쉬 확보

3. 메모리
성능모니터링을 이용한 메모리 모니터링
Available Bytes   - 4 메가  이상이 정상
Pages/sec (paging in/out)         - 0~20일 때 정상
Page Reads/sec   - 5  이하가 정상
Page Writes/sec   - 5  이하가 정상
Buffer Cache Hit Ratio  - 90%  이상이 정상
대책 : 정상수치가 아닐 때 해결방법
메모리  추가

4. 디스크
성능모니터링을 이용한 디스크 모니터링
검사항목
% Disk Read Time  - 40%  이하가 정상
% Disk Write Time  - 40%  이하가 정상
% Disk Time   - 40%  이하가 정상
Avg. Disk Queue Length - 2 이하가 정상
대책 : 정상수치가 아닐 때 해결방법
디스크  추가 , RAID  고려

Posted by 시스템매니아
,
MS-SQL2000에서 TEST됨

쿼리분석기 에서 아래 명령줄 실행

sp_password NULL, '바꿀암호', 'sa'
Posted by 시스템매니아
,