澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

安装并使用SQL

2019-09-22 作者:数据库网络   |   浏览(90)

1. 前言

SQL Server一般是在设置进程中举行相关的配置,安装到位以往,再去修改有一点点配备就比较费心,比如更动SQL Server实例品级的排序准绳。但在Linux下,安装进程并未过多得以安顿的步调,安装步骤变得相当粗略,更加多的安顿可以透过/opt/mssql/bin/mssql-conf工具进行。

 

1. 前言

前一篇配置SQL Server on Linux(1),地址: ,是有关改动数据库排序法规的。完成的原理跟在Windows平台差不离,都以索要备份客商数据库,重新建立系统数据库来兑现的,不过操作进程简化了。下边包车型客车配备是一些符合规律的设置,举个例子最大内部存款和储蓄器,私下认可数据文件地点,等。

 

1. 前言

前一篇配置SQL Server on Linux(1),地址: ,是有关更改数据库排序准则的。完成的规律跟在Windows平台差不离,都以内需备份客户数据库,重新建立系统数据库来完毕的,可是操作进程简化了。上面包车型地铁陈设是部分例行的安装,比方最大内部存款和储蓄器,暗中认可数据文件地方,等。

 

CentOS7 安装并采纳SQL Server

2. 环境

Linux: CentOS 7.4,SQL Server 2017 (RC2) - 14.0.900.75 (X64)

 

2. 环境

Linux: CentOS 7.4,SQL Server 2017 (RC2) - 14.0.900.75 (X64)

 

2. 环境

Linux: CentOS 7.4,SQL Server 2017 (RC2) - 14.0.900.75 (X64)

 

Linux 上的 SQL Server 安装指南

  • 参考:https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup

3. 改换SQL Server排序准绳

3. 退换设置

3. 转移设置

作者依据CentOS7进展安装操作

  • 下载 Microsoft SQL Server Red Hat 存款和储蓄库配置文件
   sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo
  • 运转以下命令,安装 SQL Server
sudo yum update -y
sudo yum install -y mssql-server
  • 安装落成现在,施行上面发号施令,依据提醒设置SA密码,并接纳相应的版本
sudo /opt/mssql/bin/mssql-conf setup

![图表描述][1]

担保SA账户密码中度满意默许要求(最少 8 个字符,包蕴大写和小写字母、十进制数字和/或非字母数字符号)

  • 配置完毕后,请表明服务是或不是正在运行
systemctl status mssql-server

![图形描述][2]

  • 开启防火墙上的 SQL Server 端口,默感到TCP 1433
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload
# firewall-cmd未启用不需要做此设置

到此,SQL Server已在CentOS7上健康运作

手续如下:
  1. 备份全体客户数据库。(假若是刚刚安装好的实例未有顾客数据库能够跳过这一步。)
  2. 甘休数据库实例
  3. 运维/opt/mssql/bin/mssql-conf set-collation命令修改排序准则
  4. 开发银行数据库实例
  5. 过来客户数据库(未有客商数据库可跳过)

 

 

安装并使用SQL。下边就来演示下。

询问当前数据库实例的排序法规

1> SELECT CONVERT(NVARCHAR(50),SERVERPROPERTY('Collation'));
2> GO

--------------------------------------------------
SQL_Latin1_General_CP1_CI_AS  

Code-1: 改变前的数据库实例排序法则

 

 

使用sp_detach_db存款和储蓄进程来分别数据库。假设提示数据库正在选用,能够安装数据库为单客商形式,恐怕索性就把数据库实例重启。

1> exec sp_detach_db db1;
2> go
1> exec sp_detach_db AdventureWorks2014;
2> go
1> exec sp_detach_db AdventureWorks2017;
2> go

Code-2:分离客商数据库

 

 

在shell 下:

[root@134test ~]# systemctl stop mssql-server
[root@134test ~]# /opt/mssql/bin/mssql-conf set-collation
Enter the collation: Chinese_PRC_CI_AS
Configuring SQL Server...

This is an evaluation version.  There are [27] days left in the evaluation period.
2017-12-27 10:31:21.81 Server      Microsoft SQL Server 2017 (RC2) - 14.0.900.75 (X64) 
    Jul 27 2017 08:53:49 
    Copyright (C) 2017 Microsoft Corporation
    Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))
