메뉴 닫기

Linux On MSSQL 설치 및 테스트

Linux On MSSQL 설치 및 테스트 

 

  1. 테스트 환경

   Cloudv MAX IO LINUX

   http://www.cloudv.kr/rew1/cloud/MAX_IO_Linux.html

 

   O S : Ubuntu 16.04 64Bit

 

   CPU: 1Core

   RAM : 4G (MSSQL 리눅스 버전은 최소 4G 이상에서 설치가능)

 

  1. MSSQL ON Linux 설치

1) Import the public repository GPG keys

 

# curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

# curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

 

2) 패키지 업데이트 및 설치

# apt-get update

# apt-get install -y mssql-server

 

3) SQL 설정

# /opt/mssql/bin/sqlservr-setup

– 라이센스 동의여부 : yes

– administrator(SA) password 입력

– SQL 서비스 시작 y/n : y

– SQL 서비스 부팅시 시작 y/n : y

 

  1. SQL 접속 방법 

  – 로컬 접속 방법

sqlcmd -S localhost -U SA -P ‘설정한 패스워드’

1> CREATE DATABASE smileserv;

2> go

1> SELECT Name from sys.Databases;

2> go

——————————————————————————————————————————–

master                                                                                                                          

tempdb                                                                                                                          

model                                                                                                                           

msdb                                                                                                                            

smileserv                                                                                                                       

 

(5 rows affected)

# smileserv 데이터 베이스가 생성되었다

 

  – 외부 MSSQL 2008 R2 매니지먼트 스튜디오에서 접근

mssql4

mssql3

 

 

 

  –  원격 MSSQL 매니지먼트 스튜디오 상에서 생성 및 삭제는 아직까지 

불가능 하며, SELECT 부분만 가능함 

(추후 새로운 버전이 나오면 모든 기능 가능할듯 보임)

 

  1. SQL On Linux VS MSSQL 2008 R2데이터 INSERT 비교

 

 1) MSSQL 2008 R2 사양 

Intel E3-1230 v2 3.3Ghz

16GB

SATA3 SSD 250G

※ 리눅스 사양은 1번 테스트 환경 참고

※ MSSQL 성능 측정을 할수 있는 Tool이 없는것 같아서 구글 검색을 통해서

  참고한 쿼리 입니다

2) 테이블 생성

use smileserv

go

CREATE TABLE InvoiceDetail(

        LegacyId nvarchar(50) NULL,

        InvoiceId nvarchar(50) NULL,

        DetailTypeId nvarchar(50) NULL,

        Fee nvarchar(50) NULL,

        FeeTax nvarchar(50) NULL,

        Investigatorid nvarchar(50) NULL,

        SalespersonId nvarchar(50) NULL,

        CreateDate nvarchar(50) NULL,

        CreatedById nvarchar(50) NULL,

        IsChargeBack nvarchar(50) NULL,

        Expense nvarchar(50) NULL,

        RepoAgentId nvarchar(50) NULL,

        PayeeName nvarchar(50) NULL,

        ExpensePaymentId nvarchar(50) NULL,

        AdjustDetailId nvarchar(50) NULL

)

GO

 

 3) 데이터 INSERT 쿼리

use smileserv

go

set nocount on;

declare @start datetime = getutcdate();

declare @i int = 0;

while @i < 100000

begin

INSERT INTO InvoiceDetail (

  LegacyId,InvoiceId,DetailTypeId,Fee,

  FeeTax,Investigatorid,SalespersonId,

  CreateDate,CreatedById,IsChargeBack,

  Expense,RepoAgentId,PayeeName,ExpensePaymentId,

  AdjustDetailId)

  VALUES(1,1,2,1500.0000,0.0000,163,1002,

    ’11/30/2001 12:00:00 AM’,

    1116,0,550.0000,850,NULL,1,NULL);

  set @i = @i+1;

end

 

select datediff(ms, @start, getutcdate());

 

 4) 데이터 INSERT 결과

– 모든 명령어는 동일하게 sql 파일 생성후에 SQLCMD 명령어로 처리함

 Ex) sqlcmd -S localhost -U SA -P ‘패스워드’ -d smileserv -i 2.sql

– 3번의 명령어를 입력했으며, 하드웨어가 안정화 되는 부분 없이 바로 입력함

– 아래 결과 단위는 ms(밀리세컨드 입니다)

MSSQL On Linux

MSSQL 2008 R2(Windows)

20656

14780

21674

15020

20517

14963

 

– 서버 사양을 동일하게 했으면 좀더 정확한 데이터가 나오겠지만, 

테스트 서버 사양이 낮은 부분을 감안한다면 인간이 느낄 수 있는 그런 성능 차이는 없을 것 같다. 

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x