Technical posts/MS-SQL

SQL server 2017 리눅스 버전 설치

ODB 2017. 4. 28. 10:27

mssql 리눅스버전 / sql server 2017 linux 


오호~


드디어 나왔다 linux버전 mssql  나만 늦게 알았어;;



설치를 해보자


https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-get-started-tutorial


먼저 설치가능한 OS는?



좋습니다


버전은?


음... 내가 가지고 있는 리눅스중에 가장 최신이............ 7.2......... 응?


리눅스 부터 업그레이드 갑니다



yum clean all

yum update

reboot

cat /etc/redhat-release


끝..


명령은 간단하지만 오랜시간과 재부팅이 한번 필요하다


설치는

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-red-hat

와같이


curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

yum install -y mssql-server


[root@oel7 ~]# yum install mssql-server

Loaded plugins: langpacks, ulninfo

Resolving Dependencies

--> Running transaction check

---> Package mssql-server.x86_64 0:14.0.500.272-2 will be installed

--> Finished Dependency Resolution


Dependencies Resolved


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

 Package           Arch        Version             Repository                                Size

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

Installing:

 mssql-server      x86_64      14.0.500.272-2      packages-microsoft-com-mssql-server      159 M


Transaction Summary

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

Install  1 Package


Total download size: 159 M

Installed size: 159 M

Is this ok [y/d/N]: y

Downloading packages:

mssql-server-14.0.500.272-2.x86_64.rpm                                     | 159 MB  00:01:49

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : mssql-server-14.0.500.272-2.x86_64                                             1/1


+--------------------------------------------------------------+

Please run 'sudo /opt/mssql/bin/mssql-conf setup'

to complete the setup of Microsoft SQL Server

+--------------------------------------------------------------+


  Verifying  : mssql-server-14.0.500.272-2.x86_64                                             1/1


Installed:

  mssql-server.x86_64 0:14.0.500.272-2


Complete!

[root@oel7 ~]# 


159M 다운받고 압축을 풀면 826M 이고

위치는

/opt/mssql에 위치한다


설치를 마무리 하고 나온설명대로

/opt/mssql/bin/mssql-conf setup

해야 사용할 수 있다


[root@oel7 bin]# /opt/mssql/bin/mssql-conf setup

Setting up Microsoft SQL Server

Enter the new SQL Server system administrator password:

Confirm the new SQL Server system administrator password:

Starting Microsoft SQL Server...

Enabling Microsoft SQL Server to run at boot...

Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.

Setup completed successfully.

[root@oel7 bin]#

셋업 완료


systemctl status mssql-server


[root@oel7 bin]# systemctl status mssql-server

● mssql-server.service - Microsoft SQL Server Database Engine

   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)

   Active: active (running) since Fri 2017-04-28 09:14:57 KST; 9min ago

     Docs: https://docs.microsoft.com/en-us/sql/linux

 Main PID: 31789 (sqlservr)

   CGroup: /system.slice/mssql-server.service

           ├─31789 /opt/mssql/bin/sqlservr

           └─31812 /opt/mssql/bin/sqlservr


Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.15 spid19s     Server is listening...3].

Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.18 Server      Server is listening...4].

Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.20 Server      Dedicated admin con...34.

Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.21 spid19s     SQL Server is now r...ed.

Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.65 spid9s      Starting up databas...b'.

Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.86 spid9s      The tempdb database...s).

Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.87 spid22s     The Service Broker ...te.

Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.87 spid22s     The Database Mirror...te.

Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.88 spid22s     Service Broker mana...ed.

Apr 28 09:15:03 oel7 sqlservr[31789]: 2017-04-28 09:15:03.90 spid6s      Recovery is complet...ed.

Hint: Some lines were ellipsized, use -l to show in full.

[root@oel7 bin]# netstat -an |grep LISTEN|grep 1433

tcp        0      0 0.0.0.0:1433            0.0.0.0:*               LISTEN

[root@oel7 bin]# ps -ef |grep mssql

mssql    31789     1  0 09:14 ?        00:00:00 /opt/mssql/bin/sqlservr

mssql    31812 31789  3 09:14 ?        00:00:20 /opt/mssql/bin/sqlservr

root     32124 31169  0 09:25 pts/1    00:00:00 grep --color=auto mssql

[root@oel7 bin]# 


1433 port를 grep도 해보고 process를 직접 확인도 해봤다


접속을 해보자



접속이 잘되었다



use master

go

sp_helpfile


하면??


/var/opt/mssql/data/master.mdf

/var/opt/mssql/data/mastlog.ldf


오!

[root@oel7 data]# ls -arlt

total 61408

drwxrwx--- 6 mssql mssql       72 Apr 28 09:14 ..

-rw-r----- 1 mssql mssql 13959168 Apr 28 09:14 msdbdata.mdf

-rw-r----- 1 mssql mssql  8388608 Apr 28 09:15 model.mdf

-rw-r----- 1 mssql mssql  8388608 Apr 28 09:15 tempdb.mdf

-rw-r----- 1 mssql mssql  8388608 Apr 28 09:15 modellog.ldf

