MSSQL、SQL Server
     分类:数据库     有: 0 条评论

MSSQL、SQL Server

     分类:数据库     有: 0 条评论

安装

个人感觉微软的东西安装比较简单,维护会比较困难,SQL也一样,直接下载好安装就行了,有分普通版和企业版,企业版才有备份计划管理器,可以通过计划来设置数据库备份和旧备份清理;普通版只能通过bat+.sql脚本进行备份。

SQL Server 2017 for Linux

这里使用centos7 ,其他版本都是类似的,有官方源和文档。官方源:https://packages.microsoft.com

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

yum update
yum install -y mssql-server mssql-server-agent
rpm -qa | grep mssql
find / -name mssql

# 初始化
root@MSSQL01 yum.repos.d]# /opt/mssql/bin/mssql-conf setup
Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID)
  7) Enterprise Core (PAID)
  8) I bought a license through a retail sales channel and have a product key to enter.

# 这里因为有企业版密钥,所以选择6,按提示输入密钥,秘钥格式是: xxxxx-xxxxx-xxxxx-xxxxx-xxxx,可以用2016企业版的密钥。
# 输入密码,最少8位。官方要求内存最低要3.25G,cpu没要求,破解限制:https://www.ancii.com/group/database/30842.html


firewall-cmd --zone=public --add-port=1433/tcp --permanent
firewall-cmd --reload
systemctl status mssql-server
systemctl restart mssql-server
systemctl enable mssql-server

# SQL Server就安装完了,这时可以用mssql客户端去连接mssql for linux了。
# 安装命令行工具
curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
yum update
yum install -y mssql-tools unixODBC-devel
# 安装之前系统会提示你必须同意相关许可,注意:需要输入大写的YES

# 添加/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

# sqlcmd进行本地连接
sqlcmd -S localhost -U SA -P 'your-passwd'
或
sqlcmd -S localhost -U SA

参考:http://blog.csdn.net/cmzsteven/article/details/78161516?locationNum=4&fps=1


设定、配置

限制IP、账户登陆数据库

法1:启动了防火墙的,需要外网访问都会放行1433端口,可以在此基础上加一下IP限制就行了。只允许IP列表内的IP访问1433端口。

法2:用sa账户登录数据库,New Query粘贴代码,创建触发器(如果备份计划等不是用sa账户登录的,将会失效);执行。

# 新建触发器
CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON
AS
BEGIN
  DECLARE @ClientHost nvarchar(max);
  SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');
IF @ClientHost NOT IN ('' ,'192.168.0.150' ,'192.168.13.250' ,'127.0.0.1' ,'localhost')
ROLLBACK;
END;


# 如果创建了,增加或减少IP(修改)
Alter TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON
AS
BEGIN
  DECLARE @ClientHost nvarchar(max);
  SELECT @ClientHost = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)');
IF @ClientHost NOT IN ('' ,'192.168.0.150' ,'192.168.13.250' ,'127.0.0.1' ,'localhost')
ROLLBACK;
END;

执行之后会在Server Object下Triggers看到触发器
mark
删除触发器也只能在这里删除。


备份计划报错:This task requires the connection to be initialized.

SQL Server Agent无法启动,无法重启
这种情况基本是账户密码的问题,点开计划任务,上面的Manage Connections,修改为新的账户密码就行了;还有一种可能就是触发器里限制了IP登录,本地用hostname无法登录。


更改数据库存放位置

法1:http://jingyan.baidu.com/article/1709ad80969f934634c4f0c8.html

法2:

--查看当前的存放位置  
select database_id,name,physical_name AS CurrentLocation,state_desc,size from sys.master_files    
where database_id=db_id(N'数据库名');  

--修改文件的存放位置下次启动生效  
--testDb为数据库名,  
alter database 数据库名 modify file ( name = 文件名(不包含后缀), filename = '文件存储路径');  
alter database 数据库名 modify file ( name = 文件名(不包含后缀), filename = '文件存储路径');  

--例:
alter database testDb modify file ( name = testDb, filename = 'G:\SQL_DATA\testDb\testDb.mdf');  
alter database testDb modify file ( name = testDb_log, filename = 'G:\SQL_DATA\testDb\testdb_log.ldf');  


--修改默认的数据库文件存放位置(即时生效)  
EXEC xp_instance_regwrite    
@rootkey='HKEY_LOCAL_MACHINE',    
@key='Software\Microsoft\MSSQLServer\MSSQLServer',    
@value_name='DefaultData',    
@type=REG_SZ,    
@value='E:\MSSQL_MDF\data'    
GO    
--修改默认的日志文件存放位置<span style="font-family: Arial, Helvetica, sans-serif;">(即时生效)</span>  
EXEC master..xp_instance_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultLog',
@type=REG_SZ,
@value='E:\MSSQL_MDF\log'
GO

数据库同步

http://jingyan.baidu.com/article/9f7e7ec0bc3bb76f2915544c.html
http://kb.cnblogs.com/page/103975/


报错

更改win密码后备份失效问题

如果在设定备份计划时是用的win用户认证登录数据库的,在修改win密码后,备份计划因密码错误无法登录数据库,在备份计划中修改为sa账户登录即可(同理,修改了sa的密码也是要重新设定的)

打开数据库的数据表时显示"目录名称无效"

以下是一种方法,可能是无效的,我遇到这个问题时尝试了其他方法,如修改注册表之类的,并未成功,用以下方法解决了问题。
C:\Users\administrator\AppData\Local\Temp中的Temp文件夹下新建名为“2”的文件夹,即可解决。

开启远程访问

服务启动了,但是没有1433端口的情况,参考:http://blog.csdn.net/happymagic/article/details/51835522

恢复数据库报错

报:

The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\SQLData\MSSQL12.SQLEXPRESS\MSSQL\DATA\xxxxx_log.ldf'.

解决:
首先改用sa账户登录试试,查看数据库文件存放目录是不是有该文件,查看其他库文件属性是不是只读(如果是只读那其他数据库也应该会出问题)
SQL Manages Studio中没有看到该数据库;说明在恢复时复发创建同名的数据库文件;那就删除该数据库文件(XXX.mdf和XXX.ldf),再恢复一次就可以了。

如果还是不行,就是其他问题了,参考:
https://stackoverflow.com/questions/39154226/create-file-encountered-operating-system-error-5access-is-denied
https://stackoverflow.com/questions/19060441/cannot-install-adventureworks-2012-database-operating-system-error-5-access-i/19061137#19061137
https://stackoverflow.com/questions/18286765/sql-server-operating-system-error-5-5access-is-denied


报:size would exceed your licensed limit of 10240 MB per database.

System.Data.SqlClient.SqlError: CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database. (Microsoft.SqlServer.SmoExtended)

解决:
Express版的只允许每个数据库主文件大小不超过10G,换个企业版或者开发版的序列号就OK了。


数据库一直处于还原状态,不可用

mark
执行以下语句,成功后刷新一下就OK了。

RESTORE DATABASE [数据库名] WITH RECOVERY
(●゚ω゚●)