/re:how to be a dolphin?
Posts tagged ‘MSSQL’
1. 일일이 카운트 한다 – 정확도 100%
2. System table 에 저장되어있는 row count 를 참조 한다 – 정확도 1번과 비교하면 약간 떨어짐
물론 정확해야한다면 1번으로..
다만.. 데이터의 증감 또는 분위기 파악 정도로 할 때에 테이블 한 두개면 모를까.. 많으면 1번의 경우 토나오는 작업이 되는 것이 현실..
그래서 2번을 사용하는 경우가 있는데.. 이 때 사용하는 쿼리
——————————————
select B.name, A.rowcnt
from sysindexes AS A with (nolock)
JOIN sys.tables as B with (nolock)
ON A.id = B.[object_id] and A.indid < 2
order by [name] asc
——————————————
시스템 테이블에 저장되어있는 row count와 table 이름을 조합하여 테이블의 row 갯수를 반환 받을 수 있음..
가끔은 편리함 🙂
1. Cal
먼저.. 지금 일을 하고 계신 분들께서 보시면 좀 까칠 하게 생각 하실 수 있습니다
그리고 완전 개인적인 생각이니 동의 안하시면 그냥 1박 2일 보세요 ㅋ 그게 더 즐거워요 ㅎ
IT로 돈 벌고 나름 잘 살아남기 위한 방법
1. 지금이라도 늦지 않았습니다. Java를 하십시오
이유 – 지금 MS 기술의 그늘에서 월급 받는 중이라면 상관이 없지만 만약에 지금 시작을 하려는 분이라면.. Java를 하십시오.. 일반 소비자 시장에서 잘 먹히는 C++ 및 각종 MS기술보다 대규모 기업시장에서 많이 쓰이는 Java가 내 주머니에 돈을 가져다줄 확률이 더 높습니다.. 특히 외국에 나갈 생각이 있다라면 Java쪽이 좀 더 유리 할 수 있습니다
2. Web 관련 언어를 하나 정도는 익혀 두는 편이 좋을 수 있습니다
이유 – 전통적인 어플리케이션의 위치가 나름 많이 줄어드는 것 같습니다. 결국 프로그램이 하는 일은 DB에 값을 넣고 저장되어있는 DB의 값을 불러와서 원하는 모양새로 보여주는 것이 대부분인데.. 유지보수 및 관리가 나름 쉬운 웹 기반으로 제작이 되는 경우가 많으니 완전 까막눈만 아닐 정도로는 알아두면 나중에 대처하기가 쉬울듯.. 사실 Java나 C++를 공부 했으면 php에 접근을 하기 매우 쉬울 꺼고 그냥 쉽게 적응 할 수도 있을테니 완전 배척만 하지 말아주세요 🙂
3. DB를 다룰 줄 알면 편합니다
이유 – 한국에서는 개발자가 DB설계도 하고 쿼리도 짜야하고 알아서 다 해야하는 경우가 있으니 더욱 더 -_-;;
나중에 뻑나는건 나중일이 되는 경우가 많으니 일단은 지금 할 줄 아는 것이 중요하고.. DB의 중요성은 어디에서 누구에게 물어봐도 다 인정 할 껍니다.. 물론 DBA가 있다면 DBA를 더욱 괴롭히세요 🙂
4. 소프트웨어 개발을 포기 하세요
이유 – 그게 어찌보면 가장 나은 빠른 방법일 가능성도 있습니다
대안으로 공부를 해야 할 것은 네트워크, 보안 그리고 하드웨어 입니다
네트워크라는 것이 사실 매우 간단한데 프로그램의 어셈블리 같은 느낌이 강해서 접근을 하는 방법이 어렵지 개념이 잡혀 있으면 슬슬 따라오는 경우가 많습니다.. 다 따라오지 않는 것이 문제가 되기는 하지요 -ㅂ-;; 단점은 네트워크라는 것이 언제 장애가 발생을 할 지 모르기 때문에 24/7 대기를 해야하는 경우가 많이 생긴다라는 것..
하드웨어의 경우는 아무리 인터넷이 발전을 하고 컴퓨터가 발전을 해도 결국 어디에선가 서버라는 녀석이 보내주는 데이터에 의존을 해야하고 그 서버를 담당하는 자원이 생각보다는 모자라기에 서버 쪽의 하드웨어 (단순 서버 및 스토리지 포함) 쪽에 대한 이해도가 높다면 매우 큰 도움이 될 것입니다
그리고.. 가장 중요한…
뭔가 똥꼬가 찌릿찌릿하고 머리 속에서 떠나지 않는다라면 잡으세요..
다만 똥인지 된장인지 가려가면서 먹어야합니다 -_-;;
이게 참 어려운데.. 먹어보기 전에는 모르는 경우가 많기에.. 일단 간을 보고 제대로 씹기전에 삼킬지 뱉을지를 잘 결정해야 합니다..
돈 몇 푼 따라 갔다가 그거에 목 매달고 살게 될 수도 있으니.. 하고 싶은거 정해서 그거 잘 밀고 나가면 됩니다 🙂
DB서버 두 대가 있음
Linked server 로 묶어도 놨음
다른 쪽 서버의 데이터를 불러오긴 해야함..
select *
from SERVER.DB.SCHEMA.TABLE
vs
select *
from OPENQUERY(SERVER, ‘SELECT * from DB.SCHEMA.TABLE’)
지금까지의 내가 느낀 것으론 승자는 OPENQUERY
이 기종에서도 동일 기종 동일 네트워크에서도 속도 차이는 좀 많이 나는 편..
특별한 상황이 아닌 이상 OPENQUERY를 더 많이 쓰게 되었다라는..
문제는 작성하는게 귀찮다라는거 -ㅂ-;;
이를 해결 하기 위해서는 모든 서버들은 도메인으로 – 액티브디렉토리를 사용하여 도메인 구조로 묶게 되면 해결이 되지만 DBMS하나 깔아서 쓰는 것도 귀찮고 힘들어 죽겠는데 액티브 디렉토리까지 신경쓰기 싫을 경우에 나름의 꼼수로 ㅎ 쓸 수 있는 방법..
나도 IDC에 액티브 디렉토리의 설정을 들여놓긴 싫어서 같은 방법을 쓰고 있으며 뭐 잘 돌아가니 🙂
OS 는 Windows server 2003 이상이면 되고 DBMS는 SQL 2005 이상이면 동작한다
다만 SQL 2005의 서비스팩은 반드시 적용하시길~ SQL 2005의 경우 현재(2009/01/30) SP3 까지 나와있으니 download 페이지에서 받으시면 준비는 끝날듯~
미러링을 위해서는 총 세개의 인스턴스가 설치가 되어있으면 되며 물리적으로 분리가 되어있어도 되며 그게 아니라면 추가 인스턴스로 되어있어도 동작하는데는 문제가 없음..
이제 작업 리스트
내 경우에는 총 3대의 서버에서 진행을 했으며 마스터DB와 미러DB는 SQL 2005 Standard edition 그리고 감시용으로는 SQL 2005 express edition을 사용했음
1. Host file의 수정
172.10.10.10 db1.database.server.hugyou.net
172.10.10.11 db2.database.server.hugyou.net
172.10.10.12 db3.database.server.hugyou.net
뒤에 도메인 네임은 쓰고 싶으신 걸로 🙂 dbserver1.google.com 이라고 해도 상관없음 -_-;;
2. Full backup & Transaction log backup
마스터 DB에서 하나의 Full backup 그리고 그 이후에 생성된 Transaction log backup 을 생성한 후 해당 백업 파일을 미러DB에 복사해놓음
3. 2번에서 생성된 백업 파일을 복원
복원을 할 때 with nocovery 옵션으로 DB를 열어놓은 상태로 만들어 놓는다
4. DB서버 별 인증서 파일 생성
— 마스터DB
create master key encryption by password = ‘마스터DB 비밀번호’;
GO
create certificate DATABASE_A_Cert with subject = ‘DATABASE A Certificate’, start_date = ‘2009/01/01’, expiry_date = ‘2020/12/31’;
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7123, listener_ip = all)
for database_mirroring (authentication = certificate DATABASE_A_Cert, encryption = REQUIRED, role = all);
GO
Backup certificate DATABASE_A_Cert to file = ‘c:\DATABASE_A_Cert.cer’;
GO
— 미러DB
create master key encryption by password = ‘미러DB 비밀번호’;
GO
create certificate DATABASE_B_Cert with subject = ‘DATABASE B Certificate’, start_date = ‘2009/01/01’, expiry_date = ‘2020/12/31’;
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7123, listener_ip = all)
for database_mirroring (authentication = certificate DATABASE_B_Cert, encryption = REQUIRED, role = all);
GO
Backup certificate DATABASE_B_Cert to file = ‘c:\DATABASE_B_Cert.cer’;
GO
— 감시DB
create master key encryption by password = ‘감시DB 비밀번호’;
GO
create certificate DATABASE_C_Cert with subject = ‘HOST_W certificate’, start_date = ‘2009/01/01’, expiry_date = ‘2020/12/31’;
GO
Create endpoint endpoint_mirroring state = started
as tcp(listener_port = 7123, listener_ip = all)
for database_mirroring (authentication = certificate DATABASE_C_Cert, encryption = REQUIRED, role = witness);
GO
Backup certificate DATABASE_C_Cert to file = ‘c:\DATABASE_C_Cert.cer’;
GO
인증서 생성시 유효 기간은 알아서 적당하게 진행하면 되며 내 경우에는 다시 설정하기 귀찮으니–;; 10년정도 뒤로 -ㅅ-;; 설마 2020년까지 이 시스템을 사용하지는 않겠지 ㅎ
5. 생성된 인증서 파일을 각 서버에 전부 카피 한다
서버는 다른 두 서버에서 생성된 인증서를 가지고 있어야 함
6. 로그인 계정 생성 및 Endpoint 설정 – 가장 긴 부분 🙂
— 마스터DB
create login DATABASE_B_login with PASSWORD = ‘미러DB 로그인 암호’;
GO
create user DATABASE_B_user from login DATABASE_B_login;
GO
Create certificate DATABASE_B_Cert
Authorization DATABASE_B_user
From file = ‘c:\DATABASE_B_Cert.cer’;
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [DATABASE_B_login];
GO
——
create login DATABASE_C_login with PASSWORD = ‘감시DB 로그인 암호’;
GO
create user DATABASE_C_user from login DATABASE_C_login;
GO
Create certificate DATABASE_C_Cert
Authorization DATABASE_C_user
From file = ‘c:\DATABASE_C_Cert.cer’;
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [DATABASE_C_login];
GO
— 미러DB
create login DATABASE_A_login with PASSWORD = ‘마스터DB 로그인 암호’;
GO
create user DATABASE_A_user from login DATABASE_A_login;
GO
Create certificate DATABASE_A_Cert
Authorization DATABASE_A_user
From file = ‘c:\DATABASE_A_Cert.cer’;
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [DATABASE_A_login];
GO
——-
create login DATABASE_C_login with PASSWORD = ‘감시DB 로그인 암호’;
GO
create user DATABASE_C_user from login DATABASE_C_login;
GO
Create certificate DATABASE_C_Cert
Authorization DATABASE_C_user
From file = ‘c:\DATABASE_C_Cert.cer’;
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [DATABASE_C_login];
GO
— 감시DB
create login DATABASE_A_login with PASSWORD = ‘마스터DB 로그인 암호’;
GO
create user DATABASE_A_user from login DATABASE_A_login;
GO
Create certificate DATABASE_A_Cert
Authorization DATABASE_A_user
From file = ‘c:\DATABASE_A_Cert.cer’;
GO
Grant CONNECT ON Endpoint::Endpoint_mirroring to [DATABASE_A_login];
GO
——-
create login DATABASE_B_login with PASSWORD = ‘미러DB 로그인 암호’;
GO
create user DATABASE_B_user from login DATABASE_B_login;
GO
Create certificate DATABASE_B_Cert
Authorization DATABASE_B_user
From file = ‘c:\DATABASE_B_Cert.cer’;
GO
Grant CONNECT ON Endpoint::endpoint_mirroring to [DATABASE_B_login];
GO
7. 미러링 설정 하기
— 미러DB
alter database [DB이름] set partner = ‘TCP://db1.database.hugyou.net:7123’;
GO
— 마스터DB
alter database [DB이름] set partner = ‘TCP://db2.database.hugyou.net:7123’;
GO
alter database [DB이름] set witness = ‘TCP://db3.database.hugyou.net:7123’;
GO
이걸로 미러링 끝~ 🙂 뭐 오타 있을 수 있지만 그건 그 때 알아서 고치면 될 것 같고;;
이제 도메인 걱정없는 미러링을 즐겨봅시다~
마스터가 죽으면 자동으로 미러로 서비스가 옮겨지지만 ip 주소가 다를 경우 어플리케이션 들은 DB가 변경이 되었는지 알길이 없으니 연결을 시도해보고 마스터가 죽었으면 미러로 접속을 하는 설정을 해놓으면 마스터DB의 시간을 벌 수 있을듯~
오늘 간만에 한번 실무에서 써봤으니 까먹지 않게 정리 한판..
MSSQL 2005에서 지원되는 dbmail을 사용하기 위한 방법 을 그냥 나열
일단 준비단계
——————————————————
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘계정이름 – hugyou’,
@description = ‘계정설명 – 전지전능하지 못한 hugyou’,
@email_address = ‘메일주소 – [email protected]’,
@display_name = ‘보여질 이름 – 메일 받을때 발신인에 찍힐 이름’,
@username=’메일주소 – [email protected] or other’,
@password=’비밀번호 – 비번’,
@mailserver_name = ‘SMTP 서버 주소 – smtp.smtp.com’
go
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘계정이름’,
@description = ‘Profile used for 계정이름’
go
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘계정이름’,
@account_name = ‘계정이름’,
@sequence_number = 1
go
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘계정이름’,
@principal_name = ‘public’,
@is_default = 1 ;
go
——————————————————
이제 보내봅시다
——————————————————
declare @body1 varchar(100)
set @body1 = ‘서버 :’[email protected]@servername+ ‘ 첫 메일!’
EXEC msdb.dbo.sp_send_dbmail @recipients=’보낼메일 주소 [email protected] 등등..’,
@profile_name = ‘계정이름’,
@subject = ‘메일 테스트’,
@body = @body1,
@body_format = ‘HTML’ ;
——————————————————
다른 옵션은 books online의 sp_send_dbmail 을 찾아보시면 쿼리 결과를 그대로 보내는 법 등이 나와있음 입니다 🙂
——————————————————
다음에 또 치기 싫으니-_-;;
올려놔야지.. ㅋ
select substring(email, (charindex(‘@’, email)+1), (len(email)-charindex(‘@’, email)+1)) as ‘mail providers’,
count([id]) as ‘회원수’
from [회원DB] with (NOLOCK)
group by substring(email, (charindex(‘@’, email)+1), (len(email)-charindex(‘@’, email)+1))
이걸로 큰 귀찮음을 덜 수 있다면 다행!
조건절 등에서 collation 틀려서 에러 날 경우 🙂
가끔 나오는데 그게 매우 아쉬우니 적어놔야지..
select 가져올 것
from 테이블 as A
Join 테이블 AS B on A.컬럼 = B.컬럼 COLLATE 각종 collation설정들..
where 각종 조건들..
전체를 바꿀 수 없다면 잠시만 바꿔줍시다~ 🙂
물론 확인 잘 하고나서~