-rw-r----- 1 mssql mssql   524288 Apr 28 09:15 msdblog.ldf

-rw-r----- 1 mssql mssql  4194304 Apr 28 09:29 master.mdf

drwxr-xr-x 2 mssql mssql     4096 Apr 28 09:33 .

-rw-r----- 1 mssql mssql  8388608 Apr 28 09:37 templog.ldf

-rw-r----- 1 mssql mssql  2097152 Apr 28 09:38 mastlog.ldf



mssql:mssql 로 되어있다 640이고



여기쯤 오면 다들 가지는 호기심..


윈도우에서 쓰던 파일 들고가도 되나??

에 대한 해답을 찾아보려고 한다



네... 맞습니다... 툴로는 안됩니다 (2014를 써서 그렇습니다... 죄송합니다;;)

SSMS에서 하려고하면 경로를 윈도우 기준으로 다 잡아버려 정상적으로 할 수 없다


그럼 수동 명령으로

USE [master]

GO

CREATE DATABASE [abc] ON 

( FILENAME = N'/var/opt/mssql/data/abc_win.mdf' ),

( FILENAME = N'/var/opt/mssql/data/abc_1_win.ldf' )

 FOR ATTACH

GO


수행하면? 어?


정상적으로 attach가 가능하다



백업복구는?



역시나 저 '\' 때문에 GUI에서는 복구가 안된다 (2014를 써서 그렇습니다... 죄송합니다;;)


USE [master]

RESTORE DATABASE [def] FROM  DISK = N'/var/opt/mssql/data/def.bak' WITH  FILE = 1,  

MOVE N'Northwind' TO N'/var/opt/mssql/data/def.mdf',  

MOVE N'Northwind_log' TO N'/var/opt/mssql/data/def_1.ldf',  NOUNLOAD,  STATS = 5

GO


5 percent processed.

10 percent processed.

16 percent processed.

21 percent processed.

26 percent processed.

30 percent processed.

35 percent processed.

40 percent processed.

46 percent processed.

51 percent processed.

55 percent processed.

60 percent processed.

65 percent processed.

71 percent processed.

76 percent processed.

80 percent processed.

85 percent processed.

90 percent processed.

96 percent processed.

100 percent processed.

Processed 448 pages for database 'def', file 'Northwind' on file 1.

Processed 2 pages for database 'def', file 'Northwind_log' on file 1.

Converting database 'def' from version 655 to the current version 868.

Database 'def' running the upgrade step from version 655 to version 668.

Database 'def' running the upgrade step from version 668 to version 669.

Database 'def' running the upgrade step from version 669 to version 670.

Database 'def' running the upgrade step from version 670 to version 671.

Database 'def' running the upgrade step from version 671 to version 672.

Database 'def' running the upgrade step from version 672 to version 673.

Database 'def' running the upgrade step from version 673 to version 674.

Database 'def' running the upgrade step from version 674 to version 675.

Database 'def' running the upgrade step from version 675 to version 676.

Database 'def' running the upgrade step from version 676 to version 677.

Database 'def' running the upgrade step from version 677 to version 679.

Database 'def' running the upgrade step from version 679 to version 680.

Database 'def' running the upgrade step from version 680 to version 681.

Database 'def' running the upgrade step from version 681 to version 682.

Database 'def' running the upgrade step from version 682 to version 683.

Database 'def' running the upgrade step from version 683 to version 684.

Database 'def' running the upgrade step from version 684 to version 685.

Database 'def' running the upgrade step from version 685 to version 686.

Database 'def' running the upgrade step from version 686 to version 687.

Database 'def' running the upgrade step from version 687 to version 688.

Database 'def' running the upgrade step from version 688 to version 689.

Database 'def' running the upgrade step from version 689 to version 690.

Database 'def' running the upgrade step from version 690 to version 691.

Database 'def' running the upgrade step from version 691 to version 692.

Database 'def' running the upgrade step from version 692 to version 693.

Database 'def' running the upgrade step from version 693 to version 694.

Database 'def' running the upgrade step from version 694 to version 695.

Database 'def' running the upgrade step from version 695 to version 696.

Database 'def' running the upgrade step from version 696 to version 697.

Database 'def' running the upgrade step from version 697 to version 698.

Database 'def' running the upgrade step from version 698 to version 699.

Database 'def' running the upgrade step from version 699 to version 700.

Database 'def' running the upgrade step from version 700 to version 701.

Database 'def' running the upgrade step from version 701 to version 702.

Database 'def' running the upgrade step from version 702 to version 703.

Database 'def' running the upgrade step from version 703 to version 704.

Database 'def' running the upgrade step from version 704 to version 705.

Database 'def' running the upgrade step from version 705 to version 706.

Database 'def' running the upgrade step from version 706 to version 770.

Database 'def' running the upgrade step from version 770 to version 771.

Database 'def' running the upgrade step from version 771 to version 772.

Database 'def' running the upgrade step from version 772 to version 773.

Database 'def' running the upgrade step from version 773 to version 774.

