Microsoft SQL Server on Linux Pit guide

Microsoft used SQL Server to make a big news in 2016, to advertise Microsoft?? Linux to play a crowd of soft powder unprepared. But this is a good thing, Linux also has the use of SQL Server, but starting from the preview version of SQL Server on Linux configuration requires high honey, most of the cloud host users are deterred. In addition, SQL Server on Linux is limited to file systems and only supports the EXT3 and XFS file format systems, which is also annoying enough for some cloud service providers to restrict the Ext3 file format system to the cloud image.

For CentOS, for example, if you are using Ubuntu, or SUSE may be different on the steps. In general, it is recommended that you deploy in a Docker deployment manner.

Before stepping on the pit ...

Https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup-red-hat

Microsoft's official guide, follow this process to go.

Cracking memory limits

Although there is a text on the Internet to tell how to crack the preview version of 3.25G memory limitations of the article, but it is very skin, Microsoft in Update 4 after the memory limit changed to 2G, that is, even if you follow the guide, the installation of less than 2000M of free memory will get an error message:

sqlservr: This program requires a machine with at least 2000 megabytes of memory.

Sometimes we think, I ran an Express, no money to buy Enterprise or web,express can only use 1G RAM, so much to do ... In fact, the reference article is still valid, but this constant needs to be modified instead of 3250000000 instead of 2000000000. Using Python:

>>> oldfile = open("sqlservr.bak", "rb").read()>>> newfile = oldfile.replace("\x00\x94\x35\x77", "\x00\x80\x84\x1e")>>> open("sqlservr", "wb").write(newfile)

After modification, the memory limit is reduced to 512 megabytes.

However, it is important to note that the SQL Server Express version still requires 650M of memory. So if your machine is only 1G or less, it's a tough one. Be sure to use 1.5G RAM.

EXT3 file format system causes incompatibility

Microsoft says in the release note that only XFS or EXT4 file format systems are supported. Now some cloud service providers in order to ensure compatibility still use the EXT3 file format system for the system disk (resulting in SQL Server incompatibility), in fact, a little attention to ignore this point can pit for a whole day, and will always be the honey juice error, and then still unaware.
The only solution to this limitation is to put the SQL Server data file in the EXT4 file format system's mount directory. Mount the EXT4 format disk (assuming directory is /data/ ), and then create a new file directory (here /data/sqlsrv_data/ , log directory /data/sqlsrv_data/log/ ).
Set permissions:

sudo chown -R mssql /data/sqlsrv_data/sudo chown -R :mssql /data/sqlsrv_data/

If this step is omitted, subsequent installations will fail with no log file generation. or receive an error message:

ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070002)  Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG  

Then set the environment variable information:

export MSSQL_MASTER_DATA_FILE=/data/sqlsrv_data/master.mdfexport MSSQL_MASTER_LOG_FILE=/data/sqlsrv_data/mastlog.ldfexport MSSQL_ERROR_LOG_FILE=/data/sqlsrv_data/log/errorlog

Note that the above environment variable is a file location instead of a directory , and filling the directory will fail to install.

Then /opt/mssql/bin/ execute it under the directory:

./mssql-conf set filelocation.masterdatafile /data/sqlsrv_data/master.mdf./mssql-conf set filelocation.masterlogfile /data/sqlsrv_data/mastlog.ldf./mssql-conf set filelocation.errorlogfile /data/sqlsrv_data/log/errorlog

And then execute

./mssql-conf setup

For a moment, the execution

systemctl status mssql-server

Check the status of the service, if you do not see systemd[1]: Failed to start Microsoft SQL Server Database Engine. This type of death warning, then congratulations on your installation success, use Microsoft SQL Server Management Studio to start the happy Linuxxsql Server CP experience.

What if it fails?

If you fail to follow the action, especially see

FCB::Open failed: 无法打开文件号 1 的文件 d:\dbs\sh\s17o\0209_182031\cmd\16\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf。操作系统错误: 2(系统找不到指定的文件。)。

Error words ...

Yes, it is because the directory you are giving is incorrect or the database where the last installation failed is still there. The solution is to empty the file directory (if deleted, do not forget to set permissions, otherwise the file will not be accessible).

Microsoft SQL Server on Linux Pit guide