Centos7.3安装sqlserver2017

痛定思痛。 2023-06-26 05:55 38阅读 0赞

### 文章目录 ###

*  1, 系统要求
 *  2, 开始安装使用(创建用户数据库）
 *  3, 命令行导入导出数据: bcp (可连接远程主机）
 *  4, docker安装sqlserver
 *  5, 性能调优

官网指南：　 [https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017][https_docs.microsoft.com_en-us_sql_linux_quickstart-install-connect-red-hat_view_sql-server-2017]

# 1, 系统要求 #

# 2, 开始安装使用(创建用户数据库） #

### 1, 安装sqlserver
    sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
    sudo yum install -y mssql-server
    sudo /opt/mssql/bin/mssql-conf setup  # freely licensed: Evaluation, Developer, and Express.
    
    #查看服务状态
    systemctl restart mssql-server.service
    systemctl status mssql-server
    sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
    sudo firewall-cmd --reload
    
    ### 2, 安装命令行工具
    sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
    sudo yum install -y mssql-tools unixODBC-devel
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
    source ~/.bashrc
    
    ### 3, 测试使用
    sqlcmd -S localhost -U SA -P '<YourPassword>'
    #创建数据库test,和用户test
    >create database test;
     create login test with password='TESTabc@123',default_database=test
       	use test;
    	create user test for login test with default_schema=dbo;
    	exec sp_addrolemember 'db_owner','test';
    	
    >CREATE DATABASE TestDB
     SELECT Name from sys.Databases
     go
    >USE TestDB
    CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
    go
    >SELECT * FROM Inventory WHERE quantity > 152;
    go
    quit

# 3, 命令行导入导出数据: bcp (可连接远程主机） #

linux或windows安装此工具： [https://docs.microsoft.com/zh-cn/sql/tools/bcp-utility?view=sql-server-ver15][https_docs.microsoft.com_zh-cn_sql_tools_bcp-utility_view_sql-server-ver15]

*  源表或目标表： 可以是远程服务器上的
 *  两个表结构相同：例如t1表 做数据源表  
    ![在这里插入图片描述][20201117100149958.png_pic_center]

# [-t field terminator] 
    # [-r row terminator]
    # [-w wide character type]
    # [-S server name ] [-U username] [-P password]
    # -d database name
    
    #导出数据
    [root@hadoop01 ~]# bcp master.dbo.t1 out /tmp/t1.csv -Slocalhost -Usa -P123456 -t, -r\n -w 
    		 # bcp t1 out /tmp/t1.csv -Slocalhost -Usa -P123456 -d master -t, -r\n -w 
    Starting copy...
    
    3 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1      Average : (3000.0 rows per sec.)
    
    
    [root@hadoop01 ~]# cat /tmp/t1.csv
    ▒▒1,bn2,aan5,bbn
    
    
    #导入数据
    [root@hadoop01 ~]# bcp master.dbo.t2 in /tmp/t1.csv -Slocalhost -Usa -P123456 -t, -r\n -w
    		 # bcp t2 in /tmp/t1.csv -Slocalhost -Usa -P123456 -d master -t, -r\n -w 
    Starting copy...
    
    3 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1      Average : (3000.0 rows per sec.)

*  t2 表为目标表：把t1的数据导入到此表，结果如下  
    ![在这里插入图片描述][20201117100303877.png_pic_center]

# 4, docker安装sqlserver #

启用agent 代理：[https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup-sql-agent?view=sql-server-ver15\#EnableAgentAfterCU4][https_docs.microsoft.com_zh-cn_sql_linux_sql-server-linux-setup-sql-agent_view_sql-server-ver15_EnableAgentAfterCU4]  
启用代理xp: [https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/agent-xps-server-configuration-option?view=sql-server-ver15][https_docs.microsoft.com_zh-cn_sql_database-engine_configure-windows_agent-xps-server-configuration-option_view_sql-server-ver15]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V5ZW9mZWFnbGU_size_16_color_FFFFFF_t_70]

#sqlserver 服务
    docker run -d --net=host --name=mssql \
      -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=AAAaaa123!' \
      -v mssql_db:/var/opt/mssql \
      --restart=always  microsoft/mssql-server-linux

# 5, 性能调优 #

<table> 
 <thead> 
  <tr> 
   <th>条目</th> 
   <th>值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>最大内存: 默认80%</td> 
   <td>/opt/mssql/bin/mssql-conf set memory.memorylimitmb 3328 (MB)</td> 
  </tr> 
 </tbody> 
</table>

配置文件： /var/opt/mssql/mssql.conf

[EULA]
    accepteula = Y
    
    [coredump]
    captureminiandfull = true
    coredumptype = full
    
    [filelocation]
    defaultbackupdir = /var/opt/mssql/data/
    defaultdatadir = /var/opt/mssql/data/
    defaultdumpdir = /var/opt/mssql/data/
    defaultlogdir = /var/opt/mssql/data/
    
    [hadr]
    hadrenabled = 0
    
    [language]
    lcid = 1033
    
    [memory]
    memorylimitmb = 4096
    
    [network]
    forceencryption = 0
    ipaddress = 10.192.0.0
    kerberoskeytabfile = /var/opt/mssql/secrets/mssql.keytab
    tcpport = 1401
    tlscert = /etc/ssl/certs/mssql.pem
    tlsciphers = ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA
    tlskey = /etc/ssl/private/mssql.key
    tlsprotocols = 1.2,1.1,1.0

[https_docs.microsoft.com_en-us_sql_linux_quickstart-install-connect-red-hat_view_sql-server-2017]: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017
[https_docs.microsoft.com_zh-cn_sql_tools_bcp-utility_view_sql-server-ver15]: https://docs.microsoft.com/zh-cn/sql/tools/bcp-utility?view=sql-server-ver15
[20201117100149958.png_pic_center]: https://img-blog.csdnimg.cn/20201117100149958.png#pic_center
[20201117100303877.png_pic_center]: https://img-blog.csdnimg.cn/20201117100303877.png#pic_center
[https_docs.microsoft.com_zh-cn_sql_linux_sql-server-linux-setup-sql-agent_view_sql-server-ver15_EnableAgentAfterCU4]: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup-sql-agent?view=sql-server-ver15#EnableAgentAfterCU4
[https_docs.microsoft.com_zh-cn_sql_database-engine_configure-windows_agent-xps-server-configuration-option_view_sql-server-ver15]: https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/agent-xps-server-configuration-option?view=sql-server-ver15
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V5ZW9mZWFnbGU_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210322112053694.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V5ZW9mZWFnbGU=,size_16,color_FFFFFF,t_70