MSSQL on Linux?

Yes, you read that right. Â Thanks to the power of Crossover (Wine) I’m running SQL 4.21 on OS X. Â But the installation is *NOT* straightforward. Â Actually it doesn’t install at all. Â But you can ‘transport’ a working copy from a Windows machine into Wine, and it’ll run.

The first thing is, if you’ve ever dealt with MSSQL is that it is VERY picky about machine names. Â So first install MS SQL 4.21 in a NT 3.1/3.5/3.51/4.0 VM with a name you like. Also remember to set the default client library to TCPIP. Then create a Wine instance on your target box. Â Next you’ll need to make a few changes to the registry to force the machine name:

HKEY_CURRENT_USER\Software\Wine\Network

UseDnsComputerName

REG_SZ N

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\ComputerName\ActiveComputerName

ComputerName

REG_SZ MSDE

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\ComputerName\ComputerName

ComputerName

REG_SZ MSDE

I called my machine MSDE, as I have no imagination. Â With those keys in place the next thing to do is grab the service keys..

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\SQLServer

and

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\SQLMonitor

Then finally the MS SQL product key:

HKEY_LOCAL_MACHINE\Software\Microsoft\SQLServer

The final step was to copy over the c:\sql directory, and create icons for the SQL Server, and the management tool.

To run, start the server first (SQLSERVR.EXE) , then launch the admin tool (SQLADMIN.EXE), and you should (hopefully) be able to connect!

MS SQL Server 4.21 running on OS X

And there we go! Is it useful? Not really, but I mean it is cool! I haven’t tried this with 6.0 or 6.5 but I imagine they should work as well. 7.0 and beyond are more complicated to transpose as they require a great deal of COM integration. In theory they should work.

Sometimes less is more.

Posted on March 17, 2010 by neozeed

The more they overthink the plumbing, the easier it is to stop up the drain.

-Star Trek III

Well as of late we’ve been fighting this aging SQL server at work. It was originally a NT 4.0 server with SQL 6.5 upgraded to 7.0 then 2000 with Windows 2000. It also had to do some work with Oracle, and the dba’s were using MSDTC to make sure their transactions were getting committed into Oracle. Oh and to keep it ‘fresh’ the Oracle client was version 7 as it originally was talking to an Oracle 7 DB.

Well as the years go by, that Oracle 7 DB became Oracle 9i (already obsolete!), and we suddenly hit a transaction wall.

And along the way we virtualized the server to go into our VMWare ESX 2.x cluster, and it’s been since migrated to VMWare ESX 3.5

The server was dropping tens of thousands of these XA???????????????????.trc files, into the \winnt\system32 directory.

Well naturally you’ll eventually hit this wall of how many 8.3 translations you can do before the system CRAWLS. And boy oh boy did we hit that wall. So at first my idea was to delete all of these trc files, and let it live, but that’s not such a ‘great’ idea… As this reeks of a fundamental problem.

So the ‘first’ step in all of this madness was to up the OS to Windows 2003 enterprise (it was 2000 Advanced server before) And see how things were doing. The OS upgrade went smoothly I had slipstreamed sp2 into the update, so I only had some 90+ updates needing to be done once the OS had been upgraded. And for ‘good’ measure I thought I’d take the server from 768MB of ram up to 2GB, and set VMWare to allow 4 cpu’s for the database server. The node it was running on wasn’t doing terribly much so what the hell right?

Wrong…

The server was now performing markibly SLOWER… And yes, still dropping TRC files like there was no tomorrow.

After a bunch of digging around, I found out that in 2003 you have to click a box in the component manager to allow XA (cross architecture) transactions! Well now it wasn’t dropping as many XA trc files, but after watching it for a while, when two went to run at the same time, the SQL server would crash with a hex code saying it was out of memory.

Out of memory? I’d just given it more!?

So I did the ‘logical’ thing and gave the system 5 GB of ram, and enabled the /3GB flag in boot.ini

Same crashes.

I moved SQL server up to using 2GB of ram (out of 5, sure why not?). Same error.