2017-12-27 10:31:21.82 Server      UTC adjustment: 8:00
2017-12-27 10:31:21.82 Server      (c) Microsoft Corporation.
2017-12-27 10:31:21.82 Server      All rights reserved.
2017-12-27 10:31:21.82 Server      Server process ID is 4120.
2017-12-27 10:31:21.82 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2017-12-27 10:31:21.83 Server      Registry startup parameters: 
     -d /var/opt/mssql/data/master.mdf
     -l /var/opt/mssql/data/mastlog.ldf
     -e /var/opt/mssql/log/errorlog
2017-12-27 10:31:21.83 Server      Command Line Startup Parameters:
     -q "Chinese_PRC_CI_AS"
2017-12-27 10:31:21.83 Server      SQL Server detected 1 sockets with 4 cores per socket and 4 logical processors per socket, 
4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2017-12-27 10:31:21.84 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2017-12-27 10:31:21.84 Server      Detected 3027 MB of RAM. This is an informational message; no user action is required.
2017-12-27 10:31:21.84 Server      Using conventional memory in the memory manager.
2017-12-27 10:31:22.36 Server      Buffer pool extension is already disabled. No action is necessary. 
2017-12-27 10:31:22.63 Server      Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2017-12-27 10:31:22.67 Server      InitializeExternalUserGroupSid failed. Implied authentication will be disabled.
2017-12-27 10:31:22.67 Server      Implied authentication manager initialization failed. Implied authentication will be disabled.
2017-12-27 10:31:22.69 Server      Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. 
Allowed TLS ciphers are ['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-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:
AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].
2017-12-27 10:31:22.76 Server      The maximum number of dedicated administrator connections for this instance is '1'
2017-12-27 10:31:22.76 Server      Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. 
This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2017-12-27 10:31:22.77 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  
This is an informational message only. No user action is required.
2017-12-27 10:31:22.79 Server      In-Memory OLTP initialized on lowend machine.
2017-12-27 10:31:22.84 Server      Database Instant File Initialization: enabled. For security and performance considerations see the topic 
'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2017-12-27 10:31:22.85 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2017-12-27 10:31:22.85 spid5s      Warning ******************
2017-12-27 10:31:22.86 spid5s      SQL Server started in single-user mode. This an informational message only. No user action is required.
2017-12-27 10:31:22.86 Server      Query Store settings initialized with enabled = 1, 
2017-12-27 10:31:22.86 spid5s      Starting up database 'master'.
2017-12-27 10:31:22.87 Server      Software Usage Metrics is disabled.
2017-12-27 10:31:23.47 spid5s      Buffer pool extension is already disabled. No action is necessary. 
2017-12-27 10:31:23.47 spid5s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2017-12-27 10:31:23.49 spid5s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2017-12-27 10:31:23.68 spid5s      SQL Trace ID 1 was started by login "sa".
2017-12-27 10:31:23.68 spid5s      Server name is '134test'. This is an informational message only. No user action is required.
2017-12-27 10:31:23.70 spid5s      Always On Availability Groups was not started because the SQL Server instance is running in single-user mode.  
This is an informational message.  No user action is required.
2017-12-27 10:31:23.71 spid5s      Starting up database 'msdb'.
2017-12-27 10:31:23.72 spid9s      Starting up database 'mssqlsystemresource'.
2017-12-27 10:31:23.75 spid9s      The resource database build version is 14.00.900. This is an informational message only. No user action is required.
2017-12-27 10:31:23.79 spid9s      Starting up database 'model'.
2017-12-27 10:31:24.04 spid5s      Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2017-12-27 10:31:24.12 spid9s      Polybase feature disabled.
2017-12-27 10:31:24.12 spid9s      Clearing tempdb database.
2017-12-27 10:31:24.33 spid17s     A self-generated certificate was successfully loaded for encryption.
2017-12-27 10:31:24.34 spid17s     Server is listening on [ 'any' <ipv6> 1433].
2017-12-27 10:31:24.35 spid17s     Server is listening on [ 'any' <ipv4> 1433].
2017-12-27 10:31:24.35 Server      Server is listening on [ ::1 <ipv6> 1434].
2017-12-27 10:31:24.36 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2017-12-27 10:31:24.36 Server      Dedicated admin connection support was established for listening locally on port 1434.
2017-12-27 10:31:24.39 spid17s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2017-12-27 10:31:24.99 spid9s      Starting up database 'tempdb'.
2017-12-27 10:31:25.12 spid9s      The tempdb database has 1 data file(s).
2017-12-27 10:31:25.18 spid5s      Warning ******************
2017-12-27 10:31:25.18 spid5s      Attempting to change default collation to Chinese_PRC_CI_AS.
2017-12-27 10:31:25.22 spid5s       index restored for master.syspriorities.
2017-12-27 10:31:25.23 spid5s       index restored for master.sysbrickfiles.
2017-12-27 10:31:25.23 spid5s       index restored for master.sysprufiles.
2017-12-27 10:31:25.23 spid5s       index restored for master.sysowners.
2017-12-27 10:31:25.23 spid5s       index restored for master.sysdbreg.
2017-12-27 10:31:25.24 spid5s       index restored for master.sysschobjs.
2017-12-27 10:31:25.25 spid5s       index restored for master.sysextsources.
2017-12-27 10:31:25.25 spid5s       index restored for master.sysextfileformats.
2017-12-27 10:31:25.25 spid5s       index restored for master.syscolpars.
2017-12-27 10:31:25.25 spid5s       index restored for master.sysxlgns.
2017-12-27 10:31:25.25 spid5s       index restored for master.sysxsrvs.
2017-12-27 10:31:25.26 spid5s       index restored for master.sysnsobjs.
2017-12-27 10:31:25.26 spid5s       index restored for master.syscerts.
2017-12-27 10:31:25.26 spid5s       index restored for master.sysrmtlgns.
2017-12-27 10:31:25.26 spid5s       index restored for master.sysxprops.
2017-12-27 10:31:25.26 spid5s       index restored for master.sysscalartypes.
2017-12-27 10:31:25.26 spid5s       index restored for master.sysidxstats.
2017-12-27 10:31:25.27 spid5s       index restored for master.sysendpts.
2017-12-27 10:31:25.27 spid5s       index restored for master.sysclsobjs.
2017-12-27 10:31:25.27 spid5s       index restored for master.sysremsvcbinds.
2017-12-27 10:31:25.27 spid5s       index restored for master.sysrts.
2017-12-27 10:31:25.27 spid5s       index restored for master.sysasymkeys.
2017-12-27 10:31:25.27 spid5s       index restored for master.syssqlguides.
2017-12-27 10:31:25.27 spid5s       index restored for master.syssoftobjrefs.
2017-12-27 10:31:25.28 spid5s       index restored for master.MSreplication_options.
2017-12-27 10:31:25.31 spid5s       index restored for tempdb.syspriorities.
2017-12-27 10:31:25.32 spid5s       index restored for tempdb.sysowners.
2017-12-27 10:31:25.32 spid5s       index restored for tempdb.sysdbreg.
2017-12-27 10:31:25.33 spid5s       index restored for tempdb.sysschobjs.
2017-12-27 10:31:25.33 spid5s       index restored for tempdb.sysextsources.
2017-12-27 10:31:25.33 spid5s       index restored for tempdb.sysextfileformats.
2017-12-27 10:31:25.33 spid5s       index restored for tempdb.syscolpars.
2017-12-27 10:31:25.33 spid5s       index restored for tempdb.sysxlgns.
2017-12-27 10:31:25.33 spid5s       index restored for tempdb.sysxsrvs.
2017-12-27 10:31:25.33 spid5s       index restored for tempdb.sysnsobjs.
2017-12-27 10:31:25.34 spid5s       index restored for tempdb.syscerts.
2017-12-27 10:31:25.34 spid5s       index restored for tempdb.sysrmtlgns.
2017-12-27 10:31:25.34 spid5s       index restored for tempdb.sysxprops.
2017-12-27 10:31:25.34 spid5s       index restored for tempdb.sysscalartypes.
2017-12-27 10:31:25.34 spid5s       index restored for tempdb.sysidxstats.
2017-12-27 10:31:25.34 spid5s       index restored for tempdb.sysendpts.
2017-12-27 10:31:25.34 spid5s       index restored for tempdb.sysclsobjs.
2017-12-27 10:31:25.35 spid5s       index restored for tempdb.sysremsvcbinds.
2017-12-27 10:31:25.35 spid5s       index restored for tempdb.sysrts.
2017-12-27 10:31:25.35 spid5s       index restored for tempdb.sysasymkeys.
2017-12-27 10:31:25.35 spid5s       index restored for tempdb.syssqlguides.
2017-12-27 10:31:25.35 spid5s       index restored for tempdb.syssoftobjrefs.
2017-12-27 10:31:25.39 spid5s       index restored for model.syspriorities.
2017-12-27 10:31:25.40 spid5s       index restored for model.sysowners.
2017-12-27 10:31:25.40 spid5s       index restored for model.sysdbreg.
2017-12-27 10:31:25.42 spid5s       index restored for model.sysschobjs.
2017-12-27 10:31:25.42 spid5s       index restored for model.sysextsources.
2017-12-27 10:31:25.42 spid5s       index restored for model.sysextfileformats.
2017-12-27 10:31:25.43 spid5s       index restored for model.syscolpars.
2017-12-27 10:31:25.43 spid5s       index restored for model.sysxlgns.
2017-12-27 10:31:25.44 spid5s       index restored for model.sysxsrvs.
2017-12-27 10:31:25.44 spid5s       index restored for model.sysnsobjs.
2017-12-27 10:31:25.44 spid5s       index restored for model.syscerts.
2017-12-27 10:31:25.45 spid5s       index restored for model.sysrmtlgns.
2017-12-27 10:31:25.45 spid5s       index restored for model.sysxprops.
2017-12-27 10:31:25.45 spid5s       index restored for model.sysscalartypes.
2017-12-27 10:31:25.46 spid5s       index restored for model.sysidxstats.
2017-12-27 10:31:25.46 spid5s       index restored for model.sysendpts.
2017-12-27 10:31:25.47 spid5s       index restored for model.sysclsobjs.
2017-12-27 10:31:25.47 spid5s       index restored for model.sysremsvcbinds.
2017-12-27 10:31:25.48 spid5s       index restored for model.sysrts.
2017-12-27 10:31:25.48 spid5s       index restored for model.sysasymkeys.
2017-12-27 10:31:25.48 spid5s       index restored for model.syssqlguides.
2017-12-27 10:31:25.48 spid5s       index restored for model.syssoftobjrefs.
2017-12-27 10:31:25.83 spid5s       index restored for msdb.syspriorities.
2017-12-27 10:31:25.84 spid5s       index restored for msdb.sysowners.
2017-12-27 10:31:25.84 spid5s       index restored for msdb.sysdbreg.
2017-12-27 10:31:25.90 spid5s       index restored for msdb.sysschobjs.
2017-12-27 10:31:25.90 spid5s       index restored for msdb.sysextsources.
2017-12-27 10:31:25.90 spid5s       index restored for msdb.sysextfileformats.
2017-12-27 10:31:25.96 spid5s       index restored for msdb.syscolpars.
2017-12-27 10:31:25.96 spid5s       index restored for msdb.sysxlgns.
2017-12-27 10:31:25.96 spid5s       index restored for msdb.sysxsrvs.
2017-12-27 10:31:25.97 spid5s       index restored for msdb.sysnsobjs.
2017-12-27 10:31:25.97 spid5s       index restored for msdb.syscerts.
2017-12-27 10:31:25.97 spid5s       index restored for msdb.sysrmtlgns.
2017-12-27 10:31:25.98 spid5s       index restored for msdb.sysxprops.
2017-12-27 10:31:25.99 spid5s       index restored for msdb.sysscalartypes.
2017-12-27 10:31:26.00 spid5s       index restored for msdb.sysidxstats.
2017-12-27 10:31:26.00 spid5s       index restored for msdb.sysendpts.
2017-12-27 10:31:26.01 spid5s       index restored for msdb.sysclsobjs.
2017-12-27 10:31:26.01 spid5s       index restored for msdb.sysremsvcbinds.
2017-12-27 10:31:26.02 spid5s       index restored for msdb.sysrts.
2017-12-27 10:31:26.02 spid5s       index restored for msdb.sysasymkeys.
2017-12-27 10:31:26.02 spid5s       index restored for msdb.syssqlguides.
2017-12-27 10:31:26.04 spid5s       index restored for msdb.syssoftobjrefs.
2017-12-27 10:31:26.05 spid5s       index restored for msdb.syscachedcredentials.
2017-12-27 10:31:26.06 spid5s       index restored for msdb.syscollector_blobs_internal.
2017-12-27 10:31:26.07 spid5s       index restored for msdb.syspolicy_system_health_state_internal.
2017-12-27 10:31:26.10 spid5s       index restored for msdb.sysmanagement_shared_server_groups_internal.
2017-12-27 10:31:26.11 spid5s       index restored for msdb.sysssispackages.
2017-12-27 10:31:26.12 spid5s       index restored for msdb.sysmanagement_shared_registered_servers_internal.
2017-12-27 10:31:26.03 spid5s       index restored for msdb.sysssispackagefolders.
2017-12-27 10:31:26.04 spid5s       index restored for msdb.sysutility_mi_smo_properties_to_collect_internal.
2017-12-27 10:31:26.05 spid5s       index restored for msdb.syspolicy_configuration_internal.
2017-12-27 10:31:26.06 spid5s       index restored for msdb.log_shipping_primary_databases.
2017-12-27 10:31:26.08 spid5s       index restored for msdb.syspolicy_management_facets.
2017-12-27 10:31:26.10 spid5s       index restored for msdb.syspolicy_facet_events.
2017-12-27 10:31:26.11 spid5s       index restored for msdb.syscollector_config_store_internal.
2017-12-27 10:31:26.12 spid5s       index restored for msdb.sysutility_ucp_dac_health_internal.
2017-12-27 10:31:26.12 spid5s       index restored for msdb.sysmail_profile.
2017-12-27 10:31:26.13 spid5s       index restored for msdb.MSdbms.
2017-12-27 10:31:26.14 spid5s       index restored for msdb.log_shipping_primary_secondaries.
2017-12-27 10:31:26.16 spid5s       index restored for msdb.syspolicy_conditions_internal.
2017-12-27 10:31:26.17 spid5s       index restored for msdb.MSdbms_datatype.
2017-12-27 10:31:26.17 spid5s       index restored for msdb.log_shipping_monitor_primary.
2017-12-27 10:31:26.18 spid5s       index restored for msdb.log_shipping_monitor_history_detail.
2017-12-27 10:31:26.19 spid5s       index restored for msdb.log_shipping_monitor_error_detail.
2017-12-27 10:31:26.19 spid5s       index restored for msdb.log_shipping_secondary.
2017-12-27 10:31:26.20 spid5s       index restored for msdb.log_shipping_secondary_databases.
2017-12-27 10:31:26.21 spid5s       index restored for msdb.sysutility_ucp_managed_instances_internal.
2017-12-27 10:31:26.22 spid5s       index restored for msdb.log_shipping_monitor_secondary.
2017-12-27 10:31:26.23 spid5s       index restored for msdb.sysmail_account.
2017-12-27 10:31:26.23 spid5s       index restored for msdb.sysutility_ucp_mi_health_internal.
2017-12-27 10:31:26.24 spid5s       index restored for msdb.sysdac_instances_internal.
2017-12-27 10:31:26.26 spid5s       index restored for msdb.syscollector_collection_sets_internal.
2017-12-27 10:31:26.27 spid5s       index restored for msdb.syspolicy_policy_categories_internal.
2017-12-27 10:31:26.27 spid5s       index restored for msdb.sysdac_history_internal.
2017-12-27 10:31:26.29 spid5s       index restored for msdb.sysmail_servertype.
2017-12-27 10:31:26.31 spid5s       index restored for msdb.syspolicy_object_sets_internal.
2017-12-27 10:31:26.32 spid5s       index restored for msdb.sysutility_ucp_health_policies_internal.
2017-12-27 10:31:26.33 spid5s       index restored for msdb.sysmail_server.
2017-12-27 10:31:26.33 spid5s       index restored for msdb.sysutility_ucp_filegroups_with_policy_violations_internal.
2017-12-27 10:31:26.35 spid5s       index restored for msdb.sysutility_ucp_policy_check_conditions_internal.
2017-12-27 10:31:26.36 spid5s       index restored for msdb.syscollector_collector_types_internal.
2017-12-27 10:31:26.38 spid5s       index restored for msdb.syspolicy_policies_internal.
2017-12-27 10:31:26.38 spid5s       index restored for msdb.sysutility_ucp_policy_target_conditions_internal.
2017-12-27 10:31:26.39 spid5s       index restored for msdb.sysmail_configuration.
2017-12-27 10:31:26.40 spid5s       index restored for msdb.external_libraries_installed.
2017-12-27 10:31:26.41 spid5s       index restored for msdb.sysdbmaintplan_databases.
2017-12-27 10:31:26.42 spid5s       index restored for msdb.sysutility_ucp_configuration_internal.
2017-12-27 10:31:26.43 spid5s       index restored for msdb.sysproxies.
2017-12-27 10:31:26.45 spid5s       index restored for msdb.syssubsystems.
2017-12-27 10:31:26.46 spid5s       index restored for msdb.syscollector_collection_items_internal.
2017-12-27 10:31:26.47 spid5s       index restored for msdb.sysdownloadlist.
2017-12-27 10:31:26.48 spid5s       index restored for msdb.sysoriginatingservers.
2017-12-27 10:31:26.50 spid5s       index restored for msdb.autoadmin_managed_databases.
2017-12-27 10:31:26.51 spid5s       index restored for msdb.sysjobs.
2017-12-27 10:31:26.53 spid5s       index restored for msdb.smart_backup_files.
2017-12-27 10:31:26.56 spid5s       index restored for msdb.backupset.
2017-12-27 10:31:26.58 spid5s       index restored for msdb.sysjobsteps.
2017-12-27 10:31:26.59 spid5s       index restored for msdb.syscollector_execution_stats_internal.
2017-12-27 10:31:26.59 spid5s       index restored for msdb.sysutility_ucp_mi_file_space_health_internal.
2017-12-27 10:31:26.61 spid5s       index restored for msdb.syspolicy_target_sets_internal.
2017-12-27 10:31:26.62 spid5s       index restored for msdb.sysschedules.
2017-12-27 10:31:26.63 spid5s       index restored for msdb.sysutility_ucp_mi_database_health_internal.
2017-12-27 10:31:26.64 spid5s       index restored for msdb.logmarkhistory.
2017-12-27 10:31:26.64 spid5s       index restored for msdb.sysutility_ucp_dac_file_space_health_internal.
2017-12-27 10:31:26.65 spid5s       index restored for msdb.syscategories.
2017-12-27 10:31:26.66 spid5s       index restored for msdb.systargetservers.
2017-12-27 10:31:26.67 spid5s       index restored for msdb.sysutility_ucp_mi_volume_space_health_internal.
2017-12-27 10:31:26.67 spid5s       index restored for msdb.sysutility_mi_dac_execution_statistics_internal.
2017-12-27 10:31:26.68 spid5s       index restored for msdb.systargetservergroups.
2017-12-27 10:31:26.69 spid5s       index restored for msdb.syspolicy_policy_category_subscriptions_internal.
2017-12-27 10:31:26.69 spid5s       index restored for msdb.sysalerts.
2017-12-27 10:31:26.70 spid5s       index restored for msdb.sysutility_ucp_computer_cpu_health_internal.
2017-12-27 10:31:26.70 spid5s       index restored for msdb.sysutility_mi_session_statistics_internal.
2017-12-27 10:31:26.71 spid5s       index restored for msdb.sysoperators.
2017-12-27 10:31:26.73 spid5s      The default collation was successfully changed.
2017-12-27 10:31:26.73 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
The server collation has been changed.
Please run 'sudo systemctl start mssql-server' to start SQL Server.

