凉山州住房和城乡建设厅网站,怎么样建设企业网站,企业管理平台系统,建筑设计资质加盟问题引入 这天老鸟满面春风找到菜鸟#xff1a;“鸟儿啊#xff0c;看你最近研究SQL Server On Linux如鱼得水#xff0c;干得不错啊。不过呢#xff0c;这是一个张扬个性的年代#xff0c;要创新#xff0c;要与众不同#xff0c;那你怎么在Linux平台对SQL Server做个性… 问题引入 这天老鸟满面春风找到菜鸟“鸟儿啊看你最近研究SQL Server On Linux如鱼得水干得不错啊。不过呢这是一个张扬个性的年代要创新要与众不同那你怎么在Linux平台对SQL Server做个性化的配置吧比如我们数据库文件存放的路径备份文件存放的路径排序规则等等”。“鸟哥今天心情不错啊捡到金子啦得了这就按您的指示办去”。 问题分析 老鸟这个需求还是很有现实意义的比如在Windows平台我们会标准化SQL Server数据库文件存放的路径定制化排序规则等。比如mdf文件会存放在D:\DATA\DBName\SQL目录ldf文件会存放在D:\DATA\DBName\LOG目录下。让我们看看SQL Server On Linux是怎么张扬它的个性化配置的。 个性化配置 在个性化配置之前让我们来看SQL Server On Linux的默认配置是什么样子。 数据存放位置和排序规则 查看数据库物理文件的存放位置只需要访问视图sys.master_files默认排序规则访问视图sys.databases。脚本如下 USE masterGOSELECT database_name db_name(database_id) ,file_name name,physical_name
FROM sys.master_filesSELECT database_name name, collation_name
FROM sys.databases; 查询结果截图如下这里必须要吐槽不吐不快从SSMS查看数据库的物理位置有两点很难理解当然不知道微软后续的发版会不会改善Windows是使用反斜线“\”来划分目录结构而Linux系统使用的是正斜线“/”。查询出来的结果集目录中会强制添加C盘地址。最终的对应关系是Linux上的/var/opt/mssql/data对应于Windows上的目录C:\var\opt\mssql\data。吐槽截图如下 个性化配置脚本 在最开始做个性化配置脚本时候我所有的这些目录是放在/var/opt/mssql/data/这个根目录下后来发现这种做法有一个很大的坑后面的“一个深坑”错误处理时会讲到。后来我把根目录放到/data/下算是踩过了这个坑。这么大的一个深坑微软的攻城狮你出来我们聊聊我保证不打残你。 # vim reconfMSSQLOnLinux.sh#!/bin/sh# --------------------------------------------## purpose: # this script is for MSSQL on Linux# reconfiguration.## --------------------------------------------# change mssql-server listen on 1435 from 1433echo change mssql-server listen port to 1435/opt/mssql/bin/mssql-conf set tcpport 1435# Change the default data directory locationecho change mssql-server default data directory locationmkdir /data/SQL
chown mssql /data/SQL chgrp mssql /data/SQL/opt/mssql/bin/mssql-conf set defaultdatadir /data/SQL# Change the default log directory locationecho change mssql-server default log directory locationmkdir /data/LOG
chown mssql /data/LOG chgrp mssql /data/LOG/opt/mssql/bin/mssql-conf set defaultlogdir /data/LOG# Change the default dump directory locationecho change mssql-server default dump directory locationmkdir /data/DUMP
chown mssql /data/DUMP chgrp mssql /data/DUMP/opt/mssql/bin/mssql-conf set defaultdumpdir /data/DUMP# Change the default backup directory locationecho change mssql-server default backup direcotry locationmkdir /data/BACKUP
chown mssql /data/BACKUP chgrp mssql /data/BACKUP/opt/mssql/bin/mssql-conf set defaultbackupdir /data/BACKUP# Enable/Disable traceflagsecho mssql-server trace flag 1222 1204 enabled/opt/mssql/bin/mssql-conf traceflag 1222 1204 on# Change the SQL Server collationecho change mssql-server default collation/opt/mssql/bin/mssql-conf set-collation[rootlocalhost ~]# chmod x reconfMSSQLOnLinux.sh 执行配置脚本 仔细看这个返回结果也是在根目录/data/前加了盘符C始终觉得怪怪的。最后输入新的排序规则确认后一会儿SQL Server就会完成个性化的设置并重启。 # sh reconfMSSQLOnLinux.shchange mssql-server listen port to 1435Applying value 1435 to tcpport.
Restart of SQL Server is required to apply this setting. Please run systemctl restart mssql-server.service when ready.
change mssql-server default data directory location
Applying value C:\data\SQL to defaultdatadir.
Restart of SQL Server is required to apply this setting. Please run systemctl restart mssql-server.service when ready.
change mssql-server default log directory location
Applying value C:\data\LOG to defaultlogdir.
Restart of SQL Server is required to apply this setting. Please run systemctl restart mssql-server.service when ready.
change mssql-server default dump directory location
Applying value C:\data\DUMP to defaultdumpdir.
Restart of SQL Server is required to apply this setting. Please run systemctl restart mssql-server.service when ready.
change mssql-server default backup direcotry location
Applying value C:\data\BACKUP to defaultbackupdir.
Restart of SQL Server is required to apply this setting. Please run systemctl restart mssql-server.service when ready.
mssql-server trace flag 1222 1204 enabled
Applying traceflag 1222.
Restart of SQL Server is required to apply this setting. Please run systemctl restart mssql-server.service when ready.
Applying traceflag 1204.
Restart of SQL Server is required to apply this setting. Please run systemctl restart mssql-server.service when ready.
change mssql-server default collation
This option will change the collation for SQL Server and RESTART the server. Continue [Yes/No]: Yes
Enter the collation: Chinese_PRC_CI_AS 一个深坑 在最开始测试过程中所有目录放到根目录/var/opt/mssql/data/下执行会出现以下异常信息无法创建tempdb错误的原因是在C:\data\SQL\目录下不存在tempdb.mdf文件。第一反应是什么东西啊C:\data\SQL\tempdb.mdf我没有配置任何文件放到这个目录啊还C盘呢你以为是你的Windows操作系统吗最后聪明伶俐人见人爱车间车爆胎的菜鸟大胆猜测小心求证终于搞明白了。反过来猜想估计微软这帮攻城狮只截取了最后两层目录比如/var/opt/mssql/data/SQL他们会把/data/SQL做为数据库数据文件的存放目录实在是令人着急。来看看错误信息: 2016-12-14 09:24:35.40 spid6s Error: 5123, Severity: 16, State: 1.
2016-12-14 09:24:35.40 spid6s CREATE FILE encountered operating system error 31(A device attached to the system is not functioning.) while attempting to open or create the physical file C:\data\SQL\tempdb.mdf.2016-12-14 09:24:35.44 spid6s Error: 17204, Severity: 16, State: 1.2016-12-14 09:24:35.44 spid6s FCB::Open failed: Could not open file C:\data\SQL\tempdb.mdf for file number 1. OS error: 2(The system cannot find the file specified.).2016-12-14 09:24:35.44 spid6s Error: 5120, Severity: 16, State: 101.2016-12-14 09:24:35.44 spid6s Unable to open the physical file C:\data\SQL\tempdb.mdf. Operating system error 2: 2(The system cannot find the file specified.).2016-12-14 09:24:35.45 spid6s Error: 1802, Severity: 16, State: 4.2016-12-14 09:24:35.45 spid6s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.2016-12-14 09:24:35.45 spid6s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the operating system error log that may indicate why the tempdb files could not be initialized. 截个图留恋证明下 有了这层意思的精神领会以后解决问题的方法就So easy了。 # mkdir -p /data/SQL# mkdir -p /data/LOG# chown mssql -R /data# chgrp mssql -R /data 再次重试个性化配置脚本sh reconfMSSQLOnLinux.sh这次总算成功了谢天谢地。 打开防火墙 为了让外部客户端SSMS连接重新配置后的实例请打开防火墙1435端口 [rootlocalhost ~]# firewall-cmd --zonepublic --add-port1435/tcp --permanentsuccess[rootlocalhost ~]# firewall-cmd --reloadsuccess 再次检查数据存放位置 重新配置完成后接下来做如下测试创建数据看看数据库mdf和ldf文件会不会放到/data/SQL和/data/LOG目录下。备份数据库看看数据库备份文件会不会放到/data/BACKUP目录。排序规则检查检查数据库是否是新的排序规则Chinese_PRC_CI_AS create database TestDB2;
GOBACKUP DATABASE [TestDB2] TO DISK NTestDB2_full_20161213.bak WITH NOFORMAT, NOINIT, NAME TestDB2-full, SKIP, NOREWIND, NOUNLOAD, STATS 10BACKUP LOG [TestDB2] TO DISK NTestDB2_LogBackup_20161213.bak WITH NOFORMAT
, NOINIT, NAME NTestDB2_LogBackup_2016-12-13, NOSKIP, NOREWIND, NOUNLOAD , STATS 5GOUSE masterGOSELECT database_name db_name(database_id) ,file_name name,physical_name
FROM sys.master_filesSELECT database_name name, collation_name
FROM sys.databases; 查询结果展示如下使用Linux的tree命令看看/data目录文件结构如下图展示从SSMS查询结果和Linux目录结构来看所有现象都满足预期菜鸟对SQL Server On Linux进行了个性化配置。 写在最后 过程历经艰辛但最终我们到达了成功的彼岸总算对SQL Server On Linux进行了个性化的配置。就像我们最开始研究Linux版SQL Server说的那样他还是只个刚出生的孩子还有很多不完善的地方甚至看起来还有点怪异。但是让我们充满期望给予宽容和友善让他茁壮成长。 相关文章 SQL Server 急救包First Responder Kit入门教程SQL Server on Linux 理由浅析SQLServer On Linux Package List on CentOSSQL Server on Linux的文件和目录结构 原文地址https://yq.aliyun.com/articles/67204 .NET社区新闻深度好文微信中搜索dotNET跨平台或扫描二维码关注