Well this sucked, so we tried to update the Oracle client from 7 to 9i. In the process I found I couldn’t un-install the 7 client, nor could the 9i thing just ‘upgrade’ it, 9i kind of installed in parallel. Which led us to our next major fault, after swapping out the new client, removing the Oracle product key from the registry, and re-linking the Oracle servers using a new registration string, now ALL of our transactions against the oracle servers were failing.

Thank goodness for google, as we were able to deduce that the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsfot\MSDTC\MTxOCI was populated with all the old Oracle 7 values….

OracleOciLib -> oci.dll
OracleSqlLib -> orasql9.dll
OracleTraceFilePath -> c:\oratrace
OracleXaLib -> oraclient9.dll

So changing it to reflect Oracle 9i and suddenly our transactions were running! Even two at a time!!

But there was no doubt about it the transactions were slower then hell. We had gone from 1 minute to 11 minutes on one, and 5 minutes to just under an HOUR.

I added MORE memory to only find the SQL server couldn’t see the network card. So I added another one, and it got even SLOWER.

So in a minute of panic, I reduced the ram back to 768MB, took the VM from 4 cpu’s to 2 cpu’s and forced SQL server to use a single processor.

And our timings are now fantastic! That 1 minute process can complete now in 12 seconds!!! The other process finishes in about a minute give or take, but it’s tremendously faster.

From what I can gather, since SQL is so IO bound the more ‘top’ hardware you give it, the harder it pushes the IO stalling itself… Naturally it’s different on a physical machine, but sometimes it’s interesting to see what happens.

And may this be a lesson, just because it can emulate multiple CPU’s doesn’t mean it’ll run parallel things ‘better’…

Polling around….

Posted on December 6, 2009 by neozeed

This weekend has been kind of crappy as we had some cisco switches flake out, but nobody knew anything happened… I’d been pulled away on so many things over the last few years that monitoring them kind of fell by the waaaayside.

Well in this economic environment nobody is going to shell out tens of thousands of dollars for some simple syslog/polling system that pages people. I needed one for free.

And years ago, I wrote a simple one that revolved around SQL Server 7/MSDE 1.0

Anyways I’ve made it as 2 CD images, and if you are bored, or in the need to poll devices with a basic TCP connect you can download it here:

https://sourceforge.net/projects/simplemssqlnetw/

What may be useful for people is that I’ve put some impossible to find software on the prerequisite CD…

Namely:

NT 4.0 sp6a
MSDE 1.0
Option Pack (IIS4)
SQL 7 sp4

For some reason all the above stuff is getting dammed near impossible to find on Microsoft’s site. I guess it is end of the road for the NT 4.0 heyday. So at the least, this is my way to keep a location to download this software, as I’m sure someone will need it someday.

MSDE 1.0 for those who don’t know was a redistributable version of SQL Server 7.0 that had a 10 user limit, and a 2GB database size limit. Also there was no GUI management but it was great to use, because unlike SQL Express, it included the SQL Agent. The agent can run tasks at certain times, say like run a TSQL script that dumps a list of machines from a table into the hosts file, then tries to connect to each machine and record the state into a database…..

It was VERY useful stuff for the time.

There is no exciting screnshot, as I never did make an interface to the thing, instead I opted to configure everything through Access.

Other then that, I’ve been playing with a BackOffice 1.5 CD set I got on ebay… NT 3.51, MS Mail 3.5 & SQL 6.0!! It’s been so long, but MSSQL 6.0 was the first SQL server that I ever was payed to manage… It’s amazing how far we’ve come as an industry, and at the same point how things stay the same, although the installation of NT 3.51 is SO FAST!!!

On using SQL 4.21

Posted on July 6, 2009 by neozeed

Ok, I know it’s been all retro SQL server but bear with me… MSDE/SqlExpress is just too small for some pet project, and I want to run my SQL natively… So it’s 4.21 for now.

Anyways, I thought I’d mention that to run SQL 4.21 on Windows 7, you’ll need the userenv.dll from a Vista computer, just copy it to where your i386 files are so you can run the setup, even if it complains about your x64 dll as â€˜The image file c:\sql\i386\userenv.dll is valid but is for a machine type other then the current machine…â€™. Let it continue and it’ll still run. Also remember to set it to 256 colors in the compatibility tab or you’ll never get to use any radio boxes… It still barfs initializing the services, but it’ll let you run the server, and slap down a working master database from a blank installed machine (say from NT 4.0..) Naturally you’ll have to comment out the line in setup.inf about the client access DLL dbnmpntw.dll, and register c:\sql\binn;c:\sql\dll yourself in the system’s path environment variable…