Code-3:改变排序准则

 

 

[root@134test ~]# systemctl start mssql-server

Code-4:重启数据库实例

 

 

询问当前数据库实例的排序准则

1> SELECT CONVERT(NVARCHAR(50),SERVERPROPERTY('Collation'));
2> GO

--------------------------------------------------
Chinese_PRC_CI_AS

Code-5:改换后的数据库实例排序准绳

 

 

复苏客商数据库

1> exec sp_attach_db 'db1','/data/mssql_data/db1.mdf','/data/mssql_data/db1_log.ldf';
2> go
1> exec sp_attach_db 'AdventureWorks2014','/data/mssql_data/AdventureWorks2014.mdf','/data/mssql_data/AdventureWorks2014_log.ldf';
2> go
1> exec sp_attach_db 'AdventureWorks2017','/data/mssql_data/AdventureWorks2017.mdf','/data/mssql_data/AdventureWorks2017_log.ldf';
2> go

Code-6:还原数据库

 

 

询问当前数据库的排序法则

1> SELECT CONVERT(NVARCHAR(30),name), CONVERT(NVARCHAR(50),collation_name) FROM sys.databases;
2> GO

------------------------------ --------------------------------------------------
master                         Chinese_PRC_CI_AS                                 
tempdb                         Chinese_PRC_CI_AS                                 
model                          Chinese_PRC_CI_AS                                 
msdb                           Chinese_PRC_CI_AS                                 
db1                            SQL_Latin1_General_CP1_CI_AS                      
AdventureWorks2014             SQL_Latin1_General_CP1_CI_AS                      
AdventureWorks2017             SQL_Latin1_General_CP1_CI_AS                      

