MS-SQL Server on Linux? Yes, it works!


Please note that this blog has been moved.

Now it has its own domain: mynixworld.info🙂

If you want to read the latest version of this article (recommended) please click here and I open the page for you.

I’ve read hundreds of posts/articles that simply claim that MS-SQL Server does not run/work on Linux. Let me tell you this: that’s hogwash!

I have successfully installed MSDE 2000 (32-bit) on a Linux i686 architecture, more specifically on a Gentoo 32-bit machine where I have the WineHQ API framework, which allows applications designed for MS Windows to run on a POSIX-compliant OS.

Contents

  1. Prerequisite
  2. Configuration
  3. Setup
  4. Tests
  5. Troubleshooting

Prerequisite

Configuration

  • run winescript
    • click on “Select the default wineprefix” then OK
    • click on “Run winecfg” then OK
    • on Windows Version drop-down select Windows XP (or later version; I have tested Windows XP so at least I know that it works); Apply+OK
  • Install a Windows DLL or component
    • check dotnet20 (I’m not really sure that it is absolutely necessary), mdac27/mdac28 then press OK; after installing these packages close the winetricks application

Setup

  • run the following commands:
    wine /tmp/MSDE2000A.exe # this will unpack the MSDE to ~/.wine/drive_c:/MSDERelA/
    wine ~/.wine/drive_c/MSDERelA/setup.exe SAPWD="<strong-pwd>" DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL
    • it’s possible that the setup crashes; don’t worry, run the following command:
      cp ~/.wine/drive_c/users/eugen/Temp/SqlSetup/Temp/*.* ~/.wine/drive_c/windows/system32/

The above statement copies the remaining setup’ DLL into \windows\system32 folder. After the setup.exe returns (with whatever output messages you may find on your terminal) it would be a good idea to wait few minutes (because it’s a chance that it still copies/starting/doing something while you think you are done). Then, just run the following command:

ps aux|grep "sqlservr\.exe"

to make sure that there is a sqlservr.exe process running out there (it may appear as <defunct>, but don’t worry about that).

Normally the MSSQLSERVER service should be started automatically (by setup, right after the installation finishes). You can check this in at least three ways:

  • in your terminal run the command “wine net start MSSQLSERVER”; it should return an output like this:
user@gentoo ~ $ wine net start MSSQLSERVER
The MSSQLSERVER service is starting.
Service already running.
user@gentoo ~ $
  • in your terminal run the command “ps aux|grep -i sqlservr.exe”; if MSSQLSERVER is running then the command above should show the corresponding process
  • in your terminal run the command
wine ~/.wine/drive_c/Program\ Files/Microsoft\ SQL\ Server/80/Tools/Binn/sqlmangr.exe

it will start an M$ application that allows you to control the MSSQLSERVER services.

If it seems that it’s not installed properly then it is worthing reading also this post.

Tests

To check the stability and performance of MS-SQL SERVER 2000 Desktop Engine (MSDE) under Linux, I’ve done few tests.

Test 1: migrate a SQL2008 database (from a Windows Server) to the SQL 2000 (MSDE) instance that runs on top of Linux:

  • I have a SQL2008 database (1GB data) with the following objects:
    • tables: 1043
      • primary keys: 1002
      • indexes: 95
      • defaults: 19569
      • triggers: 3819
    • views: 361
    • stored procedures: 1314
    • functions: 866

I exported this database from SQL 2008 to SQL 2000 (MSDE) instance. It exported successfully!

I created the script for all triggers, stored procedures, functions then I run the script against the SQL 2000 (MSDE) instance on Linux. It ran successfully!

The database is (in fact) the server component of a client-server ERP package.

I have also a client application (the client component of the mentioned ERP package) that was built to run on Windows but thanks to WineHQ it runs also on Linux.

I have also installed the MDAC 2.8 on this Linux machine so that the client application connects the server component (the SQL database) by using the ADO components.

I have run the client application against the new SQL Server under Linux, so the entire solution (designed for Microsoft Windows) runs now on top of Linux. It works just perfectly!

Note: I haven’t found a way to determine SQL Server to use as many CPU as Linux provides. Maybe it’s something between SQL Server and WineHQ, I don’t know. So for the time being my SQL Sever seems to use only 1 CPU:

Screen capture of sqlservr.exe process

Screen capture of sqlservr.exe process

Also I’ve found out that the process name will appear as “sqlservr.exe <defunct>”. A defunct process means that it’s finished but still depends on its parent which is still alive. Well, I don’t know about that, SQL Server looks pretty alive to me :o)

Troubleshooting

  1. If the SQLServer server connection doesn’t work

I have found out that sqlservr.exe (that seems <defunct> for a reason) will not respond if you don’t have a live connection to it that keeps him alive.

For instance, you may run the “SQL Server Service Manager” tool by running the following command:

wine "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmangr.exe"
SQL Server Service Manager

SQL Server Service Manager

By using this application you can start/stop the SQL Server service.

Also I found out that we can use the osql tool to stay connected on the SQL Server and to keep it alive:

osql -U <sql-user> -P "sql-password" -S <sqlserver-ip-hostname>

This will give you a SQL console where you can send your queries (in batch-mode, by terminating the batch with a GO on the last line):

1> select top 5 id,xtype,crdate from sysobjects
2> go
 id          xtype crdate                  
 ----------- ----- ----------------------- 
           1 S     2002-12-17 14:36:14.450 
           2 S     2002-12-17 14:36:14.450 
           3 S     2002-12-17 14:36:14.450 
           4 S     2002-12-17 14:36:14.450 
           6 S     2002-12-17 14:36:14.450 

(5 rows affected)
  1. An existing connection was forcibly closed by the remote host

I have also found out that when querying the database from SSMS , if I query a table that has image/blob columns and if I include them in the query and if their values are not empty, then the connection between the SSMS and SQL Server is forcibly closed:

Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

It appears to work with no problem from osql utility but from a Windows station where I have SSMS…well, you may encounter this unfortunate situation.

About Eugen Mihailescu

Always looking to learn more about *nix world, about the fundamental concepts of arithmetic, algebra and geometry. I am also passionate about programming, database and systems administration.
This entry was posted in linux and tagged , , . Bookmark the permalink.

6 Responses to MS-SQL Server on Linux? Yes, it works!

  1. Non-foe says:

    Hey, about your test result at the wine project, they can be somewhat harsh and tough about what is allowed, so please don’t take it personally, there are many reasons for those rules beyond users not having to go to other sites like persistence and copyright.
    So please alter it and resubmit, it is an important result(for database testing and so forth). I will try it my self at the earliest opportunity.

  2. Jamie Moles says:

    Have you tried getting a newer version of MSSQL such as SQL Express 2005 or 2008 to work? Would be interested in your findings

    • The problem with any MSSQL > 2K is that they are using .NET. Although one could use Mono (on Linux) to port an .NET apps (like SQL2K5, SQL2K8) on Linux, I’m not sure that it will work like a charm (in fact I have – sometimes – problems using SQL2K5/8 even on Windows, so … no comment).
      Maybe I will give it a try sometimes.

  3. mugiseyebrows says:

    Good article. I make some experiments on linux mint and find out that DISABLEROLLBACK=1 needed in `wine setup.exe` step. Otherwise `wine net start MSSQLSERVER` will return error ‘could not get handle to service’.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s