Oh and since I’m using such an ancient engine, one thing that is kind of annoying is that .net 3.5 only wants to talk to SQL Server 7.0 and higher. However using ODBC you can force it to talk to the ancient 4.21a

Here is what I’m using in my c# to talk to the sql server:

string myConnString = “Driver={SQL Server};Server=.;Database=datamine;Uid=sa;Pwd=”;
OdbcConnection myConnection = new OdbcConnection(myConnString);
OdbcCommand myCommand = new OdbcCommand(query, myConnection);
myConnection.Open();
OdbcDataReader myReader = myCommand.ExecuteReader();
myReader.Close();
myConnection.Close();

So with a little tweak you can get it running on Windows 7, and have .net talking to the database.

Another note is be sure to run the sqladmin tool, and hit the â€˜systemâ€™ button, then under the manage menu, select the system configuration. By default it wonâ€™t accept remote connections, and limits itself to 6MB of ram! Youâ€™ll want to bump up its system limits. Iâ€™d also say use tempdb in ram, as you probably have more ram in your computer then any server back from 1992â€¦ I have 8GB for example on my desktop.

Just donâ€™t forget that something this ancient wonâ€™t grow your databases for youâ€¦ Iâ€™m pretty sure you can add additional database devices, and expand the DB, but it will be a while until I fill my first DB… Also youâ€™ll probably find that a lot of the fancy SQL features youâ€™ve grown to like are gone, but surprisingly it still offers a good base. And itâ€™s like 25Mb for the complete install… A pittance by modern standardsâ€¦

A quick tour of the packaging of SQL 4.21

Posted on July 3, 2009 by neozeed

Someone sent me some questions about SQL Server 4.21. I guess since it’s a ‘rare’ item, and most people will never see one in the wild, I thought I’d show some of it off.

Vista with SQL 4.21

So there you go, that’s the PC I used, and the software as mentioned. As you can see from the SQL Server box, it’s the “Departmental System” version good for 64 users/128 connections.

I guess for the others interested I’ll show some box contents or something.

Contents of the package

Front of the box

High resolution of the back...

Check out the ‘requirements’ and it’s features… That’s why I love this old software! It’s not as bad as it’s reputation is… But on the x64 it’ll get it’s full 2GB of ram!

Fun with Microsoft SQL Server 4.21

Posted on July 2, 2009 by neozeed

First off I have this copy of SQL server 4.21 that I wanted to run on my new super fast Vista x64 box. Now it’sÂ 32bit but completely unsupported…. And off the CD the setup doesn’t work..

But not to fear!

First from a Windows NT 4.0 CD I copy in the setupdll.dll file into the source path for SQL Server 4.21. Now theÂ setup.exe will actually run.

So the first hint is to set the compatibility for the setup.exe to “run in 256 colors”. Now it’ll render stuffÂ correctly, however it’ll barf because it wants to overwrite the file “dbnmpntw.dll” which won’t work. So simplyÂ editing the setup.inf file and commenting out the dbnmpntw.dll line.

Now the real pain is that it’ll run the setup, but it’ll crash when starting the sql server as a service… IÂ still don’t know what’s crashing it.