(7 rows affected)

Code-7:全数数据库的排序准绳

 

 

3.1 内部存款和储蓄器限制

使用mssql-conf工具

/opt/mssql/bin/mssql-conf set memory.memorylimitmb 3500

 

重启数据库实例

systemctl restart mssql-server

 

 

3.1 内部存款和储蓄器限制

使用mssql-conf工具

/opt/mssql/bin/mssql-conf set memory.memorylimitmb 3500

 

重启数据库实例

systemctl restart mssql-server

 

 

安装 SQL Server 命令行工具

要开创数据库,需求运用一种工具,能够在 SQL Server 上运转 TRANSACT-SQL 语句举办延续。 以下步骤安装 SQL Server 命令行工具:sqlcmd和bcp

  • 下载 Microsoft Red Hat 存款和储蓄库配置文件
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
  • 要是服务器以前设置过mssql,供给删除任何较旧的 unixODBC 程序包
sudo yum update
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel
  • 运作以下命令以安装mssql 工具与 unixODBC 开拓职员包
sudo yum update
sudo yum install -y mssql-tools unixODBC-devel
  • 增加/opt/mssql-tools/bin/路线随地境变量
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

4. 参考

https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-configure-mssql-conf

 

疑问

以上措施是合法的手续,但运用SSMS去查看实例的内部存款和储蓄器设置,开掘最大内部存款和储蓄器限制照旧尚未退换。不精晓干什么。使用sys.sp_configure去布署最大内部存款和储蓄器则生效了。

 

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'3500'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

 

 