Database 'def' running the upgrade step from version 774 to version 775.

Database 'def' running the upgrade step from version 775 to version 776.

Database 'def' running the upgrade step from version 776 to version 777.

Database 'def' running the upgrade step from version 777 to version 778.

Database 'def' running the upgrade step from version 778 to version 779.

Database 'def' running the upgrade step from version 779 to version 780.

Database 'def' running the upgrade step from version 780 to version 781.

Database 'def' running the upgrade step from version 781 to version 782.

Database 'def' running the upgrade step from version 782 to version 801.

Database 'def' running the upgrade step from version 801 to version 802.

Database 'def' running the upgrade step from version 802 to version 803.

Database 'def' running the upgrade step from version 803 to version 804.

Database 'def' running the upgrade step from version 804 to version 805.

Database 'def' running the upgrade step from version 805 to version 806.

Database 'def' running the upgrade step from version 806 to version 807.

Database 'def' running the upgrade step from version 807 to version 808.

Database 'def' running the upgrade step from version 808 to version 809.

Database 'def' running the upgrade step from version 809 to version 810.

Database 'def' running the upgrade step from version 810 to version 811.

Database 'def' running the upgrade step from version 811 to version 812.

Database 'def' running the upgrade step from version 812 to version 813.

Database 'def' running the upgrade step from version 813 to version 814.

Database 'def' running the upgrade step from version 814 to version 815.

Database 'def' running the upgrade step from version 815 to version 816.

Database 'def' running the upgrade step from version 816 to version 817.

Database 'def' running the upgrade step from version 817 to version 818.

Database 'def' running the upgrade step from version 818 to version 819.

Database 'def' running the upgrade step from version 819 to version 820.

Database 'def' running the upgrade step from version 820 to version 821.

Database 'def' running the upgrade step from version 821 to version 822.

Database 'def' running the upgrade step from version 822 to version 823.

Database 'def' running the upgrade step from version 823 to version 824.

Database 'def' running the upgrade step from version 824 to version 825.

Database 'def' running the upgrade step from version 825 to version 826.

Database 'def' running the upgrade step from version 826 to version 827.

Database 'def' running the upgrade step from version 827 to version 828.

Database 'def' running the upgrade step from version 828 to version 829.

Database 'def' running the upgrade step from version 829 to version 830.

Database 'def' running the upgrade step from version 830 to version 831.

Database 'def' running the upgrade step from version 831 to version 832.

Database 'def' running the upgrade step from version 832 to version 833.

Database 'def' running the upgrade step from version 833 to version 834.

Database 'def' running the upgrade step from version 834 to version 835.

Database 'def' running the upgrade step from version 835 to version 836.

Database 'def' running the upgrade step from version 836 to version 837.

Database 'def' running the upgrade step from version 837 to version 838.

Database 'def' running the upgrade step from version 838 to version 839.

Database 'def' running the upgrade step from version 839 to version 840.

Database 'def' running the upgrade step from version 840 to version 841.

Database 'def' running the upgrade step from version 841 to version 842.

Database 'def' running the upgrade step from version 842 to version 843.

Database 'def' running the upgrade step from version 843 to version 844.

Database 'def' running the upgrade step from version 844 to version 845.

Database 'def' running the upgrade step from version 845 to version 846.

Database 'def' running the upgrade step from version 846 to version 847.

Database 'def' running the upgrade step from version 847 to version 848.

Database 'def' running the upgrade step from version 848 to version 849.

Database 'def' running the upgrade step from version 849 to version 850.

Database 'def' running the upgrade step from version 850 to version 851.

Database 'def' running the upgrade step from version 851 to version 852.

Database 'def' running the upgrade step from version 852 to version 853.

Database 'def' running the upgrade step from version 853 to version 854.

Database 'def' running the upgrade step from version 854 to version 855.

Database 'def' running the upgrade step from version 855 to version 856.

Database 'def' running the upgrade step from version 856 to version 857.

Database 'def' running the upgrade step from version 857 to version 858.

Database 'def' running the upgrade step from version 858 to version 859.

Database 'def' running the upgrade step from version 859 to version 860.

Database 'def' running the upgrade step from version 860 to version 861.

Database 'def' running the upgrade step from version 861 to version 862.

Database 'def' running the upgrade step from version 862 to version 863.

Database 'def' running the upgrade step from version 863 to version 864.

Database 'def' running the upgrade step from version 864 to version 865.

Database 'def' running the upgrade step from version 865 to version 866.

Database 'def' running the upgrade step from version 866 to version 867.

Database 'def' running the upgrade step from version 867 to version 868.

RESTORE DATABASE successfully processed 450 pages in 0.381 seconds (9.214 MB/sec).


잘 복원되었다




MS의 이번 행보는 OS의 문제점을 알아서 그것에 대한 보완이 아닌가 싶다

리눅스로 넘어 왔으니 OS자체적인 문제도 없어졌고

더불어 윈도우에서 운영중인 데이터베이스를 무난하게 가져올수 있으니 귿


"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."