Next grab service pack (#4) for Sql 4.21

You’ll need some kind of MS-DOS environment to extract the service pack… Either DOSBox or VirtualPC with MS-DOSÂ will quickly do the job. I like DOSBox for this kind of thing as it can directly mount the native filesystem..

Anyways copy the DLL’s to c:\sql\dll & the exe to c:\sql\binn

Then add c:\sql\dll;c:\sql\binn to the system PATH environment….

From here you can run the sqlservr.exe in an administrator prompt, and it’ll run…

C:\SQL\sp>SQLSERVR.EXE
09/07/02 19:18:18.87 kernel SQL Server for Windows NT 4.21a.05 (Intel X86) Jun 28 1995 08:20:31
Copyright (c) 1988-1995 Microsoft Corporation; Copyright Sybase, Inc 1987-1995

09/07/02 19:18:18.88 kernel Copyright (C) 1988-1993 Microsoft Corporation.
09/07/02 19:18:18.88 kernel Copyright Sybase, Inc. 1987, 1993
09/07/02 19:18:18.88 kernel All rights reserved.
09/07/02 19:18:18.88 kernel Use, duplication, or disclosure by the United States Government is subject
09/07/02 19:18:18.88 kernel to restrictions set forth in FAR subparagraphs 52.227-19(a)-(d) for civilian
09/07/02 19:18:18.88 kernel agency contracts and DFARS 252.227-7013(c)(1)(ii)for Department of Defense
09/07/02 19:18:18.88 kernel contracts. Sybase reserves all unpublished rightsunder the copyright laws of
09/07/02 19:18:18.88 kernel the United States.
09/07/02 19:18:18.88 kernel Sybase, Inc. 6475 Christie Avenue, Emeryville, CA94608, USA.
09/07/02 19:18:18.88 kernel Logging SQL Server messages in file ‘C:\SQL\LOG\ERRORLOG’
09/07/02 19:18:18.90 kernel Maximum number of pages in batch io is limited to8
09/07/02 19:18:18.90 kernel initconfig: number of user connections limited to5
09/07/02 19:18:18.90 server SQL Server is starting at priority class ‘normal’with dataserver serialization
turned on.
09/07/02 19:18:18.90 kernel initializing virtual device 0, C:\SQL\DATA\MASTER.DAT
09/07/02 19:18:18.92 kernel Opening Master Database …
09/07/02 19:18:18.92 server Loading SQL Server’s default sort order and character set
09/07/02 19:18:18.92 server Recovering Database ‘master’
09/07/02 19:18:18.92 server Recovery dbid 1 ckpt (701,14)
09/07/02 19:18:18.94 server server is unnamed
09/07/02 19:18:18.99 server Recovering database ‘model’
09/07/02 19:18:18.99 server Recovery dbid 3 ckpt (132,26)
09/07/02 19:18:19.00 server Clearing temp db
09/07/02 19:18:19.28 kernel Using ‘SQLEVENT.DLL’ version ‘4.21.00’.
09/07/02 19:18:19.28 kernel Using ‘OPENDSNT.DLL’ version ‘4.21.09.02’.
09/07/02 19:18:19.29 kernel Using ‘NTWDBLIB.DLL’ version ‘4.21.00’.
09/07/02 19:18:19.29 ods Using ‘SSNMPNTW.DLL’ version ‘4.21.0.0’ to listenon ”.
09/07/02 19:18:21.28 server Recovery complete.
09/07/02 19:18:21.28 server SQL Server’s default sort order is:
09/07/02 19:18:21.29 server ‘bin_cp850’ (ID = 40)
09/07/02 19:18:21.29 server on top of default character set:
09/07/02 19:18:21.29 server ‘cp850’ (ID = 2)

Contrl+C will kill it… You can fire up the SQL Administrator (C:\Sql\Binn\Sqladmin.exe) but it’ll freak becauseÂ the master database isn’t fully built. It’s very close to usable but so far the only way to get around theÂ incomplete master is to copy one from an existing DB… Good thing you have that NT 4.0 CD & VirtualPC right? JustÂ remember that SQL 4.21 will only install PRIOR to sp6. SO the install order is NT 4.0, SQL 4.21, SP6,Â VMadditions, SQL service pack, then copy the c:\sql directory to overrite the prior one. You may also want toÂ export the registry keys from the Virtual NT 4.0 machine [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLServer].

Ok now for the downside… For whatever reason it will *NOT* listen on TCP/IP. So it’ll only do named pipes… IfÂ you are only doing local stuff it’ll be just fine. The services don’t work, but the uptick is that it’s SMALL…Â A zip of the installed SQL is under 5 megabytes!

So in the end, it’s not terribly usefull, but I found it interesting that with a little hacking SQL Server 4.21aÂ will run on Vista x64 sp2.

Microsoft SQL Server 4.21 on Vista x64

And here is a screenshot… I’ll have to do some quick benchmarks of 4.21 vs the 2008 Express stuff.

Virtually Fun

Fun with Virtualization

Category Archives: SQL Server