疑问

以上办法是合法的步骤,但利用SSMS去查看实例的内部存款和储蓄器设置,开掘最大内部存储器限制依然尚未更换。不亮堂怎么。使用sys.sp_configure去布置最大内部存款和储蓄器则生效了。

 

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'3500'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

 

 

本地连接配置

以下步骤使用 sqlcmd 本地连接到新的 SQL Server 实例

  • 利用 SQL Server 名称 (-S),客商名 (-U) 和密码 (-P) 的参数运行sqlcmd。 在本教程中,顾客进行业地连接,因而服务器名字为 localhost。 顾客名称为 SA,密码是在安装进度中为 SA 帐户提供的密码。
sqlcmd -S localhost -U SA -P '<YourPassword>'

密码输入能够利用互动的格局

  • 一经一连成功展示如下图:应会展现 sqlcmd 命令提醒符:1>

![图片描述][3]

3.2 设置暗许数据或日志目录地点

创办自定义目录及改变目录权限

[root@134test ~]# mkdir -p /data/mssql_data/
[root@134test ~]# chown -R mssql:mssql /data/mssql_data/

 

使用mssql-conf工具

[root@134test ~]# /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /data/mssql_data/
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.

 

重启数据库实例生效

systemctl restart mssql-server

 

测验(数据和日志文件都在那几个目录下)

4> create database db2;
5> go

 

[root@134test ~]# cd /data/mssql_data/
[root@134test /data/mssql_data]# ll db*
-rw-rw---- 1 mssql mssql  3932160 2017-12-27 12:31 db1_log.ldf
-rw-rw---- 1 mssql mssql 12582912 2017-12-27 12:31 db1.mdf
-rw-rw---- 1 mssql mssql  8388608 2017-12-27 12:37 db2_log.ldf
-rw-rw---- 1 mssql mssql  8388608 2017-12-27 12:33 db2.mdf

 

一旦须要独自改换日志的目录(如/tmp)

/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /tmp/

澳门新萄京官方网站, 

 

3.2 设置暗中认可数据或日志目录地点

创建自定义目录及改变目录权限

[root@134test ~]# mkdir -p /data/mssql_data/
[root@134test ~]# chown -R mssql:mssql /data/mssql_data/

 

使用mssql-conf工具

[root@134test ~]# /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /data/mssql_data/
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.

 

重启数据库实例生效

systemctl restart mssql-server

 

测量检验(数据和日志文件都在那几个目录下)

4> create database db2;
5> go

 

[root@134test ~]# cd /data/mssql_data/
[root@134test /data/mssql_data]# ll db*
-rw-rw---- 1 mssql mssql  3932160 2017-12-27 12:31 db1_log.ldf
-rw-rw---- 1 mssql mssql 12582912 2017-12-27 12:31 db1.mdf
-rw-rw---- 1 mssql mssql  8388608 2017-12-27 12:37 db2_log.ldf
-rw-rw---- 1 mssql mssql  8388608 2017-12-27 12:33 db2.mdf

 

假诺急需单独更换日志的目录(如/tmp)

/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /tmp/

 

 

运用SQL Server创造和查询数据

新建数据库,创立一个名称叫AniuDB的数据库

  • 在 sqlcmd 命令提醒符中,粘贴以下 Transact-SQL 命令以创设测量检验数据库:
CREATE DATABASE AniuDB
  • 在下一行中,编写三个询问以回到服务器上独具数据库的名目
SELECT Name from sys.Databases
  • 前三个指令未有应声举办。 必得在新行中键入 GO 工夫奉行从前的通令:
GO

![图形描述][4]

接下去成立多个新表 itdevops,然后插入多少个新行

  • 在 sqlcmd 命令提醒符中,将上下文切换成新的 AniuDB数据库:
USE AniuDB
  • 创造名称叫 itdevops 的新表:
CREATE TABLE itdevops(id INT, name NVARCHAR(50), quantity INT)
  • 将数据插入表中
INSERT INTO itdevops VALUES (1, 'banana', 150); INSERT INTO itdevops VALUES (2, 'orange', 100);
  • 执行GO
GO

选拔数据,运维查询从 itdevops变回来数据

  • 由此 sqlcmd 命令提醒符输入查询,以回到 itdevops表中数据超过 100 的行
SELECT * FROM itdevops WHERE quantity > 100;
# 执行 GO
  • 退出 sqlcmd 命令提醒符
quit

3.3 更改 TCP 端口

使用mssql-conf工具

/opt/mssql/bin/mssql-conf set network.tcpport <new_tcp_port>

 

重启实例生效

[root@134test ~]# systemctl restart mssql-server

 

测试

[root@134test ~# /opt/mssql/bin/mssql-conf set network.tcpport 1444
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
[root@134test ~]# systemctl restart mssql-server

 

[root@134test ~]# sqlcmd -S localhost -U sa
Password: 
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred 
while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct 
and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

 

[root@134test ~]# sqlcmd -S localhost,1444 -U sa
Password: 
1> 

 

 

3.3 更改 TCP 端口

使用mssql-conf工具

/opt/mssql/bin/mssql-conf set network.tcpport <new_tcp_port>

 

重启实例生效

[root@134test ~]# systemctl restart mssql-server

 

测试

[root@134test ~# /opt/mssql/bin/mssql-conf set network.tcpport 1444
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
[root@134test ~]# systemctl restart mssql-server

 

[root@134test ~]# sqlcmd -S localhost -U sa
Password: 
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred 
while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct 
and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

 

[root@134test ~]# sqlcmd -S localhost,1444 -U sa
Password: 
1> 

 

 

从 Windows 举办三番五次

Windows 上的 SQL Server 工具连接到 Linux 上的 SQL Server 实例,操作办法与连接到别的远程 SQL Server 实例一样

  • Windows 上运维,连接受 Linux 上的 SQL Server 的任何工具:
SSMS: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-develop-use-ssms
WP: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-manage-powershell
SSDT: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-develop-use-ssdt

3.4 删除设置

使用mssql-conf工具的unset命令

/opt/mssql/bin/mssql-conf unset network.tcpport
/opt/mssql/bin/mssql-conf unset memory.memorylimitmb
……

 

重启实例生效

[root@134test ~]# systemctl restart mssql-server

 

 

3.4 删除设置

使用mssql-conf工具的unset命令

/opt/mssql/bin/mssql-conf unset network.tcpport
/opt/mssql/bin/mssql-conf unset memory.memorylimitmb
……

 

重启实例生效

[root@134test ~]# systemctl restart mssql-server

 

 

3.5 查看当前安装及安排文件

cat /var/opt/mssql/mssql.conf

 

以下是合法提供的一个演示配置。可依附实际必要直接修改/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

[sqlagent]
databasemailprofile = default
errorlogfile = /var/opt/mssql/log/sqlagentlog.log
errorlogginglevel = 7

[telemetry]
customerfeedback = true
userrequestedlocalauditdirectory = /tmp/audit

[traceflag]
traceflag0 = 1204
traceflag1 = 2345
traceflag = 3456

 

 

3.5 查看当前设置及配置文件

cat /var/opt/mssql/mssql.conf

 

以下是官方提供的二个示范配置。可依照实际必要向来改换/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

[sqlagent]
databasemailprofile = default
errorlogfile = /var/opt/mssql/log/sqlagentlog.log
errorlogginglevel = 7

[telemetry]
customerfeedback = true
userrequestedlocalauditdirectory = /tmp/audit

[traceflag]
traceflag0 = 1204
traceflag1 = 2345
traceflag = 3456

 

 

4. 参考

愈来愈多的安顿,请参见微软的法定文档:

 

 

 

 

4. 参考

越来越多的配置,请参见微软的法定文书档案:

 

 

 

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:安装并使用SQL

关键词: